Facebook
From Corrupt Matamata, 5 Years ago, written in SQL.
Embed
Download Paste or View Raw
Hits: 237
  1. --USE NORTHWND
  2. --zad1
  3. /*CREATE VIEW [Products from USA] AS
  4. SELECT Products.productname
  5. FROM Products
  6. INNER JOIN Suppliers
  7. ON Products.supplierID = Suppliers.supplierID
  8. WHere suppliers.country='USA'*/
  9.  
  10. --zad2
  11. /*SELECT * FROM Customers WHERE Customers.CustomerID Not in (SELECT CustomerID from Orders)*/
  12.  
  13. --zad3
  14. --SELECT e.firstname, x.lastname from Employees e cross join Employees x
  15.  
  16. --zad4
  17. /*SELECT c.companyname, p.productname from customers c
  18. JOIN Orders o
  19. ON c.CustomerID=o.CustomerID
  20. JOIN [Order Details] od
  21. ON od.OrderID = o.OrderID
  22. JOIN Products p
  23. On p.ProductID = od.ProductID*/
  24.  
  25. --zad5
  26. /*SELECT c.companyname, SUM(od.UnitPrice*od.quantity)AS
  27. "naleznosc" FROM Customers c
  28. JOIN orders o
  29. ON c.CustomerID = o.CustomerID
  30. JOIN [Order Details] od
  31. ON od.OrderID = o.OrderID
  32. JOIN Products p
  33. ON p.ProductID = od.ProductID
  34. GROUP BY c.CompanyName
  35. ORDER BY "naleznosc" DESC*/
  36.  
  37. --zad6
  38. /*SELECT c.companyname from customers c
  39. LEFT OUTER JOIN orders o ON c.customerid = o.customerid
  40. WHERE o.customerid is null*/
  41.  
  42. --zad7
  43. /*SELECT cat.categoryname, SUM(quantity) AS "ilosc" FROM
  44. Products p
  45. JOIN Categories cat on p.CategoryID = cat.CategoryID
  46. JOIN [Order Details] od on od.ProductID=p.ProductID
  47. JOIN Orders o on o.OrderID=od.OrderID
  48. GROUP BY cat.CategoryName*/
  49.  
  50. --zad8
  51. /*UPDATE [Order Details] set unitprice = UnitPrice*1.1 WHERE (SELECT count(*) FROM(Select count(*)AS cc from orders o inner join [Order Details] od on o.OrderID=od.OrderID
  52. group by o.CustomerID) AS INN)>=10*/
  53.  
  54.  
  55. --zad9
  56. /*SELECT c.companyname, p.productname, e.firstname, e.lastname INTO "Customer Product Employee1" FROM Customers c
  57. Join ORDERS o On c.CustomerID = o.CustomerID
  58. JOIN[Order Details] od on od.OrderID=o.OrderID
  59. JOIN Products p on p.ProductID = p.ProductID
  60. JOIN Employees e  on e.EmployeeID = o.EmployeeID*/
  61.  
  62.  
  63.  
  64.  
  65.  
  66.