DROP TABLE IF EXISTS `offices`;

CREATE TABLE `offices` ( `officeCode` varchar(10) NOT NULL, `city` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `state` varchar(50) DEFAULT NULL, `country` varchar(50) NOT NULL, `postalCode` varchar(15) NOT NULL, `territory` varchar(10) NOT NULL, PRIMARY KEY (`officeCode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` ( `employeeNumber` int(11) NOT NULL, `lastName` varchar(50) NOT NULL, `firstName` varchar(50) NOT NULL, `extension` varchar(10) NOT NULL, `email` varchar(100) NOT NULL, `officeCode` varchar(10) NOT NULL, `reportsTo` int(11) DEFAULT NULL, `jobTitle` varchar(50) NOT NULL, PRIMARY KEY (`employeeNumber`), KEY `reportsTo` (`reportsTo`), KEY `officeCode` (`officeCode`), CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`), CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into

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, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `city` varchar(50) NOT NULL, `state` varchar(50) DEFAULT NULL, `postalCode` varchar(15) DEFAULT NULL, `country` varchar(50) NOT NULL, `salesRepEmployeeNumber` int(11) DEFAULT NULL, `creditLimit` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`customerNumber`), KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`), CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` ( `orderNumber` int(11) NOT NULL, `orderDate` date NOT NULL, `requiredDate` date NOT NULL, `shippedDate` date DEFAULT NULL, `status` varchar(15) NOT NULL, `comments` text, `customerNumber` int(11) NOT NULL, PRIMARY KEY (`orderNumber`), KEY `customerNumber` (`customerNumber`), CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `orders` */

insert into

DROP TABLE IF EXISTS `productlines`;

CREATE TABLE `productlines` ( `productLine` varchar(50) NOT NULL, `textDescription` varchar(4000) DEFAULT NULL, `htmlDescription` mediumtext, `image` mediumblob, PRIMARY KEY (`productLine`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `productlines` */

insert into

CREATE TABLE `products` ( `productCode` varchar(15) NOT NULL, `productName` varchar(70) NOT NULL, `productLine` varchar(50) NOT NULL, `productScale` varchar(10) NOT NULL, `productVendor` varchar(50) NOT NULL, `productDescription` text NOT NULL, `quantityInStock` smallint(6) NOT NULL, `buyPrice` decimal(10,2) NOT NULL, `MSRP` decimal(10,2) NOT NULL, PRIMARY KEY (`productCode`), KEY `productLine` (`productLine`), CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `products` */

insert into

CREATE TABLE `orderdetails` ( `orderNumber` int(11) NOT NULL, `productCode` varchar(15) NOT NULL, `quantityOrdered` int(11) NOT NULL, `priceEach` decimal(10,2) NOT NULL, `orderLineNumber` smallint(6) NOT NULL, PRIMARY KEY (`orderNumber`,`productCode`), KEY `productCode` (`productCode`), CONSTRAINT `orderdetails_ibfk_1` FOREIGN KEY (`orderNumber`) REFERENCES `orders` (`orderNumber`), CONSTRAINT `orderdetails_ibfk_2` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `orderdetails` */

insert into

DROP TABLE IF EXISTS `payments`;

CREATE TABLE `payments` ( `customerNumber` int(11) NOT NULL, `checkNumber` varchar(50) NOT NULL, `paymentDate` date NOT NULL, `amount` decimal(10,2) NOT NULL, PRIMARY KEY (`customerNumber`,`checkNumber`), CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `payments` */

insert into

Base on the Sample DB created, please write the SQL query for the Questions below:

1. Show the total employee in the employees table .

2. Find the employees with multiple records with same email (Hint: using Having)

3. How many unique orders in the orderdetails table.

4.List the latest ten orders in the orderdetails table.

5. Find the order with largest amount ( Quantity * unit price) in the orderdeatils table.

6. Delete the order with order number 10100 in the orders table.

7. why it will be failed when you run the query 6?