数据库表中常用的查询实验

淡淡的烟草味﹌ 2023-10-07 12:36 7阅读 0赞

### 实验1 ###

练习1、请查询表DEPT中所有部门的情况。

select \* from dept;

练习2、查询表DEPT中的部门号、部门名称两个字段的所有信息。

select deptno,dname from dept;

练习3、请从表EMP中查询10号部门工作的雇员姓名和工资。

select ename,sal from emp where deptno=10;

练习4、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。

select ename,sal from emp where job=‘CLERK’ or job=‘MANAGER’;

练习5、请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。

select ename,deptno,sal,job from emp where deptno between 10 and 30;

练习6、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。

select ename,sal,job from emp where ename like ‘J%’;

练习7、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。

select ename,job,sal from emp where sal<=2000 order by sal desc;

练习8、请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。

select ename,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and job=’CLERK’;

练习9、查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。

select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+) and a.sal>=2000;

练习10、在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。

select ename,job,sal from emp where sal>(select sal from emp where ename=’JONES’);

练习11、列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。

select ename,job,deptno from emp where deptno not in (select deptno from dept);

练习12、查找工资在1000～3000之间的雇员所在部门的所有人员信息

select \* from emp where deptno in (select distinct deptno from emp where sal between 1000 and 3000);

练习13、雇员中谁的工资最高。

select ename from emp where sal=(select max(sal) from emp);

select ename from (select \* from emp order by sal desc) where rownum<=1;

练习14、雇员中谁的工资第二高（考虑并列第一的情况，如何处理）。

select ename from (select ename ,sal from (select \* from emp order by sal desc) where rownum<=2 order by sal) where rownum<=1;

### 实验2 ###

1．查询所有雇员的姓名、SAL与COMM之和。

select ename,sal+nvl(comm,0) “sal-and-comm” from emp;

2．查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字

select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and hiredate>=to\_date(‘1981-07-01’,’yyyy-mm-dd’);

3．查询各部门中81年1月1日以后来的员工数

select deptno,count(\*) from emp where hiredate>=to\_date(‘1981-01-01’,’yyyy-mm-dd’) group by deptno;

4．查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资

select ename,sal from emp where (job=’MANAGER’ or job=’SALES’) and deptno in (select deptno from dept where loc=’CHICAGO’);

5．查询列出来公司就职时间超过24年的员工名单

select ename from emp where hiredate<=add\_months(sysdate,-288);

6．查询于81年公司所有员工的总收入（SAL和COMM）

select sum(sal+nvl(comm,0)) from emp where to\_char(hiredate,’yyyy’)=’1981’;

7．查询显示每个雇员加入公司的准确时间，按××××年××月××日时分秒显示。

select ename,to\_char(hiredate,‘yyyy-mm-dd hh24:mi:ss’) from emp;

8．查询公司中按年份月份统计各地的录用职工数量

select to\_char(hiredate,‘yyyy-mm’),loc,count(\*) from emp,dept

where emp.deptno=dept.deptno group by to\_char(hiredate,‘yyyy-mm’),loc;

9．查询列出各部门的部门名和部门经理名字

select dname,ename from emp,dept where emp.deptno=dept.deptno and job=’MANAGER’;

10．查询部门平均工资最高的部门名称和最低的部门名称

select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) ) where rownum<=1) union all select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) desc ) where rownum<=1);

11．查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名

select ename,dname from (select ename,deptno from (select ename,deptno from emp where hiredate>(select hiredate from emp where empno=7521) order by hiredate ) where rownum<=1) e,dept where e.deptno=dept.deptno

### 实验3 ###

1.查询显示EMP表各雇员的工作类型，并翻译为中文显示(用decode函数)

select empno,ename,decode(job,‘clerk’,‘职员’,‘MANAGER’,‘经理’,‘ANALYST’,‘工程师’,‘其他’) from my\_emp;

