SQL for Data Science Coursera Quiz Answers

All Weeks SQL for Data Science Coursera Quiz Answers

SQL for Data Science Coursera Week 1 Quiz Answers

Quiz 1:Let’s Practice!

Q 1. This statement will return an error. Please list why.

SELECT
TrackID
Name
AlbumID
FROM tracks

It doesn’t state where to get the data from
It’s missing comma after “TrackID”, and “Name”
It lists too many columns

Q 2. In the ER diagram below, the infinity symbol is representing a “many” relationship and the key is representing “one”. Select all the tables that have a one-to-many relationship.

Answer:

Artist to Albums
Customers to Invoices
Employees to Customers

Q 3. When using SQLite, what datatypes can you assign to a column when creating a new table? Select all that apply.

Answer:

Real
Null
Text
Integer

Q 4. Primary Keys must be unique values.

True

False

Q 5. What is the query below missing in order to execute?

Select
From
A Comma
The Column Names

Quiz 2: Practice Simple Select Queries

Q 1. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run query: Retrieve all the data from the tracks table. Who is the composer for track 18?

Select *
From Tracks;

Answer:

left join Albums

on Tracks.AlbumId=Albums.AlbumId

where Albums.Title=”Californication”;

Q 2. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Retrieve all data from the artists table. Look at the list of artists, how many artists are you familiar with (there is no wrong answer here)?

Select *
From Artists;

Answer:

comment the Answer

Q 3. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Retrieve all data from the invoices table. What is the billing address for customer 31?

Select *
From Invoices;

Answer:

comment the Answer

Q 4. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Return the playlist id, and name from the playlists table. How many playlists are there? Which would you classify is your favorite from this list?

Select Playlistid,
Name
From Playlists;

answer:

Comment the Answer.

Q 5. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Return the Customer Id, Invoice Date, and Billing City from the Invoices table. What city is associated with Customer ID number 42? What was the invoice date for the customer in Santiago?

Select CustomerId,
InvoiceDate, 
BillingCity 
From Invoices;

Answer:

Comment the Answer.

Q 6. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Return the First Name, Last Name, Email, and Phone, from the Customers table. What is the telephone number for Jennifer Peterson?

Select FirstName, 
LastName, 
Email, 
Phone
From Customers;

Answer:

Comment the Answer.

Run Query: Return the Track Id, Genre Id, Composer, Unit Price from the Tracks table. How much do these tracks cost?

Select TrackId, 
GenreId, 
Composer, 
UnitPrice 
From Tracks;

Answer:

Comment the Answer.

Q 8. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Select all the columns from the Playlist Track table and limit the results to 10 records. How might this information be used?

Select *
From Playlist_track 
Limit 10;

Answer:

comment the Answer.

Q 9. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Select all the columns from the Media Types table and limit the results to 50 records. What happened when you ran this query? Were you able to get all 50 records?

Select *
From Media_types
Limit 50;

Answer:

comment the Answer.

Q 10. To prepare for the graded coding quiz, you will be asked to execute a query, read the results, and select the correct answer you found in the results. This question is for you to practice executing queries. I have provided you the script for this query, a simple select statement. Think of this as a sandbox for you to practice. As you practice executing queries, take time to read the results in order to prepare for the quiz and get comfortable writing a basic select statement.

Run Query: Select all the columns from the Albums table and limit the results to 5 records. How many columns are in the albums table?

What is the name of the 9th album in this list?

Select *
From Albums;

Answer:

Comment the Answer.

Quiz 3: Module 1 Quiz

Q 1. Select the jobs below that may use SQL in their work (select all that apply).

Answer.

DBA
Backend Developer
Data Scientist
QA Engineer
Data Analyst

Q 2. How does a data scientist and DBA differ in how they use SQL?

Data scientists only query the database and don’t create tables.
Data scientists don’t write complex queries.
DBA’s are the only ones who merge datasets together.
DBAs manage the database for other users.

Q 3. Which of the following statements are true of Entity Relationship (ER) Diagrams?

Answer:

They are usually a representation of a business process.
They speed up your querying time.
They identify the Primary Keys
They usually are represented in a visual format.
They show you the relationships between tables.
They only represent entities in the diagram.

Q 4. Select the query below that will retrieve all columns from the customers table.

Answer:

SELECT * FROM customers

Q 5.Select the query that will retrieve only the Customer First Name, Last Name, and Company.

