API tools faq
paste
yuhsing

Untitled

yuhsing
Nov 27th, 2012
78
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.12 KB | None | 0 0
raw download clone embed print report
  1. clear
  2.  
  3. DROP TABLE Product;
  4. DROP TABLE Delivery;
  5. DROP TABLE OI;
  6. DROP TABLE Sales_Order;
  7. DROP TABLE Cust;
  8.  
  9.  
  10.  
  11. CREATE TABLE Cust (
  12. Customer_ID VARCHAR(8) NOT NULL,
  13. Customer_Name VARCHAR(20),
  14. Customer_Address VARCHAR(30),
  15. Contact_No VARCHAR(20),
  16. Fax_No VARCHAR(20),
  17. primary key ( Customer_ID )
  18. );
  19.  
  20.  
  21.  
  22. CREATE TABLE Product (
  23. Product_ID VARCHAR(8) NOT NULL,
  24. Product_Name VARCHAR(15),
  25. Product_Description VARCHAR(15),
  26. primary key ( Product_ID )
  27. );
  28.  
  29.  
  30. CREATE TABLE Sales_Order (
  31. Sales_Order_ID VARCHAR(8) NOT NULL,
  32. Sales_Order_Date DATE,
  33. Customer_ID VARCHAR(8),
  34. Order_Amount INTEGER,
  35. Sales_Order_Description VARCHAR(15),
  36. primary key ( Sales_Order_ID ),
  37. foreign key( Customer_ID ) references Cust( Customer_ID )
  38. );
  39.  
  40. CREATE TABLE Delivery (
  41. Delivery_Item_ID VARCHAR(8) NOT NULL,
  42. Sales_Order_ID VARCHAR(8),
  43. Delivery_Description VARCHAR(15),
  44. Delivery_Date DATE,
  45. primary key ( Delivery_Item_ID ),
  46. foreign key( Sales_Order_ID ) references Sales_Order( Sales_Order_ID )
  47. );
  48.  
  49.  
  50. CREATE TABLE OI (
  51. Order_Item_ID VARCHAR(8) NOT NULL,
  52. Order_Item_Date DATE,
  53. Sales_Order_ID VARCHAR(8),
  54. Product_ID VARCHAR(8) NOT NULL,
  55. primary key ( Order_Item_ID,Product_ID ),
  56. foreign key( Sales_Order_ID ) references Sales_Order( Sales_Order_ID ),
  57. foreign key( Product_ID ) references Product( Product_ID )
  58. );
  59.  
  60.  
  61.  
  62. INSERT INTO Cust VALUES ('A1427', 'Alice', '111, Tmn Emas, Perak', '011-1234567','555-555-5555');
  63. INSERT INTO Cust VALUES ('A1428', 'Janice', '222, Tmn Perak, Selangor', '012-1234567','666-666-6666');
  64. INSERT INTO Cust VALUES ('A1429', 'Jessica', '333, Tmn Gangsa, Perak', '013-1234567','777-777-7777');
  65. INSERT INTO Cust VALUES ('A1430', 'Adam', '334, Tmn Gangsa, Perak', '014-1234567','777-777-7778');
  66. INSERT INTO Cust VALUES ('A1431', 'Kesha', '335, Tmn Gangsa, Perak', '015-1234567','777-777-7779');
  67. INSERT INTO Cust VALUES ('A1432', 'Katy', '336, Tmn Gangsa, Perak', '016-1234567','777-777-7780');
  68. INSERT INTO Cust VALUES ('A1433', 'Tiffany', '337, Tmn Gangsa, Perak', '017-1234567','777-777-7781');
  69. INSERT INTO Cust VALUES ('A1434', 'Ali', '338, Tmn Gangsa, Perak', '018-1234567','777-777-7782');
  70. INSERT INTO Cust VALUES ('A1435', 'Abu', '339, Tmn Gangsa, Perak', '019-1234567','777-777-7783');
  71. INSERT INTO Cust VALUES ('A1436', 'Wong', '340, Tmn Gangsa, Perak', '020-1234567','777-777-7784');
  72. INSERT INTO Cust VALUES ('A1437', 'Chan', '341, Tmn Gangsa, Perak', '021-1234567','777-777-7785');
  73. INSERT INTO Cust VALUES ('A1438', 'Ng', '342, Tmn Gangsa, Perak', '022-1234567','777-777-7786');
  74. INSERT INTO Cust VALUES ('A1439', 'Lee', '343, Tmn Gangsa, Perak', '023-1234567','777-777-7787');
  75. INSERT INTO Cust VALUES ('A1440', 'Raj', '344, Tmn Gangsa, Perak', '024-1234567','777-777-7788');
  76. INSERT INTO Cust VALUES ('A1441', 'Alex', '345, Tmn Gangsa, Perak', '025-1234567','777-777-7789');
  77. INSERT INTO Cust VALUES ('A1442', 'Ichigo', '346, Tmn Gangsa, Perak', '026-1234567','777-777-7790');
  78. INSERT INTO Cust VALUES ('A1443', 'Naruto', '347, Tmn Gangsa, Perak', '027-1234567','777-777-7791');
  79. INSERT INTO Cust VALUES ('A1444', 'Natsu', '348, Tmn Gangsa, Perak', '028-1234567','777-777-7792');
  80. INSERT INTO Cust VALUES ('A1445', 'Usher', '349, Tmn Gangsa, Perak', '029-1234567','777-777-7793');
  81. INSERT INTO Cust VALUES ('A1446', 'David', '350, Tmn Gangsa, Perak', '030-1234567','777-777-7794');
  82.  
  83.  
  84.  
  85.  
  86.  
  87.  
  88. INSERT INTO Product VALUES ('AA0001', 'OBook 1','Completed');
  89. INSERT INTO Product VALUES ('AA0002', 'OBook 2','Completed');
  90. INSERT INTO Product VALUES ('AA0003', 'OBook 3','Completed');
  91. INSERT INTO Product VALUES ('AA0004', 'OBook 4','Completed');
  92. INSERT INTO Product VALUES ('AA0005', 'OBook 5','Completed');
  93. INSERT INTO Product VALUES ('AA0006', 'OBook 6','Completed');
  94. INSERT INTO Product VALUES ('AA0007', 'OBook 7','Completed');
  95. INSERT INTO Product VALUES ('AA0008', 'OBook 8','Completed');
  96. INSERT INTO Product VALUES ('AA0009', 'OBook 9','Completed');
  97. INSERT INTO Product VALUES ('AA0010', 'OBook 10','Completed');
  98. INSERT INTO Product VALUES ('AA0011', 'OBook 11','Completed');
  99. INSERT INTO Product VALUES ('AA0012', 'OBook 12','Completed');
  100. INSERT INTO Product VALUES ('AA0013', 'OBook 13','Completed');
  101. INSERT INTO Product VALUES ('AA0014', 'OBook 14','Completed');
  102. INSERT INTO Product VALUES ('AA0015', 'OBook 15','Completed');
  103. INSERT INTO Product VALUES ('AA0016', 'OBook 16','Completed');
  104. INSERT INTO Product VALUES ('AA0017', 'OBook 17','Completed');
  105. INSERT INTO Product VALUES ('AA0018', 'OBook 18','Completed');
  106. INSERT INTO Product VALUES ('AA0019', 'OBook 19','Completed');
  107. INSERT INTO Product VALUES ('AA0020', 'OBook 20','Completed');
  108.  
  109.  
  110. INSERT INTO Sales_Order VALUES ('SO1001', '2001-01-01', 'A1427', '01','Confirmed');
  111. INSERT INTO Sales_Order VALUES ('SO1002', '2002-02-02', 'A1428', '02','Confirmed');
  112. INSERT INTO Sales_Order VALUES ('SO1003', '2003-03-03', 'A1429', '03','Confirmed');
  113. INSERT INTO Sales_Order VALUES ('SO1004', '2004-04-04', 'A1430', '04','Confirmed');
  114. INSERT INTO Sales_Order VALUES ('SO1005', '2005-05-05', 'A1431', '05','Confirmed');
  115. INSERT INTO Sales_Order VALUES ('SO1006', '2006-06-06', 'A1432', '06','Confirmed');
  116. INSERT INTO Sales_Order VALUES ('SO1007', '2007-07-07', 'A1433', '07','Confirmed');
  117. INSERT INTO Sales_Order VALUES ('SO1008', '2008-08-08', 'A1434', '08','Confirmed');
  118. INSERT INTO Sales_Order VALUES ('SO1009', '2009-09-09', 'A1435', '09','Confirmed');
  119. INSERT INTO Sales_Order VALUES ('SO1010', '2010-10-10', 'A1436', '10','Confirmed');
  120. INSERT INTO Sales_Order VALUES ('SO1011', '2011-11-11', 'A1437', '11','Confirmed');
  121. INSERT INTO Sales_Order VALUES ('SO1012', '2012-12-12', 'A1438', '12','Confirmed');
  122. INSERT INTO Sales_Order VALUES ('SO1013', '2001-01-13', 'A1439', '13','Confirmed');
  123. INSERT INTO Sales_Order VALUES ('SO1014', '2002-02-14', 'A1440', '14','Confirmed');
  124. INSERT INTO Sales_Order VALUES ('SO1015', '2003-03-15', 'A1441', '15','Confirmed');
  125. INSERT INTO Sales_Order VALUES ('SO1016', '2004-04-16', 'A1442', '16','Confirmed');
  126. INSERT INTO Sales_Order VALUES ('SO1017', '2005-05-17', 'A1443', '17','Confirmed');
  127. INSERT INTO Sales_Order VALUES ('SO1018', '2006-06-18', 'A1444', '18','Confirmed');
  128. INSERT INTO Sales_Order VALUES ('SO1019', '2007-07-19', 'A1445', '19','Confirmed');
  129. INSERT INTO Sales_Order VALUES ('SO1020', '2008-08-20', 'A1446', '20','Confirmed');
  130.  
  131.  
  132. INSERT INTO Delivery VALUES ('DO1001', 'SO1001', 'Arrived', '2001-01-01');
  133. INSERT INTO Delivery VALUES ('DO1002', 'SO1002', 'Arrived', '2002-02-02');
  134. INSERT INTO Delivery VALUES ('DO1003', 'SO1003', 'Arrived', '2003-03-03');
  135. INSERT INTO Delivery VALUES ('DO1004', 'SO1004', 'Arrived', '2004-04-04');
  136. INSERT INTO Delivery VALUES ('DO1005', 'SO1005', 'Arrived', '2005-05-05');
  137. INSERT INTO Delivery VALUES ('DO1006', 'SO1006', 'Arrived', '2006-06-06');
  138. INSERT INTO Delivery VALUES ('DO1007', 'SO1007', 'Arrived', '2007-07-07');
  139. INSERT INTO Delivery VALUES ('DO1008', 'SO1008', 'Arrived', '2008-08-08');
  140. INSERT INTO Delivery VALUES ('DO1009', 'SO1009', 'Arrived', '2009-09-09');
  141. INSERT INTO Delivery VALUES ('DO1010', 'SO1010', 'Arrived', '2010-10-10');
  142. INSERT INTO Delivery VALUES ('DO1011', 'SO1011', 'Arrived', '2011-11-11');
  143. INSERT INTO Delivery VALUES ('DO1012', 'SO1012', 'Arrived', '2012-12-12');
  144. INSERT INTO Delivery VALUES ('DO1013', 'SO1013', 'Arrived', '2001-01-13');
  145. INSERT INTO Delivery VALUES ('DO1014', 'SO1014', 'Arrived', '2002-02-14');
  146. INSERT INTO Delivery VALUES ('DO1015', 'SO1015', 'Arrived', '2003-03-15');
  147. INSERT INTO Delivery VALUES ('DO1016', 'SO1016', 'Arrived', '2004-04-16');
  148. INSERT INTO Delivery VALUES ('DO1017', 'SO1017', 'Arrived', '2005-05-17');
  149. INSERT INTO Delivery VALUES ('DO1018', 'SO1018', 'Arrived', '2006-06-18');
  150. INSERT INTO Delivery VALUES ('DO1019', 'SO1019', 'Arrived', '2007-07-19');
  151. INSERT INTO Delivery VALUES ('DO1020', 'SO1020', 'Arrived', '2008-08-20');
  152.  
  153.  
  154. INSERT INTO OI VALUES ('OI0001', '2001-01-01', 'SO1001', 'AA0001');
  155. INSERT INTO OI VALUES ('OI0002', '2002-02-02', 'SO1001', 'AA0002');
  156. INSERT INTO OI VALUES ('OI0003', '2003-03-03', 'SO1002', 'AA0003');
  157. INSERT INTO OI VALUES ('OI0004', '2004-04-04', 'SO1003', 'AA0004');
  158. INSERT INTO OI VALUES ('OI0005', '2005-05-05', 'SO1004', 'AA0005');
  159. INSERT INTO OI VALUES ('OI0006', '2006-06-06', 'SO1004', 'AA0006');
  160. INSERT INTO OI VALUES ('OI0007', '2007-07-07', 'SO1004', 'AA0007');
  161. INSERT INTO OI VALUES ('OI0008', '2008-08-08', 'SO1008', 'AA0008');
  162. INSERT INTO OI VALUES ('OI0009', '2009-09-09', 'SO1009', 'AA0009');
  163. INSERT INTO OI VALUES ('OI1010', '2010-10-10', 'SO1010', 'AA0010');
  164.  
  165.  
  166.  
  167. set linesize 150
  168. set wrap off
  169.  
  170. SET ECHO OFF
  171. SET VERIFY OFF
  172. SET PAGESIZE 15
  173. SET LINESIZE 150
  174.  
  175. clear
  176.  
  177. ACCEPT v_sales_order_id FORMAT A10 PROMPT 'Enter Sales Order ID : '
  178.  
  179. TTITLE LEFT 'Customer & Product that involved in this Sales Order ... ', RIGHT 'Page :' PGNO SKIP 1
  180.  
  181. SELECT
  182. C.Customer_ID AS "Cust ID",
  183. C.Customer_Name AS "Name",
  184. P.Product_Name AS "Product Name",
  185. S.Sales_Order_Date AS "Date"
  186. FROM
  187. Cust C,
  188. Product P,
  189. Sales_Order S,
  190. OI A
  191. WHERE
  192. S.Sales_Order_ID = '&v_sales_order_id'
  193. AND
  194. C.Customer_ID = S.Customer_ID
  195. AND
  196. A.Sales_Order_ID = S.Sales_Order_ID
  197. AND
  198. A.Product_ID = P.Product_ID;
  199.  
  200. BTITLE 'End of report'
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!