Learn Linux, 101: Use basic SQL commands

[ian@attic-f23 ~]$ unzip -q test_db-master.zip
[ian@attic-f23 ~]$ cd test_db-master
[ian@attic-f23 test_db-master]$ mysql -u root -p <employees.sql
Enter password:
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
NULL

[ian@attic-f23 ~]$ mysqlshow -u root -p
Enter password:
+--------------------+
|     Databases      |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[ian@attic-f23 ~]$ mysql -u root -p -e "show databases"
Enter password:
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[ian@attic4-cent ~]$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/db2inst1
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

[ian@attic-f23 ~]$ mysql -u root -p employees
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.0.21-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [employees]> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

MariaDB [employees]> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MariaDB [employees]> ? describe
Name: 'DESCRIBE'
Description:
Syntax:
{DESCRIBE | DESC} tbl_name [col_name | wild]

DESCRIBE provides information about the columns in a table. It is a
shortcut for SHOW COLUMNS FROM. These statements also display
information for views. (See [HELP SHOW COLUMNS].)

col_name can be a column name, or a string containing the SQL "%" and
"_" wildcard characters to obtain output only for the columns with
names matching the string. There is no need to enclose the string
within quotation marks unless it contains spaces or other special
characters.

MariaDB> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

The description for SHOW COLUMNS provides more information about the
output columns (see [HELP SHOW COLUMNS]).

URL: https://mariadb.com/kb/en/library/describe/

MariaDB [employees]> describe departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no   | char(4)     | NO   | PRI | NULL    |       |
| dept_name | varchar(40) | NO   | UNI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [employees]> select * from departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0.00 sec)

MariaDB [employees]> select dept_name from departments;
+--------------------+
| dept_name          |
+--------------------+
| Customer Service   |
| Development        |
| Finance            |
| Human Resources    |
| Marketing          |
| Production         |
| Quality Management |
| Research           |
| Sales              |
+--------------------+
9 rows in set (0.00 sec)

MariaDB [employees]> select dept_name,dept_no from departments order by dept_no desc;
+--------------------+---------+
| dept_name          | dept_no |
+--------------------+---------+
| Customer Service   | d009    |
| Research           | d008    |
| Sales              | d007    |
| Quality Management | d006    |
| Development        | d005    |
| Production         | d004    |
| Human Resources    | d003    |
| Finance            | d002    |
| Marketing          | d001    |
+--------------------+---------+
9 rows in set (0.00 sec)

MariaDB [employees]> select * from departments where dept_no > 'd007';
+---------+------------------+
| dept_no | dept_name        |
+---------+------------------+
| d008    | Research         |
| d009    | Customer Service |
+---------+------------------+
2 rows in set (0.00 sec)

MariaDB [employees]> select * from departments
    -> WHERE dept_name = 'Customer Service' OR (
    -> dept_no > 'd002' AND dept_no <= "d006"
    -> );
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d003    | Human Resources    |
| d004    | Production         |
| d006    | Quality Management |
+---------+--------------------+
5 rows in set (0.00 sec)

MariaDB [employees]> select * from departments WHERE dept_name LIKE '%es%';
+---------+-----------------+
| dept_no | dept_name       |
+---------+-----------------+
| d003    | Human Resources |
| d008    | Research        |
| d007    | Sales           |
+---------+-----------------+
3 rows in set (0.00 sec)

MariaDB [employees]> # How many employees?
MariaDB [employees]> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.08 sec)

MariaDB [employees]> # How many managers?
MariaDB [employees]> select count(*) from dept_manager;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

MariaDB [employees]> describe dept_manager;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| dept_no   | char(4) | NO   | PRI | NULL    |       |
| from_date | date    | NO   |     | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [employees]> select DISTINCT(from_date) from dept_manager order by from_date;
+------------+
| from_date  |
+------------+
| 1985-01-01 |
| 1988-09-09 |
| 1988-10-17 |
| 1989-05-06 |
| 1989-12-17 |
| 1991-03-07 |
| 1991-04-08 |
| 1991-09-12 |
| 1991-10-01 |
| 1992-03-21 |
| 1992-04-25 |
| 1992-08-02 |
| 1992-09-08 |
| 1994-06-28 |
| 1996-01-03 |
| 1996-08-30 |
+------------+
16 rows in set (0.00 sec)

