50 classic SQL interview questions (part 1)

50 classic SQL interview questions (part 1)

Click and follow "SQL database development" above,

Set as "top or star ", dry goods will be delivered as soon as possible.

I recently found this set of classic SQL interview questions when I was collecting SQL daily questions./

I sorted it out based on the questions, including the table structure, the relationship between the tables, test data, questions, reference answers, etc. Most of the reference answers are common on various database platforms./

Due to the large number of questions (full 50 questions), it may be difficult for the friends to digest and understand, so the content is divided into two parts, I hope it will be helpful to you.

1. table structure

1. Student table

Student(Sid,Sname,Sage,Ssex)

Student number, student name, date of birth, student gender

2. Course schedule

Course(Cid,Cname,Tid) 

Course number, course name, teacher number

3. Teacher table

Teacher(Tid,Tname)

Teacher number, teacher name

4. Transcript

SC (Sid, Cid, Score)

Student ID, Course ID, Score

2. the relationship between the tables

The relationship between the four tables is as follows:

Let's interpret the above relationship:\

1. The course number (Cid) of the course table Course is used as the primary key. You can see the course scores of one or more students in the score table (SC). The relationship between the two tables is 1:n. Similarly, the relationship between the student table (Student) and the score table (SC) is also 1:n

2. The teacher number (Tid) of the teacher table Teacher is used as the primary key. One or more courses can be carried in the course table (Course), and the relationship between the two tables is also 1:n.

3. test data

1. Student table

-Build a table statement CREATE TABLE Student (   SID VARCHAR ( 10 ),   Sname nvarchar ( 10 ),   Sage datetime,   Ssex nvarchar ( 10 ) ) --Insert test data INSERT INTO Student VALUES( '01'  , N'Zhao Lei'  , '1990-01-01'  , N'male ' ) INSERT INTO Student VALUES( '02'  , N ' '  , '1990-12-21'  , N'male ' ) INSERT INTO Student VALUES( '03'  , N'Sun Feng'  , '1990-05-20'  , N'Male ' ) INSERT INTO Student VALUES( '04'  , N ' '  , '1990-08-06'  , N'male ' ) INSERT INTO Student VALUES( '05'  , N ' '  , '1991-12-01'  , N ' ' ) INSERT INTO Student VALUES( '06'  , N ' '  , '1992-03-01'  , N ' ' ) INSERT INTO Student VALUES( '07'  , N'Zheng Zhu'  , '1989-07-01'  , N ' ' ) INSERT INTO Student VALUES( '08'  , N ' '  , '1990-01-20'  , N ' ' ) copying the code

The results are as follows:

2. Course schedule

-Build a table statement CREATE TABLE Course (   CID VARCHAR ( 10 ),   Cname nvarchar ( 10 ),   TID VARCHAR ( 10 ) ) --Insert test data INSERT INTO Course VALUES( '01'  , N ' '  , '02' ) INSERT INTO Course VALUES( '02'  , N'mathematics'  , '01' ) Course, the VALUES the INTO the INSERT ( '03'  , N 'English'  , '03' ) copying the code

The results are as follows:\

3. Teacher table

-Build a table statement CREATE TABLE Teacher (   TID VARCHAR ( 10 ),   Tname nvarchar ( 10 ) ) --Insert test data INSERT INTO Teacher VALUES( '01'  , N'Zhang San' ) INSERT INTO Teacher VALUES( '02'  , N ' ' ) The INTO Teacher the VALUES the INSERT ( '03'  , N 'Wang Wu' ) copying the code

The results are as follows:\

4. Transcript

-Build a table statement CREATE TABLE SC (   SID VARCHAR ( 10 ),   CID VARCHAR ( 10 ),   score DECIMAL ( 18 , 1 ) ) --Insert test data INSERT INTO SC VALUES( '01'  , '01'  , 80 ) INSERT INTO SC VALUES( '01'  , '02'  , 90 ) INSERT INTO SC VALUES( '01'  , '03'  , 99 ) INSERT INTO SC VALUES( '02'  , '01'  , 70 ) INSERT INTO SC VALUES( '02'  , '02'  , 60 ) INSERT INTO SC VALUES( '02'  , '03'  , 80 ) INSERT INTO SC VALUES( '03'  , '01'  , 80 ) INSERT INTO SC VALUES( '03'  , '02'  , 80 ) INSERT INTO SC VALUES( '03'  , '03'  , 80 ) INSERT INTO SC VALUES( '04'  , '01'  , 50 ) INSERT INTO SC VALUES( '04'  , '02'  , 30 ) INSERT INTO SC VALUES( '04'  , '03'  , 20 ) INSERT INTO SC VALUES( '05'  , '01'  , 76 ) INSERT INTO SC VALUES( '05'  , '02'  , 87 ) INSERT INTO SC VALUES( '06'  , '01'  , 31 ) INSERT INTO SC VALUES( '06'  , '03'  , 34 ) INSERT INTO SC VALUES( '07'  , '02'  , 89 ) The INTO SC the VALUES the INSERT ( '07'  , '03'  , 98 ) to copy the code

