Handwritten sql interview questions

1, Data sheet

2, Simple query

1. Check the list of students with "monkey" in their names

3, Summary analysis

1. Query the number of students who have chosen the course

2. Query the highest and lowest scores of each subject and display them in the following form: course number, highest score and lowest score

3. Query the student number of students taking at least two courses

/* 
Translated into Vernacular:
Step 1: you need to calculate the course data of each student's elective course, which needs to be grouped by student number
 Step 2: take at least two courses: that is, the number of elective courses for each student > = 2, and specify the conditions for the grouping results

Analysis ideas
select Query result [student number, number of elective courses per student: summary function count]
from From which table do you find data [student number of the course: curriculum score]
where Query criteria [take at least two courses: you need to calculate how many courses each student has taken, and you need to use grouping, so there is no where clause here]
group by Grouping [number of elective courses for each student: group according to the course number, and then calculate the number of elective courses with the summary function count]
having Specify conditions for grouping results [at least two elective courses: number of elective courses per student > = 2]
*/
select Student number, count(Course number) as Number of elective courses
from score
group by Student number
having count(Course number)>=2;

4. Query the average score of each course, and the results are sorted in ascending order according to the average score. If the average score is the same, it is sorted in descending order according to the course number

/* 
Analysis ideas
select Query result [Course No., average grade: summary function AVG (grade)]
from From which table to find data [score]
where Query criteria [none]
group by Grouping [each course: grouped by course number]
having Specify criteria for grouping results [none]
order by Sort the query results [sort by average score in ascending order: asc, if the average score is the same, sort by course number in descending order: desc];
*/
select Course number, avg(achievement) as Average score
from score
group by Course number
order by Average score asc,Course number desc;

5. Check the student number and average score of students who have failed more than two courses

/*
Analysis ideas
 First break down the topic:
1)[Restrictions on more than two [failed courses]
2)[Student number and average score], that is, the average score of each student, showing the student number and average score
 Analysis process:
Step 1: get the average score of each student, display the student number and average score
 Step 2: add restrictions:
1)Failed course
2)More than two [failed courses]: number of courses > 2


/* 
Step 1: get the average score of each student, display the student number and average score
select Query result [student number, average score: summary function AVG (score)]
from From which table do you find data [related to grade: grade table score]
where Query criteria [none]
group by Grouping [average per student: grouped by student number]
having Specify criteria for grouping results [none]
order by Sort query results [none];
*/
select Student number, avg(achievement) as Average score
from score
group by Student number;


/* 
Step 2: add restrictions:
1)Failed course
2)More than two [failed courses]
select Query result [student number, average score: summary function AVG (score)]
from From which table do you find data [related to grade: grade table score]
where Query conditions [restrictions: failed courses, average score < 60]
group by Grouping [average per student: grouped by student number]
having Specify conditions for grouping results [limiting conditions: number of courses > 2, summary function count > 2]
order by Sort query results [none];
*/
select Student number, avg(achievement) as Average score
from score
where achievement <60
group by Student number
having count(Course number)>2;

4, Complex query

1. Query the student number and name of all students whose course scores are less than 60

[Knowledge point] sub query

1.Translate into vernacular
1)Query result: student number, name
2)Query criteria: all course scores < 60 Students need to find it from the grade table and use sub query

Step 1: write sub query (all course grades) < 60 (students)
select Query results[Student number]
from Which table to find data from[Transcript: score]
where query criteria[achievement < 60]
group by grouping[No,]
having Specify criteria for grouping results[No,]
order by Sort query results[No,]
limit Fetches the specified row from the query result[No,];

select Student number 
from student
where achievement < 60;

Step 2: query result: student number, name, condition is the student number found in the previous step

select Query results[Student number,full name]
from Which table to find data from[Student list:student]
where query criteria[Use operator in]
group by grouping[No,]
having Specify criteria for grouping results[No,]
order by Sort query results[No,]
limit Fetches the specified row from the query result[No,];
*/
select Student number,full name
from student
where  Student number in (
select Student number 
from student
where achievement < 60
);

