mysql> select * from countries;

+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
| AR | Argentina | 2 |
| AU | Australia | 3 |
| BE | Belgium | 1 |
| BR | Brazil | 2 |
| CA | Canada | 2 |
| CH | Switzerland | 1 |
| CN | China | 3 |
| DE | Germany | 1 |
| DK | Denmark | 1 |
| EG | Egypt | 4 |
+------------+--------------+-----------+
10 rows in set (0.00 sec)

mysql> select * from locations;

+-------------+-------------------------+-------------+---------------------
+------------------+------------+
| LOCATION_ID | STREET_ADDRESS | POSTAL_CODE | CITY |
STATE_PROVINCE | COUNTRY_ID |
+-------------+-------------------------+-------------+---------------------
+------------------+------------+
| 1000 | 1297 Via Cola di Rie | 989 | Roma |
| IT |
| 1100 | 93091 Calle della Testa | 10934 | Venice |
| IT |
| 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo
Prefecture | JP |
| 1300 | 9450 Kamiya-cho | 6823 | Hiroshima |
| JP |
| 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas
| US |
| 1500 | 2011 Interiors Blvd | 99236 | South San Francisco |
California | US |
| 1600 | 2007 Zagora St | 50090 | South Brunswick | New
Jersey | US |
| 1700 | 2004 Charade Rd | 98199 | Seattle |
Washington | US |
| 1800 | 147 Spadina Ave | M5V 2L7 | Toronto |
Ontario | CA |
| 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon
| CA |
+-------------+-------------------------+-------------+---------------------
+------------------+------------+
10 rows in set (0.00 sec)

mysql> select * from departments;

+---------------+------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID |
+---------------+------------------+------------+-------------+
| 10 | Administration | 200 | 1700 |
| 20 | Marketing | 201 | 1800 |
| 30 | Purchasing | 114 | 1700 |
| 40 | Human Resources | 203 | 2400 |
| 50 | Shipping | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 70 | Public Relations | 204 | 2700 |
| 80 | Sales | 145 | 2500 |
| 90 | Executive | 100 | 1700 |
| 100 | Finance | 108 | 1700 |
+---------------+------------------+------------+-------------+
10 rows in set (0.00 sec)

mysql> select * from employees;

+-------------+------------+-----------+----------+--------------+------------
+---------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE |
JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+------------+-----------+----------+--------------+------------
+---------+----------+----------------+------------+---------------+
| 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 |
AD_PRES | 24000.00 | 0.00 | 0 | 90 |
| 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 |
AD_VP | 17000.00 | 0.00 | 100 | 90 |
| 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 |
AD_VP | 17000.00 | 0.00 | 100 | 90 |
| 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 |
IT_PROG | 9000.00 | 0.00 | 102 | 60 |
| 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 |
IT_PROG | 6000.00 | 0.00 | 103 | 60 |
| 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 |
IT_PROG | 4800.00 | 0.00 | 103 | 60 |
| 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 |
IT_PROG | 4800.00 | 0.00 | 103 | 60 |
| 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 |
IT_PROG | 4200.00 | 0.00 | 103 | 60 |
+-------------+------------+-----------+----------+--------------+------------
+---------+----------+----------------+------------+---------------+
8 rows in set (0.00 sec)

mysql> select * from jobs;

+------------+-------------------------------+------------+------------+
| JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY |
+------------+-------------------------------+------------+------------+
| AD_PRES | President | 20000 | 40000 |
| AD_VP | Administration Vice President | 15000 | 30000 |
| AD_ASST | Administration Assistant | 3000 | 6000 |
| FI_MGR | Finance Manager | 8200 | 16000 |
| FI_ACCOUNT | Accountant | 4200 | 9000 |
| AC_MGR | Accounting Manager | 8200 | 16000 |
| AC_ACCOUNT | Public Accountant | 4200 | 9000 |
| SA_MAN | Sales Manager | 10000 | 20000 |
| SA_REP | Sales Representative | 6000 | 12000 |
| PU_MAN | Purchasing Manager | 8000 | 15000 |
+------------+-------------------------------+------------+------------+
10 rows in set (0.00 sec)

mysql> select * from job_history;

