数据库实验——数据库数据更新及查询

在保证需操作的数据库中已经存在XSKC模式下的数据表student、course、sc

1、修改数据表信息

1)修改student表，将cs系姓名为“李咏”的学生姓名为“李勇”

UPDATE XSKC.student
SET sname = ‘李勇’
WHERE sdept=‘CS’ AND sname=‘李咏’

2)将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”

 UPDATE XSKC.student

SET sname=‘王丹丹’,ssex=‘女’,sage=20,sdept=‘MA’
WHERE sno=‘200515010’

3)修改course表,将“数据处理”的学分改为3学分

UPDATE XSKC.COURSE
SET ccredit=3
WHERE cname=‘数据处理’

4)修改CS表，将选修课程“1”的同学成绩加5分

UPDATE XSKC.sc
SET grade=grade+5
WHERE cno=1

5)删除数据表student中无专业的学生记录

DELETE FROM XSKC.student
WHERE sdept IS NULL

6)删除数据表course中学分低于1学分的课程信息

DELETE
FROM XSKC.course
WHERE ccredit<1

2.请使用T-SQL 语句实现以下操作

1)列出姓“王”且全名为3个汉字的学生的基本信息

USE student
SELECT *
FROM XSKC.student
WHERE sname like ‘王__’

2)查询在1986年以后出生的学生的学号和姓名

USE student
SELECT sno,sname
FROM XSKC.student
WHERE sage<2019-1986

3)查询没有分配院系的学生的姓名和学号

USE student
SELECT sno,sname
FROM XSKC.student
WHERE sdept IS NULL

4)按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩

USE student
SELECT *
FROM XSKC.sc
WHERE grade BETWEEN 70 AND 80
ORDER BY cno DESC,grade DESC

5)按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明

USE student
SELECT 院系=
case
when sdept=‘CS’ then ‘计算机系’
when sdept=‘IS’ then ‘信息系’
when sdept=‘MA’ then ‘数学系’
when sdept=‘EN’ then ‘外语系’
when sdept=‘CM’ then ‘中医系’
when sdept=‘WM’ then ‘西医系’
else ‘院系不明’
end,sno as ‘学号’,sname as ‘姓名’,ssex as ‘性别’,sage as ‘年龄’
FROM XSKC.student
ORDER BY 院系 DESC

6)查询同时选修“1”号课程和“2”号课程的所有学生的学号

USE student
SELECT sno
FROM XSKC.sc
WHERE cno=1 AND sno in(SELECT sno FROM XSKC.sc WHERE Cno=2)

7)查询所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”

USE student
SELECT distinct sdept,院系规模=
case
when count()>5 then ‘规模较大’
when count()between 4 and 5 then ‘规模一般’
when count(*)between 2 and 4 then ‘规模较小’
end
FROM XSKC.student
WHERE sdept IS NOT NULL
GROUP BY sdept

8)查询学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”

USE student
SELECT 学生总人数=COUNT(distinct sno),平均年龄=AVG(sage)
FROM XSKC.student

9)查询选修的课程数大于3的各个学生的选修课程数

USE student
SELECT DISTINCT 学号=sno,选课数=COUNT(cno)
FROM XSKC.sc
GROUP BY sno
HAVING COUNT(cno)>3

10)查询IS系学生的学号、姓名、性别、年龄，并统计出IS系的学生个数

USE student
SELECT sno 学号,sname 姓名,ssex 性别,sage 年龄
FROM XSKC.student
SELECT distinct IS系的学生个数=COUNT(*)
FROM XSKC.student
WHERE sdept=‘IS’

11)查询每个院系的学生前两条记录，并组成新表ceshi

   USE student

SELECT a.*
INTO ceshi
FROM XSKC.student a
WHERE sno IN
(SELECT top 2 sno
FROM XSKC.student
WHERE sdept=a.sdept
order by sno)

12)查询平均成绩大于“赵菁菁”平均成绩的各个学生的学号、平均成绩

USE student
SELECT sno 学号,AVG(grade) 平均成绩
FROM XSKC.sc
GROUP BY sno
HAVING AVG(grade)>(SELECT AVG(grade)from XSKC.sc,XSKC.student where sname=‘赵菁菁’)

实验步骤

命令方式：在【SQL Server Management Studio】窗口左上方选择【新建查询】按钮，启动SQL编辑器窗口，在光标处输入T-SQL语句，单击【执行】按钮。例如第一题可输入：
select * from student where sname not like ‘刘%’