Teaching Ideas SQL entry exercise student achievement III. Multi-Table complex subqueries

1. query the scores of all students in Dalian.
2. query the information of students who have never taken any tests.
3. query the information of students with examination scores.
4. Add 10 points to the score of the course numbered 3 for all students in Dalian.
5. Delete the scores of all C # courses.
6. query the average score of the course with the course number 3, and display the student number with the course score, and the difference between the score and the average score.
7. query the names of all students taking the test.
8. query the number of students with scores.
9. query the names and contact information of students with more than two scores.
10. query which students have C # test scores and display the names of these students.
11. query the name of the instructor and add the number.
12. Randomly display two pieces of data in the student information table: Use the newid () function
13. query the student information whose contact number is null. Replace the contact number with "NONE.
14. The queried regions in Dalian are all displayed as null.
15. query the names of instructors whose average score is over 70.

New knowledge:

To display null values:

• The isnull (A, 'A') function can convert null values to valid values. Change the null value of column name a to 'A' for display.
• The nullif (A, 'A') function can generate null values based on specified conditions. Change the value AA of column name a to null.

Newid function:

• 
  Use the newid () function to generate random rows.

Answer
1 And query the scores of all students in Dalian.
Select S. Number , C. Score From Chengji As C, student As S Where C. Number = S. Number   And S. diqu = ' Dalian '
Select   *   From Chengji Where   Number   In ( Select   Number   From Student Where Diqu = ' Dalian ' )

2 Query the information of students who have never taken any tests.
Select   *   From Student Where   Number   Not   In ( Select   Number   From Chengji)
3 Query the information of students with examination scores.
Select   *   From Student Where   Number In ( Select   Number   From Chengji)
4 . Add 10 points to the score of the course numbered 3 for all students in Dalian.
Update Chengji Set Score = Score + 10   Where Scorenumber = 3
5 Delete the scores of all C # courses.
Delete Chengji Where Scorenumber = ( Select Scorenumber From Score Where Kemu = ' C # ' )
6 Query the average score of the course with the course number 3, and display the student number with the course score, and the difference between the score and the average score.
Select   Number , Score - ( Select   AVG (Score) From Chengji Where Scorenumber = 3 ) From Chengji Where Scorenumber = 3
7 Query the names of all students who have taken the test.
Select Name From Student Where   Number   In ( Select   Max ( Number ) From Chengji Group   By   Number )
8 Query the number of students with scores.
Selectnumber, name From Student Where   Number   In
( Select   Max ( Number ) From Chengji Group   By   Number   Having   Count (Scorenumber) = ( Select   Count ( * ) From Score ))
9 Query the names and contact information of students with more than two scores.
Select Name, lianxidianhua From Student Where   Number   In ( Select   Number   From Chengji Group   By   Number   Having   Count (Scorenumber) > 2 )
10 Query which students have C # test scores and display the names of these students.
Select Name From Student Where   Number   In ( Select   Number   From Chengji Where Scorenumber = ( Select Scorenumber From Score Where Kemu = ' C # ' ))
Add a number to the result set
11 . Query the number of the instructor and add the number.
Select Serial number = ( Select   Count ( * ) From Score As A Where A. scorenumber <= B. scorenumber), teacherid From Score As B
Select Serial number = Identity ( 1 , 1 ), Teachername Into # Newtable From Score
12 . Randomly display two data entries in the student information table: Use the newid () function
Select   Top   2   *   From Student Order   By   Newid ()
13 . Query the student information whose contact number is null. Replace the contact number with "NONE.
Select Name, Isnull ( Convert ( Char ( 20 ), Lianxidianhua ), ' None ' ) From Student
14 . The queried regions in Dalian are displayed as null.
Select Name, Nullif (Diqu, ' Dalian ' ) From Student
15 . Query the names of instructors whose average score is over 70.
Select Teachername From Teacherinfo Where Teacherid In  
( Select Teacherid From Score Where Scorenumber In
( Select Scorenumber From Chengji Group   By Scorenumber Having   AVG (Score) > = 70 ))