Coursera SQL for Data Science | Quiz答案, Week1

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

Week 1: Selecting & Retrieving Data with SQL

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

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

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

目录

Keypoints

Module 1 Quiz

Module 1 Coding Questions

Keypoints

Data Models, Part 1: Thinking About Your Data

Databases: A container (usually a file or set of files) to store organized data; a set of related information

Tables: A structured list of data or a specific type

Column: A single field in a table - all tables are made up of one or more columns

Row: A record in a table

Data Models, Part 2: The Evolution of Data Models

Data Modeling: ~organizes and structures information into multiple, related tables; can represent a business process or show relationships between business processes; should closely represent the real world

Evolution of Data Models:

1960 Hierarchical, 1969 Network, 1970 Relational, 1976 Entity Relationship, 1978 Semantic, 1985 Object-Oriented, 1990 Extended Relational, 2009 NoSQL

NoSQL: A mechanism for storage and retrieval of unstructured data modelled by means other than tabular relations in relational databases

Data Models, Part 3: Relational vs. Transactional Models

Relational Model: Allows for easy querying and data manipulation in an easy, logical and intuitive way.

Transactional Model: Operational database-insurance claims within a healthcare database

Entity, Attribute, Relationship - 1 to 1, 1 to many, many to many

ER Diagrams: Primary Keys, Foreign Keys,

ER diagram Notation:

Chen: 1 M, M N, 1 1

Crow’s Foot Notation: line

UML Class Diagram Notation: 1.1 1., 1. 1.*, 1.1 1.1

Coding: SELECT Statement, Creating Tables, Creating Temporary Tables, Adding Comments to SQL

Module 1 Quiz

我印象中Coursera上的Quiz题目顺序可能会随机生成，这里按照我遇到的罗列下来：）

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

A. Data Analyst

B. Data Scientist

C. Backend Developer

D. DBA

E. QA Engineer

Answer: ABCDE

Question 2: How does a data scientist and DBA differ in how they use SQL?

A. DBA’s are the only ones who merge datasets together.

B. Data scientists only query the database and don’t create tables.

C. DBAs manage the database for other users.

D. Data scientists don’t write complex queries.

Answer: C

Question 3: Which of the following statements are true of Entity Relationship (ER) Diagrams?

A. They usually are represented in a visual format.

B. They identify the Primary Keys

C. They speed up your querying time.

D. They are usually a representation of a business process.

E. They show you the relationships between tables.

F. They only represent entities in the diagram.

Answer: ABDE

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

Answer:

SELECT *

FROM customers

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

Answer:

SELECT

FirstName

,LastName

,Company

FROM customers

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

A. One-to-one

B. One-to-many

C. Many-to-one

D. Many-to-many

Answer: B

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

A. Star Schema

B. Relational Model

C. Transactional Model

Answer: B

Question 8: When using the "CREATE TABLE" command and creating new columns for that table, which of the following statements is true?

A. You can create the table and then assign data types later

B. You must insert data into all the columns while creating the table

C. You must assign a data type to each column

Answer: C

Question 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?

A. Column 1

B. Column 2

C. Column 1 OR Column 2

Answer: A

Question 10: In order to retrieve data from a table with SQL, every SQL statement must contain? A. WHERE

B. SELECT

C. CREATE

D. FIND

Answer: B

Module 1 Coding Questions

Question 1：Retrieve all the records from the Employees table.

Code：

SELECT *

From Employees;

What is Robert King's mailing address?

Answer：590 Columbia Boulevard West, Lethbridge, AB, CANADA T1K 5N8

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

Code：

SELECT FirstName ,

LastName ,

BirthDate ,

Address ,

City ,

State

From Employees;

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

Answer: Steve

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

Code:

SELECT *

From Tracks

Limit 20;

What is the runtime in milliseconds for the 5th track, entitled "Princess of the Dawn"? 

Answer: 375418