2023年6月21日发(作者：)
设学生选课库中有三个数据表：
S（sno，sname，birthday，sdepartment，tel，sex）
SC（sno，cno，grade）
C（cno，cname，teacher，pcno）
完成下列查询任务：
1、检索至少选修课程“数据结构”和“C语言”的学生学号。
Select
From SC AS x, SC As y
Where = and
=(Select cno From C Where cname='数据结构') and
=(Select cno From C Where cname='C语言')
2、检索学习课程号为C02的学生学号和姓名。
Select ,sname
From S,SC
Where = and ='C02'
3、检索选修课程“数据库”的学生学号和姓名。
Select ,sname
From S,SC,C
Where = and = and ='数据库'
4、检索选修课程号为C02或C04的学生学号。
Select distinct
From S,SC
Where = and (='C02' or ='C04')
5、检索至少选修课程号为C02和C04的学生姓名。
Select sname From S Where sno in
(Select
From SC AS x, SC As y
Where = and ='C02' and ='C04')
6、检索没有选修课程“数据库”的学生姓名和年龄。
Select sname,year(getdate()) - year(birthday)
From S where not Exists(
Select * From C, SC
Where = and = and cname='数据库') 7、在SC表中检索男生选修的课程名。
Select distinct cname From S, SC, C
Where ='男' and = and =
8、检索每个学生的出生年份。
Select Year(birthday) From S
9、在S中检索学生的姓名和出生年份，输出的列名分别为STUDENT_NAME和BIRTH_YEAR。
Select sname as STUDENT_NAME, Year(birthday) as BIRTH_YEAR From S
10、向基本表SC中插入一条元组（S0404，C06，90）。
Insert Into SC Values('S0404', 'C06', 90)
11、把课程名为VB的成绩从基本表SC中删除。
Delete From SC Where cno =
(Select cno From C Where cname='VB')
12、把女生的成绩提高10%。
Update SC Set grade=grade*1.1
Where sno in(
Select sno From S Where sex='女'
)
13、列出选修课程超过3门的学生姓名及选修门数。
Select sname, count(*) From SC, S
Where =
Group By sname
Having count(*) > 3
14、求选修了各课程的学生人数。
Select cname, count(*) From SC, C
Where =
Group By cname
15、在SC中，求选修课程C01的学生的学号和得分，并将结果按分数降序排序。
Select sno, grade From SC Where cno='C01' order by Grade Desc
16、查找每个同学的学号及选修课程的平均成绩情况。
Select sno, AVG(Grade) From SC
Group By sno
17、列出学生所有可能的选课情况。
Select , sname, cname From S, SC, C Where = and =
18、列出每个同学的学号及选修课程的平均成绩情况，没有选修的同学也列出。
Select ,AVG(Grade)
From SC right Join S ON =
Group By
19、列出每个同学的学号及选修课程号，没有选修的同学也列出。
Select ,cno
From SC right Join S ON =
20、如果学号为J0404的学生成绩少于90分，则加上10分。
Update SC Set Grade=Grade+10 Where sno='J0404' and Grade<90
21、将成绩最低的学生成绩加上10分。
Update SC Set =+10 From
(Select * From SC Where Grade=(Select Min(Grade) From SC)) as t
Where = and =
22、将成绩最高前3名的学生成绩减去20分。
Update SC Set =-20 From
(Select Top 3 * From SC Order By Grade Desc) as t
Where = and =
23、将成绩最低前10%的学生成绩减去5分。
Update SC Set =-5 From
(Select Top 10 PERCENT * From SC Order By Grade ASC) as t
Where = and =
24、检索至少有两名男生选修的课程名。
Select cname From C Where cno in
(Select cno From SC, S
Where = and sex='男'
Group by
Having Count() > 1)
25、检索S中不姓“王”同学记录。
Select * From S Where sname not Like '王%'
26、检索和“李军”同性别并同班的同学姓名。
Select sname From S
Where sex=(Select sex From S Where sname='李军') and sdepartment=(Select sdepartment From S Where sname='李军')
and sno <>(Select sno From S Where sname='李军')
27、统计被学生选修的课程门数。
Select Count(distinct cno) From SC
28、求选修C04课程的学生的平均年龄。
Select AVG(Year(getdate()) - Year(Birthday)) From SC, S
Where ='C04' and =
29、求LIU老师所授课程的每门课程的学生平均成绩。
Select cno, AVG(Grade) From SC
Where cno in(
Select cno From C Where Teacher='Liu')
Group By cno
30、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
Select cno, Count(sno) as rs
From SC
Group by cno
Having Count(sno)> 10
Order By rs Desc,cno Asc
31、求年龄大于所有女同学年龄的男同学姓名和年龄。
Select sname,Year(getdate()) - Year(Birthday) From S
Where Year(Birthday) <
(Select Min(Year(Birthday)) From S Where sex='女')
32、把WANG同学的学习选课和成绩全部删去。
Delete From SC Where sno in(Select sno From S Where sname='WANG')
33、把选修MATHS课不及格的成绩全改为空值。
Update SC Set Grade = NULL
Where Grade<60 and cno = (Select cno From C Where cname='MATHS')
34、从学生表中删除成绩出现过 0 分的所有学生信息。
Delete From S Where sno in
(Select sno From SC Where Grade=0)
2023年6月21日发(作者：)
设学生选课库中有三个数据表：
S（sno，sname，birthday，sdepartment，tel，sex）
SC（sno，cno，grade）
C（cno，cname，teacher，pcno）
完成下列查询任务：
1、检索至少选修课程“数据结构”和“C语言”的学生学号。
Select
From SC AS x, SC As y
Where = and
=(Select cno From C Where cname='数据结构') and
=(Select cno From C Where cname='C语言')
2、检索学习课程号为C02的学生学号和姓名。
Select ,sname
From S,SC
Where = and ='C02'
3、检索选修课程“数据库”的学生学号和姓名。
Select ,sname
From S,SC,C
Where = and = and ='数据库'
4、检索选修课程号为C02或C04的学生学号。
Select distinct
From S,SC
Where = and (='C02' or ='C04')
5、检索至少选修课程号为C02和C04的学生姓名。
Select sname From S Where sno in
(Select
From SC AS x, SC As y
Where = and ='C02' and ='C04')
6、检索没有选修课程“数据库”的学生姓名和年龄。
Select sname,year(getdate()) - year(birthday)
From S where not Exists(
Select * From C, SC
Where = and = and cname='数据库') 7、在SC表中检索男生选修的课程名。
Select distinct cname From S, SC, C
Where ='男' and = and =
8、检索每个学生的出生年份。
Select Year(birthday) From S
9、在S中检索学生的姓名和出生年份，输出的列名分别为STUDENT_NAME和BIRTH_YEAR。
Select sname as STUDENT_NAME, Year(birthday) as BIRTH_YEAR From S
10、向基本表SC中插入一条元组（S0404，C06，90）。
Insert Into SC Values('S0404', 'C06', 90)
11、把课程名为VB的成绩从基本表SC中删除。
Delete From SC Where cno =
(Select cno From C Where cname='VB')
12、把女生的成绩提高10%。
Update SC Set grade=grade*1.1
Where sno in(
Select sno From S Where sex='女'
)
13、列出选修课程超过3门的学生姓名及选修门数。
Select sname, count(*) From SC, S
Where =
Group By sname
Having count(*) > 3
14、求选修了各课程的学生人数。
Select cname, count(*) From SC, C
Where =
Group By cname
15、在SC中，求选修课程C01的学生的学号和得分，并将结果按分数降序排序。
Select sno, grade From SC Where cno='C01' order by Grade Desc
16、查找每个同学的学号及选修课程的平均成绩情况。
Select sno, AVG(Grade) From SC
Group By sno
17、列出学生所有可能的选课情况。
Select , sname, cname From S, SC, C Where = and =
18、列出每个同学的学号及选修课程的平均成绩情况，没有选修的同学也列出。
Select ,AVG(Grade)
From SC right Join S ON =
Group By
19、列出每个同学的学号及选修课程号，没有选修的同学也列出。
Select ,cno
From SC right Join S ON =
20、如果学号为J0404的学生成绩少于90分，则加上10分。
Update SC Set Grade=Grade+10 Where sno='J0404' and Grade<90
21、将成绩最低的学生成绩加上10分。
Update SC Set =+10 From
(Select * From SC Where Grade=(Select Min(Grade) From SC)) as t
Where = and =
22、将成绩最高前3名的学生成绩减去20分。
Update SC Set =-20 From
(Select Top 3 * From SC Order By Grade Desc) as t
Where = and =
23、将成绩最低前10%的学生成绩减去5分。
Update SC Set =-5 From
(Select Top 10 PERCENT * From SC Order By Grade ASC) as t
Where = and =
24、检索至少有两名男生选修的课程名。
Select cname From C Where cno in
(Select cno From SC, S
Where = and sex='男'
Group by
Having Count() > 1)
25、检索S中不姓“王”同学记录。
Select * From S Where sname not Like '王%'
26、检索和“李军”同性别并同班的同学姓名。
Select sname From S
Where sex=(Select sex From S Where sname='李军') and sdepartment=(Select sdepartment From S Where sname='李军')
and sno <>(Select sno From S Where sname='李军')
27、统计被学生选修的课程门数。
Select Count(distinct cno) From SC
28、求选修C04课程的学生的平均年龄。
Select AVG(Year(getdate()) - Year(Birthday)) From SC, S
Where ='C04' and =
29、求LIU老师所授课程的每门课程的学生平均成绩。
Select cno, AVG(Grade) From SC
Where cno in(
Select cno From C Where Teacher='Liu')
Group By cno
30、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
Select cno, Count(sno) as rs
From SC
Group by cno
Having Count(sno)> 10
Order By rs Desc,cno Asc
31、求年龄大于所有女同学年龄的男同学姓名和年龄。
Select sname,Year(getdate()) - Year(Birthday) From S
Where Year(Birthday) <
(Select Min(Year(Birthday)) From S Where sex='女')
32、把WANG同学的学习选课和成绩全部删去。
Delete From SC Where sno in(Select sno From S Where sname='WANG')
33、把选修MATHS课不及格的成绩全改为空值。
Update SC Set Grade = NULL
Where Grade<60 and cno = (Select cno From C Where cname='MATHS')
34、从学生表中删除成绩出现过 0 分的所有学生信息。
Delete From S Where sno in
(Select sno From SC Where Grade=0)
发布评论