数据库SQL语言学习--上机练习2（连接查询 嵌套查询）
上机练习2
1、 启动SQL Server 2008中的 SQL Server Management Studio。
2、 针对下面三张基本表进行操作：
学生信息表（Student）：
|
列名 |
数据类型 |
长度 |
是否可空 |
备注 |
|
学号 |
char |
4 |
N |
PRIMARY KEY |
|
姓名 |
varchar |
8 |
N |
|
|
性别 |
char |
2 |
Y |
男/女 |
|
出生日期 |
date |
|
Y |
|
|
家庭地址 |
varchar |
50 |
Y |
|
|
身高 |
decimal(3,2) |
|
Y |
|
|
备注 |
text |
|
Y |
|
课程表（Course）：
|
列名 |
数据类型 |
长度 |
是否可空 |
备注 |
|
课程编号 |
Char |
4 |
N |
主键 |
|
课程名称 |
varchar |
50 |
N |
|
|
学分 |
int |
|
Y |
|
成绩表（Score）：
|
列名 |
数据类型 |
长度 |
是否可空 |
备注 |
|
学号 |
Char |
4 |
N |
主键 |
|
课程编号 |
char |
4 |
N |
主键 |
|
分数 |
Decimal(3,1) |
|
Y |
|
3、 将下列数据输入各个表中
|
学号 |
姓名 |
性别 |
出生日期 |
家庭地址 |
身高 |
备注 |
|
0001 |
刘一平 |
男 |
1990-10-1 |
温州市环城西路201号 |
1.78 |
|
|
0002 |
张得民 |
男 |
1990-12-2 |
杭州市下沙路22号 |
1.65 |
|
|
0003 |
马东 |
男 |
1990-7-4 |
宁波市中山北道20号 |
1.71 |
|
|
0004 |
肖海燕 |
女 |
1990-3-15 |
温州市越秀北路43号 |
1.65 |
|
|
0005 |
张民华 |
女 |
1991-5-13 |
宁波市艮山路7号 |
1.63 |
|
|
课程编号 |
课程名称 |
学分 |
|
0001 |
计算机基础 |
2 |
|
0002 |
管理学原理 |
3 |
|
0003 |
数据库技术 |
3 |
|
0004 |
项目管理 |
2 |
|
0005 |
毕业论文 |
10 |
|
学号 |
课程编号 |
分数 |
|
0001 |
0001 |
80.0 |
|
0001 |
0002 |
90.0 |
|
0001 |
0003 |
70.0 |
|
0001 |
0004 |
85.0 |
|
0002 |
0001 |
78.0 |
|
0002 |
0002 |
NULL |
|
0002 |
0003 |
77.0 |
|
0002 |
0004 |
67.0 |
|
0003 |
0001 |
66.0 |
|
0003 |
0002 |
76.0 |
|
0003 |
0003 |
NULL |
|
0003 |
0004 |
73.0 |
4、 为Student表添加“系名”字段，存储数据如：“信息系”，“数学系”，“计算机系”等，具体数据可自行添加到Student表中；
ALTER TABLE Student
ADD Sdept char(10);
5、 统计各个课程号及相应的选课人数；
SELECT Cno,COUNT(Sno)
FROM Score
GROUP BY Cno;
6、 统计没有参加考试的学生学号和姓名；
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM Score
WHERE Sscore IS NULL);
7、 查询选修了3门课以上的学生姓名；
SELECT Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM Score
GROUP BY Sno
HAVING COUNT(*)>3);
8、 统计选修人数在3人以上的课程号和课程名；
SELECT Cno,Cname
FROM Course
WHERE Cno IN
(SELECT Cno
FROM Score
GROUP BY Cno
HAVING COUNT(*)>3);
9、 查询每个学生的学号、姓名、选修的课程名及成绩；
SELECT Student.Sno,Sname,Cname,Cscore
FROM Student,Score,Course
WHERE Student.Sno=Score.Sno AND Score.Cno=Course.Cno;
10、 查询选修了课程号为“0003”的学生姓名和住址；
SELECT Sname,Saddr
FROM Student,Score
WHERE Score.Sno=Student.Sno AND Score.Cno='0003';
11、 查询与“刘一平”来自同一个系的学生姓名；
SELECT S2.Sname
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S1.Sname='刘一平';
12、 查询其它系中课程‘0002’比信息系所有学生分数高的学生学号和姓名；
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM Score
WHERE Score.Cno='0002' AND Cscore>
(SELECT MAX(Cscore)
FROM Student,Score
WHERE Student.Sno=Score.Sno AND Cno='0002' AND Sdept='信息系'
)
AND Sdept<>'信息系');
13、 查询其它系中比信息系所有学生年龄大的学生姓名和性别；
SELECT Sname, Sgender
FROM Student
WHERE 2019-YEAR(Sbirth)>ALL
(SELECT 2019-YEAR(Sbirth)
FROM Student
WHERE Sdept='信息系'
) AND Sdept<>'信息系' ;
14、 查询每门课程中低于该课程平均成绩的学生学号和姓名；
SELECT DISTINCT Score.Sno,Sname
FROM Student,Score
WHERE Student.Sno = Score.Sno
AND Score.Cscore<
(SELECT avgscore
FROM
(SELECT Cno,AVG(Cscore)
FROM Score
GROUP BY Cno)
AS AVGSC(Cno,avgscore)
WHERE Cno = Score.Cno);
SELECT Student.Sno,Sname,Cno
FROM Student,Score A
WHERE Student.Sno = A.Sno
AND Cscore<
(SELECT AVG(Cscore)
FROM Score B
WHERE A.Cno = B.Cno);
我的:
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM Score A
WHERE Cscore<
(SELECT AVG(Cscore)
FROM Score B
WHERE A.Cno = B.Cno));
15、 查询“信息系”中选课最多的学生学号；
SELECT Score.Sno
FROM Score ,Student
WHERE Sdept ='信息系'
AND Score.Sno=Student.Sno
GROUP BY Score.Sno
HAVING COUNT(*)>=ALL
(SELECT COUNT(*)
FROM Score,Student
WHERE Sdept='信息系'
AND Score.Sno=Student.Sno
GROUP BY Score.Sno);
SELECT TOP 1 Score.Sno, COUNT(*) NumOfCourse
FROM Student,Score
WHERE Sdept='信息系' AND student.Sno=Score.Sno
GROUP BY Score.Sno
ORDER BY NumOfCourse DESC ;
SELECT TOP 1 Student.Sno
FROM
(SELECT Sno,COUNT(*)
FROM Score
GROUP BY sno)
AS st(sno,cnt),Student
WHERE st.sno=Student.Sno AND Student.Sdept='信息系'
ORDER BY st.cnt DESC ;
16、 查询有两门及以上课程不及格的学生学号和姓名；
SELECT Sname,Sno
FROM Student
WHERE Sno IN
(SELECT s1.Sno
FROM
(SELECT *
FROM Score
WHERE Cscore<60) s1
GROUP BY s1.Sno
HAVING COUNT(*)>1);
我的:
SELECT Sno,Sname
FROM Student
WHERE
(SELECT COUNT(*) FROM Score WHERE Student.Sno=Score.Sno AND
(Cscore<60 OR Cscore=NULL))>=2;
17、 查询比“0002”号课程平均分高的其它课程信息；
我的：
SELECT Course.*
FROM Course,Score A
WHERE Course.Cno = A.Cno AND
A.Cno!='0002' AND
(SELECT AVG(Cscore)
FROM Score B
WHERE A.Cno=B.Cno)>
(SELECT AVG(Cscore)
FROM Score
WHERE Cno ='0002')
SELECT *
FROM Course
WHERE Cno IN
(SELECT Cno
FROM Score
GROUP BY Cno
HAVING Cno <> '0002' AND AVG(Cscore)>
(SELECT AVG(Cscore)
FROM Score
GROUP BY Cno
HAVING Cno = '0002'));
18、 查询选修了所有课程的学生学号和姓名。
我的：
SELECT Sno,Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM Score
WHERE Sno = Student.Sno
AND Cno=Course.Cno));
SELECT Sno
FROM Score
GROUP BY Sno
HAVING COUNT(*) =
(SELECT COUNT(*)
FROM Course);
