数据库SQL语言实习.docx

1、数据库SQL语言实习 数据库上机实习报告 实 验 名 称：数据库和数据表操作实验 实 验 环 境：Win10+interi7-5500U 2.4GHZ +内存4.0GVMware虚拟WinXP系统 Oracle 11g + navicat2016年 11月13 日1. 分别采用 UI 界面和 SQL 语言为 University 的 Department 表输入下列数据： 输入SQL语言代码为：/*系的信息表 Department（Dno,Dname,Daddress）*/create table Department( Dno number(10), Dname varchar2(50),

2、Daddress varchar2(50), primary key (Dno) );insert into Department(Dno,Dname,Daddress) values(1,地球科学学院,主楼东);insert into Department(Dno,Dname,Daddress) values(2,资源学院,主楼西);insert into Department(Dno,Dname,Daddress) values(3,材化学院,材化楼);insert into Department(Dno,Dname,Daddress) values(4,环境学院,文华楼);insert

3、into Department(Dno,Dname,Daddress) values(5,工程学院,水工楼);insert into Department(Dno,Dname,Daddress) values(6,地球物理与空间信息学院,物探楼);insert into Department(Dno,Dname,Daddress) values(7,机械与电子信息学院,教二楼);insert into Department(Dno,Dname,Daddress) values(8,经济管理学院,经管楼);insert into Department(Dno,Dname,Daddress) va

4、lues(9,外语学院,北一楼);insert into Department(Dno,Dname) values(10,信息工程学院);insert into Department(Dno,Dname,Daddress) values(11,数学与物理学院,基委楼);insert into Department(Dno,Dname,Daddress) values(12,珠宝学院,珠宝楼);insert into Department(Dno,Dname,Daddress) values(13,政法学院,政法楼);insert into Department(Dno,Dname,Daddre

5、ss) values(14,计算机学院,北一楼);insert into Department(Dno,Dname) values(15,远程与继续教育学院);insert into Department(Dno,Dname) values(16,国际教育学院);insert into Department(Dno,Dname,Daddress) values(17,体育部,体育馆);insert into Department(Dno,Dname,Daddress) values(18,艺术与传媒学院,艺传楼);insert into Department(Dno,Dname,Daddres

6、s) values(19,马克思主义学院,保卫楼);insert into Department(Dno,Dname,Daddress) values(20,江城学院,江城校区);运行结果为：2. 用SQL 语言为 University 的 Student 表输入下列数据： 输入SQL语言代码为：/*学生信息表Student（Sno, Sname, Ssex, Sage, Dno）*/create table Student( Sno char(11) , Sname varchar2 (50), Ssex char(2), Sage number(10) , Dno number(10),

7、primary key (Sno), foreign key (Dno) references Department(Dno) );insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091000231,吕岩,M,18,14);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091004391,颜荣,M,19,14);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091001598,王海涛,M,

8、20,14);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091003085,袁恒,M,18,14);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091000863,孟飞,M,17,14);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091000934,罗振俊,M,19,8);insert into Student(Sno, Sname, Ssex, Sage, Dno) valu

9、es(20091000961,曾雪君,F,18,8);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091000983,巴翔,M,19,8);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091001175,周雷,M,18,8);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091001261,马欢,M,17,8);insert into Student(Sno, Sname, Ssex

10、, Sage, Dno) values(20091001384,陈亮,M,20,8);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20081003492,易家新,M,19,5);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20081001197,李子聪,M,17,5);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20081001266,蔡景学,F,19,5);insert into Stude

11、nt(Sno, Sname, Ssex, Sage, Dno) values(20081001888,赵林云,F,20,5);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20091000481,姜北,M,17,5);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20101000199,孙中孝,M,18,11); insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20101000424,杨光,M,17,

12、11);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20101000481,张永强,M,16,11);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20101000619,陈博,M,20,11);insert into Student(Sno, Sname, Ssex, Sage, Dno) values(20101000705,汤文盼,M,18,11);insert into Student(Sno, Sname, Ssex, Sage, Dno) value

