数据库上机的所有题答案

【boat-sailor】
1. 找出预订了103船的水手的名字
SELECT distinct sname
FROM saliors
WHERE sid IN(select sid from reserves where bid='103')

Dustin
Lubber
Horatio

2. 找出预订了红色船的所有水手的名字
SELECT distinct sname
FROM saliors
WHERE sid IN(select sid from reserves where bid in
(select bid from boats where color='red'))

Dustin
Lubber
Horatio

3. 找出Lubber预定的船的所有颜色
select distinct color
from boats
where bid in(select bid from reserves where sid in
(select sid from saliors where sname='Lubber'))

Red
Green
Red

4. 找出至少预订了一艘船的水手的名字
select distinct sname
from saliors
where exists(select sid from reserves)

Andy
Art
Bob
Brutus
Dustin
Horatio
Lubber
Rusty
Zorba

5. 找出预订了一艘红色船或者绿色船的水手名
select distinct sname
from saliors
where sid in(select sid from reserves where bid in
(select bid from boats where color='red' or color='green'))

Dustin
Horatio
Lubber

6. 找出同时预订了红色船和绿色船的水手名
select distinct sname
from saliors
where sid in(select sid from reserves where bid in
(select bid from boats where color='red'))
intersect
select distinct sname
from saliors
where sid in(select sid from reserves where bid in
(select bid from boats where color='green'))

Dustin
Horatio
Lubber

7. 找出预定了所有船的水手们的名字
select distinct sname
from saliors
where not exists(select * from boats where not exists
(select * from reserves where sid=saliors.sid and bid=boats.bid))

Dustin

8. 找出预定了所有名为Interlake船的水手的名字
select distinct sname
from saliors
where not exists(select * from boats where bname='Interlake'and not exists
(select * from reserves where sid=saliors.sid and bid=boats.bid))

Dustin
Horatio

9. 找出最老的水手的名字和年龄
select distinct sname,age
from saliors
where age>=ALL(select age from saliors)

Bob 63.5

10. 找出比等级为10的最老的水手的年龄还要大的水手的名字
select distinct sname
from saliors
where age>ALL(select age from saliors where rating='10')

Bob
Dustin
Lubber

11. 对于每个等级级别找出最年轻的水手的年龄
select min(age)
from saliors
group by rating

33.0
25.5
35.0
25.5
35.0
16.0



12. 对于至少有两个水手的等级级别，求出水手们的平均年龄
select avg(age)
from saliors
group by rating
having count(*)>=2

25.5
40.0
40.5
44.5

13. 对于至少有两个水手的等级，找出到了投票年龄（>18岁）的所有水手的平均年龄
select avg(s.age)
from saliors s
where s.age>18
group by s.rating
having 1<(select count(*)
from saliors s2
where s.rating=s2.rating)

44.5
40
40.5
35


14. 找出在所有的等级中水手的平均年龄是最低的那些等级
select s.rating
from saliors s
group by s.rating
having avg(s.ag

e)<=all(select avg(s2.age)
from saliors s2
group by s2.rating)

10


【work】
1. 列出公司全部职工的信息
select *
from emp
7369 SMITH 7902 CLERK 1980-11-17 00:00:00.000 NULL 800 20
7499 ALLEN 7698 SALESMAN 1981-02-20 00:00:00.000 500 1600 30
7521 WORD 7698 SALESMAN 1981-02-22 00:00:00.000 500 1250 30
7566 JONES 7839 MANAGER 1981-04-02 00:00:00.000 NULL 2975 20
7654 MARTIN 7698 SALESMAN 1981-09-28 00:00:00.000 1400 1250 30
7698 BLACK 7839 MANAGER 1981-05-01 00:00:00.000 NULL 1850 30
7782 CLARK 7839 MANAGER 1981-06-09 00:00:00.000 NULL 2450 10
7788 SCOTT 7566 ANALYST 1981-12-09 00:00:00.000 NULL 3000 20
7839 KING NULL PRESIDENT 1981-12-17 00:00:00.000 NULL 5000 10
7844 TURNER 7698 SALESMAN 1981-09-08 00:00:00.000 0 1500 30
7876 ADAMS 7788 CLERK 1981-09-23 00:00:00.000 NULL 1100 20
7900 JAMES 7698 CLERK 1981-11-03 00:00:00.000 NULL 950 30
7902 FORD 7566 ANALYST 1981-11-03 00:00:00.000 NULL 3000 20
7934 MILLER 7788 CLERK 1982-01-23 00:00:00.000 NULL 1300 10

2. 列出公司总全部职工的姓名
select ename
from emp

SMITH
ALLEN
WORD
JONES
MARTIN
BLACK
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

