-- Connect to your Postgres server and set the active database to CAP ("\connect CAP" in psql). Then ...
Droping Tables if already exist with same name
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Agents;
DROP TABLE IF EXISTS People;
Creating Tables
-- People --
CREATE TABLE People (
pid int not null,
prefix text,
firstName text,
lastName text,
suffix text,
homeCity text,
DOB date,
primary key(pid)
);
-- Customers --
CREATE TABLE Customers (
pid int not null references People(pid),
paymentTerms text,
discountPct decimal(5,2),
primary key(pid)
);
-- Agents --
CREATE TABLE Agents (
pid int not null references People(pid),
paymentTerms text,
commissionPct decimal(5,2),
primary key(pid)
);
-- Products --
CREATE TABLE Products (
prodId char(3) not null,
name text,
city text,
qtyOnHand int,
priceUSD numeric(10,2),
primary key(prodId)
);
-- Orders --
CREATE TABLE Orders (
orderNum int not null,
dateOrdered date not null,
custId int not null references Customers(pid),
agentId int not null references Agents(pid),
prodId char(3) not null references Products(prodId),
quantityOrdered integer,
totalUSD numeric(12,2),
primary key(orderNum)
);
Inserting Records Into tables
-- People --
INSERT INTO People (pid, prefix, firstName, lastName, suffix, homeCity, DOB)
VALUES
(001, 'Dr.', 'Neil', 'Peart', 'Ph.D.', 'Toronto', '1952-09-12'),
(002, 'Ms.', 'Regina', 'Schock', NULL, 'Toronto', '1957-08-31'),
(003, 'Mr.', 'Bruce', 'Crump', 'Jr.', 'Jacksonville', '1957-07-17'),
(004, 'Mr.', 'Todd', 'Sucherman', NULL, 'Chicago', '1969-05-02'),
(005, 'Mr.', 'Bernard', 'Purdie', NULL, 'Teaneck', '1939-06-11'),
(006, 'Ms.', 'Demetra', 'Plakas', 'Esq.', 'Santa Monica', '1960-11-09'),
(007, 'Ms.', 'Terri Lyne', 'Carrington', NULL, 'Boston', '1965-08-04'),
(008, 'Dr.', 'Bill', 'Bruford', 'Ph.D.', 'Kent', '1949-05-17'),
(009, 'Mr.', 'Alan', 'White', 'III', 'Pelton', '1949-06-14')
;
-- Customers --
INSERT INTO Customers (pid, paymentTerms, discountPct)
VALUES
(001, 'Net 30' , 21.12),
(004, 'Net 15' , 4.04),
(005, 'In Advance', 5.50),
(007, 'On Receipt', 2.00),
(008, 'Net 30' , 10.00)
;-- Agents --
INSERT INTO Agents (pid, paymentTerms, commissionPct)
VALUES
(002, 'Quarterly', 5.00),
(003, 'Annually', 10.00),
(005, 'Monthly', 2.00),
(006, 'Weekly', 1.00)
;
-- Products --
INSERT INTO Products( prodId, name, city, qtyOnHand, priceUSD )
VALUES
('p01', 'Heisenberg Compensator', 'Dallas', 47, 67.50),
('p02', 'Universal Translator', 'Newark', 2399, 5.50 ),
('p03', 'Commodore PET', 'Duluth', 1979, 65.02 ),
('p04', 'LCARS module', 'Duluth', 3, 47.00 ),
('p05', 'Remo drumhead', 'Dallas', 8675309, 16.61 ),
('p06', 'Trapper Keeper', 'Dallas', 1982, 2.00 ),
('p07', 'Flux Capacitor', 'Newark', 1007, 1.00 ),
('p08', 'HAL 9000 memory core', 'Newark', 200, 1.25 ),
('p09', 'Red Barchetta', 'Toronto', 1, 379000.47 )
;
-- Orders --
INSERT INTO Orders(orderNum, dateOrdered, custId, agentId, prodId, quantityOrdered, totalUSD)
VALUES
(1011, '2020-01-23', 001, 002, 'p01', 1100, 58568.40),
(1012, '2020-01-23', 004, 003, 'p03', 1200, 74871.83),
(1015, '2020-01-23', 005, 003, 'p05', 1000, 15696.45),
(1016, '2020-01-23', 008, 003, 'p01', 1000, 60750.00),
(1017, '2020-02-14', 001, 003, 'p03', 500, 25643.88),
(1018, '2020-02-14', 001, 003, 'p04', 600, 22244.16),
(1019, '2020-02-14', 001, 002, 'p02', 400, 1735.36),
(1020, '2020-02-14', 004, 005, 'p07', 600, 575.76),
(1021, '2020-02-14', 004, 005, 'p01', 1000, 64773.00),
(1022, '2020-03-15', 001, 003, 'p06', 450, 709.92),
(1023, '2020-03-15', 001, 002, 'p05', 500, 6550.984),
(1024, '2020-03-15', 005, 002, 'p01', 880, 56133.00),
(1025, '2020-04-01', 008, 003, 'p07', 888, 799.20),
(1026, '2020-05-01', 008, 005, 'p03', 808, 47282.54)
;Queries Related to Given Schema
Query 1:
Display the cities that makes the most different kinds of products.
Query 2:
Display the names of products whose priceUSD is at or above the average priceUSD, in reverse-alphabetical order.
Query 3:
Display the customer last name, product id ordered, and the totalUSD for all orders made in March, sorted by totalUSD from high to low.
Query 4:
Display the last name of all customers (in reverse alphabetical order) and their total ordered, and nothing more. (Hint: Use coalesce to avoid showing NULLs.)
Query 5:
Display the names of all customers who bought products from agents based in Teaneck along with the names of the products they ordered, and the names of the agents who sold it to them.
Query 6:
Write a query to check the accuracy of the totalUSD column in the Orders table. This means calculating Orders.totalUSD from data in other tables and comparing those values to the values in Orders.totalUSD. Display all rows in Orders where Orders.totalUSD is incorrect, if any. If there are any incorrect values, explain why they are wrong.
Query 7:
Display the first and last name of all customers who are also agents.
Query 8:Create a VIEW of all Customer and People data called PeopleCustomers. Then another VIEW of all Agent and People data called PeopleAgents. Then "select *" from each of them in turn to test.
Query 9:Display the first and last name of all customers who are also agents, this time using the views you created.
Query 10:Compare your SQL in #7 (no views) and #9 (using views). The output is the same. How does that work? What is the database server doing internally when it processes the #9 query?