SQL程序练习题及答案

已知一学生成绩信息库，其中库里包含以下四个数据表，按要求完成以下各题的代码程序设计。

学生基本信息表：

课程信息表：

选课表：

表名：slt_couse 说明：选课表

院系信息表：

代码要求如下：

1、分别用T-SQL语句写出上述几个表的创建过程。

学生基本信息表

USE test

GO

CREATE TABLE student

(

st_id nVarChar(9) primary key NOT NULL ,

st_nm nVarChar(8) NOT NULL ,

st_sex nVarChar(2) NULL ,

st_birth datetime NULL ,

st_score int NULL ,

st_date datetime NULL ,

st_ from nVarChar(20) NULL ,

st_dpid nVarChar(2) NULL ,

st_ mnt tinyint NULL

)

创建课程信息表：

CREATE TABLE couse

(

cs_id nVarChar(4) primary key NOT NULL ,

cs_nm nVarChar(20) NOT NULL ,

cs_tm int NULL ,

cs_sc int NULL

)

选课表：

CREATE TABLE couse

(

cs_id nVarChar(4) NOT NULL ,

st_id nVarChar(9) NOT NULL ,

score int NULL ,

sltdate datetime NULL

)

院系信息表：

USE test

GO

CREATE TABLE dept

(

dp_id nVarChar(2) NOT NULL ,

dp_nm nVarChar(20) NOT NULL ,

dp_drt nVarChar(8) NULL ,

dp_tel nVarChar(12) NULL

)

2、向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567。

INSERT INTO dept VALUES('11', '自动控制系', '李其余', '81234567')

3、修改student表记录，将王小五的入学成绩改为88

UPDATE student

SET st_score=88

WHERE st_nm='王小五'

4、删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除

DELETE FROM slt_couse WHERE cs_id='1234' AND st_id='070201001'

5、查询所有系的信息

SELECT * FROM dept

6、在查询student表时使用列表达式：入学成绩+400。

SELECT st_id, st_nm, st_score, st_score+400 AS new_score

FROM student

7、使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话

SELECT dp_id AS 系号, dp_nm AS 系名, dp_drt AS 系主任, dp_tel AS联系电话FROM dept

8、显示所有学生学号、姓名、性别和班级(学号前6位)

SELECT st_id, st_nm, st_sex, LEFT(st_id, 6) AS 班级

FROM student

9、显示所有学生班级(学号前6位)

SELECT DISTINCT LEFT(st_id,6) AS 班级 FROM student

10、显示前25%条学生记录信息

SELECT TOP 25 PERCENT * FROM student

11、显示前n条学生记录信息，n为局部变量

DECLARE @n INT

SET @n = 4

SELECT TOP @n * FROM student

12、查询student表中2008年及以后入学的学生信息。

SELECT * FROM student

WHERE DATEPART( yy, st_date )>= 2008

13、查询student表中非11系的学生信息。

SELECT * FROM student WHERE NOT (st_dpid = '11')

14、查询2007年入学的11系所有男生信息。

SELECT * FROM student

WHERE DATEPART(yy,st_date)=2007 AND st_dpid='11' AND st_sex='男'

15、查询11系和12系的学生信息。

SELECT * FROM student

WHERE st_dpid='11' OR st_dpid='12'

16、查询所有“计算机”开头的课程信息

SELECT * FROM couse WHERE cs_nm LIKE '计算机%'

17、查询所有由三个字组成的“王”姓学生信息。

SELECT * FROM student WHERE st_nm LIKE '王__'

18、查询所有课程名中包含“信息”的课程信息。

SELECT * FROM couse WHERE cs_nm LIKE '%信息%'

19、查询学生姓名介于王姓到张姓的信息。

SELECT * FROM student

WHERE st_nm LIKE '[王-张]%'

20、查询在1989.7.1到1990.6.30之间出生的学生信息。

SELECT st_id, st_nm, st_sex, st_birth FROM student

WHERE st_birth BETWEEN '1981.7.1' AND '1999.6.30'

21、查询所有生源为非空的学生信息。

SELECT * FROM student WHERE st_from IS NOT NULL

22、查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序。

SELECT st_id, score FROM slt_corse

WHERE cs_id='1001' AND score IS NOT NULL

ORDER BY score DESC

23、查询陕西籍学生，将结果保存在新表st_shanxi。

SELECT * INTO st_shanxi

FROM student

WHERE st_from='陕西省'

24、用局部变量@stage保存学生张三的年龄。

DECLARE @stage int

SELECT @stage = DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)

FROM student

WHERE st_nm='张三'

25、求所修课程总学分在20分以上的姓名。

select st_nm,sum(cs_sc) as 学分合计

From student join slt_couse on student.st_id= slt_couse. student.st_id join couse on couse.cs_id= slt_couse.cs_id

Group by st_nm

Having sum(cs_sc) >=20

26、统计各班人数。

SELECT LEFT(st_id,6) AS 班级, COUNT(st_id) AS 人数

FROM student

GROUP BY LEFT(st_id,6)

27、用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩。

SELECT a.st_id, st_nm, cs_nm, score

FROM student a, slt_couse b, couse c

WHERE a.st_id = b.st_id AND b.cs_id = c.cs_id

ORDER BY a.st_id

28、用左外连接查询没有选修任何课程的学生学号、姓名。

SELECT a.st_id, st_nm, score

FROM student a LEFT OUTER JOIN slt_couse b ON a.st_id = b.st_id

WHERE b.cs_id IS NULL

ORDER BY b.st_id

29．查询选修了1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序。

SELECT st_id, st_nm, st_sex FROM student

WHERE st_id IN

( SELECT st_id FROM slt_couse

WHERE cs_id='1002' AND score < 60 )

ORDER BY st_nm

通过子查询实现：——使用比较运算符

SELECT st_id, st_nm, st_sex

FROM student a

WHERE

( SELECT score FROM slt_couse b

WHERE a.st_id = b.st_id AND cs_id = '1002' ) < 60

ORDER BY st_nm

30、查询选修了1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序

通过子查询实现：——使用IN关键字。

SELECT st_id, st_nm, st_sex FROM student

WHERE st_id IN

( SELECT st_id FROM slt_couse

WHERE cs_id='1002' AND score < 60 )

ORDER BY st_nm

通过子查询实现：——使用比较运算符

SELECT st_id, st_nm, st_sex

FROM student a

WHERE

( SELECT score FROM slt_couse b

WHERE a.st_id = b.st_id AND cs_id = '1002' ) < 60

ORDER BY st_nm

31、查询系主任为“赵虎”的系的所有学生信息。

SELECT * FROM student

WHERE EXISTS

( SELECT * FROM dept

WHERE st_dpid = dp_id AND dp_drt='赵虎' )

通过子查询实现：——=运算符

SELECT * FROM student

WHERE st_dpid =

( SELECT dp_id FROM dept WHERE dp_drt='赵虎' )

32、将没有被选修的课程的学分更改为0。

UPDATE couse SET cs_sc=0

WHERE cs_id NOT IN

( SELECT DISTINCT cs_id FROM slt_couse )

33、删除学分为0的选课信息。

DELETE FROM slt_couse

WHERE cs_id IN

( SELECT DISTINCT cs_id FROM slt_couse WHERE cs_id=0 )