50 SQL interview questions

Time:2021-4-11

This blog SQL script address: gitee

preparation

There are four tables as follows:

Student form: student (student number, student name, date of birth, gender)

Grade: score (student number, course number, grade)

Curriculum: Course (course number, course name, teacher number)

Teacher table: Teacher

1、 Create databases and tables

In order to demonstrate the running process of the topic, we first create the database and table in the client heidis QL according to the following statement.

1. Create table

1.1 create student table

1.2. Create score

Create a “grade sheet.”. “Student number” and “course number” of the curriculum table are set as the primary key constraint (joint primary key), and the column “grade” is set as the float (floating point value)

1.3 create a course

The course number of the curriculum is set as the primary key constraint

1.4 teachers

The teacher number column of the teacher table is set as the primary key constraint,

The column of teacher’s name is set to “null” (not checked in the red box), which means that the column is allowed to contain null values

2. Adding data to a table

2.1 student table

Insert into student (student number, name, date of birth, gender) 
Values ('0001 ','monkey','1989-01-01 ','male');

Insert into student (student number, name, date of birth, gender) 
Values ('0002 ','monkey','1990-12-21 ','female');

Insert into student (student number, name, date of birth, gender) 
Values ('0003 ','ma Yun','1991-12-21 ','male');

Insert into student (student number, name, date of birth, gender) 
Values ('0004 ','wang Sicong','1990-05-20 ','male');

2.2 score

Insert into score 
values('0001' , '0001' , 80);

Insert into score 
values('0001' , '0002' , 90);

Insert into score 
values('0001' , '0003' , 99);

Insert into score 
values('0002' , '0002' , 60);

Insert into score 
values('0002' , '0003' , 80);

Insert into score 
values('0003' , '0001' , 80);

Insert into score 
values('0003' , '0002' , 80);

Insert into score 
values('0003' , '0003' , 80);

2.3 Curriculum

Insert into course
Values ('0001 ',' Chinese ',' 0002 ');

Insert into course
Values ('0002 ','mathematics','0001');

Insert into course
Values ('0003 ','English','0003 ');

2.4 teacher table

Insert into teacher 
Values ('0001 ','mengzaza');

Insert into teacher 
Values ('0002 ','ma Huateng');

--The teacher's name here is null
Insert into teacher 
values('0003' , null);

--The teacher's name here is an empty string (')
Insert into teacher 
values('0004' , '');

50 interview questions

In order to facilitate learning, 50 interview questions are classified

1、 Simple query

Search the list of students surnamed “monkey”

1. Query the number of teachers surnamed Meng

Select count (teacher number)
from teacher
Where teacher's name is like 'Meng%';

2、 Summary analysis

2. Query the total score of course number “0002”

Select sum
from score
Where course number ='0002 ';

3. Query the number of students who have chosen the course

Select count (distinct student number) as number of students 
from score;

4. Query the highest and lowest scores of each subject, and display them in the following form: course number, highest score and lowest score

Select course number, max (score) as the highest score, min (score) as the lowest score
from score
Group by course number;

5. Query the number of students selected for each course

Select course number, count as number of students
from score
Group by course number;

6. Query the number of boys and girls

Select gender, count (*) as number
from student
Group by gender;

7. Query the student number and average score of students with average score more than 60

Select student number, AVG (grade) as average
from score
Group by student number
Having AVG > 60;

8. Check the student number of at least two courses

Select student number, count (course number) as number of elective courses
from score
Group by student number
Having count > = 2;

9. Query the list of students with the same name and surname and count the number of students with the same name

Select name, count (*) as number
from student
Group by name
having count(*)>=2;

10. Check the failed courses and arrange them by course number

Select course number
from score 
Where score < 60
Order by course number DESC;

11. Query the average score of each course. The results are sorted in ascending order according to the average score. The same average score is sorted in descending order according to the course number

Select course number, AVG (grade) as average
from score
Group by course number
Order by average ASC, course number DESC;

12. The student numbers of students whose course number is “0004” and score is less than 60 are searched, and the results are arranged in descending order

Select student number
from score
Where course number ='0004 'and grade < 60
Order by DESC;

13. Count the number of students taking each course (only for courses with more than 2 students)

It is required to output the course number and the number of electives. The query results are sorted in descending order according to the number of students. If the number of students is the same, they are sorted in ascending order according to the course number

Select course number, count as' number of electives'
from score
Group by course number
Having count > 2
Order by count (student number) DESC, course number ASC;

