50 SQL query Tips

Student (s#,sname,sage,ssex) Student Table Course (c#,cname,t#) Timetable SC (s#,c#,score) score table teacher (t#,tname) Teacher table question: 1, query "001" Course than "002" School number of all students with high course performance; Select a.s# from (select S#,score from SC where c#= ' 001 ') A, (select S#,scorefrom SC where c#= ' 002 ') bwhere A . Score>b.score and A.s#=b.s#;2, the number of students with average scores greater than 60 points and the average score; Select S#,avg (Score) from Scgroup by s# have avg (score) > 60;3, query All students of the school number, name, number of courses selected, total; select Student.s#,student.sname,count (SC. C #), SUM (score) from Student to Outer join SC on STUDENT.S#=SC. S#group by Student.s#,sname4, the number of teachers who queried the surname "Li"; select COUNT (Distinct (tname)) from Teacherwhere tname like ' li% '; 5, inquiry did not learn "cotyledons" The student's number and name of the teacher's class; Select Student.s#,student.snamefrom Studentwhere s# not in (select DISTINCT (SC. s#) from Sc,course,teacher where SC. C#=course.c# and teacher.t#=course.t# and Teacher.tname= ' cotyledons '); 6, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name; Select student.s#, Student.sname from STUDENT,SC where STUDENT.S#=SC. s# and SC. C#= ' 001 ' and exists (Select * from SC as sc_2 where SC_2.S#=SC. s# and sc_2.c#= ' 002 '); 7, inquiry learning "cotyledons" oldThe student's number and name of all classes taught by the teacher; Select S#,snamefrom studentwhere s# in (select s# from SC, Course, Teacher where SC. C#=course.c# and teacher.t#=course.t# and Teacher.tname= ' cotyledons ' GROUP by s# have count (SC. C #) = (select count (C #) from Course,teacher where teacher.t#=course.t# and Tname= ' cotyledons ')); 8, query the course number "002" of the grade than the course number "001" School number, name of all students with low course; Select S#,sname from (select Student.s#,student.sname,score, (select score from SC sc_2 where Sc_2.s#=stud Ent. s# and sc_2.c#= ' 002 ') Score2from STUDENT,SC where STUDENT.S#=SC. s# and c#= ' 001 ') s_2 where Score2 <score;9, query All course scores less than 60 points of the student's school number, name; Select S#,snamefrom studentwhere s# not in (select St Udent. s# from STUDENT,SC where S.S#=SC. s# and score>60); 10, the inquiry did not learn all the classes of the students ' study number, name; Select Student.s#,student.snamefrom student,scwhere student.s#=sc. s# GROUP BY Student.s#,student.sname have Count (C #) < (select count (C #) from Course); 11, inquires at least one course and the student number is "1001" students learn the same student number and name; Select S#,sname from Student,sc where STUDENT.S#=SC. s# and C # in select C # from SC where s#= ' 1001 '; 12, inquiry at least study number as "001 "Students of all classes of the other students study number and name; select DISTINCT SC. S#,snamefrom Student,scwhere STUDENT.S#=SC. s# and C # in (select C # from SC where s#= ' 001 '), 13, the "SC" table in the "cotyledons" teacher taught the results of the course is changed to the average performance of the courses; Update SC set score= (select AVG (Sc_2.sc Ore) from SC Sc_2where SC_2.C#=SC. C #) from Course,teacher where COURSE.C#=SC. C # and course.t#=teacher.t# and Teacher.tname= ' cotyledons '); 14, Query and "1002" number of students to study the course of the same class of other students and names; Select s# from SC where C # in (SE Lect C # from SC where s#= ' 1002 ') group by s# have count (*) = (select count (*) from SC where s#= ' 1002 '); 15, delete learning "cotyledons" the SC table record of the teacher class ; delect Scfrom Course, Teacherwhere course.c#=sc. C # and course.t#= teacher.t# and tname= ' cotyledons '; 16. Insert some records into the SC table, which require the following conditions: The average score of the student number, 2, and number of the class that has not been numbered "003"; Insert SC Select s#, ' 002 ', (select AVG (score) from SC where c#= ' 002 ') from Student where s# not in (select s# from SC where c#= ' 002 '); 17, by average Performance from high to low show all students "database", "Enterprise Management", "English" of the course scores, shown as follows: Student ID, database, business management, English, effective course number, effective average SELECT s# as student ID, (select score from SC where sc.s#=t.s# and c#= ' 004 ') as database, (SELECT score from SC WHERE SC. S#=t.s# and c#= ' 001 ') as Enterprise management, (SELECT score from SC WHERE sc.s#=t.s# and c#= ' 006 ') as English, COUNT (*) as Active course number, AVG (T.score) as Average score from SC as Tgroup by S#order by AVG (t.score) 18, query the highest and lowest scores for each section: show the following: Course ID, highest score, Min. Select L.c# as course Id,l.score as highest score, R  . Score as Min. from SC L, sc as rwhere l.c# = r.c# Andl.score = (SELECT MAX (Il.score) from SC as il,student as imwhere l.c# = IL. C # and IM. S#=il. S#group by IL. C #) Android. Score = (SELECT MIN (Ir.score) from SC as Irwhere r.c# = IR. C#group by IR. C #) 19, the percentage of average grades from low to high and pass rates by section from high to low order select T.c# as course number, max (course. Cname) As course name, ISNULL (AVG (Score), 0) as average score, $ * SUM (case when IsNull (score,0) >=60 then 1 ELSE 0 END)/count (*) as passing percent F ROM SC t,coursewhere T.c#=course. C#group by T.c#order by $ * SUM (case if IsNull (score,0) >=60 then 1 ELSE 0 END)/count (*) DESC20, query the following percentage of the average grade and pass rate for the course (with "1 Lines" display): Enterprise Management (001), Marx (002), OO&AMP;UML (003), Database (004) SELECT SUM (case when C # = ' 001 ' then score ELSE 0 END)/sum (Case C # WHE N ' 001 ' then 1 ELSE 0 END) as Enterprise Management average, + * SUM (case when C# = ' 001 ' and score >= 1 Else 0 end)/sum (case when C # = ' 001 ' then 1 ELSE 0 END) as Enterprise management passing percentage, SUM (case when C # = ' 002 ' then score ELSE 0 end]/sum (case C # when ' 002 ' then 1 else 0 END) as Marx's average, $ * SUM (case when C # = ' 002 ' and Scor E >= 1 Else 0 END)/sum (case when C # = ' 002 ' then 1 ELSE 0 END) as Max pass percentage, SUM (case when C # = ' 003 ' then score ELSE 0 END)/sum (Case C # when ' 003 ' then 1 ELSE 0 END) as UML average, $ * SUM (case when C # = ' 003 ' and score >= and 1 E LSE 0 END)/sum (case when C # = ' 003 ' then 1 else 0 end) As UML pass percentage, SUM (case when C # = ' 004 ' then score ELSE 0 end)/sum (CAS  E C # when the ' 004 ' then 1 else 0 end) as database average, a SUM of $ (case when C # = ' 004 ' and score >= 1 ELSE 0 end)/sum (case When C # = ' 004 ' then 1 ELSE 0 END) As database passing percentage from SC 21, query different teachers teach different courses average score from high to low show Select Max (z.t#) as Teacher Id,max (Z.tname) as teach Division name, c.c# as Course Id,max (c.cname) As course name, AVG (score) as average score from SC as t,course as C, Teacher as Zwhere t.c#=c.c# and c.t#=z.t #GROUP by C.c#orderVG (Score) DESC22, query the following course results 3rd to 6th student Transcripts: Enterprise Management (001), Marx (002), UML (003), Database (004) [Student id],[student name], Enterprise management, Marx, UML, database, Average score Select DISTINCT Top 3SC. s# as student number, student.sname as student name, T1.score as Enterprise management, T2.score as Marx, T3.score as uml,t4.score as database, ISNULL (t1.score,0) + is NULL (t2.score,0) + ISNULL (t3.score,0) + ISNULL (t4.score,0) as total score from STUDENT,SC left JOIN SC as t1on SC. s# = T1. s# and T1. C # = ' 001 ' left JOIN SC as T2on SC. s# = T2. s# and T2. C # = ' 002 ' left JOIN SC as T3on SC. s# = T3. s# and T3. C # = ' 003 ' left JOIN SC as T4on SC. s# = T4. s# and T4. C # = ' 004 ' WHERE student. S#=sc. s# Andisnull (t1.score,0) + ISNULL (t2.score,0) + ISNULL (t3.score,0) + ISNULL (t4.score,0) not in (Selectdistincttop + T Iesisnull (t1.score,0) + ISNULL (t2.score,0) + ISNULL (t3.score,0) + ISNULL (t4.score,0) from Scleft JOIN SC as t1on SC. s# = T1. s# and T1. C # = ' K1 ' left JOIN SC as T2on SC. s# = T2. s# and T2. C # = ' K2 ' left JOIN SC as T3on SC. s# = T3. s# and T3. C # = ' K3 ' left JOIN SC as T4on SC. s# = T4. s# and T4. C # = ' K4 ' ORDER by ISNULL(t1.score,0) + ISNULL (t2.score,0) + ISNULL (t3.score,0) + ISNULL (t4.score,0) DESC); 23, the statistics print each section result, each score segment number: Course ID, course name, [100-85],[85-70],[70-60],[<60]select SC. C # as Course ID, Cname as course name, SUM (case when score between and 1 ELSE 0 END) as [100-85],sum if score BETW EEN and 1 Else 0 end) as [85-70],sum (case score between and 1 else 0 end) as [70-60],sum (CA SE when score < 1 ELSE 0 END) as [-]from sc,coursewhere SC. C#=course.c#group by SC. C#,cname;  24. Check the average scores of students and their rankings select 1+ (select COUNT (distinct average score) from (select S#,avg (score) as average score from Scgroup by s#) as T1where average score > T2. Average score) as rank, s# as student number, average score from (SELECT S#,avg (score) average score from Scgroup to s#) as T2order by average score desc; 25. Check the record of the top three records of each section: (regardless of the results of the situation) SELECT T1. s# as student id,t1. C # as courses Id,score as fractions from SC T1where score in (SELECT TOP 3 scorefrom scwhere t1. c#= C#order by score DESC) ORDER by T1. c#;26, query the number of students enrolled in each course select C#,count (s#) from the SC Group by c#;27, check out the number and name of all students taking only one course SeleCT SC. S#,student.sname,count (C #) As course number from SC, Studentwhere SC. s#=student.s# GROUP by SC. s#, Student.sname have count (C #) =1;28, query Boys, girls number select count (Ssex) as the number of males from Student Group by Ssex have ssex= ' male '; Select COUNT (Ssex) as the number of females from the Student group by Ssex have ssex= ' female '; 29. List of students with surname "Zhang" select Sname from Student WHERE Sname Like ' Zhang% '; 30. Check the same-name list of same-sex students and count the number of the same name Select Sname,count (*) from Student GROUP by Sname have Count (*) >1;31, 1981-Born student list ( Note: The type of Sage column in the student table is datetime) Select Sname, convert (char (one), DATEPART (Year,sage)) as Agefrom Studentwhere CONVERT ( char (one), DATEPART (year,sage)) = ' 1981 '; 32, the average score of each course is queried, and the results are ranked in ascending order by average, with the same average score, in descending order by course number Select C#,AVG (score) from SC Group By C # ORDER by AVG (score), C # DESC; 33, the number of students who query average scores greater than 85, name and average score of select SNAME,SC. s#, AVG (score) from Student,scwhere STUDENT.S#=SC. s# GROUP by SC. S#,sname having AVG (score) >85;34, query course name "database", and score less than 60 student name and score select Sname,isnull (score,0) from STUDENT,SC, Coursewhere SC. s#=student.s# and SC. c#=course.c# and Course.cname= ' database' and score <60;35, inquiring about the course selection of all students, select SC. S#,sc. C#,sname,cnamefrom Sc,student,coursewhere SC. s#=student.s# and SC. c#=course.c#; 36. Check the name, course name and score of any course score above 70 points; SELECT distinct student. S#,student. Sname,sc. C#,sc.scorefrom student,scwhere sc.score>=70 and SC. S#=student. s#;37, query failed courses, and according to the course number from large to small arrangement Select C # from SC where Scor e <60 order by C #; 38, Query the course number 003 and the course score of 80 points above the student's number and name; Select Sc.s #,student.sname from Sc,student where SC. s#=student.s# and Score>80 and c#= ' 003 '; 39, the number of students selected for the course select COUNT (*) from sc;40, query for elective "cotyledons" students of the course, The names of the students with the highest scores and their scores select Student.sname,scorefrom student,sc,course c,teacherwhere student.s#=sc. s# and SC. C#=c.c# and c.t#=teacher.t# and Teacher.tname= ' cotyledons ' and sc.score= (select Max (score) from SC where c#=c.c#); 41. Inquiry for each course and corresponding elective Number of SELECT COUNT (*) from SC Group by c#;42, check the student's number, course number, student score select distinct A.s#,b.score from SC A, SC B where A.sco Re=b.score and A.c# <>b.c#, 43, query the top two select T1 of the best performance of each gate. s# as student id,t1. C # as course Id,score as fraction from SC t1where score in(SELECT TOP 2 scorefrom scwhere t1. c#= C#order by score DESC) ORDER by T1. C#;44, the number of students enrolled in each course is counted (more than 10 participants). Required to output the course number and the number of elective, query results in descending order of numbers, query results in descending order of numbers, if the number is the same, in ascending order by course number Select C # as course number, COUNT (*) as the number of people from Scgroup by C#order by Count (*) des C,C#45, retrieving at least two elective courses of student number select S#from Scgroup by s#having count (*) > = 246, Query the course number and course name of all students taking courses select C#,cnamefrom Coursewhere C # in (select C # from SC Group by C #) 47, query did not learn the name of the student who "cotyledons" taught any of the courses, select Sname from Student where s# not in (sel ECT s# from COURSE,TEACHER,SC where course.t#=teacher.t# and SC. C#=course. C # and Tname= ' cotyledons '); 48. Check the student's number and average score of two or more failed courses select S#,AVG (IsNull (score,0)) from SC where s# in (select s# from SC where S Core &LT;60 GROUP BY s# have Count (*) >2) Group by s#;49, retrieving "004" course score less than 60, ranked by fractions in descending order of student number select s# from SC where c#= ' 004 ' an D score <60 ORDER by score desc;50, delete "002" Students of "001" The results of the course delete from Sc where s#= ' 001 ' and c#= ' 001 ';

50 SQL query Tips