SQL classic 50 question exercise

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

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

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