目录
作业
- 1.项目三:超过5名学生的课(难度:简单)
创建如下所示的courses 表 ,有: student (学生) 和 class (课程)。
例如,表:
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
| A | Math |
+---------+------------+
编写一个 SQL 查询,列出所有超过或等于5名学生的课。
Note:
学生在每个课中不应被重复计算。
解答:
mysql> use datawhaledb
Database changed
mysql> CREATE TABLE courses(
-> student VARCHAR(64) NOT NULL,
-> class VARCHAR(20)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> INSERT INTO courses VALUES('A','Math');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO courses VALUES('B','English');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO courses VALUES('C','Math');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO courses VALUES('D','Biology');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO courses VALUES('E','Math');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO courses VALUES('F','Computer');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO courses VALUES('G','Math');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO courses VALUES('H','Math');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO courses VALUES('I','Math');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO courses VALUES('A','Math');
Query OK, 1 row affected (0.00 sec)
mysql>mysql> select class
from courses
group by class
having count(distinct student)>=5;
+-------+
| class |
+-------+
| Math |
+-------+
1 row in set (0.00 sec)
mysql>
- 2.项目四:交换工资(难度:简单)
创建一个 salary表,如下所示,有m=男性 和 f=女性的值 。
例如:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求使用一个更新查询,并且没有中间临时表。
运行你所编写的查询语句之后,将会得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
mysql> CREATE TABLE salary(
-> id INT NOT NULL PRIMARY KEY,
-> name VARCHAR(20) NOT NULL,
-> sex CHAR NOT NULL,
-> salary int
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO salary VALUES(1,'A','m',2500);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO salary VALUES(2,'B','f',5500);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO salary VALUES(3,'C','m',5000);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO salary VALUES(4,'D','f',1500);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO salary VALUES(5,'E','f',2000);
Query OK, 1 row affected (0.01 sec)
mysql>