Coursera SQL for Data Science - Notes

SQL for Data Science - Notes

待补充ing

注：同一类编程题型只摘选1~2道做笔记；所有概念题型都不摘选哦，详见课堂讲义

Module 1：Select and Retrieve Data with SQL

随堂练习

1. Rtrieve all the data from the tracks table

Select *
From Tracks;

DATA: all the data
TABLE: tracks table

2. Return the playlist id, and name from the playlists table

Select Playlistid,
Name
From Playlists;

DATA: playlist id and name
TABLE: playlists table

3. Select all the columns from the Playlist Track table and limit the results to 10 records

Select *
From Playlist_track 
Limit 10;

DATA: all the columns
TABLE: playlist track list
FILTER: limit 10 results

代码测评

很简单，不做记录了哈

Module 2：Filtering, Sorting, and Calculating Data with SQL

随堂练习

1. Find the distinct values for the extended step from salary_range_by_job_classification

SELECT 
distinct Extended_step
From salary_range_by_job_classification

DATA: extended step
TABLE: salary_range_by_job_classification
FILTER: distinct

2. Excluding $0.00, what is the minimum bi-weekly high rate of pay (please include the dollar sign and decimal point in your answer)

Select 
min(Biweekly_high_Rate)
From salary_range_by_job_classification
Where Biweekly_high_Rate <> '$0.00'

DATA: bi-weekly high rate of pay
CAL: minimum
TABLE: salary_range_by_job_classification
FILTER: Excluding $0.00，用到了<>符号，表示排除

3. What is the pay type for all the job codes that start with ‘03’

Select
job_code,
pay_type
From salary_range_by_job_classification
Where job_code Like '03%'

DATA: pay type，job codes
TABLE: salary_range_by_job_classification
FILTER: start with ‘03’

4. Run a query to find the Effective Date (eff_date) or Salary End Date (sal_end_date) for grade Q90H0

Select
grade,
eff_date,
sal_end_date
From salary_range_by_job_classification
Where grade = 'Q90H0'

DATA: Effective Date (eff_date)，Salary End Date (sal_end_date) ，grade
p.s 凡是出现了的都要SELECT
TABLE: salary_range_by_job_classification
FILTER: for grade Q90H0

5. What Step are Job Codes 0110-0400

SELECT 
Step
,Job_Code
From salary_range_by_job_classification
Where Job_Code Between '0110' AND '0400'

DATA: Step，Job Codes
p.s 凡是出现了的都要SELECT
TABLE: salary_range_by_job_classification
FILTER: Job Codes 0110-0400

6. Sort the Biweekly low rate in ascending order

SELECT 
Biweekly_Low_Rate 
From salary_range_by_job_classification
Order by Biweekly_Low_Rate ASC

DATA: Biweekly low rate
TABLE: salary_range_by_job_classification
ORDER: in ascending order

7. What is the Biweekly High Rate minus the Biweekly Low Rate for job Code 0170

SELECT 
(Biweekly_High_Rate - Biweekly_Low_Rate) AS Dif 
,Job_Code
From salary_range_by_job_classification
Where Job_Code='0170'

DATA: Biweekly High Rate，Biweekly Low Rate，Job Code
CAL: Biweekly High Rate minus the Biweekly Low Rate
TABLE: salary_range_by_job_classification
FILTER: job Code 0170

8. What is the Extended Step for Pay Types M, H, and D

SELECT 
Extended_Step
,Pay_Type
From salary_range_by_job_classification
Where Pay_Type IN ('M', 'H', 'D')

DATA: Extended Step，Pay Types
TABLE: salary_range_by_job_classification
FILTER: Pay Types M, H, and D

9. What is the step for Union Code 990 and a Set ID of SFMTA or COMMN

SELECT 
Step
,Union_Code
,SetID
From salary_range_by_job_classification
Where Union_Code = '990' AND (SetID = 'SFMTA' OR SetID = 'COMMN')

DATA: step，Union Code，Set I