Did some MySQL exercises feel enough to cope with the interview

1. Create student and score tables

CREATE TABLE Student (

ID INT (Ten) is not NULL UNIQUE PRIMARY KEY,

Name VARCHAR () is 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) is not NULL UNIQUE 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 Lu ', ' male ', 1985, ' computer Department ', ' Beijing Haidian District ');

Insert into student values ( 902, ' dick ',  ' male ", 1986, ' Beijing Changping District ");

Insert into student values ( 903, ' Zhang San ',  ' female ", 1990, ' Hunan Yongzhou ");

Insert into student values ( 904, ' John Doe ',  ' male ", 1990, ' Liaoning Province Fuxin ");

Insert into student values ( 905, ' Harry ',  ' female ", 1991, ' Xiamen, Fujian province ");

INSERT into student VALUES (906, ' wangliuqi ', ' male ', 1988, ' 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 (null,901, ' English ', +);

INSERT into score VALUES (null,902, ' computer ', +);

INSERT into score VALUES (null,902, ' Chinese ', n);

INSERT into score VALUES (null,903, ' Chinese ', up);

INSERT into score VALUES (null,904, ' computer ', +);

INSERT into score VALUES (null,904, ' English ', ();

INSERT into score VALUES (null,905, ' English ', 94);

INSERT into score VALUES (null,906, ' computer ', +);

INSERT into score VALUES (null,906, ' English ', ");

Here is the answer to the question of the problem feeling or a harvest

3. Querying all records of the student table
SELECT * FROM Student
4. Query the 2nd to 4 records of the student table
SELECT * FROM student limit 1,3
5. Check the information of all students (ID), name and faculties (department) from the student table
Select ID, name, 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 in (' Computer department ', ' English Department ')
Check the age 18~22岁 student information from the student table
SELECT * FROM student where 2017-birth between and 30
Find out how many people are in each faculty from the student table
Select Department,count (1) from student GROUP by department
Query the highest score for each account from the score table
Select C_name,max (grade) from score GROUP by C_name
Query John Doe exam subjects (C_NAME) and exam results (grade)

Select T1.c_name,t1.grade from score T1 left joins student t2 on t1.stu_id = t2.id where t2.name = ' John Doe '
Select T1.c_name, t1.grade from score t1 where t1.stu_id = (select id from student where name= ' John Doe ')
Select T1.c_name, t1.grade from score t1,student t2 where t2.name = ' John Doe ' and t1.stu_id = T2.id

Access information and exam information for all students in a connected way
Select Student.id,name,sex,birth,department,address,c_name,grade from score,student where score.stu_id = Student.id
Calculate the total of each student
Select T2.name,sum (t1.grade) from score t1,student t2 WHERE t1.stu_id = t2.id GROUP by t2.name
13. Calculate the average score for each test subject
Select C_name, AVG (grade) from score GROUP by C_name
14. Check the student information of computer score below 95
Select T1.name,t1.sex,t1.birth,t1.department,t1.address,t2.grade,t2.c_name from student T1, score T2 where T2.c_name= ' Computer ' and t2.grade<95 and t1.id = t2.stu_id
15. Check the information of students who participate in both computer and English exams
Select t1.name,t1.sex,t1.birth,t1.department,t1.address from student t1,score T2,score T3
where t1.id = t2.stu_id
and t2.c_name = ' computer '
and t1.id = t3.stu_id
and T3.c_name= ' English '
SELECT * FROM Student
WHERE ID =any
(select stu_id from score where stu_id on (select stu_id from score where c_name= ' computer ') and C_name= ' English ')

16. Sort computer test scores from highest to lowest
SELECT * FROM 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 T1.name,t1.sex,t1.birth,t1.department,t1.address,t2.grade,t2.c_name from student T1, score T2 where

(T1.name like ' Zhang% ' or t1.name like ' King% ')
and t1.id = t2.stu_id

19. Enquiries are the names, ages, faculties and examinations of students in Hunan province and their scores
Select T1.name,t1.sex,t1.birth,t1.department,t1.address,t2.grade,t2.c_name from student T1, score T2 where
T1.address like ' Hunan% '
and t1.id = t2.stu_id

The efficiency of merging queries is much more efficient than sub-queries.

Did some MySQL exercises feel enough to cope with the interview