SQL06 秋招秘籍(11.28-11.29)

秋招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;