|
-- 14、查询所有学?的Sname、Cno和Degree列。
/*
select Cno,degree from SCORE where SNAME in (
select SNAME from STUDENG) */
select b.sname,a.cno,a.degree
from SCORE a
inner join STUDENG b
on a.sno=b.sno
-- 15、查询所有学?的Sno、Cname和Degree列。
select a.Sno,b.Cname,a.Degree
from SCORE a
inner join COURSE b
on a.cno=b.cno
-- 16、查询所有学?的Sname、Cname和Degree列。
select a.sname,b.cname,c.degree
from STUDENG a
inner join COURSE b
inner join SCORE c
on a.sno=c.sno and b.cno=c.cno
-- 17、查询“95033”班所选课程的平均分。
select avg(b.degree) as'95003 avgrage'
from COURSE a
inner join SCORE b
inner join STUDENG c
on a.cno=b.cno and b.sno=c.sno
where c.class='95033'
-----------------------------------
SELECT CNO,AVG(DEGREE) FROM SCORE WHERE SNO IN (SELECT SNO FROM
STUDENG WHERE CLASS =95033) GROUP BY CNO
-- 19、查询选修“3-105”课程的成绩?于“109”号同学成绩的所有同学的记录。?
select * from SCORE where cno='3-105' and degree > (
select degree from SCORE where sno='109' and cno='3-105')
-- 20、查询score中选学多?课程的同学中各科分数为?最?分成绩的记录。
SELECT * FROM SCORE A WHERE A.SNO IN (SELECT SNO FROM SCORE GROUP BY
SNO HAVING COUNT(*)>1)
-- 21.查询1975年之后出?的学?的所学课程以及成绩。
select a.cname,b.degree,c.sname
from COURSE a
inner join SCORE b
inner join STUDENG c
on a.cno=b.cno and b.sno= c.sno
where c.sbirthday > 1975-01-01;
-- 23、查询“张旭“教师任课的学?成绩。
select degree
from TEACHER a
inner join COURSE b
inner join SCORE c
on a.tno= b.tno and b.cno=c.cno
where a.tname='张旭'
-- 24、查询选修某课程的同学?数多于5?的教师姓名。
select a.tname
from TEACHER a
inner join COURSE b
inner join SCORE c
on a.tno=b.tno and b.cno=c.cno
group by c.cno having count(*)>5
-- 25、查询95033班和95031班全体学?的记录
select * from STUDENG where class in (95033,95031)
|