数据库SQL语言学习--上级练习1（数据查询）
上机练习1
1、 启动SQL Server 2008中的 SQL Server Management Studio。
2、 创建数据库Studentdb；
3、 在数据库Studentdb中用SQL的DDL语句创建三张基本表，分别如下：
（注意：其中涉及列名均需自行重命名）
学生信息表（Student）：
|
列名 |
数据类型 |
长度 |
是否可空 |
备注 |
|
学号Sno |
char |
4 |
N |
PRIMARY KEY |
|
姓名Sname |
varchar |
8 |
N |
|
|
性别 Sgender |
char |
2 |
Y |
男/女 |
|
出生日期 Sbirth |
date |
|
Y |
|
|
家庭地址Saddr |
varchar |
50 |
Y |
|
|
身高 Sheight |
decimal(3,2) |
|
Y |
|
|
备注 Memo |
text |
|
Y |
|
课程表（Course）：
|
列名 |
数据类型 |
长度 |
是否可空 |
备注 |
|
课程编号 Cno |
Char |
4 |
N |
主键 |
|
课程名称 Cname |
varchar |
50 |
N |
|
|
学分 Ccredit |
int |
|
Y |
|
成绩表（Score）：
|
列名 |
数据类型 |
长度 |
是否可空 |
备注 |
|
学号 Sno |
Char |
4 |
N |
主键 |
|
课程编号 Cno |
char |
4 |
N |
主键 |
|
分数 Cscore |
Decimal(3,1) |
|
Y |
|
4、 将下列数据输入各个表中
|
学号 |
姓名 |
性别 |
出生日期 |
家庭地址 |
身高 |
备注 |
|
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 |
5、 使用SQL语句修改学生信息表（Student）中字段属性如下：
姓名 varchar 20 N
ALTER TABLE Student ALTER COLUMN Sname VARCHAR(20);
6、 使用SQL语句删除学生信息表（Student）中“备注”字段；
ALTER TABLE Student DROP COLUMN Memo;
7、 查询全体学生的详细记录；
SELECT *
FROM Student;
8、 查询学生信息表中学生的姓名和地址信息；
SELECT Sname,Saddr
FROM Student;
9、 查询学生信息表中“刘”姓学生的信息；
SELECT *
FROM Student
WHERE Sname LIKE '刘%';
10、 查询学生信息表中姓名含“民”的学生的信息；
SELECT *
FROM Student
WHERE Sname LIKE '%民%';
11、 查询所有身高1.75以上的男学生的学号和姓名；
SELECT Sno, Sname
FROM Student
WHERE Sgender='男' AND Sheight>=1.75;
12、 查询所有来自“宁波”的学生姓名、性别和年龄；
SELECT Sname, Sgender, YEAR(getdate())-DATENAME(yyyy,Sbirth) FROM Student
WHERE Saddr LIKE '%宁波%';
SELECT Sname,Ssex,2019-YEAR(Sbirth) as Sage
From Student
WHERE Saddress Like '%宁波%';
13、 查询没有考试成绩的学生学号和课程编号；
SELECT Sno, Cno
FROM Score
WHERE Cscore IS NULL;
14、 查询所有参加过考试的学生学号；
SELECT DISTINCT(Sno)
FROM Score
WHERE Cscore IS NOT NULL;
15、 查询所有学分不小于3的课程名；
SELECT Cname
FROM Course
WHERE Ccredit>=3;
16、 查询学分在1~5范围内的课程编号和课程名；
SELECT Cno, Cname
FROM Course
WHERE Ccredit BETWEEN 1 AND 5;
17、 查询“数据库技术”课程的信息；
SELECT *
FROM Course
WHERE Cname='数据库技术';
18、 计算“0003”号课程中所有参加过考试的学生平均分；
SELECT AVG(Cscore)
FROM Score
WHERE Cno='0003';
19、 查询选修“0002”号课程的学生的成绩最高分；
SELECT MAX(Cscore)
FROM Score
WHERE Cno='0002';
20、 统计参加“0001”号课程考试的学生人数；
SELECT COUNT(*)
FROM Score
WHERE Cno='0001';
21、 查询每一门课的间接先修课（即选修课先修课的先修课）；
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST ,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
22、 查询每个学生及其选修课程的情况（包括没有选修课程的学生）；
SELECT *
FROM Student
LEFT OUTER JOIN Score ON (Student.Sno= Score.Sno);
23、 查询每门选修课以及选修该课程的学生信息；
SELECT Score.Cno,Student.*
FROM Student,Score
WHERE Student.Sno=Score.Sno
ORDER BY Score.Cno;
24、 查询选修了3门课以上的学生姓名。
SELECT Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM Score
GROUP BY Sno
HAVING COUNT(*)>3);
