Database Learning 5: Introduction to Database Learning 41-50 Questions (based on MySQL)

SQL interview must have 50 questions 123

41. Query the student number, course number, and student achievement (key) of the same students in different courses.

Classify by number and achievement first:

SELECT s_id,s_score
FROM Score
GROUP BY s_id,s_score

The results are as follows. The table shows that there are several grades for different courses of each school number, for example, 80 points for Chinese Mathematics and English, then only one 80 point will be shown under the school number. If 60 points for Chinese Mathematics and 70 points for English, then one 60 and one 70 points will be shown under the school number.

Next, we will select more than one student with the same number and name for each course.

SELECT s_id FROM 
(
SELECT a.s_id,a.s_score
FROM Score AS a
INNER JOIN
(
SELECT s_id FROM Score
GROUP BY s_id HAVING COUNT(DISTINCT c_id) > 1 # Student number with more than one course selected
) AS b ON a.s_id = b.s_id
GROUP BY a.s_id,a.s_score
) AS c
GROUP BY s_id HAVING COUNT(s_id) > 1

42 Questions Repeated

43. Count the number of students taking each course (more than 5 courses are counted).Require output of course number and number of electives, query results in descending order by number of people, if the number is the same, in ascending order by course number (not important)

SELECT a.c_id,COUNT(DISTINCT a.s_id)'Number of Course Selections' ,b.c_name'Course Name'
FROM Score AS a
INNER JOIN Course AS b ON a.c_id = b.c_id
GROUP BY c_id HAVING COUNT(DISTINCT a.s_id) > 5
ORDER BY Number of Course Selections DESC,c_id ASC

44. Retrieve student numbers for at least two courses (not important)

SELECT s_id,COUNT(DISTINCT c_id)'Number of Course Selections'
FROM Score 
GROUP BY s_id HAVING COUNT(DISTINCT c_id) >= 2

45. Query the information of the students who have taken all the courses (highlighted in red)

SELECT s_id,COUNT(DISTINCT c_id)'Number of Course Selections' FROM Score
GROUP BY s_id HAVING COUNT(DISTINCT c_id) = (SELECT COUNT(DISTINCT c_id) FROM Course)

46. Query the age of each student (precise to month)

SELECT s_id,s_birth,FLOOR(DATEDIFF(CURDATE(),s_birth)/365)'Age' FROM Student

FLOOR function: rounding down
ROUND function: Rounding

47. Query the names of students who have not learned any of the courses taught by Teacher Zhang San (or, if you write it yourself, none of the answers)

SELECT s_id'School Number',s_name FROM Student
WHERE s_id NOT IN
(
	SELECT a.s_id
	FROM Student AS a
	INNER JOIN Score AS b ON a.s_id = b.s_id
	INNER JOIN Course AS c ON b.c_id = c.c_id
	INNER JOIN Teacher AS d ON c.t_id = d.t_id
	WHERE d.t_name = 'Zhang San'
)

Easy to make mistakes: Choose the number t_name!='Zhang San'. The error lies in, for example, if student 1 chooses the classes of Zhang San and Li Si, two messages will be placed on the table, one is the result of student 1 choosing the three classes, the other is the result of student 1 choosing the four classes of Li, when t_name is used!=When'Zhang San', the information about the results of Lesson 1 students in Lesson 4 of Li will also be selected. If you choose a number, Lesson 1 will also be considered as a student who has not selected Lesson 3 of Zhang.

48. Query students who have birthdays next week

Wrong practices

SELECT WEEK('20200101',0) -- 0
SELECT WEEK('20200101',1) -- 1

Query students who meet the criteria "week (birth date, 1)=week (present date, 1)+1"

SELECT s_id,s_name,s_birth FROM Student 
WHERE WEEK(s_birth,1) = WEEK(CURDATE(),1) + 1

However, there is a problem that the week in which the birthday is born may differ from the week in which this year's birthday is born, which makes the condition "week (birth date, 1)=week (present date, 1)+1" unavailable.

Correct practices
We can put each person's birthday in that year, and then compare, for example,'1990-07-01'replaced by'2020-07-01', and whether the week('2020-07-01',1) and week('current date', 1) have the same number of weeks.Since no one will have a birthday next week on the current date, consider having a birthday next week on'2020-06-27'.
The replacement year is accomplished by substring and concat functions.

SUBSTRING (string, a,b): take B characters from the a-bit of the string
CONCAT (string 1, string 2,...: Concatenate multiple strings together

SELECT * FROM Student
WHERE WEEK(CONCAT('2020-',SUBSTRING(s_birth,6,5)),1) = WEEK('2020-06-27',1) + 1

49. Query students who have birthdays this month (week, date(now()) cannot be used)

SELECT * FROM Student
WHERE MONTH(s_birth) = MONTH(CURDATE())

50. Query students who have birthdays next month (week, date(now()) cannot be used)

SELECT * FROM Student
WHERE MONTH(s_birth) = MONTH(CURDATE()) + 1

In the case of December, the following are corrections:

SELECT * FROM Student WHERE
CASE WHEN MONTH(CURDATE()) = 12 
	 THEN 
	   	 MONTH(s_birth) = 1 
	 ELSE 
		 MONTH(s_birth) = MONTH(CURDATE()) + 1 
	 END
  1. Video Column on Station B - up Owner: Lu Xiaoliang - [Data Analysis] - SQL 50 Questions Interview - Join me to wonder about upgrading to become a data scientist ↩︎

  2. Know-Monkey-Common SQL Interview Questions: Classic 50 Questions https://zhuanlan.zhihu.com/p/38354000 ↩︎

  3. Know - Little Tomatoes - SQL interview will have 50 questions https://zhuanlan.zhihu.com/p/43289968 ↩︎

Published 7 original articles, won 1. Visits 551
Private letter follow

Tags: SQL

Posted on Sun, 08 Mar 2020 22:20:57 -0400 by Zoud