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

```
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

```
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;
```