Create a Database using the following Script:

create table PRODUCTS (ProdID integer Not null, Descrip varchar(50) Not null, Price decimal(4,2), Primary key(ProdID));

create table DEPARTMENTS (DeptNo char(2) unique Not null, DName varchar(14), Location varchar(13), Primary key(DeptNo));

create table STAFF (StaffID char(4) unique Not null, Ename varchar(10), Job varchar(9), MGR char(4), Hiredate date, Salary decimal(7,2), Comm decimal(7,2), DeptNo char(2) not null, Primary key(StaffID), Foreign key(DeptNo) REFERENCES DEPARTMENTS(DeptNo));

create table CUSTOMERS (CustID char(6) unique Not null, Name varchar(45), Address varchar(40), City varchar(30), State varchar(2), Zip varchar(9), AreaCode char(3), Phone varchar (9), RepID char(4) not null, CreditLimit decimal(9,2), Primary key(CustID), Foreign key(RepID) References Staff(StaffID));

create table ORDERS (OrdID char(4) Not null, OrderDate DATE, CustID char(6) Not null, ShipDate DATE, Primary key(OrdID), Foreign key(CustID) References CUSTOMERS(CustID));

create table ITEMS (OrdID char(4) Not null, ItemID smallint Not null, ProdID integer, Qty integer, Primary key(OrdID,ItemID), Foreign key(ProdID) References PRODUCTS(ProdID), Foreign key(OrdID) References ORDERS(OrdID));

insert into PRODUCTS values (100860,'ace tennis racket I',65.00); insert into PRODUCTS values (100861,'ace tennis racket II',58.00); insert into PRODUCTS values (100870,'ace tennis balls-3 pack',15.80); insert into PRODUCTS values (100871,'ace tennis balls-6 pack',10.00); insert into PRODUCTS values (100890,'ace tennis net',74.00); insert into PRODUCTS values (101860,'sp tennis racket',37.00); insert into PRODUCTS values (101863,'sp junior racket',25.00); insert into PRODUCTS values (102130,'rh: "guide to tennis"',8.95); insert into PRODUCTS values (200376,'sb energy bar-6 pack',7.00); insert into PRODUCTS values (200380,'sb vita snack-6 pack',8.40);

Select * from PRODUCTS;

insert into DEPARTMENTS values (10,'Accounting','New York'); insert into DEPARTMENTS values (20,'Research','Dallas'); insert into DEPARTMENTS Values (30,'Sales','Chicago'); insert into DEPARTMENTS values (40,'Operations','Boston');

select * from DEPARTMENTS;

insert into STAFF values (7839,'King','President',null,'17-Nov-11',5000,null,10); insert into STAFF values (7698,'Blake','Manager',7839,'01-May-11',2850,null,30); insert into STAFF values (7782,'Clark','Manager',7839,'02-Jun-11',2450,null,10); insert into STAFF values (7566,'Jones','Manager',7839,'02-Apr-11',2975,null,20); insert into STAFF values (7654,'Martin','Salesman',7698,'28-Feb-12',1250,1400,30); insert into STAFF values (7499,'Allen','Salesman',7698,'20-Feb-11',1600,300,30); insert into STAFF values (7844,'Turner','Salesman',7698,'08-Sep-11',1500,0,30); insert into STAFF values (7900,'James','Clerk',7698,'22-Feb-12',950,null,30); insert into STAFF values (7521,'Ward','Salesman',7698,'22-Feb-12',1250,500,30); insert into STAFF values (7902,'Ford','Analyst',7566,'03-Dec-11',3000,null,20); insert into STAFF values (7369,'Smith','Clerk',7902,'17-Dec-10',800,null,20); insert into STAFF values (7788,'Scott','Analyst',7566,'09-Dec-12',3000,null,20); insert into STAFF values (7876,'Adams','Clerk',7788,'12-Jan-10',1100,null,20); insert into STAFF values (7934,'Miller','Clerk',7782,'23-Jan-12',1300,null,10);

Select * from Staff;

