数据库上机题汇总.doc

1、实验二 使用 SQL 语句创建和修改表系 专业班级 学号 姓名 成绩： 实验目的：1. 掌握使用 SQL 语句创建数据库。2. 掌握使用 SQL 语句创建数据表，创建各种完整性约束，修改表的结构。3. 掌握使用 SQL 语句修改表中的记录值。实验内容：一、 使用 SQL 语句完成下列题。1、创建一个名称为 JXGL 的数据库，要求：（1） 将主数据文件 JXGL.mdf 放置在 c:DBF 文件夹中，其文件大小自动增长为按 5mb 增长。（2） 将事务日志文件 JXGL_log.ldf 放置在 C:DBF 文件夹中，其文件大小自动增长为按 1mb 增长。在 c 盘创建 DBF 文件夹。crea

2、te database JXGLon(name=JXGL_data,filename=c:DBFJXGL.mdf,filegrowth=5mb)log on(name=JXGL_log,filename=c:DBFJXGL_log.ldf,filegrowth=1mb)2、在 JXGL 数据库中创建下列表。 学生表 Student，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记作：Student(Sno,Sname,Ssex,Sage,Sdept)其中主码为 Sno。姓名取值唯一，性别取值为男或女，默认为男。Create Tabl

3、e Student( Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),Sname VARCHAR(20) constraint uq_sname unique,Sage SMALLINT,Ssex CHAR(2) DEFAULT 男 CHECK (Ssex=男 OR Ssex=女),Sdept CHAR(2); 课程表 Course，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)，其中主码为 Cno。Cname 取值唯一,Cpno 参照 cno。C

4、reate Table Course ( Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),Cname VARCHAR(20) UNIQUE,Cpno CHAR(2) references course(cno),Ccredit SMALLINT); 学生 SC，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作：SC(Sno,Cno,Grade)其中主码为(SNO,CNO)，sno 参照 student 表的 sno 列，cno 参照 course 表的 cno 列。grade 取值范围 0-100。Create Table SC( Sno C

5、HAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno),Cno CHAR(2) NOT NULL Constraint fk_cno FOREIGN KEY(Cno) REFERENCES Course(Cno),Grade SMALLINT CHECK (Grade IS NULL) OR (Grade BETWEEN 0 AND 100),PRIMARY KEY(Sno,Cno),);3、向 Student 表增加“入学时间”列，其数据类型为日期型。Alter table studentAdd 入学时间 smal

6、ldatetime4、 将年龄的数据类型改为 smallint。Alter table studentAlter column sage smallint5、 删除学生姓名必须取唯一值的约束。Alter table studentDrop uq_sname6、向 course 表中增加一个约束，以限制 Ccredit 的取值范围为 1-4。Alter table courseAdd constraint ck_ccredit check(Ccredit between 1 and 4)7、向 student 表中插入值（ 98006， 欧阳姗姗 ，18， 女 ，CS） 。INSERT INTO

7、 student VAlues(98006,欧阳姗姗,18,女，CS)8、从 student 表中删除姓名为 李四的学生。Delete from studentWhere sname=李四9、在 SC 表中将2号课程的成绩加 5。Update scSet grade=grade+5Where cno=210、 在 SC 表中将2号课程的成绩减 5。Update scSet grade=grade-5Where cno=2实验三 单表查询（一）系 专业班级 学号 姓名 成绩： 实验目的：1、 掌握投影查询。2、 掌握选择查询。实验内容：对 JXGL 数据库进行查询。1、查询全体学生的学号与姓名。

