数据库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);