MariaDB [employees]> # Show marker for managers still managing
MariaDB [employees]> select max(to_date) from dept_manager;
+--------------+
| max(to_date) |
+--------------+
| 9999-01-01   |
+--------------+
1 row in set (0.00 sec)

MariaDB [employees]> # Calculate duration of management in days
MariaDB [employees]> select emp_no,
     datediff(least(to_date, curdate()),from_date)
     from dept_manager;
+--------+-----------------------------------------------+
| emp_no | datediff(least(to_date, curdate()),from_date) |
+--------+-----------------------------------------------+
| 110022 |                                          2464 |
| 110039 |                                          8869 |
| 110085 |                                          1811 |
| 110114 |                                          9522 |
| 110183 |                                          2636 |
| 110228 |                                          8697 |
| 110303 |                                          1347 |
| 110344 |                                          1423 |
| 110386 |                                          1489 |
| 110420 |                                          7074 |
| 110511 |                                          2671 |
| 110567 |                                          8662 |
| 110725 |                                          1586 |
| 110765 |                                           859 |
| 110800 |                                          1020 |
| 110854 |                                          7868 |
| 111035 |                                          2256 |
| 111133 |                                          9077 |
| 111400 |                                          2288 |
| 111534 |                                          9045 |
| 111692 |                                          1385 |
| 111784 |                                          1422 |
| 111877 |                                          1212 |
| 111939 |                                          7314 |
+--------+-----------------------------------------------+
24 rows in set (0.00 sec)

MariaDB [employees]> select emp_no AS Employee,
     (datediff(least(to_date, curdate()),from_date)/365.25) as Years
     from dept_manager where to_date > curdate();
+----------+---------+
| Employee | Years   |
+----------+---------+
|   110039 | 24.2820 |
|   110114 | 26.0698 |
|   110228 | 23.8111 |
|   110420 | 19.3676 |
|   110567 | 23.7153 |
|   110854 | 21.5414 |
|   111133 | 24.8515 |
|   111534 | 24.7639 |
|   111939 | 20.0246 |
+----------+---------+
9 rows in set (0.00 sec)

MariaDB [employees]> describe salaries;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| salary    | int(11) | NO   |     | NULL    |       |
| from_date | date    | NO   | PRI | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [employees]> select salary DIV 10000 as 10K, count(*) as Number
     from salaries where to_date > curdate() group by 10K;
+------+--------+
| 10K  | Number |
+------+--------+
|    3 |     85 |
|    4 |  20220 |
|    5 |  44666 |
|    6 |  56236 |
|    7 |  49128 |
|    8 |  32351 |
|    9 |  19939 |
|   10 |  10611 |
|   11 |   4729 |
|   12 |   1645 |
|   13 |    421 |
|   14 |     78 |
|   15 |     15 |
+------+--------+
13 rows in set (1.42 sec)

MariaDB [employees]> select salary DIV 10000 as 10K, count(*) as Number
     from salaries where to_date > curdate() group by 10K
     HAVING Number <= 5000;
+------+--------+
| 10K  | Number |
+------+--------+
|    3 |     85 |
|   11 |   4729 |
|   12 |   1645 |
|   13 |    421 |
|   14 |     78 |
|   15 |     15 |
+------+--------+
6 rows in set (1.45 sec)

MariaDB [employees]> SELECT e.first_name, e.last_name, e.gender
     FROM employees as e JOIN dept_manager as m
     ON e.emp_no = m.emp_no
     WHERE m.to_date > now()
     ORDER BY e.last_name;
+------------+------------+--------+
| first_name | last_name  | gender |
+------------+------------+--------+
| Leon       | DasSarma   | F      |
| Oscar      | Ghazalie   | M      |
| Hilary     | Kambil     | F      |
| Isamu      | Legleitner | F      |
| Vishwani   | Minakawa   | M      |
| Dung       | Pesch      | M      |
| Karsten    | Sigstam    | F      |
| Yuchang    | Weedman    | M      |
| Hauke      | Zhang      | M      |
+------------+------------+--------+
9 rows in set (0.00 sec)

