SQL第六次打卡-课程；练习

课程过的真快，不过也是学的快忘的也快，这次作业练习花费了半天多的时间，跟多题目都不会，相当于重新学习了一遍，还有参考对比别人的人答案，断断续续的半天多时间总算是完成了

练习一

CREATE TABLE employee(

Id VARCHAR(100) NOT NULL,

Name VARCHAR(100) NOT NULL,

Salary INTEGER NOT NULL,

DepartmentId VARCHAR(100) NOT NULL

);

INSERT INTO employee VALUES ('1', 'Joe', 70000, '1');

INSERT INTO employee VALUES ('1', 'Henry', 80000, '2');

INSERT INTO employee VALUES ('1', 'Sam', 60000, '2');

INSERT INTO employee VALUES ('1', 'Max', 90000, '1');

CREATE TABLE department(

Id VARCHAR(100) NOT NULL,

Name VARCHAR(100) NOT NULL

);

INSERT INTO department VALUES ('1', 'IT');

INSERT INTO department VALUES ('2', 'Sales');

SELECT

d.Name, Name, max_salary

FROM

employee

JOIN

department d

ON employee.Id = d.Id

JOIN

(SELECT Id, MAX(salary) as max_salary

FROM employee

GROUP BY Id) temp

ON temp.Id = d.Id

WHERE

employee.salary = max_salary

ORDER BY

max_salary DESC;

练习二

(SELECT s1.seat_id,

CASE WHEN s2.student_name IS NULL THEN s1.student_name

ELSE s2.student_name

END AS “student”

FROM seat s1

LEFT JOIN seat s2

ON s1.seat_id = s2.seat_id-1

WHERE s1.seat_id % 2 = 1)

UNION

(SELECT s1.seat_id, s2.student_name

FROM seat s1

LEFT JOIN seat s2

ON s1.seat_id = s2.seat_id+1

WHERE s1.seat_id%2=0)

ORDER BY seat_id;

练习三

SELECT FORMAT(score,2),

DENSE_RANK() OVER(ORDER BY score DESC) AS “Rank”

FROM scores;

练习四

SELECT

DISTINCT l1.num as ConsecutiveNums

FROM

logs l1

LEFT JOIN logs l2 ON l1.id = l2.id-1

LEFT JOIN logs l3 ON l2.id = l3.id-1

WHERE

l1.num=l2.num AND l2.num=l3.num

练习五-不会

练习六

SELECT

name

FROM

employee

JOIN

(SELECT managerID,

COUNT(DISTINCT id) as cnt

FROM

employee

GROUP BY

managerID

HAVING

cnt > 4) temp

ON id = temp.managerID;

练习七-不会

练习八

SELECT department_name, emplyee_name, salary

FROM department

JOIN(

SELECT emplyee_name,

department_id,

salary,

DENSE_RANK() over (PARTITION BY department_id

ORDER BY salary DESC) as “rank”

FROM employee) salary_rank

ON department.department_id=salary_rank.department_id

WHERE salary_rank.rank<4;

练习九

SELECT MIN(ROUND(SQRT((POW(p1.x-p2.x, 2)+POW(p1.y-p2.y, 2))), 2)) AS shortest

FROM point_2d as p1,

point_2d as p2

WHERE p1.x > p2.x OR p1.y > p2.y;

练习十-不会