Advertisement
Guest User

Untitled

a guest
Jun 14th, 2018
185
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.00 KB | None | 0 0
  1. create table client(
  2. clientNo varchar(6) primary key,
  3. name varchar(20),
  4. city varchar(20),
  5. date_joined datetime,
  6. balance_due money
  7. );
  8. create table product(
  9. productNo varchar(6) primary key,
  10. description1 varchar(50),
  11. profit_margin int ,
  12. qty_available int,
  13. re_order_level int,
  14. item_cost money,
  15. selling_price money,
  16. CONSTRAINT chk_Product CHECK (profit_margin>=0 AND profit_margin<=100)
  17. );
  18. insert into client values ('C001','Sagara','Colombo','2010-12-20', $25000);
  19. insert into client values ('C002','Nisansala','Galle','05-08-2014', $12000);
  20. insert into client values ('C003','Pamith','Piliyandala','2014-01-30', 4500);
  21. insert into client values ('C004','Amali','Moratuwa','2015-06-15',20000);
  22. insert into client values ('C005','Nayana','Nugegoda','2011-12-18', 16500);
  23. insert into client values ('C006','Krishan','Anuradapura','04-03-2014', 22000);
  24. insert into client values ('C007','Ruwanthi','Maharagama','04-05-2015', 8500);
  25. insert into client values ('C008','Nalaka','Colombo','20-MAY-2016', $25000);
  26. insert into client values ('C009','Janaka','Colombo','20-MAY-2016', $25000);
  27. insert into product values ('p0001','FlashDrive 8 GB',5,100, 30,1000,1050);
  28. insert into product values ('p0002','Keyboard',10,25, 5,3500,3850);
  29. insert into product values ('p0003','Mouse',10,50, 15,1200,1320);
  30. insert into product values ('p0004','HardDisk 400 GB',15,20, 5,10000,11500);
  31. insert into product values ('p0005','HardDisk 1 TB',15,35, 3,15000,17250);
  32. insert into product values ('p0006','FlashDrive 32 GB',60,100, 25,1100,1155);
  33. insert into product values ('p0007','LED Monitor 15"',15,15, 5,18000,20700);
  34. insert into product values ('p0008','LED Monitor 17"',20,10, 2,30000,34500);
  35. insert into product values ('p0009','Mouse Pad"',50,10, 2,30,40);
  36. create table Sales_Order
  37. (
  38. Sales_Order_No int primary key,
  39. Sales_Order_Date date,
  40. Order_Taken_By varchar(20),
  41. ClientNo varchar(6),
  42. Delivery_Address varchar(255),
  43. constraint sales_order_fk foreign key(ClientNo) references Client(ClientNo)
  44. )
  45. create table Sales_Order_Details
  46. (
  47. Sales_Order_No int primary key,
  48. Product_No varchar(6),
  49. Quantity int,
  50. constraint order_Details_fk foreign key(Sales_Order_No) references
  51. Sales_Order(Sales_Order_No),
  52. constraint order_Details_fk2 foreign key(Product_No) references Product(ProductNo)
  53. )
  54. create table Items_to_Order
  55. (
  56. NoticeNo int primary key,
  57. Product_No varchar(6),
  58. DateNotified date,
  59. constraint Items_to_order_fk foreign key(Product_No) references Product(ProductNo)
  60. )
  61. insert into Sales_Order values(1,'12-Jan-2010','Nuwani','C001','Homagama')
  62. insert into Sales_Order values(2,'12-Feb-2010','Thushari','C002','Badulla')
  63. insert into Sales_Order values(3,'12-Mar-2010','Sunil','C003','Narahenpita')
  64. insert into Sales_Order values(4,'12-Apr-2010','Chamari','C004','Piliyandala')
  65. insert into Sales_Order values(5,'12-May-2010','Nimal','C005','Moratuwa')
  66. insert into Sales_Order values(6,'12-Jun-2010','Hiran','C005','Katubedda')
  67. insert into Sales_Order values(7,'12-Jul-2010','Tharindu','C007','Kelaniya')
  68. insert into Sales_Order values(8,'12-Aug-2010','Nishadi','C005','Katubedda')
  69. insert into Sales_Order values(9,'12-Aug-2010','Chamari','C008','Colombo')
  70. insert into Sales_Order values(10,'12-Aug-2010','Sunil','C009','Colombo')
  71. insert into Sales_Order values(11,'12-Aug-2010','Sunil','C009','Colombo')
  72. insert into Sales_Order_Details values(1,'p0001',10)
  73. insert into Sales_Order_Details values(2,'p0002',20)
  74. insert into Sales_Order_Details values(3,'p0004',30)
  75. insert into Sales_Order_Details values(4,'p0003',40)
  76. insert into Sales_Order_Details values(5,'p0006',50)
  77. insert into Sales_Order_Details values(6,'p0005',60)
  78. insert into Sales_Order_Details values(7,'p0006',20)
  79. insert into Sales_Order_Details values(9,'p0009',100)
  80. insert into Items_to_Order values(1,'p0007','12-Dec-2015')
  81. insert into Items_to_Order values(2,'p0006','12-Nov-2015')
  82. insert into Items_to_Order values(3,'p0005','12-Oct-2015')
  83. insert into Items_to_Order values(4,'p0004','12-Sep-2015')
  84.  
  85. create view view_3_1
  86. as
  87. select *
  88. from Sales_Order
  89. where ClientNo='C005'
  90.  
  91. select *
  92. from view_3_1
  93. where Sales_Order_No=5
  94.  
  95. select * from view_3_1
  96.  
  97. create view view_3_2
  98. as
  99. select p.description1,sum(sod.Quantity) as total_qty
  100. from product p,Sales_Order_Details sod
  101. where p.productNo=sod.Product_No
  102. group by p.description1
  103.  
  104. select * from view_3_2
  105.  
  106. create view view_3_3
  107. as
  108. select so.Order_Taken_By
  109. from Sales_Order so, Sales_Order_Details sod, product p
  110. where so.Sales_Order_No=sod.Sales_Order_No and sod.Product_No=p.productNo and p.description1
  111. ='HardDisk 1 TB'
  112.  
  113. select * from view_3_3
  114.  
  115. create view view_3_4
  116. as
  117. select Sales_Order_no, DATENAME(DW,Sales_Order_Date) as Order_Day
  118. from Sales_Order
  119.  
  120. select * from view_3_4
  121.  
  122. select *
  123. from Sales_Order
  124. where Sales_Order_Date='12-jan-2010'
  125.  
  126. create view view_3_5
  127. as
  128. select *
  129. from Sales_Order
  130. where datename(D,Sales_Order_Date)=12
  131.  
  132. select * from view_3_5
  133.  
  134. create view view_3_6
  135. as
  136. select sod.Sales_Order_No, p.selling_price*sod.Quantity as Bill_Value
  137. from Sales_Order_Details sod,product p
  138. where sod.Product_No=p.productNo
  139.  
  140. select * from view_3_6
  141.  
  142. CREATE VIEW view_1
  143. AS SELECT * FROM Sales_order WHERE ClientNo IN
  144. (SELECT ClientNo FROM client WHERE Date_Joined='2016-05-20')
  145. select * from view_1
  146.  
  147. UPDATE view_1 SET Delivery_Address = '58, Main Road, Colombo 8'
  148. WHERE Delivery_Address LIKE '%Colombo%';
  149.  
  150. --******4.2******--
  151. CREATE VIEW view_2
  152. AS
  153. SELECT Product .productNo, Product.ProductNo,
  154. Product .Selling_Price* Sales_Order_Details.Quantity AS Billvalue
  155. FROM Sales_Order_Details, Product
  156. WHERE Sales_Order_Details.Product_No = Product.ProductNo AND Selling_Price* Quantity >1000
  157. with check option;
  158.  
  159. select * from product
  160. select * from view_2
  161. UPDATE view_2 SET Selling_Price = 25 WHERE Selling_Price = 40
  162. select * from view_2
  163. UPDATE view_2 SET Selling_Price = 30 WHERE ProductNo = 2
  164. select * from view_2
  165.  
  166. alter procedure sp_3_1
  167. @no varchar(6)
  168. as
  169. begin
  170. select *
  171. from Sales_Order
  172. where ClientNo=@no
  173. end
  174.  
  175. exec sp_3_1 'C007'
  176.  
  177. create procedure sp_3_3
  178. @desc varchar(50)
  179. as
  180. begin
  181. select so.Order_Taken_By
  182. from Sales_Order so, Sales_Order_Details sod, product p
  183. where so.Sales_Order_No=sod.Sales_Order_No and sod.Product_no=p.productNo
  184. and p.description1=@desc
  185. end
  186. exec sp_3_1 'HardDisk 1 TB'
  187.  
  188. create procedure sp_3_3
  189. @desc varchar(50)
  190. as
  191. begin
  192. select so.Order_Taken_By
  193. from Sales_Order so, Sales_Order_Details sod, product p
  194. where so.Sales_Order_No=sod.Sales_Order_No and sod.Product_no=p.productNo
  195. and p.description1=@desc
  196. end
  197.  
  198. create procedure sp_3_3
  199. @desc varchar(50)
  200. as
  201. begin
  202. declare @Sale_name varchar(20)
  203. select @Sale_name=so.Order_Taken_By
  204. from Sales_Order so, Sales_Order_Details sod, product p
  205. where so.Sales_Order_No=sod.Sales_Order_No and sod.Product_no=p.productNo
  206. and p.description1=@desc
  207.  
  208. if @Sale_name='Hiran'
  209. begin
  210. insert into client values (@client,'Janaka','Colombo','20-MAY-2016',$25000)
  211. end
  212. return 5
  213.  
  214. end
  215.  
  216. declare @rtnValue int
  217. exec @rtnValue=sp_3_3 'HardDisk 1 TB','C012'
  218. print @rtnValue
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement