The difficulty of 45 questions

1. Query all records of the score table in CNO Ascending, degree descending order.
Select *from Score ORDER by CNO Asc,degree desc

2. Check the average scores of the courses that have been enrolled by at least 5 students in the score table and begin with 3.
Select AVG (degree) as ' average ' from score where CNO in (
Select CNO from Score Group by CNO have COUNT (*) >5 and cno like ' 3% ')

3. Table joins, column-to-column comparisons can be made when there is no common column

Ps:select Sno,cno,degree,[rank] from Score,grade where degree>low and degree <upp

4. Query score the number of students who have chosen to learn more courses is a record of non-highest scores
Select *from Score where degree < (select MAX (degree) from score where Sno in (select Sno to score GROUP by Sno have C Ount (*) >1))
and Sno in (select Sno from Score Group by SNO have COUNT (*) >1)

5. Check the name of the teacher who has more than 5 students in a course.

Select Tname from teacher where TNO in (select Tno from Course where CNO in (select CNO from Score GROUP by CNO has Coun T (*) >5))

6. Check the elective number "3-105" course with a score higher than the CNO, Sno and degree of the students with the elective number "3-245", and sort by degree from highest to lowest order.

Select Cno,sno,degree from score where cno= ' 3-105 ' and degree >any (select degree from score where cno= ' 3-245 ') Order by D Egree ASC

7. Check the scores of students who have a lower average score than the course.
SELECT * FROM score as a where Degree<all (select AVG (degree) from score as b where a.cno=b.cno)

8. Check the class number of at least 2 men.
Select class from student where Ssex = ' Male ' GROUP by class has COUNT (*) >=2

9. Query all records in the student table in order of class and age from large to small.
SELECT *, Year (GETDATE ())-year (Sbirthday) as ' age ' from student order by class, Year (GETDATE ())-year (sbirthday) desc

Note: Having is the Where condition of group by, filtering the results generated by the group by, only with the aggregate function         

SQL inside as can be simple to understand the name, as a pair of tables, aliases, alias is to grab the front table to use

The difficulty of 45 questions