SQL interview 50 questions – learning record (I)

Time:2022-1-9

These 50 questions are classic SQL questions on the Internet. First try to write them yourself, and then compare them with the teacher’s writing method. In this process, I will paste up my mistakes and the teacher’s writing method and my writing method. Of course, there may be some places where the consideration is not rigorous. I hope you can make more corrections. In addition, I am a product maker with non-technical background, and this series of articles is the first time to learn manuscripts. If I make relatively low-level mistakes, spray gently ~ ha ha

I will send out this series of articles in five parts, each with 10 questions.

The following is a table creation statement:


CREATE TABLE `Student`(

`s_id` VARCHAR(20),

`s_name` VARCHAR(20) NOT NULL DEFAULT ”,

`s_birth` VARCHAR(20) NOT NULL DEFAULT ”,

`s_sex` VARCHAR(10) NOT NULL DEFAULT ”,

PRIMARY KEY(`s_id`)

);

CREATE TABLE `Course`(

`c_id` VARCHAR(20),

`c_name` VARCHAR(20) NOT NULL DEFAULT ”,

`t_id` VARCHAR(20) NOT NULL,

PRIMARY KEY(`c_id`)

);
CREATE TABLE `Teacher`(

`t_id` VARCHAR(20),

`t_name` VARCHAR(20) NOT NULL DEFAULT ”,

PRIMARY KEY(`t_id`)

);
CREATE TABLE `Score`(

`s_id` VARCHAR(20),

`c_id` VARCHAR(20),

`s_score` INT(3),

PRIMARY KEY(`s_id`,`c_id`)

);

The following is the statement to insert data:

#Insert student table data

Insert into student values ('01 ',' Zhao Lei ',' 1990-01-01 ',' male ');

Insert into student values ('02 ',' money and electricity ',' 1990-12-21 ',' male ');

Insert into student values ('03 ',' sun Feng ',' 1990-05-20 ',' male ');

Insert into student values ('04 ',' Li Yun ',' 1990-08-06 ',' male ');

Insert into student values ('05 ',' Zhou Mei ',' 1991-12-01 ',' female ');

Insert into student values ('06 ',' Wu Lan ',' 1992-03-01 ',' female ');

Insert into student values ('07 ',' Zheng Zhu ',' 1989-07-01 ',' female ');

Insert into student values ('08 ',' Wang Ju ',' 1990-01-20 ',' female ');
#Insert course table data

Insert into course values ('01 ',' Chinese ',' 02 ');

Insert into course values ('02 ',' Math ',' 01 ');

Insert into course values ('03 ',' English ',' 03 ');
#Insert teacher table data

Insert into teacher values ('01 ',' Zhang San ');

Insert into teacher values ('02 ','li Si');

Insert into teacher values ('03 ',' Wang Wu ');
#Insert score table data

INSERT INTO Score VALUES(’01’ , ’01’ , 80);

INSERT INTO Score VALUES(’01’ , ’02’ , 90);

INSERT INTO Score VALUES(’01’ , ’03’ , 99);

INSERT INTO Score VALUES(’02’ , ’01’ , 70);

INSERT INTO Score VALUES(’02’ , ’02’ , 60);

INSERT INTO Score VALUES(’02’ , ’03’ , 80);

INSERT INTO Score VALUES(’03’ , ’01’ , 80);

INSERT INTO Score VALUES(’03’ , ’02’ , 80);

INSERT INTO Score VALUES(’03’ , ’03’ , 80);

INSERT INTO Score VALUES(’04’ , ’01’ , 50);

INSERT INTO Score VALUES(’04’ , ’02’ , 30);

INSERT INTO Score VALUES(’04’ , ’03’ , 20);

INSERT INTO Score VALUES(’05’ , ’01’ , 76);

INSERT INTO Score VALUES(’05’ , ’02’ , 87);

INSERT INTO Score VALUES(’06’ , ’01’ , 31);

INSERT INTO Score VALUES(’06’ , ’03’ , 34);

INSERT INTO Score VALUES(’07’ , ’02’ , 89);

INSERT INTO Score VALUES(’07’ , ’03’ , 98);

Here are the learning and sharing of questions 1-10:

Question 1: query the student numbers of all students whose grades of the course with the course number “01” are higher than those of the course with the course number “02”

The first way to write

SELECT a.s_ ID student number, C.S_ Name student name, A.S_ Score 01 course grade, B.S_ Score 02 course grade from

(SELECT s_id,c_id,s_score FROM score WHERE c_id = ’01’) AS a

JOIN

