实验四-SQL语言-SELECT查询操作

实验四 SQL语言 习题一 习题二

USE DATABASE jxgl

USE jxgl

Create 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

(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),

CONSTRAINT C_F FOREIGN KEY(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 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 VALUES('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',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);

1

课程表Course（课程号Cno,课程名Cname,先修课号Cpno,学分Ccredit）

学生表Student（学号Sno,姓名Sname,年龄Sage,性别Ssex,所在系Sdept）

学生选课表SC（学号Sno,课程号Cno,成绩Grade)

（1）基于“教学管理”数据库jxgl，使用SQL的查询语句表示下列查询： ①检索年龄大于23岁的男学生的学号和姓名； select Sno,Sname from Student

where Sage>'23' AND Ssex='男';

②检索至少选修一门课程的女学生的姓名； select Sname from Student,SC

where Ssex='女' AND Student.Sno=SC.Sno

2

group by Student.Sname having count(*)>=1;

或者

Select Sname From Student Where Ssex='女' AND Sno in (select sno from SC group by sno

having count(*)>=1);

③检索王同学不学的课程的课程号； select Cno from Course

where Course.Cno not in (select Cno

from SC,Student

where SC.Sno=Student.Sno AND Sname LIKE '王%');

④检索至少选修两门课程的学生学号； select DISTINCT Student.Sno from Student,SC

WHERE Student.Sno=SC.Sno

GROUP BY Student.Sno HAVING COUNT(*)>=2;

⑤检索全部学生都选修的课程的课程号与课程名； 注 参考 课本p111 例题46 SELECT Cno,Cname from Course where not exists (select * from student where not exists

3

(select * from SC

where SC.sno=Student.Sno AND SC.Cno=Course.Cno) )

或者假设所有学生只有两人 SELECT Cno,Cname from Course

WHERE Course.Cno in (select Cno from SC

group by SC.Cno having count(Sno)= (select count(*) from Student));

SELECT Cno,Cname from Course

WHERE Course.Cno in (select Cno from SC

group by SC.Cno

having count(Sno)=2);

⑥检索选修了所有3学分课程的学生学号。 select distinct Student.Sno from Student,SC where exists (select * from Course

where Ccredit ='3' AND Student.Sno=SC.Sno AND SC.Cno=Course.Cno);

4

(2)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询： ①统计有学生选修的课程门数； select count(distinct SC.Cno) FROM SC;

②求选修4号课程的学生的平均年龄； SELECT avg(Student.Sage) from Student,SC

where Student.Sno=SC.Sno AND Cno='4';

SELECT avg(Student.Sage) as 平均年龄 from Student,SC

where Student.Sno=SC.Sno AND Cno='3';

③求学分为3的每门课程的学生平均成绩； SELECT avg(SC.Grade) from Course,SC,Student

where Student.Sno=SC.Sno AND Course.Ccredit='3' group by SC.Cno

用group by 语句以课程号分组 注意，如果程序是这样的： SELECT avg(SC.Grade) from Course,SC,Student

where Student.Sno=SC.Sno AND Course.Ccredit='3'AND Course.Cno=SC.Cno;

5

只显示一门课程的成绩！

④统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列； SELECT Cno,count(Sno) from SC

GROUP BY Cno

HAVING Count(Sno)>3

order by count(sno)DESC,Cno;

SELECT Cno,count(Sno) as 选修人数 from SC GROUP BY Cno HAVING Count(Sno)>1

order by count(sno)DESC,Cno;

⑤检索学号比“王林”同学大而年龄比他小的学生姓名； SELECT Sname FROM Student where Sno> (select Sno from Student

where Sname='王林') AND Sage< (select Sage from Student

where Sname='王林'); 或者也可以 select X.sname

from Student as X,Student as Y

where X.Sno>Y.Sno AND X.Sage⑥检索姓名以“王”开头的所有学生的姓名和年龄； select Sname,Sage

6

from Student

where Student.Sname like '王%';

注意：like那里不能用等号“=”

⑦在SC中检索成绩为空值的学生学号和课程号； SELECT Sno,Cno from SC

where Grade is null

注意：is null 那里不能用等号“=”

⑧求年龄大于女学生平均年龄的男学生姓名和年龄； SELECT Sname,Sage from Student

where Sage>(select avg(Sage) from student where Ssex='女') AND Ssex='男';

⑨求年龄大于所有女学生年龄的男学生姓名和年龄； SELECT Sname,Sage from Student

where Sage>(SELECT MAX(Sage) from Student where Ssex='女') AND Ssex='男';

⑩检索所有比“王林”年龄大的学生姓名、年龄和性别； SELECT Sname,Sage,Ssex from Student

where Sage>(select Sage from Student

where Sname='王林')

7

⑾检索选修“2”课程的学生中成绩最高的学生的学号； SELECT Sno,Grade from SC

where Grade=(select MAX(Grade) from SC

where Cno='2');

注意：不能写成Grade=MAX(Grade)的形式，因为不存在！Count,min,avg也是 ⑿检索学生姓名以及所选修课程的课程号和成绩； select Sname,Cno,Grade from SC,Student

where Student.Sno=SC.Sno;

⒀检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。

选择4门以上 注意：选择2门课（包括两门） select sno,sum(Grade) sum select sno,sum(Grade) from SC from SC where grade>=60 where grade>=60 AND Sno in AND Sno in (select Sno (select Sno from SC from SC group by Sno group by Sno having count(Sno)>4) having count(Sno)>=2) group by sno group by sno order by sum(grade) desc order by sum(grade) desc

8

再注意：

Select sno,sum(grade) From SC

Where grade>=60

Group by sno having count(sno)>1 Order by sum(grade) desc

因为输入的数据分数都大于60，所以结果与上面的例子相同。但是，当分数小于60时，有课不及格的同学的学号会被删除，统计不完整。

9