Coursera SQL for Data Science - Notes

最新推荐文章于 2024-07-30 18:36:42 发布

一个竹哥更比六个强

最新推荐文章于 2024-07-30 18:36:42 发布

阅读量2.9w

点赞数 13

文章标签： sql sqlite 数据库

本文链接：https://blog.csdn.net/lwz77789/article/details/107777686

版权

这篇博客详细记录了Coursera上的SQL for Data Science课程的学习笔记，涵盖了从选择和检索数据到过滤、排序、计算，再到子查询和联接，以及数据修改和分析等多个方面。通过随堂练习和代码测评，展示了如何使用SQL进行数据操作，包括从Chinook数据库中选取数据，并解决实际问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL for Data Science - Notes

待补充ing

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

Module 1：Select and Retrieve Data with SQL

随堂练习

Rtrieve all the data from the tracks table

Select *
From Tracks;

DATA: all the data
TABLE: tracks table

Return the playlist id, and name from the playlists table

Select Playlistid,
Name
From Playlists;

DATA: playlist id and name
TABLE: playlists table

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

随堂练习

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

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，用到了<>符号，表示排除

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’

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

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

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

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

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

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

最低0.47元/天解锁文章