3. 列出10号部门中全体职工的姓名及职工号
select ename,empno
from emp
where deptno=10

CLARK 7782
KING 7839
MILLER 7934

4. 列出10号部门中全体职工的姓名及其工作情况
select ename,job
from emp
where deptno=10

CLARK MANAGER
KING PRESIDENT
MILLER CLERK

5. 求工资额在2000，3000之间的职员情况（姓名，工种，工资额）
select ename,job,sal
from emp
where sal<3000 and sal>2000

JONES MANAGER 2975
CLARK MANAGER 2450

6. 列出公司中所有名字以LI开头的全部职工及其所在的部门代号
select ename,deptno
from emp
where ename like 'LI%'

(结果为空)

7. 查询20号部门的不是服务员的雇员姓名和工种情况
select ename,job
from emp
where job!='clerk' and deptno=20

JONES MANAGER
SCOTT ANALYST
FORD ANALYST

8. 查询20号部门或者不是服务员的雇员的情况
select *
from emp
where job!='server' or deptno=20

7369 SMITH 7902 CLERK 1980-11-17 00:00:00.000 NULL 800 20
7499 ALLEN 7698 SALESMAN 1981-02-20 00:00:00.000 500 1600 30
7521 WORD 7698 SALESMAN 1981-02-22 00:00:00.000 500 1250 30
7566 JONES 7839 MANAGER 1981-04-02

00:00:00.000 NULL 2975 20
7654 MARTIN 7698 SALESMAN 1981-09-28 00:00:00.000 1400 1250 30
7698 BLACK 7839 MANAGER 1981-05-01 00:00:00.000 NULL 1850 30
7782 CLARK 7839 MANAGER 1981-06-09 00:00:00.000 NULL 2450 10
7788 SCOTT 7566 ANALYST 1981-12-09 00:00:00.000 NULL 3000 20
7839 KING NULL PRESIDENT 1981-12-17 00:00:00.000 NULL 5000 10
7844 TURNER 7698 SALESMAN 1981-09-08 00:00:00.000 0 1500 30
7876 ADAMS 7788 CLERK 1981-09-23 00:00:00.000 NULL 1100 20
7900 JAMES 7698 CLERK 1981-11-03 00:00:00.000 NULL 950 30
7902 FORD 7566 ANALYST 1981-11-03 00:00:00.000 NULL 3000 20
7934 MILLER 7788 CLERK 1982-01-23 00:00:00.000 NULL 1300 10

9. 查询获得的佣金超过他本人基础工资的5%的职工，并按百分比的高低排序显示
select ename,comm,sal,comm*1.00/sal
from emp
where comm>sal*0.05
order by comm*1.00/sal desc

MARTIN 1400 1250 1.1200000000000
WORD 500 1250 0.4000000000000
ALLEN 500 1600 0.3125000000000

10. 列出公司中不能给奖金的雇员姓名及其工种情况
select ename,job
from emp
where comm is null or comm=0

SMITH CLERK
JONES MANAGER
BLACK MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK

11. 要安排Wilson做销售员，同时工资改为2000，佣金增加50%
update emp
set sal=2000,comm=comm*1.5,job='SALESMAN'
where ename='Wilson'

12. 给20号部门中所有分析员和经理提高工资15%
update emp
set sal=sal*1.15
where deptno='20' and job in(select job
from emp
where job='manager' or job='analyst')


13. 给在NEW YORK工作的雇员提高工资５％
update emp
set sal=sal*1.05
where deptno in(select deptno from dept where city='NEW YORK')


14. 为公司中工龄超过10年的职工提升一级工资（10元）
update emp
set sal=sal+10
where datediff(year,hiredate,getdate())>10


15. 找出所有在1956年1月4日到4月15日之间雇佣的雇员的日期
select hiredate
from emp
where hiredate between '1956-1-4' and '1956-4-15'



16. 计算部门30中雇员的日工资（每月按22个工作日计算），并按不舍入，舍入到美元，舍入到美分三个不同的结果显示。
select sal*1.0/22,sal/22,sal/22*1.0
from emp
where deptno='30'

72.727272 72 72.0
56.818181 56 56.0
56.818181 56 56.0
84.090909 84 84.0
68.181818 68 68.0
43.181818 43 43.0


17. 30号部门中每一个职工领取的总薪金是多少？
select sal+comm
from emp
where deptno='30' and comm is not null


18. 计算公司支付给每一种工种的总工资
select (
case
when c

omm is null then sal
else
sal+comm
end)
from emp
where deptno='30'

2100
1750
2650
1850
1500
950

19. 计算每个部门的总工资及平均工资，最高工资和最低工资
select sum(sal),avg(sal),min(sal),max(sal)
from emp
group by deptno