Answer:

SELECT 
FirstName
,LastName
,Company
FROM customers

Q 6. The ER diagram below is depicting what kind of relationship between the EMPLOYEES and CUSTOMERS tables?

One-to-one
One-to-many
Many-to-one
Many-to-many

Q 7. The data model depicted in the ER diagram below could be described as a _______________.

Transactional Model
Star Schema
Relational Model

Q 8. When using the “CREATE TABLE” command and creating new columns for that table, which of the following statements is true?

You must assign a data type to each column
You must insert data into all the columns while creating the table
You can create the table and then assign data types later

Q 9. Look at the values in the two columns below. Based on the values in each column, which column could potentially be used as a primary key?

Column 1	Column 2
5	2
6	4
1	5
2	5
34	32
8	6
9	4

Column 1
Column 2
Column 1 OR Column 2

Q 10. In order to retrieve data from a table with SQL, every SQL statement must contain?

CREATE
SELECT
WHERE
FIND

Quiz 4: Module 1 Coding Questions

Q 1. For all of the questions in this quiz, we are using the Chinook database. All of the interactive code blocks have been setup to retrieve data only from this database.

Retrieve all the records from the Employees table.

What is Robert King’s mailing address? Note: You will have to scroll to the right in order to see it.

1111 6 Ave SW, Calgary, AB, CANADA T2P 5M5
590 Columbia Boulevard West, Lethbridge, AB, CANADA T1K 5N8
683 10 Street SW, Calgary, AB, CANADA T2P 5G3
11120 Jasper Ave. NW, Edmonton, AB, CANADA T5K 2N1

Q 2. Retrieve the FirstName, LastName, Birthdate, Address, City, and State from the Employees table.

Which of the employees listed below has a birthdate of 3-3-1965?

Michael
Robert
Steve
Jane
Nancy

Q 3. Retrieve all the columns from the Tracks table, but only return 20 rows.

What is the runtime in milliseconds for the 5th track, entitled “Princess of the Dawn”? Note: You will need to scroll to the right to see it, and you may want to copy and paste the number to ensure it is entered correctly.

Answer:

comment the Answer.

SQL for Data Science Coursera Week 2 Quiz Answers

Quiz 01: Module 2 Practice Quiz

Q 1. For all the questions in this practice set, you will be using the Salary by Job Range Table. This is a single table titled: salary_range_by_job_classification. This table contains the following columns:

SetID
Job_Code
Eff_Date
Sal_End_Date
Salary_setID
Sal_Plan
Grade
Step
Biweekly_High_Rate
Biweekly_Low_Rate
Union_Code
Extended_Step
Pay_Type

Please refer to this information to write queries to answer the questions. Are you ready to get started?

Yes, I am ready to begin.
No, I am not ready to begin.

Q 2. Find the distinct values for the extended step. The code has been started for you, but you will need to program the third line yourself before running the query.

Which of the following values is not a distinct value?

Q 3. Excluding $0.00, what is the minimum bi-weekly high rate of pay (please include the dollar sign and decimal point in your answer)? The code has been started for you, but you will need to add onto the last line of code to get the correct answer.

Select 
min(Biweekly_high_Rate)
From salary_range_by_job_classification

Answer:

Where Biweekly_high_Rate <> ‘$0.00’ (note: it’s not the answer ,add this line in your code to get the Answer)

Q 4. What is the maximum biweekly high rate of pay (please include the dollar sign and decimal point in your answer)? The code has been started for you, but you will need to add onto the last line of code to get the correct answer.

Answer:

comment the Answer :comment the Answer: comment the Answer :

Q 5. What is the pay type for all the job codes that start with ’03’? The code has been started for you, but you will need to program the fourth and fifth lines yourself before running the query.

Select
job_code,
pay_type

Answer:

comment the Answer:

Q 6. Run a query to find the Effective Date (eff_date) or Salary End Date (sal_end_date) for grade Q90H0? The code has been started for you, but you will need to program the third through the sixth lines yourself before running the query.

Select
grade,

Answer:

comment the Answer:

Q 7. Sort the Biweekly low rate in ascending order. There is no starter code, as you need to write and run the query on your own. Hint: there are 4 lines to run this query.

Are these values properly sorted?

Answer:

Q 8. Write and run a query, with no starter code to answer this question: What Step are Job Codes 0110-0400? Hint: there are 6 lines to run this query.

