SQL Pen question Finishing

Preface: has been writing various SQL query statements, the longest has more than 100 lines, confident what needs can be connected, can ... , think about, may have been in a fixed scene under the written, usually also to meet the actual needs for the purpose, do not know how to test the problem is how to do, and how to do. Then find some pen test to practice.

There are four tables stored in the following format:

--1, query "001" Course than "002" of the high grade of all students of the school number;
Select t1.s# from
(select S#,c#,score from SC where C # = 001) T1 INNER JOIN (select S#,c#,score from SC where C # = 002) t2
On t1.s#=t2.s#
where T1.score > T2.score

--2 and average scores of students with average scores greater than 60 points;
Select S#,average (Score)
From SC
where average (score) >60 GROUP by s#

--3, query All students of the school number, name, number of courses selected, total;
Select Student.s#,student.sname,count (sc.c#), sum (sc.score)
From student left join SC on student.s#=sc.s#
GROUP BY Student.s#,student.sname

--4, the number of teachers who queried the surname "Li";
Select COUNT (t#)
From teacher
Where tname like ' li% '

--5, inquiry did not learn the "cotyledons" teacher class students of the school number, name;
Select T1.s#,t1.sname
From student T1
Left join SC T2 on t1.s#=t2.s#
Left JOIN course T3 on t2.c#=t3.c#
Left join teacher T4 on t3.t#=t4.t#
where t4.tname! = ' cotyledons '

--6, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name;

Select T0.s#,t0.sname
From student t0
INNER JOIN (select S#,c#,score from SC where C # = 001) T1
On t0.s#=t1.s#
INNER JOIN (select S#,c#,score from SC where C # = 002) t2
On t0.s#=t2.s#

--7, inquires the study "Cotyledons" the teacher teaches all classes The student's school number, the name;
Select T1.s#,t1.sname
From student T1
Left join SC T2 on t1.s#=t2.s#
Left JOIN course T3 on t2.c#=t3.c#
Left join teacher T4 on t3.t#=t4.t#
where t4.tname = ' cotyledons '

--8 and name of all students who have a lower grade than the course number "001" for the course number "002";
Select T0.s#,t0.sname
From student t0
INNER JOIN (select S#,c#,score from SC where C # = 001) T1
On t0.s#=t1.s#
INNER JOIN (select S#,c#,score from SC where C # = 002) t2
On t0.s#=t2.s#
where T2.score<t1.score

--9, check all the course scores of less than 60 points of the student's school number, name;

Select T1.s#,t1.sname
From student T1
INNER JOIN (select S#,count (C #), COUNT (if (score<60,c#,null)) from SC where count (C #) = Count (if (score<60,c#,null)) ) T2
On t1.s#=t2.s#
GROUP BY T1.s#,t1.sname

--10, the inquiry did not learn all classes of the students of the school number, name;

--11, inquires at least one course and the student number is "1001" students learn the same student number and name;

--12, inquires at least study number for "001" classmates all the other students of the class number and name;

--13, inquires and "1002" number of students to study the course of the same class of other students and names;

--14, query the highest and lowest score of each section: shown in the following form: Course ID, highest score, lowest score

Select C#,max (Score), Min (score)
From SC
GROUP BY C #

--15, average grades from low to high and pass rates by section from highest to lowest order

--16, query the following percentage of the course average and pass rate (shown in "1 lines"): Enterprise Management (001), Marx (002), OO&UML (003), Database (004)

--17, query different teachers teach different courses average score from high to low display

Select T2.cname,t3.tname,average (T1.score)
from SC T1
Inner JOIN course T2 on t1.c#=t2.c#
Inner JOIN teacher T3 on t2.t#=t3.t#
GROUP BY T2.cname,t3.tname
Order by average (T1.score) desc

--18, check the following course results 3rd to 6th Students Transcript: Enterprise Management (001), Marx (002), UML (003), Database (004)

SELECT * FROM
(select T1.s#,t1.sname,t2.c#,t2.score,row_number () over (partition by t2.c# ORDER BY t2.score DESC) num
From student T1
INNER JOIN SC T2 on t1.s#=t2.s#
where C # in (001,002,003,004)) t
where T.num >=3 and t.num<=6

--19, statistical printing of various subjects, each score segment number: Course ID, course name, [100-85],[85-70],[70-60],[<60]

Select T1.c#,t2.cname,case when T1.score >=85 then [100-85] When (T1.score <85 and T1.score >=70) then [85-70] Wh En (T1.score <70 and T1.score >=60) then [70-60] When T1.score <60 then [<60] End as score, COUNT (t1.s#)
from SC T1
Inner JOIN course T2 on t1.c#=t2.c#
GROUP BY T1.c#,t2.cname

--20, inquiring students ' average grades and their rankings

Select T1.s#,t1.sname,average (T2.score), Row_number () over (order by average (T2.score)) as ranking
From student T1
INNER JOIN SC T2 on t1.s#=t2.s#

--21, check the records of the top three grades of each section: (regardless of the results of the situation)

SELECT * FROM
(select T1.s#,t1.sname,t2.c#,t2.score,row_number () over (partition by t2.c# ORDER BY t2.score DESC) num
From student T1
INNER JOIN SC T2 on t1.s#=t2.s#
) T
where T.num <=3

--22, number of students enrolled in each course

Select C#,count (s#)
From SC GROUP by C #

--23, check out the number and name of all students who have only one course of study

Select T1.s#,t2.sname
from SC T1
Inner JOIN student T2 on t1.s#=t2.s#
where count (t1.c#) =1
GROUP BY T1.s#,t2.sname

--24, query the number of boys and girls

Select Ssex,count (s#)
From student GROUP by Ssex

A list of students who--25 and queried the surname "Zhang"

Select S#,sname
From student
Where sname like ' Zhang% '

--26, query the same-sex student list, and count the number of the same name

Select Sname,count (s#)
From student
where count (s#) >1
GROUP BY Sname

--27, 1981-Born student list (note: The type of sage column in the student table is datetime)

--28, the average score of each course is queried, the results are ranked in ascending order by average, and the average grade is the same, descending by course number.

--29 and average scores of all students with average scores greater than 85

Select T1.s#,t2.sname,average (T1.score)
From SC t1 INNER join student T2 on t1.s#=t2.s#
where average (t1.score) >85 GROUP by t1.s#

--30, query Course name "database", and score less than 60 of the student's name and score

Select T0.sname,t1.score
From student t0
INNER JOIN SC t1 on t0.s#=t1.s#
Inner JOIN course T2 on t1.c#=t2.c#
where t2.cname = ' database ' and t1.score<60

--31, check the course of all students;

Select T1.s#,t1.sname,t2.c#,t3.cname
From student T1
Left join SC T2 on t1.s#=t2.s#
Inner JOIN course T3 on t2.c#=t3.c#

--32, inquire about the name, course name and score of any course score above 70 points;

Select T1.sname,t3.cname,t2.score
From student T1
INNER JOIN SC T2 on t1.s#=t2.s#
Inner JOIN course T3 on t2.c#=t3.c#
where t2.score>70

--33, fail-to-track courses, arranged by course number from large to small

--34 and name of the student whose course number is 003 and the course score is 80 or more;

Select T1.s#,t1.sname
From student T1
INNER JOIN SC T2 on t1.s#=t2.s#
Inner JOIN course T3 on t2.c#=t3.c#
where t3.c#=003 and t2.score>80

--35, number of students selected for the course

Select COUNT (Distinct s#)
From SC

--36 of students with the highest grade of students who have enrolled in the course "cotyledons", and their results

--37, check each course and the corresponding number of elective

Select C#,count (s#)
From SC
GROUP BY C #

--38, the number of students who have the same grades in different courses, course numbers, student scores

--39, query the top two of the best results for each door

--40, the number of students enrolled in each course is counted (more than 10 participants). Require the output of 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

--41, retrieving at least two elective courses of student number

Select s#
From SC
where COUNT (C #) >=2
GROUP BY s#

--42, check the course number and course name of all students enrolled

--43, inquires the names of students who have not learned any of the courses taught by the Cotyledons teacher

--44 and average scores of students who have failed in two or more courses

--45, search "004" course score is less than 60, in descending order by fractions of the student number

Select S#,score
From SC
where c#=004 and score<60
ORDER BY score Desc

Above, are written by themselves, if there are errors, please advise

SQL Pen question Finishing