数据库原理与应用——实验二

实验二题目内容

实验二 SQL语言编程实验（一）
Structured Query Language,简称SQL,是一种介于关系代数与关系演算之间的语言,其功能包括查询、操纵、定义和控制4个方面，是一个通用的、功能极强的关系数据库语言,目前已成为关系数据库的标准语言。
实验目的：使用数据库管理系统SQL Server的查询分析器，巩固学习SQL语言。教学课时数为6。
实验内容：熟练查询分析器的各种操作，使用SQL语言进行编程设计。
实验要求：能够熟练使用查询分析器，掌握SQL中的DDL、DML、DCL等各种语言,完成各种编程练习.理解视图的概念,能够按照要求设计数据库的外模式.

实验操作题目
(1) 使用DDL语言定义基本表.
有一个员工项目数据库,现有四张表，分别是部门（DEPT），员工（EMP），项目（PROJ）以及员工参与项目的情况表（EP）。其中一个员工属于一个部门，一个部门有多个员工；一个员工可同时参加多个项目，一个项目有多个员工一起开发。用SQL语言中的DDL语言建立这四张表,表的主码,以及设计并建立表的外码和索引，其中周期部门表（DEPT）上按部门名称设计升序唯一索引，在员工表（EMP）上按照部门设计聚簇索引，按照职工工资降序索引，在员工项目表上按员工号升序、项目号降序建立唯一索引。

DEPT
DNO（部门号） DNAME（部门名） CNAME（领导）
D1 厂部 1001
D2 工程部 1003
D3 技术部 1004
D4 质监部 1005
。。。 。。。 。。。

PROJ
PNO（项目编号） PNAME（项目名称） PCITY（项目所在城市）
J1 Y 天津
J2 W 上海
J3 X1 宁波
J4 S 北京
。。。 。。。 。。。

EMP
ENO（员工号） ENAME（姓名） ESEX（性别） ESALARY（工资） DNO（所在部门）
1001 李月 男 1200 D1
1002 魏兰 女 1440 D3
1003 赵希青 男 1360 D2
1004 张明 男 1600 D3
。。。 。。。 。。。 。。。 。。。

EP
ENO（员工号） PNO（项目号） RESPONSE（任务）
1001 J1 总工
1001 J3 联系人
1002 J1 技术负责
1002 J2 技术监督
1003 J1 现场管理
1004 J3 主管
。。 。。 。。。

(2) 使用DML语言输入以上表中的数据(INSERT,UPDATE和DELETE 语句)
(3) 对以上表中的数据以下数据的更新:
①删除有关‘J3’项目的所有数据
②将D2部门员工的工资增加5%
③修改J2项目的所在城市为北京
④删除张明这个员工的所有信息
（4）完成以下简单查询
①求参加‘J3’项目的员工号
②查询工资在1500以上的员工信息
③求没有参加‘J2’项目的员工号
⑦找出参加项目数大于3的员工号
⑧统计每个部门的最高工资、最低工资、平均工资
⑨查询人员数目大于30的部门号

实验二具体操作

【方案设计】
关系名 属性的名字 属性含义 域 是否主码 是否外码
dept dno 部门号 Char(10) 是
dname Char(10)
cname Char(10)
proj pno Char(10) 是
pname Char(10)
pcity Char(10)
emp eno Char(10) 是
ename Char(10)
esex Char(10)
esalary Numeric(10)
dno Char(10) 是
-dept（dno）
ep eno Char(10) 是
-emp（eno）
pno Char(10)
pesponse Char(10)

【实验（实训）过程】（步骤、记录、数据、程序等）
（1）使用DDL语言定义基本表

create table dept
(
dno char(10),
dname char(10),
cname char(10),
constraint pk_dept primary key (dno),
)

create table proj
(
pno char(10),
pname char(10),
pcity char(10),
constraint pk_proj primary key (pno),
)

create table emp
(
eno char(10),
ename char(10),
esex char(10),
esalary numeric(10),
dno char(10),
constraint pk_emp primary key (eno),
constraint fk_emp foreign key (dno) references dept(dno),
)

create table ep
(eno char(10),
pno char(10),
pesponse char(10),
constraint fk_ep foreign key (pno) references proj(pno),
)

（2）使用DML语言输入以上表中的数据（INSERT，UPDATE和DELETE语句）

insert into dept(dno,dname,cname) values ('D1','厂部','1001')
insert into dept(dno,dname,cname) values ('D2','工程部','1002')
insert into dept(dno,dname,cname) values ('D3','技术部','1004')
insert into dept(dno,dname,cname) values ('D4','质监部','1005')

insert into proj(pno,pname,pcity) values ('J1','Y','天津')
insert into proj(pno,pname,pcity) values ('J2','W','上海')
insert into proj(pno,pname,pcity) values ('J3','X1','宁波')
insert into proj(pno,pname,pcity) values ('J4','S','北京')

insert into emp(eno,ename,esex,esalary,dno) values ('1001','李月','男','1200','D1')
insert into emp(eno,ename,esex,esalary,dno) values ('1002','魏兰','女','1440','D2')
insert into emp(eno,ename,esex,esalary,dno) values ('1003','赵希青','男','1360','D3')
insert into emp(eno,ename,esex,esalary,dno) values ('1004','张明','男','1600','D4')

insert into ep(eno,pno,pesponse) values ('1001','J1','总工')
insert into ep(eno,pno,pesponse) values ('1001','J3','联系人')
insert into ep(eno,pno,pesponse) values ('1002','J1','技术负责')
insert into ep(eno,pno,pesponse) values ('1002','J2','技术监督')
insert into ep(eno,pno,pesponse) values ('1003','J1','现场管理')
insert into ep(eno,pno,pesponse) values ('1004','J3','主管')

（3）对上表中的数据进行以下数据的更新：

①删除有关‘J3’项目的所有数据
delete from proj where pno = 'J3'
delete from ep where pno = 'J3'
②将D2部门员工的工资增加5%
update emp set esalary = esalary*1.05 where dno = 'D2'
③修改J2项目的所在城市为北京
update proj set pcity = '北京' where pno='J2'
④删除张明这个员工的所有信息
delete from emp where ename = '张明'
（4）完成以下简单查询
①求参加‘J3’项目的员工号
select eno
from ep
where pno='J3'
②查询工资在1500以上的员工信息
select *
from emp
where esalary>1500
③求没有参加‘J2’项目的员工号
select *
from ep
where pno != 'J2'
⑦找出参加项目数大于3的员工号
select eno
from ep
group by eno
having count(*)>1
⑧统计每个部门的最高工资、最低工资、平均工资

select max(esalary) as maxsalary
from emp

select min(esalary) as minsalary
from emp

select avg(esalary) 
from emp
⑨查询人员数目大于30的部门号
select distinct dno
from emp
group by dno
having count(*)>30