Orcle database Query Exercise review: Four

First, the topic

1. Find out the highest and lowest points of Zhang San and the corresponding course names

Select *  fromCourse C,mark MwhereC.cid=M.cid andSid=(SelectSid fromStudentwhereSname='Zhang San')  and(Cmark>=all(SelectCmark fromCourse C,mark MwhereC.cid=M.cid andSid=(SelectSid fromStudentwhereSname='Zhang San'))orCmark<=all(SelectCmark fromCourse C,mark MwhereC.cid=M.cid andSid=(SelectSid fromStudentwhereSname='Zhang San')))

2. The scores of those students were higher than Zhang San

Step1, find out that there is a class score lower than Zhang San This course results of the person step1.1、SelectSid fromStudentwhereSname='Zhang San'Step1.2 SelectA.sid fromMark A,mark bwhereA.cid=B.cid andB.sid=1002  andA.cmark<B.cmarkstep2, Paichu Step1, the rest is the purpose of STEP2.1, exclude from the score table, find the remaining SIDSelectSid fromMarkwhereSid not inch(Step1.2) Step2.2Redeem your name in the student formSelectSname fromStudentwhereSidinch(Step2.1)

3. The average score from high to low shows all students ' math ', ' English ', ' Chinese ' and the course scores (shown in the following form: Student ID, advanced mathematics, Computer Math, English, effective course number, effective average score)

SelectSid Student ID, (SelectCmark fromMarkwhereCid=(SelectCid fromCoursewhereCName='Mathematics') andSid=SC.SID) Mathematics, (SelectCmark fromMarkwhereCid=(SelectCid fromCoursewhereCName='English') andSid=SC.SID) English,Count(*) Number of effective courses,avg(Cmark) effective sharing fromMark SCGroup  bySid

4. The query only selected students ' names in math and English classes

Step1,SelectCid fromCoursewhereCName='Mathematics'Step1,SelectCid fromCoursewhereCName='English'Step3Select  fromMark A,mark bwhereA.cid=(STEP1) andB.cid=(STEP2) andA.sid=B.SIDSTEP4SelectSid fromMarkwhereSidinch(STEP3)Group  bySid HvingCount(*)=2

5. Identify the most evenly divided male names in the computer profession

SelectSid fromStudentwhereSmajor='Computer'  andSsex='male'SelectSidavg(Cmark) AMK fromMarkwhereSidinch(STEP1)Group  bySidSelect Max(AMK) from(STEP2)SelectSid from(STEP2)whereAmk=(STEP3)

6. Find the names of the boys with the highest sharing of math and English

Step1SelectCid fromCoursewhereCNameinch('Mathematics','English') Step2SelectCid fromStudentwhereSsex='male'Step3SelectSid fromMarkwhereSidinch(STEP2) andCidinch(STEP1)Group  bySid

7. Find out the names of students who are evenly divided by more than the total sharing (sharing of all courses for all)

Select avg  from  Selectfromgroupby hasavg(cmark)> (STEP1)

8. Which course in Shanghai is evenly divided than Fujian

Step1SelectSid fromStudentwhereSnativeplace='Shanghai'Step2SelectCidavg(Cmark) fromMarkwhereSidin=(STEP1)Group  byCIDSTEP3SelectSid fromStudentwhereSnativeplace='Fujian'Step4SelectCidavg(Cmark) fromMarkwhereSidin=(STEP3)Group  byCIDSTEP5Select *  fromMark A,mark bwhereA.cid=(STEP2) andB.cid=(STEP4) andA.cid=B.cid andA.cid<B.cid/*SELECT * from Mark A,mark b where a.sid= ' 10001 ' and b.cid= ' 10002 ' and a.cid=b.cid and A.cid>b.cid*/

9. Ask each course to remove the average score from one of the highest and lowest points

Step1Select *  fromMarkwhereCid='2001' Order  byCMARKSTEP2SelectCNAME, (Select avg(Smark) from( SelectSid,cid,cmark,rownum R from (Select *  fromMark MwhereCid='2001' Order  byCmark)whereR!=1  andR!=(Select Count(*) fromMark MwhereCid='2001') ) (j) Evenly fromCourse C

10. Ask for 2001 courses to remove the average score after the highest and lowest points

Step1Select *  fromMarkwhereCid='2001' Order  byCMARKSTEP2SelectCNAME, (Select avg(Smark) from( SelectSid,cid,cmark,rownum R from (Select *  fromMark MwhereCid='2001' Order  byCmark)whereR!=1  andR!=(Select Count(*) fromMark MwhereCid='2001')) ) fromCourse C

Orcle database Query Exercise review: Four