Exercises

Table 1

mysql> create database linux50 charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux50            |
| ming               |
| mysql              |
| performance_schema |
| test               |
| world              |
| xudao              |
+--------------------+
8 rows in set (0.00 sec)
mysql> \u linux50
Database changed
mysql> create table student(sno bigint(20) not null primary key auto_increment comment 'Student ID',
    -> sname varchar(300) not null comment 'Student name',
    -> sage tinyint unsigned not null comment 'Student age',
    -> ssex enum('1','0') not null  default '1' comment 'Student gender',
    -> sbirthday datetime  default null comment 'Student birthday',
    -> class int not null comment 'Student class') ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> select *from student;
+-----+-----------+------+------+---------------------+-------+
| sno | sname     | sage | ssex | sbirthday           | class |
+-----+-----------+------+------+---------------------+-------+
|   1 | Wang Liying    |   18 | 1    | 2018-11-28 17:45:58 |     7 |
|   2 | Wang Ya Jiao    |   19 | 0    | 2018-11-28 17:50:07 |     7 |
|   3 | Cheng Kang Hua    |   22 | 1    | 2018-11-28 17:50:40 |     7 |
|   4 | Guo Ya Wang    |   20 | 1    | 2018-11-28 17:51:19 |     7 |
|   5 | Wen Chang Qing    |   21 | 1    | 2018-11-28 17:51:42 |     7 |
|   6 | Ma Hui Feng    |   20 | 1    | 2018-11-28 20:54:04 |     5 |
|   7 | Jing Wong      |   20 | 0    | 2018-11-28 20:55:00 |     3 |
+-----+-----------+------+------+---------------------+-------+

Table two

mysql> create table source(cno bigint(20) not null primary key auto_increment comment 'Course number',  cname varchar(50) not null comment 'Course title',  tno int(3) zerofill not null  comment 'Teacher number'  );
Query OK, 0 rows affected (0.02 sec)
mysql> select *from source;
+-----+--------+-----+
| cno | cname  | tno |
+-----+--------+-----+
|   1 | Chinese   | 001 |
|   2 | Mathematics   | 002 |
|   3 | English?   | 003 |
+-----+--------+-----+

Table three

mysql> create table score(sno bigint(20) not null comment 'Student ID',
    -> cno bigint(20) not null  comment 'Course number',
    -> mark double(4,1) not null comment 'achievement',
    -> primary key(sno,cno)
    -> );
   mysql> select *from score;
+-----+-----+-------+
| sno | cno | mark  |
+-----+-----+-------+
|   1 |   1 |  90.0 |
|   2 |   1 |  90.0 |
|   2 |   2 |  70.0 |
|   2 |   3 |  70.0 |
|   3 |   1 |  95.0 |
|   3 |   2 | 100.0 |
+-----+-----+-------+
6 rows in set (0.00 sec)

Table four

create table teacher(cno int(3) zerofill not null primary key auto_increment comment 'Teacher number',
tname varchar(50) not null comment 'Teacher's name',
tage tinyint unsigned not null comment 'Age of Teachers',
tsex enum('1','0') not null default '1' comment 'Gender of Teachers',
prof varchar(100) comment 'Teacher title',
depart varchar(50) comment 'Teachers' Department'
);

Collective practice

Query exercise:

1. Query sname, ssex and class columns of all records in student table.

mysql> select sname,ssex,class from student;
+-----------+------+-------+
| sname     | ssex | class |
+-----------+------+-------+
| Wang Liying    | 1    |     7 |
| Wang Ya Jiao    | 0    |     7 |
| Cheng Kang Hua    | 1    |     7 |
| Guo Ya Wang    | 1    |     7 |
| Wen Chang Qing    | 1    |     7 |
| Ma Hui Feng    | 1    |     5 |
| Jing Wong      | 0    |     3 |
+-----------+------+-------+

2. Query all units of the teacher, i.e. non duplicate Department column.

mysql> select *from teacher;
+-----+--------------+------+------+--------------+--------------+
| cno | tname        | tage | tsex | prof         | depart       |
+-----+--------------+------+------+--------------+--------------+
| 001 | Gao Xiang     |   23 | 1    | DBA Boss      | DBA system        |
| 002 | Xu Liang Wei       |   24 | 1    | Lecturer     | Linux system      |
| 003 | Li Yong Yi       |   26 | 1    | Comprehensive boss     | Old boy Department     |
| 004 | Old boy       |   24 | 1    | Company boss     | Old boy Department     |
+-----+--------------+------+------+--------------+--------------+

