MySQL Query exercise

Last Update:2016-03-29 Source: Internet

Author: User

Tags mysql query

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

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;

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

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

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

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

Mysql> SELECT * FROM student LIMIT 1,3;

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

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

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

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

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

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

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

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 |

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

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

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 |

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

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

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;

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

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

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

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]

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

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

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

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"

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

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

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

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

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

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

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

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;

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

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

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

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;

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

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

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

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;

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

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

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

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]

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

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

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

1 row in Set (0.00 sec)

Last three data:"Here is a question"

MySQL Query exercise

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

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

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

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

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More