Answer:

comment the Answer:

Q 9. Write and run a query, with no starter code or hints to answer this question: What is the Biweekly High Rate minus the Biweekly Low Rate for job Code 0170?

Answer:

comment the Answer:

Q 10. Write and run a query, with no starter code or hints to answer this question: What is the Extended Step for Pay Types M, H, and D?

Answer:

comment the Answer:

Q 11. Write and run a query, with no starter code or hints to answer this question: What is the step for Union Code 990 and a Set ID of SFMTA or COMMN?

Answer:

comment the Answer:

Quiz 02: Module 2 Quiz

Q 1. Filtering data is used to do which of the following? (select all that apply)

Answer:

Helps you understand the contents of your data
Reduce the time it takes to run the query
Reduces the strain on the client application
Narrows down the results of the data.
Removes unwanted data in a calculation

Q 2. You are doing an analysis on musicians that start with the letter “K”. Select the correct query that would retrieve only the artists whose name starts with this letter.

Answer:

SELECT name
FROM Artists
WHERE name LIKE ‘K%’;

Q 3. A null and a zero value effectively mean the same thing. True or false?

True
False

Q 4. Select all that are true regarding wildcards (Select all that apply.)

Answer:

Wildcards at the end of search patterns take longer to run
Wildcards take longer to run compared to a logical operator

Q 5. Select the statements below that ARE NOT true of the ORDER BY clause (select all that apply)

Answer:

Can be anywhere in the select statement
Cannot sort by a column not retrieved

Q 6. Select all of the valid math operators in SQL (select all that apply).

Answer:

– (subtraction)
+ (addition)
/ (division)
* (multiplication)

Q 7. Which of the following is an aggregate function? (select all that apply)

Answer:

COUNT()
MAX()
MIN()

Q 8. Which of the following is true of GROUP BY clauses? (Select all that apply.)

Answer:

Every column in your select statement may/can be present in a group by clause, except for aggregated calculations.
NULLs will be grouped together if your Group By column contains NULLs
GROUP BY clauses can contain multiple columns

Q 9.Select the true statement below.

WHERE filters after the data is grouped
HAVING filters after the data is grouped.

Q 10. Which is the correct order of occurrence in a SQL statement?

select, group by, from, where, having
select, from, where, group by, having
select, from, where, order by, having
select, having, where, group by

Quiz 03: Module 2 Coding Assignment

Q 1. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the tracks that have a length of 5,000,000 milliseconds or more.

select Count(TrackId)
from Track
where Milliseconds >= 5000000

How many tracks are returned?

Answer:

Q 2. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices whose total is between $5 and $15 dollars.

select count(DISTINCT i.InvoiceId)
from Invoice as i
where i.Total between 5 and 15

While the query in this example is limited to 10 records, running the query correctly will indicate how many total records there are – enter that number below.

Answer:

168

Q 3. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY.

select FirstName, LastName, Company, State
from Customer
where State in ('RJ', 'DF', 'AB', 'BC', 'CA', 'WA', 'NY')

What company does Jack Smith work for?

Rogers Canada
Apple Inc.
Google Inc.
Microsoft Corp

Q 4. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00.

select InvoiceId, InvoiceDate, CustomerId, Total
from Invoice
where CustomerId in (56, 58) AND (Total >= 1.00 and Total <= 5.00)

What was the invoice date for invoice ID 315?

10-27-2012
12-22-2013
1-29-2013
6-12-2010

Q 5. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the tracks whose name starts with ‘All’.

select t.Name, COUNT(t.Name)
from Track t
where t.Name like 'All%'

While only 10 records are shown, the query will indicate how many total records there are for this query – enter that number below.

Answer:

Q 6. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the customer emails that start with “J” and are from gmail.com.

select c.Email
from Customer c
where c.Email like 'J%gmail.com'

Enter the one email address returned (you will likely need to scroll to the right) below.

Answer:

[email protected]

Q 7. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find all the invoices from the billing city Brasília, Edmonton, and Vancouver and sort in descending order by invoice ID.

select i.InvoiceId, i.Total
from Invoice i
where i.BillingCity in ('Brasilia', 'Edmonton', 'Vancouver')
order by i.InvoiceId DESC

What is the total invoice amount of the first record returned? Enter the number below without a $ sign. Remember to sort in descending order to get the correct answer