8、select sno,snamefrom student2、查询全体学生的姓名、学号、所在系。select sname,sno,sdeptfrom student3、查询全体学生的详细记录。select *from student4、查全体学生的姓名及其出生年份。select sname,year(getdate()-sage 出生年份 from student5、查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。select sname,year(getdate()-sage 出生年份 ,lower(sdept) sdeptfrom student6、查询选修了课程的学生学号

9、。select distinct snofrom sc7、查询所有年龄在 20 岁以下的学生姓名及其年龄。select sname,sagefrom studentwhere sage=9014、 查询所有姓刘学生的姓名、学号和性别。Select sname,sno,ssexFrom studentWhere sname like 刘%15、 查询姓“欧阳“ 且全名为三个汉字的学生的姓名。Select snameFrom studentWhere sname like 欧阳_16、 查询名字中第 2 个字为“阳“字的学生的姓名和学号。Select sname,snoFrom studentWh

10、ere sname like _阳%17、 查询所有不姓刘的学生姓名。Select sname,sno,ssexFrom studentWhere sname not like 刘%18、 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。Select sno,cnoFrom scWhere grade is null19、 查所有有成绩的学生学号和课程号。Select sno,cnoFrom scWhere grade is not null实验四 单表查询（二）系 专业班级 学号 姓名 成绩： 实验目的：3、 掌握使用集函数的单表查询。

11、4、 掌握对查询结果排序与分组。实验内容：对 JXGL 数据库进行查询。1、查询学生总人数。select count(*) 总人数from student2、查询选修了课程的学生人数。select count(distinct sno) 人数from sc3、计算 1 号课程的学生平均成绩。select avg(grade) 平均分from scwhere cno=14、查询选修 1 号课程的学生最高分数。select max(grade) 最高分from scwhere cno=15、计算选修 1 号课程的学生人数、最高成绩、最低成绩及平均成绩。select count(*) 人数,max

12、(grade) 最高分 ,min(grade) 最低分,avg( grade) 平均分from scwhere cno=16、求各个课程号及相应的选课人数。select cno,count(*) 选课人数from scgroup by cno7、查询选修了 3 门以上课程的学生学号。select snofrom scgroup by snohaving count(*)38、查询有 3 门以上课程是 90 分以上的学生的学号。select snofrom scwhere grade90group by snohaving count(*)39、查询各专业的专业名及学生人数。select sde

13、pt,COUNT(*) 学生人数from studentgroup by sdept10、 查询平均成绩在 80 分以上的学生的学号及平均成绩select sno,AVG(grade) as 平均成绩from scgroup by snohaving AVG(grade)8011、 查询至少有 2 个人选修的课程的课程号，选课人数。 、select cno,COUNT(*) 选课人数from scgroup by cnohaving COUNT(*)=212、 查询选修了 3 号课程的学生的学号及其成绩，查询结果按分数降序排列。select sno,gradefrom scwhere cno=

14、3order by grade desc13、 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select *from studentorder by sdept asc,sage desc实验五 多表查询（一）系名 专业及班级 学号 姓名 成绩： 实验目的：5、 掌握连接查询的一般格式。6、 熟练掌握连接查询的各种方法。实验内容：对 JXGL 数据库进行查询。1. 查询选修 2 号课程的学生名字。select snamefrom student,scwhere student.sno=sc.sno and cno=22. 查询选修了这样课程的学生学号，其直接

15、先行课是 5 号课程。select snofrom sc,coursewhere o=o and cpno=53. 查询选修的课程成绩为 90 分以上的学生姓名与课程名和成绩。select sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and o=oand grade904. 查询每一门课的间接先修课（即先修课的先修课）select o,b.cpnofrom course a,course bwhere a.cpno=o5. 查询选修 2 号课程且成绩在 90 分以上的所有学生的学号、姓名。select stud

16、ent.sno,snamefrom student,scwhere student.sno=sc.sno and cno=2 and grade906. 查询每个学生的学号、姓名、选修的课程名及成绩。select student.sno,sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and o=o7. 查询性别为男、课程成绩及格的学生信息及课程号、成绩。select student.*,cno,gradefrom student,scwhere student.sno=sc.sno and grade=60 an

17、d ssex=男8. 查询平均成绩大于 85 分的学号、姓名、平均成绩。select student.sno,sname,avg(grade) 平均分from student,scwhere student.sno=sc.snogroup by student.sno,snamehaving avg(grade)859. 查询选修了数据库系统的学生的姓名及成绩。select sname,gradefrom student,course,scwhere student.sno=sc.sno and o=o and cname=数据库系统 10. 查询每个系的学生选修的 1 号课程的平均成绩，最高

18、分，最低分，选课人数。select avg(grade) 平均成绩 ,max(grade) 最高分,min(grade) 最低分 ,count(*) 选课人数 from student,scwhere student.sno=sc.sno and cno=1group by sdept实验六 多表查询（二）系名 专业及班级 学号 姓名 成绩： 实验目的：7、 掌握子查询语句的一般格式。8、 熟练掌握子查询的各种方法。实验内容：对 JXGL 数据库进行查询。11. 查询不选 1 号课程的学生名字。select snamefrom studentwhere sno not in(select sn

19、o from sc where cno=1)12. 查询至少被 2 个学生选修的课程的课程名。select cnamefrom coursewhere cno in(select cnofrom scgroup by cnohaving count(*)=2)13. 查询计算机系（CS）选修了 2 门及以上课程的学生的学号。select snofrom scwhere sno in(select sno from student where sdept=CS)group by snohaving count(*)=214. 查询与“钱横”在同一个系学习的学生信息。select *from st

20、udentwhere sname!=钱横 and sdept=(select sdept from student where sname=钱横 )15. 查询选修了课程名为“数学分析”的学生学号、姓名和所在系。select sno,sname,sdeptfrom studentwhere sno in(select snofrom scwhere cno in(select cnofrom coursewhere cname=数学分析 )16. 查询同时选修了“1”课程和“2”课程的学生的姓名。select snamefrom studentwhere sno in(select snofr

21、om scwhere cno=2 and sno in(select snofrom scwhere cno=1)17. 查询至少选修了 3 门课程的学生的姓名。select snamefrom studentwhere sno in(select snofrom scgroup by snohaving count(*)=3)18. 查询平均成绩在 80 分以上的学生的姓名。select snamefrom studentwhere sno in(select snofrom scgroup by snohaving avg(grade)80)实验七 视图系名 专业及班级 学号 姓名 成绩：

22、 实验目的：1. 掌握视图定义方法和使用方法。2. 掌握对视图数据的增，删，改操作。实验内容：19. 创建计算机系（CS）的学生信息视图 V_1。create view v_1asselect *from studentwhere sdept=CS20. 建立计算机系(CS)选修了 1 号课程的学生信息视图 V_2。create view v_2asselect *from studentwhere sdept=CS AND SNO IN(SELECT SNO FROM SC WHERE CNO=1)21. 建立计算机系选修了 1 号课程且成绩在 90 分以上的学生的视图 V_3。create

23、 view v_3asselect *from studentwhere sdept=CS AND SNO IN(SELECT SNO FROM SC WHERE CNO=1 AND GRADE90)22. 定义一个反映学生学号，姓名，出生年份的视图 V_4。CREATE VIEW V_4(学号,姓名,出生年份)ASSELECT SNO,SNAME,YEAR(GETDATE()-SAGEFROM STUDENT23. 将学生的学号及其平均成绩定义为一个视图 V_5。CREATE VIEW V_5(学号,平均成绩)ASSELECT SNO,AVG(GRADE)FROM SCGROUP BY SN

24、O24. 创建计算机专业(CS)学生的平均成绩视图 V_6,包括学号和平均成绩.CREATE VIEW V_6(学号,平均成绩)ASSELECT SNO,AVG(GRADE)FROM SCWHERE SNO IN(SELECT SNO FROM STUDENT WHERE SDEPT=CS)GROUP BY SNO25. 通过 V_2 视图查询计算机系选修了 1 号课程的学生姓名，性别，年龄。SELECT SNO,SNAME,SAGEFROM V_226. 在 V_5 视图中查询平均成绩在 90 分以上的学生学号和平均成绩。SELECT 学号,平均成绩FROM V_5WHERE 平均成绩 90

25、27. 将计算机系学生视图 V_1 中学号 98002 的学生姓名改为“ 刘辰”。UPDATE V_1SET SNAME=刘辰WHERE SNO=9800228. 向计算机系学生视图 V_1 中插入一个新的学生记录：95029，赵新，20 岁INSERT INTO V_1(SNO,SNAME,SAGE,SDEPT)VALUES(95029,赵新,20, CS)29. 删除视图 V_1 中学号为 95029 的记录。DELETE V_1WHERE SNO=95029实验八 索引实验目的：9、 掌握索引的类型10、 掌握使用界面方式和 SQL 语句创建索引，查看索引，删除索引的方法。实验内容：分别

26、使用界面方式和 SQL 语句完成下面题。1、为 student 表的 sname 列创建索引。Create index ind_sname on student(sname)2、为 student 表按 sname 的升序，sage 的降序建立索引。Create index ind_student on student(sname asc,sgae desc)3、根据 course 表的 cname 列降序创建唯一索引。Create unique index ind_cname on course(cname)4、根据 student 表中 sno 列创建唯一聚集索引。Create unique clustered index ind_sno on student(sno)5、删除 course 表 cname 列上的索引。Drop index ame6、使用存储过程查看 student 表上所有的索引。Exec Sp_helpindex student