tbl001

select Student.Sid from (select sid,score from SC where Cid='1') Student,

(select Sid,score from SC where Cid='2') Course

--2、查詢平均成績大於60分的同學的學號和平均成績；

select Sid,avg(score) from SC group by Sid having avg(score)>60

--3、查詢所有同學的學號、姓名、選課數、總成績；

select Sid,Sname,Cid,score from Student inner join SC on(Student.Sid=SC.Sid) inner join Course on (Course.Cid=SC.Cid)

--4、查詢姓“李”的老師的個數；

select count(*) from Teacher where Tname like '李%'

--5、查詢沒學過“葉平”老師課的同學的學號、姓名；

--select Sid,Sname from Student inner join SC on (Student.Sid=score.Sid) inner join Course on(Course.Cid=SC.Cid) inner join Teacher on (Teacher.Tid=Course.Tid) where Teacher.Tname is null--6、查詢學過“”並且也學過編號“”課程的同學的學號、姓名；

select SID,Sname

--7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名；

select Sid,Sname

from Student

where Sid in (select Sid from SC ,Course ,Teacher where SC.Cid=Course.Cid AND Teacher.Tid=Course.Tid AND Teacher.Tname='葉平'

group by Sid

having count(SC.Cid)=(select count(Cid) from Course,Teacher where Teacher.Tid=Course.Tid AND Tname='葉平'))

--8、查詢課程編號“”的成績比課程編號“”課程低的所有同學的學號、姓名；

--9、查詢所有課程成績小於60分的同學的學號、姓名；

Select Student.SID,Student.Sname from Student,SC where Student.Sid=SC.Sid and SC.score<60

--10、查詢沒有學全所有課的同學的學號、姓名；

select Student.Sid,Student.Sname

from Student,SC where Student.Sid=SC.Sid

group by Student.Sid,Student.Sname

having count(Cid) <(select count(Cid) from Course) 交個朋友吧!好在大家都是學程序的

上篇: 輝煌了30年，本田的VTEC到底是什麽技術？ 下篇: 工業用方形噸桶都是裝什麽的有毒嗎