数据库实验二

--①查询信息管理系学生的学号和姓名。
--select sno,sname from Student where sdept = 'IS'

--②查询选修了课程的学生的学号。
--select distinct sc.sno from sc,student

--③查询选修了课程号为C001的学生的学号和成绩，并对查询结果按成绩降序排列，如果成绩相同，则按学号升序排列。
--SELECT sc.sno, sc.grade 
--FROM sc 
--JOIN course ON sc.cno = course.cno 
--JOIN student ON sc.sno = student.sno 
--WHERE sc.cno = 'C001' 
--ORDER BY sc.grade DESC, sc.sno ASC;

--④查询选修课程号为C001的成绩在80-90分的学生的学号和成绩，并将成绩乘以系数0.8输出。
--select sc.Sno,sc.grade*0.8 as grade
--from sc 
--JOIN course ON sc.cno = course.cno 
--join student ON sc.sno = student.sno 
--where sc.cno = 'C001' and sc.grade between 80 and 90

--⑤查询通信工程系或计算机系中姓张的学生的信息。
--select *from student where (sdept = 'IM' or sdept = 'IS') and sname like '张%'
--⑥查询缺少了成绩的学生的学号和课程号。
--select sno,Cno from sc where grade is null

--⑦查询成绩最高的前3名学生的学号、成绩。
--select top 3 sno,grade from sc order by grade desc 

--⑧查询选修了课程号为C001的课程的成绩最差的3名学生的学号和成绩。
--select top 3 sno,grade from sc where cno = 'C001' order by grade asc

--⑨统计各系2000年以后出生的学生的总人数，并按总人数进行降序排列。
--SELECT sdept, COUNT(*) as total_students
--FROM student
--WHERE DATEDIFF(YEAR, '2000-01-01', GETDATE()) - sage >= 0
--GROUP BY sdept
--ORDER BY total_students DESC;

--（2）连接查询：参见实验指导书P40
--①查询每个学生的情况以及他（她）所选修的课程。
--select student.sno,sname,sgender,sage,sc.cno,cname,Ccredit,grade from sc 
--JOIN course ON sc.cno = course.cno 
--join student ON sc.sno = student.sno 

--②查询学生的学号、姓名、选修的课程名及成绩。
--SELECT student.sno, student.sname, course.cname, sc.grade
--FROM student
--JOIN sc ON student.sno = sc.sno
--JOIN course ON sc.cno = course.cno;
 
--③查询选修了JAVA课程且成绩在90分以上的学生的学号、姓名及成绩。
--select sc.sno,sname,grade from sc 
--join student on sc.sno = student.Sno 
--join course on sc.cno = course.cno
--and cname = 'JAVA' and grade>90

--④查询每一门课程的间接先修课（即先修课的先修课）。
--select b.Cpno from course a,course b where a.Cpno = b.cno

--⑤查询选修了课程名以“计算机”开头的课程的学生信息。
--select sc.sno,sname,sgender,sage,sdept from sc 
--join student on sc.sno = student.Sno 
--join course on sc.cno = course.cno
--where cname like '计算机%'

--⑥查询各系各门课程的平均成绩。
--select Sdept,cname,AVG(grade) from sc 
--join student on sc.sno = student.Sno
--join course on sc.cno = course.cno 
--group by Sdept,cname

--⑦查询选修Java和计算机文化学的学生的学号、姓名和这两门课的平均成绩。
--SELECT sc.sno, student.sname, 
--  (AVG(CASE WHEN course.cname = 'JAVA' THEN sc.grade ELSE NULL END) + 
--   AVG(CASE WHEN course.cname = '计算机文化学' THEN sc.grade ELSE NULL END)) / 2 AS avg_grade
--FROM sc
--JOIN student ON sc.sno = student.sno
--JOIN course ON sc.cno = course.cno
--WHERE course.cname IN ('Java', '计算机文化学')
--GROUP BY sc.sno, student.sname
--HAVING COUNT(DISTINCT course.cname) >= 2;

