MySQL（四）项目实战

这是本次课程的最后一次作业了。

作业七
题目：
MySQL（四）项目实战
code：

	--创建表
	CREATE TABLE Employee(
	Id INT NOT NULL PRIMARY KEY,
	Name VARCHAR(50) NOT NULL,
	Salary INT NOT NULL,
	Departmentld INT
	);

	INSERT INTO Employee (Id,Name,Salary,Departmentld) 
	VALUES (1,'Joe',70000,1),
				(2,'Herry',80000,2),
				(3,'Sam',60000,2),
				(4,'Max',90000,1);
				
	CREATE TABLE Department(
		Id INT NOT NULL PRIMARY KEY,
		Name VARCHAR(50) NOT NULL);
		
	INSERT INTO Department VALUES
		(1,'IT'),
		(2,'Sales');

	SELECT D.name AS department,E.name,E.salary
	FROM Employee AS E 
	JOIN Department AS D
	ON E.department_id = D.id
	WHERE salary IN(
	SELECT MAX(salary)
	FROM Employee
	GROUP BY department_id);

作业八
题目：
MySQL（四）项目实战
code:

	CREATE TABLE seat(
	id INT NOT NULL PRIMARY KEY,
	student CHAR(10)
	);

	INSERT INTO seat
	VALUES
	(1, 'Abbot'),
	(2, 'Doris'),
	(3, 'Emerson'),
	(4, 'Green'),
	(5, 'Jeams');


	SELECT
	(CASE
		WHEN id%2=1 AND id!=(SELECT MAX(id) FROM seat) THEN id+1
		WHEN id%2=0 THEN id-1
		ELSE id
	END)id,student
	FROM seat
	ORDER BY id;

作业九
题目：
MySQL（四）项目实战
code:

	CREATE TABLE Scores(
	id INT NOT NULL PRIMARY KEY,
	score FLOAT);

	INSERT INTO Scores
	VALUES 
	(1, 3.50),
	(2, 3.65),
	(3, 4.00),
	(4, 3.85),
	(5, 4.00),
	(6, 3.65);

	SELECT s.score, 
	(SELECT count(DISTINCT score) 
	FROM Scores 
	WHERE score>=s.score) AS `Rank` 
	FROM Scores AS s 
	ORDER BY score DESC;

作业十
题目:
MySQL（四）项目实战

MySQL（四）项目实战

暂时还没想出来，等明天我研究一下其他同学的。

作业十一:
题目：
MySQL（四）项目实战

code：

	--插入数据
	INSERT INTO Employee
	VALUES(5, 'Janet', 69000, 1),(6, 'Randy', 85000, 1);

	--查询
	SELECT d.Name Department, e1.Name Employee, e1.Salary Salary
	FROM Employee AS e1
	JOIN Department AS d
	ON e1.department_id = d.Id
	WHERE 3 > (
           SELECT COUNT(DISTINCT e2.Salary) 
           FROM Employee e2
           WHERE e2.Salary > e1.Salary AND e1.department_id = e2.department_id
          )
	ORDER BY d.Name, e1.Salary DESC;

作业十二：
题目：
MySQL（四）项目实战
code：

SELECT s1.Score,
	(SELECT COUNT(s2.Score)+1 
	FROM Scores AS s2 
	WHERE s2.Score > s1.Score) AS `Rank`
FROM Scores AS s1
ORDER BY s1.Score DESC;

这两天事情确实多，最后一次作业很马马虎虎的对付了，抱歉，我会抽个时间重新修改一下。

MySQL（四）项目实战

相关推荐