1. 给定Weather表，编写SQL查询找出所有温度较前一天高的记录Id
+———+————+——————+ | Id(INT) | Date(DATE) | Temperature(INT) | +———+————+——————+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +———+————+——————+ sql: select w1.id from Weather as w1 join Weather as w2 on TO_DAYS(w1.Date) = TO_DAYS(w2.Date) + 1 and w1.Temperature > w2.Temperature —
2. Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id
+—-+——————+ | Id | Email | +—-+——————+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +—-+——————+ sql: delete p1 from Person as p1 join Person p2 on p1.Email = p2.Email and p1.Id > p2.Id; sql: delete p1 from Person as p1 where p1.Id not in (select min(p2.Id) from Person as p2 group by p2.Email) —
3.The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
+—-+——-+——–+————–+ | Id | Name | Salary | DepartmentId | +—-+——-+——–+————–+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | +—-+——-+——–+————–+
sql:select d.Name as Department, c.Name as Employee,c.Salary from Department as d join select a.* from Employee as a where (select count(distinct b.Salary) from Employee as b where a.DepartmentId=b.DepartmentId and a.Salary <= b.Salary) <=3) as c on d.Id=c.DepartmentId order by d.Name,c.Salary desc;
The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
sql:select d.Name as Department, e.Name as Employee, e.Salary from Department as d join (select * from Employee as a where a.Salary >= (select max(b.Salary) from Employee as b where a.DepartmentId=b.DepartmentId) order by a.DepartmentId) as e on d.Id = e.DepartmentId;
4.Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL
query to find all customers who never order anything.
Table: Customers.
+—-+——-+ | Id | Name | +—-+——-+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +—-+——-+
Table: Orders.
+—-+————+ | Id | CustomerId | +—-+————+ | 1 | 3 | | 2 | 1 | +—-+————+ sql:select c.Name from Customers as c left join Orders as o on c.Id = o.CustomerId where o.Id is null; 注意：A left join B on 后面的限制条件只针对于B，另外如果需要过滤join之后的结果只能使用where —
5. Write a SQL query to find all numbers that appear at least three times consecutively.
+—-+—–+ | Id | Num | +—-+—–+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +—-+—–+ sql: select distinct(d.Num) from Logs as d join (select a.Id as Id1, a.Num as Num from Logs as a join Logs as b on a.Num = b.Num and a.Id = b.Id+1) as c on d.Id = c.Id1-2 and d.Num = c.Num; —
6.Write a SQL query to get the nth highest salary from the Employee table.
+—-+——–+ | Id | Salary | +—-+——–+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +—-+——–+ sql: select distinct(a.Salary) from Employee as a join (select distinct Salary from Employee) as b on a.Salary <= b.Salary group by a.Id having count(a.Salary)=N