13、s(20101000802,苏海恩,M,17,11);运行结果为：3. 分别采用 UI 界面和 SQL 语言为 University 的 Course 表输入下列数据输入SQL语言代码为：/*课程信息表 Course （Cno, Cname, Cpno, Ccredit)*/create table Course( Cno number(10) primary key , Cname varchar2 (50), Cpno number(10) , CCredit number(10), foreign key(Cpno) references Course(Cno) );insert int

14、o Course(Cno, Cname, Ccredit) values(2,高等数学,8);insert into Course(Cno, Cname, Ccredit) values(6,C语言程序设计,4);insert into Course(Cno, Cname, Ccredit) values(7,大学物理,8);insert into Course(Cno, Cname, Ccredit) values(8,大学化学,3);insert into Course(Cno, Cname, Ccredit) values(10,软件工程,2);insert into Course(Cn

15、o, Cname, Ccredit) values(12,美国简史,2);insert into Course(Cno, Cname, Ccredit) values(13,中国通史,6);insert into Course(Cno, Cname, Ccredit) values(14,大学语文,3);insert into Course(Cno, Cname, Cpno, Ccredit) values(5,数据结构,6,4);insert into Course(Cno, Cname, Cpno, Ccredit) values(4,操作系统,5,4);insert into Cours

16、e(Cno, Cname, Cpno, Ccredit) values(1,数据库原理,5,4);insert into Course(Cno, Cname, Cpno, Ccredit) values(3,信息系统,1,2);insert into Course(Cno, Cname, Cpno, Ccredit) values(9,汇编语言,6,2);insert into Course(Cno, Cname, Cpno, Ccredit) values(11,空间数据库,1,3运行结果为：4.分别采用 UI 界面和 SQL 语言为 University 的 Teacher 表输入下列数据

17、： 输入SQL语言代码为：/*课程信息表 Course （Cno, Cname, Cpno, Ccredit)*/create table Course( Cno number(10) primary key , Cname varchar2 (50), Cpno number(10) , CCredit number(10), foreign key(Cpno) references Course(Cno) );insert into Course(Cno, Cname, Ccredit) values(2,高等数学,8);insert into Course(Cno, Cname, Ccr

18、edit) values(6,C语言程序设计,4);insert into Course(Cno, Cname, Ccredit) values(7,大学物理,8);insert into Course(Cno, Cname, Ccredit) values(8,大学化学,3);insert into Course(Cno, Cname, Ccredit) values(10,软件工程,2);insert into Course(Cno, Cname, Ccredit) values(12,美国简史,2);insert into Course(Cno, Cname, Ccredit) valu

19、es(13,中国通史,6);insert into Course(Cno, Cname, Ccredit) values(14,大学语文,3);insert into Course(Cno, Cname, Cpno, Ccredit) values(5,数据结构,6,4);insert into Course(Cno, Cname, Cpno, Ccredit) values(4,操作系统,5,4);insert into Course(Cno, Cname, Cpno, Ccredit) values(1,数据库原理,5,4);insert into Course(Cno, Cname, C

20、pno, Ccredit) values(3,信息系统,1,2);insert into Course(Cno, Cname, Cpno, Ccredit) values(9,汇编语言,6,2);insert into Course(Cno, Cname, Cpno, Ccredit) values(11,空间数据库,1,3);运行结果为：5. 分别采用 UI 界面和 SQL 语言为 University 的 SC 表输入下列数据：输入SQL语言代码为：create table SC( Sno char(11), Cno number(10), Grade number(10), primar

21、y key(Sno, Cno), foreign key(Sno) references Student(Sno), foreign key (Cno) references Course(Cno) );insert into SC values(20091003085,1,90);insert into SC values(20091000863,1,98);insert into SC values(20091000934,1,89);insert into SC values(20091000961,1,85);insert into SC values(20081001197,1,79

22、);insert into SC values(20081001266,1,97);insert into SC values(20081001888,1,60);insert into SC values(20091000481,1,78);insert into SC values(20101000199,1,65);insert into SC values(20101000424,1,78);insert into SC values(20101000481,1,69);insert into SC values(20091000863,6,90);insert into SC val

23、ues(20091000934,6,90);insert into SC values(20091000961,6,87); 运行结果为：6. 分别采用 UI 界面和 SQL 语言为 University 的 TC 表输入下列数据输入SQL语言代码为：/*教师授课表TC（Tno,Cno,Site）*/create table TC( Tno number(10) , Cno number(10), Site varchar2(50), primary key (Tno,Cno), foreign key(Tno) references Teacher(Tno), foreign key (Cn

24、o) references Course(Cno) );insert into TC values(1,1,教一楼407)insert into TC values(1,6,教一楼307);insert into TC values(2,10,教二楼217);insert into TC values(3,2,教三楼507);insert into TC values(4,5,教三楼208);insert into TC values(6,3,综合楼207);insert into TC values(7,4,教二楼817);insert into TC values(5,9,教一楼207);

25、运行结果为：7. 采用 SQL 语言查询所有的学生信息；输入SQL语言代码为：SELECT*FROM STUDENT;结果为：8. 采用 SQL 语言查询所有女生的姓名输入SQL语言代码为： SELECT SNAME FROM STUDENT WHERE SSEX=F;运行结果为： 9. 采用 SQL 语言查询各个院系学生人数； 输入SQL语言代码为：SELECT DNO,COUNT(SNO) AllNumber FROM STUDENT GROUP BY DNO;输出结果为：10. 采用 SQL 语言查询各个院系老师人数； 输入SQL语言代码为：SELECT DNO,COUNT(TNO) A

26、llNumber FROM TEACHER GROUP BY DNO;输出结果为：11. 采用 SQL 语言查询所有选修了数据库原理并且成绩在 60-100 分之间的学生的姓名和成绩，并按照成绩的降序排列；输入SQL语言代码为：SELECT STUDENT.SNAME,SC.GRADEFROM STUDENT,SCWHERE SC.SNO=STUDENT.SNO AND SC.GRADE BETWEEN 60 AND 100 AND CNO IN( SELECT CNO FROM COURSE WHERE CNAME=数据库原理 )ORDER BY SC.GRADE DESC;运行结果为：12

27、. 采用 SQL 语言编写一个连接查询：查询经济管理学院年龄在 20 岁以下的男生的姓名和年龄输入SQL语言代码为：SELECT SNAME,SAGEFROM STUDENT,DEPARTMENTWHERE DEPARTMENT.DNAME=经济管理学院 AND DEPARTMENT.DNO=STUDENT.DNO AND SAGE5 );运行结果为：14. 采用 SQL 语言编写一个嵌套查询：查询各门课程的最高成绩的学生姓名和及其成绩输入SQL语言代码为：SELECT A2.CNO,STUDENT.SNAME, A2.GRADEFROM STUDENT,SC A2 WHERE A2.SNO=