14. Inquire the student number and average score of students who fail two or more courses

Select student number, AVG (grade) as average
from score
Where score < 60
Group by student number
Having count > 2;

.

15. Query the total score of students and rank them

Select student number, sum as
from score 
Group by student number
Order by sum;

16. Query the student number and average score of students with average score greater than 60

Select student number, AVG (grade) 
from score 
Group by student number  
Having AVG > 60;

3、 Complex query

17. Query the student number and name of all students whose score is less than 60

Select student
from student,score
Where < 60 and student. ` student number '= score. ` student number';

18. Query the student number and name of the students who have not learned all the courses

Select score
from student,score
Group by score
Having count < (select count from course);

19. Find out the student numbers and names of all the students who have only taken two courses

Select score
from student,score
Where student number = score`
Group by score`
Having count (course number) = 2;

20. List of students born in 1990

Select student number, name 
from student 
Where year = 1990;

21. Query the age of each student (accurate to the month)

Select student number, timestampdiff (month, date of birth, now ()) / 12 as age
from student;

22. Find out the students who have their birthdays this month

select * 
from student 
Where month (date of birth) = month (now ());

4、 Multi table query

23. Check the student number, name, number of courses and total score of all students

Select A. student number, A. name, count (B. course number) as number of selected courses, sum (B. score) as total score
from student as a left join score as b
On A. student number = B. student number
Group by A;

24. Query the student number, name and average score of all students whose average score is greater than 85

Select A. student number, A. name, AVG (B. grade) as average
from student as a left join score as b 
On A. student number = B. student number
Group by A
Having AVG (B. score) > 85;

25. Query the students’ course selection: student number, name, course number and course name

Select A. student number, A. name, C. course number, C. course name
from student a 
Student number = B. student number 
Inner join course C on B;

26. Find out the number of students who have passed and failed each course

Select course number,
Sum (case when > = 60 then 1 
	 else 0 
    End) as the number of people who have passed,
Sum (case when < 60 then 1 
	 else 0 
    End) as number of failed students
from score
Group by course number;

27. Use segments [100-85], [85-70], [70-60], [< 60] to count the scores of each subject, including the number of students in each score segment, course number and course name

Select A. course number, B. course name,
Sum (case when score between 85 and 100 
	 then 1 else 0 end) as '[100-85]',
Sum (case when score > = 70 and score < 85) 
	 then 1 else 0 end) as '[85-70]',
Sum (case when score > = 60 and score < 70  
	 then 1 else 0 end) as '[70-60]',
Sum (case when score < 60 then 1 else 0 end) as' [< 60] '
from score as a right join course as b 
On A. course number = B. course number
Group by A. course number, B. course name;

28. Query the student number and name of the student whose course number is 0003 and whose course score is more than 80

Select A. student number, A. name
From student as a inner join score as B on a
Where B. course number ='0003 'and B. grade > 80;

.

29. Search the information of students whose “0001” course score is less than 60 in descending order

The idea is as follows:

.

Select a. *, B 
from student as a 
inner join score as b 
On A. student number = B. student number 
Where B. grade < 60 and B. course number = 01
Order by B;

30. Query the average score of different courses taught by different teachers from high to low

[knowledge point] grouping + condition + sorting + multi table connection, the idea is shown in the figure

.

Select A. teacher number, A. teacher name, AVG (C. grade) 
from  teacher as a 
inner join course as b 
On A. teacher number = B. teacher number
Inner join score C on B
Group by A
Order by AVG (C. score) DESC;

31. Query the name and score of students whose course name is “Mathematics” and whose score is lower than 60

[knowledge point] multi table connection, as shown in the figure

Select A. name, B. grade 
from student as a 
inner join score as b 
On A. student number = B. student number 
Inner join course C on B 
Where B. grade < 60 and C. course name ='mathematics';

32. Query the name, course name and score of any course with score above 70 (similar to the above question)

Select A. name, C. course name, B. grade
from student as a
Inner join score as b
On A. student number = B. student number
Inner join course C on B
Where B. score > 70;

33. Check the student number, name and average score of students who failed two or more courses

[knowledge point] grouping + condition + multi table connection

Calculate the number of failing grades of each student number, screen out more than 2 student numbers and find out their names and average scores

img

Select B. name, AVG (A. grade), A. student number
from score as a
Inner join student as b
On A. student number = B. student number
Where A. grade < 60
Group by A
Having count (A. student number) > = 2;

