MySQL Query exercise

Source: Internet
Author: User
Tags mysql query

The picture is omitted.

This article transferred from: http://blog.sina.com.cn/s/blog_767d65530101861c.html


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 boss ', ' Male ', 1985, ' computer Department ', ' Haidian District, Beijing ');

INSERT into student VALUES (902, ' Zhang Dick ', ' Male ', 1986, ' Chinese department ', ' Beijing Changping District ');

INSERT into student VALUES (903, ' Zhang San ', ' Female ', 1990, ' Chinese department ', ' Hunan province Yongzhou ');

INSERT into student VALUES (904, ' John Doe ', ' Male ', 1990, ' English Department ', ' Liaoning province Fuxin ');

INSERT into student VALUES (905, ' Harry ', ' Female ', 1991, ' English Department ', ' Xiamen, Fujian Province ');

INSERT into student VALUES (906, ' King vi ', ' 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 ', 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

Mysql> SELECT * from student;

+-----+--------+------+-------+------------+--------------+

| ID | name | sex | Birth | Department | Address |

+-----+--------+------+-------+------------+--------------+

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |

| 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |

| 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |

| 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 |

+-----+--------+------+-------+------------+--------------+

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 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |

| 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |

+-----+--------+------+-------+------------+--------------+

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 | Boss Zhang | Computer Systems |

| 902 | Double Dick | Chinese Department |

| 903 | Zhang San | Chinese Department |

| 904 | John Doe | English Department |

| 905 | Harry | English Department |

| 906 | Wangliuqi | Computer Systems |

+-----+--------+------------+

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. Check the age 18~22岁 student information from the student table

Mysql> SELECT id,name,sex,2013-birth as Age,department,address

From student

WHERE 2013-birth between and 22;

+-----+------+------+------+------------+--------------+

| ID | name | sex | Age | Department | Address |

+-----+------+------+------+------------+--------------+

| 905 | Harry |   Women | 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 |   Women | 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; [Note: not top]

+--------+------------+

| C_name | MAX (grade) |

+--------+------------+

|         Computer | 98 |

|         English | 94 |

|         English | 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= ' John Doe ');

+--------+-------+

| C_name | Grade |

+--------+-------+

|    Computer | 70 |

|    English | 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 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 |

| 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 | 85 |

+-----+--------+------+-------+------------+--------------+--------+-------+

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; [Note: not count]

+-----+--------+------------+

| ID | name | SUM (grade) |

+-----+--------+------------+

| 901 |        Boss Zhang | 178 |

| 902 |        Double Dick | 153 |

| 903 |         Zhang San | 95 |

| 904 |        John Doe | 162 |

| 905 |         Harry | 94 |

| 906 |        Wangliuqi | 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) |

+--------+------------+

|    Computer | 80.7500 |

|    English | 87.7500 |

|    English | 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= "Computer" and grade<95); [Note: query for SELECT clause]

+-----+--------+------+-------+------------+--------------+

| ID | name | sex | Birth | Department | Address |

+-----+--------+------+-------+------------+--------------+

| 902 | Double Dick |  Male | 1986 | Chinese Department | Beijing Changping District |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |

+-----+--------+------+-------+------------+--------------+

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= ' computer ')

and C_name= ' English '); "Here's a question"

+-----+--------+------+-------+------------+--------------+

| 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 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |

+-----+--------+------+-------+------------+--------------+

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 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |

+-----+--------+------+-------+------------+--------------+

16. Sort computer test scores from highest to lowest

Mysql> SELECT stu_id, Grade

-From score WHERE c_name= ' computer '

ORDER by grade DESC; [ Note: The default is positive order ]

+--------+-------+

| 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; "Here's a question"

+-----+

| 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% ')

and

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 | 85 |

+-----+--------+------+-------+------------+--------------+--------+-------+

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 ' Hunan% ' and

student.id=score.stu_id;

+-----+------+------+-------+------------+--------------+--------+-------+

| ID | name | sex | Birth | Department | Address | C_name | Grade |

+-----+------+------+-------+------------+--------------+--------+-------+

| 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |    English | 95 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    Computer | 90 |

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |    English | 85 |

+-----+------+------+-------+------------+--------------+--------+-------+

Supplemental sections:

======================================================================================

20. Query the first data, the last data and the last 3 data of the student table respectively.

First piece of data:

Mysql> SELECT * from student limit 1;

+-----+-----------+------+-------+--------------+--------------------+

| ID | name | sex | Birth | Department | Address |

+-----+-----------+------+-------+--------------+--------------------+

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |

+-----+-----------+------+-------+--------------+--------------------+

1 row in Set (0.00 sec)

Last Piece of data:

Mysql> SELECT * FROM student ORDER by id desc Limit 1; [Note: Check the first rule in reverse]

+-----+--------+------+-------+--------------+--------------------+

| ID | name | sex | Birth | Department | Address |

+-----+--------+------+-------+--------------+--------------------+

| 906 | Wangliuqi |  Male | 1988 | Computer Systems | Hunan Province Hengyang |

+-----+--------+------+-------+--------------+--------------------+

1 row in Set (0.00 sec)

Last three data:"Here is a question"


MySQL Query exercise

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.