+-------------+------------+------------+------------+---------------+
| EMPLOYEE_ID | START_DATE | END_DATE | JOB_ID | DEPARTMENT_ID |
+-------------+------------+------------+------------+---------------+
| 102 | 1993-01-13 | 1998-07-24 | IT_PROG | 60 |
| 101 | 1989-09-21 | 1993-10-27 | AC_ACCOUNT | 110 |
| 101 | 1993-10-28 | 1997-03-15 | AC_MGR | 110 |
| 201 | 1996-02-17 | 1999-12-19 | MK_REP | 20 |
| 114 | 1998-03-24 | 1999-12-31 | ST_CLERK | 50 |
| 122 | 1999-01-01 | 1999-12-31 | ST_CLERK | 50 |
| 200 | 1987-09-17 | 1993-06-17 | AD_ASST | 90 |
| 176 | 1998-03-24 | 1998-12-31 | SA_REP | 80 |
| 176 | 1999-01-01 | 1999-12-31 | SA_MAN | 80 |
| 200 | 1994-07-01 | 1998-12-31 | AC_ACCOUNT | 90 |
+-------------+------------+------------+------------+---------------+
10 rows in set (0.00 sec)

mysql> SELECT location_id, street_address, city, state_province, country_name

-> FROM locations
-> NATURAL JOIN countries;
+-------------+-----------------+------------+----------------+--------------+
| location_id | street_address | city | state_province | country_name |
+-------------+-----------------+------------+----------------+--------------+
| 1800 | 147 Spadina Ave | Toronto | Ontario | Canada |
| 1900 | 6092 Boxwood St | Whitehorse | Yukon | Canada |
+-------------+-----------------+------------+----------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT first_name, last_name, department_id, department_name

-> FROM employees
-> JOIN departments USING (department_id);
+------------+-----------+---------------+-----------------+
| first_name | last_name | department_id | department_name |
+------------+-----------+---------------+-----------------+
| Steven | King | 90 | Executive |
| Neena | Kochhar | 90 | Executive |
| Lex | De Haan | 90 | Executive |
| Alexander | Hunold | 60 | IT |
| Bruce | Ernst | 60 | IT |
| David | Austin | 60 | IT |
| Valli | Pataballa | 60 | IT |
| Diana | Lorentz | 60 | IT |
+------------+-----------+---------------+-----------------+
8 rows in set (0.00 sec)

mysql> SELECT e.first_name, e.last_name, e.job_id, e.department_id,

d.department_name
-> FROM employees e
-> JOIN departments d
-> ON (e.department_id = d.department_id)
-> JOIN locations l ON
-> (d.location_id = l.location_id)
-> WHERE LOWER(l.city) = 'London';
Empty set (0.00 sec)

mysql> SELECT e.employee_id 'Emp_Id', e.last_name 'Employee',

-> m.employee_id 'Mgr_Id', m.last_name 'Manager'
-> FROM employees e
-> join employees m
-> ON (e.manager_id = m.employee_id);
+--------+-----------+--------+---------+
| Emp_Id | Employee | Mgr_Id | Manager |
+--------+-----------+--------+---------+
| 101 | Kochhar | 100 | King |
| 102 | De Haan | 100 | King |
| 103 | Hunold | 102 | De Haan |
| 104 | Ernst | 103 | Hunold |
| 105 | Austin | 103 | Hunold |
| 106 | Pataballa | 103 | Hunold |
| 107 | Lorentz | 103 | Hunold |
+--------+-----------+--------+---------+
7 rows in set (0.00 sec)

mysql> SELECT e.first_name, e.last_name, e.hire_date

-> FROM employees e
-> JOIN employees davies
-> ON (davies.last_name = 'Jones')
-> WHERE davies.hire_date < e.hire_date;
Empty set (0.00 sec)

mysql> SELECT department_name AS 'Department Name',

-> COUNT(*) AS 'No of Employees'
-> FROM departments
-> INNER JOIN employees
-> ON employees.department_id = departments.department_id
-> GROUP BY departments.department_id, department_name
-> ORDER BY department_name;
+-----------------+-----------------+
| Department Name | No of Employees |
+-----------------+-----------------+
| Executive | 3 |
| IT | 5 |
+-----------------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT employee_id, job_title, end_date-start_date Days FROM job_history

