sql练习1708
给定表Employee(Id, Salary)，查第二高工资，如不存在，返回NULL。
select (select distinct Salary from Employee order by Salary desc limit 1, 1)
as SecondHighestSalary;
给出表Employee(Id, Name, Salary, DepartmentID)和表Department(Id, Name)，查各部门工资前三名信息。
select d.Name Department, e1.Name Employee, e1.Salary from Employee e1
join Department d on d.Id = e1.DepartmentId
where 3 > (
select count(distinct(e2.Salary)) from Employee e2
where e2.DepartmentId = e1.DepartmentId and e2.Salary > e1.Salary
)
给出表Scores(Id, Score)，查询所有分数，并给出其对应的排名，要求相同的分数排名相同，名次连续。
select Score, (select count(distinct Score) from Scores where Score >= s.Score) as Rank
from Scores s order by Score desc;
给出表salary(id, name, sex, salary)，将表中sex字段值反转。
update salary set sex = (case sex when 'm' then 'f' else 'm' end);
给出表Logs(Id, Num)，查出连续出现至少3次的数。
select distinct t1.Num as ConsecutiveNums from Logs t1, Logs t2, Logs t3
where t1.Id + 1 = t2.Id and t2.Id + 1 = t3.Id and t1.Num = t2.Num and t2.Num = t3.Num;
给出表courses(student, class)，查询选修学生数不少于5个的所有课程，注意表中可能存在重复记录。
select class from courses group by class having count(distinct student) >= 5;
给出表stadium(id, date, people)，查出连续至少3天人流数超过100的记录。
select s1.* from stadium s1, stadium s2, stadium s3
where s1.people >= 100 and s2.people >= 100 and s3.people >= 100
and ((s1.id + 1 = s2.id and s2.id + 1 = s3.id)
or (s2.id + 1 = s1.id and s1.id + 1 = s3.id)
or (s2.id + 1 = s3.id and s3.id + 1 = s1.id))
group by s1.id order by s1.id;
给出表Employee(Id, Name, Salary, ManagerId)，查出薪资比上级高的员工姓名。
select e1.Name as Employee from Employee e1, Employee e2
where e1.ManagerId = e2.Id and e1.Salary > e2.Salary;
给出表Person(Id, Email)，查询出现过多次的邮箱地址。
select Email from Person group by Email having count(Id) > 1;
给出表Person(Id, Email)，删除出现多次邮箱地址的记录，只保留Id最小的一条。
delete p1 from Person p1, Person p2
where p1.Email = p2.Email and p1.Id > p2.Id;
delete from Person where Id not in (
select t.id from (
select min(Id) as id from Person group by Email
) t
);
给出表Customers(Id, Name)和Orders(Id, CustomerId)，查询没有购买过任何物品的客户姓名。
select Name as Customers from Customers c
left join Orders o on o.CustomerId = c.Id
where o.Id is null;
给出表Weather(Id, Date, Temperature)，查出气温比前一天更高的记录的Id。
select w2.Id from Weather w1, Weather w2
where to_days(w1.date) + 1 = to_days(w2.date) and w1.Temperature < w2.Temperature;