Common SQL interview questions: classic 50 questions
The following four tables are known:
Student (student ID, name, date of birth, gender) Score (Student ID, course ID, grade)Copy the codeWrite the corresponding SQL statement based on the above information.
Ps: these questions investigate the ability to write SQL, for this type of topic, you need to first put 4 tables between the association relationship clear, the best way is to draw their own sketch paper association diagram, and then write the corresponding SQL statement is relatively easy. Here is a diagram of the four tables I drew, showing how they are related by foreign keys:
Create a database and table
To demonstrate how the topic works, we will create the database and table in the client navicat by following the following statement.
If you don’t know what a database is and what a client navicat is, you can learn this:
1. Create the tables
1) Create student table
Create the student table in navicat client as shown below.
The “Student number” column of the student table is set with primary key constraints. The following figure shows the data types and constraints set for each column
After creating the table, click “Save”
2) Create a score
Do the same to create a “score table”. Set the primary key constraint for the “course number” and “course Number”, and set the “grade” column to the numeric type.
3) Create a course
The “course Number” of the course schedule is set to the primary key constraint
4) Teacher’s Table
The teacher id column of the teacher table is set to the primary key constraint, and the teacher name column is set to null (the red box is not checked), indicating that this column is allowed to contain null values (NULL).
Add data to a table
1) Add data to the student table
SQL to add data
Insert into sc values('0001', '01',' 01', '01',' 01'); Insert into sc values('0002', '0002', '0002', '0002'); Insert into sc values('0003', 'ma', '1991-12-21', 'male '); Insert into sc values('0004', '1990-05-20', 'male ');Copy the codeOperations in the client navicat
2) Score
SQL to add data
Insert into SCORE values('0001', '0001', 80); Insert into SCORE ('0001', '0002', 90) 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);Copy the codeOperations in navicat client
3) Class schedule
SQL to add data
Insert into sc values('0001', '0002', '0002'); Insert into sc ('0002', '0001') VALUES ('0002', '0002', '0001'); Insert into sc values('0003', '0003', '0003');Copy the codeOperations in navicat client
4) Add data to the teacher table
SQL to add data
Insert into teacher('0001', '0001') VALUES ('0001', '0001'); Insert into SC values('0002', 'ma '); Insert into teacher(id, ID) VALUES ('0003', null); Insert into teacher(ID, ID) VALUES ('0004', '');Copy the codeNavicat client operation
Add the results
50 interview questions
In order to facilitate learning, I have sorted 50 interview questions
Select * from student whose surname is “monkey”
Query the number of teachers whose surname is “meng”
Select count(teacher id) from teacher where teacher id like 'meng %';Copy the code2. Summary and statistical group analysis
Interview question: Query the overall grade of course number “0002”
Select * from score where (score = 0002) select * from score where (score = 0002) select * from score where (score = 0002 Course id = '0002';Copy the codeQuery the number of students enrolled in a course
Select count (*) from score; select count (*) from score; select count (*) from score; select count (*) from score; Select count(distinct student id) from score;Copy the codeQuery the highest and lowest score of each subject in the following format: course number, highest score, lowest score
/ * analysis methods select query results [ID: course is course, alias, points: Max (achievement), minimum points: min (grades)] from which finds data table from the result for [score] where the query conditions [no] group by group [two subjects: That is, the grade of each course, should be grouped by course number. */ select score, Max (score) as Max (score),min(score) as min(score) from score group by score;Copy the codeQuery the number of students enrolled in each course
/* select count (*) from count (*) where count (*) = count (*) where count (*) = count (*); */ select count(count) from score group by score;Copy the codeQuery the number of boys and girls
/* select * from (select * from (select * from (select * from (select * from))) Select * from 'student' where 'student' = 'student' where 'student' = 'student' where 'student' = 'student' Having criteria for grouping results [none] Order by sorting query results [none]; */ select count(*) from student group by student group;Copy the code
Select * from student where gpa > 60
Select * from student id where student average = 60; select * from student id where student average = 60; select * from student id where student average = 60; Select avg(score) from avg(score) where avg(score) = avg(score) where avg(score) = avg(score) where avg(score) = avg(score) where AVg (score) = avg(score) where AVg (score) = avg(score) where AVg (score) = avg(score) where AVg (score) = AVg (score) */ select avG from score group by AVG >60; */ select avG from score group by AVG >60;Copy the codeQuery student id of at least two courses
/* select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * from student select * Select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from))))) You need to calculate how many courses each student takes, so you need to use the where clause. Group by course number and count how many courses were taken using the count function. Having specifies criteria for grouping results [at least two courses: */ select * from score group by * having count(* id)>=2; */ select * from score group by * id having count(* ID)>=2;Copy the codeQuery the list of students with the same sex and count the number of students with the same name
Select * from the student where the name of the student is the same as that of the student. Select * from the student where the name is the same as that of the student. Select * from the student where the name is the same as that of the student. 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. Select count(*) from student where count(*) = student where count(*) = student where count(*) = student Count (*)>=2) order by count(*)>=2; */ select count(*) as count from student group by name having count(*)>=2;Copy the codeQuery for courses that failed and rank them by course number
Select * from 'score' where 'score' = 'score' where 'score' = 'score' Score <60] group by group [no] having group by group [no] order by sorting query results [no] order by sorting query results [no]; */ SELECT * from score where score <60 order by */Copy the codeQuery the gpa of each course and the results are sorted by ascending gpa or descending course number if the average is the same
/ * analysis methods select query results [course number, average grade: summary function avg (grades)] from which finds data table from the result for [score] where the query conditions [no] group by group [each course: Order by sorting query results [order by ascending grade: ASC, descending grade: DESC]; */ select AVG (score) from score group by SCORE group order by ASC (score) desc;Copy the codeStudents whose course number is “0004” and score is less than 60 are retrieved in descending order of score
/* select * from 'score' where 'score' = '04' and 'score' = '60' group by group [] select * from 'score' where 'score' = '04' group by group [] Order by sort query results in descending order by score */ SELECT * from score where score ='04' and score <60 ORDER by score desc;Copy the codeCount the number of students enrolled in each course (only for courses with more than 2 students)
The query results are sorted in descending order according to the number of students. If the number of students is the same, the query results are sorted in ascending order according to the number of students
/ * analysis methods select query results [number] output required courses and elective from which finds data table from [] where the query conditions [] group by group [each course: Having group results by course number [Number of students enrolled in courses >2] ORDER by sort query results [Order results by descending number of students, if there are same number of students, order results by ascending number of course number] */ SELECT * from score group having count(* id)>2 order by count(* ID) desc, asC; */ select * FROM score group having count(* ID)>2 order by count(* ID) desc, asC;Copy the codeQuery student numbers and average grades of students who failed more than two courses
/ * analysis methods First decompose title: 1) more than [two] [] failed the course constraints (2) student number and the average [students], that is each student's grade point average, according to student number, average score analysis process: step 1: get each student's grade point average, show student number, the average grades Step 2: plus restrictions: 1) failed the course 2) two or more [] failed to pass the course: course number > 2 / * step 1: get each student's grade point average, according to student number, average grades of the select query results [student number, grade point average: summary functions avg (grades)] from which finds data table from [involves the achievement: Score score] where the query conditions [no] group by group [each student's average: grouped by student id] having specified conditions [no] the order by the result of the group to sort the query results [no]; */ select avG (score) from score group by SCORE; Select * from 'select * from' select * from 'select * from' select * from 'select * from' select * from 'select * from' select * from 'select * from' select * from ' Select * from (select * from (select * from (select * from (select * from)) where (select * from (select * from)) where (select * from (select * from)) where (select * from (select * from)) where (select * from) where (select * from) where (select * from) where (select * from) where (select * from)) Count (course id)>2 order by (count(course id)>2) */ select avG (score) as avG from score where score <60 group by avG (score) having count(score)>=2;Copy the codeIf you can’t do the above questions, you can review the SQL knowledge covered in this part:
3. Complex query
Select id, name from student whose course score is less than 60
Subquery
1. Translate into Plain English
1) Query result: student id, student name 2) Query condition: all students whose course score < 60 need to search from grade table, use subquery
Step 1: Write subquery (all students whose course score < 60)
Select * from ‘select * from’ select * from ‘select * from’ select * from ‘select * from’ Score select * from score where score < 60 group by group by having order by having order by limit select * from score where score < 60 GROUP by having ORDER by having LIMIT select * from score where score < 60 GROUP by having ORDER by having LIMIT select * from score where score < 60
Select * from score where score < 60;Copy the codeThe second step, query results: student number, name, the condition is the first step to check the student number
Select * from student where id = student where id = student where id = student where id = student where id = student where id = student where id = student where id = student Limit Select row from query result [none];
Select count (*) from student where count (select count (*) from score where score < 60);Copy the codeSelect id, name from student where not enrolled in all courses
/* Select * from student id; The student did not take all the courses, i.e. the number of courses the student took. */ select * from student where id in(select id from score group by ID having count(select count(id)) < (select count(id)) from course) );Copy the codeSelect student numbers and names from all students who only took two courses
Select * from student where (select * from score group by * having count(*)=2); select * from student where (select * from score group by * having count(*)=2);Copy the codeA list of students born in 1990
Select * from student where year(year)=1990; select * from student where year(year)=1990;Copy the codeQuery the records of the top two students in each subject
In fact, this kind of problem is common: group the maximum value of each group, the minimum value, each group of the largest N (top N) records.
SQL interview questions: topN questions
In my work, I often encounter business problems like:
How do you find out which products users like best in each category? What are the top 5 items that users click on in each category? In fact, this kind of problem is common: group the maximum value of each group, the minimum value, each group of the largest N (top N) records.Copy the codeFacing this kind of problem, how to solve?
Let’s use the example of the score sheet to give the answer.
The result sheet is the student’s result, which contains the student number (student’s student number), course number (the course number of the elective course) and result (the result of the elective course).
Select the maximum value for each group
Case: Group the data in the row with the maximum score according to the course number
We can use group by and aggregate functions to get a value (maximum, minimum, average, etc.) for each group. But you can’t get the data for the row with the maximum score.
Select Max (score) from score group by Max (score);Copy the code
We can use associative subqueries to do this:
Select * from score as a where score = (select Max (score) from score as b where b Course id = a. Course ID);Copy the code
In the query result “0001”, there are 2 rows of data, because there are 2 rows of maximum score 80
Group the minimum value of each group Case: Group the data in the row where the minimum value of the score is according to the course number
The same is done using associative subqueries
Select * from score as a where score = (select min(score) from score as b where b Course id = a. Course ID);Copy the code
Maximum N records per group
Case: Query the records of the top two students in each subject
Step 1, find out which groups there are
We can group by course number to find out which groups there are, and which course number corresponds to this question
Select Max (score) from score group by Max (score);Copy the code
Step 2: First use the order by clause to sort by grade (desc), then use the limt clause to return topN (the top two grades returned by this question).
Select * from score WHERE score = '0001' ORDER BY score desc LIMIT 1;Copy the codeSimilarly, you can write other groups (other course numbers) and fetch the top 2 SQL
In step 3, use union All to merge the selected data from each group
Select * from score WHERE '0001' ORDER by desc limit 2) union all (select * from score where '0001' order by desc limit 2 Union all (select * from score WHERE score = '0003' ORDER by score desc LIMIT 1);Copy the code
Previously we used the order by clause to sort a column by descending order (DESC) to get the maximum number of N records per group. To achieve a minimum of N records per group, sort the ORDER BY clause in ascending order (ASC).
The topN problem can also be solved using custom variables, which will be covered later.
If you are not familiar with the multi-table merge, you can see the “multi-table query” in “Learning SQL from Zero”.
conclusion
Grouping takes each group maximum value, minimum value, each group maximum N (top N) records.
4. Multi-table query
Query all student id, name, number of courses selected, total score
Select a, b,count(b),sum(b) From student as a left join score as B on A B. Group by a.Copy the codeQuery student id, name, and gpa of all students whose gpa is greater than 85
Select * from student as a left join score as b on a select * from student as a left join score as b on a Student id = B. Student id group by A. Student ID having avg(b. Grade) > 85;Copy the codeQuery student’s course id, name, course id, course name
Select a, B, C,c from student a inner join score b on a. B. Inner join course C on B Course id =c. Course ID;Copy the codeFind the number of students who passed and failed in each course
Select course number, sum(case when score >=60 THEN 1 else 0 end) as 数 据, Sum (case when score < 60 then 1 else 0 end) as from score group by course id;Copy the codeUse segments [100-85],[85-70],[70-60] and [<60] to count the scores of each subject, including the number of students in each score section, course number and course name
Select SUM (case when grades 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 A. Group b. Group B.Copy the codeQuery course number is 0003 and curriculum result in more than 80 points in the student’s name and student id |
Select A from student as a inner join score as b on a. Student id =b. Student ID where b. course id ='0003' and b > 80;Copy the codeBelow is the score table (table name: student NUMBER, course number, grade).
The SQL implementation is used to row the table into the following table structure
【 summary 】 This kind of topic belongs to the ranks and ranks of how to exchange, solve the problem idea is as follows:
【 interview questions 】 The following is the score of the students.
The SQL implementation is used to row the table into the following table structure
“Answer”
Step 1 prints the structure of the target table using constant columns
You can see that the query results are very close to the target table
Select student id,' 0001',' 0002',' 0003' from score;Copy the code
Step 2, use a case expression to replace the grade corresponding to the constant column
Select STUDENT id, (case student ID when '0001' THEN ELSE 0 end) as '0001', (CASE course id when '0002' THEN ELSE 0 end) as '0002' As '0003' from score (case course id when '0003' THEN ELSE 0 end)Copy the code
In this query result, each row represents a student’s grade in a particular course. For example, the first row is’ Student 0001′ elective ‘course 00001′, while the other two columns’ Course 0002′ and’ course 0003′ are 0.
The result of each student taking a particular course is shown in each box below. We can take out the grades of each course by grouping.
In the third level, group by group, and use the maximum function Max to take out the maximum value in each square above
Select STUDENT id, Max (case student ID when '0001' THEN else 0 end) as '0001', Max (case course id when '0002' THEN else 0 end) as '0002', Max (case course id when '0003' THEN else 0 end) as '0003' from Score group by course ID;Copy the codeSo we have the target table (row and column interchange)