(SELECT s_id,c_id,s_score FROM score WHERE c_id = ’02’) AS b

JOIN Student c

ON a.s_id = b.s_id AND b.s_id=c.s_id

WHERE a.s_score > b.s_score;

The second way to write

SELECT a.s_ ID student number, C.S_ Name student name, A.S_ Score 01 course grade, B.S_ Score 02 course grade from

(SELECT s_id,c_id,s_score FROM score WHERE c_id = ’01’) AS a

JOIN

(SELECT s_id,c_id,s_score FROM score WHERE c_id = ’02’) AS b

ON a.s_id = b.s_id

JOIN Student c ON a.s_id=c.s_id

WHERE a.s_score > b.s_score;

Question 2: query the student number and average score of students with an average score greater than 60

SELECT s_ ID student number, AVG (s_score) average score

FROM score

GROUP BY s_id

HAVING AVG(s_score)>60

ORDER BY AVG(s_score);

Question 3: check the student number, name, number of courses selected and total score of all students

SELECT a.s_ ID student number, B.S_ Name, A. number of courses selected, A. total score from

(select s_id, count (*) number of courses selected, sum (s_score) total score from score

GROUP BY s_id ) AS a JOIN student b ON a.s_id = b.s_id;

/*There are several problems with the above writing method (I wrote it for the first time)

1. Internal connection is used. If some students do not choose courses, they will count fewer students, so external connection is required

2. If a student does not choose a course, the total score may display a null value

3. The writing is a little complicated. Maybe you can connect the score with the student table first and then do the related query, instead of querying first and then connecting

*/

Teacher’s writing

SELECT b.s_ ID student number, B.S_ Name, count (a.c_id), number of courses selected, ifnull (sum (a.s_score), 0) total score from

score a RIGHT JOIN student b ON a.`s_id` = b.`s_id`

GROUP BY b.s_id

/*

In the process of writing the above SQL, the following problems occur

SELECT b.s_ ID student number, B.S_ Name, count (*) number of courses selected, ifnull (sum (a.s_score), 0) total score from

score a RIGHT JOIN student b ON a.s_id = b.s_id

GROUP BY b.s_id

Count (a.c_id) is written as count (*). At this time, the number of courses selected by Wang Ju will be 1, because the null line is counted

(during right connection, Wang Ju’s s s_score and c_id are null, which means that there is such a row of data, so count (*) will be counted.)

Therefore, when writing such SQL, it is better to aggregate according to the fields to be queried.

2. According to s from the table_ The ID group is aggregated, and the s of the main table is queried_ Name field, so B.S should be used here_ ID aggregation

SELECT b.s_ ID student number, B.S_ Name, count (a.c_id), number of courses selected, ifnull (sum (a.s_score), 0) total score from

score a RIGHT JOIN student b ON a.s_id = b.s_id

GROUP BY a.s_id

*/

Question 4: query the number of teachers surnamed monkey

SELECT COUNT(*) FROM teacher

WHERE t_ Name like 'monkey%';

Add: sometimes the keyword distinct is needed to exclude duplicate values in statistics

SELECT COUNT(DISTINCT t_name)

FROM teacher

WHERE t_ Name like 'Zhang%';

/*

The wrong point in this question is that% is written as * in the use of like, and any character in regular expression is written with “*”,

In mysql, like is a wildcard with% and_ Represents a single character

SELECT COUNT(*) FROM teacher

WHERE t_ Name like ‘monkey *’;

*/

Question 5: query the student number and name of students who have not studied teacher Zhang San’s class (I haven’t got it right for a long time)

The following is the SQL written by myself. It is a little confusing and complex. It has been written for a long time, and it was written after watching the video

SELECT s_ ID student number, s_ Name name from student

WHERE s_name NOT IN

(SELECT s_name FROM

(SELECT a.`t_id`,a.`c_id` FROM

course a LEFT JOIN teacher b ON a.`t_id`=b.`t_id`

WHERE b.`t_ Name ` = "Zhang San"

)c

LEFT JOIN (SELECT score.`c_id`, student.`s_name` FROM score LEFT JOIN student ON score.`s_id`=student.`s_id`) d

ON c.c_id = d.`c_id` )

The following is the SQL written by the teacher

SELECT s_ ID student number, s_ Name name from student

WHERE s_id NOT IN

(

SELECT s_id FROM score AS s

INNER JOIN course AS c ON s.c_id = c.c_id

INNER JOIN teacher AS t ON c.t_id = t.t_id

WHERE t.t_ Name = "Zhang San")

The above code uses not in instead of where C_ id != “02” because this just excludes the line of 02. Think again

