秋招A
练习一:
1、
CREATE TABLE Employee
(Id INTEGER NOT NULL,
Name VARCHAR(100) NOT NULL,
Salary INTEGER NOT NULL,
DepartmentId INTEGER NOT NULL,
PRIMARY KEY(Id)
);
INSERT INTO Employee VALUES('1', 'Joe', '70000', '1');
INSERT INTO Employee VALUES('2', 'Henry', '80000', '2');
INSERT INTO Employee VALUES('3', 'Sam', '60000', '2');
INSERT INTO Employee VALUES('4', 'Max', '90000', '1');
2、
CREATE TABLE Department(
Id VARCHAR(100) NOT NULL,
Name VARCHAR(100) NOT NULL,
PRIMARY KEY(Id)
);
INSERT INTO Department VALUES('1', 'IT');
INSERT INTO Department VALUES('2', 'Sales');
3、
SELECT Department.name AS Dep,
employee.name AS Emp,
Salary
FROM employee
LEFT JOIN department
ON employee.DepartmentId = department.Id
WHERE (employee.DepartmentId, Salary) in
(SELECT DepartmentId, max(Salary)
FROM employee
GROUP BY DepartmentId);
练习2:
1、
CREATE TABLE seat
(id VARCHAR(100) NOT NULL,
student VARCHAR(100) NOT NULL,
PRIMARY KEY(id)
);
# 插入数据
INSERT INTO seat VALUES('1', 'Abbot');
INSERT INTO seat VALUES('2', 'Doris');
INSERT INTO seat VALUES('3', 'Emerson');
INSERT INTO seat VALUES('4', 'Greeen');
INSERT INTO seat VALUES('5', 'Jeames');
2、
SELECT
IF(id%2 = 0,
id - 1,
IF(id = (select COUNT(distinct id) from seat),
id,
id + 1))
AS id, student
FROM seat
ORDER BY id;
练习6
SELECT product_type, product_name, sale_price
FROM product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);
练习7
SELECT question_id FROM survey_log GROUP BY question_id ORDER BY COUNT(answer_id) / COUNT(IF(action = 'show', 1, 0)) DESC LIMIT 1;
练习8
SELECT D1.Name Department, E1.Name Employee, E1.Salary
FROM Employee E1, Employee E2, Department D1
WHERE E1.DepartmentID = E2.DepartmentID
AND E2.Salary >= E1.Salary
AND E1.DepartmentID = D1.ID
GROUP BY E1.Name
HAVING COUNT(DISTINCT E2.Salary) <= 3
ORDER BY D1.Name, E1.Salary DESC;
练习9
SELECT p1.x, p1.y, p2.x, p2.y,
round(min(sqrt(power(p1.x - p2.x, 2) + power(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
ORDER BY shortest;