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