|
写在前面：本文中可能有错误，还请大佬斧正，如果小伙伴有什么好的方法，也可以发给我，我添加到里面
6、 综合练习 (习题部分)
（1） 查询选修课程“8105”且成绩在 80 到 90 之间的所有记录。 （2） 查询成绩为 76、86 或 96 的记录。 （3） 查询 9803 班的学生人数。 （4） 查询至少有 4 名学生选修的并以 81 开头的课程的平均成绩。 （5） 查询最低分大于 80，最高分小于等于 95 的 SNO 与平均分。 （6） 查询 9803 班学生所选各课程的课程号及其平均成绩。 （7） 查询选修“8105”课程的成绩高于“980301”号同学成绩的所有同学的记录。 （8） 查询与学号为“980101”的同学同岁的所有学生的 SNO，SNAME 和 SBIRTH。 （9） 查询“钱军”教师任课的课程号，选修其课程学生的学号和成绩。 （10） 查询选修某课程的学生人数多于 3 人的教师姓名。 （11） 查询同学选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程的同 学的 SNO 及“8105”课程成绩，并按成绩从高到低次序排列。 （12） 查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学 的 CNO、SNO、GRADE。 （13） 列出所有教师和同学的姓名、性别、年龄。 （14） 查询成绩比该课程平均成绩高的学生的成绩表。 （15） 列出所有任课教师的 TNAME 和 DEPT。 （16） 列出所有未讲课教师的 TNAME 和 DEPT。 （17） 列出至少有 2 名男生的班号。 （18） 查询不姓“张”的学生的记录。 （19） 查询每门课最高分的学生的 SNO、CNO、GRADE。 （20） 查询与“李华”同性别并同班的同学的 SNAME。 （21） 查询“女”教师及其所上的课程。 （22） 查询选修“数据库系统”课程的“男”同学的成绩表。 （23） 查询所有比王立年龄大的教师姓名、年龄和王立的年龄。 （24） 查询不讲授“8104”号课程的教师姓名。
----------------------查询----------------------
--1.查询选修课程“8105”且成绩在 80 到 90 之间的所有记录
select *
from SC
where SC.CNO = '8105'
and SC.GRADE between 80 and 90
--2.查询成绩为 76、86 或 96 的记录
select *
from SC
where SC.GRADE in (76,86,96)
--3.查询 9803 班的学生人数
select count(*) as 人数
from Student
where Student.CLASS = '9803'
--4.查询至少有 4 名学生选修的并以 81 开头的课程的平均成绩
select count(distinct SC.SNO),SC.CNO,avg(SC.GRADE)
from SC
where SC.CNO like '81%'
group by SC.CNO
--5.查询最低分大于 80，最高分小于等于 95 的 SNO 与平均分
select SC.SNO,avg(SC.GRADE) 平均成绩
from SC
group by SC.SNO
having min(SC.GRADE) > 80 and max(SC.GRADE) (
select SC.GRADE
from SC
where SC.SNO = '980301')
--8.查询与学号为“980101”的同学同岁的所有学生的 SNO，SNAME 和 SBIRTH
select Student.SNO,Student.SNAME,Student.SBIRTH
from Student
where year(Student.SBIRTH) = (
select year(Student.SBIRTH)
from Student
where Student.SNO = '980101')
--9.查询“钱军”教师任课的课程号，选修其课程学生的学号和成绩
select SC.*
from Course
inner join Teacher on Teacher.TNO = Course.TNO
inner join SC on SC.CNO = Course.CNO
where Teacher.TNAME = '钱军'
--10.查询选修某课程的学生人数多于 3 人的教师姓名
select distinct Teacher.TNAME
from Teacher
inner join Course on Course.TNO = Teacher.TNO
inner join SC on Course.CNO = SC.CNO
where SC.CNO in(
select SC.CNO
from SC
group by SC.CNO
having count(*) > 3)
--11.查询同学选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程的同
-- 学的 SNO 及“8105”课程成绩，并按成绩从高到低次序排列
-- 这题并没有结果，只有980302通识选修了8105和8245这两门课程，但是其8245分数更高
select SC.SNO,SC.GRADE
from SC
where SC.CNO = '8105'
and SC.GRADE > (
select GRADE
from SC as S2
where S2.SNO = SC.SNO
and S2.CNO = '8245')
order by SC.GRADE desc
-- select SC.SNO,SC.GRADE
-- from SC
-- where SC.CNO = '8105'
-- and SC.GRADE > (
-- select GRADE
-- from SC as S2
-- where S2.SNO = SC.SNO
-- and S2.CNO = '8104')
-- order by SC.GRADE desc
--12.查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩 的同学
-- 的 CNO、SNO、GRADE
-- 这题依旧没有答案，从SC表可以看出，最高分就是980302同学的8245的96分
-- 和980102号同学的8244的96分,科目8105最高分95分
select *
from SC
inner join Student on Student.SNO = SC.SNO
where SC.CNO = '8105'
and SC.GRADE > (
select max(SC.GRADE)
from SC
where SC.CNO = '8245')
--12(改).查询选修编号为“8245”课程且成绩高于所有选修编号为“8105”课程成绩 的同学
-- 的 CNO、SNO、GRADE
select *
from SC
inner join Student on Student.SNO = SC.SNO
where SC.CNO = '8245'
and SC.GRADE > (
select max(SC.GRADE)
from SC
where SC.CNO = '8105')
--13.列出所有教师和同学的姓名、性别、年龄
--使用并集
select Student.SNAME as Name,Student.SEX as sex,Student.SBIRTH as birth
from Student
union
select Teacher.TNAME as Name,Teacher.SEX as sex,Teacher.TBIRTH as birth
from Teacher
--14.查询成绩比该课程平均成绩高的学生的成绩表
select *
from SC a
where a.GRADE not between 0
and(
select avg(GRADE)
from SC b
where a.cno=b.cno )
--15.列出所有任课教师的 TNAME 和 DEPT
select distinct Teacher.TNAME,Teacher.DEPT
from Teacher
inner join Course on Course.TNO = Teacher.TNO
where Course.TNO in (
select distinct Course.TNO
from Course)
--16.列出所有未讲课教师的 TNAME 和 DEPT
-- 所有老师都讲课，没有结果
select Teacher.TNAME,Teacher.DEPT
from Teacher
where Teacher.TNO not in (
select distinct Course.TNO
from Course)
--17.列出至少有 2 名男生的班号
select Student.CLASS
from Student
where SEX = '男'
group by Student.CLASS
having count(*) >= 2
--18.查询不姓“张”的学生的记录
select Student.*
from Student
where Student.SNAME not like '张%'
--19.查询每门课最高分的学生的 SNO、CNO、GRADE
select s1.SNO,s1.CNO,s1.GRADE
from SC as s1
where s1.GRADE IN (
select max(s2.GRADE)
from SC as s2
where s1.CNO = s2.CNO)
--20.查询与“李华”同性别并同班的同学的 SNAME
select Student.SNAME
from Student
where Student.SEX = (
select Student.SEX
from Student
where Student.SNAME = '李华')
and Student.CLASS = (
select Student.CLASS
from Student
where Student.SNAME = '李华')
--21.查询“女”教师及其所上的课程
select Teacher.TNAME,Course.CNO,Course.CNAME
from Teacher
inner join Course on Course.TNO = Teacher.TNO
where Teacher.SEX = '女'
--22.查询选修“数据库系统”课程的“男”同学的成绩表
select distinct SC.SNO,Student.SNAME,Student.SEX,Course.CNAME,SC.GRADE
from SC
inner join Student on Student.SNO = SC.SNO
inner join Course on Course.CNO = SC.CNO
where Student.SEX = '男'
and Course.CNAME = '数据库系统'
--23.查询所有比王立年龄大的教师姓名、年龄和王立的年龄
select Teacher.TNAME,(year(GETDATE())-year(Teacher.TBIRTH)) as age
from Teacher
where (year(GETDATE())-year(Teacher.TBIRTH)) > (
select year(GETDATE())-year(Teacher.TBIRTH)
from Teacher
where Teacher.TNAME = '王立')
or Teacher.TNAME = '王立'
--24.查询不讲授“8104”号课程的教师姓名
select Teacher.TNAME
from Teacher
where Teacher.TNAME not in(
select Teacher.TNAME
from Teacher
inner join Course on Course.TNO = Teacher.TNO
where Course.CNO = '8104')
还请大家指出里面的错误，我好改正，谢谢啦
|