Coursera SQL for Data Science | Quiz答案, Week2
最近开始系统性地学习SQL语言，在Coursera平台接触到了UCD学院提供的《SQL for Data Science》课程。该课程设计精良且非常适合初学者入门学习。这节课的概念题量较大，在此详细解析这些关键知识点并分享相关的练习题及代码实现过程。期待与志同道合的朋友共同进步！
Week 2: Filtering, Sorting, and Calculating Data with SQL
接第1周：[Coursera SQL for Data Science | Quiz答案, Week1_Spectre23c的博客-博客]( "Coursera SQL for Data Science | Quiz答案, Week1_Spectre23c的博客-博客)
Coursera上的数据科学SQL课程提供Week3阶段的所有练习题及答案解析
SQL for Data Science on Coursera 中的 Quiz 答案和 Week4 期次的相关内容
目录
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
Data filtering is utilized for performing the following tasks.
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 conducting an analysis on musicians whose names begin with the character “K”. Select the correct query to retrieve only those artists whose names start with this character.
Answer:
SELECT name
FROM Artists
WHERE name LIKE ‘K%’;
Question 3: A null value and a zero are equivalent in meaning. True or false?
Answer: False
Question 4: Identify the following statements that are accurate concerning wildcard features (Choose 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: Choose the statements below which IS NOT true for 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: Identify or list all correct mathematical operators in SQL (choose the correct mathematical operators).
Answer:
- (multiplication)
+ (addition)
/ (division)
- (subtraction)
**问题7：**以下哪些属于聚合函数？（全选）
Answer:
MAX()
COUNT()
MIN()
Question 8： Which of the following are correct regarding 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
Each column in your select statement can be included in a GROUP BY clause, excluding those involved in 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
Once this instance's query is confined to 10 entries, correctly executing the query will reveal the total number of records available - please input that number below.
168
Question3
Execute the query to retrieve all customers among these states.
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: Identify all invoices belonging to customers numbered 56 and 58, where the total amount falls within a range of 1.00 to 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%"
Only 10 records are displayed, but the system will show the total number of records for this query - I invite you to enter the total number of records for this query below.
15
Question6
Query: Issue a command to retrieve all customer accounts with a prefix of 'J' belonging to gmail.com.
SELECT*
FROM Customers
WHERE Email LIKE "j%gmail.com"
请输入一封返回的电子邮件地址（滚动至右侧可能会看到结果）下方。
Question7
Run Query:** Retrieve all invoices located in the billing cities Brasília, Edmonton, and Vancouver, sorted 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? Please input the number without a $ sign.
Remember to sort in descending order in order to obtain the correct answer.
13.86
Question8
Execute the query: Display a list showing, for each client, their total order counts, which can be found in the invoices table, sorted in descending order.
SELECT *
, COUNT (InvoiceId) AS Number_of_Orders
FROM Invoices
GROUP BY CustomerId
ORDER BY Number_of_Orders DESC
Please provide the total number of items assigned to person number 8 in this list. I would like you to input 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
Provided that only 10 records are returned by the query, if it is executed properly, it will show the total number of records available. Enter this figure in the space provided below.
158