5, Multi table query

1. Query the student number, name and average score of all students whose average score is greater than 85

select a.Student number,a.full name, avg(b.achievement) as Average score
from student as a left join score as b
on a.Student number = b.Student number
group by a.Student number
having avg(b.achievement)>85;

2. Find out the number of pass and fail of each course

-- investigate case expression
select Course number,
sum(case when achievement>=60 then 1 
	 else 0 
    end) as Number of qualified persons,
sum(case when achievement <  60 then 1 
	 else 0 
    end) as Number of failed
from score
group by Course number;

3. Query the student number and name of students whose course number is 0003 and whose course score is more than 80

select a.Student number,a.full name
from student  as a inner join score as b on a.Student number=b.Student number
where b.Course number='0003' and b.achievement>80;

4. Query the average score of different courses taught by different teachers from high to low

select a.Teacher number,a.Teacher name,avg(c.achievement) 
from  teacher as a 
inner join course as b 
​​on a.Teacher number= b.Teacher number
inner join score  c on b.Course number= c.Course number
group by a.Teacher name
order by avg(c.achievement) desc;

5. Query the student numbers of all students whose grades of the course with the course number "0001" are higher than those of the course with the course number "0002"

select a.Student number  
​from 
(select Student number ,achievement from score where Course number=01) as a
inner join 
(select Student number ,achievement from score where Course number=02) as b
on a.Student number =b.Student number 
inner join student c on c.Student number =a.Student number 
where a.achievement >b.achievement ;

6. Query the student number and name of the students who have learned all the lessons taught by the "mengzaza" teacher

select s.Student number ,s.full name,a.Student number ,b.Course number,c.Teacher number ,c.Teacher name
from student as s  
inner join score as a  
​​on s.Student number =a.Student number 
inner join  course  b on a.Course number =b.Course number
inner join  teacher c  on b.Teacher number = c.Teacher number
where c.Teacher name ='Menzaza';

7. Query the names of students who have not learned any course taught by "monzaza" (similar to the above question, "not learned" is realized by not in)

select full name ,Student number 
from student 
​where Student number not in (
select a.Student number 
from student as a 
inner join score as​ b
on a.Student number =b.Student number 
inner join course as​ c on b.Course number =c.Course number 
inner join teacher as​ d on c.Teacher number =d.Teacher number 
where d.Teacher name ='Menzaza');

8. Query the names and grades of the students with the highest scores among the students taking the courses taught by "monzaza"

select a.full name,b.achievement 
from student as​ a 
inner join score as​ b on a.Student number=b.Student number
inner join course as​ c on b.Course number =c.Course number 
inner join teacher as​ d on c.Teacher number = d.Teacher number 
where d.Teacher name = 'Menzaza'
order by b.achievement desc limit 1;

9. Display the scores and average scores of all courses of all students from high to low according to the average score

select a.Student number,avg(a.achievement ),
max(case when b.Course name  = 'mathematics' then a.achievement else null end ) as 'mathematics',
max(case when b.Course name  = 'language' then a.achievement else null end ) as 'language',
max(case when b.Course name  = 'English' then a.achievement else null end ) as 'English'
from score as​ a
inner join course as b 
​​on a.Course number =b.Course number 
group by a.Student number ;

6, Window function

1. Query the average score and ranking of students

select Student number ,avg(achievement),
row_number () over( order by avg(achievement) desc)
from score
group by Student number  ;

2. Query the names of the top two students with the best scores in each subject

select a.Course number ,b.full name ,a.achievement,a.ranking​ from (
select Course number ,Student number ,achievement ,
row_number () over(partition by Course number order by achievement desc) as ranking​
from  score) as a 
inner join student b on a.Student number =b.Student number 
where a.ranking​ <3 ;