The 50 questions of SQL are widely circulated on the Internet. They are a classic and comprehensive set of questions. Recently, they have just joined the data development engineer, and SQL needs to be strengthened. Therefore, naturally, they can't miss this set of questions. Each question is written by themselves. If they can't, they have seen the analysis of other big guys, and then write it again. Maybe the answers to some questions will be the same, You are welcome to point out any mistakes or areas that can be improved.
The title comes from: 50 SQL exercises, answers and detailed analysis - brief book
The questions that won't usually use search experience to find inspiration. There is no detailed record. It mainly draws lessons from the big man's video and speaks in great detail. [data analysis] - 50 SQL interview questions - play strange and upgrade with me and become a data scientist together_ Beep beep beep_ bilibili
Data sheet introduction
The data required in the question comes from four tables: Student - grade - course - teacher. The fields and association diagrams of the four tables are listed here
Creating tables and inserting data
Student form
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); insert into Student values('01' , 'Lei Zhao' , '1990-01-01' , 'male'); insert into Student values('02' , 'Qian Dian' , '1990-12-21' , 'male'); insert into Student values('03' , 'Sun Feng' , '1990-12-20' , 'male'); insert into Student values('04' , 'Li Yun' , '1990-12-06' , 'male'); insert into Student values('05' , 'Zhou Mei' , '1991-12-01' , 'female'); insert into Student values('06' , 'Wu Lan' , '1992-01-01' , 'female'); insert into Student values('07' , 'Zheng Zhu' , '1989-01-01' , 'female'); insert into Student values('09' , 'Zhang San' , '2017-12-20' , 'female'); insert into Student values('10' , 'Li Si' , '2017-12-25' , 'female'); insert into Student values('11' , 'Li Si' , '2012-06-06' , 'female'); insert into Student values('12' , 'Zhao Liu' , '2013-06-13' , 'female'); insert into Student values('13' , 'Sun Qi' , '2014-06-01' , 'female');
Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)); insert into Course values('01' , 'chinese' , '02'); insert into Course values('02' , 'mathematics' , '01'); insert into Course values('03' , 'English' , '03');
Teacher table
create table Teacher(TId varchar(10),Tname varchar(10)); insert into Teacher values('01' , 'Zhang San'); insert into Teacher values('02' , 'Li Si'); insert into Teacher values('03' , 'Wang Wu');
Transcript SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1)); insert into SC values('01' , '01' , 80); insert into SC values('01' , '02' , 90); insert into SC values('01' , '03' , 99); insert into SC values('02' , '01' , 70); insert into SC values('02' , '02' , 60); insert into SC values('02' , '03' , 80); insert into SC values('03' , '01' , 80); insert into SC values('03' , '02' , 80); insert into SC values('03' , '03' , 80); insert into SC values('04' , '01' , 50); insert into SC values('04' , '02' , 30); insert into SC values('04' , '03' , 20); insert into SC values('05' , '01' , 76); insert into SC values('05' , '02' , 87); insert into SC values('06' , '01' , 31); insert into SC values('06' , '03' , 34); insert into SC values('07' , '02' , 89); insert into SC values('07' , '03' , 98);
Exercise topic
1. Query the information and course scores of students with higher scores in "01" than "02"
1.1 query the existence of "01" course and "02" course at the same time
1.2 query if there is a "01" course but there may be no "02" course (null if it does not exist)
1.3 query if there is no "01" course but there is "02" course
2. Query the student number, student name and average score of students whose average score is greater than or equal to 60
3. Query the information of students with scores in SC table
4. Query the student number, student name, total number of courses selected and total score of all courses of all students (null if there is no score)
4.1 check the information of students with grades
5. Inquire about the number of teachers surnamed "Li"
6. Query the information of students who have learned "Zhang San"
7. Query the information of students who have not studied all courses
8. Query the information of at least one course that is the same as the student with student number "01"
9. Query the information of other students whose courses are exactly the same as those of "01"
10. Query the names of students who have not studied any course taught by "Zhang San"
11. Check the student number, name and average score of students who fail two or more courses
12. Retrieve the student information of "01" course whose score is less than 60 in descending order
13. Display the scores and average scores of all courses of all students from high to low
14. Query the highest score, lowest score and average score of each subject:
It is displayed in the following form: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate and excellent rate
Pass is > = 60, medium is 70-80, excellent is 80-90, excellent is > = 90
It is required to output the course number and the number of electives. The query results are arranged in descending order by the number of people. If the number of people is the same, they are arranged in ascending order by the course number
15. Sort according to the scores of each subject and display the ranking. If the Score is repeated, the ranking vacancy will be reserved
15.1 rank according to the scores of each subject and display the ranking. If the Score is repeated, the ranking will be merged
16. Query the total score of students and rank them. If the total score is repeated, the ranking vacancy will be reserved
16.1 query the total score of students and rank them. If the total score is repeated, the ranking vacancy will not be reserved
17. Count the number of students in each score segment of each subject: course number, course name, [100-85], [85-70], [70-60], [60-0] and percentage
18. Query the records of the top three scores of each section
19. Query the number of students selected for each course
20. Find out the student number and name of students who only take two courses
21. Query the number of boys and girls
22. Query the student information with the word "wind" in the name
23. Query the list of same-sex students with the same name and count the number of students with the same name
24. Check the list of students born in 1990
25. Query the average score of each course, and the results are arranged in descending order according to the average score. If the average score is the same, it is arranged in ascending order according to the course number
26. Query the student number, name and average score of all students whose average score is greater than or equal to 85
27. Query the names and scores of students whose course name is "Mathematics" and whose score is less than 60
28. Query the courses and scores of all students (there are cases where students have no grades and have not selected courses)
29. Query the name, course name and score of any course with a score of more than 70
30. Inquire about failed courses
31. Query the student number and name of students with course number 01 and course score above 80
32. Number of students per course
33. The results are not repeated. Query the information and results of the students with the highest scores among the students taking the courses taught by teacher Zhang San
34. In case of repeated scores, query the information of the students with the highest scores and their scores among the students taking the courses taught by teacher Zhang San performance
35. Query the student number, course number and student score of students with the same scores in different courses
36. Query the top two with the best scores in each skill
37. Count the number of students taking elective courses in each course (only courses with more than 5 people are counted).
38. Search student numbers of students taking at least two courses
39. Query the information of students who have taken all courses
40. Query the age of each student, calculated by year only
41. According to the date of birth, if the current month day is less than the month day of the year of birth, the age will be reduced by one
42. Inquire about the students whose birthday is this week
43. Inquire about the students whose birthday is next week
44. Inquire about the students whose birthday is this month
45. Check the students whose birthday is next month
answer
1. Query the information and course scores of students with higher scores in "01" than "02"
First select two tables for the grades of courses 01 and 02 respectively, then connect the two tables together and write greater than with where
select t1.SId, t1.CId, t1.score, t2.CId as CId_2, t2.score as score_2 from (select * from SC where CId = '01') as t1 inner join (select * from SC where CId = '02') as t2 on t1.SId = t2.SId where t1.score > t2.score
1.1 query the existence of "01" course and "02" course at the same time
As long as courses 01 and 02 are selected at the same time, the relevant information of all courses selected by the student will be output
Since the result returned by the subquery is more than one row, an error will be reported if any is not added
select student.SId, Sname, CId, score from student left join SC on student.SId = SC.SId where student.SId = any(select SC.SId from SC where CId in ('01', '02'))
1.2 query if there is a "01" course but there may be no "02" course (null if it does not exist)
select * from (select * from SC where SC.CId='01')as t1 left join (select * from SC where SC.CId='02')as t2 on t1.SId=t2.SId
1.3 query if there is no "01" course but there is "02" course
Find out all the conditions of 02 courses and screen out those with 01 courses
select * from SC where CId = '02' and SId not in(select SId from SC where SC.CId = '01')
2. Query the student number, student name and average score of students whose average score is greater than or equal to 60
select student.SId, Sname, avg(score) from student left join SC on student.SId = SC.SId group by SId having avg(score) >= 60
3. Query the information of students with scores in SC table
select distinct student.* from student, SC where student.SId = SC.SId
4. Query the student number, student name, total number of courses selected and total score of all courses of all students (null if there is no score)
select student.SId, Sname, COUNT(CId), SUM(score) from student, SC where student.SId = SC.SId group by SId
#When it is necessary to display students who have not selected courses, it is necessary to use join
select t1.SId, Sname, CourseSum, ScoreSum from (select student.SId, Sname from student )t1 left join (select SC.SId, COUNT(SC.CId) as CourseSum, SUM(SC.score) as ScoreSum from SC group by SC.SId)t2 on t1.SId = t2.SId
4.1 check the information of students with grades
select distinct student.* from student, SC where student.SId = SC.SId AND SC.score is not NULL
5. Inquire about the number of teachers surnamed "Li"
Wildcards should be matched with like. I always write=
select * from teacher where Tname like 'Lee%'
6. Query the information of students who have learned "Zhang San"
select student.* from student left join SC on student.SId = SC.SId where CId = (select TId from teacher where Tname = 'Zhang San')
7. Query the information of students who have not studied all courses
Calculate the number of all courses and the number of courses selected by each student, and filter out the student IDS with unequal number of courses.
select * from student where student.SId not in (select sc.SId from sc group by sc.SId having count(sc.CId)= (select count(CId) from course))
8. Query the information of at least one course that is the same as the student with student number "01"
Find out the courses selected by students with student No. 01, and use in to query the qualified courses in the course selection of all students
select distinct student.* from student left join sc on student.SId = sc.SId where CId in (select CId from sc where SId = '01') and student.SId != '01'
9. Query the information of other students whose courses are exactly the same as those of "01"
The two-tier query ensures that the selected students (all the courses selected by 01 students + the same number of courses selected)
It will not happen that the selected courses include 01 students' courses, but other additional courses are selected, or only a part of 01 students' courses are selected, which can ensure that the selected courses are exactly the same.
select * from Student where sid in (select SId from SC # Query students with the same number of selected courses as 01 students where SId != '01' group by SId having count(CId) = (select count(CId) from SC where SId = '01') # Remove those who have selected 01 students and have not selected courses in the selected courses and SId not in (select distinct SId from sc where CId not in (select CId from sc where SId = '01')) )
10. Query the names of students who have not studied any course taught by "Zhang San"*
Connect the three tables, find out which students chose Mr. Zhang San's class, and then rule it out,
select student.Sname from student where SId not in (select s.SId from student s left join sc on s.SId = sc.SId left join teacher t on sc.CId = t.TId where Tname = 'Zhang San')
11. Check the student number, name and average score of students who fail two or more courses
select student.SId, Sname, avg(score) from student left join sc on student.SId = sc.SId where score < 60 group by SId having count(*) > 1
12. Retrieve the student information of "01" course whose score is less than 60 in descending order
select s.* from student s left join sc on s.SId = sc.SId where CId = '01' and score < 60 order by score DESC
13. Display the scores and average scores of all courses of all students from high to low
Direct select will only display one course record for each student, so join with the sc table again
select * from sc left join(select SId, avg(sc.score) as AvgScore from sc group by SId) t on sc.SId = t.SId order by AvgScore DESC
14. Query the highest score, lowest score and 1 average score of each section
It is displayed in the following form: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate and excellent rate
Pass is > = 60, medium is 70-80, excellent is 80-90, excellent is > = 90
It is required to output the course number and the number of electives. The query results are arranged in descending order by the number of people. If the number of people is the same, they are arranged in ascending order by the course number
select sc.CId ,max(sc.score)as Highest score, min(sc.score)as Lowest score, AVG(sc.score)as average, count(*)as Number of electives, sum(case when sc.score>=60 then 1 else 0 end )/count(*)as pass rate, sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as Medium rate, sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as Excellent rate, sum(case when sc.score>=90 then 1 else 0 end )/count(*)as Excellent rate from sc GROUP BY sc.CId ORDER BY count(*)DESC, sc.CId ASC
15. Sort according to the scores of each subject and display the ranking. If the Score is repeated, the ranking vacancy will be reserved
15.1 rank according to the scores of each subject and display the ranking. If the Score is repeated, the ranking will be merged
16. Query the total score of students and rank them. If the total score is repeated, the ranking vacancy will be reserved
16.1 query the total score of students and rank them. If the total score is repeated, the ranking vacancy will not be reserved
I haven't come up with a good solution for the time being. After reading it on the Internet, I don't feel very satisfied with the answer. I'm a little busy recently, so I'll make it up when I think of it.
17. Count the number of students in each score segment of each subject: course number, course name, [100-85], [85-70], [70-60], [60-0] and percentage
select c.CId, c.Cname, sum(case when sc.score <= 100 and sc.score > 85 then 1 else 0 end) as '[100-85]', sum(case when sc.score <= 85 and sc.score > 70 then 1 else 0 end) as '[85-70]', sum(case when sc.score <= 70 and sc.score > 60 then 1 else 0 end) as '[70-60]', sum(case when sc.score <= 60 and sc.score > 0 then 1 else 0 end) as '[60-0]' from sc left join course c on sc.CId = c.CId group by c.CId
18. Query the records of the top three scores of each section
Seeing this problem, I think of a window function analysis I've seen before. The example is very similar, that is, sorting within the group after grouping.
Easy to understand: SQL window function - know
<Window function> over (partition by <Column name for grouping> order by <Column name for sorting>)
In the first layer, select creates a separate rk column to represent the ranking in the course
The second layer select s and queries all relevant information (SId, CId, score, rk)
select * from(select *, rank() over (partition by CId order by score) as rk from sc) r where rk <= 3
19. Query the number of students selected for each course
select CId, count(SId) as SIdnum from SC group by CId
20. Find out the student number and name of students who only take two courses
select student.SId, Sname, count(CId) as CIdnum from student left join sc on student.SId = sc.SId group by student.SId having CIdnum = 2
21. Query the number of boys and girls
select Ssex, count(*) from student group by Ssex
22. Query the student information with the word "wind" in the name
select * from student where Sname like '%wind%'
23. Query the list of same-sex students with the same name and count the number of students with the same name
select Sname, count(SId) as num from student group by Sname having num > 1
24. Check the list of students born in 1990
select * from student where year(Sage) = 1990
25. Query the average score of each course, and the results are arranged in descending order according to the average score. If the average score is the same, it is arranged in ascending order according to the course number
When sorting by multiple standards, connect them directly with commas and write them first.
select CID, avg(score) as AvgScore from sc group by CId order by AvgScore DESC, CId ASC
26. Query the student number, name and average score of all students whose average score is greater than or equal to 85
select s.SId, s.Sname, avg(score) as AvgScore from student s left join sc on s.SId = sc.SId group by s.SId having AvgScore >= 85
27. Query the names and scores of students whose course name is "Mathematics" and whose score is less than 60
select s.Sname, score from student s left join sc on s.SId = sc.SId left join course on sc.CId = course.CId where Cname = 'mathematics' and score < 60
28. Query the courses and scores of all students (there are cases where students have no grades and have not selected courses)
select * from student s left join sc on s.SId = sc.SId left join course on sc.CId = course.CId
29. Query the name, course name and score of any course with a score of more than 70
select Sname, Cname, score from student s left join sc on s.SId = sc.SId left join course on sc.CId = course.CId where score > 70
30. Inquire about failed courses
select * from sc where score < 60
31. Query the student number and name of students with course number 01 and course score above 80
There are no qualified students in the original data, so it is normal to find that they are empty, but there are two equal to 80.
select s.SId, Sname, score from student s left join sc on s.SId = sc.SId where CId = '01' and score > 80
32. Number of students per course
select CId, count(SId) as StudentNum from sc group by CId
33. The results are not repeated. Query the information and results of the students with the highest scores among the students taking the courses taught by teacher Zhang San
If there are two same highest scores, using limit1 can also ensure that there is only one person.
select s.* from student s left join sc on s.SId = sc.SId left join course c on sc.CId = c.CId left join teacher t on c.TId = t.TId where Tname = 'Zhang San' order by score DESC limit 1
34. In case of repeated scores, query the information of the students with the highest scores and their scores among the students taking the courses taught by teacher Zhang San performance
Calculate the highest score from the class taught by Mr. Zhang San, and then select the students with the highest score from the people who chose the class.
The three paragraphs of left join were repeated once. After thinking for a long time, I didn't come up with a solution. I read some on the Internet, and almost everything written by others is like this. Write it like this for the time being.
select s.* from student s left join sc on s.SId = sc.SId left join course c on sc.CId = c.CId left join teacher t on c.TId = t.TId where Tname = 'Zhang San' and score in (select max(score) from student s left join sc on s.SId = sc.SId left join course c on sc.CId = c.CId left join teacher t on c.TId = t.TId where Tname = 'Zhang San' )
35. Query the student number, course number and student score of students with the same scores in different courses
Rename a table to create a new table, which can be directly used for comparison.
select distinct a.* from sc a left join sc b on a.CId != b.CId where a.score = b.score
36. Check the top two with the best grades in each subject
Create the same table sc1, "count(*)" to calculate the number of times that "a score in the same course is smaller than other scores"
< 2 means that only one or zero times, that is, the second or first place, can meet the question conditions.
In the where condition, the score (sc1. Score < sc.score) that meets the above conditions in the sc1 table is written, because the query range has been limited to sc1 in the from. If you select sc. *, an error will be reported.
select * from sc sc1 where (select count(*) from sc where sc1.CId = sc.CId and sc1.score < sc.score )<2 order by CId, score DESC
37. Count the number of students taking elective courses in each course (only courses with more than 5 people are counted)
select CId, count(SId) as num from sc group by CId having num > 5
38. Search student numbers of students taking at least two courses
select SId from student where SId in (select SId from sc group by SId having count(*) > 1)
39. Query the information of students who have taken all courses
After grouping student numbers, calculate how many lines each student number has (how many courses have been selected) and whether it is equal to the total number of courses
select * from student where SId in (select SId from sc group by SId having count(*) = (select count(*) from course))
40. Query the age of each student, calculated by year only
select *, year(now())-year(sage) as Age from Student
41. According to the date of birth, if the current month day is less than the month day of the year of birth, the age will be reduced by one
select Sname, floor(datediff(CURRENT_DATE, Sage)/365) as age from student
42. Inquire about the students whose birthday is this week
select *, week(Sage), week(now()) from Student where week(Sage) = week(now());
43. Inquire about the students whose birthday is next week
+ 1 on the basis of 42 questions
select *, week(Sage), week(now()) from Student where week(Sage) = week(now()) + 1;
44. Inquire about the students whose birthday is this month
select *, month(Sage), month(now()) from Student where month(Sage) = month(now());
45. Check the students whose birthday is next month
select *, month(Sage), month(now()) from Student where month(Sage) = month(now()) + 1;