/* Creation of tables for part2*/

mysql> create database exercise1_2;
Query OK, 1 row affected (0.00 sec)

mysql> use exercise1_2;
Database changed
mysql> create table tastes
    -> (
    -> name char(15) not null,
    -> filling char(15) not null
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> select * from tastes;
+-------+---------+
| name  | filling |
+-------+---------+
| Brown | Turkey  |
| Brown | Beef    |
| Brown | Ham     |
| Jones | Cheese  |
| Green | Beef    |
| Green | Turkey  |
| Green | Cheese  |
+-------+---------+
7 rows in set (0.00 sec)

mysql> create table locations
    -> (
    -> id int not null auto_increment,
    -> LName varchar(20) not null,
    -> Phone int(11) not null,
    -> Address varchar(30) not null,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> select * from locations;
+----+-----------+---------+---------------+
| id | LName     | Phone   | Address       |
+----+-----------+---------+---------------+
|  1 | Lincoln   | 6834523 | Lincoln Place |
|  2 | O'Neill's | 6742134 | Pearse St     |
|  3 | Old Nag   | 7678132 | Dame St       |
|  4 | Buttery   | 7023421 | College St    |
+----+-----------+---------+---------------+
4 rows in set (0.00 sec)

mysql> create table sandwiches
    -> (
    -> Location varchar(20) not null,
    -> Bread char(10) not null,
    -> Filling char(15) not null,
    -> Price double not null
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> select * from sandwiches;
+-----------+-------+---------+-------+
| Location  | Bread | Filling | Price |
+-----------+-------+---------+-------+
| Lincoln   | rye   | ham     |  1.25 |
| O'Neill's | white | cheese  |   1.2 |
| O'Neill's | whole | ham     |  1.25 |
| Old Nag   | rye   | beef    |  1.35 |
| Buttery   | white | cheese  |     1 |
| O'Neill's | white | turkey  |  1.35 |
| Buttery   | white | ham     |   1.1 |
| Lincoln   | rye   | beef    |  1.35 |
| Lincoln   | white | ham     |   1.3 |
| Old Nag   | rye   | ham     |   1.4 |
+-----------+-------+---------+-------+
10 rows in set (0.00 sec)

/*Query 2.2 solution*/
mysql> select sandwiches.location
    -> from tastes left join sandwiches
    -> on tastes.filling = sandwiches.filling
    -> where tastes.name = 'jones';
+-----------+
| location  |
+-----------+
| O'Neill's |
| Buttery   |
+-----------+
2 rows in set (0.00 sec)

mysql> alter table tastes add constraint pk_tasteID PRIMARY KEY(name,filling);
Query OK, 7 rows affected (0.23 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> alter table sandwiches add constraint pk_sandID PRIMARY KEY(location,bread,filling);
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> alter table locations drop PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> alter table locations drop column id;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from locations;
+-----------+---------+---------------+
| LName     | Phone   | Address       |
+-----------+---------+---------------+
| Lincoln   | 6834523 | Lincoln Place |
| O'Neill's | 6742134 | Pearse St     |
| Old Nag   | 7678132 | Dame St       |
| Buttery   | 7023421 | College St    |
+-----------+---------+---------------+
4 rows in set (0.00 sec)

mysql> alter table locations add PRIMARY KEY(lname);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0
/* Query 2.1 solution*/
mysql> select location from sandwiches where filling in(select filling from tastes where name='jones';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select location from sandwiches where filling in(select filling from tastes where name='jones');
+-----------+
| location  |
+-----------+
| Buttery   |
| O'Neill's |
+-----------+
2 rows in set (0.00 sec)

/*Query 2.3 solution */
mysql> select sandwiches.location, count(distinct name) from tastes inner join sandwiches on tastes.filling = sandwiches.filling group by sandwiches.location;
+-----------+----------------------+
| location  | count(distinct name) |
+-----------+----------------------+
| Buttery   |                    3 |
| Lincoln   |                    2 |
| O'Neill's |                    3 |
| Old Nag   |                    2 |
+-----------+----------------------+
4 rows in set (0.05 sec)

/* Creation of database and tables for part 3 */

mysql> create database exercise1_3;
Query OK, 1 row affected (0.00 sec)

mysql> use exercise1_3;
Database changed
mysql> create table Branches
    -> (
    -> bcode varchar(5) not null,
    -> librarian varchar(30) not null,
    -> address varchar(30) not null,
    -> PRIMARY KEY(bcode)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> create table titles
    -> (
    -> title char(20) not null Unique,
    -> author char(20) not null,
    -> publisher char(20) not null,
    -> PRIMARY KEY(title)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> create table holdings
    -> (
    -> branch varchar(5) not null,
    -> title char(20) not null,
    -> #copies int(10) not null,
    -> constraint pk_holdID PRIMARY KEY(branch,title),
    -> FOREIGN KEY(branch) REFERENCES branches(bcode),
    -> FOREIGN KEY(title) REFERENCES titles(title)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> select * from branches;
+-------+---------------+---------------+
| bcode | librarian     | address       |
+-------+---------------+---------------+
| B1    | John Smith    | 2 anglesea rd |
| B2    | Mary Jones    | 34 pearse st  |
| B3    | Francis Owens | grange X      |
+-------+---------------+---------------+
3 rows in set (0.00 sec)

mysql> select * from titles;
+---------------------+---------------+------------+
| title               | author        | publisher  |
+---------------------+---------------+------------+
| Susannah            | Ann Brown     | macmillian |
| How to fish         | Amy Fly       | stop press |
| A history of dublin | David Little  | wiley      |
| Computers           | Blaise Pascal | applewoods |
| The Wife            | Ann Brown     | macmillian |
+---------------------+---------------+------------+
5 rows in set (0.01 sec)

mysql> select * from holdings;
+--------+---------------------+---------+
| branch | title               | #copies |
+--------+---------------------+---------+
| B1     | Susannah            |       3 |
| B1     | How to fish         |       2 |
| B1     | A history of Dublin |       1 |
| B2     | how to fish         |       4 |
| B2     | Computers           |       2 |
| B2     | The wife            |       3 |
| B3     | A history of dublin |       1 |
| B3     | Computers           |       4 |
| B3     | Susannah            |       3 |
| B3     | The Wife            |       1 |
+--------+---------------------+---------+
10 rows in set (0.00 sec)

/* Query 3.1 solution*/
mysql> select title from titles where publisher = 'macmillian';
+----------+
| title    |
+----------+
| Susannah |
| The Wife |
+----------+
2 rows in set (0.00 sec)

/*Query 3.3 solution*/
mysql> select distinct holdings.branch
    -> from titles left join holdings
    -> on titles.title = holdings.title
    -> where titles.author = 'ann brown';
+--------+
| branch |
+--------+
| B1     |
| B3     |
| B2     |
+--------+
3 rows in set (0.00 sec)

/*Query 3.4 solution*/
mysql> select branch, count(branch)
    -> from holdings
    -> group by branch;
+--------+---------------+
| branch | count(branch) |
+--------+---------------+
| B1     |             3 |
| B2     |             3 |
| B3     |             4 |
+--------+---------------+
3 rows in set (0.00 sec)

/*Query 3.2 solution*/

mysql> select distinct(branch) from holdings where title in(select title from titles where author = 'ann brown');
+--------+
| branch |
+--------+
| B1     |
| B2     |
| B3     |
+--------+
3 rows in set (0.00 sec)

/*Query 3.4 redone*/
mysql> select branch, sum(copies) from holdings group by branch;
+--------+-------------+
| branch | sum(copies) |
+--------+-------------+
| B1     |           6 |
| B2     |           9 |
| B3     |           9 |
+--------+-------------+
3 rows in set (0.00 sec)