emp

-- 多表連接

-- 1、返回員工和所屬經(jīng)理的姓名。 
select e.ENAME '打工仔' ,man.ENAME '經(jīng)理'
  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

劃重點

-- 查詢各部門中薪資最低和最高的數(shù)據(jù)，包括的字段有部門編號、部門名、員工名稱、最高薪資、最低薪資
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);

-- 上機(jī)練習(xí)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);