实验3数据查询.pdf

《实验3数据查询.pdf》由会员分享，可在线阅读，更多相关《实验3数据查询.pdf（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 西安财经学院信息学院 数据库原理 实验报告 实验名称 数据查询 实验室 实验日期 一、实验目的及要求 1、掌握关系数据库的各种查询方法，理解其含义，包括嵌套查询、连接查询等；2、对关系数据库进行查询，包括嵌套查询、连接查询；二、实验环境 硬件平台：PC；软件平台：Windows 2000/MICROSOFT SQLSERVER；三、实验内容 在实验 1 和实验 2 的基础上，练习查询语句的使用，包括简单查询、嵌套查询、连接查询及 EXISTS 查询等，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。1在表 S、C、SC 上进行简单查询；2在表 S、C

2、、SC 上进行连接查询；3在表 S、C、SC 上进行嵌套查询；4使用聚合函数的查询；5对数据的分组查询；6对数据的排序查询。具体内容：在表 S，C，SC 上完成以下查询：1 查询学生的基本信息；2 查询“CS”系学生的基本信息；3 查询“CS”系学生年龄不在 19 到 21 之间的学生的学号、姓名；4 找出所有学生中的最大年龄；5 找出“CS”系年龄最大的学生，显示其学号、姓名；6 找出各系年龄最大的学生，显示其学号、姓名；7 统计“CS”系学生的人数；姓名 学号 班级 年级 指导教师 8 统计各系学生的人数，结果按升序排列；9 按系统计各系学生的平均年龄，结果按降序排列；10 查询每门课程的

3、课程名；11 查询无先修课的课程的课程名和学时数；12 统计无先修课的课程的学时总数；13 统计每位学生选修课程的门数、学分及其平均成绩；14 统计选修每门课程的学生人数及各门课程的平均成绩；15 找出平均成绩在 85 分以上的学生，结果按系分组，并按平均成绩的升序排列；16 查询选修了“1”或“2”号课程的学生学号和姓名；17 查询选修了“1”和“2”号课程的学生学号和姓名；18 查询选修了课程名为“数据库系统”且成绩在 60 分以下的学生的学号、姓名和成绩；19 查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；20 查询没有选修课程的学生的基本信息；21 查询选

4、修了 3 门以上课程的学生学号；22 查询选修课程成绩至少有一门在 80 分以上的学生学号；23 查询选修课程成绩均在 80 分以上的学生学号；24 查询选修课程平均成绩在 80 分以上的学生学号；25 选做：针对 SPJ 数据库中的四个表 S，P，J，SPJ，完成教材 P74-75-习题 5中的查询及教材 P127-习题 5 中的查询。四、实验步骤 1打开 SQL server；2进入查询分析器；3创建 S 数据库；4建立 S、C、SC 三个学生关系表；5根据要求，对三个关系表进行操作；6用 SQL 命令语句完成实验要求，详见源程序；7将调试过程及结果，截图保存。五、调试过程及实验结果 查询

5、学生的基本信息 查询“CS”系学生的基本信息 查询“CS”系学生年龄不在 19 到 21 之间的学生的学号、姓名 找出所有学生中的最大年龄 找出“CS”系年龄最大的学生，显示其学号、姓名 找出各系年龄最大的学生，显示其学号、姓名 统计“CS”系学生的人数 统计各系学生的人数，结果按升序排列 按系统计各系学生的平均年龄，结果按降序排列 查询每门课程的课程名 查询无先修课的课程的课程名和学时数 统计无先修课的课程的学时总数 统计每位学生选修课程的门数、学分及其平均成绩 统计选修每门课程的学生人数及各门课程的平均成绩 找出平均成绩在 85 分以上的学生，结果按系分组，并按平均成绩的升序排列 查询选修

6、了“1”或“2”号课程的学生学号和姓名 查询选修了“1”和“2”号课程的学生学号和姓名 查询选修了课程名为“数据库系统”且成绩在 60 分以下的学生的学号、姓名和成绩 查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）查询没有选修课程的学生的基本信息 查询选修了 3 门以上课程的学生学号 查询选修课程成绩至少有一门在 80 分以上的学生学号 查询选修课程成绩均在 80 分以上的学生学号 查询选修课程平均成绩在 80 分以上的学生学号 六、总结 通过本次实验，我基本掌握了 SQL 语句中的各种查询方式，简单查询、连接查询、嵌套查询、使用聚合函数的查询、对数据的分组查询、对