8750 2916 1300 5000
10875 2175 800 3000
8400 1400 950 1850

20. 计算每个部门中每种工种都有多少职工
select deptno,job,count(*)
from emp
group by deptno,job

20 ANALYST 2
10 CLERK 1
20 CLERK 2
30 CLERK 1
10 MANAGER 1
20 MANAGER 1
30 MANAGER 1
10 PRESIDENT 1
30 SALESMAN 4

21. 查询那些部门总工资额超过9000

select deptno
from emp
group by deptno
having sum(sal)>9000

20


22. 在ACCOUNTING部门中有哪些职业是SALES部门中所没有的
select job
from emp a,dept b
where dname='ACCOUNTING' and a.deptno=b.deptno and a.job not in
(select job
from emp,dept
where dname='SALES' and emp.deptno=dept.deptno
)

PRESIDENT


23. 谁和Smith在同一部门中工作
select ename
from emp
where deptno=(select deptno from emp where ename='Smith')

SMITH
JONES
SCOTT
ADAMS
FORD

24. 找出其他部门中比部门30中某一雇员工资高的雇员
select ename
from emp
where deptno!=30 and sal>any(select sal from emp where deptno=30)

JONES
CLARK
SCOTT
KING
ADAMS
FORD
MILLER


25. 找出其他部门中比30号部门全体员工的工资都高的雇员
select ename
from emp
where deptno!=30 and sal>all(select sal from emp where deptno=30)

JONES
CLARK
SCOTT
KING
FORD

26. 查找与clark工种相同或者工资比clark高的职员
select ename
from emp
where sal>(select sal from emp where ename='clark')
or job=(select job from emp where ename='clark')

JONES
BLACK
CLARK
SCOTT
KING
FORD


27. 找出所有其工资高于所在部门平均工资的雇员
select ename,job,deptno,sal,comm,t.averange
from emp,(select avg(case when comm is null then sal
else sal+comm end) averange
from emp
group by deptno) t
where t.averange<(case when comm is null then sal
else sal+comm end)

JONES MANAGER 20 2975 NULL 2916
SCOTT ANALYST 20 3000 NULL 2916
KING PRESIDENT 10 5000 NULL 2916
FORD ANALYST 20 3000 NULL 2916
JONES MANAGER 20 2975 NULL 2175
MARTIN SALESMAN 30 1250 1400 2175
CLARK MANAGER 10 2450 NULL 2175
SCOTT ANALYST 20 3000 NULL 2175
KING PRESIDENT 10 5000 NULL 2175
FORD ANALYST 20 3000 NULL 2175
ALLEN SALESMAN 30 1600 500 1800
JONES MANAGER 20 2975 NULL 1800
MARTIN SALESMAN 30 1250 1400 1800
BLACK MANAGER 30

1850 NULL 1800
CLARK MANAGER 10 2450 NULL 1800
SCOTT ANALYST 20 3000 NULL 1800
KING PRESIDENT 10 5000 NULL 1800
FORD ANALYST 20 3000 NULL 1800


【S-P-J】
1 求供应工程J1零件的供应商号码SNO
select sno
from spj
where jno='j1'

S1
S2
S2


2 求供应工程J1零件P1的供应商号码SNO
select sno
from spj
where jno='j1' and pno='p1'

S2

3 求供应工程J1零件为红色的供应商号码SNO
select sno
from spj,p
where jno='j1' and color='红' and spj.pno=p.pno

S2

4 求没有使用天津供应商生产的红色零件的工程号JNO
select jno
from spj
except (select jno
from spj,s,p
where s.city='天津'and color='红'
and spj.pno=p.pno and spj.sno=s.sno
)

J1
J2

5 找出所有供应商的姓名和所在城市
select sname,city
from s

敬意 天津
为民 上海


6 找出所有零件的名称，颜色，重量(无效)
select pname,color,weight
from p
罗斯 红
铁钉 率
NULL HA

7 找出使用供应商S1所供应零件的工程代码
select jno
from j
where jno in (select jno from spj where sno = 'S1');

J1
J2
J3

8 找出工程项目J2使用的各种零件的名称及其数量
select pname,qty
from spj,p
where jno='J2' and spj.pno=p.pno

铁钉 NULL
罗斯 NULL

9 找出上海厂商供应的所有零件号码
select p.pno
from spj,p,s
where sname='上海' and p.pno=spj.pno and spj.sno=s.sno



10 找出使用上海产的零件的工程项目
select j.jno,jname,j.city
from s,j,spj
where sname='上海' and spj.sno=s.sno and spj.jno=j.jno



11 找出没有使用天津产的零件的工程号码
select jno
from spj
except (select jno
from spj,s,p
where s.city='天津'
and spj.pno=p.pno and spj.sno=s.sno
)