MariaDB [employees]> SELECT e.first_name, e.last_name, s.salary
     FROM (employees as e JOIN dept_manager as m ON e.emp_no = m.emp_no)
     JOIN salaries as s on e.emp_no = s.emp_no
     WHERE m.to_date > now() AND s.to_date > now()
     ORDER BY e.last_name;
+------------+------------+--------+
| first_name | last_name  | salary |
+------------+------------+--------+
| Leon       | DasSarma   |  74510 |
| Oscar      | Ghazalie   |  56654 |
| Hilary     | Kambil     |  79393 |
| Isamu      | Legleitner |  83457 |
| Vishwani   | Minakawa   | 106491 |
| Dung       | Pesch      |  72876 |
| Karsten    | Sigstam    |  65400 |
| Yuchang    | Weedman    |  58745 |
| Hauke      | Zhang      | 101987 |
+------------+------------+--------+
9 rows in set (0.00 sec)

MariaDB [employees]> SELECT e.emp_no, e.first_name, e.last_name, s.salary, m.from_date, m.to_date
     FROM (employees as e LEFT JOIN dept_manager as m ON e.emp_no = m.emp_no)
     JOIN salaries as s on e.emp_no = s.emp_no AND s.to_date > now()
     WHERE e.last_name LIKE 'Kambi%' AND e.first_name > 'G'
     ORDER BY e.last_name, e.first_name limit 15;
+--------+------------+-----------+--------+------------+------------+
| emp_no | first_name | last_name | salary | from_date  | to_date    |
+--------+------------+-----------+--------+------------+------------+
| 431582 | Gaurav     | Kambil    | 118128 | NULL       | NULL       |
| 252478 | Gaurav     | Kambil    |  69516 | NULL       | NULL       |
| 487991 | Gift       | Kambil    | 115960 | NULL       | NULL       |
| 204311 | Gil        | Kambil    |  96756 | NULL       | NULL       |
| 416604 | Gonzalo    | Kambil    |  80009 | NULL       | NULL       |
| 236164 | Hausi      | Kambil    |  66130 | NULL       | NULL       |
| 412003 | Hausi      | Kambil    |  83213 | NULL       | NULL       |
| 111534 | Hilary     | Kambil    |  79393 | 1991-04-08 | 9999-01-01 |
| 295702 | Huei       | Kambil    |  49498 | NULL       | NULL       |
|  77408 | Idoia      | Kambil    |  67122 | NULL       | NULL       |
| 271049 | Jianhao    | Kambil    |  58393 | NULL       | NULL       |
| 216820 | JiYoung    | Kambil    |  87541 | NULL       | NULL       |
| 206261 | Jongsuk    | Kambil    |  78396 | NULL       | NULL       |
| 250164 | Josyula    | Kambil    |  98835 | NULL       | NULL       |
| 289558 | Jouko      | Kambil    |  51393 | NULL       | NULL       |
+--------+------------+-----------+--------+------------+------------+
15 rows in set (0.14 sec)

MariaDB [employees]> describe titles;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int(11)     | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [employees]> select * from titles  where emp_no IN (10001, 10004, 499666);

+--------+--------------------+------------+------------+
| emp_no | title              | from_date  | to_date    |
+--------+--------------------+------------+------------+
|  10001 | Senior Engineer    | 1986-06-26 | 9999-01-01 |
|  10004 | Engineer           | 1986-12-01 | 1995-12-01 |
|  10004 | Senior Engineer    | 1995-12-01 | 9999-01-01 |
| 499666 | Assistant Engineer | 1987-10-18 | 1994-10-18 |
| 499666 | Engineer           | 1994-10-18 | 2001-10-18 |
| 499666 | Senior Engineer    | 2001-10-18 | 9999-01-01 |
+--------+--------------------+------------+------------+
6 rows in set (0.00 sec)

MariaDB [employees]> select * from titles  where emp_no BETWEEN 10001 AND 10004;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  10004 | Engineer        | 1986-12-01 | 1995-12-01 |
|  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
+--------+-----------------+------------+------------+
5 rows in set (0.01 sec)

MariaDB [employees]> SELECT count(*) as jobs from titles group by emp_no limit 5;
+------+
| jobs |
+------+
|    1 |
|    1 |
|    1 |
|    2 |
|    2 |
+------+
5 rows in set (0.00 sec)

