sql

.大于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)

 

 

 

 

 

 

--有奖金的人数
select count(comm) from emp
--有奖金的人数
select count(*) from emp where comm is not null

select deptno,avg(sal) from emp group by deptno
having avg(sal)>(select avg(sal) from emp)

--显示出部门平均工资高于公司平均工资的部门名称
select dname from dept,(select deptno,avg(sal) from emp group by deptno
having avg(sal)>(select avg(sal) from emp)) temp where dept.deptno=temp.deptno
--显示出部门平均工资高于公司平均工资的部门名称
select dname from dept where deptno in (select deptno from emp group by deptno
having avg(sal)>(select avg(sal) from emp))

--求出工资最高的人
select ename from emp where sal=(select max(sal) from emp)

select nvl(comm,0)+300 from emp

select * from A a,A b,A c

select * from emp where sal >all(select sal from emp where deptno=20)
select * from emp where sal >(select max(sal) from emp where deptno=20)

select * from emp where sal >any(select sal from emp where deptno=20)
select * from emp where sal >(select min(sal) from emp where deptno=20)

select * from emp where sal <all(select sal from emp where deptno=20)
select * from emp where sal <(select min(sal) from emp where deptno=20)

select * from emp where sal <any(select sal from emp where deptno=20)
select * from emp where sal <(select max(sal) from emp where deptno=20)

--有员工的部门有哪些
select dname from dept where
 exists (select * from emp where emp.deptno=dept.deptno)
--有员工的部门有哪些
select dname from dept where deptno in(select distinct deptno from emp)
--有员工的部门有哪些
select dname from dept ,(select distinct deptno from emp) temp
where dept.deptno=temp.deptno

set serveroutput on
declare
myemp emp%rowtype;
begin
select * into myemp from emp where empno='7369';
dbms_output.put_line(myemp.ename);
end;

select abc.nextval from dual

declare
myempno emp.empno%type;
begin
select abc.nextval into myempno from dual;
insert into mytemp (empno,sal,hiredate)  values(myempno,46464,sysdate);
end;

create table mytemp as (select * from emp where 1=2)

declare
myempno emp.empno%type;
myename emp.ename%type;
mysal emp.sal%type;
mysize number(5);
cursor empcur is select empno,ename,sal from emp where deptno=10 order by sal;
begin
select count(*) into mysize from emp where deptno=10;
open empcur;
for i in 1..mysize loop
    fetch empcur into myempno,myename,mysal;
    dbms_output.put_line(myempno||'  '||myename||'  '||mysal);
end loop;
close empcur;
end;

 

 

--哪个员工卖的最多
select lastname||' '||firstname,maxsum from employees,
(select employeeid tid ,sum(freight) maxsum from orders group by employeeid having sum(freight)=
(SELECT max(sum(freight)) from orders group by employeeid)) where employees.employeeid=tid
--哪个顾客买的最多
select companyname,maxsum from customers,
(select customerid cid ,sum(freight) maxsum from orders group by customerid having
sum(freight)=(SELECT max(sum(freight)) from orders group by customerid))
where customers.customerid=cid

 

 

表的种类
业务表：主要用于存储业务逻辑的数据
master：管理单一种类物品的信息

template表：

1.表的连接方式：
a，b           a,c  
a1，b1    a1,c1   
a2,  b2     a3,c3

inner join:
select table1.a,table2.b from table1 inner join table2 on 条件一 and 条件二
例：select table1.a,table2.c from table1 inner join table2 on table1.a = table2.a
      结果为：a1，c1

left join:
select table1.a,table2.b from table1 left join table2 on 条件一 and 条件二
例：select table1.a,table2.c from table1 left join table2 on table1.a = table2.a
结果为：a1，c1
                a2，
select *
from table1, table2
where table1.a = table2.a(+)(oracle写法)

right join:
同left join 不过主表在右

cross join:
a，b           c,d  
a1，b1    c1,d1   
a2,  b2     c2,d2
例：select * from table1 cross join table2
结果为：a1，b1 ，c1, d1
                a1，b1 ，c2, d2
                a2,  b2 ， c1, d1
                a2,  b2 ， c2, d2

常用的注意点:
union 起码保证上下两个表的字段as的别名一样

用max min avg count时候
对选出的其他字段要用group by
（select  a from table1 where） as  a,做个虚拟字段