- Please Check 1 And Help With 2 4 Thank You 1 Create A View Over The Product Salestransaction Includes Customer 1 (312.42 KiB) Viewed 77 times
-- 1. create a view over the product, salestransaction,
includes, customer, store, region tables
-- with columns: tdate, productid, productname,
productprice, quantity, customerid, customername,
-- storeid, storezip, regionname
create view view1 as
select tdate, product.productid, productname,
productprice, quantity, customer.customerid, customername,
store.storeid, storezip, regionname
from product inner join includes on product.productid
= includes.productid
inner join salestransaction on includes.tid =
salestransaction.tid
inner join customer on salestransaction.customerid =
customer.customerid
inner join store on salestransaction.storeid =
store.storeid inner join region on store.regionid = region.regionid
;
-- 2. Display the product ID and name for products
whose
-- total sales is less than 3 or total transactions is
at most 1.
-- Use a UNION.
-- 3. Create a view named
category_region
-- over the category, region, store,
salestranaction, includes,
-- and product tables that summarizes total
quantity sold by region and category. The view
-- should have columns: categoryid,
categoryname, regionid, regionname, totalsales
-- 4. Using the view created in 3, which region has the
most sales for each category.
-- you should get the result
-- categoryname regionname
totalsales
-- Electronics Chicagoland 6
-- Climbing Indiana
17
-- Camping Tristate
9
-- Footwear Tristate
20
-- Cycling Chicagoland
13
- create the database DROP DATABASE IF EXISTS zagimore; CREATE DATABASE zagimore; CREATE TABLE salestransaction ( tid VARCHAR(8) NOT NULL, customerid CHAR(7) NOT NULL) storeid VARCHAR(3) NOT NULL, tdate DATE NOT NULL, PRIMARY KEY (tid), FOREIGN KEY (customerid) REFERENCES customer(customerid), FOREIGN KEY (storeid) REFERENCES store (storeid) ); -- select the database USE zagimore; INSERT INTO customer VALUES ('1-2-333', 'Tina', '60137'); INSERT INTO customer VALUES ('2-3-444', 'Tony', '60611'); INSERT INTO customer VALUES ('3-4-555', 'Pam', 35481'); INSERT INTO customer VALUES ('4-5-566', 'Elly', 47374); INSERT INTO customer VALUES ('5-6-777', 'Nora', '68640'); INSERT INTO customer VALUES ('6-7-888', 'Miles, 60602'); INSERT INTO customer VALUES ('7-8-999', 'Neil', '55403'); INSERT INTO customer VALUES ('8-9-900', 'Maggie', '47401); INSERT INTO customer VALUES ('9-9-111','Ryan', '46202); INSERT INTO customer VALUES ('0-1-222', 'Dan', '55499'); INSERT INTO includes VALUES ('1x1', '1111',1); INSERT INTO includes VALUES ('2X2', '1222,1); INSERT INTO includes VALUES ('3X3', '333,5); INSERT INTO includes VALUES ('1x1','T333',1); INSERT INTO includes VALUES (4X4', 'T444", 1); INSERT INTO includes VALUES ('2X2', '1444', 2); INSERT INTO includes VALUES ('4x4','T555', 4); INSERT INTO includes VALUES ('5X5, T555', 2); -- create tables and data CREATE TABLE vendor ( vendorid CHAR(2) NOT NULL, vendorname VARCHAR(25) NOT NULL) PRIMARY KEY (vendorid) ); CREATE TABLE includes ( productid CHAR(3) NOT NULL, tid VARCHAR(8) NOT NULL, quantity INT NOT NULL) PRIMARY KEY (productid, tid), FOREIGN KEY (productid) REFERENCES product (productid), FOREIGN KEY (tid) REFERENCES salestransaction(tid) ); INSERT INTO product VALUES ('1x1','Zzz Bag',100,PG','CP); INSERT INTO product VALUES (2X2', 'Easy Boot', 70, 'M','FW'); INSERT INTO product VALUES ('33','Cosy Sock',15, MK, FW'); INSERT INTO product VALUES ('4X4', 'Dura Boot',98,'PG','FW'); INSERT INTO product VALUES ('5X5', 'Tiny Tent',158, 'MK', 'CP); INSERT INTO product VALUES ('6X6','Biggy Tent',250, 'M','CP); INSERT INTO product VALUES ('7X7', 'Hi-Tec GPS,300,OA','EL"); INSERT INTO product VALUES ('8X8', 'Power Pedals',20,MK', 'CY'); INSERT INTO product VALUES ('99', 'Trusty Rope',30, 'WL', 'CL'); INSERT INTO product VALUES ('1X2','Comfy Harness',150, 'M','CL'); INSERT INTO product VALUES ('1X3', 'Sunny Charger',125, 'OA', 'EL"); INSERT INTO product VALUES ('1X4', 'Safe-T Helmet',40,'PG','CY'); INSERT INTO product VALUES (2X1', 'Mmm Stove', 80, 'WL', 'CP); INSERT INTO product VALUES ('2x3', 'Reflect-o Jacket',35,'PG', 'CY'); INSERT INTO product VALUES ('2X4', 'Strongster Carribeaner',20,MK','CL'); INSERT INTO product VALUES ('3x1', 'Sleepy Pad', 25, 'WL', 'CP); INSERT INTO product VALUES ('3X2', 'Bucky Knife', 60, 'WL', 'CP); INSERT INTO product VALUES ('3X4', 'Treado Tire',30, OA', 'CY'); INSERT INTO product VALUES ('4X1','Slicky Tire', 25, 'OA','CY'); INSERT INTO product VALUES ('4X2', 'Electra Compass,45, MK','EL"); INSERT INTO product VALUES ('4x3', 'Mega Camera',275, 'WL','EL"); INSERT INTO product VALUES ('5x1', 'Simple Sandal',50,PG', 'FW'); INSERT INTO product VALUES (5X2', 'Action Sandal',70,'PG',''); INSERT INTO product VALUES ('5X3', 'Luxo Tent',500,TOA', 'CP); CREATE TABLE category (categoryid CHAR(2) NOT NULL categoryname VARCHAR(25) NOT NULL, PRIMARY KEY (categoryid) ); /* ZAGIMORE - INSERT INTO statements */ INSERT INTO vendor VALUES ('PG', 'Pacifica Gear'); INSERT INTO vendor VALUES ('MK, Mountain King'); INSERT INTO vendor VALUES ('OA', 'Outdoor Adventures'); INSERT INTO vendor VALUES ('WL', 'Wilderness Limited'); CREATE TABLE product productid CHAR(3) NOT NULL productname VARCHAR(25) NOT NULL productprice NUMERIC(7,2) NOT NULL, vendorid CHAR(2) NOT NULL, categoryid CHAR(2) NOT NULL, PRIMARY KEY (productid), FOREIGN KEY (vendorid) REFERENCES vendor(vendorid), FOREIGN KEY (categoryid) REFERENCES category(categoryid) ); INSERT INTO salestransaction VALUES ('7111','1-2-333', '61','2020-81-81'); INSERT INTO salestransaction VALUES ('T222', '2-3-444', '52', '2928-01-01'); INSERT INTO salestransaction VALUES ('7333','1-2-333','3','2920-81-82'); INSERT INTO salestransaction VALUES ('T444', '3-4-555', '53','2020-81-82'); INSERT INTO salestransaction VALUES ('7555','2-3-444','S','2020-01-82'); INSERT INTO salestransaction VALUES ('7666,5-6-777', '110', '2020-81-83'); INSERT INTO salestransaction VALUES ('7777', '6-7-888', '113','2028-01-03); INSERT INTO salestransaction VALUES ('T888, 8-9-600', '14', '2020-81-84'); INSERT INTO salestransaction VALUES ('1999', 4-5-666','56', '2020-81-84'); INSERT INTO salestransaction VALUES ('T101','7-8-999', '512', '2020-81-84'); INSERT INTO salestransaction VALUES ('1202', 8-1-222', '58', '2020-01-94'); INSERT INTO salestransaction VALUES ('T303', '4-5-666', '56','2020-81-85'); INSERT INTO salestransaction VALUES ('1404','8-9-800', '6',' 2020-01-25'); INSERT INTO salestransaction VALUES ('7585', '6-7-888', '514', '2020-81-85); INSERT INTO salestransaction VALUES ('7606','8-1-222', '511','2020-81-86'); INSERT INTO salestransaction VALUES ('T707,5-6-777', '14', '2020-81-86); INSERT INTO salestransaction VALUES ('T808,7-8-999','59', '2028-01-86); INSERT INTO salestransaction VALUES ('1999', '5-6-777','S','2020-81-86'); INSERT INTO salestransaction VALUES ('T01, 8-9-000', '57', '2829-01-87'); INSERT INTO salestransaction VALUES ('T°22', '9-8-111', '55', '2020-61-87'); INSERT INTO includes VALUES ('1''111',1); INSERT INTO includes VALUES ('22","1222,1) INSERT INTO Includes VALUES ('8','7333,5) INSERT INTO includes VALUES ('1X1,333,1) INSERT INTO includes VALUES ('4X4,444,1); INSERT INTO includes VALUES ('2x2,741,2); INSERT INTO includes VALUES ('4X4','1555); INSERT INTO includes VALUES ('5x5,555,2); INSERT INTO includes VALUES ('6X6,7555,1); INSERT INTO includes VALUES ('7X7,7566',1); INSERT INTO includes VALUES ('99,7666',1); INSERT INTO Includes VALUES ('13 7566', 2) INSERT INTO includes VALUES ('8X8", 1777',1); INSERT INTO includes VALUES ('1,'1388",) INSERT INTO includes VALUES ('23', '38')); INSERT INTO includes VALUES ('99', '1999, 1); INSERT INTO includes VALUES ('1x2",1999',5); INSERT INTO includes VALUES ('X', 1999,3); INSERT INTO Includes VALUES ('X','1999,1) INSERT INTO Includes VALUES ('1X2,101,3) INSERT INTO Includes VALUES ('14','13',); INSERT INTO includes VALUES ('2X4","7202); INSERT INTO includes VALUES ('9x9,7303,3); INSERT INTO includes VALUES ('1X4,133,2); INSERT INTO includes VALUES ('2', 1303,2); INSERT INTO includes VALUES ('31','1303,2) INSERT INTO includes VALUES ('2X4, 484,1); INSERT INTO includes VALUES ('23'' 404,2)) INSERT INTO includes VALUES ('22",505",) INSERT INTO includes VALUES ('3X2,7505,1); INSERT INTO includes VALUES ('2','1505, 4); INSERT INTO includes VALUES ('2', '60', 7); INSERT INTO includes VALUES ('31','T,) INSERT INTO includes VALUES ('2x2,506,3) INSERT INTO includes VALUES ('34', tsas', 2) INSERT INTO includes VALUES ('4x4', '60', 2) INSERT INTO Includes VALUES ('3x2","1797,1) INSERT INTO includes VALUES ('3X4","1707','); INSERT INTO includes VALUES ('41','17072); INSERT INTO includes VALUES ('53','TOG',1); INSERT INTO includes VALUES ('42","1508,1); INSERT INTO includes VALUES ('22','',1); INSERT INTO includes VALUES ('43","TB',1); INSERT INTO Includes VALUES ('33','TROS, ) INSERT INTO Includes VALUES ('42","7909) INSERT INTO includes VALUES ('6X6', '1909',1); INSERT INTO includes VALUES ('3','11',); INSERT INTO includes VALUES (4x3,022,3) INSERT INTO includes VALUES ('2x2,922')); INSERT INTO includes VALUES ('s', '22,2); commits INSERT INTO category VALUES ('CP','Camping'); INSERT INTO category VALUES ('Fw', 'Footwear'); INSERT INTO category VALUES ('CL', 'Climbing'); INSERT INTO category VALUES ('EL', 'Electronics ); INSERT INTO category VALUES ('CY','Cycling'); CREATE TABLE region (regionid CHAR(1) NOT NULL, regionname VARCHAR(25) NOT NULL, PRIMARY KEY (regionid) ); CREATE TABLE store ( storeid VARCHAR(3) NOT NULL storezip CHAR(5) NOT NULL, regionid CHAR(1) NOT NULL, PRIMARY KEY (storeid), FOREIGN KEY (regionid) REFERENCES region(regionid) ); INSERT INTO region VALUES ('C', 'Chicagoland'); INSERT INTO region VALUES ('T', 'Tristate'); INSERT INTO region VALUES ('I','Indiana); INSERT INTO region VALUES ('N','North); INSERT INTO store VALUES ('51', '66',''); INSERT INTO store VALUES ('52', '60685',''); INSERT INTO store VALUES ('53','35400', 'T'); INSERT INTO store VALUES ('64', '68640', 'c'); INSERT INTO store VALUES ('55', '46307', 'T'); INSERT INTO store VALUES ('56', '47374','1'); INSERT INTO store VALUES ('57', '47481', 'I'); INSERT INTO store VALUES ('58', '55401',''); INSERT INTO store VALUES ('59', '54937', 'N'); INSERT INTO store VALUES ('S10', '68682',''); INSERT INTO store VALUES ('11', '46201','I'); INSERT INTO store VALUES ('512', '55701', 'N'); INSERT INTO store VALUES ('513', '68885', 'T'); INSERT INTO store VALUES ('514', '53140', 'T'); CREATE TABLE customer ( customerid CHAR(7) NOT NULL customername VARCHAR(15) NOT NULL, customerzip CHAR(5) NOT NULL PRIMARY KEY (customerid) );