daily pastebin goal
3%
SHARE
TWEET

Untitled

a guest Jun 14th, 2018 48 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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
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