Orcle database Query Exercise review: Three

First, the topic

1. Names of boys with "Zhang San"

Select *  from where snativeplace=(selectfromwhere sname=' Zhang San '  and Ssex=' male '

2. Number of students who have taken the courses taught by Zhaolu

Select Count(*) fromMarkwhereCidinch(SelectCid fromCoursewhereTid=(SelectTid fromTeacherwhereTname='Zhaolu'))Group  byCIDSelect Count(distinctSid fromMarkwhereCidinch(SelectCid fromCoursewhereTid=(SelectTid fromTeacherwhereTname='Zhaolu'))

3. Inquiry did not learn the "Wang" surname teacher class students of the school number, name

 select  *   student where  sid not  in  (select  sid from  Mark where  cid in   ( select  cid from  course where  tid Span style= "color: #808080;" >in  (select  tid from  teacher where  tname "   King%   "))) 

4. The student's name, gender, and the highest score in the mathematics course

Select *  fromStudentwhereSidinch(SelectSid fromMarkwhereCid=(SelectCid fromCoursewhereCName='Mathematics')  andCmark>=  All(SelectCmark fromMarkwhereCid=(SelectCid fromCoursewhereCName='Mathematics')))

5. Count average scores per course and sort in descending order of grades

Select * from(Select(SelectCName fromCoursewhereCid=M.CID),avg(Cmark) Cavg fromMark MGroup  byCID)Order  byCavgdescSelect *  from(Select avg(Cmark) CAVR fromMarkGroup  byCidOrder  byCavgdesc

6. Sub-query implementation query ' 3-2 class ' "Zhang Li" students "English" scores

SelectCmark Results fromMarkwhereSid=(SelectSid fromStudentwhereSname='Zhang San' andSclass='Class 2') andCid=(SelectCid fromCoursewhereCName='English')

7. Check the age of each class and the student in the class and compare the age of each person in the class (requires more than 20 years old to participate in the statistics)

Select sclass,sumfromwhere sage>=Groupby 

8. Check the age of the class and the student in the class (requires that each person in the class is older than 20 years old)

SelectSclass,sum(Sage) fromStudentGroup  bySclass having min(Sage)> -SelectSclass,sum(Sage) fromStudentwhereSclass not inch(SelectSclass fromStudentwhereSage< - Group  bySclass)Group  bySclass

9. Querying with subqueries The overall scores of all the students in the Advanced mathematics course

Select sum  from where = (Selectfrom where cname=' math ')

10. Querying with subqueries all of the students in the "advanced mathematics" course overall scores */

Select sum  from where inch (Selectfromwhere=(selectfromwhere cname  =' math '))

11. Please do it in two ways: find the names of all students who have scored more than 68 points * *

SelectSid,sname fromStudentwhereSidinch (SelectSid fromMarkGroup  bySid having min(Cmark)>= the)SelectSid,sname fromStudent Swhere exists (SelectSid fromMark MwhereS.sid=M.sidGroup  bySid having min(Cmark)>= the)

12. Find the names of at least 2 students with a score of 80 or more

Select  from where inch (Selectfromwhere>=Group by  having Count (*) >= 2 )

13. Inquire at least one course with the students of the same school Zhang San and the name

 select  sid,sname from  student where  sid in   ( select  sid from  mark where  CID Span style= "color: #808080;" >in   ( select  CID from  Mark where  sid in   ( select  sid from  student where  sname=   "  Zhang San  ))) 

14. The name of the student who did not take the "math" class * *

SelectSname fromStudentwhereSidinch (SelectSid fromMarkwhereSid not inch(SelectSid fromMarkwhereCidinch(SelectCid fromCoursewhereCName='Mathematics')))

15. Check the name of the student whose total is less than the average

Step1, calculates the average total score step1.1 calculates the personal total score tableSelectSidsum(Cmark) SMK fromMarkGroup  bySID step1.2 averages the total score tableSelect avg(SMK) from(Step1.1Step2, find out in the Personal score table to find out whose total score is lower than STEP1STEP2.1 SelectSid from(Step1.1)whereSmk<(Step1.2) Step2.2 SelectSname fromStudentwhereSidinch(Step2.1)

Orcle database Query Exercise review: Three