28、STUDENT.SNO AND A2.GRADE IN( SELECT MAX(A1.GRADE) FROM SC A1 GROUP BY A1.CNO HAVING A2.CNO=A1.CNO);运行结果为：15. 采用 SQL 语言查询所有选修了何小峰老师开设课程的学生姓名及其所在的院系名称。 输入SQL语言代码为：SELECT DISTINCT SNAME,DNAMEFROM TC,TEACHER,SC,STUDENT,DEPARTMENTWHERE TNAME=何小峰 AND TEACHER.TNO=TC.TNO AND TC.CNO=SC.CNO AND SC.SNO=STUDENT

29、.SNO AND STUDENT.DNO=DEPARTMENT.DNO;运行结果为：16. 采用 SQL 语言，在数据库中删除学号为 20091003085 的学生的所有信息（包括其选课记 录）。输入SQL语言代码为：ALTER TABLE STUDENTDISABLE PRIMARY KEY CASCADE;ALTER TABLE SCDISABLE PRIMARY KEY CASCADE;DELETEFROM STUDENTWHERE STUDENT.SNO=20091003085;DELETEFROM SCWHERE SC.SNO=20091003085;ALTER TABLE STUDENTENABLE PRIMARY KEY ;ALTER TABLE SCENABLE PRIMARY KEY ;运行结果为： 17. 采用 SQL语言，将学号为 20091000863 的学生的数据库原理这门课的成绩修改为