You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
522 lines
9.4 KiB
522 lines
9.4 KiB
-- create tables
|
|
|
|
CREATE TABLE vendor (
|
|
vendorid CHAR(2) NOT NULL,
|
|
vendorname VARCHAR(25) NOT NULL,
|
|
PRIMARY KEY (vendorid)
|
|
);
|
|
|
|
CREATE TABLE category (
|
|
categoryid CHAR(2) NOT NULL,
|
|
categoryname VARCHAR(25) NOT NULL,
|
|
PRIMARY KEY (categoryid)
|
|
);
|
|
|
|
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)
|
|
);
|
|
|
|
CREATE TABLE region (
|
|
regionid CHAR 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 NOT NULL,
|
|
PRIMARY KEY (storeid),
|
|
FOREIGN KEY (regionid) REFERENCES region(regionid)
|
|
);
|
|
|
|
CREATE TABLE customer (
|
|
customerid CHAR(7) NOT NULL,
|
|
customername
|
|
VARCHAR(15) NOT NULL,
|
|
customerzip CHAR(5) NOT NULL,
|
|
PRIMARY KEY (customerid)
|
|
);
|
|
|
|
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)
|
|
);
|
|
|
|
CREATE TABLE soldvia (
|
|
productid CHAR(3) NOT NULL,
|
|
tid VARCHAR(8) NOT NULL,
|
|
noofitems INT NOT NULL,
|
|
PRIMARY KEY (productid, tid),
|
|
FOREIGN KEY (productid) REFERENCES product(productid),
|
|
FOREIGN KEY (tid) REFERENCES salestransaction(tid)
|
|
);
|
|
|
|
|
|
-- insert data into tables
|
|
|
|
INSERT INTO vendor VALUES ('PG','Pacifica Gear');
|
|
INSERT INTO vendor VALUES ('MK','Mountain King');
|
|
|
|
INSERT INTO category VALUES ('CP','Camping');
|
|
INSERT INTO category VALUES ('FW','Footwear');
|
|
|
|
INSERT INTO product VALUES ('1X1','Zzz Bag',100,'PG','CP');
|
|
INSERT INTO product VALUES ('2X2','Easy Boot',70,'MK','FW');
|
|
INSERT INTO product VALUES ('3X3','Cosy Sock',15,'MK','FW');
|
|
INSERT INTO product VALUES ('4X4','Dura Boot',90,'PG','FW');
|
|
INSERT INTO product VALUES ('5X5','Tiny Tent',150,'MK','CP');
|
|
INSERT INTO product VALUES ('6X6','Biggy Tent',250,'MK','CP');
|
|
|
|
INSERT INTO region VALUES ('C','Chicagoland');
|
|
INSERT INTO region VALUES ('T','Tristate');
|
|
|
|
INSERT INTO store VALUES ('S1','60600','C');
|
|
INSERT INTO store VALUES ('S2','60605','C');
|
|
INSERT INTO store VALUES ('S3','35400','T');
|
|
|
|
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 ','35401');
|
|
|
|
INSERT INTO salestransaction VALUES (
|
|
'T111','1-2-333','S1','2013-01-01'
|
|
);
|
|
INSERT INTO salestransaction VALUES (
|
|
'T222','2-3-444','S2','2013-01-01'
|
|
);
|
|
INSERT INTO salestransaction VALUES (
|
|
'T333','1-2-333','S3','2013-01-02'
|
|
);
|
|
INSERT INTO salestransaction VALUES (
|
|
'T444','3-4-555','S3','2013-01-02'
|
|
);
|
|
INSERT INTO salestransaction VALUES (
|
|
'T555','2-3-444','S3','2013-01-02'
|
|
);
|
|
|
|
INSERT INTO soldvia VALUES ('1X1','T111',1);
|
|
INSERT INTO soldvia VALUES ('2X2','T222',1);
|
|
INSERT INTO soldvia VALUES ('3X3','T333',5);
|
|
INSERT INTO soldvia VALUES ('1X1','T333',1);
|
|
INSERT INTO soldvia VALUES ('4X4','T444',1);
|
|
INSERT INTO soldvia VALUES ('2X2','T444',2);
|
|
INSERT INTO soldvia VALUES ('4X4','T555',4);
|
|
INSERT INTO soldvia VALUES ('5X5','T555',2);
|
|
INSERT INTO soldvia VALUES ('6X6','T555',1);
|
|
|
|
|
|
-- delete the tables
|
|
|
|
DROP TABLE soldvia;
|
|
DROP TABLE salestransaction;
|
|
DROP TABLE store;
|
|
DROP TABLE product;
|
|
DROP TABLE vendor;
|
|
DROP TABLE region;
|
|
DROP TABLE category;
|
|
DROP TABLE customer;
|
|
|
|
|
|
-- queries
|
|
|
|
-- 1
|
|
|
|
SELECT productid, productname, productprice, vendorid, categoryid
|
|
FROM product;
|
|
|
|
-- 1a
|
|
|
|
SELECT * FROM product;
|
|
|
|
-- 2
|
|
|
|
SELECT productname, productid, vendorid, categoryid, productprice
|
|
FROM product;
|
|
|
|
-- 3
|
|
|
|
SELECT productid,productprice
|
|
FROM product;
|
|
|
|
-- 3a
|
|
|
|
SELECT productid, productprice, productprice * 1.1
|
|
FROM product;
|
|
|
|
-- 4
|
|
|
|
-- ERROR EXECUTING
|
|
|
|
SELECT productid, productname, vendorid, productprice
|
|
FROM product;
|
|
WHERE productprice > 100;
|
|
|
|
-- 5
|
|
|
|
SELECT productid, productname, vendorid, productprice
|
|
FROM product;
|
|
WHERE productprice <= 100 AND categoryid = 'FW';
|
|
|
|
-- 6
|
|
|
|
SELECT vendorid
|
|
FROM product;
|
|
|
|
-- 7
|
|
|
|
SELECT DISTINCT vendorid
|
|
FROM product;
|
|
|
|
-- 8
|
|
|
|
SELECT productid, productname, categoryid, productprice
|
|
FROM product;
|
|
WHERE categoryid = 'FW'
|
|
ORDER BY productprice;
|
|
|
|
-- 9
|
|
|
|
SELECT productid, productname, categoryid, productprice
|
|
FROM product;
|
|
WHERE categoryid = 'FW'
|
|
ORDER BY productprice DESC;
|
|
|
|
-- 10
|
|
|
|
SELECT productid, productname, categoryid, productprice
|
|
FROM product;
|
|
ORDER BY categoryid, price;
|
|
|
|
-- 11
|
|
|
|
SELECT *
|
|
FROM product;
|
|
WHERE productname LIKE '%Boot%';
|
|
|
|
-- 12
|
|
|
|
SELECT AVG(productprice)
|
|
FROM product;
|
|
|
|
-- 13
|
|
|
|
SELECT COUNT(*)
|
|
FROM product;
|
|
|
|
-- 14
|
|
|
|
SELECT COUNT(DISTINCT vendorid)
|
|
FROM product;
|
|
|
|
-- 15
|
|
|
|
SELECT COUNT(*), AVG(productprice), MIN(productprice), MAX(productprice)
|
|
FROM product
|
|
WHERE categoryid = 'CP';
|
|
|
|
-- 16
|
|
|
|
SELECT vendorid, COUNT(*), AVG(productprice)
|
|
FROM product
|
|
GROUP BY vendorid;
|
|
|
|
-- 17
|
|
|
|
SELECT COUNT(*), AVG(productprice)
|
|
FROM product
|
|
GROUP BY vendorid;
|
|
|
|
-- 18
|
|
|
|
SELECT vendorid, COUNT(*)
|
|
FROM product
|
|
WHERE productprice >= 100
|
|
GROUP BY vendorid;
|
|
|
|
-- 19
|
|
|
|
SELECT vendorid, categoryid, COUNT(*), AVG(productprice)
|
|
FROM product
|
|
GROUP BY vendorid, categoryid;
|
|
|
|
-- 20
|
|
|
|
SELECT productd, SUM(noofitems)
|
|
FROM soldvia
|
|
GROUP BY productid;
|
|
|
|
-- 21
|
|
|
|
SELECT productid, COUNT(tid)
|
|
FROM soldvia
|
|
GROUP BY productid;
|
|
|
|
-- 22
|
|
|
|
SELECT vendorid, categoryid, COUNT(*), AVG(productprice)
|
|
FROM product
|
|
GROUP BY vendorid, categoryid
|
|
HAVING COUNT(*) > 1;
|
|
|
|
-- 23
|
|
|
|
SELECT vendorid, categoryid, COUNT(*), AVG(productprice)
|
|
FROM product
|
|
WHERE productprice >= 50
|
|
GROUP BY vendorid, categoryid
|
|
HAVING COUNT(*) > 1;
|
|
|
|
-- 24
|
|
|
|
SELECT productid, SUM(noofitems)
|
|
FROM soldvia
|
|
GROUP BY productid;
|
|
HAVING SUM(noofitems) > 3;
|
|
|
|
-- 25
|
|
|
|
SELECT productid, COUNT(tid)
|
|
FROM soldvia
|
|
GROUP BY productid
|
|
HAVING COUNT(tid) > 1;
|
|
|
|
-- 26
|
|
|
|
SELECT productid
|
|
FROM soldvia
|
|
GROUP BY productid
|
|
HAVING SUM(noofitems) > 3;
|
|
|
|
-- 27
|
|
|
|
SELECT productid
|
|
FROM soldvia
|
|
GROUP BY productid
|
|
HAVING COUNT(tid) > 1;
|
|
|
|
-- 28
|
|
|
|
SELECT productid, productname, productprice
|
|
FROM product
|
|
WHERE productprice < (
|
|
SELECT AVG(productprice)
|
|
FROM product
|
|
);
|
|
|
|
-- 29
|
|
|
|
SELECT productid, productname, productprice
|
|
FROM product
|
|
WHERE productid IN (
|
|
SELECT productid
|
|
FROM soldvia
|
|
GROUP BY productid
|
|
HAVING SUM(noofitems) > 3
|
|
);
|
|
|
|
-- 30
|
|
|
|
SELECT productid, productname, productprice
|
|
FROM productd
|
|
WHERE productid IN (
|
|
SELECT productid
|
|
FROM soldvia
|
|
GROUP BY productid
|
|
HAVING COUNT(tid) > 1
|
|
);
|
|
|
|
-- 31
|
|
|
|
SELECT productid, productname, vendorname, productprice
|
|
FROM product, vendor
|
|
WHERE product.vendorid = vendorid;
|
|
|
|
-- 31a
|
|
|
|
SELECT p.productid, p.productname, v.vendorname, p.productprice
|
|
FROM product p, vendor v
|
|
WHERE p.vendorid = v.vendorid
|
|
|
|
-- 31b
|
|
|
|
SELECT p.productid pid, p.productname pname, v.vendorname vname, p.productprice pprice
|
|
FROM product p, vendor v
|
|
WHERE p.vendorid = v.vendorid;
|
|
|
|
-- 31c
|
|
|
|
SELECT p.productid AS pid, p.productname AS pname, v.vendorname AS vname, p.productprice AS pprice
|
|
FROM product p, vendor v
|
|
WHERE p.vendorid = v.vendorid;
|
|
|
|
-- 32
|
|
|
|
SELECT productid, productname, vendorname, productprice
|
|
FROM product, vendor
|
|
|
|
-- 33
|
|
|
|
SELECT *
|
|
FROM product, vendor;
|
|
|
|
-- 34
|
|
|
|
SELECT *
|
|
FROM product, vendor
|
|
WHERE product.vendorid = vendor.vendorid;
|
|
|
|
-- 35
|
|
|
|
SELECT t.tid, t.tdate, p.productname, sv.noofitems AS quantity, (sv.noofitems * p.productprice) AS amount
|
|
FROM product p, salestransaction t, soldvia sv
|
|
WHERE sv.productid = p.productid AND sv.tid = t.tid
|
|
ORDER BY t.tid;
|
|
|
|
-- Alter Statement 1
|
|
|
|
ALTER TABLE vendor ADD (
|
|
vendorphonenumber CHAR(11)
|
|
);
|
|
|
|
-- Alter Statement 2
|
|
|
|
ALTER TABLE vendor DROP (
|
|
vendorphonenumber
|
|
);
|
|
|
|
-- Insert Statement 1
|
|
|
|
INSERT INTO product VALUES (
|
|
'7X7', 'Airy Sock', '1000', 'MK', 'CP'
|
|
);
|
|
|
|
-- Update Statement 1
|
|
|
|
UPDATE product
|
|
SET productprice = 10
|
|
WHERE productid = '7X7';
|
|
|
|
-- Alter Statement 3
|
|
|
|
ALTER TABLE product ADD (
|
|
discount CHAR(11)
|
|
);
|
|
|
|
-- Update Statement 2
|
|
|
|
UPDATE product
|
|
SET discount = 0.2;
|
|
|
|
-- Update Statement 3
|
|
|
|
UPDATE product
|
|
SET discount = 0.3
|
|
WHERE vendorid = 'MK';
|
|
|
|
-- Alter Statement 4
|
|
|
|
ALTER TABLE product DROP (
|
|
discount
|
|
);
|
|
|
|
-- Delete Statement 1
|
|
|
|
DELETE FROM product
|
|
WHERE productid = '7X7';
|
|
|
|
-- Create View Statement 1
|
|
-- first line may be broken
|
|
|
|
CREATE VIEW products_more_than_3_sold AS
|
|
SELECT productid, productname, productprice
|
|
FROM product
|
|
WHERE productid IN (
|
|
SELECT productid
|
|
FROM soldvia
|
|
GROUP BY productid
|
|
HAVING SUM(noofitems) > 3
|
|
);
|
|
|
|
-- Query 29a
|
|
|
|
SELECT *
|
|
FROM products_more_than_3_sold;
|
|
|
|
-- Create View Statement 2
|
|
-- first line may be broken
|
|
|
|
CREATE VIEW products_in_multiple_trnsc AS
|
|
SELECT productid, productname, productprice
|
|
FROM product
|
|
WHERE productid in (
|
|
SELECT productid
|
|
FROM soldvia
|
|
GROUP BY productid
|
|
HAVING COUNT(tid) > 1
|
|
);
|
|
|
|
-- Query 30a
|
|
|
|
SELECT *
|
|
FROM products_in_multiple_trnsc;
|
|
|
|
-- Drop View Statement 1
|
|
|
|
DROP VIEW products_more_than_3_sold;
|
|
|
|
-- Drop View Statement 2
|
|
|
|
DROP VIEW products_in_multiple_trnsc;
|
|
|
|
-- Query 36
|
|
|
|
SELECT *
|
|
FROM products_more_than_3_sold
|
|
UNION
|
|
SELECT *
|
|
FROM products_in_multiple_trnsc
|
|
|
|
-- Query 37
|
|
|
|
SELECT *
|
|
FROM products_more_than_3_sold
|
|
INNER JOIN products_in_multiple_trnsc
|
|
USING (
|
|
productid, productname, productprice
|
|
);
|
|
|
|
-- Query 38
|
|
|
|
SELECT DISTINCT *
|
|
FROM products_more_than_3_sold
|
|
WHERE (
|
|
productid, productname, productprice
|
|
) NOT IN (
|
|
SELECT *
|
|
FROM products_in_multiple_trnsc
|
|
);
|
|
|
|
-- Alternative Query 38
|
|
|
|
SELECT DISTINCT *
|
|
FROM products_more_than_3_sold
|
|
LEFT JOIN products_in_multiple_trnsc
|
|
USING (
|
|
productid, productname, productprice
|
|
)
|
|
WHERE products_in_multiple_trnsc.productid IS NULL;
|