Coursera SQL for Data Science | Quiz答案, Week2

最近开始初步学习SQL，在Coursera上找到了UCD的SQL for Data Science，个人感觉挺细致的，适合入门学习。这节课的视频里概念题很多，所以在这里把关键概念知识点和做过的Quiz和Coding题分享出来，希望同行者一起进步～

Week 2: Filtering, Sorting, and Calculating Data with SQL

接 Week1:Coursera SQL for Data Science | Quiz答案, Week1_Spectre23c的博客-优快云博客

接 Week3:Coursera SQL for Data Science | Quiz答案, Week3_Spectre23c的博客-优快云博客

接 Week4: Coursera SQL for Data Science | Quiz答案, Week4_Spectre23c的博客-优快云博客

目录

Keypoints

Module 2 Quiz

Module 2 Coding Questions

---

Keypoints

Basic Filtering with SQL

SELECT column_name, column_name

FROM table_name

WHERE column_name operator value;

=, <>, <, >, > =, < =， BETWEEN, IS NULL

Advanced Filtering with SQL

SELECT column_name, column_name

FROM table_name

WHERE column_name operator value;

IN Operator

WHERE column_name IN (x,y,z);

OR Operator

WHERE column_name = 'x' OR 'y';

AND Operator

WHERE (column_name = 'x' OR 'y' )AND z > 10;

NOT Operator

WHERE NOT column_name = 'x' AND NOT column_name = 'y' ;

Using Wildcards in SQL

Wildcards are only used with strings

Example:

%Pizza       Grabs anything ending with the word Pizza

Pizza%       Grabs anything after the word Pizza

%Pizza%    Grabs anything before and after the word Pizza

S%E           Grabs anything that starts with "S" and ends with "E" 

t%@gmail.com  Grabs gmail addresses that start with "t"

Sorting with ORDER BY

SELECT column_name, column_name

FROM table_name

ORDER BY column_name;

Sort direction: DESC and ASC 

Math Operations

+, -, *, /

Multiplication Example:

SELECT

a

, b

, a*b AS c

FROM x;

Aggregate Functions

AVG(), COUNT(), MIN(), MAX(), SUM()

eg:

SELECT AVG(x) AS y

FROM a;

SELECT COUNT(DISTINCT x)

FROM y;

Grouping Data with SQL

eg:

SELECT x

, COUNT(y) AS z

FROM w

GROUP BY w

HAVING COUNT (y) > = 2;

Module 2 Quiz

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

Answer:

Narrows down the results of the data.

Removes unwanted data in a calculation

Reduces the strain on the client application

Reduce the time it takes to run the query

Helps you understand the contents of your data

Question 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%’;

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

Answer: False

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

Answer:

Wildcards take longer to run compared to a logical operator

Wildcards at the end of search patterns take longer to run

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

Answer: 

Cannot sort by a column not retrieved

Can be anywhere in the select statement

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

Answer:

* (multiplication)

+ (addition)

/ (division)

- (subtraction)

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

Answer:

MAX()

COUNT()

MIN()

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

Answer:

NULLs will be grouped together if your Group By column contains NULLs

GROUP BY clauses can contain multiple columns

Every column in your select statement may/can be present in a group by clause, except for aggregated calculations.

Question 9: Select the true statement below.

Answer:

HAVING filters after the data is grouped.

Question 10: Which is the correct order of occurrence in a SQL statement?

Answer:

select, from, where, group by, having

Module 2 Coding Questions

Question1

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

SELECT *

FROM Tracks

WHERE Milliseconds >=5000000

How many tracks are returned?

2

Question2

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

SELECT *

FROM Invoices

WHERE 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.

168

Question3

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

SELECT *

FROM Customers

WHERE State IN ("RJ","DF","AB","BC","CA","WA","NY")

What company does Jack Smith work for?

Microsoft Corp

Question4

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

SELECT *

FROM Invoices

WHERE (CustomerId IN("56","58")) AND (Total BETWEEN 1 AND 5) 

What was the invoice date for invoice ID 315?

10-27-2012

Question5

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

SELECT*

FROM Tracks

WHERE 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.

15

Question6

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

SELECT*

FROM Customers

WHERE Email LIKE "j%gmail.com"

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

jubarnett@gmail.com 

Question7

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

SELECT*

FROM Invoices

WHERE BillingCity IN ("Brasília","Edmonton"," Vancouver ") 

GROUP BY BillingCity

ORDER BY 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.

13.86

Question8

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 *

, COUNT (InvoiceId) AS Number_of_Orders

FROM Invoices

GROUP BY CustomerId

ORDER BY Number_of_Orders DESC

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

7

Question9

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

SELECT *

, COUNT (TrackId) AS number

FROM Tracks

GROUP BY AlbumId

HAVING number >= 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.

158