MySQL exercises two

Create a table

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 ', ");

Second: Query training

--Query the 2nd to 4 records of the student table
SELECT * FROM Student LIMIT 1, 3;

--From the student table for information on all students ' student numbers (IDs), names and faculties (department)
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 ');

--7. Check the age 20~30岁 student information from the student table
SELECT * FROM student WHERE year (now ())-birth between and 30;

--8. From the student table, find out how many people are in each faculty
SELECT Department,count (department) as ' number ' from student GROUP by department;

--9. Query the highest score for each account from the score table
SELECT C_name,max (grade) as ' highest score ' from score GROUP by C_name;

--10. Query John Doe exam subjects (C_NAME) and exam results (grade)
SELECT * FROM score where stu_id= (SELECT ID from student where name= ' John Doe ');

--11. Access all students ' information and exam information in a connected way
SELECT * from Score,student WHERE stu_id=student.id;

--12. Calculate the total of each student
SELECT Stu_id,name,sum (grade) as ' total scores ' from Score,student WHERE student.id=stu_id GROUP by stu_id;
--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 * FROM student WHERE ID in
(select stu_id from score WHERE grade<95 and c_name= ' computer ');

--15. Check the information of students who participate in both computer and English exams
SELECT * FROM student WHERE ID in
(Select a.stu_id from (SELECT * from score WHERE c_name= ' computer ') A,
(SELECT * from score where c_name= ' English ') b where a.stu_id=b.stu_id);

--16. Sort computer test scores from highest to lowest
SELECT C_name,grade from score WHERE c_name= ' computer ' ORDER by grade;

--17. Query the student's number from the student table and the score table, then merge the query results
SELECT DISTINCT student.id from student JOIN score WHERE stu_id=student.id;

--18. Check the name of the student surnamed Zhang or Wang surname, department and examination subjects and results
SELECT Name,department,c_name,grade from Score,student WHERE (the name like ' sheet% ' OR name like ' King% ') and student.id=stu_id;

SELECT Name,department,c_name,grade from Score,
(SELECT * FROM student WHERE name like ' Zhang% ' OR name like ' King% ') a
WHERE stu_id=a.id;
--19. Enquiries are the names, ages, faculties, subjects and scores of students in Hunan province.
SELECT Name,year (now ())-birth as ' age ', Department,c_name,grade
From score, (SELECT * to student where address like ' Hunan% ') a where a.id=stu_id;

MySQL exercises two