The results are as follows:\

4. Interview questions and reference answers

1. Query the information and course scores of students with higher grades in the "01" course than in the "02" course

--method one SELECT a.*, b.score FROM Student a JOIN SC b ON a.SID = b.SID JOIN sc c ON b.SID = c.SID WHERE b.Cid = '01' AND c.Cid = '02' AND b.Score> c.Score --Method Two SELECT A.*, B.score FROM Student A JOIN (SELECT * FROM SC WHERE CID = '01' ) B ON A.SID = B.SID JOIN  (SELECT * FROM SC WHERE CID = '02' ) C ON C.SID = B.SID WHERE B.score> C.score Copy code

2. Query the situation that "01" course and "02" course exist at the same time

--method one SELECT   A.* FROM Student A JOIN SC B ON A.SID=B.SID JOIN SC C ON C.SID=B.SID WHERE B.CID = '01' AND C.CID = '02' --Method Two SELECT   A.* FROM Student A JOIN (SELECT * FROM SC WHERE CID = '01' ) B ON A.SID=B.SID The JOIN (the WHERE the CID the SELECT * the FROM SC = '02' ) C = the ON B.SID C.SID duplicated code

3. Query the situation where the "01" course exists but the "02" course may not exist (it will be displayed as null if it does not exist)

SELECT   * FROM (SELECT * FROM SC WHERE CID = '01' ) A LEFT JOIN  (The WHERE the CID the SELECT * the FROM SC = '02' ) B = the ON a.sid B.SID duplicated code

4. Query the situation that there is no "01" course but "02" course

SELECT * FROM SC WHERE CID = '02' AND SID NOT IN (   SELECT SID FROM SC   WHERE CID = '01' ) Copy code

5. Query the student number and student name and average grade of students whose average score is greater than or equal to 60 points

SELECT   A. SID,   B.Sname,   A.dc FROM   (     SELECT SID,AVG (score) dc     FROM SC GROUP BY SID     HAVING AVG(score)>= 60   ) A JOIN Student B ON A.SID = B.SID Copy code

6. Query the information of students who have scores in the SC table

SELECT   * FROM   Student WHERE   SID IN (SELECT DISTINCT SID FROM SC) Copy code

7. Query the student number, student name, total number of selected courses, and total score of all courses of all students (no score is displayed as null)

SELECT   A. SID,   A.Sname,   B.Cnt,   B.Total FROM Student A LEFT JOIN  (     SELECT       SID,       COUNT (CID) Cnt,       SUM (score) Total     FROM SC     GROUP BY SID   ) B ON A.SID = B.SID Copy code

8. Check information about students with grades

SELECT   A. SID,   A.Sname,   B.Cnt,   B.Total FROM Student A RIGHT JOIN  (     SELECT       SID,       COUNT (CID) Cnt,       SUM (score) Total     FROM SC     GROUP BY SID   ) B ON A.SID = B.SID Copy code

9. Query the number of teachers surnamed "Li" 

SELECT   COUNT (*) Number of teachers surnamed Li FROM Teacher Tname the LIKE the WHERE  '% Li' duplicated code

10. Inquire about the information of students who have learned the lessons of "Zhang San" 

SELECT * FROM Student WHERE SID IN  ( select  DISTINCT SID FROM SC a JOIN Course b ON a.cid=b.cid JOIN Teacher c ON b.Tid=c.Tid = C.Tname the WHERE 'John Doe' ) copying the code

11. Query information about students who have not studied all courses 

SELECT * FROM Student WHERE SID IN  ( SELECT SID FROM SC GROUP BY SID HAVING COUNT (CID) < 3 ) Copy code

12. Query the information of at least one class that is the same as the student whose student ID is "01" 

SELECT * FROM Student WHERE SID IN ( SELECT DISTINCT SID FROM SC WHERE CID IN  ( SELECT CID FROM SC WHERE SID = '01'  ) ) Copy code

13. Query the information of other students who are learning exactly the same course as the student with number "01" 

SELECT * FROM Student WHERE SID in ( SELECT SID FROM SC WHERE CID in  (SELECT DISTINCT CID FROM SC WHERE SID = '01' ) and SID<> '01' GROUP BY SID COUNT HAVING (the CID) = . 3 ) copying the code

14. Query the names of students who have not studied any course taught by teacher "Zhang San" 

SELECT * FROM Student WHERE SID NOT IN  ( select  DISTINCT SID FROM SC a JOIN Course b ON a.cid=b.cid JOIN Teacher c ON b.Tid=c.Tid = C.Tname the WHERE 'John Doe' ) copying the code

15. Query the student ID, name and average grade of students who failed two or more courses 

SELECT A.SID, A.Sname, B. Average grade FROM Student A RIGHT JOIN (SELECT SID, AVG (score) average score FROM SC WHERE score< 60  GROUP BY SID  HAVING COUNT(score)>= 2 )B on A.SID=B.SID Copy code

16. Retrieve the information of students whose scores of "01" are less than 60, sorted in descending order of scores

SELECT A.*,B.score FROM  Student A JOIN SC B ON A.SID=B.SID WHERE CID = '01'  AND Score< 60  ORDER BY score DESC Copy code

17. Display the grades and average grades of all the courses of all students in descending order of grade average

SELECT SID, MAX( case  CID when  '01'  then score else  0  end) '01' , MAX( case  CID when  '02'  then score else  0  end) '02' , MAX( case  CID when  '03'  then score else  0  end) '03' , AVG (score) average score FROM SC GROUP BY SID ORDER BY Average score DESC Copy code

18. Query the highest score, lowest score and average score of each subject:

Displayed in the following form: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate, excellent rate\

Passing is >=60, medium is: 70-80, excellent is: 80-90, excellent is: >=90

--SQL Server's solution SELECT DISTINCT A. CID, Cname, highest score, lowest score, average score, pass rate, medium rate, excellent rate, excellent rate FROM SC A LEFT JOIN Course on A.CID=Course.CID LEFT JOIN (SELECT CID, MAX (score) highest score, MIN (score) lowest score, AVG (score) average score FROM SC GROUP BY CID) B on A.CID=B.CID LEFT JOIN (SELECT CID,(convert(decimal( 5 , 2 ),(sum( case  when score>= 60  then  1  else  0  end)* 1.00 )/COUNT(*))* 100 ) Pass rate FROM SC GROUP BY CID )C on A.CID=C.CID LEFT JOIN (SELECT CID,(convert(decimal( 5 , 2 ),(sum( case  when score >= 70  and score< 80  then  1  else  0  end)* 1.00 )/COUNT(*))* 100 )Medium rate FROM SC GROUP BY CID)D on A.CID=D.CID LEFT JOIN (SELECT CID,(convert(decimal( 5 , 2 ),(sum( case  when score >= 80  and score< 90  then  1  else  0  end)* 1.00 )/COUNT(*))* 100 )Excellent rate FROM SC GROUP BY CID)E on A.CID=E.CID LEFT JOIN (SELECT CID,(convert(decimal( 5 , 2 ),(sum( case  when score >= 90  then  1  else  0  end)* 1.00 )/COUNT(*))* 100 )Excellent rate FROM SC GROUP BY CID)F on A.CID=F.CID Copy code

(Hint: You can slide the code left and right)

19. Sort by the scores of each subject and display the rankings. If the score is repeated, the ranking will remain vacant

SELECT *, RANK () over ( order by score desc) Ranking FROM SC duplicated code

20 Sort according to the scores of each subject, and display the rankings. When the scores are repeated, the rankings are combined

SELECT *, DENSE_RANK () over ( order by score desc) Ranking FROM SC duplicated code

21. Query the student's total score and rank it. If the total score is repeated, the ranking will remain vacant

SELECT *,RANK()over(order by total score desc) ranking FROM( SELECT SID, SUM(score) FROM SC GROUP BY SID )A Copy code

22 Query the student's total score and rank it. If the total score is repeated, the vacancy will not be reserved.

SELECT *,DENSE_RANK()over(order by total score desc) ranking FROM( SELECT SID, SUM (score) total score FROM SC GROUP BY SID )A Copy code

