Linda's Web Technologies

I'm uploading the details of how I completed our first SQL exercise.  It makes for some fairly boring reading, but I may have to refer back to it someday and will be glad I made it available here. 


## I used tee c:\database\store.txt  to create a log file of my session on SQL ##
## I have 3 tables in my database: one is called supplier ##
## each table has a primary key ##
## supplier primary key is comp_code ##
## employee primary key is emp_id ##
## store primary key is st_code ##


mysql> describe supplier;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| fname     | varchar(15) | YES  |     | NULL    |       |
| lname     | varchar(15) | YES  |     | NULL    |       |
| price     | int(3)      | YES  |     | NULL    |       |
| product   | varchar(20) | YES  |     | NULL    |       |
| comp_code | varchar(6)  | NO   | PRI |         |       |
| ph_num    | int(15)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
## I populated the table supplier using the following syntax ##
Query OK, 1 row affected (0.00 sec)
## Once I had the table populated I checked to see that it looked ok by doing the following ##
mysql> select * from supplier;
+----------+--------------+-------+------------+-----------+-----------+
| fname    | lname        | price | product    | comp_code | ph_num    |
+----------+--------------+-------+------------+-----------+-----------+
| tony     | brereton     |    87 | champagne  | cg 059    | 869764935 |
| angela   | mcloughlin   |     8 | irishtimes | it 024    | 869865935 |
| linda    | keating      |    20 | cornflakes | kg 010    | 878305835 |
| michelle | stapleton    |    28 | merlot     | mt 049    | 868438935 |
| eoghan   | mac g bhride |    10 | sugarpops  | sp 011    | 879805835 |
| hugh     | corcoran     |     7 | sweets     | sw 039    | 867508935 |
| sinead   | mcbride      |    18 | toiletroll | tr 019    | 879808935 |
+----------+--------------+-------+------------+-----------+-----------+
7 rows in set (0.00 sec)
## then I wanted to fill my employee table so i checked what order the fields were in..##
mysql> describe employee;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| fname     | varchar(15) | YES  |     | NULL    |       |
| lname     | varchar(15) | YES  |     | NULL    |       |
| salary    | int(4)      | YES  |     | NULL    |       |
| job_title | varchar(20) | YES  |     | NULL    |       |
| st_code   | varchar(60) | YES  |     | NULL    |       |
| ph_num    | int(15)     | YES  |     | NULL    |       |
| emp_id    | varchar(6)  | NO   | PRI |         |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

## then i started populating it by doing the following ##

mysql> insert into employee values ('Mary','Murray',30000,'assistant manager','db 001', 0869865935, 'am 001');
Query OK, 1 row affected (0.02 sec)

## and again I checked that all the table had been filled in properly by using the selec * from employee##

mysql> select * from employee;
+---------+-----------+--------+-------------------+---------+-----------+--------+
| fname   | lname     | salary | job_title         | st_code | ph_num    | emp_id |
+---------+-----------+--------+-------------------+---------+-----------+--------+
| Mary    | Murray    |  30000 | assistant manager | db 001  | 869865935 | am 001 |
| Kate    | Conkey    |  25000 | Assistnt Manager  | db 003  | 879993935 | am 006 |
| henry   | oreilly   |  15000 | cleaner           | db 001  | 879655935 | cl 001 |
| Lizzie  | keating   |  15000 | checkout          | db 050  | 877655935 | co 001 |
| Mick    | Lally     |  18000 | checkout          | db 001  | 878989935 | co 002 |
| Siobhan | Jennings  |  19000 | checkout          | db 003  | 877643935 | co 006 |
| Sarah   | Wilkinson |  50000 | manager           | db 001  | 879755935 | ma 001 |
| Patrick | Monaghan  |  31000 | Manager           | db 003  | 877643935 | ma 006 |
+---------+-----------+--------+-------------------+---------+-----------+--------+
8 rows in set (0.00 sec)

## I then completed the database by filling out the store table first by checking it's structure##

mysql> describe store;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(15) | YES  |     | NULL    |       |
| st_code  | varchar(6)  | NO   | PRI |         |       |
| ph_num   | int(15)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

## and then by filling it in ##

mysql> insert into store values ('Finglas','db 001',018883935);
Query OK, 1 row affected (0.00 sec)

