SQL_Exercise1

Part-1

desc tastes;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(40) | YES  |     | NULL    |       |
| filling | varchar(40) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

 desc locations;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| lname   | varchar(40) | NO   | PRI |         |       |
| phone   | varchar(10) | YES  |     | NULL    |       |
| address | varchar(60) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

desc sandwiches;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| location | varchar(40)  | YES  |     | NULL    |       |
| bread    | varchar(40)  | YES  |     | NULL    |       |
| filling  | varchar(40)  | YES  |     | NULL    |       |
| price    | decimal(3,2) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+


1. select address from locations where lname in(select location from sandwiches where filling in(select filling from tastes where name="Jones"));

2. select address from locations, sandwiches, tastes where locations.lname=sandwiches.location and tastes.filling=sandwiches.filling and name='Jones';

3. select location, count(distinct name) from tastes inner join sandwiches on tastes.filling=sandwiches.filling group by location;


Part-2

desc branch;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bcode     | varchar(10) | YES  |     | NULL    |       |
| librarian | varchar(30) | YES  |     | NULL    |       |
| address   | varchar(40) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

desc titles;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| title     | varchar(30) | YES  |     | NULL    |       |
| author    | varchar(20) | YES  |     | NULL    |       |
| publisher | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

desc holdings;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| branch | varchar(10) | YES  |     | NULL    |       |
| title  | varchar(20) | YES  |     | NULL    |       |
| copies | int(2)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

1. select title from titles where publisher='Macmillan';

2. select branch from holdings where title in( select title  from titles where author='Ann Brown');

3. select branch from holdings inner join titles on holdings.title=titles.title where author='Ann Brown';

4. select branch, sum(copies) from holdings group by branch;