查詢作業1

----針對S_T資料庫，完成以下操作：

----1. 按課程号降序顯示選修各個課程的總人數、最高成績、最低成績及平均成績；

select cno 課程号 ,count() 總人數,max(grade) 最高成績,min(grade) 最低成績,(sum(grade)/count()) 平均成績

from sc

group by cno

order by cno desc;

----2. 列出有二門以上課程（含兩門）不及格的學生的學号及不及格門數；

select sno 學号,count() 不及格門數

from sc

where grade<60

group by sno

having count()>=2;

----3. 查詢名字中第2個字為‘勇’的學生姓名和學号及選修的課程号、課程名；

select sname 姓名,a.sno 學号,c.cno 課程号, cname 課程名

from student a,course b,sc c

where a.sno=c.sno and b.cno =c.cno and a.sno in (

select sno

from student

where sname like ‘_勇%’

);

----4. 查詢至少選修了一門間接先行課為“5”号課程的學生姓名；

select sname

from student

where sno in (select sno

from sc

where cno in (select cno

from course

where cpno in (select cno

from course

where cpno=‘5’)));

----5. 查詢選修了“資料庫”和“數學”兩門課程的學生的學号；

select sno

from sc

where cno in(select cno

from course

where cname =‘資料庫’)

intersect

select sno

from sc

where cno in

(select cno

from course

where cname =‘數學’)

----6. 找出至少選修了“200515004”号同學所選修課程的學生學号；

select distinct sno 學号

from sc a

where a.sno!=‘200515004’ and not exists

(select *

from sc b

where b.sno=‘200515004’ and not exists

(select *

from sc c

where a.sno=c.sno and b.cno=c.cno))

----7. 找出“資料庫系統”這門課成績最高的學生學号，姓名；

select sno 學号,sname 姓名

from student

where sno in(

select sno

from sc

where cno in (select cno

from course

where cname=‘資料庫系統’) and grade >=

(select max(grade)

from sc

where cno in

(select cno

from course

where cname=‘資料庫系統’)

group by cno));

----8. 找出選修了“2”課程但沒有選修“1”課程的學生姓名；

select sname

from student

where sno in

(select sno

from sc

where cno=‘2’)and sno not in (select sno

from sc

where cno=‘1’);

----9. 找出被所有同學選修了的課程号；–反義：沒有一門課程沒有被所有同學選修

select distinct cno 課程号

from sc as a

where not exists

(select *

from sc as b

where not exists(select *

from sc as c

where a.cno=c.cno and b.sno=c.sno));

----10. 查詢沒有選課的學生姓名。

select sname

from student

where sno not in

(select sno

from sc);