查询

-- 1、查询全体学生的学号和姓名
SELECT Sno, Sname FROM Student

-- 2、查询全体学生的姓名、学号、所在系，要求修改列名
select Sno AS 学号, Sname 姓名, 所在系 = Sdept from Student 

-- 3、查询所有学生的信息（使用*表示所有列）
select * from Student

-- 4、查询全体学生的姓名及其出生年份（GetDate()函数获取系统日期时间）
select Sname, Year(GetDate()) - Sage 出生年份 from Student

-- 5、查询选修了课程的学生学号
select distinct Sno from SC

-- 6、查询计算机系全体学生的名单
select Sname from Student where Sdept = 'cs'

-- 7、查询所有年龄在20岁以下的学生姓名及其年龄
select Sname, Sage from Student where Sage < 20

-- 8、查询考试成绩有不及格的学生的学号
select distinct Sno from SC where Grade < 60

-- 9、查询年龄在20至23岁之间的学生的姓名、系别、和年龄（between...and）
select Sname, Ssex, Sage from Student where Sage between 20 and 23

-- 10、查询年龄不在20至23岁之间的学生的姓名、系别、和年龄
select Sname, Ssex, Sage from Student where Sage not between 20 and 23

-- 11、查询信息系(IS)和计算机科学系(CS)的学生的姓名和性别(使用in)
select Sname, Ssex from Student where Sdept in('cs', 'is')

-- 12、查询所有姓“刘”的学生的姓名、学号和性别
select Sname, Sno, Ssex from Student where Sname like '刘%'

-- 13、查询姓“张”且全名为二个汉字的学生的姓名
select Sname from Student where Sname like '张_'
select Sname from Student where Sname like '[刘-张]%'
select Sname from Student where Sname like '[^刘-张]%'

create table City(
	cityName varchar(10),
	cityZip char(6) check(cityZip like '[0-9][0-9][0-9][0-9][0-9][0-9]') --邮编为6位数字
)
-- 14、查询CS系年龄在22岁以下的学生姓名
select Sname from Student 
where Sdept = 'CS' and Sage < 22

-- 15、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列
select Sno, Grade from SC where Cno = '3' 
order by Grade desc

-- 16、查询全体学生情况，查询结果按所在系降序排列，对同一系中的学生按年龄降序排列
select * from Student 
order by Sdept desc, Sage desc

-- 17、查询学生总人数
--聚合函数：count, sum, avg, max, min
select count(*) from Student

--查询多少个系
select count(distinct Sdept) from Student  --重复值不累计计数

-- 18、查询选修了课程的学生人数
select count(distinct Sno) from SC

-- 19、计算2号课程的学生平均成绩 
select avg(Grade) from SC where Cno = '2'

-- 20、查询各个课程号与相应的选课人数
--分组：group by
select Cno, count(*) from SC group by Cno

--查询学生的学号与相应的选课门数
select Sno, count(*) from SC group by Sno

-- 21、查询选修了2门以上课程的学生的学号
select Sno, count(*) from SC group by Sno
having count(*) > 2

-- 22、查询没有先修课的课程信息
select * from Course where Cpno is null
select * from Course where Cpno is not null

-- 23、查询学生的学号，姓名，课程号和成绩
select Student.Sno, Sname, Cno, Grade
from Student, SC
where Student.Sno = SC.Sno

-- 24、查询学生的学号，姓名，课程名和成绩
select Student.Sno, Sname, Cname, Grade
from Student, Course, SC
where Student.Sno = SC.Sno and Course.Cno = SC.Cno

-- 25、查询每一门课的间接先修课（即先修课的先修课）
select A.Cno, B.Cpno
from Course A, Course B
where A.Cpno = B.Cno

-- 26、查询选修2号课程且成绩在85分以上的所有学生的学号和姓名
select Student.Sno, Sname 
from Student, SC
where Student.Sno = SC.Sno and Grade > 85 and Cno = '2'