7、数据的排序查询等，但是对比较复杂的查询方式，还不能熟练运用，特别是其 WHERE 条件，有时，还不能够准确描述，这将在以后的练习中有所加强。七、源程序清单 建立 S 数据库 CREATE DATABASE S;建立关系表 Student CREATE TABLE Student(Sno CHAR(9)PRIMARY KEY,Sname CHAR(20),Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20);建立关系表 Course create table Course (Cno char(4)primary key,Cname char(40),Cpno cha

8、r(4),Ccredit smallint,foreign key(Cpno)references Course(Cno);建立关系表 SC CREATE TABLE SC(Sno char(9),Cno char(4),Grade smallint,primary key(Sno,Cno),foreign key(Sno)references Student(Sno),foreign key(Cno)references Course(Cno);向学生表 Student 中插入数据 INSERT INTO Student VALUES(200215121,李勇,男,20,CS);INSERT

9、 INTO Student VALUES(200215122,刘晨,女,19,CS);INSERT INTO Student VALUES(200215123,王敏,女,18,MA);INSERT INTO Student VALUES(200215125,张立,男,19,IS);向课程表 Course 中插入数据 INSERT INTO Course VALUES(2,数学,NULL,2);INSERT INTO Course VALUES(6,数据处理,NULL,2);INSERT INTO Course VALUES(4,操作系统,6,3);INSERT INTO Course VALU

10、ES(7,PASCAL 语言,6,4);INSERT INTO Course VALUES(5,数据结构,7,4);INSERT INTO Course VALUES(1,数据库,5,4);INSERT INTO Course VALUES(3,信息系统,1,4);向学生选课表 SC 中插入数据 INSERT INTO SC VALUES(200215121,1,92);INSERT INTO SC VALUES(200215121,2,85);INSERT INTO SC VALUES(200215121,3,88);INSERT INTO SC VALUES(200215122,2,90)

11、;INSERT INTO SC(Sno,Cno,Grade)VALUES(200215122,3,80);1 查询学生的基本信息；SELECT*from student;2 查询“CS”系学生的基本信息；SELECT*from student where sdept=cs;3 查询“CS”系学生年龄不在 19 到 21 之间的学生的学号、姓名；SELECT sno,sname,sdept from student where sdept=cs and sage not between 19 and 21;4 找出所有学生中的最大年龄；SELECT Sno,Sname,Ssex,Sdept,Sa

12、ge MAXSage FROM Student WHERE Sage=(SELECT MAX(Sage)FROM Student );5 找出“CS”系年龄最大的学生，显示其学号、姓名；SELECT Sno,Sname,Ssex,Sdept FROM Student WHERE Sage=(SELECT MAX(Sage)FROM Student WHERE Sdept=cs );6 找出各系年龄最大的学生，显示其学号、姓名；SELECT Sdept,MAX(Sage)FROM Student GROUP BY Sdept;7 统计“CS”系学生的人数；SELECT Sdept,COUNT(*

13、)NumStudent FROM Student WHERE Sdept=CS GROUP BY Sdept;8 统计各系学生的人数，结果按升序排列；SELECT Sdept,COUNT(*)NumStudent FROM Student GROUP BY Sdept ORDER BY NumStudent ASC;9 按系统计各系学生的平均年龄，结果按降序排列；SELECT Sdept,AVG(Sage)AVGSage FROM Student GROUP BY Sdept ORDER BY AVGSage DESC;10 查询每门课程的课程名；SELECT Cno 课程编号,Cname 课

14、程名称 FROM Course;11 查询无先修课的课程的课程名和学时数；SELECT Cname,Ccredit FROM Course WHERE Cno!=ALL(SELECT Cno FROM Course WHERE Cpno!=NULL );12 统计无先修课的课程的学时总数；SELECT SUM(Ccredit)无先修课的课程学分总数 FROM Course WHERE Cno!=ALL(SELECT Cno FROM Course WHERE Cpno!=NULL );13 统计每位学生选修课程的门数、学分及其平均成绩；SELECT Sno,COUNT(SC.Cno)选修课程总

15、数,AVG(Grade)平均成绩,SUM(Ccredit)总学分 FROM Course,SC WHERE Course.Cno=SC.Cno GROUP BY Sno;14 统计选修每门课程的学生人数及各门课程的平均成绩；SELECT Cno,COUNT(Sno)课程选修人数,AVG(Grade)平均成绩 FROM SC GROUP BY Cno;15 找出平均成绩在 85 分以上的学生，结果按系分组，并按平均成绩的升序排列；SELECT SC.Sno,Sdept,AVG(Grade)平均成绩 FROM SC,Student WHERE Student.Sno=SC.Sno GROUP BY

16、 Sdept,SC.Sno HAVING AVG(Grade)85 ORDER BY AVG(Grade)ASC;16 查询选修了“1”或“2”号课程的学生学号和姓名；SELECT DISTINCT Student.Sno,Sname FROM SC,Student WHERE Student.Sno=SC.Sno AND Cno IN(1,2);17 查询选修了“1”和“2”号课程的学生学号和姓名；SELECT Student.Sno,Sname FROM SC,Student WHERE SC.Sno=Student.Sno AND Cno=1 AND Student.Sno IN (SELECT Student.Sno FROM SC,Student WHERE SC.Sno=Student.Sno AND Cno=2 );18 查询选修了课程名为“数据库系统”且成绩在 60 分以下的学生的学号、姓名和成绩；SELECT Student.Sno,Sname,Grade FROM SC,Student,Course WHERE SC.Sno=Student.Sno AND Course.Cno=SC.Cno AND Cname=数据库系统 AND Grade60;