insert into CUSTOMERS values (100,'Jocksports','345 Viewridge','Belmont','CA','96711',415,'598-6609',7844,5000); insert into CUSTOMERS values (101,'TKB Sport Shop','490 Boli Rd.','Redwood City','CA','94061',415,'368-1223',7521,10000); insert into CUSTOMERS values (102,'Vollyrite','9722 Hamilton','Burlingame','CA','95133',415,'644-3341',7654,7000); insert into CUSTOMERS values (103,'Just Tennis','Hillview Mall','Burlingame','CA','97544',415,'677-9312',7521,3000); insert into CUSTOMERS values (104,'Every Mountain','574 Surry Rd.','Cupertino','CA','93301',408,'996-2323',7499,10000); insert into CUSTOMERS values (105,'K + T Sports','3476 El Paseo','Santa Clara','CA','91003',408,'376-9966',7844,5000); insert into CUSTOMERS values (106,'Shape Up','908 Sequoia','Palo Alto','CA','94301',415,'364-9777',7521,6000); insert into CUSTOMERS values (107,'Womens Sports','Valco Village','Sunnyvale','CA','93301',408,'967-4398',7499,10000); insert into CUSTOMERS values (108,'North Woods Fitness Supply Center','98 Lone Pine Way','Hibbing','MN','55649',612,'566-9123',7844,8000);

select * from CUSTOMERS;

insert into ORDERS values (610,'07-JAN-11',101,'08-JAN-11'); insert into ORDERS values (611,'11-JAN-11',102,'11-JAN-11'); insert into ORDERS values (612,'15-JAN-12',104,'20-JAN-12'); insert into ORDERS values (601,'01-MAY-11',106,'30-MAY-11'); insert into ORDERS values (602,'05-JUN-11',102,'20-JUN-11'); insert into ORDERS values (604,'15-JUN-12',106,'30-JUN-12'); insert into ORDERS values (605,'14-JUL-11',106,'30-JUL-11'); insert into ORDERS values (606,'14-JUL-11',100,'30-JAN-12'); insert into ORDERS values (609,'01-AUG-11',100,'15-AUG-11'); insert into ORDERS values (607,'18-JUL-12',104,'18-JUL-12'); insert into ORDERS values (608,'25-JUL-12',104,'25-JUL-12'); insert into ORDERS values (603,'05-JUN-12',102,'05-JUN-12'); insert into ORDERS values (620,'12-MAR-12',100,'12-MAY-12'); insert into ORDERS values (613,'01-FEB-12',108,'01-FEB-12'); insert into ORDERS values (614,'01-FEB-12',102,'05-FEB-12'); insert into ORDERS values (616,'03-FEB-11',103,'10-FEB-11'); insert into ORDERS values (619,'22-FEB-12',104,'04-FEB-12'); insert into ORDERS values (617,'07-FEB-10',105,'03-MAR-10'); insert into ORDERS values (615,'07-FEB-10',107,'06-FEB-10'); insert into ORDERS values (618,'07-FEB-12',102,'06-MAR-12'); insert into ORDERS values (621,'07-MAR-12',100,'01-JAN-12');

Select * from ORDERS;

insert into ITEMS values (610,3,100890,1); insert into ITEMS values (611,1,100861,1); insert into ITEMS values (612,1,100860,100); insert into ITEMS values (601,1,200376,1); insert into ITEMS values (602,1,100870,20); insert into ITEMS values (604,1,100890,3); insert into ITEMS values (604,2,100861,2); insert into ITEMS values (604,3,100860,10); insert into ITEMS values (603,2,100860,4); insert into ITEMS values (610,1,100860,1); insert into ITEMS values (610,2,100870,3); insert into ITEMS values (613,4,200376,200); insert into ITEMS values (614,1,100860,444); insert into ITEMS values (614,2,100870,1000); insert into ITEMS values (612,2,100861,20); insert into ITEMS values (612,3,101863,150); insert into ITEMS values (620,1,100860,10); insert into ITEMS values (620,2,200376,1000); insert into ITEMS values (620,3,102130,500); insert into ITEMS values (613,1,100871,100); insert into ITEMS values (613,2,101860,200); insert into ITEMS values (613,3,200380,150); insert into ITEMS values (619,3,102130,100); insert into ITEMS values (617,1,100860,50); insert into ITEMS values (617,2,100861,100); insert into ITEMS values (614,3,100871,1000); insert into ITEMS values (616,1,100861,10); insert into ITEMS values (616,2,100870,50); insert into ITEMS values (616,3,100890,2); insert into ITEMS values (616,4,102130,10); insert into ITEMS values (616,5,200376,10); insert into ITEMS values (619,1,200380,100); insert into ITEMS values (619,2,200376,100);

