Using the following SQL Code create table customer ( customer_id integer not nul
ID: 3728414 • Letter: U
Question
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 don’t 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)
Explanation / Answer
1. select * from customer,"Order",OrderLine,Product
where customer.customer_id="Order".customer_id and
customer.customer_id=OrderLine.customer_id and
OrderLine.UniversalProductCode=Product.upc and
"Order".orderdate=OrderLine.orderdate
2. select prodName,unitListPrice from product where upc not in(select UniversalProductCode from OrderLine)
3. select cFirstname,cLastname,cPhone from customer where customer_id in(select customer_id from "Order") order by cLastname,cFirstname
4. select cFirstname,cLastname,cPhone ,orderdate from customer,"Order"
where customer.customer_id="Order".customer_id and datepart(YYYY,"Order".orderdate)<2016
order by cLastname,cFirstname,orderdate
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.