数据库SQL语言编程实验
实验二 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）上按照部门设计聚簇索引，按照职工工资降序索引，在员工项目表上按员工号升序、项目号降序建立唯一索引。
表设计：
create table DEPT
(
DNO char(20)not null,
DNAME char(20)not null,
CNAME char(20),
constraint PK_DEPT primary key(DNO)
)
create table PROJ
(
PNO char(20)not null,
PNAME char(20)not null,
PCITY char(20)not null,
constraint PK_PROJ primary key(PNO)
)
create table EMP
(
ENO char(20)not null,
ENAME char(10)not null,
ESEX char(10)not null,
ESALARY int,
DNO char(20)not null,
constraint PK_EMP primary key(ENO),
constraint fk_EMP_DNO foreign key(DNO)references DEPT(DNO)
)
create table EP
(
ENO char(20)not null,
PNO char(20)not null,
RESPONSE char(20),
constraint PK_EP primary key(ENO,PNO),
constraint fk_EP_ENO foreign key(ENO)references EMP(ENO), constraint fk_EP_PNO foreign key(PNO)references PROJ(PNO)
)
(2) 使用DML语言输入以上表中的数据(INSERT,UPDATE和DELETE 语句)
insert into DEPT(DNO,DNAME)
VALUES('D1','厂部')
insert into DEPT(DNO,DNAME)
VALUES('D2','工程部')
insert into DEPT(DNO,DNAME)
VALUES('D3','技术部')
insert into DEPT(DNO,DNAME)
VALUES('D4','质监部')
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,'D3')
insert into EMP(ENO,ENAME,ESEX,ESALARY,DNO) VALUES('1003','赵希青','男',1360,'D2')
insert into EMP(ENO,ENAME,ESEX,ESALARY,DNO) VALUES('1004','张明','男',1600,'D3')
insert into EP(ENO,PNO,RESPONSE)
VALUES('1001','J1','总工')
insert into EP(ENO,PNO,RESPONSE)
VALUES('1001','J3','联系人')
insert into EP(ENO,PNO,RESPONSE)
VALUES('1002','J1','技术负责')
insert into EP(ENO,PNO,RESPONSE)
VALUES('1002','J2','技术监督')
insert into EP(ENO,PNO,RESPONSE)
VALUES('1003','J1','现场管理')
insert into EP(ENO,PNO,RESPONSE)
VALUES('1004','J3','主管')
(3) 对以上表中的数据以下数据的更新:
①删除有关‘J3’项目的所有数据
Delete from EP
where PNO='J3'
Delete from PROJ
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 EP
where ENO=(
select ENO from EMP
where ENAME='张明'
)
Delete from EMP
where ENAME='张明'
（4）完成以下简单查询
①求参加‘J3’项目的员工号
select ENO
from EP
where PNO='J3'
②查询工资在1500以上的员工信息
select*
from EMP
where ESALARY>1500
③求没有参加‘J2’项目的员工号
select ENO from EMP
where not ENO=(
select ENO from EP
where PNO='J2')
⑦找出参加项目数大于3的员工号
select EP.ENO from EP group by EP.ENO having COUNT(EP.PNO)>=3
⑧统计每个部门的最高工资、最低工资、平均工资
select EMP.DNO as 部门,
MAX(EMP.ESALARY)as 工资最高,
MIN(EMP.ESALARY)as 工资最低 ,
AVG(EMP.ESALARY)as 平均工资
from EMP GROUP by EMP.DNO
⑨查询人员数目大于30的部门号
select EMP.DNO from EMP group by EMP.DNO having COUNT(EMP.ENO)>=30