insert into ITEMS values (615,1,100861,4); insert into ITEMS values (607,1,100871,1); insert into ITEMS values (615,2,100870,100); insert into ITEMS values (617,3,100870,500); insert into ITEMS values (617,4,100871,500); insert into ITEMS values (617,5,100890,500); insert into ITEMS values (617,6,101860,100); insert into ITEMS values (617,7,101863,200); insert into ITEMS values (617,8,102130,100); insert into ITEMS values (617,9,200376,200); insert into ITEMS values (617,10,200380,300); insert into ITEMS values (609,2,100870,5); insert into ITEMS values (609,3,100890,1); insert into ITEMS values (618,1,100860,23); insert into ITEMS values (618,2,100861,50); insert into ITEMS values (618,3,100870,10); insert into ITEMS values (621,1,100861,10); insert into ITEMS values (621,2,100870,100); insert into ITEMS values (615,3,100871,50); insert into ITEMS values (608,1,100860,1); insert into ITEMS values (608,2,100871,2); insert into ITEMS values (609,1,100861,1); insert into ITEMS values (606,1,102130,1); insert into ITEMS values (605,1,100861,100); insert into ITEMS values (605,2,100870,500); insert into ITEMS values (605,3,100890,5); insert into ITEMS values (605,4,101860,50); insert into ITEMS values (605,6,102130,10); insert into ITEMS values (612,4,100871,100); insert into ITEMS values (619,4,100871,50);

select * from ITEMS;

CIS 360 - Business Database Concepts Assignment 3 - SQL Join \& Aggregate Queries Write the SQL scripts (queries) to answer the following questions. Use the Sports Shop database. 1. What products do we sell? Names and price. Highest price first. 2. I need the name and city of the customers from zip code ending in 301 . Alphabetically by city. 3. How many customers do we have? Label the result column Total Customers. 4. Which department is in Chicago? List the name of the department and its location. 5. Someone's on the phone and wants to know what tennis balls we sell. I need the id number and price also. List cheapest first. 6. HR needs a list of employee names and the date they were hired for all employees hired in 2012. List by hire date within department, oldest first. 7. Which employees earn more than \$2500? We don't need the President. Include the department name, emplovee name, and salary in the result - highest salary first. Keep all employees in the same department together. 8. List all employees that are in department 20. Include the location of the department and order the result by name. List the location first. 9. Display the employee name, salary, department name, and location of all employees who earned a commission. Highest commission first. Use Commission as the header for commissions. 10. Display the employee name and customer name for all employees who are in departments 20 and 30 . List in alphabetical order of employee. 11. List the employees, their jobs and hire dates for those employees who work in Dallas. Order the output by most recent hire date. Include the location in the output. 12. List the number of days delay to ship each order out that was delayed (order date is not the same date as the ship date). Include order number and 'Shipment Delay' in the output - highest number first. What does the output tell you? 13. I was supposed to increase an employee's salary by 20% but I've forgotten which salary I was to increase. I think the employee's name started with the letter " M ". I also seem to recall that the employee worked in department 10. List any employees that match these conditions. Include department number and name so that I can check for sure. 14. List all employees and their managers alphabetically by manager name. Label the columns appropriately. 15. Which customers have placed orders? Display the customer name (alphabetically) only once if they have ordered multiple times. 16. Display the employee name, department number, and all the employee's colleagues that work in the same department. List alphabetically by department number first, then the Employee name, then the colleague names. Label the columns appropriately. 17. What is the annual salary for each employee? Include the employee's job, name, and annual salary. List the result by salary within job. Use appropriate column headings. (weekly salary is stored) 18. What is the difference between the highest and lowest salary? Label the result Salary Difference. 19. We finally hired a number of employees to work in our operations department. Add the following information to the database. 20. What is the average salary for the operations staff? Label output Average Salary for Operations. Show the output with 2 decimal places