– one Prepare data

Four tables are known as follows:
Student form: student (student number, student name, date of birth, gender)
Transcript: score (student number, course number, grade)
Course schedule: Course (course number, course name, teacher number)
Teacher form: teacher (teacher number, teacher name)
Write the corresponding SQL statement according to the above information and the following requirements.

--auto_increment   Attribute self increment

drop database if exists school;
create database school charset utf8;
use school;
create table if not exists student(
    id int primary key, 
    name varchar(50),
    birthday date,
    gender char(1)
);
	create table if not exists teacher(
    id int primary key,
    name varchar(50)
);
	 create table if not exists course(
    id int primary key,
    name varchar(50),
    t_id int,
    foreign key(t_id) references teacher(id)
);
create table if not exists score(
    s_id int,
    c_id int,
    score int,
			primary key(s_id,c_id),
    foreign key(s_id) references student(id),
    foreign key(c_id) references course(id)
);

Prepare data
sql for adding data
student table

insert into student 
values('0001' , 'monkey' , '1989-01-01' , 'male');

insert into student
values('0002' , 'monkey' , '1990-12-21' , 'female');

insert into student
values('0003' , 'Jack Ma' , '1991-12-21' , 'male');

insert into student
values('0004' , 'Sephirex Wang' , '1990-05-20' , 'male');

teacher table

insert into teacher
values('0001' , 'Menzaza');

insert into teacher
values('0002' , 'pony ');

insert into teacher 
values('0003' , null);

insert into teacher
values('0004' , '');

course table

insert into course
values('0001' , 'language' , '0002');

insert into course
values('0002' , 'mathematics' , '0001');

insert into course
values('0003' , 'English' , '0003');

score table

insert into score
values('0001' , '0001' , 80);

insert into score
values('0001' , '0002' , 90);

insert into score
values('0001' , '0003' , 99);

insert into score
values('0002' , '0002' , 60);

insert into score
values('0002' , '0003' , 80);

insert into score
values('0003' , '0001' , 80);

insert into score
values('0003' , '0002' , 80);

insert into score
values('0003' , '0003' , 80);

– 2.50 interview questions
– 1. Simple query

- (1) inquire about students surnamed 'monkey'

select *
from student
where name like 'monkey%';

- (2) query the student whose last name is "monkey"

select *
from student
where name like '%monkey';

- (3) query students with 'monkey' in their names

select *
from student
where name like '%monkey%';

- (4) query the number of teachers surnamed 'Meng'

select * from teacher where name like 'Meng%';

–2. Summary analysis

- (5) query the total score of the course number '0002'

select sum(score) '2 Total score of'
from score
where score = '2';

- (6) query the number of students who have selected the course

select count(distinct s_id)
from score;

–3. grouping

- (7) check the highest and lowest scores in each subject

select c_id , max(score), min(score)
from score
group by c_id;

- (8) query the number of students selected for each course

select c_id, count(s_id)
from score
group by c_id;

- (9) query the number of boys and girls

select gender , count(id)
from student
group by gender;

–3. Conditions for grouping results

- (10) query the student number and average score of students with an average score greater than 60

select s_id , avg(score)
from score
group by s_id
having avg(score)>60;

- (11) query the student number of students taking at least two courses

select s_id
from score
group by s_id
having count(distinct c_id)>=2;

- (12) query the list of students with the same name and surname and count the number of students with the same name

select name , count(*)
from student
group by name;

- (13) check the failed courses and rank them from the largest to the smallest

select s_id, c_id , score
from score
where score<60
order by c_id desc;

- (14) query the average score of each course, and the results are sorted by the average score in ascending order. If the average scores are the same, they are sorted by the course number in descending order

select c_id ,avg(score)
from score
group by c_id
order by avg(score) asc, c_id desc;

– (15) search student numbers with course number "0004" and score less than 60, and the results are arranged in descending order of score

select s_id , score
from score
where c_id = 4 and score <60
order by score desc;

- (16) count the number of students in each course (only those with more than 2 students)

select c_id , count(s_id)
from score
group by c_id
having count(s_id)>2;

– (17) it is required to output the course number and the number of electives. The query results are sorted by the number of people in descending order. If the number of people is the same, they are sorted by the course number in ascending order

select c_id , count(s_id)
from score
group by c_id
order by count(s_id) desc, c_id asc;

- (18) check the student number and average score of students who have failed more than two courses

select s_id , avg(score)
from score
where score<60
group by s_id
having count(c_id) > 2;

– (19) query students' assembly records and rank them

select s_id , sum(score)
from score
group by s_id
order by sum(score);

– query the student number and average score of students with an average score greater than 60

select s_id , avg(score)
from score
having avg(score)>60

–4. Complex query

- (20) query the student numbers and names of all students whose course scores are less than 60

select id , name
from student
where id in (
select s_id
from score
where score < 60);

- (21) query the student number and name of students who did not learn all the courses

select id ,name
from student
where id in (
select id 
form score
group by 
having count(c_id)<(select count(id) from course));

- (22) find out the student numbers and names of all students who have only taken two courses

select id , name
from student
where id in (select s_id from score group by s_id having count(c_id)=2);

- (23) list of students born in 1990

select name 
from student
where year(birthday) = 1990;

- (24) check the records of the top two scores of each subject

select  top 2 * 
from score
order by score desc;

- (25) query the age of each student (accurate to month)

select name , timestampdiff(month,birthday,now())
from student;

- (26) inquire about the students whose birthday is this month

select name 
from student
where month(birthday)=month(now());

–5. multi-table query

- (27) check the student number, name, number of courses selected and total score of all students

- (28) query the student number, name and average score of all students with an average score greater than 85

- (29) query the course selection of students: student number, name, course number and course name

- (30) find out the number of pass and fail in each course

- (31) use sub sections [100-85], [85-70], [70-60], [< 60] to count the scores of each subject, including the number of people in each score section, course number and course name

- (32) query the student number and name of students with course number 0003 and course score above 80

–5. Multi table connection

– (33) retrieve the student information in descending order of scores for "0001" courses with scores less than 60

- (34) query the average scores of different courses taught by different teachers from high to low

- (35) query the name, course name and score of any course with a score of more than 70 (similar to the above question)

- (36) check the student number, name and average score of students who failed two or more courses

- (37) query the student number, course number and student score of students with the same scores in different courses

- (38) query the student numbers of all students whose grades in the course with the course number "0001" are higher than those in the course with the course number "0002"

– (39) query the student number and name of students who have studied the course numbered "0001" and also studied the course numbered "0002"

- (40) query the student number and name of the students who have learned all the lessons taught by "mengzaza"

- (41) 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)

- (42) query the student number and name of students who have not studied the "monzaza" teacher's class (similar to the previous question)

- (43) query the name of the student with the highest score among the students taking the course taught by "monzaza" and its score (similar to the above question, rank with score and get the highest one with limit 1)

- (44) query the student number and name of students whose at least one course is the same as that of students with student number "0001"

– (45) displays the scores and average scores of all students in all courses from high to low

–6. Window function

- (46) query students' average grades and their ranking
–now_ The number () over function adds a column to the ranking

– (47) sort by the scores of each subject and display the ranking

- (48) check the names of the top two students with the best scores in each subject

- (49) query the information of the second to third students in all courses and the course grades (similar to the previous question)

- (50) query the records of the top three scores of each subject (regardless of the juxtaposition of scores) (similar to the previous question)