Simple MySQL and SQL exercises

How to create a sample MySQL data base and user

You can download an example data base sql file from here: http://www.mysqltutorial.org/mysql-sample-database.aspx. After unziping you should find following file:

 
rado2@ubuntu12-04:~$ ls -la mysqlsampledatabase.sql
-rw-rw-r-- 1 rado2 rado2 190711 May 23  2013 mysqlsampledatabase.sql

 
rado2@ubuntu12-04:~$ more mysqlsampledatabase.sql
/*
http://www.mysqltutorial.org
*/

CREATE DATABASE /*!32312 IF NOT EXISTS*/`classicmodels` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `classicmodels`;

/*Table structure for table `customers` */

DROP TABLE IF EXISTS `customers`;

CREATE TABLE `customers` (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
....

We don't want to use a root user to manipulate our data base records. To create a separate user you can run these commands:

 
$ mysql -u root -p
mysql> use information_schema;
mysql> CREATE USER 'rado2'@'localhost';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'rado2'@'localhost';
mysql> select * from USER_PRIVILEGES ;

To import and inspect the database we can use this commands:

 
$ mysql -u rado2 < mysqlsampledatabase.sql

$ mysql -u rado2
mysql> show databases;
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)

mysql> select * from employees  LIMIT 5;
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
| employeeNumber | lastName  | firstName | extension | email                           | officeCode | reportsTo | jobTitle             |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
|           1002 | Murphy    | Diane     | x5800     | dmurphy@classicmodelcars.com    | 1          |      NULL | President            |
|           1056 | Patterson | Mary      | x4611     | mpatterso@classicmodelcars.com  | 1          |      1002 | VP Sales             |
|           1076 | Firrelli  | Jeff      | x9273     | jfirrelli@classicmodelcars.com  | 1          |      1002 | VP Marketing         |
|           1088 | Patterson | William   | x4871     | wpatterson@classicmodelcars.com | 6          |      1056 | Sales Manager (APAC) |
|           1102 | Bondur    | Gerard    | x5408     | gbondur@classicmodelcars.com    | 4          |      1056 | Sale Manager (EMEA)  |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+

mysql> select * from offices  LIMIT 5;
+------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+
| officeCode | city          | phone           | addressLine1             | addressLine2 | state      | country | postalCode | territory |
+------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782 | 100 Market Street        | Suite 300    | CA         | USA     | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825 | 1550 Court Place         | Suite 102    | MA         | USA     | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000 | 523 East 53rd Street     | apt. 5A      | NY         | USA     | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404 | 43 Rue Jouffroy D'abbans | NULL         | NULL       | France  | 75017      | EMEA      |
| 5          | Tokyo         | +81 33 224 5000 | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan   | 102-8578   | Japan     |
+------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+

mysql> show COLUMNS FROM employees
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL    |       |
| lastName       | varchar(50)  | NO   |     | NULL    |       |
| firstName      | varchar(50)  | NO   |     | NULL    |       |
| extension      | varchar(10)  | NO   |     | NULL    |       |
| email          | varchar(100) | NO   |     | NULL    |       |
| officeCode     | varchar(10)  | NO   | MUL | NULL    |       |
| reportsTo      | int(11)      | YES  | MUL | NULL    |       |
| jobTitle       | varchar(50)  | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

mysql> show COLUMNS FROM offices ;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| officeCode   | varchar(10) | NO   | PRI | NULL    |       |
| city         | varchar(50) | NO   |     | NULL    |       |
| phone        | varchar(50) | NO   |     | NULL    |       |
| addressLine1 | varchar(50) | NO   |     | NULL    |       |
| addressLine2 | varchar(50) | YES  |     | NULL    |       |
| state        | varchar(50) | YES  |     | NULL    |       |
| country      | varchar(50) | NO   |     | NULL    |       |
| postalCode   | varchar(15) | NO   |     | NULL    |       |
| territory    | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

Exercise 1: select all employees from offices in USA only

 
mysql> SELECT * FROM employees as e, offices as o  where e.officeCode = o.officeCode and o.country='USA';
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
| employeeNumber | lastName  | firstName | extension | email                           | officeCode | reportsTo | jobTitle           | officeCode | city          | phone           | addressLine1         | addressLine2 | state | country | postalCode | territory |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
|           1002 | Murphy    | Diane     | x5800     | dmurphy@classicmodelcars.com    | 1          |      NULL | President          | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1056 | Patterson | Mary      | x4611     | mpatterso@classicmodelcars.com  | 1          |      1002 | VP Sales           | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1076 | Firrelli  | Jeff      | x9273     | jfirrelli@classicmodelcars.com  | 1          |      1002 | VP Marketing       | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1143 | Bow       | Anthony   | x5428     | abow@classicmodelcars.com       | 1          |      1056 | Sales Manager (NA) | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1165 | Jennings  | Leslie    | x3291     | ljennings@classicmodelcars.com  | 1          |      1143 | Sales Rep          | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1166 | Thompson  | Leslie    | x4065     | lthompson@classicmodelcars.com  | 1          |      1143 | Sales Rep          | 1          | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
|           1188 | Firrelli  | Julie     | x2173     | jfirrelli@classicmodelcars.com  | 2          |      1143 | Sales Rep          | 2          | Boston        | +1 215 837 0825 | 1550 Court Place     | Suite 102    | MA    | USA     | 02107      | NA        |
|           1216 | Patterson | Steve     | x4334     | spatterson@classicmodelcars.com | 2          |      1143 | Sales Rep          | 2          | Boston        | +1 215 837 0825 | 1550 Court Place     | Suite 102    | MA    | USA     | 02107      | NA        |
|           1286 | Tseng     | Foon Yue  | x2248     | ftseng@classicmodelcars.com     | 3          |      1143 | Sales Rep          | 3          | NYC           | +1 212 555 3000 | 523 East 53rd Street | apt. 5A      | NY    | USA     | 10022      | NA        |
|           1323 | Vanauf    | George    | x4102     | gvanauf@classicmodelcars.com    | 3          |      1143 | Sales Rep          | 3          | NYC           | +1 212 555 3000 | 523 East 53rd Street | apt. 5A      | NY    | USA     | 10022      | NA        |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
10 rows in set (0.00 sec)

References

http://www.mysqltutorial.org/mysql-sample-database.aspx
http://en.wikipedia.org/wiki/Join_%28SQL%29
https://answers.yahoo.com/question/index?qid=20080520200936AAmD1Mt
http://www.cyberciti.biz/tips/mysql-auto-completion-for-database-table-names.html