Coursera SQL for Data Science | Quiz答案, Week3

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

Week 3: Subqueries and Joins in SQL

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

接Week2: Coursera SQL for Data Science | Quiz答案, Week2_Spectre23c的博客-CSDN博客

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

目录

Keypoints

Module 3 Quiz

Module 3 Coding Questions

---

Keypoints

Using Subqueries

Joining Tables

Cartesian (Cross) Joins

Inner Joins

Aliases and Self Joins

Advanced Joins: Left, Right, and Full Outer Joins

Unions

Module 3 Quiz

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

Answer:

Subqueries always process the innermost query first and the work outward.

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

Answer:

Joins are usually faster, but subqueries can be more reliable, so it depends on your situation.

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

Answer: Inner Join

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

Answer:

Inner joins are one of the most popular types of joins use

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

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

Answer:

Aliases are often used to make column names more readable.

An alias only exists for the duration of the query.

SQL aliases are used to give a table, or a column in a table, a temporary name.

6. What is wrong with the following query?

Answer:

The table name comes after the join condition

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

Answer:

The only difference between a left and right join is the order in which the tables are relating.

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?

Answer: 200

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

Answer:

The UNION operator is used to combine the result-set of two or more SELECT statements

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

Answer:

Retrieve data from multiple tables.

Module 3 Coding Questions

1. Using a subquery, find the names of all the tracks for the album "Californication".

What is the title of the 8th track?

Answer: Porcelain

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

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: frantisekw@jetbrains.com

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

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

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

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

Answer: Callahan, King

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

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

Answer: João Gilberto, Bebel Gilberto  

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

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 

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

Indicate your answer below.

Answer: No customers have a different city listed in their billing city versus customer city.