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
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
SELECT s_id,COUNT(DISTINCT c_id)'Number of Course Selections' FROM Score GROUP BY s_id HAVING COUNT(DISTINCT c_id) >= 2
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)
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.
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.
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
SELECT * FROM Student WHERE MONTH(s_birth) = MONTH(CURDATE())
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
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 ↩︎