--⑧统计每个学生的选课门数（包括没选课的学生），列出学号、选课门数和选课情况，其中选课情况显示规则为：
--a.如果选课门数大于或等于6门，则选课情况为：较多；
--b.如果选课门数在3-5门之间，则选课情况为：一般；
--c.如果选课门数在1-2门，则选课情况为：较少；
--d.如果没有选课，则选课情况为：未选课。
--select student.sno,count(sc.cno),
--(case when count(sc.Cno)>=6 then '较多' 
--when count(sc.Cno)>=3 and count(sc.Cno)<=5 then'一般'
-- when count(sc.Cno)>=1 and count(sc.Cno)<=2 then'较少'
--  else '未选课' end)from student 
-- LEFT JOIN sc ON student.sno = sc.sno
--group by student.sno

--（3）嵌套查询：参见实验指导书P40
--①查询选修了JAVA课程的学生的学号和姓名。
--方法一
--select sc.sno,sname from sc
--join student on sc.sno = student.sno 
--join course on course.cno = sc.cno
--and cname = 'JAVA'

--方法二
--select sno,sname from student where sno = 
--(select sno from sc where cno = 
--(select cno from course where cname = 'JAVA') )

--②查询其它系中年龄小于计算机系年龄最大者的学生信息。
--select  * from student where sage<(select max(sage) from student where sdept = 'IS') and sdept !='IS'
--③查询其它系中比计算机系的学生年龄都小的学生信息。
--select  * from student where sage<(select min(sage) from student where sdept = 'IS') and sdept !='IS'
--④查询选修课程号为C001的课程的成绩高于张海的学生的学号和成绩。
--select sno,grade from sc where cno = 'C001'
-- and grade>(select grade from sc where cno = 'C001' 
-- and sno =(select sno from student where sname = '张海'))
-- 子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，
-- 或子查询用作表达式时，这种情况是不允许的。

--⑤查询和李勇的高等数学课程分数相同的学生的学号。
--select sno from sc where grade = 
--(select grade from sc where sno = 
--(select sno from student where sname = '李勇')
--and cno = (select cno from course where cname = '高等数学'))

--⑥查询没有选修c002课程的学生的姓名。
--select sname from student where sno!=(select sno from sc where cno = 'C002')

--⑦查询年龄大于女同学平均年龄的男同学的姓名和年龄。
--select sname,sage from student where
-- sage>(select AVG(sage) from student where sgender = '女') and sgender = '男'

--⑧查询选修JAVA课程的学生成绩比此课程的平均成绩高的学生的学号和成绩。
--select sno,grade from sc where
-- grade>(select AVG(grade) from sc 
-- where cno = (select cno from course where cname = 'JAVA')) 
-- and cno = (select cno from course where cname = 'JAVA')

--（4）增加、删除、修改记录
--①　向表Student中插入一个学生元组，属性值自拟。
--insert into student values ('98008','刘备','男','35','MA')

--②　从表SC中删除尚无成绩的选课元组。
--delete sc where grade is null

--③　检索每门课程成绩都大于80分的学生学号、姓名和性别，结果存入Stu表。
--SELECT student.sno, sname, sgender
--FROM student 
--JOIN sc ON student.sno = sc.sno
--GROUP BY student.sno, sname, sgender
--HAVING MIN(grade) > 80;
-- group by 有一个原则,就是 select 
-- 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。

--④　把低于总平均成绩的女同学成绩提高5%。
--UPDATE sc
--SET grade = grade * 1.05
--WHERE sno IN (
--  SELECT sno
--  FROM student
--  WHERE sgender = '女'
--) AND grade < (
--  SELECT AVG(grade)
--  FROM sc
--  JOIN student ON sc.sno = student.sno
--  WHERE student.sgender = '女'
--);

--⑤　把钱小平同学的有关信息全部删掉。
--delete from student where sname = '钱小平'
--⑥　把选修JAVA课不及格的成绩全改为0。
--update sc set grade = 0 where grade<60 and
-- cno = (select cno from course where cname='JAVA')