【面试必备】领扣SQL题解

select name, population, area

from world

where area > 3000000 or population > 25000000;

---

 

update  salary

set sex = char(ASCII(sex) ^ ASCII('m') ^ ASCII('f'));

 

---

 

select * from cinema

where id % 2 = 1 and description != 'boring'

order by rating desc;

---

 

 

select class from courses

group by class

having count(distinct student) >= 5;

---

 

 

select Email from Person

group by Email

having count(distinct Id) >= 2;

---

 

 

delete p1

from Person p1, Person p2

where p1.Email = p2.Email and p1.Id > p2.Id

---

 

 

select p.FirstName, p.LastName, a.City, a.State

from Person p left join Address a on p.PersonId = a.PersonId;

---

 

 

SELECT b.Name AS Employee
FROM Employee a INNER JOIN Employee b
ON b.ManagerId IS NOT NULL AND b.ManagerId=a.Id AND b.Salary >a.Salary;

---

 

 

select b.Name as Customers from Customers b left join Orders a on b.Id = a.CustomerId where a.CustomerId is null; 

---

 

 

select d.Name as Department, e.Name as Employee, e.Salary from Department d, Employee e

(select DepartmentId, MAX(Salary) Salary from Employee group by DepartmentId) M

where e.DepartmentId = d.Id and e.DepartmentId = M.DepartmentId and e.Salary = M.Salary;

 

---

 

 

select

(select distinct Salary from Employee order by Salary desc limit 1,1)

SecondHighestSalary;

---

 

 

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N -1;
  RETURN (
      # Write your MySQL query statement below.      
      select 
      (Select distinct Salary from Employee order by Salary desc limit N,1)
  );
END

---

 

 

 

---

 

---