SQL query Job Answer

• A topic
• Two answers

A topic

1, query the name of all courses and the corresponding teacher 's name2How many men and women are there in the student list?3, the name of the student who queried the physical score equal to 1004, the name and average score of the students who have average scores greater than 80 points5, check all students ' School number, name, number of courses selected, Total6, query the number of teachers surnamed Li7, query the name of the student who did not report to Li Ping class8, inquiring the student's number of physics course higher than biology course9, inquire about the names of students who do not take physical and physical courses10, enquiries about the names and classes of students with more than two doors (including two doors), and the names of students who have enrolled in all courses12and query all records of the courses taught by Mr. Li13, Query the course number and course name that all students have enrolled14, query the number of times each course is elective15, the name and number of the student who took a course16, check the scores of all students and order from high to low (score to weight)17, and the average score of students with average scores greater than 8518, the name of the student who failed to check the biological grade and the corresponding biological score19, query for all students who have enrolled in Li Ping's course, these courses (Li Ping's course, not all courses) have the highest average student name20, the first two students who have the best results for each course are named21st, search for different courses but the same grades, course numbers, grades22, inquire about the names of the students who have not studied the "cotyledons" teacher course and the name of the elective courses;23, query all the students who took the student number 1 to take one or more courses of the student number and name;24. The student with the highest students ' name

TopicsTwo answers

#1. Check the name of all courses and the corresponding teacher 's nameSELECT course.cname, Teacher.tnamefrom courseinner JOIN teacher on course.teacher_id=Teacher.tid;#2. Find out how many men and women are in the student list .SELECT Gender Sex, COUNT (1) number from Studentgroup by gender;#3. The name of the student who queried the physical result equal to 100Select Student.snamefrom studentwhere sid in (select student_id from SCO Re INNER JOIN course on score.course_id=course.cid WHERE course.cname='Physical'and Score.num= 100    );#4. The names and average scores of students who have average scores greater than 80 pointsSelect Student.sname, T1.avg_numfrom studentinner JOIN (select student_id, AVG (num) as Avg_ Num from score GROUP by STUDENT_ID have avg (num)> 80) as T1 on Student.sid=t1.student_id;#5. Check all student's number, name, number of courses selected, total (note: For those who do not take any courses in the student also counted)Select Student.sid, Student.sname, T1.course_num, T1.total_numfrom studentleft JOIN (select student_id, COUNT (course_id) course_num, sum (num) Total_num from score GROUP by Student_ ID) as T1 on Student.sid=t1.student_id;#6. Query the number of teachers surnamed LiSELECT count (tid) from Teacherwhere tname like'Li%';#7, the inquiry did not report to Li Ping Teacher class student name (find out the students who enrolled in Li Ping's course, then take the reverse can be)Select Student.snamefrom studentwhere sid not in (select DISTINCT student_id from                Score WHERE course_id in (SELECT course.cid From course INNER JOIN teacher on course.teacher_id=Teacher.tid WHERE teacher.tname='Mr. Li Ping'            )    );#8. Check the student's number of physics course higher than biology course (Get Physical score table and biological score table respectively, then even watch)Select T1.student_idfrom (select student_id, num from score WHERE course_id=(SELECT CID from course WHERE CNAME='Physical') as T1inner JOIN (SELECT student_id, num from score WHERE cour se_id=(SELECT cid from course WHERE CNAME 
    ='Biological')) as T2 on t1.student_id=T2.student_idwhere T1.num>T2.num;#9, the inquiry does not simultaneously take the physical course and the physical Education class student name (does not have the same elective is the elective one, the idea is obtains the physical + Physical Education curriculum Student Information table, then based on the student group, counts the Count (course) =1)Select Student.snamefrom studentwhere sid in (select student_id from SCO                    Re WHERE course_id in (SELECT CID from Course WHERE CNAME='Physical'OR CNAME='Sports') GROUP by student_id have COUNT (course_id)= 1    );#10, Query the Hang section more than two doors (including two doors) of the student name and class (to find out the <60 table, and then group students, statistics course number >=2)SELECT Student.sname,class. Captionfrom studentinner JOIN (SELECT student_id from score WHERE num< 60GROUP by student_id have count (course_id)>= 2) as T1inner JOINclassOn student.sid =T1.student_idand student.class_id=class. Cid;#11. Check the name of the student who took all the courses (first the total number of courses from the course table, then grouped according to STUDENT_ID based on the score table, statistics course data equals the total number of courses)Select Student.snamefrom studentwhere sid in (select student_id from SCO Re GROUP by student_id have COUNT (course_id)=(SELECT count (CID) from course));#12. Check all records of the courses taught by teacher Li PingSELECT*From scorewhere course_id in (SELECT CID from course INNER JOIN TEAC her on course.teacher_id=Teacher.tid WHERE teacher.tname='Mr. Li Ping'    );#13. Check the course number and course name taken by all students (take all the students and then group the courses based on the score table to find the count (student_id) equals the number of students)Select CID, Cnamefrom coursewhere CID in (select course_id from Score GROUP by course_id have COUNT (student_id)=(SELECT COUNT (SID) from student) );#14. Check the number of electives per courseSELECT course_id, COUNT (student_id) from Scoregroup by course_id;#15. The name and student number of the students who have enrolled in a courseSelect Sid, Snamefrom studentwhere SID in (select student_id from SCO Re GROUP by student_id have COUNT (course_id)= 1    );#16. Check all students ' scores and sort them from high to low (grades go Heavy)SELECT DISTINCT numfrom scoreorder by num DESC;#17. The average student's name and average score is more than 85.Select Sname, T1.avg_numfrom studentinner JOIN (select student_id, avg (num) Avg_num from Score GROUP by student_id have AVG (num)> 85) T1 on Student.sid=t1.student_id;#18. The name of the student who failed to check the biological grade and the corresponding biological scoreSELECT sname name, num biological score from Scoreleft JOIN course on score.course_id=course.cidleft JOIN student on score.student_id=Student.sidwhere Course.cname='Biological'and Score.num< 60;#19. For all students who have enrolled in Li Ping's course, these courses (Li Ping's course, not all courses) have the highest average student nameSELECT snamefrom studentwhere Sid=(SELECT student_id from score WHERE course_id in ( SELECT course.cid from course INNER JOIN teacher O N course.teacher_id=Teacher.tid WHERE teacher.tname='Mr. Li Ping') GROUP by student_id ORDER by AVG (num) DESC LIMIT1    );#20. Search for the top two students with the best results for each course#View the information for each course according to the score, and provide a basis for finding the following correctlySELECT*From scoreorder by course_id, num DESC;#Table 1: Find out the course course_id for each course, with the highest score First_numSELECT course_id, max (num) first_numfrom scoregroup by course_id;#Table 2: The highest score is obtained by removing the highest score and then grouping according to the course, which is the second high fraction second_numSelect score.course_id, max (num) second_numfrom scoreinner JOIN (select course_id, max (num) First_num from score GROUP by course_id) as-T on score.course_id=T.course_idwhere Score.num<T.first_numgroup by course_id;#Combine table 1 and table 2 together to get a table T3 containing the course course_id and the First_num and Second_num of the courseSelect t1.course_id, T1.first_num, T2.second_numfrom (select course_id, Max ( num) First_num from score GROUP by course_id) as T1inner JOIN (SELECT s            CORE.COURSE_ID, Max (num) second_num from score INNER JOIN (SELECT course_id, Max (num) First_num from score GROUP by course_id) as-T on score.course_id =t.course_id WHERE score.num<t.first_num GROUP by course_id) As T2 on t1.course_id=t2.course_id;#Query the first two students (there may be tie or a second case)Select score.student_id, t3.course_id, T3.first_num, T3.second_numfrom scoreinner JOIN (select                t1.course_id, T1.first_num, t2.second_num from (SELECT course_id,  Max (num) First_num from score GROUP by course_id) As T1 INNER JOIN (SELECT score.course_id, max (num) Second_num from SCO                Re INNER JOIN (SELECT course_id, max (num) First_num from Score GROUP by course_id) as-T on score.course_id=t.course_id WHERE score.num<t.first_num GROUP by course_id) As T2 on t1.course_id=t2.course_id) as T3 on score.course_id=T3.course_idwhere Score.num>=T3.second_numand Score.num<=T3.first_num;#the obvious points that can be seen after sortingSelect score.student_id, t3.course_id, T3.first_num, T3.second_numfrom scoreinner JOIN (select                t1.course_id, T1.first_num, t2.second_num from (SELECT course_id,  Max (num) First_num from score GROUP by course_id) As T1 INNER JOIN (SELECT score.course_id, max (num) Second_num from SCO                Re INNER JOIN (SELECT course_id, max (num) First_num from Score GROUP by course_id) as-T on score.course_id=t.course_id WHERE score.num<t.first_num GROUP by course_id) As T2 on t1.course_id=t2.course_id) as T3 on score.course_id=T3.course_idwhere Score.num>=T3.second_numand Score.num<=T3.first_numorder by course_id;#You can verify the correctness of the above query with the following commandSELECT*From scoreorder by course_id, num DESC;--21, search for different courses but the same grades, course numbers, grades--22, inquire about the names of the students who have not studied the "cotyledons" teacher course and the name of the elective courses;--23, query all the students who took the student number 1 to take one or more courses of the student number and name;--24. Students with the highest student name

More exercises and reference answers: http://www.cnblogs.com/wupeiqi/articles/5748496.html

SQL query Job Answer