50 classic SQL questions

Time:2021-1-24

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

According to the above information, write the corresponding SQL statement according to the following requirements.

PS: these questions examine the writing ability of SQL. For this type of questions, you need to make clear the relationship between the four tables. The best way is to draw the association diagram on the draft paper, and then write the corresponding SQL statement. The following figure is the relationship diagram of the four tables I drew. We can see which foreign keys are associated between them

50 classic SQL questions

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 Navicat according to the following statement.

(if you don’t know what a database is or what a client Navicat is, you can learn this first:

Learning SQL from scratch (Introduction)

50 classic SQL questions

1. Create a table

1) Create student table

Create student table in client Navicat according to the figure below

50 classic SQL questions

The student number column of the student table is set as the primary key constraint. The following figure shows the data type and constraint set for each column

50 classic SQL questions

After creating the table, click “save”

50 classic SQL questions

2) Create a score

The same step is to 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)

50 classic SQL questions

3) Create a course

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

50 classic SQL questions

4) Teacher table

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

50 classic SQL questions

2. Add data to the table

50 classic SQL questions

1) Add data to student table

SQL to add data

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');

Operation in client Navicat

50 classic SQL questions

50 classic SQL questions

50 classic SQL questions

2) Score

SQL to add data

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

Operation in client Navicat

50 classic SQL questions

3) Curriculum

SQL to add data

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

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

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

Operation in client Navicat

50 classic SQL questions

4) Add data to teacher table

SQL to add data

--Teacher table: adding data
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' , '');

Operation in client Navicat

50 classic SQL questions

Add results

50 classic SQL questions

3、 50 interview questions

In order to facilitate learning, I classified 50 interview questions

1. Simple query

50 classic SQL questions

Search the list of students surnamed “monkey”

50 classic SQL questions

Query the number of teachers surnamed “Meng”

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

If you can’t do the above questions, you can review the SQL knowledge involved in this part:

Learning SQL from scratch: simple query www.zhihu.com50 classic SQL questions

2. Summary analysis

50 classic SQL questions

Interview questions: query the total score of course number “0002”

*
Analysis ideas
Select query result [total score: summary function sum]
From which table to find data [score]
Where query condition [course number is 0002]
*/
Select sum
from score
Where course number ='0002 ';

Query the number of students who have chosen the course

/*
How many candidates are there for the course
Select student number. There is a duplicate value in the student number in the grade table that needs to be removed
From to find the score from the curriculum;
*/
Select count (distinct student number) as number of students 
from score;

50 classic SQL questions

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

/*
Analysis ideas
Select query result [Course ID: alias of course number, highest score: Max (score), lowest score: min (score)]
From which table to find data [score]
Where query condition [no]
Group by group [scores of each subject: that is, the scores of each course should be grouped by course number];
*/
Select course number, max (score) as the highest score, min (score) as the lowest score
from score
Group by course number;

Query the number of students selected for each course

/*
Analysis ideas
Select query result [course number, number of students taking the course: summary function count]
From which table to find data [score]
Where query condition [no]
Group by [each course: group by course number];
*/
Select course number, count
from score
Group by course number;

Query the number of boys and girls

/*
Analysis ideas
Select query result [gender, number of people corresponding to gender: summary function count]
From which table to search for data [gender is in the student table, so the student table is searched]
Where query condition [no]
Group by [number of boys and girls: by gender]
Having specifies conditions for grouping results [none]
Order by sorts the query results [none];
*/
Select gender, count (*)
from student
Group by gender;

50 classic SQL questions

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

/* 
The title is translated into vernacular
Average score: to expand, it is to calculate the average score of each student
When it comes to "each", it means grouping
If the average score is more than 60, it is to specify the conditions for the grouping results

Analysis ideas
Select query result [student number, average score: summary function AVG (score)]
From which table to find data
Where query condition [no]
Group by group
Having to specify the conditions for grouping results [average score greater than 60 points]
*/
Select student number, AVG (grade)
from score
Group by student number
Having AVG > 60;

Check the student number of at least two courses

/* 
Translated into Vernacular:
In the first step, we need to calculate the data of each student's elective course, which needs to be grouped by student number
Step 2, at least two courses should be selected: that is, the number of courses selected by each student is > = 2, and conditions should be specified for grouping results

Analysis ideas
Select query result [student number, number of elective courses per student: summary function count]
From which table to find data
Where query condition [select at least two courses: you need to calculate how many courses each student has taken, and you need to use grouping, so there is no where clause here]
Group by group [number of elective courses for each student: group by course number, and then calculate the number of elective courses with the summary function count]
Having specifies conditions for grouping results [at least two courses: number of courses per student > = 2]
*/
Select student number, count (course number) as number of elective courses
from score
Group by student number
Having count > = 2;

Query the list of same-sex students with the same name and count the number of students with the same name

/* 
Translation into vernacular, problem analysis:
1) Find out who are the students with the same name and the number of students with the same name
Query results: name, number of people
Condition: how to calculate the same name? The number of people grouped by name is greater than or equal to 2, because the number of people with the same name is greater than or equal to 2
Analysis ideas
Select query result [name, number of people: summary function count (*)
From which table to find data [student table]
Where query condition [no]
Group by group
Having specifies conditions for grouping results [same name: count (*) > = 2]
Order by sorts the query results [none];
*/

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