3. Query all records of student table.

mysql> select *from student;
+-----+-----------+------+------+---------------------+-------+
| sno | sname     | sage | ssex | sbirthday           | class |
+-----+-----------+------+------+---------------------+-------+
|   1 | Wang Liying    |   18 | 1    | 2018-11-28 17:45:58 |     7 |
|   2 | Wang Ya Jiao    |   19 | 0    | 2018-11-28 17:50:07 |     7 |
|   3 | Cheng Kang Hua    |   22 | 1    | 2018-11-28 17:50:40 |     7 |
|   4 | Guo Ya Wang    |   20 | 1    | 2018-11-28 17:51:19 |     7 |
|   5 | Wen Chang Qing    |   21 | 1    | 2018-11-28 17:51:42 |     7 |
|   6 | Ma Hui Feng    |   20 | 1    | 2018-11-28 20:54:04 |     5 |
|   7 | Jing Wong      |   20 | 0    | 2018-11-28 20:55:00 |     3 |
+-----+-----------+------+------+---------------------+-------+

4. Query all records with scores between 60 and 80 in the score table.

5. Check the score of 85, 86 or 88.

select * from score where Degree in (90,95,70);

6. Query the student records of class 7 or gender "female" in the student table.

7. Query all records of Student table in class descending order.

mysql> select *from student order by class desc;
+-----+-----------+------+------+---------------------+-------+
| sno | sname     | sage | ssex | sbirthday           | class |
+-----+-----------+------+------+---------------------+-------+
|   1 | Wang Liying    |   18 | 1    | 2018-11-28 17:45:58 |     7 |
|   2 | Wang Ya Jiao    |   19 | 0    | 2018-11-28 17:50:07 |     7 |
|   3 | Cheng Kang Hua    |   22 | 1    | 2018-11-28 17:50:40 |     7 |
|   4 | Guo Ya Wang    |   20 | 1    | 2018-11-28 17:51:19 |     7 |
|   5 | Wen Chang Qing    |   21 | 1    | 2018-11-28 17:51:42 |     7 |
|   6 | Ma Hui Feng    |   20 | 1    | 2018-11-28 20:54:04 |     5 |
|   7 | Jing Wong      |   20 | 0    | 2018-11-28 20:55:00 |     3 |

8. Query all records in Score table in ascending cno and descending mark order

mysql> select *from score order by cno;
+-----+-----+-------+
| sno | cno | mark  |
+-----+-----+-------+
|   1 |   1 |  90.0 |
|   2 |   1 |  90.0 |
|   3 |   1 |  95.0 |
|   2 |   2 |  70.0 |
|   3 |   2 | 100.0 |
|   2 |   3 |  70.0 |
+-----+-----+-------+
mysql> select *from score order by  mark desc;
+-----+-----+-------+
| sno | cno | mark  |
+-----+-----+-------+
|   3 |   2 | 100.0 |
|   3 |   1 |  95.0 |
|   1 |   1 |  90.0 |
|   2 |   1 |  90.0 |
|   2 |   2 |  70.0 |
|   2 |   3 |  70.0 |
+-----+-----+-------+

9. Check the number of students in class 7.

mysql> select count(*)  from student where class='7';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

10. Inquire about the students' performance of "Zeng zhigaoxiang".

mysql> select teacher.tname, student.sno,student.sname,score.mark 
    -> from teacher,student,score,course
    -> where student.sno=score.sno and
    -> score.cno=course.cno
    -> and course.tno=teacher.tno 
    -> and teacher.tno='001';

11. Check the scores of all boys in the Chinese course and find out the teacher's name, title and Department of the corresponding course.

mysql> select student.sname,score.mark,teacher.tname,teacher.prof,teacher.depart 
    -> from teacher,student,score,course
    -> where student.sno=score.sno and
    -> score.cno=course.cno and
    -> course.tno=teacher.tno and
    -> student.ssex='1'
-> and course.cname='Chinese';

12. Rank the results of 11 questions in descending order.

mysql> select student.sname,score.mark,teacher.tname,teacher.prof,teacher.depart 
    -> from teacher,student,score,course
    -> where student.sno=score.sno and
    -> score.cno=course.cno and
    -> course.tno=teacher.tno and
    -> student.ssex='1'
-> and course.cname='Chinese'
-> order by score.mark desc;