【高频SQL基础50题】1-5

目录

1.可回收且低脂的产品

2. 使用唯一标识码替换员工ID

3.有趣的电影 

4.每位教师所教授的科目种类的数量 

5.每位经理的下属员工数量 

1.可回收且低脂的产品

  

 查询题。

# Write your MySQL query statement below
SELECT product_id
FROM Products
WHERE low_fats="Y" AND recyclable = "Y"

2. 使用唯一标识码替换员工ID

 连接题。

通过id连接

# Write your MySQL query statement below
SELECT en.unique_id,e.name
FROM Employees AS e
#左连接
LEFT JOIN EmployeeUNI AS en
ON e.id=en.id

 

3.有趣的电影 

 聚合函数题。

要实现非操作，并且操作，以及降序操作

mod(id,2)=1 来确定奇数 id，然后添加 description != 'boring' 来解决问题

oder by

# Write your MySQL query statement below
SELECT *
FROM cinema
WHERE id%2=1 AND description!='boring'
ORDER BY rating DESC

 

4.每位教师所教授的科目种类的数量 

 排序和分组题

# Write your MySQL query statement below
SELECT teacher_id,COUNT(DISTINCT subject_id) AS cnt
FROM Teacher
GROUP BY teacher_id

 

5.每位经理的下属员工数量 

 高级查询和连接。

不就是自连接嘛

# Write your MySQL query statement below
SELECT 
     e1.employee_id,e1.name,COUNT(e2.employee_id) AS reports_count,
     ROUND(AVG(e2.age)) AS average_age
FROM
     Employees e1
JOIN
     Employees e2 ON e1.employee_id=e2.reports_to
GROUP BY
     e1.employee_id,e1.name
ORDER BY
     e1.employee_id;