MariaDB [employees]> SELECT jobs as '# jobs', count(subq.jobs) as '# employees'
     FROM (
     SELECT count(*) as jobs from titles group by emp_no
     ) subq
     group by jobs;
+--------+-------------+
| # jobs | # employees |
+--------+-------------+
|      1 |      159754 |
|      2 |      137256 |
|      3 |        3014 |
+--------+-------------+
3 rows in set (0.31 sec)

MariaDB [employees]> SELECT e.emp_no, e.last_name, e.first_name,
     (
     SELECT MAX(salary) FROM salaries s
     WHERE e.emp_no = s.emp_no
     ) max_sal
     FROM employees e ORDER BY max_sal desc limit 10;
+--------+-----------+------------+---------+
| emp_no | last_name | first_name | max_sal |
+--------+-----------+------------+---------+
|  43624 | Pesch     | Tokuyasu   |  158220 |
| 254466 | Mukaidono | Honesty    |  156286 |
|  47978 | Whitcomb  | Xiahua     |  155709 |
| 253939 | Luders    | Sanjai     |  155513 |
| 109334 | Alameldin | Tsutomu    |  155377 |
|  80823 | Baca      | Willard    |  154459 |
| 493158 | Meriste   | Lidong     |  154376 |
| 205000 | Griswold  | Charmane   |  153715 |
| 266526 | Chenoweth | Weijing    |  152710 |
| 237542 | Hatcliff  | Weicheng   |  152687 |
+--------+-----------+------------+---------+
10 rows in set (2.66 sec)

MariaDB [employees]> SELECT e.emp_no, e.last_name, e.first_name,  max(s.salary) as max_sal
     FROM employees e JOIN salaries s
     ON e.emp_no = s.emp_no
     GROUP BY e.emp_no ORDER BY max_sal desc limit 10;
+--------+-----------+------------+---------+
| emp_no | last_name | first_name | max_sal |
+--------+-----------+------------+---------+
|  43624 | Pesch     | Tokuyasu   |  158220 |
| 254466 | Mukaidono | Honesty    |  156286 |
|  47978 | Whitcomb  | Xiahua     |  155709 |
| 253939 | Luders    | Sanjai     |  155513 |
| 109334 | Alameldin | Tsutomu    |  155377 |
|  80823 | Baca      | Willard    |  154459 |
| 493158 | Meriste   | Lidong     |  154376 |
| 205000 | Griswold  | Charmane   |  153715 |
| 266526 | Chenoweth | Weijing    |  152710 |
| 237542 | Hatcliff  | Weicheng   |  152687 |
+--------+-----------+------------+---------+
10 rows in set (2.16 sec)

MariaDB [employees]> SELECT * FROM (
     SELECT * FROM employees LIMIT 5
     ) x ORDER BY x.gender;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

MariaDB [employees]> CREATE TABLE gender (
     code CHAR(1) NOT NULL,
     gender VARCHAR(20) NOT NULL,
     PRIMARY KEY (code)
     );
Query OK, 0 rows affected (0.23 sec)

MariaDB [employees]> describe gender;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| code   | char(1)     | NO   | PRI | NULL    |       |
| gender | varchar(20) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [employees]> INSERT INTO gender (code, gender)
     VALUES
     ('F', 'FEMALE'),
     ('M', 'MALE');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [employees]> select * from gender;
+------+--------+
| code | gender |
+------+--------+
| F    | FEMALE |
| M    | MALE   |
+------+--------+
2 rows in set (0.00 sec)

MariaDB [employees]> INSERT INTO gender SET code='U', gender='UNSIGNED';
Query OK, 1 row affected (0.04 sec)

MariaDB [employees]> select * from gender;
+------+----------+
| code | gender   |
+------+----------+
| F    | FEMALE   |
| M    | MALE     |
| U    | UNSIGNED |
+------+----------+
3 rows in set (0.00 sec)

MariaDB [employees]> UPDATE gender SET gender='UNASSIGNED' WHERE code='U';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [employees]> select * from gender;
+------+------------+
| code | gender     |
+------+------------+
| F    | FEMALE     |
| M    | MALE       |
| U    | UNASSIGNED |
+------+------------+
3 rows in set (0.00 sec)

