MySQL Database for practice testing

1. Create Student and score tables

CREATE  TABLE  student (id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  ,name  VARCHAR(20)  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(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,stu_id  INT(10)  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,‘张老大‘, ‘男‘,1985,‘计算机系‘, ‘北京市海淀区‘);INSERT INTO student VALUES( 902,‘张老二‘, ‘男‘,1986,‘中文系‘, ‘北京市昌平区‘);INSERT INTO student VALUES( 903,‘张三‘, ‘女‘,1990,‘中文系‘, ‘湖南省永州市‘);INSERT INTO student VALUES( 904,‘李四‘, ‘男‘,1990,‘英语系‘, ‘辽宁省阜新市‘);INSERT INTO student VALUES( 905,‘王五‘, ‘女‘,1991,‘英语系‘, ‘福建省厦门市‘);INSERT INTO student VALUES( 906,‘王六‘, ‘男‘,1988,‘计算机系‘, ‘湖南省衡阳市‘);

Insert statement that inserts a record into the score table is as follows:

INSERT INTO score VALUES(NULL,901, ‘计算机‘,98);INSERT INTO score VALUES(NULL,901, ‘英语‘, 80);INSERT INTO score VALUES(NULL,902, ‘计算机‘,65);INSERT INTO score VALUES(NULL,902, ‘中文‘,88);INSERT INTO score VALUES(NULL,903, ‘中文‘,95);INSERT INTO score VALUES(NULL,904, ‘计算机‘,70);INSERT INTO score VALUES(NULL,904, ‘英语‘,92);INSERT INTO score VALUES(NULL,905, ‘英语‘,94);INSERT INTO score VALUES(NULL,906, ‘计算机‘,90);INSERT INTO score VALUES(NULL,906, ‘英语‘,85);

3. Querying all records of the student table

mysql> SELECT * FROM student;+-----+--------+------+-------+------------+--------------+| id  | name   | sex  | birth | department | address      |+-----+--------+------+-------+------------+--------------+| 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 || 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 || 903 | 张三   | 女   |  1990 | 中文系     | 湖南省永州市 || 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 || 905 | 王五   | 女   |  1991 | 英语系     | 福建省厦门市 || 906 | 王六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |+-----+--------+------+-------+------------+--------------+

4. Query the 2nd to 4 records of the student table

mysql> SELECT * FROM student LIMIT 1,3;+-----+--------+------+-------+------------+--------------+| id  | name   | sex  | birth | department | address      |+-----+--------+------+-------+------------+--------------+| 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 || 903 | 张三   | 女   |  1990 | 中文系     | 湖南省永州市 || 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |+-----+--------+------+-------+------------+--------------+

5. Check the information of all students (ID), name and faculties (department) from the student table

mysql> SELECT id,name,department FROM student;+-----+--------+------------+| id  | name   | department |+-----+--------+------------+| 901 | 张老大 | 计算机系   || 902 | 张老二 | 中文系     || 903 | 张三   | 中文系     || 904 | 李四   | 英语系     || 905 | 王五   | 英语系     || 906 | 王六   | 计算机系   |+-----+--------+------------+

6. Check the information from the student table for students in the Department of Computer Science and English

  mysql> SELECT * FROM Student WHERE department in (' Computer department ', ' English Department '), +-----+--------+------+-------+--------- ---+--------------+| ID | name | sex | Birth | Department | Address |+-----+--------+------+-------+------------+--------------+| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing | | 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin | | 905 | Harry |  Women | 1991 | English Department | Xiamen, Fujian | | 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |+-----+--------+------+-------+------------+--------------+  

7. Query the student information for age 18~22岁 from the student table
mysql> SELECT Id,name,sex,2013-birth as Age,department,address
-from Student
, WHERE 2013-birth between;
+-----+------+------+------+------------+--------------+
| id | name | sex | age | department | address |
+-----+------+------+------+------------+--------------+
| 905 | harry | female | 22 | English Department-Xiamen, Fujian |
+-----+------+------+------+------------+--------------+
Mysql> SELECT Id,name,sex,2013-birth as age, Department,address
, from student
, WHERE 2013-birth>=18 and 2013-birth<=22;
+-----+------+------+------+------------+--------------+
| id | name | sex | age | department | address |
+-----+------+------+------+------------+--------------+
| 905 | harry | female | 22 | English Department-Xiamen, Fujian |
+-----+------+------+------+------------+--------------+

8. From the student table, find out how many people are in each faculty
Mysql> SELECT Department, COUNT (ID) from student GROUP by department;
+------------+-----------+
| Department | COUNT (ID) |
+------------+-----------+
| Computer Systems | 2 |
| English Department | 2 |
| Chinese Department | 2 |
+------------+-----------+
9. Query the highest score for each account from the score table

mysql> SELECT c_name,MAX(grade) FROM score GROUP BY c_name;+--------+------------+| c_name | MAX(grade) |+--------+------------+| 计算机 |         98 || 英语   |         94 || 中文   |         95 |+--------+------------+

10. Query John Doe exam subjects (C_NAME) and exam results (grade)

mysql> SELECT c_name, grade    ->      FROM score WHERE stu_id=    ->  (SELECT id FROM student    ->    WHERE name= ‘李四‘ );+--------+-------+| c_name | grade |+--------+-------+| 计算机 |    70 || 英语   |    92 |+--------+-------+

11. Access all students ' information and exam information in a connected way

Mysql> SELECT Student.id,name,sex,birth,department,address,c_name,grade, from Student,score, WHERE Stu dent.id=score.stu_id;+-----+--------+------+-------+------------+--------------+--------+-------+| ID | name | sex | Birth | Department | Address | C_name | Grade |+-----+--------+------+-------+------------+--------------+--------+-------+| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |    Computer | 98 | | 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |    English | 80 | | 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |    Computer | 65 | | 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |    English | 88 | | 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |    English | 95 | | 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |    Computer | 70 | | 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |    English | 92 | | 905 | Harry |  Women | 1991 | English Department | Xiamen, Fujian |    English | 94 | | 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    Computer | 90 | | 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    English | |+-----+--------+------+-------+------------+--------------+--------+-------+ 

12. Calculate the total of each student

mysql> SELECT student.id,name,SUM(grade) FROM student,score    -> WHERE student.id=score.stu_id    -> GROUP BY id;+-----+--------+------------+| id  | name   | SUM(grade) |+-----+--------+------------+| 901 | 张老大 |        178 || 902 | 张老二 |        153 || 903 | 张三   |         95 || 904 | 李四   |        162 || 905 | 王五   |         94 || 906 | 王六   |        175 |+-----+--------+------------+

13. Calculate the average score for each test subject

mysql> SELECT c_name,AVG(grade) FROM score GROUP BY c_name;+--------+------------+| c_name | AVG(grade) |+--------+------------+| 计算机 |    80.7500 || 英语   |    87.7500 || 中文   |    91.5000 |+--------+------------+

14. Check the student information of computer score below 95

mysql> SELECT * FROM student    -> WHERE id IN    -> (SELECT stu_id FROM score    -> WHERE c_name="计算机" and grade<95);+-----+--------+------+-------+------------+--------------+| id  | name   | sex  | birth | department | address      |+-----+--------+------+-------+------------+--------------+| 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 || 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 || 906 | 王六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |+-----+--------+------+-------+------------+--------------+

15. Check the information of students who participate in both computer and English exams

mysql> SELECT *  FROM student    ->  WHERE id =ANY    ->  ( SELECT stu_id FROM score    ->  WHERE stu_id IN (    ->          SELECT stu_id FROM    ->          score WHERE c_name=  ‘计算机‘)    ->  AND c_name= ‘英语‘ );+-----+--------+------+-------+------------+--------------+| id  | name   | sex  | birth | department | address      |+-----+--------+------+-------+------------+--------------+| 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 || 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 || 906 | 王六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |+-----+--------+------+-------+------------+--------------+

Mysql> SELECT a.* from student A, score B, score C
--WHERE a.id=b.stu_id
and b.c_name= ' computer '
and a.id=c.stu_id
and C.c_name= ' English ';
+-----+--------+------+-------+------------+--------------+
| ID | name | sex | Birth | Department | Address |
+-----+--------+------+-------+------------+--------------+

| 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 || 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 || 906 | 王六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |+-----+--------+------+-------+------------+--------------+

16. Sort computer test scores from highest to lowest

mysql> SELECT stu_id, grade    ->  FROM score WHERE c_name= ‘计算机‘    ->  ORDER BY grade DESC;+--------+-------+| stu_id | grade |+--------+-------+|    901 |    98 ||    906 |    90 ||    904 |    70 ||    902 |    65 |+--------+-------+

17. Query the student's number from the student table and the score table, then merge the query results

mysql> SELECT id  FROM student    -> UNION    -> SELECT stu_id  FROM score;+-----+| id  |+-----+| 901 || 902 || 903 || 904 || 905 || 906 |+-----+

18. Check the name of the student surnamed Zhang or Wang surname, department and examination subjects and results

Mysql> SELECT Student.id, name,sex,birth,department, Address, c_name,grade, from student, score, WHERE ---(name like '% ' OR name like ' King% '), student.id=score.stu_id, +-----+--------+------+-- -----+------------+--------------+--------+-------+| ID | name | sex | Birth | Department | Address | C_name | Grade |+-----+--------+------+-------+------------+--------------+--------+-------+| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |    Computer | 98 | | 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |    English | 80 | | 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |    Computer | 65 | | 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |    English | 88 | | 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |    English | 95 | | 905 | Harry |  Women | 1991 | English Department | Xiamen, Fujian |    English | 94 | | 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    Computer | 90 | | 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    English | |+-----+--------+------+-------+------------+--------------+--------+-------+ 

19. Enquiries are the names, ages, faculties and examinations of students in Hunan province and their scores

mysql> SELECT student.id, name,sex,birth,department, address, c_name,grade    -> FROM student, score    -> WHERE address LIKE ‘湖南%‘   AND    ->  student.id=score.stu_id;+-----+------+------+-------+------------+--------------+--------+-------+| id  | name | sex  | birth | department | address      | c_name | grade |+-----+------+------+-------+------------+--------------+--------+-------+| 903 | 张三 | 女   |  1990 | 中文系     | 湖南省永州市 | 中文   |    95 || 906 | 王六 | 男   |  1988 | 计算机系   | 湖南省衡阳市 | 计算机 |    90 || 906 | 王六 | 男   |  1988 | 计算机系   | 湖南省衡阳市 | 英语   |    85 |+-----+------+------+-------+------------+--------------+--------+-------+

MySQL Database for practice testing