数据库第二次试验

1、南昌航空大学实验报告2016 年 4 月 14日课程名称：数据库系统概论 实验名称： SQL-select查询操作 学号： 14204129 姓名： 袁小峰 同组人： 指导教师评定： 签名： 一、实验目的：表或视图数据的各种查询（与统计）SQL命令操作，具体分为：1、 了解查询的概念和方法；2、 掌握SQL Sever集成管理器查询子窗口中执行SELECT操作方法；3、 掌握SELECT语句在单表查询中的应用；4、 掌握SELECT语句在多表查询中的应用；5、 掌握SELECT语句在复杂查询中的使用方法；二、实验内容与要求：请有选择地实践以下各题。（1）基于“教学管理”数据库jxgl，试用SQ

2、L的查询语句表达下列查询： Jxgl数据库的建立： 代码：Create database jxglUSE jxglGOCreate Table Student(Sno CHAR(5) not null primary key(Sno), Sname varchar(20), Sage smallint check(Sage>=15 AND Sage<=45), Ssex char(2) default'男'check(Ssex='男' OR Ssex='女' ), Sdept char(2); Create Table Course

3、(Cno char(2)NOT NULL primary key(Cno), Cname VARCHAR(20), Cpno char(2), Ccredit SMALLINT); Create Table SC (Sno char(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno), Cno CHAR(2) NOT NULL, Grade smallint check (Grade IS NULL)OR(Grade between 0 and 100), Primary key(Sno,Cno), foreign ke

4、y(Cno) references Course(Cno); insert into Student values('98001','钱横',18,'男','CS'); insert into Student values('98002','王林',19,'女','CS'); insert into Student values('98003','李民',20,'男','IS'); insert into

5、 Student values('98004','赵三',16,'女','MA'); insert into Course values('1','数据库系统','5',4); insert into Course values('2','数学分析',null,2); insert into Course values('3','信息系统导论','1',3); insert into Course val

6、ues('4','操作系统_原理','6',3); insert into Course values('5','数据结构','7',4); insert into Course values('6','数据处理基础',null,4); insert into Course values('7','C语言','6',3); insert into SC values('98001','1'

7、,87); insert into SC values('98001','2',67); insert into SC values('98001','3',90); insert into SC values('98002','2',95); insert into SC values('98002','3',88);图： Student表：Course表：SC表：1、 检索年龄大于23岁的男学生的学号和姓名；代码：select sno,sname 图：from s

8、tudent where sage>23 and ssex='男'2、 检索至少选修一门课程的学生的学号和姓名；代码：select sname from studentwhere sno in图： (select sno from sc )and ssex='女' 3、 检索王同学不学的课程的课程号；代码：图：select cno from courseexceptselect cnofrom sc where sno in (select sno from student where sname Like '王%' ); 4、 检索至少选

9、修两门课程的学生学号；代码：图：select distinct s1.snofrom sc s1,sc s2where s1.sno=s2.sno and o!=o; 5、 检索全部学生都选修的课程的课程号和课程名；代码：select o,cnamefrom sc,coursewhere o=o 图：group by o,cnamehaving count(*)= (select count(*) from Student ); 6、 检索选修了所有3学分课程的学生学号；代码：图：select sno from course,scwhere ccredit=3 and o=o; （2）基于“教

10、学管理”数据库jxgl，试用SQL的查询语句表达下列查询：1、统计所有学生选修的课程门数；代码：select Student.sno,count(cno)from Student left outer join SC on (Student.sno = SC.sno)group by Student.Sno;图： 2、求选修4号课程的学生的平均成绩；代码：图：select avg(sage) from studentwhere sno in (select sno from sc where cno='4' );3、求学分为3的每门课程的平均成绩；代码：图：select avg

11、(grade) from scwhere cno in (select cno from course where (ccredit=3);4、统计每门课程的学生选修人数，超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列；代码：图：select cno,count(sno) from scgroup by cnohaving count(*)>0order by count(*) desc,cno;5、 检索学号比王林同学大，而年龄他小的学生姓名；代码：图：select sname from studentwhere sno >

12、(select sno from student where sname='王林' ) and sage < (select sage from student where sname='王林' ); 6、 检索姓名以王字打头的所有学生姓名和年龄；代码：图：select sname,sage from studentwhere sname like '王%'7、 在SC中检索成绩为空值的学生学号和课程号；代码：图：select sno,cno from scwhere grade is null;8、 求年龄大于女同学平均年龄的男学生的姓名

13、和年龄；代码：图：select sname,sage from studentwhere ssex='男' and sage> (select avg(sage) from student where ssex='女' );9、 求年龄大于所有女同学年龄的男同学的姓名和年龄；代码：图：select sname,sage from studentwhere sage>all (select sage from student where ssex='女' );10、 检索所有比“王林”年龄大的学生姓名、年龄和性别；代码： 图：selec

14、t sname,sage,ssex from studentwhere sage> (select sage from student where sname='王林' );11、 检索选修2号课程的学生中成绩最高的学生的学号；代码：图：select sno from scwhere grade in (select max(grade) from sc where cno='2' ); 12、 检索学生姓名及其所选修课程的课程号和成绩；代码：图：select sname,cno,grade from student,scwhere student.sno=sc.sno; 13、 检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的