MySQL Task2

项目三：超过5名学生的课（难度：简单）

编写一个 SQL 查询，列出所有超过或等于5名学生的课。

DROP TABLE IF EXISTS courses;

CREATE TABLE IF NOT EXISTS courses (
student	VARCHAR(10)	NOT NULL ,
class	  VARCHAR(20) NOT NULL);

INSERT INTO courses VALUES ('A', 'Math');
INSERT INTO courses VALUES ('B', 'English');
INSERT INTO courses VALUES ('C', 'Math');
INSERT INTO courses VALUES ('D', 'Biology');
INSERT INTO courses VALUES ('E', 'Math');
INSERT INTO courses VALUES ('F', 'Computer');
INSERT INTO courses VALUES ('G', 'Math');
INSERT INTO courses VALUES ('H', 'Math');
INSERT INTO courses VALUES ('I', 'Math');
INSERT INTO courses VALUES ('A', 'Math');

SELECT class,COUNT(class) FROM courses
GROUP BY class
HAVING COUNT(class) >= 5 ;

[外链图片转存失败(img-mISyuuG2-1565449541421)(C:\Users\ranchunfu\AppData\Roaming\Typora\typora-user-images\1565448658261.png)]

项目四：交换工资（难度：简单）

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。

CREATE TABLE IF NOT EXISTS salary(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(5) NOT NULL,
ses VARCHAR(5) NOT NULL,
salary INT NOT NULL);

INSERT INTO salary VALUES (1, 'A', 'm', 2500);
INSERT INTO salary VALUES (2, 'B', 'f', 1500);
INSERT INTO salary VALUES (3, 'C', 'm', 5500);
INSERT INTO salary VALUES (4, 'D', 'f', 500);

ALTER TABLE salary CHANGE ses sex VARCHAR(5);

UPDATE salary 
SET sex = CASE sex
	WHEN 'm' THEN 'f'
	ELSE 'm'
END;

SELECT * FROM salary;

[外链图片转存失败(img-QcX3PT9t-1565449541422)(C:\Users\ranchunfu\AppData\Roaming\Typora\typora-user-images\1565449035474.png)]

项目五：有趣的电影 （难度：简单）

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

±--------±----------±-------------±----------+
|   id    | movie     |  description |  rating   |
±--------±----------±-------------±----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
±--------±----------±-------------±----------+

SELECT * FROM cinema
WHERE description != 'boring' and MOD(id,2) = 1
ORDER BY rating DESC;

[外链图片转存失败(img-JKxIL4FR-1565449541423)(C:\Users\ranchunfu\AppData\Roaming\Typora\typora-user-images\1565449194732.png)]

项目六：组合两张表 （难度：简单）

在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键

表2: Address
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

SELECT FirstName, LastName, City, State FROM Person
JOIN Address
ON Person.Personld = Address.Personld;

[外链图片转存失败(img-FY5ZBf7b-1565449541424)(C:\Users\ranchunfu\AppData\Roaming\Typora\typora-user-images\1565449251295.png)]

项目七：删除重复的邮箱（难度：简单）

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 **Id ***最小 *的那个。
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
Id 是这个表的主键。

CREATE TABLE IF NOT EXISTS email(
Id   INT NOT NULL PRIMARY KEY,
Email  VARCHAR(30) NOT NULL
);

INSERT INTO email VALUES (1, 'a@b.com');
INSERT INTO email VALUES (2, 'c@d.com');
INSERT INTO email VALUES (3, 'a@b.com');

DELETE E1 FROM email e1, email e2
WHERE e1.Email = e2.Email
			AND e1.Id > e2.Id

SELECT * FROM email;

[外链图片转存失败(img-azuirC9k-1565449541425)(C:\Users\ranchunfu\AppData\Roaming\Typora\typora-user-images\1565449320163.png)]

项目八：从不订购的客户 （难度：简单）

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

±—±------+
| Id | Name  |
±—±------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
±—±------+
Orders 表：

±—±-----------+
| Id | CustomerId |
±—±-----------+
| 1  | 3          |
| 2  | 1          |
±—±-----------+

SELECT Name AS Customers FROM Customers 
WHERE Customers.Id NOT IN (
			SELECT CustomerId FROM Orders 
);

SELECT Name AS Customers FROM Customers LEFT JOIN Orders 
ON customers.Id = Orders.CustomerId
WHERE Orders.Id IS NULL

[外链图片转存失败(img-Quw0PQQT-1565449541425)(C:\Users\ranchunfu\AppData\Roaming\Typora\typora-user-images\1565449383457.png)]

项目九：超过经理收入的员工（难度：简单）

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

±—±------±-------±----------+
| Id | Name  | Salary | ManagerId |
±—±------±-------±----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
±—±------±-------±----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

SELECT
	e1.NAME AS Employee 
FROM
	Employee e1
	JOIN Employee e2 
	ON e1.ManagerId = e2.ID 
	AND e1.Salary > e2.Salary

[外链图片转存失败(img-peiv49II-1565449541427)(C:\Users\ranchunfu\AppData\Roaming\Typora\typora-user-images\1565449497173.png)]