/*Project 2
Abeer Malik, Hassan Abbas
Seats: E7, E8
Part 1 a*/

--Tables
--Create Customer Table
CREATE TABLE Customer (
CustomerID INTEGER PRIMARY KEY,
CustomerName CHAR(20),
CustomerAddress CHAR(50),
CustomerCity CHAR(20),
CustomerState CHAR(20),
CustomerPostalCode CHAR(20),
CustomerEmail CHAR(30),
CustomerUserName CHAR(20),
CustomerPassword CHAR(20));

--Create Order Table
CREATE TABLE Orders (
OrderID INTEGER PRIMARY KEY,
OrderDate DATE,
CustomerID INTEGER,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID));

--Create Territory Table
CREATE TABLE Territory (
TerritoryID INTEGER PRIMARY KEY,
TerritoryName CHAR(20));

--Create Sales Person Table
CREATE TABLE SalesPerson (
SalesPersonID INTEGER PRIMARY KEY,
SalesPersonName CHAR(20),
SalesPersonPhone CHAR(50),
SalesPersonEmail CHAR(30),
SalesPersonUserName CHAR(20),
SalesPersonPassword CHAR(20),
TerritoryID INTEGER,
FOREIGN KEY (TerritoryID) REFERENCES Territory(TerritoryID));

--Create Product Line Table
CREATE TABLE ProductLine (
ProductLineID INTEGER PRIMARY KEY,
ProductLineName CHAR(20));

--Create Product Table
CREATE TABLE Product (
ProductID INTEGER PRIMARY KEY,
ProductName CHAR(20),
ProductFinish CHAR(20),
ProductStandardPrice INTEGER,
ProductLineID INTEGER,
Photo VARCHAR(50),
FOREIGN KEY (ProductLineID) REFERENCES ProductLine(ProductLineID));

--Create Order Line Table
CREATE TABLE OrderLine (
OrderID INTEGER,
ProductID INTEGER,
OrderedQuantity INTEGER,
SalePrice INTEGER,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID));

--Create Price Update Table
CREATE TABLE PriceUpdate (
PriceUpdateID INTEGER PRIMARY KEY,
DateChanged DATE,
NewPrice INTEGER,
OldPrice INTEGER);

