emp

-- 多表连接

-- 1、返回员工和所属经理的姓名。 
select e.ENAME '打工仔' ,man.ENAME '经理'
  from emp e, emp man
 where e.DEPTNO = man.DEPTNO
   and e.JOB != 'MANAGER'
   and man.JOB = 'MANAGER';

-- 2、返回工资水平多于平均工资AVG的员工。 
select *
  from emp
 where SAL > (select avg(SAL) from emp);

dept

划重点

-- 查询各部门中薪资最低和最高的数据，包括的字段有部门编号、部门名、员工名称、最高薪资、最低薪资
select d.DNAME, d.DEPTNO, x.max, x.min, e_max.ENAME '最高薪资者', e_min.ENAME '最低薪资者'
  from dept d, emp e_max, emp e_min,(select DEPTNO, MAX(SAL) max,MIN(SAL) min from emp group by DEPTNO) x
 where d.DEPTNO = x.DEPTNO
   and (e_max.DEPTNO = x.DEPTNO and e_max.SAL = x.max)
   and (e_min.DEPTNO = x.DEPTNO and e_min.SAL = x.min);

-- 上机练习3 查询所有用户的信息（包括部门名）并按ACCOUNTING，SALES，OPERATIONS，RESEARCH排序

  select e.*,d.DNAME
    from emp e inner join dept d
      on e.DEPTNO = d.DEPTNO
order by FIELD(DNAME,'ACCOUNTING','SALES','OPERATIONS','RESEARCH');

movies

划重点

-- (2) Find the studios which had produced more than two films.

  select studioName,count(*) as moviesCount
    from movies 
group by studioName
  having moviesCount >= 2;

-- (5) Which studio has the largest number of films.

  select studioName,count(movies.title) as moviesCount
    from movies 
group by studioName
  having moviesCount >=  ALL(select count(*) from movies group by studioName);