Answer:

13.86

Q 8. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Show the number of orders placed by each customer (hint: this is found in the invoices table) and sort the result by the number of orders in descending order.

select i.CustomerId , COUNT(i.InvoiceId)
from Invoice i
group by i.CustomerId
order by COUNT(i.InvoiceId) DESC

What is the number of items placed for the 8th person on this list? Enter that number below.

Answer:

Q 9. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram to familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Run Query: Find the albums with 12 or more tracks.


select t.TrackId, t.AlbumId
from Track t
group by t.AlbumId
having COUNT(DISTINCT t.TrackId) >= 12;

While the number of records returned is limited to 10, the query, if run correctly, will indicate how many total records there are. Enter that number below.

Answer:

158

SQL for Data Science Coursera Week 3 Quiz Answers

Quiz 01: Practice Quiz – Writing Queries

Q 1. All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

How many albums does the artist Led Zeppelin have?

Answer:

Comment the Answer:

Q 2. All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Answer:

Comment the Answer

Q 3. All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query?

Answer:

Q 4. All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

Find the total price for each album.

Answer:

Comment the Answer.

Q 5 .All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

How many records are created when you apply a Cartesian join to the invoice and invoice items table?

Only 25 records will be shown in the output so please look at the bottom of the output to see how many records were retrieved.

Answer:

Comment the Answer.

Quiz 02: Module 3 Quiz

Q 1. Which of the following statements is true regarding subqueries?

Subqueries always process the innermost query first and the work outward.
Subqueries always process the outermost query first and the work inward.
Subqueries will process whichever query you indicate for them to process first.

Q 2. If you can accomplish the same outcome with a join or a subquery, which one should you always choose?

Joins are usually faster, but subqueries can be more reliable, so it depends on your situation.
Whichever one you understand better and can write faster.
A subquery because they are always faster
A join because they are always faster

Q 3.The following diagram is a depiction of what type of join?

Right Join
Left Join
Inner Join
Full Outer Join

Q 4. Select which of the following statements are true regarding inner joins. (Select all that apply)

Answer:

There is no limit to the number of table you can join with an inner join.
Performance will most likely worsen with the more joins you make
Inner joins are one of the most popular types of joins use

Q 5. Which of the following is true regarding Aliases? (Select all that apply.)

Answer:

Aliases are often used to make column names more readable.
SQL aliases are used to give a table, or a column in a table, a temporary name.
An alias only exists for the duration of the query.

Q 6. What is wrong with the following query?

SELECT Customers.CustomerName, Orders.OrderID
FROM LEFT JOIN ON Customers.CustomerID = Orders.CustomerID FROM Orders AND Customers
ORDER BY
CustomerName;

Column names do not have an alias
The table name comes after the join condition
Should be using an inner join rather than a left join

Q 7. What is the difference between a left join and a right join?

A right join is always used before a full outer join, whereas a left join is always used after a full outer join
A left join always is used before a right join in a query statement
There is actually no difference between a left and a right join.
The only difference between a left and right join is the order in which the tables are relating.

Q 8. If you perform a cartesian join on a table with 10 rows and a table with 20 rows, how many rows will there be in the output table?

200
10
15
20

Q 9. Which of the following statements about Unions is true? (select all that apply)

Answer.

Each SELECT statement within UNION must have the same number of columns
The UNION operator is used to combine the result-set of two or more SELECT statements
The columns must also have similar data types

Q 10. Data scientists need to use joins in order to: (select the best answer)

Retrieve data from multiple tables.
Filter data from multiple tables.
Create new tables.

Quiz 03: Module 3 Coding Assignment

Q 1. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Using a subquery, find the names of all the tracks for the album “Californication”.

select t.Name
from Tracks t
where t.AlbumId = ( select a.AlbumId
from Albums a
where a.Title = 'Californication')

What is the title of the 8th track?

Answer:

Porcelain

Q 2. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Find the total number of invoices for each customer along with the customer’s full name, city and email.

