Simple MySQL and SQL exercises_MySQL

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 232013 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 -pmysql> 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  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 employeesLIMIT 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 officesLIMIT 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 owhere 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