MariaDB [employees]> DELETE FROM gender WHERE code='U';
Query OK, 1 row affected (0.04 sec)

MariaDB [employees]> select * from gender;
+------+--------+
| code | gender |
+------+--------+
| F    | FEMALE |
| M    | MALE   |
+------+--------+
2 rows in set (0.00 sec)

MariaDB [employees]> ALTER TABLE gender ADD COLUMN gender_fr VARCHAR(20) AFTER gender;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [employees]> UPDATE gender SET gender_fr='MASCULIN' WHERE code='M';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [employees]> UPDATE gender SET gender_fr='FMININ' WHERE code='F';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [employees]> ALTER TABLE gender CHANGE COLUMN gender gender_en VARCHAR(20);
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [employees]> select * from gender;
+------+-----------+-----------+
| code | gender_en | gender_fr |
+------+-----------+-----------+
| F    | FEMALE    | FÉMININ   |
| M    | MALE      | MASCULIN  |
+------+-----------+-----------+
2 rows in set (0.00 sec)

MariaDB [employees]> create table female_employees
     select * from employees where gender='F';
Query OK, 120051 rows affected (3.22 sec)
Records: 120051  Duplicates: 0  Warnings: 0

MariaDB [employees]> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MariaDB [employees]> describe female_employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   |     | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MariaDB [employees]> CREATE VIEW male_employees AS
     select * from employees where gender='M';
Query OK, 0 rows affected (0.05 sec)

MariaDB [employees]> select * from male_employees limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

MariaDB [employees]> CREATE TABLE employees_new (
     emp_no int(11) NOT NULL,
     birth_date date NOT NULL,
     first_name varchar(14) NOT NULL,
     last_name varchar(16) NOT NULL,
     gender CHAR(1),
     hire_date date NOT NULL,
     PRIMARY KEY (emp_no)
     ) select
     emp_no, birth_date, first_name, last_name,
     IF(gender = 'M', 'M', 'F') as gender,
     hire_date from employees;

Query OK, 300024 rows affected (5.92 sec)
Records: 300024  Duplicates: 0  Warnings: 0

MariaDB [employees]> describe employees_new;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no     | int(11)     | NO   | PRI | NULL    |       |
| birth_date | date        | NO   |     | NULL    |       |
| first_name | varchar(14) | NO   |     | NULL    |       |
| last_name  | varchar(16) | NO   |     | NULL    |       |
| gender     | char(1)     | YES  |     | NULL    |       |
| hire_date  | date        | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MariaDB [employees]> select e.emp_no, e.first_name, e.last_name, e.gender,
     g.gender_en, g.gender_fr
     from employees_new e join gender g on e.gender=g.code
     limit 5;
+--------+------------+-----------+--------+-----------+-----------+
| emp_no | first_name | last_name | gender | gender_en | gender_fr |
+--------+------------+-----------+--------+-----------+-----------+
|  10001 | Georgi     | Facello   | M      | MALE      | MASCULIN  |
|  10002 | Bezalel    | Simmel    | F      | FEMALE    | FÉMININ   |
|  10003 | Parto      | Bamford   | M      | MALE      | MASCULIN  |
|  10004 | Chirstian  | Koblick   | M      | MALE      | MASCULIN  |
|  10005 | Kyoichi    | Maliniak  | M      | MALE      | MASCULIN  |
+--------+------------+-----------+--------+-----------+-----------+
5 rows in set (0.00 sec)

MariaDB [employees]> ALTER TABLE female_employees DROP COLUMN gender;
Query OK, 0 rows affected (4.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [employees]> describe female_employees;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no     | int(11)     | NO   |     | NULL    |       |
| birth_date | date        | NO   |     | NULL    |       |
| first_name | varchar(14) | NO   |     | NULL    |       |
| last_name  | varchar(16) | NO   |     | NULL    |       |
| hire_date  | date        | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

MariaDB [employees]> drop table female_employees;
Query OK, 0 rows affected (0.14 sec)

MariaDB [employees]> drop view male_employees;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> drop database employees;
Query OK, 10 rows affected (0.92 sec)