Check the failed courses and arrange them by course number

/* 
Analysis ideas
Select query result [course number]
From which table to find data [score]
Where query condition [fail: score < 60]
Group by group
Having specifies conditions for grouping results [none]
Order by to sort the query results [course number from large to small: descending desc];
*/
Select course number
from score 
Where score < 60
Order by course number DESC;

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

/* 
Analysis ideas
Select query result [course number, average score: summary function AVG (score)]
From which table to find data [score]
Where query condition [no]
Group by group [each course: group by course number]
Having specifies conditions for grouping results [none]
Order by to sort the query results [ascending order by average score: ASC, descending order by course number: desc] with the same average score;
*/
Select course number, AVG (grade) as average
from score
Group by course number
Order by average ASC, course number DESC;

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

/* 
Analysis ideas
Select query result []
From which table to find data [score]
Where query condition [course number is "04" and score is less than 60]
Group by group
Having specifies the condition for grouping results []
Order by to sort the query results [query results are sorted in descending order by score];
*/
Select student number
from score
Where course number ='04 'and grade < 60
Order by DESC;

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

/* 
Analysis ideas
Select query result [required to output course number and number of electives]
From which table to find data from
Where query condition []
Group by group [each course: group by course number]
Having specifies the conditions for the grouping results [the number of students selected (more than 2 courses are counted): the number of students in each course is more than 2]
Order by sorts the query results [the query results are sorted in descending order according to the number of people; if the number of people 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;

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

/*
Analysis ideas
Break down the topic first
1) Restrictions on [more than two] [failing courses]
2) [student number and average score], that is, the average score of each student, showing the student number and average score
Analysis process:
Step 1: get the average score of each student, display student number and average score
Step 2: add restrictions:
1) Failing course
2) More than two [failed courses]: number of courses > 2


/* 
Step 1: get the average score of each student, display student number and average score
Select query result [student number, average score: summary function AVG (score)]
From which table to find data
Where query condition [no]
Group by [average of each student: group by student number]
Having specifies conditions for grouping results [none]
Order by sorts the query results [none];
*/
Select student number, AVG (grade) as average
from score
Group by student number;


/* 
Step 2: add restrictions:
1) Failing course
2) More than two [failing courses]
Select query result [student number, average score: summary function AVG (score)]
From which table to find data
Where query condition [restriction condition: failed course, average score < 60]
Group by [average of each student: group by student number]
Having specifies conditions for grouping results [restriction conditions: number of courses > 2, summary function count > 2]
Order by sorts the query results [none];
*/
Select student number, AVG (grade) as average
from score
Where score < 60
Group by student number
Having count > = 2;

If you can’t do the above questions, you can review the SQL knowledge involved in this part:

Learning SQL from scratch: summary analysis www.zhihu.com50 classic SQL questions

3. Complex query

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

Sub query of knowledge point

1. Translate into vernacular
1) Query results: student number, name
2) Query criteria: all students with a course score of < 60 need to search from the score sheet and use sub query

Step 1, write a sub query (all students with a course score < 60)
Select query result [student number]
From which table to find data [score table: score]
Where query condition [grade < 60]
Group by group
Having specifies conditions for grouping results [none]
Order by to sort the query results [none]
Limit fetches the specified line [none] from the query result;

Select student number 
from student
Where < 60;

Step 2, query result: student number, name, condition is the student number found in the previous step

Select query result [student number, name]
From which table to find data [student table: student]
Where query condition
Group by group
Having specifies conditions for grouping results [none]
Order by to sort the query results [none]
Limit fetches the specified line [none] from the query result;
*/
Select student number,姓名
from student
Where student number in(
Select student number 
from student
Where < 60
);

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

/*
Find out the student number, condition: did not learn all the courses, that is, the number of elective courses < the total number of courses
In, subquery
*/
Select student number, name
from student
Where student number in(
Select student number 
from score
Group by student number
Having count < (select count from course)
);

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

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

List of students born in 1990

50 classic SQL questions

/*
Look up the list of students born in 1990
The type of the date of birth column in the student table is datetime
*/
Select student number, name 
from student 
Where year = 1990;

Check the records of the top two students in each subject

This kind of problem is actually common: grouping takes the maximum and minimum value of each group, and the maximum n records of each group. I wrote a special article to answer such questions

SQL interview questions: topn questions mp.weixin.qq .com50 classic SQL questions

If you can’t do the above questions, you can review the SQL knowledge involved in this part:

Learning SQL from scratch: complex queries www.zhihu.com50 classic SQL questions

4. Multi table query

50 classic SQL questions

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

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;

Query the students’ course selection: student number, name, course number, course name

Select A. student number, A. name, C. course number, C. course name
From student a inner join score B on a
Inner join course C on B;

Find out the number of pass and fail in each course

--Investigate case expression
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;

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

--Investigate case expression
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;

Query the student number and name of the student whose course number is 0003 and whose course score is above 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;