实验 ·SQL语言综合练习

学号：																姓名：

一、按以下方式建立建立部门（dept）

Create table dept(
  dno char(10) primary key,
	Dname char(40),
	Dloc char(40));

Insert into dept
	Values(‘10’,’研究所’,’北京’);
Insert into dept
  Values(‘11’,’经营部’,’潍坊’);
Insert into dept
  Values(‘12’,’办公室’,’潍坊’);

问题：对于给定的部门编号，编写语句块，按以下格式输出该部门的新息：

部门编号：XXX

部门名称：XXX

部门所在位置：XXX

DECLARE
   CURSOR dept_cur IS 
   select dno,dname,dloc from dept;
   dept_record dept%ROWTYPE
BEGIN
   OPEN dept_cur;
   LOOP
   	FETCH dept_cur INTO dept_record;
   	EXIT WHEN depu_cur%NOTFPUND;
   	dbms.output.put_line('部门编号：'|| dept_record.dno);
   	dbms.output.put_line('部门名称：'|| dept_record.Dname)
   	dbms.output.put_line('部门所在位置：'|| dept_record.Dloc)
END;

二、SQL综合练习

Create table emp(
	编号 char(20) primary key,
	姓名 char(20),
	性别 char(4),
	出生年月 date,
	入职日期 date,
	岗位 char(20),
	薪水 number,
	部门编号 char(10));

Insert into dept
	Values(‘10001’,’张力’,’男’,to_date(‘1998-7-1’,’YYYY-MM-DD’),to_date(‘1998-1-1’,’YYYY-MM-DD’),’研究员’,6100,’10’);

Insert into dept
	Values(‘10002’,’赵军’,’男’,to_date(‘1998-6-1’,’YYYY-MM-DD’),to_date(‘1998-1-1’,’YYYY-MM-DD’),’研究员’,7000,’11’);

Insert into dept
	Values(‘10003’,’王张力’,’男’,to_date(‘1989-1-1’,’YYYY-MM-DD’),to_date(‘1998-1-1’,’YYYY-MM-DD’),null,5800,’12’);

针对关系模式：雇员（编号，姓名，性别，出生年月，入职日期，岗位，薪水，部门编号）
部门（部门编号，部门名称，所在城市）
写出完成以下操作的SQL命令。

1、写出创建“部门”表的命令

create table 部门(
 部门编号 varchar2(10) primary key,
 部门名称 varchar2(20),
 所在城市 varchar2(20)
);

2、查询男性雇员的信息

select * from 雇员 where(
 性别='男'
);

3、查询暂时未分配岗位的雇员编号和姓名

select 编号,姓名 from 雇员 where(
 岗位 is NULL
);

4、该单位共设置了哪些岗位

select 岗位 from 雇员;

5、查询薪水低于6000的雇员编号、姓名及岗位

select 编号,姓名,岗位 from 雇员 where(
 薪水 < 6000
);

6、查询90年之前入职的雇员信息

select * from 雇员 where(
 入职日期 < to_date('1990-1-1','yyyy-mm-dd')
);

7、查询“张”姓雇员信息

select * from 雇员 where(
 姓名 like '张%'
);

8、查询所有雇员的编号、姓名、性别和部门名称

select 编号,姓名,性别,部门名称 from 雇员,部门 where(
 emp.部门编号=dept.部门编号
);

9、查询与“王军”岗位相同的雇员信息

select * from 雇员 where(
 岗位=(select 岗位 from 雇员 where 
     姓名='王军')
);

10、薪水低于6000的提高10%

update 雇员 
set 薪水=薪水*1.1 where(
 薪水 < 6000
);

11、向部门表插入一条新纪录：1004，研究所、北京

insert into dept 
values('1004','研究所','北京');

12、删除部门表中部门编号为1004的纪录

delete from dept where(
 部门编号='1004'
);

13、统计单位的雇员总人数

select count(编号) from 雇员;

14、统计男性雇员的人数

select count(编号) from 雇员 where(
   性别='男'
);

15、统计男女雇员各有多少人

select count(编号) from 雇员
group by 性别;

16、12部门男女雇员各有多少人

#1

select count(*) as 人数  /*这种方法不推荐，效率不高*/
from 雇员
group by 性别
having 部门编号='12';

#2

这位就是爱吃肉rodalao写的效率会比上面的好很多

 /*推荐这种方法*/
select 部门编号 count(*) as 人数
from 雇员
where 部门编号 = '12'
group by 性别;

17、人数不足3人的岗位有哪些

select 岗位 from 雇员
group by 岗位
having count(*) < 3;

18、统计单位的薪水总额

select sum(薪水) from 雇员;

19、统计各部门的薪水总额、平均、最高和最低薪水

select sum(薪水) as 薪水总额,
   ave(薪水) as 平均薪水,
   max(薪水) as 最高薪水,
   min(薪水) as 最低薪水
from 雇员
group by 部门编号;

20、删除部门表

drop table dept;