Orcle Database review: Two

First, the topic

1. Find out the names of all students who have scored below 80

SelectSname fromStudentwhereSidinch(SelectSid fromMarkGroup  bySid having Max(Cmark)< the)SelectSname fromStudentwhereSidinch(SelectSid fromMarkwhereSid not inch(SelectSid fromMarkwhereCmark>= the))

2. Find the names of students who are exactly the same as the Zhang San students ' chosen course

STEP1:SelectSid fromStudentwhereSname='Zhang San'Step2:SelectCid fromMarkwhereSid=(STEP1) Step3:SelectSid fromMarkwhereCid not inch(STEP2) Step4:SelectSid fromMarkwhereSid not inch(STEP3)Group  bySid having Count(*)=(Select Count(*) fromMarkwhereSid=(STEP1))

3. Show Zhang San's math and English scores, require display of names, math scores, English scores

Selectsname, (SelectCmark fromMarkwhereSid=(SelectSid fromStudentwhereSname='Zhang San')  andCID=(SelectCid fromCoursewhereCName='Mathematics') ) Mathematical results, (SelectCmark fromMarkwhereSid=(SelectSid fromStudentwhereSname='Zhang San')  andCID=(SelectCid fromCoursewhereCName='English')) English score fromStuden

Students who have at least 10002 elective courses

Mleftjoin   on s.sid =where Cmark <  -

5. Find out the names of all students who have failed courses

Select  from  Left Join  on S.sid=where cmark<

6. Student number of at least 10002 elective courses

SelectSid fromMark SC1where  not exists(Select *  fromMark SC2whereSid=10002  and  not exists(Select * fromMarkwhereSid=Sc1.sid andCid=sc2.cid)) andSid!=10002 Group  bySid

7. The names of students who have at least enrolled in all courses Zhang San elective

SelectSid fromMark SC1where  not exists(Select *  fromMark SC2whereSid=(SelectSid fromStudentwhereSname='Zhang San') and  not exists(Select * fromMarkwhereSid=Sc1.sid andCid=sc2.cid)) andSid!=10002 Group  bySid

Orcle Database review: Two