23. Count the number of people in each score section of each subject score: course number, course name, [100-85], [85-70], [70-60], [60-0] and percentage

SELECT DISTINCT A.CID,B.Cname,C.[ 100 -85 ], Percentage of C., D. [ 85 -70 ], Percentage of D., E. [ 70 -60 ], Percentage of E. Percentage, F.[ 60 -0 ], Percentage of F. FROM SC A LEFT JOIN Course B ON A.CID=B.CID LEFT JOIN (SELECT CID,sum( case  when score> 85  and score<= 100  then  1  else  null end) [ 100 -85 ], convert(decimal( 5 , 2 ),(sum( case  when score> 85  and score< 100  then  1  else  null end))* 1.00/COUNT(*))* 100  % FROM SC GROUP BY CID)C on A .CID=C.CID LEFT JOIN (SELECT CID,sum( case  when score> 70  and score<= 85  then  1  else  null end)[ 85 -70 ], convert(decimal( 5 , 2 ),(sum( case  when score> 70  and score<= 85  then  1  else  null end))* 1.00/COUNT(*))* 100  % FROM SC GROUP BY CID)D on A.CID=D.CID LEFT JOIN (SELECT CID,sum( case  when score> 60  and score<= 70  then  1  else  null end)[ 70 -60 ], convert(decimal( 5 , 2 ),(sum( case  when score> 60  and score<= 70  then  1  else  null end))* 1.00/COUNT(*))*  Percentage of 100 FROM SC GROUP BY CID)E on A.CID=E.CID LEFT JOIN (SELECT CID,sum( case  when score> 0  and score<= 60  then  1  else  null end)[ 60 -0 ], convert(decimal( 5 , 2 ),(sum( case  when score> 0  and score<= 60  then  1  else  null end))* 1.00/COUNT(*))* 100  % FROM SC GROUP BY CID)F on A.CID=F.CIDCopy code

24. Query the top three records of each subject

SELECT * FROM  (SELECT *,rank()over (partition by CID order by score desc) A FROM SC)B BA the WHERE <= . 3 duplicated code

25. Query the number of students selected for each course 

SELECT CID, COUNT (SID) number of students FROM SC GROUP BY CID Copy the code

The above is the content of this sharing. If you don t understand, please leave a message below to discuss.

End

```php

Back-end reply keyword: 1024, get a well-organized technical dry goods Back-end reply keyword: enter the group, take you into the master-like communication group. Recommended reading

* [It's miserable! Even the major programming languages have been set up! ](http://mp.weixin.qq.com/s?\__biz=MzA3MTg4NjY4Mw%3D%3D\&chksm=88a59586bfd21c90bf46d69cf57a5144f9ba416401b22b5a5b8d23cfa6afd4f7d=457e6960a)688b&midechad=457eccede6960a\#88a59586bfd21c90bf46d69cf57a5144f9ba416401b22b5a5b8d23cfa6afd4f7d=457e6960a * [SELECT COUNT(\*) What did the bottom layer do? ](http://mp.weixin.qq.com/s?\__biz=MzA3MTg4NjY4Mw%3D%3D\&chksm=88a59544bfd21c52809788273b193c5f3443ae3246e5f8921f3b823a4ae243433073b9d9de36\&idx=24ae243433073b9d9de36\&idx=1243433073b9d9de36\n * [SQL query, are you still SELECT/*? ](http://mp.weixin.qq.com/s?\__biz=MzA3MTg4NjY4Mw%3D%3D\&chksm=88a59566bfd21c70746026c7edf3a98438c9f9a8f13d5cb999c193fa8d19e966c332f4b4cba 8d19e966c332f4b4cbaa8d19e966c332f4b4cbaa8d19e966c332f4b4cfa8d19e966c53549\&sc763549\&c/c/c/c/c/c. * [Red Alert 1 game is open source, the code is very standardized. Netizen: beautiful meals](http://mp.weixin.qq.com/s?\__biz=MzA3MTg4NjY4Mw%3D%3D\&chksm=88a59507bfd21c114d91aed5997d85e8f1e79408a9990837110cd793a19762284519f57ba78c\&idbbc\&id51echae=salchatchae=245e706937970\&id51e79e79e=&mid51e6519/s? **This is an official account that can learn technology, welcome to pay attention**![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/734b5b6d284146699eb7493b981916ea~tplv-k3u1fbpfcp-zoom-1 .image) Click "**Read Original**" to learn about SQL Bootcamp Copy code