34. Query the student number, course number and student score of students with the same score in different courses

Select distinct A. student number, A. grade, A. course number
from score as a
inner join score as b
On A. student number = B. student number
Where A. grade = B. grade and A. course number! = B. course number;

35. Query the student numbers of all students whose course number is “0001” is higher than that of “0002”

Student number  
from 
(select student number, grade from score where = 01) as a
inner join 
(select student number, grade from score where = 02) as B
On A. student number = B. student number 
Student number = a 
Where A. achievement > B. achievement;

36. Query the student number and name of the students who have studied the course No. “0001” and the course No. “0002”

Student number  
from 
(select student number, grade from score where = 01) as a
inner join 
(select student number, grade from score where = 02) as B
On A. student number = B. student number 
Student number = a 
Where A. achievement > B. achievement;

37. Inquire the student numbers and names of the students who have learned all the lessons taught by “mengzaza” teacher

Select S. student number, S. name, A. student number, B. course number, C. teacher number, C. teacher name
from student as s
inner join score as a
Student number
Inner join course B on a
Inner join teacher C on B
Where C. teacher's name ='mengzaza ';

38. Query the names of students who have not learned any course taught by teacher “mengzaza” (similar to the above question, “not learned” is realized by not in)

Select name, student number
from student
Where student number not in(
Student number
from student as a
inner join score AS b
On A. student number = B. student number
Inner join course as C on B
Inner join teacher as D on C
Where D. teacher's name ='mengzaza ');

39. Inquire the student number and name of the students who have not studied the “mengzaza” class (similar to the above question)

Select student number, name 
from student
Where student number not in
(select student number from score where)=
(select course number from course where)= 
(select teacher number from teacher where teacher name ='mengzaza ')
)
);

40. Query the name and grade of the student with the highest score in the course given by teacher “mengzaza” (similar to the above question, use grade ranking, use limit 1 to get the highest one)

Select A. name, B. grade
From student as a
Inner join score as B on a
Inner join course as C on B
Inner join teacher as D on C
Where D. teacher's name ='mengzaza '
Order by B. grade desc limit 1;

41. Query the student number and name of the student who has at least one course and the same course as the student whose student number is “0001”

Select student number, name
from student 
Where student number in
(select distinct (student number) from score where
(select course number from score where = 0001))
And student number! = 0001;

42. Show the grades of all courses and average grades of all students from high to low

[knowledge point] connect multiple tables to create new fields, as shown in the figure

Select A. student number, AVG (A. grade),
Max (case when B. course name ='mathematics' then A. grade else null end) as mathematics,
Max (case when B. course name ='language 'then A. grade else null end) as language,
Max (case when B. course name ='English 'then A. grade else null end) as English
from score as a
Inner join course as b
On A. course number = B. course number
Group by A;

5、 SQL advanced function: window function

43. Query the average score and ranking of students

[knowledge point] window function ranking, as shown in the figure

Select student number, AVG as average, row_ Number () over (order by AVG DESC) as
from score
Group by student number;

44. Rank according to the scores of each subject and display the ranking

Select course number, row_ Number () over (partition by course number order by grade) as ranking
from score;

45. Check the names of the top two students who have the best scores in each subject

[knowledge point] window function ranking + multi table connection + condition

preview

Select A. course number, B. name, A. grade, A. ranking from(
Select course number, student number, grade, row_ Number () over (partition by course number, order by grade DESC) as ranking
from  score) as a 
Student number = student number 
where a.ranking<3;

45. Query the information of the second to third place students in all courses and the course scores (similar to the previous question)

Select B. name, A. course number, A. grade 
from (
Select course number, student number, grade, row_ Number () over (partition by course number, order by grade DESC) as ranking
from  score ) as a 
inner join student as b 
On A. student number = B. student number 
where a.ranking in(2,3);

46. Check the records of the top three students in each subject (regardless of the parallel results) (similar to the previous question)

Select B. name, A. course number, A. grade 
from(
Select course number, student number, grade,
row_ Number () over (partition by course number, order by grade DESC) as' ranking '
from  score) as a 
inner join student as b 
On A. student number = B. student number 
where a.ranking<4;

Recommended Today

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

Hello, I’m younger brother. A few days ago, I shared the second interview question, the interview site of search engine in MySQL. This question is the interview at normal temperature. After reading it, I’m sure you will gain something in terms of database engine If you haven’t read my first share, you can refer to […]