select c.CustomerId, c.FirstName, c.LastName, c.City, c.Email, COUNT(i.InvoiceId
from Customers c join Invoices i
on c.CustomerId = i.CustomerId
Group by c.CustomerId

After running the query described above, what is the email address of the 5th person, František Wichterlová? Enter the answer below (feel free to copy and paste).

Answer:

f[email protected]

Q 3. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Retrieve the track name, album, artistID, and trackID for all the albums.

select t.Name, a.Title, ar.Name, t.TrackId
from Artists ar
inner join Albums a
on ar.ArtistId = a.ArtistId
inner join Tracks t
on a.AlbumId = t.AlbumId

What is the song title of trackID 12 from the “For Those About to Rock We Salute You” album? Enter the answer
below.

Answer:

Breaking The Rules

Q 4. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Retrieve a list with the managers last name, and the last name of the employees who report to him or her.

select mgr.LastName Manager, e.LastName Employee
from Employees e
left join Employees mgr
on e.ReportsTo = mgr.EmployeeId

After running the query described above, who are the reports for the manager named Mitchell (select all that apply)?

Answer:

Callahan
King

Q 5. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Find the name and ID of the artists who do not have albums.

select a.Title, ar.Name, ar.ArtistId
from Artists ar
left join Albums a
on ar.ArtistId = a.ArtistId
where a.Title is NULL

After running the query described above, two of the records returned have the same last name. Enter that name below.

Answer:

Gilberto

Q 6. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Use a UNION to create a list of all the employee’s and customer’s first names and last names ordered by the last name in descending order.

select e.FirstName, e.LastName
from Employees e
UNION
select c.FirstName, c.LastName
from Customers c
order by c.LastName DESC

After running the query described above, determine what is the last name of the 6th record? Enter it below. Remember to order things in descending order to be sure to get the correct answer.

Answer:

Taylor

Q 7. All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

See if there are any customers who have a different city listed in their billing city versus their customer city.

select c.CustomerId, c.FirstName, c.LastName, c.City
from Customers c
join Invoices i
on c.CustomerId = i.CustomerId
where c.City <> i.BillingCity

No customers have a different city listed in their billing city versus customer city.
3 customers have a different city listed in their billing city versus customer city.
8 customers have a different city listed in their billing city versus customer city.
17 customers have a different city listed in their billing city versus customer city.

SQL for Data Science Coursera Week 4 Quiz Answers

Quiz 01: Module 4 Quiz

Q 1. Which of the following are supported in SQL when dealing with strings? (Select all that apply)

Answer:

Trim
Concatenate
Lower
Substring
Upper

Q 2. What will the result of the following statement be?

SELECT SUBSTR('You are beautiful.', 3)

u are beautiful.
beautiful.
You are beautiful.
This will return an error

Q 3. What are the results of the following query?

select * orders
where order_date = ‘2017-07-15’

Additional information:

Orders = integer
Order_date = datetime

Answer:

You will get all the orders with an order date of 2017-07-15.
You will get all of the orders.
You won’t get any results.

Q 4. Case statements can only be used for which of the following statements (select all that apply)?

Answer:

Update
Delete
Insert
Select

Q 5. Which of the following is FALSE regarding views?

Views will remain after the database connection has ended
Views are stored in a query
Views can be used to encapsulate queries

Q 6. You are only allowed to have one condition in a case statement. True or false?

Answer:

False

Q 7. Select the correct SQL syntax for creating a view.

Answer:

CREATE VIEW
customers AS
SELECT * 
FROM customers
WHERE Name LIKE '%I'

Q 8. Profiling data is helpful for which of the following? (Select all that apply)

Answer:

Understanding your data
Filter out unwanted data elements

Q 9. What is the most important step before beginning to write queries?

Deciding what tables you want to join
Deciding what should be done on the client application vs the RDMS
Understanding your data

Q 10. When debugging a query, what should you always remember to do first?

Start simple and break it down first
Start with the inner most query
Make sure you didn’t miss any commas.
Start by examining the joins

Quiz 02: Module 4 Coding Questions

Pull a list of customer ids with the customer’s full name, and address, along with combining their city and country together. Be sure to make a space in between these two and make it UPPER CASE. (e.g. LOS ANGELES USA)

select c.CustomerId,
c.FirstName || ' ' || c.LastName as Full_Name,
c.Address,
UPPER(c.City || ' ' || c.Country)
from Customer c

What is the city and country result for CustomerID 16?

Answer:

MOUNTAIN VIEW USA

Create a new employee user id by combining the first 4 letters of the employee’s first name with the first 2 letters of the employee’s last name. Make the new field lower case and pull each individual step to show your work.

select c.FirstName,
substr(c.FirstName, 1,4) as FirstNameShort,
c.LastName,
substr(c.LastName,1,2) as LastNameShort,
LOWER(substr(c.FirstName,1,4) || substr(c.LastName,1,2)) as NewID
from Customers c

What is the final result for Robert King?

Answer:

RobeKi

Show a list of employees who have worked for the company for 15 or more years using the current date function. Sort by lastname ascending.

1	select e.LastName,e.FirstName,e.BirthDate,DATE('now') - e.BirthDate as Age,DATE('n
2	from Employees e
3	where Tenure >= 15	Run
4	order by e.LastName asc

What is the lastname of the last person on the list returned?\

Answer:

Peacock

Profiling the Customers table, answer the following question.

select *
from Customers c
where c.Company	

is null

Are there any columns with null values? Indicate any below. Select all that apply.

Answer:

Fax
Company
Postal Code

Find the cities with the most customers and rank in descending order.

1	select c.City, count(c.CustomerId)
2	from Customers c
3	group by c.City	Run
4	order by count(c.CustomerId) DESC

Which of the following cities indicate having 2 customers?

Answer:

London
Mountain View
São Paulo

Create a new customer invoice id by combining a customer’s invoice id with their first and last name while ordering your query in the following order: firstname, lastname, and invoiceID.

1	select  c.FirstName  ||  c.LastName  ||   i.InvoiceId
2	from Customers c
3	left join Invoices i
4	on c.CustomerId = i.CustomerId
5	where NewInvoiceId like 'AstridGruber%'
6	order by c.FirstName, c.LastName,  i.InvoiceId	as	NewInvoiceId

Select all of the correct “AstridGruber” entries that are returned in your results below. Select all that apply.

Answer:

AstridGruber273
AstridGruber296
AstridGruber370

Get All Course Quiz Answers of Learn SQL Basics for Data Science Specialization

SQL for Data Science Coursera Quiz Answers

Data Wrangling, Analysis and AB Testing with SQL Coursera Quiz Answers

Distributed Computing with Spark SQL Coursera Quiz Answers

SQL for Data Science Coursera Quiz Answers – Networking Funda

All Weeks SQL for Data Science Coursera Quiz Answers

SQL for Data Science Coursera Week 1 Quiz Answers

Quiz 1:Let’s Practice!

Quiz 2: Practice Simple Select Queries

Quiz 3: Module 1 Quiz

Quiz 4: Module 1 Coding Questions

SQL for Data Science Coursera Week 2 Quiz Answers

Quiz 01: Module 2 Practice Quiz

Quiz 02: Module 2 Quiz

Quiz 03: Module 2 Coding Assignment

SQL for Data Science Coursera Week 3 Quiz Answers

Quiz 01: Practice Quiz – Writing Queries

Quiz 02: Module 3 Quiz

Quiz 03: Module 3 Coding Assignment

SQL for Data Science Coursera Week 4 Quiz Answers

Quiz 01: Module 4 Quiz

Quiz 02: Module 4 Coding Questions

Get All Course Quiz Answers of Learn SQL Basics for Data Science Specialization

Leave a ReplyCancel Reply

All Weeks SQL for Data Science Coursera Quiz Answers

SQL for Data Science Coursera Week 1 Quiz Answers

Quiz 1:Let’s Practice!

Quiz 2: Practice Simple Select Queries

Quiz 3: Module 1 Quiz

Quiz 4: Module 1 Coding Questions

SQL for Data Science Coursera Week 2 Quiz Answers

Quiz 01: Module 2 Practice Quiz

Quiz 02: Module 2 Quiz

Quiz 03: Module 2 Coding Assignment

SQL for Data Science Coursera Week 3 Quiz Answers

Quiz 01: Practice Quiz – Writing Queries

Quiz 02: Module 3 Quiz

Quiz 03: Module 3 Coding Assignment

SQL for Data Science Coursera Week 4 Quiz Answers

Quiz 01: Module 4 Quiz

Quiz 02: Module 4 Coding Questions

Get All Course Quiz Answers of Learn SQL Basics for Data Science Specialization

Newsletter Updates

Leave a ReplyCancel Reply

Related Posts

Critical Thinking: Reasoned Decision Making Week 03 Quiz Answers

Positive Psychology: Resilience Skills Coursera Quiz Answers

Positive Psychology: Character, Grit and Research Methods Quiz Answers

Trending now