--Create Does Business In Table
CREATE TABLE DoesBusinessIn (
CustomerID INTEGER NOT NULL,
TerritoryID INTEGER NOT NULL,
PRIMARY KEY (CustomerID,TerritoryID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (TerritoryID) REFERENCES Territory(TerritoryID));

--Views
--Create Product Line Comparison View
CREATE VIEW PLComparison (ProductLineID, ProductID, ProductName, Quantity)
AS SELECT PL.ProductLineID, P.ProductID, P.ProductName, O.OrderedQuantity
FROM Product P, ProductLine PL, OrderLine O
WHERE PL.ProductLineID = P.ProductLineID AND P.ProductId = O.ProductID;

--Create Product Sales View
CREATE VIEW ProductSales (ProductID, ProductName, StandardPrice, Quantity)
AS SELECT P.ProductID, P.ProductName, P.ProductStandardPrice, O.OrderedQuantity
FROM Product P, OrderLine O
WHERE P.ProductId = O.ProductID;

--Create Customer Purchases View
CREATE VIEW CustomerPurchases (CustomerID, ProductID, StandardPrice, Quantity, Price)
AS SELECT C.CustomerID, P.ProductID, P.ProductStandardPrice, O.OrderedQuantity, O.SalePrice
FROM Product P, OrderLine O, Customer C
WHERE P.ProductId = O.ProductID;

--Create Customer In State View
CREATE VIEW CustomerInState (CustomerID, CustomerState, CustomerName, CustomerPostalCode)
AS SELECT C.CustomerID, C.CustomerState, C.CustomerName, C.CustomerPostalCode
FROM Customer C;

--Create Past Purchases View
CREATE VIEW PastPurchases (CustomerID, OrderDate, ProductName, Quantity, Price)
AS SELECT C.CustomerID, O.OrderDate, P.ProductName, OL.OrderedQuantity, OL.SalePrice
FROM Customer C, Orders O, Product P, OrderLine OL
WHERE P.ProductId = OL.ProductID AND OL.OrderID = O.OrderID;

--Part 2
--Inserting Values into Customer Table
INSERT INTO Customer VALUES (1, 'Contemporary Casuals', '1355 S Hines Blvd', 'Gainesville', 'FL', '32601-2871', '', '', '');
INSERT INTO Customer VALUES (2, 'Value Furnitures', '15145 S.W. 17th St.', 'Plano', 'TX', '75094-7734', '', '', '');
INSERT INTO Customer VALUES (3, 'Home Furnishings', '1900 Allard Ave', 'Albany', 'NY', '12209-1125',
                            'homefurnishings?@gmail.com', 'CUSTOMER1', 'CUSTOMER1#');
INSERT INTO Customer VALUES (4, 'Eastern Furniture', '1925 Beltline Rd.', 'Carteret', 'NJ', '07008-3188', '', '', '');
INSERT INTO Customer VALUES (5, 'Impressions', '5585 Westcott Ct.', 'Sacramento', 'CA', '94206-4056', '', '', '');
INSERT INTO Customer VALUES (6, 'Furniture Gallery', '325 Flatiron Dr.', 'Boulder', 'CO', '80514-4432', '', '', '');
INSERT INTO Customer VALUES (7, 'New Furniture', 'Palace Ave', 'Farmington', 'NM', '', '', '', '');
INSERT INTO Customer VALUES (8, 'Dunkins Furniture', '7700 Main St', 'Syracuse', 'NY', '31590', '', '', '');
INSERT INTO Customer VALUES (9, 'A Carpet', '434 Abe Dr', 'Rome', 'NY', '13440', '', '', '');
INSERT INTO Customer VALUES (12, 'Flanigan Furniture', 'Snow Flake Rd', 'Ft Walton Beach', 'FL', '32548', '', '', '');
INSERT INTO Customer VALUES (13, 'Ikards', '1011 S. Main St', 'Las Cruces', 'NM', '88001', '', '', '');
INSERT INTO Customer VALUES (14, 'Wild Bills', 'Four Horse Rd', 'Oak Brook', 'Il', '60522','','','');
INSERT INTO Customer VALUES (15, 'Janet’s Collection', 'Janet Lane', 'Virginia Beach', 'VA', '10012', '', '', '');
INSERT INTO Customer VALUES (16, 'ABC Furniture Co.', '152 Geramino Drive', 'Rome', 'NY', '13440', '', '', '');
SELECT * FROM Customer;

--Inserting Values into Territory Table
INSERT INTO Territory VALUES (1, 'SouthEast');
INSERT INTO Territory VALUES (2, 'SouthWest');
INSERT INTO Territory VALUES (3, 'NorthEast');
INSERT INTO Territory VALUES (4, 'NorthWest');
INSERT INTO Territory VALUES (5, 'Central');
SELECT * FROM Territory;

--Inserting Values into Sales Person Table
INSERT INTO SalesPerson VALUES (1, 'Doug Henny', '8134445555', 'salesperson?@gmail.com', 'SALESPERSON', 'SALESPERSON#',1);
INSERT INTO SalesPerson VALUES (2, 'Robert Lewis', '8139264006', '', '', '', 2);
INSERT INTO SalesPerson VALUES (3, 'William Strong', '5053821212', '', '', '', 3);
INSERT INTO SalesPerson VALUES (4, 'Julie Dawson', '4355346677', '', '', '', 4);
INSERT INTO SalesPerson VALUES (5, 'Jacob Winslow', '2238973498', '', '', '', 5);
SELECT * FROM SalesPerson;

--Inserting Values into Does Business In Table
INSERT INTO DoesBusinessIn VALUES (1, 1);
INSERT INTO DoesBusinessIn VALUES (2, 2);
INSERT INTO DoesBusinessIn VALUES (3, 3);
INSERT INTO DoesBusinessIn VALUES (4, 4);
INSERT INTO DoesBusinessIn VALUES (5, 5);
INSERT INTO DoesBusinessIn VALUES (6, 1);
INSERT INTO DoesBusinessIn VALUES (7, 2);
SELECT * FROM DoesBusinessIn;

--Inserting Values into Product Line Table
INSERT INTO ProductLine VALUES (1, 'Cherry Tree');
INSERT INTO ProductLine VALUES (2, 'Scandinavia');
INSERT INTO ProductLine VALUES (3, 'Country Look');
SELECT * FROM ProductLine;

--Inserting Values into Product Table
INSERT INTO Product VALUES (1, 'End Table', 'Cherry', 175, 1, 'table.jpg');
INSERT INTO Product VALUES (2, 'Coffee Table', 'Natural Ash', 200, 2, '');
INSERT INTO Product VALUES (3, 'Computer Desk', 'Natural Ash', 375, 2,'');
INSERT INTO Product VALUES (4, 'Entertainment Center', 'Natural Maple', 650, 3,'');
INSERT INTO Product VALUES (5, 'Writers Desk', 'Cherry', 325, 1,'');
INSERT INTO Product VALUES (6, '8-Drawer Desk', 'White Ash', 750, 2,'');
INSERT INTO Product VALUES (7, 'Dining Table', 'Natural Ash', 800, 2,'');
INSERT INTO Product VALUES (8, 'Computer Desk', 'Walnut', 250, 3,'');
SELECT * FROM Product;

--Inserting Values into Order Table
INSERT INTO Orders VALUES (1001, '21/Aug/16', 1);
INSERT INTO Orders VALUES (1002, '21/Jul/16', 8);
INSERT INTO Orders VALUES (1003, '22/ Aug/16', 15);
INSERT INTO Orders VALUES (1004, '22/Oct/16', 5);
INSERT INTO Orders VALUES (1005, '24/Jul/16', 3);
INSERT INTO Orders VALUES (1006, '24/Oct/16', 2);
INSERT INTO Orders VALUES (1007, '27/ Aug/16', 5);
INSERT INTO Orders VALUES (1008, '30/Oct/16', 12);
INSERT INTO Orders VALUES (1009, '05/Nov/16', 4);
INSERT INTO Orders VALUES (1010, '05/Nov/16', 1);
SELECT * FROM Orders;

--Inserting Values into Order Line Table
INSERT INTO OrderLine VALUES (1001, 1, 2, '');
INSERT INTO OrderLine VALUES (1001, 2, 2, '');
INSERT INTO OrderLine VALUES (1001, 4, 1, '');
INSERT INTO OrderLine VALUES (1002, 3, 5, '');
INSERT INTO OrderLine VALUES (1003, 3, 3, '');
INSERT INTO OrderLine VALUES (1004, 6, 2, '');
INSERT INTO OrderLine VALUES (1004, 8, 2, '');
INSERT INTO OrderLine VALUES (1005, 4, 4, '');
INSERT INTO OrderLine VALUES (1006, 4, 1, '');
INSERT INTO OrderLine VALUES (1006, 5, 2, '');
INSERT INTO OrderLine VALUES (1006, 7, 2, '');
INSERT INTO OrderLine VALUES (1007, 1, 3, '');
INSERT INTO OrderLine VALUES (1007, 2, 2, '');
INSERT INTO OrderLine VALUES (1008, 3, 3, '');
INSERT INTO OrderLine VALUES (1008, 8, 3, '');
INSERT INTO OrderLine VALUES (1009, 4, 2, '');
INSERT INTO OrderLine VALUES (1009, 7, 3, '');
INSERT INTO OrderLine VALUES (1010, 8, 10, '');
SELECT * FROM OrderLine;

--Part 3
--Part 3 Question 1
SELECT P.ProductName, P.ProductStandardPrice
FROM Product P
WHERE P.ProductStandardPrice < 275;
/*Q1 PRODUCTNAME   PRODUCTSTANDARDPRICE
     End Table     175
     Coffee Table  200
     ComputerDesk  250
*/

--Part 3 Question 2
SELECT P.ProductStandardPrice, P.ProductName, P.ProductID
FROM Product P;
/*Q2 PODUCTSTANDARDPRICE    PRODUCTNAME             PRODUCTID
     157                    End Table               1
     200                    Coffee Table            2
     375                    Computer Desk           3
     650                    Entertainment Center    4
     325                    Writers Desk            5
     750                    8-Drawer Desk           6
     800                    Dining Table            7
     250                    Computer Desk           8
*/

--Part 3 Question 3
SELECT AVG(P.ProductStandardPrice)
FROM Product P;
/*Q3 AVG(P.ProductStandardPrice)
     440.625
*/

--Part 3 Question 4
SELECT COUNT(DISTINCT O.ProductID)
FROM OrderLine O
WHERE O.OrderID = 1004;
/*Q4
COUNT(DISTINCT O.PRODUCTID)
2
*/

--Part 3 Question 5
SELECT O.OrderID, O.OrderDate
FROM Orders O
WHERE O.OrderDate > TO_DATE('24-OCT-2010');
/*Q5
ORDERID ORDERDATE
1001    21-AUG-16
1002    21-JUL-16
1003    22-AUG-16
1004    22-OCT-16
1005    24-JUL-16
1006    24-OCT-16
1007    27-AUG-16
1008    30-OCT-16
1009    05-NOV-16
1010    05-NOV-16
*/

--Part 3 Question 6
SELECT P.ProductID, P.ProductName, P.ProductFinish
FROM Product P
WHERE P.ProductFinish != 'Cherry';
/*Q6
PRODUCTID   PRODUCTNAME          PRODUCTFINSIH
2           Coffee Table         Natural Ash
3           Computer Desk        Natural Ash
4           Entertainment Center Natural Maple
6           8-Drawer Desk        White Ash
7           Dining Table         Natural Ash
8           Computer Desk        Walnut
*/

--Part 3 Question 7
SELECT P.ProductName, P.ProductFinish, P.ProductStandardPrice
FROM Product P
WHERE (P.ProductName LIKE '%Table%' OR P.ProductName LIKE '%Desk%') AND P.ProductStandardPrice > 300;
/*Q7
PRODUCTNAME     PRODUCTFINISH   PRODUCTSTANDARDPRICE
Computer Desk   Natural Ash     375
Writers Desk    Cherry          325
8-Drawer Desk   White Ash       750
Dining Table    Natural Ash     800
*/

--Part 3 Question 8
SELECT P.ProductID, P.ProductName, P.ProductStandardPrice
FROM Product P
WHERE P.ProductStandardPrice >= 200 AND P.ProductStandardPrice <= 300;
/*Q8
PRODUCTID   PRODUCTNAME    PRODUCTSTANDARDPRICE
1           Coffee Table    200
2           Computer Desk   250
*/

--Part 3 Question 9
SELECT C.CustomerName, C.CustomerCity, C.CustomerState
FROM Customer C
WHERE C.CustomerState = 'FL' OR C.CustomerState = 'TX' OR C.CustomerState = 'CA' OR C.CustomerState = 'HI'
ORDER BY C.CustomerState, C.CustomerName;
/*Q9
CUSTOMERNAME         CUSTOMERCITY    CUSTOMERSTATE
Impressions          Sacramento      CA
Contemporary Casuals Gainesville     FL
Flanigan Furniture   Ft Walton Beach FL
Value Furnitures     Plano           TX
*/

--Part 3 Question 10
SELECT C.CustomerState, COUNT(*)
FROM Customer C
GROUP BY c.customerstate;
/*Q10
CUSTOMERSTATE   COUNT(*)
TX              1
NJ              1
FL              2
IL              1
VA              1
NM              2
CO              1
NY              4
CA              1
*/

--Part 3 Question 11
SELECT C.CustomerState, C.CustomerCity, COUNT(C.CustomerID)
FROM Customer C
GROUP BY C.CustomerState, C.CustomerCity
ORDER BY C.CustomerState;
/*Q11
CUSTOMERSTATE           CUSTOMERCITY            COUNT(C.CUSTOMERID)
CA                      Sacramento              1
CO                      Boulder                 1
FL                      Ft Walton Beach         1
FL                      Gainesville             1
Il                      Oak Brook               1
NJ                      Carteret                1
NM                      Farmington              1
NM                      Las Cruces              1
NY                      Albany                  1
NY                      Rome                    2
NY                      Syracuse                1
TX                      Plano                   1
VA                      Virginia Beach          1
*/

--Part 3 Question 12
SELECT C.customerState, COUNT(C.customerID)
FROM Customer C
GROUP BY C.CustomerState
HAVING COUNT(C.customerID) > 1;
/*Q12
CUSTOMERSTATE   COUNT(C.CUSTOMERID)
FL              2
NM              2
NY              4
*/

--Part 3 Question 13
SELECT P.ProductFinish, AVG(P.ProductStandardPrice)
FROM Product P
GROUP BY p.productfinish
HAVING AVG(P.ProductStandardPrice) < 750
ORDER BY P.ProductFinish;
/*Q13
PRODUCTFINISH   AVG(P.PRODUCTSTANDARDPRICE)
Cherry          250
Natural Ash     458.3333333
Natural Maple   650
Walnut          250
*/

--Part 3 Question 14
SELECT P.ProductID, SUM(O.orderedquantity * p.productstandardprice)
FROM Product P, Orderline O
WHERE P.productid = O.productid
GROUP BY P.productid;
/*Q14
PRODUCTID   SUM(O.ORDEREDQUANTITY*P.PRODUCTSTANDARDPRICE)
1           875
2           800
3           4125
4           5200
5           650
6           1500
7           4000
8           3750
*/