daily pastebin goal
18%
SHARE
TWEET

Untitled

a guest Dec 11th, 2018 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select productid,OrderID,sum(Quantity) as ord
  2. from [Order Details]
  3. where OrderID<10280
  4. group by ProductID, OrderID
  5. with rollup
  6. order by ProductID,OrderID
  7.  
  8. -------------zadania domowe----------------
  9.  
  10. --------------slajd 1-----------------------
  11.  
  12. select top 10 with ties OrderID, sum(UnitPrice*(1-Discount)*Quantity) as 'networth'
  13. from [Order Details]
  14. group by OrderID
  15. order by networth desc
  16.  
  17. ------------slajd 2--------------------------
  18.  
  19. select productid, sum(quantity) as ilosc
  20. from [Order Details]
  21. group by ProductID
  22. having ProductID <3
  23.  
  24. select OrderID, sum(quantity*UnitPrice*(1-Discount)) as wartosc
  25. from [Order Details]
  26. group by OrderID
  27. having sum(Quantity) > 250
  28.  
  29. -------------------slajd 3-------------------
  30.  
  31. select EmployeeID,count(orderid) as [liczba obslugiwanych zamowien]
  32. from Orders
  33. group by EmployeeID
  34.  
  35. select ShipVia, sum(Freight)
  36. from Orders
  37. group by ShipVia
  38.  
  39. select ShipVia, sum(Freight)
  40. from Orders
  41. where datepart(yy,ShippedDate)>1995 and datepart(yy,ShippedDate)<1998
  42. group by ShipVia
  43.  
  44. select EmployeeID,datepart(yy,OrderDate) as rok,datepart(month,OrderDate) as miesiac, count(OrderID) as [liczba zamowien]
  45. from Orders
  46. group by EmployeeID,datepart(yy,OrderDate),datepart(month,OrderDate)
  47.  
  48. select CategoryID, min(UnitPrice) as min, max(UnitPrice) as max
  49. from Products
  50. group by CategoryID
  51.  
  52.  
  53. --==================================LABY 44444444===========================================
  54. use joindb
  55. select * from Produce
  56.  
  57. select * from Buyers
  58.  
  59. select * from Sales
  60.  
  61. USE joindb
  62. SELECT buyer_name, sales.buyer_id, Sales.qty
  63. FROM buyers, sales
  64. WHERE buyers.buyer_id = sales.buyer_id
  65. GO
  66.  
  67. USE northwind
  68. SELECT DISTINCT companyname, orderdate
  69. FROM orders
  70. INNER JOIN customers
  71. ON orders.customerid = customers.customerid
  72. WHERE orderdate >3/1/98
  73. GO
  74.  
  75. SELECT companyname, customers.customerid, orderdate
  76. FROM customers
  77. LEFT OUTER JOIN orders
  78. ON customers.customerid = orders.customerid
  79. WHERE OrderDate IS NULL
  80.  
  81.  
  82. --===========================slajd 17======================
  83.  
  84. select p.ProductName,p.UnitPrice,s.Address
  85. from Products AS p inner join Suppliers AS [s]
  86.     on p.SupplierID = s.SupplierID
  87. where p.UnitPrice between 20 and 30
  88.  
  89. select p.ProductName,p.UnitsInStock,s.CompanyName
  90. from Products AS p inner join Suppliers AS s
  91.     on p.SupplierID=s.SupplierID
  92. where s.CompanyName = 'Tokyo Traders' and p.Discontinued=0
  93.  
  94. --Czy są jacyś klienci którzy nie złożyli żadnego zamówienia w 1997
  95. --roku, jeśli tak to pokaż ich dane adresowe
  96. select c.Address,o.OrderDate
  97. from Customers AS c left outer join Orders AS o
  98.     on c.CustomerID=o.CustomerID
  99. and year(OrderDate)=1997
  100. where OrderID is null
  101.  
  102. select s.CompanyName, s.Phone,p.UnitsInStock
  103. from Suppliers AS s inner join Products AS p
  104.     on s.SupplierID=p.SupplierID
  105. where p.UnitsInStock = 0 and p.Discontinued=0
  106.  
  107. --==============================slajd 18=====================
  108.  
  109. select m.FirstName,m.LastName,j.birth_date
  110. from member AS m inner join juvenile AS j
  111.     on j.member_no = m.member_no
  112.  
  113. select distinct t.title
  114. from title AS t inner join loan AS l
  115.     on l.title_no=t.title_no
  116.  
  117. select l.in_date,l.fine_paid,datepart(dd,l.in_date)-datepart(dd,l.due_date) -------------- coś źle
  118. from title AS t inner join loanhist AS l
  119.     on l.title_no=t.title_no
  120. where t.title='Tao Teh King' and l.fine_paid is not null
  121.  
  122. select r.isbn,r.member_no
  123. from reservation AS r inner join member as m on m.member_no=r.member_no
  124. and m.firstname ='Stephen' and m.middleinitial like 'A' and m.lastname like 'Graff'
  125.  
  126. --==========================slajd 23======================================
  127.  
  128. select p.ProductName, p.UnitPrice,s.Address,c.CategoryName
  129. from Products AS p inner join Categories AS c
  130.     on p.CategoryID = c.CategoryID
  131.     inner join Suppliers AS s on p.SupplierID = s.SupplierID
  132. where p.UnitPrice between 20 and 30 and c.CategoryName like 'Meat/Poultry'
  133.  
  134. select p.ProductName, p.UnitPrice,s.CompanyName,c.CategoryName
  135. from Products AS p inner join Categories AS c
  136.     on p.CategoryID = c.CategoryID
  137.     inner join Suppliers AS s on p.SupplierID = s.SupplierID
  138. where c.CategoryName like 'Confections'
  139.  
  140. select c.ContactName,c.Phone, o.OrderDate,s.CompanyName
  141. from Customers AS c inner join Orders as o
  142.     on o.CustomerID = c.CustomerID
  143. inner join Shippers as s on s.ShipperID = o.ShipVia
  144. where datepart(yy,o.OrderDate) = 1997 and s.CompanyName like 'United Package'
  145.  
  146. select distinct c.ContactName,c.Phone, cat.CategoryName
  147. from Orders AS o inner join [Order Details] as od
  148.     on o.OrderID=od.OrderID  --                                   TRZEBA JESZCZE ORDER DETAILS
  149.     --                                                           BO TO NIE JEST TAK ŻE WSZYSTKO Z ORDERID BYŁO ZAMOWIONE CZY COS TAKIEGO
  150. inner join Products as p on od.ProductID = p.ProductID
  151. inner join Categories as cat on cat.CategoryID=p.CategoryID
  152. inner join Customers as c on c.CustomerID= o.CustomerID
  153. where cat.CategoryName like'Confections'
  154.  
  155. --===========================slajd 24===========================
  156.  
  157. select m.firstname,m.lastname,j.birth_date,a.city,a.street
  158. from juvenile as j inner join member as m on m.member_no=j.member_no
  159. inner join adult as a on a.member_no=j.adult_member_no
  160.  
  161. select m.firstname,m.lastname,j.birth_date,a.city,a.street,b.firstname,b.lastname
  162. from juvenile as j inner join member as m on m.member_no=j.member_no
  163. inner join adult as a on a.member_no=j.adult_member_no
  164. --inner join member as b on j.adult_member_no = b.member_no
  165. inner join member as b on a.member_no = b.member_no
  166.  
  167.  
  168. --=========================================================
  169.  
  170. SELECT a.buyer_id AS buyer1, a.prod_id
  171. ,b.buyer_id AS buyer2
  172. FROM sales AS a
  173. INNER JOIN sales AS b
  174. ON a.prod_id = b.prod_id
  175. where a.buyer_id <> b.buyer_id
  176.  
  177. USE northwind
  178. SELECT a.employeeid, LEFT(a.lastname,10) AS name
  179. ,a.title AS title
  180. ,b.employeeid, b.lastname AS name
  181. ,LEFT(b.title,10) AS title
  182. FROM employees AS a
  183. INNER JOIN employees AS b
  184. ON a.title = b.title
  185. WHERE a.employeeid < b.employeeid
  186.  
  187. use library
  188. --=====================SLAJD 29 =====================
  189.  
  190. select a.LastName,a.FirstName,a.ReportsTo,b.EmployeeID,b.FirstName,b.LastName
  191. from Employees as a inner join Employees as b
  192. on a.ReportsTo = b.EmployeeID
  193.  
  194. --!!!!!!!!!Napisz polecenie, które wyświetla pracowników, którzy nie mają
  195. --podwładnych (baza northwind)
  196.  
  197. select a.EmployeeID,b.EmployeeID,a.ReportsTo,b.ReportsTo from Employees as a
  198. left join Employees as b
  199.     on a.EmployeeID=b.ReportsTo
  200. where b.ReportsTo is null
  201.  
  202. select a.street, a.City,a.member_no
  203. from adult as a
  204. inner join juvenile as j on a.member_no=j.adult_member_no
  205. and datepart(yy,j.birth_date) < 1996
  206.  
  207. select distinct a.street, a.City,a.member_no
  208. from adult as a
  209. inner join juvenile as j on a.member_no=j.adult_member_no
  210. and datepart(yy,j.birth_date) < 1996
  211. left outer join loan as l on a.member_no=l.member_no
  212. where l.member_no is null
  213.  
  214. --============================slajd 31/32========================
  215.  
  216. SELECT (a.firstname + ' ' + a.lastname) AS name,b.street,b.city,b.state,b.zip
  217. from adult as b inner join member as a on a.member_no = b.member_no
  218.  
  219. select c.isbn,c.copy_no,c.on_loan,t.title,i.translation,i.cover
  220. from copy as c inner join item as i on c.isbn=i.isbn
  221. inner join title as t on c.title_no=t.title_no
  222. where c.isbn = 1 or c.isbn = 500 or c.isbn = 1000
  223.  
  224. select m.member_no,m.firstname,m.lastname,r.isbn,r.log_date
  225. from member as m inner join reservation as r on m.member_no=r.member_no
  226.  
  227. select a.member_no, count(j.member_no)
  228. from adult as a inner join juvenile as j on a.member_no=j.adult_member_no
  229. inner join member as m on m.member_no=a.member_no
  230. where a.state like 'AZ'
  231. group by a.member_no
  232. having count(j.member_no)>2
  233. union all
  234. select a.member_no, count(j.member_no)
  235. from adult as a inner join juvenile as j on a.member_no=j.adult_member_no
  236. where a.state like 'CA'
  237. group by a.member_no
  238. having count(j.member_no)>3
  239.  
  240.  
  241. select r.member_no,m.firstname,m.lastname,r.counts
  242. from(select a.member_no, count(j.member_no) as counts
  243. from adult as a inner join juvenile as j on a.member_no=j.adult_member_no
  244. where a.state like 'AZ'
  245. group by a.member_no
  246. having count(j.member_no)>2) as r
  247. inner join member as m on m.member_no=r.member_no
  248. union
  249. select r.member_no,m.firstname,m.lastname,r.counts
  250. from(select a.member_no, count(j.member_no) as counts
  251. from adult as a inner join juvenile as j on a.member_no=j.adult_member_no
  252. where a.state like 'CA'
  253. group by a.member_no
  254. having count(j.member_no)>3) as r
  255. inner join member as m on m.member_no=r.member_no
  256.  
  257. --jak bez uniona?
  258.  
  259.  
  260.  
  261. --ostatnie z 31 sladu done by marcjan
  262.  
  263. select m.firstname,m.lastname,count(*)
  264. from member as m join adult as a on m.member_no=a.member_no
  265. inner join juvenile as j on j.adult_member_no=m.member_no
  266. where a.state like 'AZ'
  267. group by m.firstname,m.lastname-- i tu mamy błąd bo nie można grupować po nieunikalnych kluczach
  268. --powinnismy grupować po member_no
  269. having count(*)>2
  270. -- JAK BEZ UNIONA WAAAAAAAAAAAAAAAŻNE
  271. select  m.firstname,m.lastname,count(*)
  272. from member as m join adult as a on m.member_no=a.member_no
  273. inner join juvenile as j on j.adult_member_no=m.member_no
  274. group by m.firstname,m.lastname,m.member_no,a.state
  275. having (count(*)>2 and state like 'az') or (count(*)>2 and state like 'ca')
  276. --=============================zad.dom slajd 1==============
  277.  
  278. select od.OrderID,o.CustomerID,sum (od.Quantity)as 'quantity'
  279. from Orders as o inner join [Order Details] as od
  280.     on o.OrderID=od.OrderID
  281. group by od.orderid,o.customerid
  282. order by od.orderid
  283.  
  284. select od.OrderID,o.CustomerID, sum (od.Quantity)as 'quantity'
  285. from Orders as o inner join [Order Details] as od
  286.     on o.OrderID=od.OrderID
  287. group by od.orderid,o.customerid
  288. having sum (od.quantity) > 250
  289.  
  290. select od.OrderID,o.CustomerID,sum(od.unitprice*od.quantity*(1-od.discount)) as 'worth'
  291. from Orders as o inner join [Order Details] as od
  292.     on o.OrderID=od.OrderID
  293. group by od.orderid,o.customerid
  294.  
  295. select od.OrderID,o.CustomerID,sum(od.unitprice*od.quantity*(1-od.discount)) as 'worth'
  296. from Orders as o inner join [Order Details] as od
  297.     on o.OrderID=od.OrderID
  298. group by od.orderid,o.customerid
  299. having sum (od.quantity) > 250
  300.  
  301. select od.OrderID,o.CustomerID,sum(od.unitprice*od.quantity*(1-od.discount)) as 'worth',
  302. e.firstname,e.lastname
  303. from Orders as o inner join [Order Details] as od
  304.     on o.OrderID=od.OrderID
  305.     inner join employees as e on e.employeeid = o.employeeid
  306. group by od.orderid,o.customerid,e.firstname,e.lastname
  307. having sum (od.quantity) > 250
  308. order by od.orderid
  309.  
  310. select od.OrderID,o.CustomerID,od.worth,e.FirstName,e.LastName
  311. from (select OrderID,sum(UnitPrice*Quantity*(1-Discount)) as worth,sum(quantity) as quantity
  312. from [Order Details] group by OrderID) as od inner join orders as o
  313.     on o.OrderID=od.OrderID
  314.     inner join employees as e on e.employeeid = o.employeeid
  315. where od.quantity > 250
  316. order by od.OrderID
  317.  
  318.  
  319. --============================ zad. dom slajd 2 ===========================
  320.  
  321. select c.categoryid, sum(a.suma) as ilosc
  322. from (select ProductID,sum(Quantity) as suma from [order details] group by productid) as a
  323. inner join Products as p on a.ProductID=p.ProductID
  324. inner join Categories as c on c.CategoryID=p.CategoryID
  325. group by c.CategoryID
  326. order by ilosc desc
  327.  
  328. select c.categoryid, sum(a.wartosc) as worth
  329. from (select ProductID,sum(Quantity*UnitPrice*(1-Discount)) as wartosc from [order details] group by productid) as a
  330. inner join Products as p on a.ProductID=p.ProductID
  331. inner join Categories as c on c.CategoryID=p.CategoryID
  332. group by c.CategoryID
  333. order by worth desc
  334. -- zrobić podpunkt b
  335.  
  336. select a.OrderID,a.wartosc+b.freight
  337. from (select OrderID,sum(Quantity*UnitPrice*(1-Discount)) as wartosc
  338. from [order details] group by orderid) as a
  339. inner join Orders as b on a.OrderID=b.OrderID
  340.  
  341. --============================ zad. dom slajd 3 ===========================
  342.  
  343. select b.companyname,a.ShipVia,a.liczba
  344. from (select ShipVia,count (OrderID) as liczba
  345. from Orders
  346. where datepart(yy,ShippedDate) = 1997
  347. group by shipvia) as a
  348. inner join Shippers as b on b.ShipperID=a.ShipVia
  349.  
  350. select top 1 b.companyname,a.liczba
  351. from (select ShipVia,count (OrderID) as liczba
  352. from Orders
  353. where datepart(yy,ShippedDate) = 1997
  354. group by shipvia) as a
  355. inner join Shippers as b on b.ShipperID=a.ShipVia
  356. order by a.liczba desc
  357.  
  358. select e.FirstName,e.LastName,a.razem
  359. from (select o.EmployeeID,sum(od.suma) as razem
  360. from(select orderid,sum(Quantity*UnitPrice*(1-Discount)) as suma
  361. from [Order Details]
  362. group by orderid) as od
  363. inner join Orders as o on o.OrderID=od.orderid
  364. group by o.EmployeeID) as a
  365. inner join Employees as e on a.EmployeeID=e.EmployeeID
  366.  
  367. select e.FirstName,e.LastName,a.liczba
  368. from(select EmployeeID,count (OrderID) as liczba
  369. from Orders where datepart(yy,OrderDate)= 1997
  370. group by EmployeeID) as a
  371. inner join Employees as e on a.EmployeeID=e.EmployeeID
  372. order by a.liczba desc
  373.  
  374. select e.FirstName,e.LastName,a.liczba
  375. from(select EmployeeID,count (OrderID) as liczba
  376. from Orders where datepart(yy,OrderDate)= 1997
  377. group by EmployeeID) as a
  378. inner join Employees as e on a.EmployeeID=e.EmployeeID
  379. order by a.liczba desc
  380.  
  381. select top 1 e.FirstName,e.LastName,a.suma as razem
  382. from(select o.EmployeeID, sum(od.Quantity*od.UnitPrice*(1-od.Discount)) as suma
  383. from [order details] as od
  384. inner join orders as o on od.orderid=o.orderid
  385.  and datepart(yy,o.OrderDate)= 1997
  386. group by o.EmployeeID) as a
  387. inner join Employees as e on a.EmployeeID=e.EmployeeID
  388. order by a.suma desc
  389.  
  390. --============================ zad. dom slajd 4 ===========================
  391.  
  392. select e.FirstName,e.LastName,a.suma as razem
  393. from(select o.EmployeeID, sum(od.Quantity*od.UnitPrice*(1-od.Discount)) as suma
  394. from [order details] as od
  395. inner join orders as o on od.orderid=o.orderid
  396.  and datepart(yy,o.OrderDate)= 1997
  397. group by o.EmployeeID) as a
  398. inner join Employees as e on a.EmployeeID=e.EmployeeID and e.ReportsTo is not null
  399. order by a.suma desc
  400.  
  401. select c.firstname,c.lastname,b.EmployeeID,b.razem
  402. from(select a.EmployeeID,a.suma as razem
  403. from(select o.EmployeeID, sum(od.Quantity*od.UnitPrice*(1-od.Discount)) as suma
  404. from [order details] as od
  405. inner join orders as o on od.orderid=o.orderid
  406.  and datepart(yy,o.OrderDate)= 1997
  407. group by o.EmployeeID) as a
  408. left join Employees as e on a.EmployeeID=e.ReportsTo
  409. where e.EmployeeID is null) as b
  410. inner join Employees as c on b.EmployeeID=c.EmployeeID
  411.  
  412.  
  413. -- zajęc ciąg dalszy
  414.  
  415. select (firstname + ' ' +lastname) as name , city , postalcode
  416. from Employees union all
  417. select companyname,city,postalcode
  418. from customers
  419.  
  420.  
  421. /*
  422. ============================================================================
  423. ================================       =====================================
  424. ==============================   Laby 4   ==================================
  425. ================================       =====================================
  426. ============================================================================
  427.  */
  428.  
  429. USE northwind
  430. SELECT productname, UnitPrice
  431. ,( SELECT AVG(UnitPrice) FROM products) AS average
  432. ,UnitPrice-(SELECT AVG(UnitPrice) FROM products) AS difference
  433. FROM products
  434.  
  435. USE northwind
  436. SELECT productname, unitprice,CategoryID
  437. ,( SELECT AVG(unitprice)
  438. FROM products as p_wew
  439. WHERE p_zew.categoryid = p_wew.categoryid ) AS
  440. average
  441. FROM products as p_zew
  442.  
  443. select p.ProductName,p.UnitPrice,p.CategoryID,s.sr
  444. from Products p join
  445. (select CategoryID,avg(UnitPrice) sr
  446. from Products
  447. group by CategoryID) s on p.CategoryID=s.CategoryID
  448. order by 1
  449.  
  450. --== EXIST lub notEXIST
  451.  
  452. SELECT lastname, employeeid
  453. FROM employees AS e
  454. WHERE EXISTS (SELECT * FROM orders AS o
  455. WHERE e.employeeid = o.employeeid
  456. AND o.orderdate = '9/5/97')
  457.  
  458. SELECT DISTINCT lastname, e.employeeid
  459. FROM orders AS o
  460. INNER JOIN employees AS e
  461. ON o.employeeid = e.employeeid
  462. WHERE o.orderdate = '9/5/1997'
  463.  
  464. SELECT lastname, employeeid
  465. FROM employees AS e
  466. WHERE not EXISTS (SELECT * FROM orders AS o
  467. WHERE e.employeeid = o.employeeid
  468. AND o.orderdate = '9/5/97')
  469.  
  470. SELECT DISTINCT lastname, e.employeeid
  471. FROM orders AS o
  472. right JOIN employees AS e
  473. ON o.employeeid = e.employeeid
  474. and o.orderdate = '9/5/1997'
  475. where o.OrderID is null
  476.  
  477.  
  478. -- in =================================
  479.  
  480. SELECT lastname, employeeid
  481. FROM employees AS e
  482. WHERE employeeid IN (SELECT employeeid FROM orders AS o
  483. WHERE o.orderdate = '9/5/97')
  484.  
  485.  
  486.  
  487.  
  488. SELECT productname, unitprice
  489. ,( SELECT AVG(unitprice) FROM products as p_wew
  490. WHERE p_zewn.categoryid = p_wew.categoryid ) AS
  491. average
  492. FROM products as p_zewn
  493.  
  494.  
  495. select e.LastName,e.EmployeeID
  496. from Employees as e
  497. inner join Orders as o on o.EmployeeID=e.EmployeeID
  498. where o.OrderDate = '9/5/1997'
  499.  
  500. --=======================                ============================
  501. --====================    Zadania domowe   ==========================
  502. --=======================       Slajd 1     =========================
  503.  
  504.  
  505. select c.ContactName,c.Phone
  506. from (select distinct o.CustomerID
  507. from Orders as o
  508. inner join Shippers as s
  509. on o.ShipVia=s.ShipperID
  510. where datepart(yy,o.ShippedDate)= '1997'
  511. and s.CompanyName like 'United Package')
  512. as a
  513. inner join Customers as c on c.CustomerID=a.CustomerID
  514.  
  515. select c.ContactName,c.Phone
  516. from Customers as c
  517. inner join Orders as o
  518. on o.CustomerID=c.CustomerID and datepart(yy,o.ShippedDate)= '1997'
  519. inner join Shippers as s on o.ShipVia=s.ShipperID and s.CompanyName like 'United Package'
  520.  
  521. select cus.ContactName,cus.Phone
  522. from(select distinct c.CustomerID
  523. from (select o.CustomerID
  524. from orders as o inner join
  525. (select od.OrderID
  526. from [Order Details] as od
  527. inner join (select p.productid
  528. from Products as p
  529. where categoryid not in (select categoryid from categories as c
  530. where c.CategoryName like 'Confections'))
  531. as a on od.productid=a.productid) as b
  532. on o.OrderID=b.OrderID) as d
  533. inner join Customers as c
  534. on c.CustomerID=d.CustomerID) as cu
  535. inner join Customers as cus on
  536. cu.CustomerID=cus.CustomerID
  537.  
  538. --========================== slajd 2 ===========================
  539.  
  540. select distinct o.productid,quantity
  541. from [order details] as o
  542. where quantity = (select max(quantity)
  543. from [Order Details] as od
  544. where od.productid=o.productid)
  545.  
  546. select distinct o.productid,o.UnitPrice
  547. from [order details] as o
  548. where unitprice < (select avg(unitprice)
  549. from [Order Details])
  550.  
  551. select pr.productid,pr.unitprice,b.avr
  552. from Products as pr
  553. inner join (select c.categoryid,avg(p.UnitPrice) as avr --zrobic nie korzystajc z unitprice
  554. from products as p
  555. inner join categories as c
  556. on c.categoryid=p.categoryid
  557. group by c.categoryid) as b
  558. on pr.CategoryID=b.categoryid and pr.UnitPrice<avr
  559.  
  560. --=================slajd 3===============
  561.  
  562. select ProductName, UnitPrice, (select avg(UnitPrice) from Products) as srednia, (UnitPrice - (select avg(UnitPrice)from Products)) as roznica
  563. from Products
  564.  
  565. select p.productname,c.CategoryName,p.UnitPrice,a.avgs,(p.UnitPrice-a.avgs) as roznica
  566. from Products as p
  567. inner join Categories as c
  568. on c.CategoryID=p.CategoryID
  569. inner join (select c.CategoryID, avg(pr.UnitPrice) as avgs
  570. from Products as pr
  571. inner join Categories as c
  572. on c.CategoryID=pr.CategoryID
  573. group by c.CategoryID) as a
  574. on a.CategoryID=c.CategoryID
  575.  
  576. --=============== slajd 4 ===============================
  577.  
  578. select a.orderid,(a.suma+o.Freight) as suma
  579. from Orders as o
  580. inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
  581. from [order details] as od
  582. group by od.orderid
  583. having od.orderid=10248) as a
  584. on o.OrderID=a.orderid
  585.  
  586. select a.orderid,(a.suma+o.Freight) as suma
  587. from Orders as o
  588. inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
  589. from [order details] as od
  590. group by od.orderid) as a
  591. on o.OrderID=a.orderid
  592.  
  593. select c.Address,c.City,c.CustomerID
  594. from Customers as c
  595. where not exists(select *
  596. from orders as o
  597. where o.CustomerID=c.CustomerID
  598. and datepart(yy,o.OrderDate) = 1997)
  599.  
  600. select distinct a.ProductID,count(o.CustomerID) as clients
  601. from (select od.ProductID,od.OrderID
  602. from [Order Details] as od
  603. group by od.productid,od.orderid) as a
  604. inner join Orders as o
  605. on o.OrderID=a.OrderID
  606. group by a.ProductID
  607. having count(o.CustomerID) > 1
  608.  
  609. --===================== Slajd 5 =============================
  610.  
  611. select e.FirstName,e.LastName,b.sums,b.EmployeeID
  612. from Employees as e
  613. inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
  614. from Orders as o
  615. inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
  616. from [order details] as od
  617. group by od.orderid) as a
  618. on o.OrderID=a.orderid
  619. group by o.EmployeeID) as b on
  620. e.EmployeeID=b.EmployeeID
  621.  
  622. select top 1 e.FirstName,e.LastName,b.sums,b.EmployeeID
  623. from Employees as e
  624. inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
  625. from Orders as o
  626. inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
  627. from [order details] as od
  628. group by od.orderid) as a
  629. on o.OrderID=a.orderid and datepart(yy,o.OrderDate) = '1997'
  630. group by o.EmployeeID) as b on
  631. e.EmployeeID=b.EmployeeID
  632. order by b.sums desc
  633.  
  634. select employ.FirstName,employ.LastName,c.sums
  635. from Employees as employ
  636. inner join (select distinct e.EmployeeID,b.sums
  637. from Employees as e
  638. inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
  639. from Orders as o
  640. inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
  641. from [order details] as od
  642. group by od.orderid) as a
  643. on o.OrderID=a.orderid
  644. group by o.EmployeeID) as b on
  645. e.EmployeeID=b.EmployeeID
  646. left join employees as em
  647. on em.reportsto=e.employeeid
  648. where em.ReportsTo is not null) as c
  649. on c.EmployeeID=employ.EmployeeID
  650.  
  651. select employ.FirstName,employ.LastName,c.sums
  652. from Employees as employ
  653. inner join (select distinct e.EmployeeID,b.sums
  654. from Employees as e
  655. inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
  656. from Orders as o
  657. inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
  658. from [order details] as od
  659. group by od.orderid) as a
  660. on o.OrderID=a.orderid
  661. group by o.EmployeeID) as b on
  662. e.EmployeeID=b.EmployeeID
  663. left join employees as em
  664. on em.reportsto=e.employeeid
  665. where em.ReportsTo is null) as c
  666. on c.EmployeeID=employ.EmployeeID
  667.  
  668. -- zad 4 =======================
  669.  
  670. select employ.FirstName,employ.LastName,c.sums,d.maxdata
  671. from Employees as employ
  672. inner join (select distinct e.EmployeeID,b.sums
  673. from Employees as e
  674. inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
  675. from Orders as o
  676. inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
  677. from [order details] as od
  678. group by od.orderid) as a
  679. on o.OrderID=a.orderid
  680. group by o.EmployeeID) as b on
  681. e.EmployeeID=b.EmployeeID
  682. left join employees as em
  683. on em.reportsto=e.employeeid
  684. where em.ReportsTo is not null) as c
  685. on c.EmployeeID=employ.EmployeeID
  686. inner join (select max(o.OrderDate) as maxdata,ed.EmployeeID
  687. from Orders as o
  688. inner join Employees as ed
  689. on ed.EmployeeID=o.EmployeeID
  690. group by ed.EmployeeID) as d
  691. on d.EmployeeID=employ.EmployeeID
  692.  
  693. select employ.FirstName,employ.LastName,c.sums,d.maxdata
  694. from Employees as employ
  695. inner join (select distinct e.EmployeeID,b.sums
  696. from Employees as e
  697. inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
  698. from Orders as o
  699. inner join (select od.orderid,
  700. sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
  701. from [order details] as od
  702. group by od.orderid) as a
  703. on o.OrderID=a.orderid
  704. group by o.EmployeeID) as b on
  705. e.EmployeeID=b.EmployeeID
  706. left join employees as em
  707. on em.reportsto=e.employeeid
  708. where em.ReportsTo is null) as c
  709. on c.EmployeeID=employ.EmployeeID
  710. inner join (select max(o.OrderDate) as maxdata,ed.EmployeeID
  711. from Orders as o
  712. inner join Employees as ed
  713. on ed.EmployeeID=o.EmployeeID
  714. group by ed.EmployeeID) as d
  715. on d.EmployeeID=employ.EmployeeID
  716.  
  717. --================================================================================================================
  718. --================================================================================================================
  719. select lower ('imie: '+ FirstName+ ' nazwisko: '+substring(LastName,1,2)) as dane
  720. from Employees
  721. SELECT sum (unitprice)
  722. FROM products
  723.  
  724. select orderid , count (*) as  liczba
  725. from [Order Details]
  726. group by orderid
  727.  
  728. select orderid,sum(Quantity)as liczba,sum(quantity*UnitPrice*(1-Discount)) as sums
  729. from [Order Details]
  730. group by orderid
  731. having sum(Quantity)>250
  732. order by sums desc
  733.  
  734. select ShipVia, sum(Freight) as oplata
  735. from Orders
  736. where datepart(yy,OrderDate) between 1996 and 1997
  737. group by ShipVia
  738.  
  739. select em.FirstName,em.LastName,a.sums
  740. from Employees as em
  741. inner join (select e.EmployeeID,
  742. count(*) as sums
  743. from [Order Details] as od
  744. inner join orders as o
  745. on o.orderid=od.orderid
  746. and datepart(yy,o.orderdate)='1997'
  747. inner join employees as e
  748. on e.employeeid=o.employeeid
  749. group by e.employeeid) as a
  750. on a.EmployeeID=em.EmployeeID
  751. order by a.sums desc
  752.  
  753. select e.EmployeeID
  754. from Employees as e
  755. left join Employees as a
  756. on a.ReportsTo=e.EmployeeID
  757. where a.EmployeeID is null
  758.  
  759.  
  760.  
  761. --===========================================================
  762.  
  763. select o.orderid, a.suma+o.Freight as sums, c.CompanyName
  764. from Orders as o
  765. inner join (select od.orderid,isnull(sum(od.quantity*od.UnitPrice*(1-od.Discount)),0) as suma
  766. from [Order Details] as od
  767. group by od.orderid) as a on a.orderid=o.OrderID
  768. inner join Customers as c
  769. on c.CustomerID=o.CustomerID
  770.  
  771.  
  772. select c.CompanyName, o.OrderDate
  773. from Customers as c
  774. left join orders as o
  775. on o.CustomerID=c.CustomerID and year (o.orderdate) = '1997'
  776. where o.orderdate is null
  777.  
  778. select c.CompanyName
  779. from Customers as c
  780. where c.CustomerID not in
  781. (select CustomerID
  782. from Orders as o
  783. where YEAR(o.OrderDate) = '1997')
  784.  
  785. select c.CompanyName
  786. from Customers as c
  787. where not exists
  788. (select CustomerID
  789. from Orders as o
  790. where c.CustomerID=o.CustomerID
  791. and YEAR(o.OrderDate) = '1997')
  792.  
  793. select juv.member_no, count (*)
  794. from juvenile as juv
  795. inner join loanhist as l
  796. on l.member_no = juv.member_no
  797. and datepart(yy,l.in_date) = '2001' and datepart(mm,l.in_date) = '12'
  798. group by juv.member_no
  799. having count(*) >1
  800. order by juv.member_no
  801.  
  802.  
  803. select m.firstname,m.lastname,a.street, a.city, a.state, a.zip, pm.firstname,pm.lastname,ju.member_no,((select count(*) from loanhist as l
  804. where ju.member_no=l.member_no and month(l.in_date) = 12 and year(l.in_date)=2001)
  805. +
  806. (select count(*)
  807. from loanhist as l
  808. where pm.member_no=l.member_no and month(l.in_date) = 12 and year(l.in_date)=2001)
  809. )as ilosc from juvenile as ju
  810. inner join member as m on m.member_no=ju.member_no
  811. inner join adult as a on a.member_no=ju.adult_member_no
  812. inner join member as pm on pm.member_no=ju.adult_member_no
  813. where ((select count(*) from loanhist as l
  814. where ju.member_no=l.member_no and month(l.in_date) = 12 and year(l.in_date)=2001)
  815. +
  816. (select count(*)
  817. from loanhist as l
  818. where ju.adult_member_no=l.member_no and month(l.in_date) = 12 and year(l.in_date)=2001)
  819. ) > 1
  820.  
  821.  
  822. select ch.firstname, ch.lastname, a.street, a.city, a.state, a.zip, par.firstname as 'parent_firstname', par.lastname as 'parent_lastname',
  823.     ((select count(*)
  824.     from loanhist as lh
  825.     where lh.member_no = ch.member_no and month(in_date) = 12 and year(in_date)=2001)
  826.     +
  827.     (select count(*)
  828.     from loanhist as lh
  829.     where lh.member_no = par.member_no and month(in_date) = 12 and year(in_date)=2001))
  830.     as liczba_ksiazek
  831.     from juvenile as j
  832.     join member as ch
  833.     on j.member_no = ch.member_no
  834.     join adult as a
  835.     on j.adult_member_no = a.member_no
  836.     join member as par
  837.     on j.adult_member_no = par.member_no
  838.     where ((select count(*)
  839.     from loanhist as lh
  840.     where lh.member_no = ch.member_no and month(in_date) = 12 and year(in_date)=2001)
  841.     +
  842.     (select count(*)
  843.     from loanhist as lh
  844.     where lh.member_no = par.member_no and month(in_date) = 12 and year(in_date)=2001))>1
  845.  
  846. --==zadanie 4
  847.  
  848. select c.categoryid, sum(od.Quantity*od.UnitPrice*(1-od.Discount)) as wartosc, datepart(mm,o.orderdate) as m,datepart(yy,o.orderdate) as y
  849. from [order details] as od
  850. inner join orders as o
  851. on o.orderid= od.orderid and (datepart(yy,o.orderdate) = 1996 or datepart(yy,o.orderdate) = 1997)
  852. inner join products as p on od.productid=p.productid
  853. inner join categories as c on c.categoryid=p.categoryid
  854. group by c.categoryid,datepart(mm,o.orderdate),datepart(yy,o.orderdate)
  855.     ORDER BY c.Categoryid
  856.  
  857.  
  858. --== Grupa A
  859. -- 1
  860. select c.CategoryName,p.ProductName,p.UnitPrice,
  861. (select avg(unitprice)
  862. from products as prod
  863. where prod.categoryid=c.categoryid) as aver,
  864. (p.unitprice - (select avg(unitprice)
  865. from products as prod
  866. where prod.categoryid=c.categoryid)) as roznica,
  867. (select isnull(sum(od.quantity*od.UnitPrice*(1-od.Discount)),0)
  868. from [Order Details] as od
  869. inner join orders as o on o.orderid=od.orderid and month(o.orderdate) = '3' and year(o.orderdate)='1997'
  870. where od.productid=p.productid) as sums
  871. from products as p
  872. inner join categories as c on c.categoryid=p.categoryid
  873. order by p.productname
  874. -- 2 produkty nie sprzedawane w 1997
  875.  
  876. select p.productid
  877. from Products as p
  878. left join (select distinct od.productid
  879. from [Order Details] as od
  880. inner join orders as o on o.orderid=od.orderid
  881. and year(o.orderdate)=1997) as a on p.ProductID=a.productid
  882. where a.productid is not null
  883.  
  884. -- 3
  885. --Dla każdego pracownika (imie i nazwisko) podaj
  886. -- łączną wartość zamówień obsłużonych
  887. -- przez tego pracownika (z ceną za przesyłkę).
  888. -- Uwzględnij tylko pracowników, którzy mają podwładnych.
  889.  
  890.  
  891. select distinct e.FirstName,e.LastName,a.summs
  892. from Employees as e
  893. left join Employees as em
  894. on e.EmployeeID=em.ReportsTo
  895. inner join (select o.EmployeeID,sum(a.sums+o.Freight) as summs
  896. from Orders as o
  897. inner join (select od.orderid,sum(od.quantity*od.UnitPrice*(1-od.Discount)) as sums
  898. from [order details] as od
  899. group by od.orderid) as a on a.orderid=o.OrderID
  900. group by o.EmployeeID) as a on e.employeeid=a.employeeid
  901. where em.ReportsTo is not null
  902.  
  903. --4
  904. -- Czy są jacyś klienci, którzy nie złożyli żadnego zamówienia w 1997, jeśli tak pokaż
  905. --ich nazwy i dane adresowe (3 wersje - join, in, exists).
  906.  
  907. select c.CompanyName,c.City,c.Address,c.Region,c.PostalCode
  908. from Customers as c
  909. left join (select cc.CustomerID
  910. from Customers as cc
  911. inner join orders as o
  912. on o.CustomerID=cc.CustomerID
  913. where year(o.orderdate) = 1997) as a
  914. on a.CustomerID=c.CustomerID
  915. where a.CustomerID is null
  916.  
  917. select c.CompanyName,c.City,c.Address,c.Region,c.PostalCode
  918. from Customers as c
  919. where c.CustomerID not in (select cc.CustomerID
  920. from Customers as cc
  921. inner join orders as o
  922. on o.CustomerID=cc.CustomerID
  923. where year(o.orderdate) = 1997)
  924.  
  925. select c.CompanyName,c.City,c.Address,c.Region,c.PostalCode
  926. from Customers as c
  927. where not exists (select cc.CustomerID
  928. from Customers as cc
  929. inner join orders as o
  930. on o.CustomerID=cc.CustomerID
  931. where year(o.orderdate) = 1997 and c.CustomerID=cc.customerid)
  932.  
  933.  
  934. --4
  935. -- Podaj listę członków biblioteki (imię, nazwisko)
  936. -- mieszkających w Arizonie (AZ), którzy mają
  937. -- więcej niż dwoje dzieci zapisanych do biblioteki
  938. -- oraz takich, którzy mieszkają w Kalifornii (CA)
  939. -- i mają więcej niż troje dzieci zapisanych do bibliotek.
  940. --  Dla każdej z tych osób podaj liczbę książek
  941. -- przeczytanych (oddanych) przez daną osobę i jej dzieci
  942. -- w grudniu 2001 (użyj operatora union).
  943.  
  944. select j.adult_member_no, count (j.member_no),
  945. (select j.adult_member_no,count(distinct isbn) as suma
  946. from loanhist as l
  947. where l.member_no=j.member_no and year(l.out_date) = 2001 and month(l.out_date)= 12
  948. group by j.adult_member_no)+ (select count(distinct isbn)
  949. from loanhist as l
  950. where l.member_no=a.member_no and  year(l.out_date) = 2001 and month(l.out_date)= 12) as liczba_pozyczonych_ksiazek_dzieci
  951. from adult as a
  952. inner join juvenile as j on j.adult_member_no = a.member_no
  953. where a.state like 'AZ'
  954. group by j.adult_member_no
  955. having count(j.member_no)>=2
  956.  
  957. select a.member_no,(b.suma+(select count(distinct isbn)
  958. from loanhist as l
  959. where l.member_no=a.member_no and  year(l.out_date) = 2001 and month(l.out_date)= 12)) as sums
  960. from adult as a
  961. inner join (select j.adult_member_no,count(distinct isbn) as suma
  962. from loanhist as l
  963. inner join juvenile as j on l.member_no=j.member_no
  964. group by j.adult_member_no) as b on b.adult_member_no=a.member_no
  965. inner join loanhist as l on l.member_no=a.member_no
  966.  
  967. select j.adult_member_no,count(distinct isbn)
  968. from loanhist as l
  969. inner join juvenile as j
  970. on l.member_no=j.member_no
  971. group by j.adult_member_no
  972.  
  973. select j.adult_member_no,count(*)
  974. from juvenile as j
  975. group by j.adult_member_no
  976. --===============
  977. select a.member_no,
  978. (select count(*)
  979. from juvenile as j
  980. where j.adult_member_no = a.member_no) as il_dzieci,
  981. (select(select count(distinct title_no)
  982. from loanhist as l
  983. inner join juvenile as j
  984. on l.member_no=j.member_no
  985. where j.adult_member_no=a.member_no and year(l.in_date)=2001 and month(l.in_date)=12)
  986. +
  987. (select count(distinct title_no)
  988. from loanhist as l
  989. where l.member_no=a.member_no)) as il_ksiazek
  990. from adult as a
  991. --===========
  992. select a.member_no,m.firstname,m.lastname,
  993. (select count(*)
  994. from juvenile as j
  995. where j.adult_member_no = a.member_no) as il_dzieci,
  996. (select count(distinct title_no)
  997. from loanhist as l
  998. inner join juvenile as j
  999. on l.member_no=j.member_no or l.member_no=a.member_no
  1000. where (j.adult_member_no=a.member_no) and year(l.in_date)=2001 and month(l.in_date)=12)
  1001.  as il_ksiazek
  1002. from adult as a
  1003. inner join member as m on m.member_no=a.member_no
  1004. where a.state like 'CA' and (select count(*)
  1005. from juvenile as j
  1006. where j.adult_member_no = a.member_no)>3
  1007. union
  1008. select a.member_no,m.firstname,m.lastname,
  1009. (select count(*)
  1010. from juvenile as j
  1011. where j.adult_member_no = a.member_no) as il_dzieci,
  1012. (select count(distinct title_no)
  1013. from loanhist as l
  1014. inner join juvenile as j
  1015. on l.member_no=j.member_no or l.member_no=a.member_no
  1016. where (j.adult_member_no=a.member_no) and year(l.in_date)=2001 and month(l.in_date)=12)
  1017.  as il_ksiazek
  1018. from adult as a
  1019. inner join member as m on m.member_no=a.member_no
  1020. where a.state like 'AZ' and (select count(*)
  1021. from juvenile as j
  1022. where j.adult_member_no = a.member_no)>2
  1023. --============ wersja B
  1024. -- ad 1
  1025. select m.FirstName,m.LastName,j.birth_date,a.city,a.street,a.state,par.firstname,par.lastname,
  1026. (select count(distinct isbn)
  1027. from loan as l
  1028. where l.member_no=j.member_no) as "il_ksiazek"
  1029. from juvenile as j
  1030. inner join member as m on m.member_no=j.member_no
  1031. inner join adult as a on a.member_no=j.adult_member_no
  1032. inner join member as par on par.member_no=j.adult_member_no
  1033. --ad 2
  1034.  
  1035. select empl.FirstName,empl.LastName,b.suma
  1036. from (select e.EmployeeID,sum(a.worth+o.Freight) as suma
  1037. from orders as o
  1038. inner join (select od.orderid ,sum(od.quantity*od.UnitPrice*(1-od.Discount)) as worth
  1039. from [Order Details] as od
  1040. group by od.orderid) as a on a.orderid=o.orderid
  1041. inner join Employees as e on e.EmployeeID=o.EmployeeID
  1042. group by e.EmployeeID) as b
  1043. left join Employees as em on b.EmployeeID=em.ReportsTo
  1044. inner join Employees as empl on b.EmployeeID=empl.EmployeeID
  1045. where em.ReportsTo is null
  1046. -- ad 3
  1047. -- join
  1048. select c.CompanyName
  1049. from Customers as c
  1050. left join (select cus.CompanyName
  1051. from customers as cus
  1052. inner join Orders as o on o.CustomerID=cus.CustomerID
  1053. where year(o.orderdate)=1997) as a on a.CompanyName=c.CompanyName
  1054. where a.CompanyName is null
  1055. -- in
  1056. select c.CompanyName
  1057. from Customers as c
  1058. where c.companyname not in (select cus.CompanyName
  1059. from customers as cus
  1060. inner join Orders as o on o.CustomerID=cus.CustomerID
  1061. where year(o.orderdate)=1997)
  1062.  
  1063. select c.CompanyName
  1064. from Customers as c
  1065. where not exists (select cus.CompanyName
  1066. from customers as cus
  1067. inner join Orders as o on o.CustomerID=cus.CustomerID
  1068. where year(o.orderdate)=1997 and c.CompanyName=cus.companyname)
  1069.  
  1070. --ad 4
  1071.  
  1072. select m.FirstName,m.LastName,m.member_no,
  1073. (select count(*)
  1074. from juvenile as j where j.adult_member_no=m.member_no) as dzieci,
  1075. (select count(distinct title_no)+(select count(distinct title_no)
  1076. from loanhist as l
  1077. where l.member_no=m.member_no) as ksiazki
  1078. from loanhist as l
  1079. inner join juvenile as jj
  1080. on jj.member_no=l.member_no
  1081. where jj.adult_member_no=m.member_no)
  1082. from member as m
  1083. inner join adult as a
  1084. on a.member_no=m.member_no and (((select count(*)
  1085. from juvenile as j where j.adult_member_no=m.member_no)>2 and a.state like 'AZ')
  1086. or ((select count(*)
  1087. from juvenile as j where j.adult_member_no=m.member_no)>3 and a.state like 'CA'))
  1088.  
  1089. --dla kazdego pracownika wyznacz najczesciej obslugiwana kategorie 1997
  1090.  
  1091. select e.employeeid,p.CategoryID,count(*) as suma
  1092. from [order details] as od
  1093. inner join products as p
  1094. on od.productid=p.productid
  1095. inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
  1096. inner join employees as e on e.employeeid=o.employeeid
  1097. group by e.employeeid,p.categoryid
  1098. order by suma desc
  1099.  
  1100. select top 1 p.CategoryID,count(*) as suma
  1101. from [order details] as od
  1102. inner join products as p
  1103. on od.productid=p.productid
  1104. inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
  1105. inner join employees as e on e.employeeid=o.employeeid
  1106. group by e.employeeid,p.categoryid
  1107. order by suma desc
  1108.  
  1109. select e.EmployeeID,
  1110. (select top 1 p.CategoryID
  1111. from products as p
  1112. inner join [order details] as od
  1113. on od.productid=p.productid
  1114. inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
  1115. where o.employeeid=e.employeeid
  1116. group by p.categoryid
  1117. ORDER BY count(*) desc),
  1118. (select top 1 count(*) as suma
  1119. from [order details] as od
  1120. inner join products as p
  1121. on od.productid=p.productid
  1122. inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
  1123. where o.employeeid=e.employeeid
  1124. group by p.categoryid
  1125. order by suma desc)
  1126. from Employees as e
  1127.  
  1128. select e.EmployeeID,
  1129. (select TOP 1 P.CategoryID
  1130. from [order details] as od
  1131. inner join products as p
  1132. on od.productid=p.productid
  1133. inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
  1134. where o.employeeid=e.employeeid
  1135. group by p.categoryid
  1136. order by count(*) desc)
  1137. from Employees as e
  1138.  
  1139. select c.CompanyName,a.categoryid
  1140. from Customers as c
  1141. inner join
  1142. (select top 1 cus.customerid,p.categoryid
  1143. from [order details] as od
  1144. inner join orders as o on
  1145. o.orderid=od.orderid and year(o.orderdate)=1997
  1146. inner join products as p on p.productid=od.productid
  1147. inner join customers as cus on o.customerid = cus.customerid
  1148. group by cus.customerid,p.categoryid
  1149. order by count(*) desc) as a on a.CustomerID=c.CustomerID
  1150. group by c.CompanyName
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