## the next thing I wanted to do was find all the managers in all the stores ##

mysql> select fname, lname from employee where job_title like "man%";
+---------+-----------+
| fname   | lname     |
+---------+-----------+
| Sarah   | Wilkinson |
| Patrick | Monaghan  |
+---------+-----------+
2 rows in set (0.06 sec)

## next question was to return all details from supplier ##

mysql> select * from supplier;
+----------+--------------+-------+------------+-----------+-----------+
| fname    | lname        | price | product    | comp_code | ph_num    |
+----------+--------------+-------+------------+-----------+-----------+
| tony     | brereton     |    87 | champagne  | cg 059    | 869764935 |
| angela   | mcloughlin   |     8 | irishtimes | it 024    | 869865935 |
| linda    | keating      |    20 | cornflakes | kg 010    | 878305835 |
| michelle | stapleton    |    28 | merlot     | mt 049    | 868438935 |
| eoghan   | mac g bhride |    10 | sugarpops  | sp 011    | 879805835 |
| hugh     | corcoran     |     7 | sweets     | sw 039    | 867508935 |
| sinead   | mcbride      |    18 | toiletroll | tr 019    | 879808935 |
+----------+--------------+-------+------------+-----------+-----------+
7 rows in set (0.00 sec)

## then return the products that were less than €10 ##

mysql> select product from supplier where price < 10;
+------------+
| product    |
+------------+
| irishtimes |
| sweets     |
+------------+
2 rows in set (0.05 sec)

## the next question in the exercise was to return all the products that began with A ##
## but as I didn't have any products beginning with a I decided to use c instead ##

mysql> select product from supplier where product like ('c%');
+------------+
| product    |
+------------+
| champagne  |
| cornflakes |
+------------+
2 rows in set (0.03 sec)

## next part of exercise was to return all the details of the employees ##

mysql> select * from employee;
+---------+-----------+--------+-------------------+---------+-----------+--------+
| fname   | lname     | salary | job_title         | st_code | ph_num    | emp_id |
+---------+-----------+--------+-------------------+---------+-----------+--------+
| Mary    | Murray    |  30000 | assistant manager | db 001  | 869865935 | am 001 |
| Kate    | Conkey    |  25000 | Assistnt Manager  | db 003  | 879993935 | am 006 |
| henry   | oreilly   |  15000 | cleaner           | db 001  | 879655935 | cl 001 |
| Kate    | Murray    |  18000 | cleaner           | db 003  | 878883935 | cl 002 |
| Lizzie  | keating   |  15000 | checkout          | db 050  | 877655935 | co 001 |
| Mick    | Lally     |  18000 | checkout          | db 001  | 878989935 | co 002 |
| Siobhan | Jennings  |  19000 | checkout          | db 003  | 877643935 | co 006 |
| Sarah   | Wilkinson |  50000 | manager           | db 001  | 879755935 | ma 001 |
| Patrick | Monaghan  |  31000 | Manager           | db 003  | 877643935 | ma 006 |
+---------+-----------+--------+-------------------+---------+-----------+--------+
9 rows in set (0.00 sec)

## and then find out which employees are paid between €10K - €20 K ##

mysql> select fname,lname from employee where salary between 10000 and 20000;
+---------+----------+
| fname   | lname    |
+---------+----------+
| henry   | oreilly  |
| Kate    | Murray   |
| Lizzie  | keating  |
| Mick    | Lally    |
| Siobhan | Jennings |
+---------+----------+
5 rows in set (0.03 sec)

## next task display all store details ##

mysql> select * from store;
+------------+---------+----------+
| location   | st_code | ph_num   |
+------------+---------+----------+
| Finglas    | db 001  | 18883935 |
| Drumcondra | db 003  | 17673935 |
| Kilmainham | db 050  | 18483935 |
+------------+---------+----------+
3 rows in set (0.00 sec)

## then return all locations that contain the string "in" ##

mysql> select location, st_code, ph_num from store where location like ('%in%');
+------------+---------+----------+
| location   | st_code | ph_num   |
+------------+---------+----------+
| Finglas    | db 001  | 18883935 |
| Kilmainham | db 050  | 18483935 |
+------------+---------+----------+
2 rows in set (0.00 sec)

## and that's it!! I really hope that nobody has read this far through what is possibly one of the most boring posts ever ##