Using the following SQL Code

create table customer ( customer_id integer not null generated always as identity (start with 1, increment by 1) constraint customers_pk primary key, cFirstname varchar(20) not null, cLastname varchar(20) not null, cPhone varchar(20) not null, cstreet varchar(50), czipcode varchar(5), constraint customers_uk01 unique(cFirstname, cLastname, cPhone) );

create table "Order" ( customer_id integer not null, orderdate date, soldby varchar(20), constraint orders_customers_fk01 foreign key (customer_id) references customer(customer_id), constraint orders_pk primary key (customer_id, orderdate));

create table product ( upc integer not null primary key, prodName varchar(40) not null, mfgr varchar(40) not null, model varchar(20) not null, unitListPrice double not null, unitsInStock integer not null);

create table OrderLine ( customer_id integer not null, orderdate date not null, UniversalProductCode integer not null, quantity integer not null, unitSalePrice double, constraint orderline_pk primary key (customer_id, orderdate, UniversalProductCode), constraint OrderLine_Order_fk01 foreign key (customer_id, orderdate) references "Order" (customer_id, orderdate), constraint OrderLine_product foreign key (UniversalProductCode) references product (upc) );

insert into customer (cFirstname, cLastname, cPhone, cstreet, czipcode) values ('Dave', 'Brown', '562-982-8696', '123 Lakewood Blvd. Long Beach', '90080'), ('Rachel', 'Burris', '213-543-2211', '54 218th St. Torrance', '90210'), ('Tom', 'Jewett', '714-555-1212', '10200 Slater', '92708'), ('Alvero', 'Monge', '562-111-1234', '314159 Pi St. Long Beach', '90814');

insert into product (UPC, prodName, mfgr, model, unitListPrice, unitsInStock) values (135798642, 'Framing hammer, 20oz.', 'Stanley', 'Frame01', 18.95, 20), (123456789, 'Framer''s level 9 ft.', 'Stanley', 'Frame09', 28.57, 10), (777999111, 'Blade scredriver #2', 'Proto', 'Blad02', 8.53, 15), (123123123, 'Cold Chisel 1"', 'Challenger', 'One inch', 12.04, 30), (321321321, 'Jackhammer, electric', 'Bosche', 'Sml Elec', 128.95, 5), (111222333, 'Arc Welder', 'Lincoln', 'Industrial', 5298.65, 1);

insert into "Order" (customer_ID, orderDate, soldBy) values (1, '2015-12-24', 'Patrick'), (1, '2015-11-25', 'Sally Forth'), (2, '2016-05-05', 'Mack'), (3, '2012-05-05', 'Phillip'), (3, '2014-04-04', 'Patrick');

insert into orderLine (customer_id, orderdate, universalproductcode, quantity, unitsaleprice) values (1, '2015-12-24', 135798642, 3, NULL), (1, '2015-12-24', 123456789, 1, NULL), (1, '2015-11-25', 777999111, 3, NULL), (2, '2016-05-05', 321321321, 2, 120.00), (3, '2012-05-05', 123123123, 1, NULL), (3, '2012-05-05', 777999111, 3, NULL), (3, '2012-05-05', 123456789, 2, NULL), (3, '2014-04-04', 135798642, 1, NULL), (3, '2014-04-04', 123123123, 2, NULL);

Once you have successfully created the various tables above and performed the inserts, write the appropriate code to produce the following results. The number of rows to expect back are listed in () after the question.

1. Perform a join between customer to order to orderline to product and list out all of the columns (9).

2. List the product name and price of all of the products that have never been ordered (1).

3. Not using the natural join, list the first name, last name, and phone number of all of the customers who have placed at least one order. List alphabetically by last name, then first name. List each customer only once. (3)

4. Not using the natural join, list the customer last name, first name, phone number and the date of the order of those who have placed an order prior to 2016. List each customer order only once. List the orders in order by last name, then first name, and finally by date.(4)

5. You remember that someone came in and ordered a level, but you dont recall what sort of level it was, and you want to give them a call. Write a query that will print the first name, last name, phone number and the name of the product of anyone who has ordered a product with the word level in it. Do not assume that you know what the capitalization of the product name is. List each customer and product only once.(2)

6. List the product name, manufacturer, and model of all products that have been ordered at least once in an order in which the month number was the same as the day number. For instance, July 7th 2010 is such a date. List a given product only once.(5)