EMPNO ENAME DECODE(JOB,‘CLERK’,‘职员’,'MAN

--------------------

7799 YUAN 职员

7566 JONES 经理

7782 CLARK 经理

7788 SCOTT 工程师

7839 KING 其他

7876 ADAMS 其他

7902 FORD 工程师

7934 MILLER 其他

2301 kkkkk 职员

2.建立一个视图myV\_emp，视图包括myEMP表的empno、ename、sal，并按sal从大到小排列。

create view myV\_emp as select empno,ename,sal from my\_emp order by sal desc;

3.在myEMP表中建立ename的唯一性索引。

create unique index index\_ename on my\_emp(ename);

4.计算EMP表中COMM最高与最低的差值，COMM值为空时按0计算。

SQL> declare

2 masal int;

3 misal int;

4 intersectsal int;

5 begin

6 select max(nvl(sal,0)) into masal from emp ;

7 select min(nvl(sal,0)) into misal from emp;

8 intersectsal:=masal-misal;

9 dbms\_output.put\_line(intersectsal);

10 end;

11 /

3900

PL/SQL procedure successfully completed

5.根据表myEMP中deptno字段的值，为姓名‘JONES’的雇员修改工资；若部门号为10，则工资加100；部门号为20，加200；其他部门加300。

selectdecode(deptno,10,sal+100,20,sal+200,sal+300)from empwhere ename=‘YUAN’;

6.查找部门编号和职位都不同的职员信息

SQL> select distinct empno,job from emp;

EMPNO JOB

--------------------

7566 MANAGER  
7799 clerk  
7788 ANALYST  
7934 CLERK  
7839 PRESIDENT  
7876 CLERK

说明：distinct同时作用于empno，job两个字段；

### 实验4 ###

1.找出emp表中的ename第三个字母是A的员工信息

select \*from empwhere enamelike’\_\_A%’;

2.找出emp表中员工姓名中含有A和的员工姓名

select enamefrom empwhere enamelike’%A%‘and enamelike’%N%’;

3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小排序

select sal+nvl(comm,0)as sc,ename,sal,nvl(comm,0)from emp orderby salasc,scdesc;

4.找出部门号是20的职位类型

select distinct jobfrom emp where deptno=20;

5.显示工资不在1500和2000之间的员工信息：姓名、工资，并按工资从大到小排序

select ename,salfrom empwhere salnot between1500and2000orderby saldesc;

6.把一个职工号所对应的奖学金 ,一个一个读取出来，然后重复的不要

select zgh,to\_char(WMSYS.WM\_CONCAT(distinct(a.jlqk)))

from t\_jxjagroupbya.zgh;

7.列出至少有一个员工的所有部门

select \*from empwhere sal>(select salfrom empwhere ename=‘YUAN’);

8.列出所有员工的姓名及其直接上级的姓名

select e1.ename yuang\_name,e2.ename boss\_name from scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;

9.列出受雇日期早于其直接上级的所有员工

select t1.enamefrom scott.emp t1,scott.emp t2 where t1.hiredate<t2.hiredateand t1.mgr=t2.empno;

10.列出部门名称和这些部门的员工信息，同事列出那些没有员工的部门

select d.deptno,d.dname,d.loc,e.\*from scott.dept d left join scott.empe on d.deptno=e.deptno;

11.列出最低薪资大于1500的各种工作

select job from scott.empe groupbye.jobhavingmin(sal)>1500;

12.列出在部门‘SALES’‘工作的员工的姓名，假定不知道部门编号

selecte.enamefrom scott.empe wheree.deptnoin

(select deptnofrom scott.dept d where d.dname=‘SALES’);

13.列出薪资高于公司平均水平的所有员工

select \*from scott.empe where(e.sal+nvl(e.comm,0))>(selectavg(e1.sal+nvl(e1.comm,0))from scott.emp e1);

14.列出与’SCOTT’'从事相同工作的所有员工

select enamefrom scott.emp e1where e1.job=(selecte.jobfrom scott.empewhere ename=‘SCOTT’);

### 实验5 ###

1.问题描述：

test表中有ID(人员编号),A（考核标准）,B（实际得分）,C（课程编号）四个字段，一个ID可能会有多个科目的评分，如果一个ID中存在A=B，则合格，求合格的人员编号。

2.需求分析：

要得到的结果为：

ID 是否合格

1011 合格

1012 合格

1013 合格

1014 不合格

1015 合格

如果直接用decode（）函数，则会出现同一id有合格和不合格的成绩，错误

故合格产品满足以下两个条件：（1）ID不能重复—》distinct （2）同一id存在A=B

3.解答过程：

（1）查出合格的

selectdistinctid,‘合格’ PJ fromtestwhereidin(selectidfromtestwhere a=b)

(2)查出不合格的

selectdistinctid,‘不合格’ PJ fromtestwhereidnotin(selectidfromtestwhere a=b)

(3)使用union联接

selectdistinctid,‘合格’ PJ fromtestwhereidin(selectidfromtestwhere a=b)union

selectdistinctid,‘不合格’ PJ fromtestwhereidnotin(selectidfromtestwhere a=b);

1.  SQL代码：

selectdistinctid,‘合格’ PJ fromtestwhereidin(selectidfromtestwhere a=b)union

selectdistinctid,‘不合格’ PJ fromtestwhereidnotin(selectidfromtestwhere a=b);

或者

SELECT ‘yes’,ID FROM TEST WHERE A=BGROUP BY ID ORDER BY ID

SELECT ‘no’,ID FROM TEST WHERE ID NOT IN (SELECT ID FROM TEST WHERE A=B) GROUP BY ID ORDER BY ID

5.联想扩展：

假设只有A,B两列数据，如果存在A=B，则显示匹配成功（即根据A来判断）

select t3.xx,decode(t3.xx,t3.yy,‘success’,‘fail’)匹配情况from

(select\*from(selectdistinct A xx fromtest) t1leftjoin

(selectdistinct A yy fromtestwhere A=B) t2 on t1.xx=t2.yy) t3

### 实验6 ###

1.问题描述：

为什么第一个SQL没有数据，第二个SQL有数据？

SQL1:

SELECT t.\*

FROM alx\_material\_types\_intf\_v t

WHERE t.material\_level = 3

AND t.material\_type NOT IN

(SELECT a.parent_type FROM alx_material_types_intf_v a); --无数据

SQL2：

SELECT t.\*

FROM alx\_material\_types\_intf\_v t

WHERE t.material\_level = 3

AND t.material\_type NOT IN

(SELECT a.parent_type FROM alx_material_types_intf_v a WHERE a.parent_type = t.material_type); --有数据

2.需求分析：

对比两个SQL语句，区别在于第二个SQL语句多出了‘WHERE a.parent\_type = t.material\_type’。

3.解答过程：

第一句SQL的子查询SELECT a.parent\_type FROM alx\_material\_types\_intf\_v a中parent\_type有空值的话,not in (null)的结果是null，不是true。所以没有数据、

第二个SQL里面，因为多了个“=”的条件，导致无论如何结果集内不会出现空值。

### 实验7 ###

1.问题描述：

有一个商品信息表，该表反应了各种商品的销售情况，一个产品是按照gid和gname两个字段来区分的，一个产品可能会有多个型号。

create table T\_Goods  
(  
Id int primary key,  
GId varchar2(10) not null,  
GName varchar2(20) not null,  
GColour varchar2(10),  
GWithin int,  
GSize varchar2(10),  
GNumber int  
)

CREATE SEQUENCE seq\_goods  
INCREMENT BY 1  
START WITH 1  
NOMAXVALUE  
NOCYCLE  
CACHE 10;

现要将各种商品各种型号的销售情况进行汇总统计，达到如下效果：

2.需求分析：

分类统计—>说明要用到group by 和sum（）函数

group by 分两种情况：(1)group by GId, gname, gcolour, gwithin, gsize 得到的是每种型号的销售量

(2)group by gid, gname 得到的是每种产品的销售量

3.解答过程：

(1)求每种型号的销售量

(2)求每种产品的销售量

(3)求商品的总销售量

(4)将以上3个结果集联合在一起

(5)用decode（）函数精简gid和gname，用row\_number() over(partition by ) 函数来排序

4.SQL代码：

select rownum seq,  
decode(rn, 1, gid) gid,  
decode(rn, 1, gname) gname,  
gcolour,  
gwithin,  
gsize,  
gnumber  
from (select t.\*, row\_number() over(partition by gid,gname order by gnumber) rn  
from (select GId,  
gname,  
gcolour,  
gwithin,  
gsize,  
sum(gnumber) gnumber  
from t\_goods  
group by GId, gname, gcolour, gwithin, gsize  
union all  
select gid, gname, null, null, ‘小计’, sum(gnumber)  
from t\_goods  
group by gid, gname  
union all  
select null, null, null, null, ‘总计’, sum(gnumber)  
from t\_goods) t);

### 实验8 ###

1.问题描述：

有一store\_fee表，表中有四个字段（会员卡编号、办卡店编号、消费情况、消费店编号）

现要统计各店的办卡总计和消费总计

2.需求分析：

在A店办卡的会员，可能会在其他店里进行消费

3.解答过程：

(1)求各店的办卡统计情况

(2)求各店的消费统计情况

(3)将以上2个结果集联合起来

4.SQL代码：

select t1.dept\_no, t1.办卡统计, t2.消费统计  
from (select dept\_no, count(\*) 办卡统计 from store\_fee group by dept\_no) t1  
left join (select deptno\_no2, sum(fee) 消费统计  
from store\_fee  
group by deptno\_no2) t2  
on t1.dept\_no = t2.deptno\_no2  
order by dept\_no;