SQL - Atelier - Kaamelott

➜  ~ git:(master) ✗ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
…
mysql> CREATE DATABASE kaamelott;
…
mysql> USE kaamelott;
Database changed
mysql> CREATE TABLE knight ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(80) NOT NULL,
    -> age INT NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO knight (name, age) VALUES ('Arthur', 40), ('Perceval', 35), ('Lancelot', 38);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DESCRIBE knight;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(80) | NO   |     | NULL    |                |
| age   | int         | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM knight;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Arthur   |  40 |
|  2 | Perceval |  35 |
|  3 | Lancelot |  38 |
+----+----------+-----+
3 rows in set (0.00 sec)

mysql> UPDATE knight SET age = 36 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM knight;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Arthur   |  40 |
|  2 | Perceval |  36 |
|  3 | Lancelot |  38 |
+----+----------+-----+
3 rows in set (0.00 sec)

mysql> SELECT * FROM knight WHERE age > 37 ;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Arthur   |  40 |
|  3 | Lancelot |  38 |
+----+----------+-----+
2 rows in set (0.00 sec)

mysql> ALTER TABLE knight ADD is_dubbed BOOLEAN;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE knight;
  +-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(80) | NO   |     | NULL    |                |
| age       | int         | NO   |     | NULL    |                |
| is_dubbed | tinyint(1)  | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> UPDATE knight SET is_dubbed = true;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM knight;
+----+----------+-----+-----------+
| id | name     | age | is_dubbed |
+----+----------+-----+-----------+
|  1 | Arthur   |  40 |         1 |
|  2 | Perceval |  36 |         1 |
|  3 | Lancelot |  38 |         1 |
+----+----------+-----+-----------+
3 rows in set (0.00 sec)

mysql> UPDATE knight SET is_dubbed = false WHERE name = 'Perceval';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM knight
    -> ;
+----+----------+-----+-----------+
| id | name     | age | is_dubbed |
+----+----------+-----+-----------+
|  1 | Arthur   |  40 |         1 |
|  2 | Perceval |  36 |         0 |
|  3 | Lancelot |  38 |         1 |
+----+----------+-----+-----------+
3 rows in set (0.00 sec)

mysql> DELETE FROM knight WHERE name = 'Lancelot';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM knight
    -> ;
+----+----------+-----+-----------+
| id | name     | age | is_dubbed |
+----+----------+-----+-----------+
|  1 | Arthur   |  40 |         1 |
|  2 | Perceval |  36 |         0 |
+----+----------+-----+-----------+
2 rows in set (0.00 sec)

mysql> TRUNCATE TABLE knight;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM knight;
Empty set (0.00 sec)

mysql> DROP TABLE knight;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE weapon ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(50));
Query OK, 0 rows affected (0.01 sec)

mysql> DESCRIBE weapon;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

.. .. ..