SHARE
TWEET

Untitled

a guest Dec 11th, 2018 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --=============GRUPA C
  2.  
  3.  
  4. --1 Dla każdego produktu podaj jego nazwe, nazwe kategorii,cene,średnią cene produktów
  5. --z jego kategorii, różnice miedzy ceną jego a srednią, wartość z jego sprzedaży w marcu 1997
  6.  
  7.  
  8. --2 Napisz polecenie, które wyświetla listę dzieci będących członkami
  9. --biblioteki (baza library). Interesuje nas imię, nazwisko, data
  10. --urodzenia dziecka, adres zamieszkania dziecka oraz imię i nazwisko
  11. --rodzica.
  12.  
  13.  
  14. --3)
  15. -- Podaj listę człownków biblioteki (imię, nazwisko) mieszkających w Arizonie (AZ), którzy mają
  16. -- więcej niż dwoje dzieci zapisanych do biblioteki oraz takich, którzy mieszkają w Kalifornii (CA)
  17. -- i mają więcej niż troje dzieci zapisanych do bibliotek. Dla każdej z tych osób podaj liczbę książek
  18. -- przeczytanych (oddanych) przez daną osobę i jej dzieci w grudniu 2001 (z union).
  19.  
  20.  
  21.  
  22. --4. Podaj dane pracownika oraz wartośc obsłużonych przez niego zamówień  razem z freight
  23. --. Ogranicz sie do tych co mają podwladnych
  24.  
  25.  
  26. --1
  27. select (select c.categoryname
  28. from categories as c
  29. where c.categoryid=p.categoryid) as CategoryName,
  30. p.ProductName,p.UnitPrice,
  31. (select avg(pa.unitprice)
  32. from products as pa
  33. where p.CategoryID=pa.CategoryID) as CategoryAverage,
  34. p.UnitPrice-(select avg(pa.unitprice)
  35. from products as pa
  36. where p.CategoryID=pa.CategoryID) as Difference,
  37. (select isnull(sum((od.quantity*od.unitprice*(1-od.discount))),0)
  38. from [order details] as od
  39. inner join orders as o on o.orderid=od.orderid
  40. and year(o.orderdate)=1997 and month(o.orderdate)=3
  41. where od.productid=p.productid) as WartoscSprzedazy
  42. from Products as p
  43.  
  44. --2
  45.  
  46. select jm.FirstName,jm.LastName,j.birth_date,a.state,a.city,a.street,a.zip,
  47. am.firstname,am.lastname
  48. from member as jm
  49. inner join juvenile as j
  50. on jm.member_no=j.member_no
  51. inner join adult as a
  52. on j.adult_member_no=a.member_no
  53. inner join member as am
  54. on a.member_no=am.member_no
  55.  
  56.  
  57. --3
  58.  
  59. select m.FirstName,m.lastname,
  60. (select count(*)
  61. from loanhist as l
  62. where (l.member_no=m.member_no
  63. or l.member_no in
  64. (select j.member_no
  65.  from juvenile as j
  66.  where j.adult_member_no=m.member_no))
  67.  and year(l.in_date)=2001 and month(l.in_date)=12) as il_ksiazek
  68. from member as m
  69. inner join adult as a
  70. on a.member_no=m.member_no
  71. where (a.state like 'AZ' and (select count(*) from juvenile as j where j.adult_member_no=a.member_no)>2)
  72. union
  73. select m.FirstName,m.lastname,
  74. (select count(*)
  75. from loanhist as l
  76. where (l.member_no=m.member_no
  77. or l.member_no in
  78. (select j.member_no
  79.  from juvenile as j
  80.  where j.adult_member_no=m.member_no))
  81.  and year(l.in_date)=2001 and month(l.in_date)=12) as il_ksiazek
  82. from member as m
  83. inner join adult as a
  84. on a.member_no=m.member_no
  85. where (a.state like 'CA' and (select count(*) from juvenile as j where j.adult_member_no=a.member_no)>3)
  86.  
  87.  
  88.  
  89. --4
  90.  
  91.  
  92. select e.FirstName,e.LastName,b.wartosc
  93. from employees as e
  94. inner join (select o.EmployeeID,sum(a.suma+o.Freight) as wartosc
  95. from orders as o
  96. inner join (select OrderID,sum(isnull(quantity*UnitPrice*(1-Discount),0)) as suma
  97. from [Order Details]
  98. group by orderid) as a on a.orderid=o.OrderID
  99. group by o.EmployeeID) as b
  100. on e.employeeid=b.EmployeeID
  101. where b.EmployeeID in(select empl.employeeid
  102. from employees as empl
  103. left join Employees as emp
  104. on emp.reportsto=empl.EmployeeID
  105. where emp.employeeid is null)
  106.  
  107.  
  108. --INNE
  109.  
  110. --1)
  111. -- Napisz polecenie, które wyświetla listę dzieci będących członkami biblioteki. Interesuje nas imię, nazwisko,
  112. -- data urodzenia dziecka, adres zamieszkania, imię i nazwisko rodzica oraz liczba aktualnie wypożyczonych książek.
  113.  
  114. -- 2)Dla każdego klienta znajdź wartość wszystkich złożonych zamówień (weź pod uwagę koszt przesyłki)
  115.  
  116. --3)
  117. -- Czy są jacyś klienci, którzy nie złożyli żadnego zamówienia w 1997, jeśli tak pokaż
  118. -- ich nazwy i dane adresowe (3 wersje - join, in, exists).
  119.  
  120. -- 4) Podaj nazwy produktów, które nie były sprzedawane w marcu 1997.
  121.  
  122. --1
  123. select m.FirstName,m.lastname,am.firstname,am.lastname,j.Birth_Date,a.state,a.city,a.street,
  124. (select count(*)
  125. from loan as l
  126. where l.member_no=j.member_no)
  127. from juvenile as j-- W TYM ZADANIU JEST COŚ ZJEBANE ŻE CZASEM PRZYDZIELA NIE TEGO DZIEDZIAKA DO RODZICA
  128. --                   TRZEBA PRÓBOWAĆ DO SKUTKU
  129. inner join member as m
  130. on j.member_no=m.member_no
  131. inner join adult as a on
  132. j.adult_member_no=a.member_no
  133. inner join member as am
  134. on am.member_no=j.adult_member_no
  135.  
  136. --2
  137. select cu.CompanyName,cc.wartosc
  138. from Customers as cu
  139. inner join (select c.customerid,sum(a.suma+o.Freight) as wartosc
  140. from orders as o
  141. inner join (SELECT OrderID, sum(isnull(quantity*UnitPrice*(1-Discount),0)) as suma
  142. from [Order Details]
  143. group by orderid) as a on a.OrderID=o.OrderID
  144. inner join customers as c
  145. on c.CustomerID=o.CustomerID
  146. group by c.customerid) as cc on cc.CustomerID=cu.CustomerID
  147.  
  148. --3
  149. select c.CompanyName
  150. from customers as c
  151. left join (select o.CustomerID
  152. from Orders as o
  153. where year(o.orderdate)=1997) as a
  154. on a.CustomerID=c.CustomerID
  155. where a.CustomerID is null
  156.  
  157. select c.CompanyName
  158. from customers as c
  159. where not exists (select o.CustomerID
  160. from Orders as o
  161. where year(o.orderdate)=1997 and c.CustomerID=o.customerid)
  162.  
  163. select c.CompanyName
  164. from customers as c
  165. where c.CustomerID not in (select o.CustomerID
  166. from Orders as o
  167. where year(o.orderdate)=1997)
  168.  
  169. --4
  170.  
  171. select p.ProductID
  172. from products as p
  173. where not exists
  174. (select distinct od.productid
  175. from [order details] as od
  176. inner join orders as o
  177. on o.orderid=od.orderid and year(o.orderdate)=1997 and month(o.orderdate)=3
  178. where od.productid=p.productid)
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top