温故知新（SQL）

1.大于avg(sal)的人名和SAL
select ename,sal from emp where sal >(select avg(sal) from emp)

2.大于部门平均工资的人名和工资
select ename,sal,s from emp,
(select deptno dn ,avg(sal) s from emp group by deptno) temp
 where sal>s and emp.deptno=dn
3.每个部门的人数,部门名
select dept.deptno,count(empno) c from emp,dept
where emp.deptno(+)=dept.deptno  group by dept.deptno

4.每个职位有多少人,每个工种
select job,count(*) from emp group by job
5.工种人数大于2人的工种及他的人数
select job,count(*) from emp group by job having count(*)>2
6.每个员工级他对应的部门名.
select ename,dname from emp,dept where emp.deptno=dept.deptno

7.平均工资最高的部门名及平均工资.
select dname,av from dept,
( select deptno,avg(sal) av from emp group by deptno having avg(sal) =
(select max(avg(sal)) from emp group by deptno )) temp
where dept.deptno=temp.deptno

 

 

1. 编写一个sql语句。要求查询出1998年，船属国家是USA的订单数量
alter session set nls_date_format='yyyy-mm-dd hh:mi:ss'
select count(*) from orders where shipcountry='USA'
 and orderdate >='1998-1-1' and orderdate<'1999-1-1'

2. 编写一个SQL语句。要求显示出员工的名称及其对应的客户企业名称（companyname）（员工表是Employees，顾客表是customers，订单表是orders，订单表跟员工表都有employeeid，客户表跟订单表都有customerid）
select distinct lastname||' '||firstname ,companyname from
 customers c,employees e,orders o
 where o.customerid=c.customerid and o.employeeid=e.employeeid

3. 编写一个sql语句，要求查询每个员工的名称及其对应的最贵的单价
 select lastname||firstname,maxx from employees,
(select employeeid,max(freight) maxx from orders group by employeeid) temp
where employees.employeeid=temp.employeeid

4. 编写一个sql语句。要求显示有订单的员工姓名及其订单数量（员工姓名是lastname+firstname。员工表是Employees,订单表是orders,员工表跟订单表中都有employeeid
select lastname||firstname,cc from employees,
(select employeeid,count(*) cc from orders group by employeeid) temp
where employees.employeeid = temp.employeeid

5. 显示全部员工的姓名及其对应的订单数量（其它同上）
select lastname||firstname,nvl(cc,0) from employees,
(select employeeid,count(*) cc from orders group by employeeid) temp
where employees.employeeid = temp.employeeid(+)

 

6．顾客表（customers表）中电话（phone）以0921开头的企业名称（companyname）
select companyname from customers where phone like '0921%'

 

7．国籍（country）是美国（USA）职位（contacttitle）是Owner或Sales Manager的联系人名称（contactName），要求用两种写法写出
select contactName from customers
where country='USA' and
(contacttitle='Owner' or contacttitle='Sales Manager')

select contactName from customers
where country='USA' and
contacttitle in('Owner','Sales Manager')

 

8.显示年龄最大的3个员工信息（员工表为employees，出生日期为birthdate）
select * from (select * from employees order by birthdate) where rownum <=3
 

9.显示出销售订单最多的船属国家
select shipcountry from orders group by shipcountry having count(*)=
(select max(count(*)) from orders group by shipcountry)

 

 

练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响）
select ename,dname from emp,dept where emp.deptno(+)=dept.deptno

练习二：每个员工所在的部门和部门所在的地区
select ename,dname,loc from emp,dept where emp.deptno=dept.deptno(+)

练习三:查出每个员工和每个员工的工资级别
 select ename,sal,grade from emp ,salgrade where sal>=losal and sal<=hisal
 select ename,sal,grade from emp ,salgrade where sal between losal and hisal
 

练习四:查出所有员工的部门领导的名称：
SELECT ename,e from emp,
 (select deptno,ename e from emp where job='MANAGER') temp
  where emp.deptno=temp.deptno

练习五：找出没有员工的部门：
select deptno ,dname from dept where deptno not in
  (select distinct deptno from emp where deptno is not null)

练习六：查询员工有多少人有提成：
select count(comm) from emp
select count(*) from emp where comm is not null

练习七：员工分部在多少个不同的部门：
select count(distinct deptno) from emp

练习八：求各个部门的平均工资：
select avg(sal) ,deptno from emp group by deptno
select avg(nvl(sal,0)) ,deptno from emp group by deptno

练习九：求各个部门不同工种的平均工资：
select dname,job,avgs from dept ,(select job,deptno,avg(sal) avgs from emp group by deptno,job) temp where dept.deptno=temp.deptno

 

练习十：查询哪些部门的平均工资比2000高:
select dname from dept where deptno
  in (select deptno from emp group by deptno having avg(sal)>2000)

 

练习十一：除了30部门以外的部门的平均工资:
select dname,avgs from dept,
(select deptno,avg(sal) avgs from emp group by deptno  ) temp
where dept.deptno=temp.deptno(+) and dept.deptno!=30

 

练习十三：找出所有员工中，工资最低的那个员工：
select ename ,sal from emp where sal =(select min(sal) from emp)

练习十四：查询谁跟Smith的工种一样：
select ename ,job from emp
where job=(select job from emp where ename='SMITH') AND ename!='SMITH'

练习十五：查出哪些员工的工资比平均工资低：
SELECT ENAME ,SAL FROM EMP WHERE SAL <(SELECT AVG(SAL) FROM EMP)