MySQL Interview common topics 2

Definition of Sutdent table
Field Name fields description data type primary key foreign key non-null unique self-increment
Id number INT (10) Yes Yes Yes
SName name VARCHAR (20) No no No
Sex Sex VARCHAR (4) No no no No
Birth year of Birth No no no no no
Department Department VARCHAR (20) No no No
Address Home Address VARCHAR (50) No no no No

Definition of score Table
Field Name fields description data type primary key foreign key non-null unique self-increment
ID number INT (10) Yes Yes Yes
stu_id INT (10) No no no No
C_name Course name VARCHAR (20) No no no No
Grade score INT (10) No no no no no

1. Create Student and score tables
CREATE TABLE Student (
ID INT (Ten) PRIMARY KEY auto_increment,
Sname VARCHAR () not NULL,
Sex VARCHAR (4),
Birth year,
Department VARCHAR (20),
Address VARCHAR (50)
);
Create a score table. The SQL code is as follows:
CREATE TABLE Score (
ID INT (Ten) PRIMARY KEY auto_increment,
stu_id INT (Ten) is not NULL,
C_name VARCHAR (20),
Grade INT (10)
);
2. Adding records for student and score tables
Insert statement that inserts a record into the student table is as follows:
INSERT into student VALUES (901, ' Zhang boss ', ' Male ', 1995, ' Computer Department ', ' Haidian District, Beijing ');
INSERT into student VALUES (null, ' Zhang Dick ', ' Male ', 1996, ' Chinese department ', ' Beijing Changping District ');
INSERT into student VALUES (null, ' Zhang San ', ' Female ', 2000, ' Chinese department ', ' Hunan province Yongzhou ');
INSERT into student VALUES (null, ' John Doe ', ' Male ', 2000, ' English Department ', ' Liaoning province Fuxin ');
INSERT into student VALUES (null, ' Harry ', ' Female ', 2001, ' English Department ', ' Xiamen, Fujian Province ');
INSERT into student VALUES (null, ' King VI ', ' Male ', 1998, ' Computer Department ', ' Hunan province Hengyang ');
Insert statement that inserts a record into the score table is as follows:
INSERT into score VALUES (null,901, ' computer ', 98);
INSERT into score VALUES (default,901, ' English ', 80);
INSERT into score VALUES (null,902, ' computer ', 65);
INSERT into score VALUES (null,902, ' Chinese ', 88);
INSERT into score VALUES (null,903, ' Chinese ', 95);
INSERT into score VALUES (null,904, ' computer ', 70);
INSERT into score VALUES (null,904, ' English ', 92);
INSERT into score VALUES (null,905, ' English ', 94);
INSERT into score VALUES (null,906, ' computer ', 90);
INSERT into score VALUES (null,906, ' English ', 85);

3. Querying all records of the student table
SELECT * from student;
4. Query the records of 2nd to 4 of the student table
SELECT * FROM Student LIMIT 1, 3;
5. Check the information of all students (ID), name (sname) and faculty (department) from the student table
SELECT id,sname,department from student;
6. Check the information from the student table for students in the Department of Computer Science and English
SELECT * FROM student WHERE Department = ' Computer Department ' OR Department = ' English Department ';
7. Check the age 18~21岁 student information from the student table (if this year is 2017)
2017-birth
SELECT Id,sname,sex, (2017-birth) age,department,address from Student WHERE (2017-birth) between and 21;
SELECT Id,sname,sex, (2017-birth) age,department,address from Student WHERE (2017-birth) >=18 and (2017-birth) <=21 ;
8. From the student table, find out how many people are in each faculty
SELECT department, COUNT (1) from student GROUP by department;
9. Query the highest score for each account from the score table
SELECT C_name,max (grade) ' highest score ' from score GROUP by C_name;
10. Query John Doe exam subjects (C_NAME) and exam results (grade)
SELECT A.sname,b.c_name,b.grade from student a INNER joins score B on a.id = b.stu_id WHERE a.sname = ' John Doe ';
11. Access all students ' information and exam information in a connected way
SELECT A., B.From student a INNER joins score B on a.id = b.stu_id;
12. Calculate the total of each student
SELECT b.stu_id,a.sname,sum (b.grade) ' Total ' from student a INNER joins score B on a.id = b.stu_id GROUP by b.stu_id;
13. Calculate the average score for each test subject
SELECT C_name, round (avg (grade), 1) ' average score ' from score GROUP by C_name;
14. Check the student information of computer score below 95
SELECT a.* from student a INNER joins score B on a.id = b.stu_id WHERE B.grade < and b.c_name = ' computer ';
15. Check the information of students who participate in both computer and English exams
Multi-table Connection
SELECT a.* from student a INNER joins score B on a.id = b.stu_id WHERE b.c_name = ' computer ' OR b.c_name = ' English ' GROUP by b.stu_i D having count (b.c_name) = 2;
SELECT a.* from student a INNER joins score B on a.id = b.stu_id INNER joins score c on a.id = c.stu_id WHERE b.c_name = ' calculation Machine ' and c.c_name = ' English ';
Sub-query
SELECT * from student where ID in (select stu_id from score where c_name = ' computer ' and stu_id in (select stu_id from score WH ERE c_name = ' English ');
16. Sort computer test scores from highest to lowest
SELECT A., B.c_name,b.grade from student a INNER joins score B on a.id = b.stu_id WHERE b.c_name = ' computer ' ORDER by B.grade DESC;
SELECTFrom score WHERE c_name = ' computer ' ORDER by grade DESC;
17. Query the student's number from the student table and the score table, then merge the query results
SELECT ID from Student
UNION
SELECT stu_id from score;
18. Check the name of the student surnamed Zhang or Wang surname, department and examination subjects and results
SELECT A.sname,a.department,b.c_name,b.grade from Student a INNER JOIN score b in a.id = b.stu_id WHERE a.sname like ' sheet% ' or a.sname like ' King% ';
19. Enquiries are the names, ages, faculties and examinations of students in Hunan province and their scores
SELECT A.sname, (2017-birth) Age,a.department,b.c_name,b.grade from student a INNER joins score B on a.id = b.stu_id WHERE A . Address like '% Hunan ';

MySQL Interview common topics 2