Question 6: query the student number and name of the students who have learned all the lessons taught by Mr. Zhang San

SELECT stu. s_ ID student number, stu s_ Name name from teacher t

LEFT JOIN course c ON t.t_id = c.t_id

LEFT JOIN score s ON c.c_id = s.c_id

LEFT JOIN student stu ON s.`s_id` = stu.`s_id`

WHERE t.t_ Name = "Zhang San"

Question 7: query the student number and name stu of students who have studied the course numbered “01” and also studied the course numbered “02” s_ id,stu. s_ name

SELECT stu.s_id,stu.s_name FROM student stu

LEFT JOIN score s ON stu.s_id = s.s_id

WHERE s.c_id = ’01’ OR s.c_id = ’02’

GROUP BY stu. s_ name,stu. s_ Putting multiple fields after ID # group by means of grouping multiple fields as a whole

HAVING COUNT(*)= 2;

/*I wrote the above myself, but it took a long time. The errors are:

  1. Only one field is used for group by, but two fields are queried in the select. This is a syntax error.

Add both fields after all.

SELECT stu.s_id,stu.s_name FROM student stu

LEFT JOIN score s ON stu.s_id = s.s_id

WHERE s.c_id = ’01’ OR s.c_id = ’02’

GROUP BY stu.s_name

HAVING COUNT(*)= 2

2. The where condition is written as where S.C_ id = ’01’ and s.c_ The reason for the error id = ’02’ is:

The data in the same row and the same field cannot have two values (that is, two cells). This method does not report an error, but the query result is a null value

SELECT stu.s_id,stu.s_name FROM student stu

LEFT JOIN score s ON stu.s_id = s.s_id

WHERE s.c_id = ’01’ AND s.c_id = ’02’

GROUP BY stu.s_id,stu.s_name

HAVING COUNT(*)= 2

Question 7 how the teacher writes

SELECT s_id,s_name FROM student

WHERE s_id IN

(

SELECT a.s_id FROM

(SELECT s_id FROM score WHERE c_id=’01’)AS a

INNER JOIN

(SELECT s_id FROM score WHERE c_id=’02’)AS b

ON a.s_id=b.s_id

);

Question 8: query the total score of course No. “02”

SELECT SUM(s_score) FROM score

WHERE c_id = “02”;

Question 9: query the student number and name of all students whose course scores are less than 60

SELECT stu.s_id,stu.s_name

LEFT FROM student stu JOIN score s

ON stu.`s_id` = s.`s_id`

WHERE s.`s_score` < 60

GROUP BY stu.s_id,stu.s_name

HAVING COUNT(*)= 3;

/*There are problems in their own practice as above: not all students have studied three courses,

Therefore, count (*) = 3 is wrong, which means that each student has studied 3 courses by default.

*/

According to the bullet screen prompt, modify as follows

SELECT stu.s_id,stu.s_name

FROM student stu LEFT JOIN score s

ON stu.`s_id` = s.`s_id`

GROUP BY stu.s_id

HAVING MAX(s.`s_score`)< 60;

/*Note that Wang Ju did not attend the course in the later stage, so there is a null value,

However, she is ignored in Max operation. Maybe ifnull can be used here. The optimization is as follows

SELECT stu.s_id,stu.s_name

FROM student stu LEFT JOIN score s

ON stu.`s_id` = s.`s_id`

GROUP BY stu.s_id

HAVING ifnull(MAX(s.`s_score`),0)< 60;

In fact, I have another problem. If the SQL statement above is based on stu s_ If name comes to groupby, it will report an error,

But according to stu s_ ID will not report an error,

Of course, according to stu s_ id ,stu. s_ Name will not report an error.

The reason for this is because s_ Is this ID the primary key?

*/

Question 10: query the student number and name of students who have not learned all courses

SELECT stu.s_id,stu.s_name

From student stu left join score s # note that you need to use the left join. If you use the join directly, you will miss Wang Ju

ON stu.`s_id` = s.`s_id`

GROUP BY stu.s_id

HAVING COUNT(DISTINCT c_id)< 3;# Note that distinct is used to exclude cases where there are multiple results in a course

Strictly speaking, if you haven’t finished all the lessons, you shouldn’t write a 3 directly, but it is summarized by course

So the SQL can be changed to:

SELECT stu.s_id,stu.s_name

FROM student stu LEFT JOIN score s

ON stu.`s_id` = s.`s_id`

GROUP BY stu.s_id

HAVING COUNT(DISTINCT c_id)< (select count(*) from course);