-> NATURAL JOIN jobs
-> WHERE department_id=90;
+-------------+--------------------------+-------+
| employee_id | job_title | Days |
+-------------+--------------------------+-------+
| 200 | Administration Assistant | 59700 |
| 200 | Public Accountant | 40530 |
+-------------+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT d.department_id, d.department_name, d.manager_id, e.first_name

-> FROM departments d
-> INNER JOIN employees e
-> ON (d.manager_id = e.employee_id);
+---------------+-----------------+------------+------------+
| department_id | department_name | manager_id | first_name |
+---------------+-----------------+------------+------------+
| 60 | IT | 103 | Alexander |
| 90 | Executive | 100 | Steven |
+---------------+-----------------+------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT d.department_name, e.first_name, l.city

-> FROM departments d
 -> JOIN employees e
-> ON (d.manager_id = e.employee_id)
-> JOIN locations l USING (location_id);
+-----------------+------------+-----------+
| department_name | first_name | city |
+-----------------+------------+-----------+
| IT | Alexander | Southlake |
| Executive | Steven | Seattle |
+-----------------+------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT job_title, first_name, salary-min_salary 'Salary - Min_Salary'

-> FROM employees
-> NATURAL JOIN jobs;
+-------------------------------+------------+---------------------+
| job_title | first_name | Salary - Min_Salary |
+-------------------------------+------------+---------------------+
| President | Steven | 4000.00 |
| Administration Vice President | Neena | 2000.00 |
| Administration Vice President | Lex | 2000.00 |
+-------------------------------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT jh.* FROM job_history jh

-> JOIN employees e
-> ON (jh.employee_id = e.employee_id)
-> WHERE salary > 10000;
+-------------+------------+------------+------------+---------------+
| EMPLOYEE_ID | START_DATE | END_DATE | JOB_ID | DEPARTMENT_ID |
+-------------+------------+------------+------------+---------------+
| 101 | 1989-09-21 | 1993-10-27 | AC_ACCOUNT | 110 |
| 101 | 1993-10-28 | 1997-03-15 | AC_MGR | 110 |
| 102 | 1993-01-13 | 1998-07-24 | IT_PROG | 60 |
+-------------+------------+------------+------------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT first_name, last_name, hire_date, salary,

-> (DATEDIFF(now(), hire_date))/365 Experience
-> FROM departments d JOIN employees e
-> ON (d.manager_id = e.employee_id)
-> WHERE (DATEDIFF(now(), hire_date))/365>15;
+------------+-----------+------------+----------+------------+
| first_name | last_name | hire_date | salary | Experience |
+------------+-----------+------------+----------+------------+
| Steven | King | 1987-06-17 | 24000.00 | 31.7233 |
| Alexander | Hunold | 1987-06-20 | 9000.00 | 31.7151 |
+------------+-----------+------------+----------+------------+
2 rows in set (0.00 sec)

mysql> SELECT job_title, AVG(salary)

-> FROM employees
-> NATURAL JOIN jobs
-> GROUP BY job_title;
+-------------------------------+--------------+
| job_title | AVG(salary) |
+-------------------------------+--------------+
| Administration Vice President | 17000.000000 |
| President | 24000.000000 |
+-------------------------------+--------------+
2 rows in set (0.00 sec)

Database changed
mysql> SELECT jh.* FROM job_history jh
-> JOIN employees e
-> ON (jh.employee_id = e.employee_id)
-> WHERE salary > 10000;
+-------------+------------+------------+------------+---------------+
| EMPLOYEE_ID | START_DATE | END_DATE | JOB_ID | DEPARTMENT_ID |
+-------------+------------+------------+------------+---------------+
| 101 | 1989-09-21 | 1993-10-27 | AC_ACCOUNT | 110 |
| 101 | 1993-10-28 | 1997-03-15 | AC_MGR | 110 |
| 102 | 1993-01-13 | 1998-07-24 | IT_PROG | 60 |
+-------------+------------+------------+------------+---------------+
3 rows in set (0.00 sec)