# if you're installing this on your own computer, un-comment and run the following lines, so that you place the tables into their own schema drop schema if exists Assignment2; create schema Assignment2; use Assignment2;

SET foreign_key_checks = 0;

drop table if exists Supplier; drop table if exists Delivery; Drop table if exists Item; drop table if exists Department; drop table if exists Employee; drop table if exists Sale;

CREATE TABLE Item ( ItemID SMALLINT, ItemName VARCHAR(50) NOT NULL, ItemType CHAR(1), ItemColour VARCHAR(20), PRIMARY KEY (ItemID) );

CREATE TABLE Employee ( EmployeeID SMALLINT, EmployeeName VARCHAR(50), EmployeeSalary DECIMAL(8,2), DepartmentID SMALLINT, BossID SMALLINT, PRIMARY KEY (EmployeeID), FOREIGN KEY (BossID) references Employee(EmployeeID), FOREIGN KEY (DepartmentID) references Department(DepartmentID) );

CREATE TABLE Department ( DepartmentID SMALLINT, DepartmentName VARCHAR(50) NOT NULL, DepartmentFloor INTEGER, DepartmentPhone INTEGER, ManagerID SMALLINT NOT NULL, PRIMARY KEY (DepartmentID), FOREIGN KEY (ManagerID) references Employee(EmployeeID) );

CREATE TABLE Sale ( SaleID INTEGER NOT NULL, SaleQTY INTEGER, ItemID SMALLINT NOT NULL, DepartmentID SMALLINT NOT NULL, PRIMARY KEY (SaleID), FOREIGN KEY (ItemID) references Item(ItemID), FOREIGN KEY (DepartmentID) references Department(DepartmentID) );

CREATE TABLE Supplier ( SupplierID SMALLINT NOT NULL, SupplierName VARCHAR(25), SupplierPhone VARCHAR(16), PRIMARY KEY (SupplierID) );

CREATE TABLE Delivery ( DeliveryID INTEGER NOT NULL, DeliveryQTY INTEGER NOT NULL, ItemID SMALLINT NOT NULL, DepartmentID SMALLINT NOT NULL, SupplierID SMALLINT NOT NULL, PRIMARY KEY (DeliveryID), FOREIGN KEY (ItemID) references Item(ItemID), FOREIGN KEY (DepartmentID) references Department(DepartmentID), FOREIGN KEY (SupplierID) references Supplier(SupplierID) );

5. Type the query to find the average salary of employees in the Clothes department 6. List the name and salary of the managers with more than 2 employees 7. Find the supplier id and supplier names that deliver compasses 8. Write and type the query that finds the name and salary of Clare's manager 9. Whom does Todd manage? 10. Find the departments that have never sold a geo positioning system 11. Find, for each item, the total quantity sold by the departments on the second floor 12. Who earns the lowest salary? 13. Find, for each department on the second floor, the average salary of the employees 14. Find the first name of Sophie's boss. 15. Find bosses who are in the same department as their employees