SHARE
TWEET

Untitled

a guest Oct 13th, 2019 110 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*Project 2
  2. Abeer Malik, Hassan Abbas
  3. Seats: E7, E8
  4. Part 1 a*/
  5.  
  6. --Tables
  7. --Create Customer Table
  8. CREATE TABLE Customer (
  9. CustomerID INTEGER PRIMARY KEY,
  10. CustomerName CHAR(20),
  11. CustomerAddress CHAR(50),
  12. CustomerCity CHAR(20),
  13. CustomerState CHAR(20),
  14. CustomerPostalCode CHAR(20),
  15. CustomerEmail CHAR(30),
  16. CustomerUserName CHAR(20),
  17. CustomerPassword CHAR(20));
  18.  
  19. --Create Order Table
  20. CREATE TABLE Orders (
  21. OrderID INTEGER PRIMARY KEY,
  22. OrderDate DATE,
  23. CustomerID INTEGER,
  24. FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID));
  25.  
  26. --Create Territory Table
  27. CREATE TABLE Territory (
  28. TerritoryID INTEGER PRIMARY KEY,
  29. TerritoryName CHAR(20));
  30.  
  31. --Create Sales Person Table
  32. CREATE TABLE SalesPerson (
  33. SalesPersonID INTEGER PRIMARY KEY,
  34. SalesPersonName CHAR(20),
  35. SalesPersonPhone CHAR(50),
  36. SalesPersonEmail CHAR(30),
  37. SalesPersonUserName CHAR(20),
  38. SalesPersonPassword CHAR(20),
  39. TerritoryID INTEGER,
  40. FOREIGN KEY (TerritoryID) REFERENCES Territory(TerritoryID));
  41.  
  42. --Create Product Line Table
  43. CREATE TABLE ProductLine (
  44. ProductLineID INTEGER PRIMARY KEY,
  45. ProductLineName CHAR(20));
  46.  
  47. --Create Product Table
  48. CREATE TABLE Product (
  49. ProductID INTEGER PRIMARY KEY,
  50. ProductName CHAR(20),
  51. ProductFinish CHAR(20),
  52. ProductStandardPrice INTEGER,
  53. ProductLineID INTEGER,
  54. Photo VARCHAR(50),
  55. FOREIGN KEY (ProductLineID) REFERENCES ProductLine(ProductLineID));
  56.  
  57. --Create Order Line Table
  58. CREATE TABLE OrderLine (
  59. OrderID INTEGER,
  60. ProductID INTEGER,
  61. OrderedQuantity INTEGER,
  62. SalePrice INTEGER,
  63. FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
  64. FOREIGN KEY (ProductID) REFERENCES Product(ProductID));
  65.  
  66. --Create Price Update Table
  67. CREATE TABLE PriceUpdate (
  68. PriceUpdateID INTEGER PRIMARY KEY,
  69. DateChanged DATE,
  70. NewPrice INTEGER,
  71. OldPrice INTEGER);
  72.  
  73. --Create Does Business In Table
  74. CREATE TABLE DoesBusinessIn (
  75. CustomerID INTEGER NOT NULL,
  76. TerritoryID INTEGER NOT NULL,
  77. PRIMARY KEY (CustomerID,TerritoryID),
  78. FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
  79. FOREIGN KEY (TerritoryID) REFERENCES Territory(TerritoryID));
  80.  
  81. --Views
  82. --Create Product Line Comparison View
  83. CREATE VIEW PLComparison (ProductLineID, ProductID, ProductName, Quantity)
  84. AS SELECT PL.ProductLineID, P.ProductID, P.ProductName, O.OrderedQuantity
  85. FROM Product P, ProductLine PL, OrderLine O
  86. WHERE PL.ProductLineID = P.ProductLineID AND P.ProductId = O.ProductID;
  87.  
  88. --Create Product Sales View
  89. CREATE VIEW ProductSales (ProductID, ProductName, StandardPrice, Quantity)
  90. AS SELECT P.ProductID, P.ProductName, P.ProductStandardPrice, O.OrderedQuantity
  91. FROM Product P, OrderLine O
  92. WHERE P.ProductId = O.ProductID;
  93.  
  94. --Create Customer Purchases View
  95. CREATE VIEW CustomerPurchases (CustomerID, ProductID, StandardPrice, Quantity, Price)
  96. AS SELECT C.CustomerID, P.ProductID, P.ProductStandardPrice, O.OrderedQuantity, O.SalePrice
  97. FROM Product P, OrderLine O, Customer C
  98. WHERE P.ProductId = O.ProductID;
  99.  
  100. --Create Customer In State View
  101. CREATE VIEW CustomerInState (CustomerID, CustomerState, CustomerName, CustomerPostalCode)
  102. AS SELECT C.CustomerID, C.CustomerState, C.CustomerName, C.CustomerPostalCode
  103. FROM Customer C;
  104.  
  105. --Create Past Purchases View
  106. CREATE VIEW PastPurchases (CustomerID, OrderDate, ProductName, Quantity, Price)
  107. AS SELECT C.CustomerID, O.OrderDate, P.ProductName, OL.OrderedQuantity, OL.SalePrice
  108. FROM Customer C, Orders O, Product P, OrderLine OL
  109. WHERE P.ProductId = OL.ProductID AND OL.OrderID = O.OrderID;
  110.  
  111. --Part 2
  112. --Inserting Values into Customer Table
  113. INSERT INTO Customer VALUES (1, 'Contemporary Casuals', '1355 S Hines Blvd', 'Gainesville', 'FL', '32601-2871', '', '', '');
  114. INSERT INTO Customer VALUES (2, 'Value Furnitures', '15145 S.W. 17th St.', 'Plano', 'TX', '75094-7734', '', '', '');
  115. INSERT INTO Customer VALUES (3, 'Home Furnishings', '1900 Allard Ave', 'Albany', 'NY', '12209-1125',  
  116.                             'homefurnishings?@gmail.com', 'CUSTOMER1', 'CUSTOMER1#');
  117. INSERT INTO Customer VALUES (4, 'Eastern Furniture', '1925 Beltline Rd.', 'Carteret', 'NJ', '07008-3188', '', '', '');
  118. INSERT INTO Customer VALUES (5, 'Impressions', '5585 Westcott Ct.', 'Sacramento', 'CA', '94206-4056', '', '', '');
  119. INSERT INTO Customer VALUES (6, 'Furniture Gallery', '325 Flatiron Dr.', 'Boulder', 'CO', '80514-4432', '', '', '');
  120. INSERT INTO Customer VALUES (7, 'New Furniture', 'Palace Ave', 'Farmington', 'NM', '', '', '', '');
  121. INSERT INTO Customer VALUES (8, 'Dunkins Furniture', '7700 Main St', 'Syracuse', 'NY', '31590', '', '', '');
  122. INSERT INTO Customer VALUES (9, 'A Carpet', '434 Abe Dr', 'Rome', 'NY', '13440', '', '', '');
  123. INSERT INTO Customer VALUES (12, 'Flanigan Furniture', 'Snow Flake Rd', 'Ft Walton Beach', 'FL', '32548', '', '', '');
  124. INSERT INTO Customer VALUES (13, 'Ikards', '1011 S. Main St', 'Las Cruces', 'NM', '88001', '', '', '');
  125. INSERT INTO Customer VALUES (14, 'Wild Bills', 'Four Horse Rd', 'Oak Brook', 'Il', '60522','','','');
  126. INSERT INTO Customer VALUES (15, 'Janet’s Collection', 'Janet Lane', 'Virginia Beach', 'VA', '10012', '', '', '');
  127. INSERT INTO Customer VALUES (16, 'ABC Furniture Co.', '152 Geramino Drive', 'Rome', 'NY', '13440', '', '', '');
  128. SELECT * FROM Customer;
  129.  
  130. --Inserting Values into Territory Table
  131. INSERT INTO Territory VALUES (1, 'SouthEast');
  132. INSERT INTO Territory VALUES (2, 'SouthWest');
  133. INSERT INTO Territory VALUES (3, 'NorthEast');
  134. INSERT INTO Territory VALUES (4, 'NorthWest');
  135. INSERT INTO Territory VALUES (5, 'Central');
  136. SELECT * FROM Territory;
  137.  
  138. --Inserting Values into Sales Person Table
  139. INSERT INTO SalesPerson VALUES (1, 'Doug Henny', '8134445555', 'salesperson?@gmail.com', 'SALESPERSON', 'SALESPERSON#',1);
  140. INSERT INTO SalesPerson VALUES (2, 'Robert Lewis', '8139264006', '', '', '', 2);
  141. INSERT INTO SalesPerson VALUES (3, 'William Strong', '5053821212', '', '', '', 3);
  142. INSERT INTO SalesPerson VALUES (4, 'Julie Dawson', '4355346677', '', '', '', 4);
  143. INSERT INTO SalesPerson VALUES (5, 'Jacob Winslow', '2238973498', '', '', '', 5);
  144. SELECT * FROM SalesPerson;
  145.  
  146. --Inserting Values into Does Business In Table
  147. INSERT INTO DoesBusinessIn VALUES (1, 1);
  148. INSERT INTO DoesBusinessIn VALUES (2, 2);
  149. INSERT INTO DoesBusinessIn VALUES (3, 3);
  150. INSERT INTO DoesBusinessIn VALUES (4, 4);
  151. INSERT INTO DoesBusinessIn VALUES (5, 5);
  152. INSERT INTO DoesBusinessIn VALUES (6, 1);
  153. INSERT INTO DoesBusinessIn VALUES (7, 2);
  154. SELECT * FROM DoesBusinessIn;
  155.  
  156. --Inserting Values into Product Line Table
  157. INSERT INTO ProductLine VALUES (1, 'Cherry Tree');
  158. INSERT INTO ProductLine VALUES (2, 'Scandinavia');
  159. INSERT INTO ProductLine VALUES (3, 'Country Look');
  160. SELECT * FROM ProductLine;
  161.  
  162. --Inserting Values into Product Table
  163. INSERT INTO Product VALUES (1, 'End Table', 'Cherry', 175, 1, 'table.jpg');
  164. INSERT INTO Product VALUES (2, 'Coffee Table', 'Natural Ash', 200, 2, '');
  165. INSERT INTO Product VALUES (3, 'Computer Desk', 'Natural Ash', 375, 2,'');
  166. INSERT INTO Product VALUES (4, 'Entertainment Center', 'Natural Maple', 650, 3,'');
  167. INSERT INTO Product VALUES (5, 'Writers Desk', 'Cherry', 325, 1,'');
  168. INSERT INTO Product VALUES (6, '8-Drawer Desk', 'White Ash', 750, 2,'');
  169. INSERT INTO Product VALUES (7, 'Dining Table', 'Natural Ash', 800, 2,'');
  170. INSERT INTO Product VALUES (8, 'Computer Desk', 'Walnut', 250, 3,'');
  171. SELECT * FROM Product;
  172.  
  173. --Inserting Values into Order Table
  174. INSERT INTO Orders VALUES (1001, '21/Aug/16', 1);
  175. INSERT INTO Orders VALUES (1002, '21/Jul/16', 8);
  176. INSERT INTO Orders VALUES (1003, '22/ Aug/16', 15);
  177. INSERT INTO Orders VALUES (1004, '22/Oct/16', 5);
  178. INSERT INTO Orders VALUES (1005, '24/Jul/16', 3);
  179. INSERT INTO Orders VALUES (1006, '24/Oct/16', 2);
  180. INSERT INTO Orders VALUES (1007, '27/ Aug/16', 5);
  181. INSERT INTO Orders VALUES (1008, '30/Oct/16', 12);
  182. INSERT INTO Orders VALUES (1009, '05/Nov/16', 4);
  183. INSERT INTO Orders VALUES (1010, '05/Nov/16', 1);
  184. SELECT * FROM Orders;
  185.  
  186. --Inserting Values into Order Line Table
  187. INSERT INTO OrderLine VALUES (1001, 1, 2, '');
  188. INSERT INTO OrderLine VALUES (1001, 2, 2, '');
  189. INSERT INTO OrderLine VALUES (1001, 4, 1, '');
  190. INSERT INTO OrderLine VALUES (1002, 3, 5, '');
  191. INSERT INTO OrderLine VALUES (1003, 3, 3, '');
  192. INSERT INTO OrderLine VALUES (1004, 6, 2, '');
  193. INSERT INTO OrderLine VALUES (1004, 8, 2, '');
  194. INSERT INTO OrderLine VALUES (1005, 4, 4, '');
  195. INSERT INTO OrderLine VALUES (1006, 4, 1, '');
  196. INSERT INTO OrderLine VALUES (1006, 5, 2, '');
  197. INSERT INTO OrderLine VALUES (1006, 7, 2, '');
  198. INSERT INTO OrderLine VALUES (1007, 1, 3, '');
  199. INSERT INTO OrderLine VALUES (1007, 2, 2, '');
  200. INSERT INTO OrderLine VALUES (1008, 3, 3, '');
  201. INSERT INTO OrderLine VALUES (1008, 8, 3, '');
  202. INSERT INTO OrderLine VALUES (1009, 4, 2, '');
  203. INSERT INTO OrderLine VALUES (1009, 7, 3, '');
  204. INSERT INTO OrderLine VALUES (1010, 8, 10, '');
  205. SELECT * FROM OrderLine;
  206.  
  207. --Part 3
  208. --Part 3 Question 1
  209. SELECT P.ProductName, P.ProductStandardPrice
  210. FROM Product P
  211. WHERE P.ProductStandardPrice < 275;
  212. /*Q1 PRODUCTNAME   PRODUCTSTANDARDPRICE
  213.      End Table     175
  214.      Coffee Table  200
  215.      ComputerDesk  250
  216. */
  217.      
  218. --Part 3 Question 2    
  219. SELECT P.ProductStandardPrice, P.ProductName, P.ProductID
  220. FROM Product P;
  221. /*Q2 PODUCTSTANDARDPRICE    PRODUCTNAME             PRODUCTID
  222.      157                    End Table               1
  223.      200                    Coffee Table            2
  224.      375                    Computer Desk           3
  225.      650                    Entertainment Center    4
  226.      325                    Writers Desk            5
  227.      750                    8-Drawer Desk           6
  228.      800                    Dining Table            7
  229.      250                    Computer Desk           8
  230. */
  231.      
  232. --Part 3 Question 3      
  233. SELECT AVG(P.ProductStandardPrice)
  234. FROM Product P;
  235. /*Q3 AVG(P.ProductStandardPrice)
  236.      440.625
  237. */
  238.      
  239. --Part 3 Question 4    
  240. SELECT COUNT(DISTINCT O.ProductID)
  241. FROM OrderLine O
  242. WHERE O.OrderID = 1004;
  243. /*Q4
  244. COUNT(DISTINCT O.PRODUCTID)
  245. 2
  246. */
  247.  
  248. --Part 3 Question 5
  249. SELECT O.OrderID, O.OrderDate
  250. FROM Orders O
  251. WHERE O.OrderDate > TO_DATE('24-OCT-2010');
  252. /*Q5
  253. ORDERID ORDERDATE
  254. 1001    21-AUG-16
  255. 1002    21-JUL-16
  256. 1003    22-AUG-16
  257. 1004    22-OCT-16
  258. 1005    24-JUL-16
  259. 1006    24-OCT-16
  260. 1007    27-AUG-16
  261. 1008    30-OCT-16
  262. 1009    05-NOV-16
  263. 1010    05-NOV-16
  264. */
  265.  
  266. --Part 3 Question 6
  267. SELECT P.ProductID, P.ProductName, P.ProductFinish
  268. FROM Product P
  269. WHERE P.ProductFinish != 'Cherry';
  270. /*Q6
  271. PRODUCTID   PRODUCTNAME          PRODUCTFINSIH
  272. 2           Coffee Table         Natural Ash
  273. 3           Computer Desk        Natural Ash
  274. 4           Entertainment Center Natural Maple
  275. 6           8-Drawer Desk        White Ash
  276. 7           Dining Table         Natural Ash
  277. 8           Computer Desk        Walnut
  278. */
  279.  
  280. --Part 3 Question 7
  281. SELECT P.ProductName, P.ProductFinish, P.ProductStandardPrice
  282. FROM Product P
  283. WHERE (P.ProductName LIKE '%Table%' OR P.ProductName LIKE '%Desk%') AND P.ProductStandardPrice > 300;
  284. /*Q7
  285. PRODUCTNAME     PRODUCTFINISH   PRODUCTSTANDARDPRICE
  286. Computer Desk   Natural Ash     375
  287. Writers Desk    Cherry          325
  288. 8-Drawer Desk   White Ash       750
  289. Dining Table    Natural Ash     800
  290. */
  291.  
  292. --Part 3 Question 8
  293. SELECT P.ProductID, P.ProductName, P.ProductStandardPrice
  294. FROM Product P
  295. WHERE P.ProductStandardPrice >= 200 AND P.ProductStandardPrice <= 300;
  296. /*Q8
  297. PRODUCTID   PRODUCTNAME    PRODUCTSTANDARDPRICE
  298. 1           Coffee Table    200
  299. 2           Computer Desk   250
  300. */
  301.  
  302. --Part 3 Question 9
  303. SELECT C.CustomerName, C.CustomerCity, C.CustomerState
  304. FROM Customer C
  305. WHERE C.CustomerState = 'FL' OR C.CustomerState = 'TX' OR C.CustomerState = 'CA' OR C.CustomerState = 'HI'
  306. ORDER BY C.CustomerState, C.CustomerName;
  307. /*Q9
  308. CUSTOMERNAME         CUSTOMERCITY    CUSTOMERSTATE
  309. Impressions          Sacramento      CA
  310. Contemporary Casuals Gainesville     FL
  311. Flanigan Furniture   Ft Walton Beach FL
  312. Value Furnitures     Plano           TX
  313. */
  314.  
  315. --Part 3 Question 10
  316. SELECT C.CustomerState, COUNT(*)
  317. FROM Customer C
  318. GROUP BY c.customerstate;
  319. /*Q10
  320. CUSTOMERSTATE   COUNT(*)
  321. TX              1
  322. NJ              1
  323. FL              2
  324. IL              1
  325. VA              1
  326. NM              2
  327. CO              1
  328. NY              4
  329. CA              1
  330. */
  331.  
  332. --Part 3 Question 11
  333. SELECT C.CustomerState, C.CustomerCity, COUNT(C.CustomerID)
  334. FROM Customer C
  335. GROUP BY C.CustomerState, C.CustomerCity
  336. ORDER BY C.CustomerState;
  337. /*Q11
  338. CUSTOMERSTATE           CUSTOMERCITY            COUNT(C.CUSTOMERID)
  339. CA                      Sacramento              1
  340. CO                      Boulder                 1
  341. FL                      Ft Walton Beach         1
  342. FL                      Gainesville             1
  343. Il                      Oak Brook               1
  344. NJ                      Carteret                1
  345. NM                      Farmington              1
  346. NM                      Las Cruces              1
  347. NY                      Albany                  1
  348. NY                      Rome                    2
  349. NY                      Syracuse                1
  350. TX                      Plano                   1
  351. VA                      Virginia Beach          1
  352. */
  353.  
  354. --Part 3 Question 12
  355. SELECT C.customerState, COUNT(C.customerID)
  356. FROM Customer C
  357. GROUP BY C.CustomerState
  358. HAVING COUNT(C.customerID) > 1;
  359. /*Q12
  360. CUSTOMERSTATE   COUNT(C.CUSTOMERID)
  361. FL              2
  362. NM              2
  363. NY              4
  364. */
  365.  
  366. --Part 3 Question 13
  367. SELECT P.ProductFinish, AVG(P.ProductStandardPrice)
  368. FROM Product P
  369. GROUP BY p.productfinish
  370. HAVING AVG(P.ProductStandardPrice) < 750
  371. ORDER BY P.ProductFinish;
  372. /*Q13
  373. PRODUCTFINISH   AVG(P.PRODUCTSTANDARDPRICE)
  374. Cherry          250
  375. Natural Ash     458.3333333
  376. Natural Maple   650
  377. Walnut          250
  378. */
  379.  
  380. --Part 3 Question 14
  381. SELECT P.ProductID, SUM(O.orderedquantity * p.productstandardprice)
  382. FROM Product P, Orderline O
  383. WHERE P.productid = O.productid
  384. GROUP BY P.productid;
  385. /*Q14
  386. PRODUCTID   SUM(O.ORDEREDQUANTITY*P.PRODUCTSTANDARDPRICE)
  387. 1           875
  388. 2           800
  389. 3           4125
  390. 4           5200
  391. 5           650
  392. 6           1500
  393. 7           4000
  394. 8           3750
  395. */
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top