实验四 SQL综合练习
- 格式：doc
- 大小：42.50 KB
- 文档页数：4
实验四 SQL综合练习
一 实验目的
全面掌握SQL 语句，包括创建表、视图和索引，定义完整性，插入、删除和修改数据，进行数据查询（函数）。
二、实验要求
用ORACLE SQL*PLUS完成实验内容。具体要求：
1、使用save语句保存所写的所有SQL命令，实验结束时，上传到FTP。
文件名为“学号+姓名.sql”;
2、使用spool语句，保存所有操作及结果，实验结束时，上传到FTP。
文件名为“学号+姓名.txt”;
三、实验环境
ORACLE 9i或10G
四、实验内容
（一）、假设已经创建S、C、SC表，并插入下列数据：
创建学生表S：
SQL> CREATE TABLE S
2 (S# CHAR(4) NOT NULL,
3 SNAME CHAR(8) NOT NULL,
4 AGE SMALLINT,
5 SEX CHAR(1),
6 PRIMARY KEY(S#));
创建课程表C：
SQL> CREATE TABLE C
2 (C# CHAR(4),
3 CNAME CHAR(10) NOT NULL,
4 TEACHER CHAR(8),
5 PRIMARY KEY(C#));
创建选课关系表SC：
SQL> CREATE TABLE SC
2 (S# CHAR(4),
3 C# CHAR(4),
4 GRADE SMALLINT,
5 PRIMARY KEY(S#,C#),
6 FOREIGN KEY(S#) REFERENCES S(S#),
7 FOREIGN KEY(C#) REFERENCES C(C#),
8 CHECK(GRADE BETWEEN 0 AND 100));
插入几条S记录：
SQL> INSERT INTO S(S#,SNAME,AGE,SEX) 2 VALUES('S1','LI',19,'M');
SQL> INSERT INTO S(S#,SNAME,AGE,SEX)
2 VALUES('S2','YANG',19,'F');
SQL> INSERT INTO S(S#,SNAME,AGE,SEX)
2 VALUES('S3','ZHANG',18,'F');
插入几条C记录：
SQL> INSERT INTO C(C#,CNAME,TEACHER)
2 VALUES('C1','MATHS','WANG');
SQL> INSERT INTO C(C#,CNAME,TEACHER)
2 VALUES('C2','ENGLISH','ZHAO');
SQL> INSERT INTO C(C#,CNAME,TEACHER)
2 VALUES('C3','COMPUTER','DING');
SQL> INSERT INTO C(C#,CNAME,TEACHER)
2 VALUES('C4','MATHS','YANG');
插入几条SC记录：
SQL> INSERT INTO SC(S#,C#,GRADE)
2 VALUES('S2','C3',90);
SQL> INSERT INTO SC(S#,C#,GRADE)
2 VALUES('S3','C2',91);
SQL> INSERT INTO SC(S#,C#,GRADE)
2 VALUES('S3','C4',80);
完成下列任务：
1、检索选修课程名为MATHS的学生学号和姓名；
2、检索选修课程号为C2或C4的学生学号（区分大小写）；
3、检索LIU老师教的课程号和课程名；
4、检索不学C2的学生姓名与年龄；
5、为SC表按学号降序和课程号降序建唯一索引；
6、修改S表，增加SDEPT列（SDEPT表示学生所在的系）；
7、创建关于学生学号和平均成绩的视图；
（二）已知公司的员工表EMP1(EID, ENAME, BDATE, SEX, CITY)，
部门表DEPT1（DID, DNAME, DCITY），
工作表WORK（EID,DID,STARTDATE,SALARY）。各个字段说明如下：
create table EMP1
(EID VARCHAR2(6) PRIMARY KEY,
ENAME VARCHAR2(10),
BDATE DATE,
SEX VARCHAR2(2),
CITY VARCHAR2(20)
);
Create table DEPT1
(DID VARCHAR2(3) PRIMARY KEY,
DNAME VARCHAR2(20), DCITY VARCHAR2(20)
);
create table WORK
(EID VARCHAR2(6) PRIMARY KEY,
DID VARCHAR2(10),
STARTDATE DATE,
SALARY INT
);
向每个表中插入适当的数据。例如：插入三条部门的数据，分别为每个部门插入两条员工数据
insert into emp1
values ('A0001','jack', TO_DATE ('1979-08-01','YYYY-mm-dd'),'女','上海');
insert into emp1
values('A0002','l刘敏',to_date('1980-08-07','YYYY-mm-dd'),'男','江苏');
insert into dept1
values('B01','研发部','上海');
insert into dept1
values('B02','生产部','湖北');
insert into dept1
values('B03','销售部','上海');
insert into work
values('A0001','B01',to_date('2002-04-06','yyyy-mm-dd'),3000);
insert into work
values('A0002','B02',to_date('2003-5-1','yyyy-mm-dd'),2500);
注：EID——员工编号，最多6个字符。例如A00001（主键）
ENAME——员工姓名，最多10个字符。例如SMITH
BDATE——出生日期，日期型
SEX——员工性别，单个字符。F或者M
CITY——员工居住的城市，最多20个字符。例如：上海
DID——部门编号，最多3个字符。例如 A01 （主键）
DNAME——部门名称，最多20个字符。例如：研发部门
DCITY——部门所在的城市，最多20个字符。例如：上海
STARTDATE——员工到部门上班的日期，日期型
SALARY——员工的工资。整型。
请使用ORACLE的sql*plus 完成下列的操作
1、 列出员工编号以字母P至S开头的所有员工的基本信息。
2、 删除年龄超过60岁的员工。
3、 为工龄超过10年的职工增加10%的工资。
4、 查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。
5、 查询部门人数大于5的每个部门的编号，名称，人数。
6、 查询部门人数大于5的每个部门的最高工资，最低工资。
