SQL数据定义与数据查询.doc

数据库上机报告数据定义与数据查询一、 写明实验所采用的实验环境：硬件环境：普通联网的PC机；操作系统：Windows 2000或者Windows XP；数据库管理系统：MS SQL Server 2005。二、 给出实验内容与完成情况：（一）使用命令行方式建立学生课程数据库(1) 用SQL语言建立学生课程数据库，数据库命名：SC_学号后四位及姓名，如SC_3112liyan；CREATE DATABASE SC_1224cxj ON (NAME = SC_1224cxj_dat,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL SC_1224cxj_dat.mdf)LOG ON (NAME= SC_1224cxj_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL SC_1224_log.ldf); (2) 用SQL语言在学生课程数据库中建立3个表，要求进行完整性定义：学生关系Student(Sno，Sname，Ssex，Sage，Sdept) 其中Sno不能为空，值是唯一的，并且Sname取值也唯一，Sage自定义约束“大于16”课程关系Course(Cno，Cname，Cpno，Ccredit) 其中Cno不能为空，值是唯一的，并且Cname取值也唯一教师关系Teacher(Tno，Tname，Department，Email，Salary) 其中Tno不能为空，值是唯一的选修关系SC(Sno，Cno，Grade) 其中Sno和 Cno不能为空，(Sno, Cno)为主码，Grade的数据类型为int建立Student表：CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(40) UNIQUE, Ssex CHAR(2), Sage SMALLINT CHECK(Sage16), Sdept CHAR(20) ); 建立Course表：CREATE TABLE Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) UNIQUE, Cpno CHAR(4), Credit SMALLINT, CONSTRAINT FK_C FOREIGN KEY (Cpno) REFERENCES Course(Cno) );建立Teacher表： CREATE TABLE Teacher (Tno CHAR(4) PRIMARY KEY, Tname CHAR(40), Department CHAR(20), Email CHAR(100), Salary INT );建立SC表：CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), CONSTRAINT FK_SC1 FOREIGN KEY(Sno) REFERENCES Student(Sno), CONSTRAINT FK_SC2 FOREIGN KEY(Cno) REFERENCES Course(Cno) ); (3) 修改数据表： 将SC表中Grade的数据类型改为float；ALTER TABLE SC ALTER COLUMN Grade FLOAT; 在Student表中增加一个属性Scome记录学生的入学时间，其数据类型为DATETIME； ALTER TABLE Student ADD Scome DATETIME; 删除Student表中新增加Scome属性；ALTER TABLE Student DROP COLUMN Scome; 删除表Teacher表中的Email属性；ALTER TABLE Teacher DROP COLUMN Email; 删除Student表中对Sname属性创建的Unique约束。（无法删除未建立名字的约束）建立约束语句：ALTER TABLE Student ADD CONSTRAINT S_CHECK UNIQUE(Sname);删除约束语句：ALTER TABLE Student DROP S_CHECK;*(4) 用SQL语言分别在3个数据表中插入数据（具体数据可见教材P.82）：INSERTINTO StudentVALUES(200215121,李勇,男,20,CS);INSERTINTO StudentVALUES(200215122,刘晨,女,19,CS);INSERTINTO StudentVALUES(200215123,王敏,女,18,MA);INSERTINTO StudentVALUES(200215125,张立,男,19,IS);INSERTINTO CourseVALUES(1,数据库,5,4);INSERTINTO CourseVALUES(2,数学,NULL,2);INSERTINTO CourseVALUES(3,信息系统,1,4);INSERTINTO CourseVALUES(4,操作系统,6,3);INSERTINTO CourseVALUES(5,数据结构,7,4);INSERTINTO CourseVALUES(6,数据处理,NULL,2);INSERTINTO CourseVALUES(7,PALCAL语言,6,4);INSERTINTO SCVALUES(200215121,1,92);INSERTINTO SCVALUES(200215121,2,85);INSERTINTO SCVALUES(200215121,3,88);INSERTINTO SCVALUES(200215122,2,90);INSERTINTO SCVALUES(200215122,3,80);(5) 创建索引： 在Student表上创建按sage降序排列的索引NON_Stusage；CREATE INDEX NON_Stusage ON Student(Sage DESC); 在Student表上为Sno列创建一个聚簇索引CLU_Stusno；CREATE CLUSTERED INDEX CLU_Stusno ON Student(Sno); 在Student表上为Sname列创建一个聚簇索引CLU_Stusname；（能否创建成功？为什么？）不能创建，因为已经存在一个聚簇索引，一个基本表中只能存在一个聚簇索引。 在Course表上创建按ccredit升序排列的唯一性索引；CREATE UNIQUE INDEX UNI_Ccre ON Course(Ccedit); 在SC表上按Sno升序和Cno降序建立唯一索引UN_SC。CREATE UNIQUE INDEX UN_SC ON SC(Sno,Cno DESC);(6)删除索引CLU_Stusno、UN_SC：DROP INDEX Student.CLU_Stusno;DROP INDEX SC.UN_SC(7) 删除Teacher表DROP TABLE Teacher;(8) 保存SQL文件，文件名：SC-姓名学号后四位.sql，如SC-xukai3112.sql（二）使用命令行方式建立SPJ数据库(1) 用SQL语言建立SPJ数据库，数据库命名：SPJBASE-学号后四位及姓名，如SPJBASE-3112liyan；CREATE DATABASE SPJBASE_1224cxjON (NAME = SPJBASE_1224cxj_dat,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL SC_1214fanglingzhi _dat.mdf)LOG ON (NAME= SPJBASE_1224cxj_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL SC_1214zhuyameng _log.ldf)(2) 用SQL语言在其中建立S表、P表、J表和SPJ表（要求进行完整性定义），表结构如下；厂家 S（SNO，SNAME，STATUS，CITY），其中STATUS（状态）为整型，其他均为字符型产品 P（PNO，PNAME，COLOR，WEIGHT），其中WEIGHT（重量）为浮点型，其他均为字符型工程 J（JNO，JNAME，CITY），其中各属性均为字符型供应 SPJ（SNO，PNO，JNO，QTY），其中QTY（供应量）为整型，其他均为字符型建立S表：CREATE TABLE Ss (SNO CHAR(9) PRIMARY KEY, SNAME CHAR(40), STATUS INT, CITY CHAR(20) );建立P表：CREATE TABLE P (PNO CHAR(9) PRIMARY KEY, PNAME CHAR(40), COLOR CHAR(20), WEIGHT FLOAT );建立J表：CREATE TABLE J (JNO CHAR(9) PRIMARY KEY, JNAME CHAR(40), CITY CHAR(20) );建立SPJ表：CREATE TABLE SPJ (SNO CHAR(9), PNO CHAR(9), JNO CHAR(9), QTY INT, PRIMARY KEY(SNO,PNO,JNO), CONSTRAINT FK_SPJ1 FOREIGN KEY(SNO) REFERENCES S(SNO), CONSTRAINT FK_SPJ2 FOREIGN KEY(PNO) REFERENCES P(PNO), CONSTRAINT FK_SPJ3 FOREIGN KEY(JNO) REFERENCES J(JNO) );*(3) 用SQL语言分别在4个数据表中输入一定的记录；省略(4) 保存SQL文件，文件名：SPJ-姓名学号后四位.sql，如SPJ-xukai3112.sql（三）针对学生课程数据库查询(1) 查询全体学生的学号与姓名。SELECT Sno,SnameFROM Student;(2) 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFROM Student;(3) 查询全体学生的详细记录。SELECT *FROM Student;(4) 查全体学生的姓名及其出生年份。SELECT Sname, SageFROM Student;(5) 查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。SELECT Sname,Sage,LOWER(Sdept)FROM Student;(6) 使用列别名改变查询结果的列标题。SELECT Sname NAME,Year of Birth: BIRTH,2000-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENTFROM Student;(7) 查询选修了课程的学生学号。SELECT DISTINCT SnoFROM SC;(8) 查询所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname,Sage FROM Student WHERE Sage20;(9) 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage BETWEEN 20 AND 23;(10) 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23;(11) 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECT Sname,SsexFROM StudentWHERE Sdept IN(IS,MA,CS);(12) 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECT Sname,SsexFROM StudentWHERE Sdept NOT IN (IS,MA,CS );(13) 查询所有姓刘学生的姓名、学号和性别。SELECT Sname,Sno,SsexFROM StudentWHERE Sname LIKE 刘%;(14) 查询学号为20080711的学生的详细情况。（具体的学号值根据表中数据确定）SELECT * FROM Student WHERE Sno=20060701;(15) 查询姓“欧阳”且全名为三个汉字的学生姓名。SELECT SnameFROM StudentWHERE Sname LIKE 欧阳_;(16) 查询名字中第2个字为“晨”字的学生的姓名和学号。SELECT Sname,SnoFROM StudentWHERE Sname LIKE _晨%;(17) 查询所有不姓刘的学生姓名。SELECT Sname,Sno,SsexFROM StudentWHERE Sname NOT LIKE 刘%;(18) 查询DB_Design课程的课程号和学分。SELECT Cno,CcreditFROM CourseWHERE Cname LIKE DB_Design ESCAPE;(19) 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。SELECT *FROM CourseWHERE Cname LIKE DB_%i_ _ ESCAPE;(20) 查询缺少成绩的学生的学号和相应的课程号。SELECT Sno,CnoFROM SCWHERE Grade IS NULL;(21) 查所有有成绩的学生学号和课程号。SELECT Sno,CnoFROM SCWHERE Grade IS NOT NULL;(22) 查询计算机系年龄在20岁以下的学生姓名。SELECT SnameFROM StudentWHERE Sdept=CS AND Sage=20 AND Sage3;(34) 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。SELECT Sno, COUNT(*)FROM SCWHERE Grade=90GROUP BY SnoHAVING COUNT(*)=3;(35) 查询学生20060711选修课程的总学分。SELECT Sum(Ccredit)FROM SC,CourseWHERE Sno=20060711 AND SC.Cno=Course.Cno;(36) 查询每个学生选修课程的总学分。SELECT Sum(Ccredit)FROM SC,CourseWHERE SC.Cno=Course.CnoGROUP BY Sno;(37) 查询每个学生及其选修课程的情况。SELECT Student.*,SC.*FROM Student,SCWHERE Student.Sno=SC.Sno;(38) 查询每一门课的间接先修课（即先修课的先修课）。SELECT FIRST.Cno,SECOND.CpnoFROM Course FIRST,Course SECONDWHERE FIRST.Cpno=SECOND.Cno;(39) 查询每个学生及其选修课程的情况包括没有选修课程的学生（用外连接）。SELECT Student.*, SC.*FROM Student join SC on Student.Sno = SC.Sno;(40) 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。SELECT Student.Sno, Student.SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90;(41) 查询每个学生的学号、姓名、选修的课程名及成绩。SELECT Student.Sno,Sname,Cname,GradeFROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;(42) 查询与“刘晨”在同一个系学习的学生（分别用嵌套查询和连接查询）。嵌套查询：SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept= (SELECT Sdept FROM Student WHERE Sname=刘晨);连接查询：FROM Student S1,Student S2WHERE S1.Sdept=S2.Sdept AND S2.Sname=刘晨;(43) 查询选修了课程名为“信息系统”的学生学号和姓名。SELECT S1.Sno,S1.Sname,S1.SdeptSELECT Sno,SnameFROM StudentWHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname=信息系统);(44) 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄。SELECT Sname,SageFROM StudentWHERE SageANY(SELECT Sage FROM Student WHERE Sdept=IS) AND SdeptIS;(45) 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用ALL谓词和集函数。ALL谓词：SELECT Sname,SageFROM StudentWHERE SageALL(SELECT Sage FROM Student WHERE Sdept=IS) AND SdeptIS;集函数：SELECT Sname,SageFROM StudentWHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept=IS) AND Sdept IS;(46) 查询所有选修了1号课程的学生姓名。（分别用嵌套查询和连查询）嵌套查询：SELECT SnameFROM StudentWHERE Sno IN (SELECT Sno FROM SC WHERE Cno=1);连接查询：SELECT Student.SnameFROM Student,SCWHERE Student.Sno=SC.Sno AND C.Cno=1;(47) 查询没有选修1号课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1);(48) 查询选修了全部课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE SC.Sno=Student.Sno AND Cno=Course.Cno);(49) 查询至少选修了学生95002选修的全部课程的学生号码。SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS(SELECT * FROM SC SCY WHERE SCY.Sno=95002AND NOT EXISTS(SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)AND Sno20060702;或：SELECT SnoFROM StudentWHERE NOT EXISTS (SELECT * FROM SC SCX WHERE SCX.Sno=95002AND NOT EXISTS(SELECT * FROM SC SCY WHERE SCY.Sno=Student.Sno AND SCY.Cno=SCX.Cno) AND Sno95002;(50) 查询计算机科学系的学生及年龄不大于19岁的学生的信息。SELECT *FROM StudentWHERE Sdept=CSUNION SELECT *FROM StudentWHERE Sage=19;(51) 查询选修了课程1或者选修了课程2的学生的信息。SELECT SnoFROM SCWHERE Cno=1UNIONSELECT SnoFROM SCWHERE Cno=2;(52) 查询计算机科学系中年龄不大于19岁的学生的信息。SELECT *FROM StudentWHERE Sdept=CSINTERSECTSELECT *FROM StudentWHERE Sage=19;(53) 查询既选修了课程1又选修了课程2的学生的信息。SELECT SnoFROM SCWHERE Cno=1INTERSECTSELECT SnoFROM SCWHERE Cno=2;(54) 查询计算机科学系的学生与年龄不大于19岁的学生的差集。SELECT SnameFROM StudentEXCEPTSELECT TnameFROM Teacher;（四）针对SPJ数据库查询1查询为工程 J1 供货的厂商号，并按厂商号升序。SELECT SNOFROM SPJWHERE JNO=J1 ORDER BY SNO ASC;2查询供货量在 300 - 500 之间的所有供货情况。SELECT *FROM SPJWHERE QTY BETWEEN 300 AND 500;3查询重量 15 以下, Paris 生产的产品号和产品名。SELECT P.PNO,PNAMEFROM S,P,SPJWHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND WEIGHT(SELECT STATUS FROM S WHERE SNAME=Clark);5查询供应P1，P2两种产品的厂家名。SELECT SNAMEFROM S, SPJWHERE S.SNO=SPJ.SNO AND PNO=P1INTERSECTSELECT SNAMEFROM S,SPJWHERE S.SNO=SPJ.SNO AND PNO=P2;6查询由 S1 提供产品的工程名。SELECT DISTINCT JNAMEFROM J,SPJWHERE SPJ.JNO=J.JNO AND SNO=S1;7查询使用了颜色为 Red 产品，在城市Athens的工程名。SELECT JNAMEFROM JWHERE CITY=Athens AND JNO IN(SELECT JNO FROM SPJ WHERE PNO IN(SELECT PNO FROM P WHERE COLOR=Red);8求使用了全部零件的工程名。SELECT JNAMEFROM JWHERE NOT EXISTS(SELECT * FROM P WHERE NOT EXISTS(SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO);9查询未采用由 London 供应者提供颜色为 Red零件的工程名。SELECT JNAMEFROM JWHERE NOT EXISTS(SELECT * FROM S,P,SPJ WHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND SPJ.JNO=J.JNO AND CITY=London AND COLOR=Red);10查询全部由厂家 S2 提供零件的工程名。SELECT JNAMEFROM JWHERE NOT EXISTS(SELECT * FROM S WHERE SNO=S2 AND NOT EXISTS(SELECT * FROM SPJ WHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO);11求供给 London 的所有工程的零件名。SELECT PNAMEFROM PWHERE NOT EXISTS(SELECT * FROM J WHERE CITY=London AND NOT EXISTS (SELECT *FROM SPJWHERE SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO);12查询由London的厂商供给London的工程的产品名。SELECT PNAMEFROM S,P,J,SPJWHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND SPJ.JNO=J.JNO AND S.CITY=London AND J.CITY=London;13查询由供应者 S1 提供零件的工程项目总数。SELECT COUNT(DISTINCT JNO)FROM SPJWHERE SNO=S1;14查