select ifnull(
(select DISTINCT salary from emplyee order by salary desc limit 1,1),null) as salary
select name from Customers c where id not in (select CustomerId from Orders)
select name as Customers from Customers c LEFT JOIN Orders o on c.id=o.CustomerId where o.CustomerId is null
select name from Customers c where not EXISTS (select CustomerId from Orders o where c.id=o.CustomerId)
select Email from
(select Email,count(Email) num from Person GROUP BY Email) p where p.num>1
4.Employees Earning More Than Their Managers
select e.name from Employee e INNER JOIN Employee f where e.ManagerId=f.Id and e.salary>f.salary
select p.FirstName,p.LastName,a.City,a.State from Person p left JOIN Address a on p.PersonId=a.PersonId
select t.Department,Employee.name Employee,t.salary salary from
(select p.did did,p.dep_name Department,max(p.salary) salary from
(select e.DepartmentID did,d.name dep_name,e.name emp_name,e.salary salary from Employee e,Department d where e.DepartmentId=d.Id) p
GROUP BY p.dep_name)t left JOIN Employee on t.salary=Employee.salary and t.did=Employee.DepartmentId
select DISTINCT num from
(select Num ,
case
when @pre=Num then @count:=@count+1
when (@pre:=Num) is not null then @count:=1
end n
from logs, (select @count := 0) r
order by Id
) a where n>=3
select Score,
case
when @pre=Score then @rank:=@rank
when (@pre:=Score) is not null then @rank:=@rank+1
end Rank
from Scores ,(select @rank:=0,@pre:=-1)r order by Score desc
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select distinct(salary) from Employee order by Salary desc limit N,1
);
END
10.Department Top Three Salaries
select d.Name Department,e.Name Employee,e.salary Salary from Department d,Employee e where e.DepartmentId=d.Id and
(select count(distinct(e1.salary)) from Employee e1 where e1.salary>e.salary and e1.DepartmentId=e.DepartmentId)<3
数据库高级部分:
索引
触发器
函数
游标
存储过程
存储引擎
事务
备份
恢复