-- 27、查询每个学生及其选修课程的情况
--内连接
select Student.Sno, Sname, Cno, Grade
from Student, SC
where Student.Sno = SC.Sno

select Student.Sno, Sname, Cno, Grade
from Student inner join SC
on Student.Sno = SC.Sno

-- 28、查询每个学生及其选修课程的情况（即使没有选课也列出该学生的基本情况）
select Student.Sno, Sname, Cno, Grade
from Student left join SC
on Student.Sno = SC.Sno

-- 29、查询选修了1号课程的学生情况（比较：on与where的区别）
select Student.Sno, Sname, Cno, Grade
from Student left join SC
on Student.Sno = SC.Sno and Cno = '1'

select Student.Sno, Sname, Cno, Grade
from Student left join SC
on Student.Sno = SC.Sno 
where Cno = '1'

-- 30、查询与张立在同一个系学习的学生（无关子查询）
select * from Student 
where Sdept = (select Sdept from student where Sname = '张立')

-- 31、查询选修了1号课程的学生的学号和姓名（要求用子查询实现）
select Sno, Sname from Student 
where Sno in (select Sno from SC where Cno = '1')

-- 32、询选修了“数据库”课程的学生的学号和姓名（要求用子查询实现）
select Sno, Sname from Student 
where Sno in (select Sno from SC 
where Cno = (select Cno from Course where Cname = '数据库'))

-- 33、查询成绩比该门课程的平均成绩要低的学生的学号、课程号和成绩（相关子查询）
select Sno, Cno, Grade from SC A
where Grade < (select avg(Grade) from SC B where A.Cno = B.Cno)

-- 34、查询选修了1号课程的学生姓名（三种方法）
select Sname from Student
where Sno in(select Sno from SC where Cno = '1')

select Sname from Student, SC
where Student.Sno = SC.Sno and Cno = '1'

select Sname from Student
where exists(select * from SC where SC.Sno = Student.Sno and Cno = '1')

-- 35、查询选修了全部课程的学生姓名
select Sname from Student
where not exists(select * from Course where not exists
   (select * from SC where Student.Sno = SC.Sno and Course.Cno = SC.Sno))

-- 36、查询CS系的学生及年龄小于21岁的学生（要求使用并操作）
select * from Student where Sdept = 'CS'
union
select * from Student where Sage < 20

-- 37、 查询CS系的学生且年龄小于21岁的学生（要求使用交操作）
select * from Student where Sdept = 'CS'
intersect
select * from Student where Sage < 21

-- 38、查询非CS系且年龄小于21岁的学生（要求使用差操作）
select * from Student where Sage < 21
except
select * from Student where Sdept = 'CS'
--等价于
select * from Student where Sage < 21 and Sdept != 'cs'

-- 39、向Student表中插入记录：（学号：95020；姓名：王桃群；性别：女；所在系：CS；年龄：23岁）
insert into Student values('95020', '王桃群', '女', 'CS', 23)

-- 40、向SC表张插入一条选课记录('95020', '1')
insert into  SC(Sno, Cno) values('95020', '1')
insert into  SC values('95020', '1', null)

-- 41、将每个系学生的平均年龄插入到DeptAge表中
create table DeptAge(
   Sdept char(2),
   AvgAge int
)
insert DeptAge(Sdept, AvgAge) select Sdept, AVG(Sage) from Student group by Sdept


-- 42、数据表Temp、Test事先不存在
select * into Temp from Student              --系统会自动创建Temp表，并将Student表中的全部数据复制到Temp表中
select * from Test from Student where 1 = 2  --系统会自动创建Test表，其结构与Student表相同，但无记录

-- 43、数据表Temp需事先存在
insert into Temp select * from Student       --将Student表中的全部数据复制到Temp表中

-- 42、将学生95001的年龄改为22岁
update Student set Sge = 22 where Sno = '95001'

-- 43、将计算机科学系全体学生的成绩置0
update SC set Grade = 0 where Sno in (select Sno from Student where Sdept = 'CS')

-- 44、删除所有成绩不及格的选课记录
delete from SC where Grade < 60