daily pastebin goal
1%
SHARE
TWEET

Untitled

a guest Dec 11th, 2018 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT CategoryName,
  2.        ProductName,
  3.        UnitPrice,
  4.        (SELECT AVG(UnitPrice) FROM Products as InnP WHERE InnP.CategoryID = OutP.CategoryID)             as CategoryAverage,
  5.        UnitPrice - (SELECT AVG(UnitPrice) FROM Products as InnP WHERE InnP.CategoryID = OutP.CategoryID) as Difference,
  6.        cast((SELECT ISNULL(SUM(UnitPrice * Quantity * (1 - Discount)), 0)
  7.              FROM [Order Details]
  8.                     JOIN Orders
  9.                          ON Orders.OrderID = [Order Details].OrderID AND YEAR(OrderDate) = 1997 AND MONTH(OrderDate) = 3
  10.              WHERE [Order Details].ProductID = OutP.ProductID) as numeric(10, 2))                        as SalesMarch1997
  11. FROM Products as OutP
  12.        JOIN Categories
  13.             ON OutP.CategoryID = Categories.CategoryID
  14.  
  15. Zad 2:
  16.  
  17. SELECT superior.FirstName,
  18.        superior.LastName,
  19.        (SELECT SUM(UnitPrice * Quantity * (1 - Discount))
  20.         FROM [Order Details]
  21.                JOIN Orders ON Orders.OrderID = [Order Details].OrderID
  22.         WHERE Orders.EmployeeID = superior.EmployeeID) +
  23.        (SELECT SUM(Freight) FROM Orders WHERE Orders.EmployeeID = superior.EmployeeID) as totalSales
  24. FROM Employees as superior
  25. WHERE EXISTS(SELECT * FROM Employees as inferior WHERE inferior.ReportsTo = superior.EmployeeID)
  26.  
  27. Zad 3:
  28.  
  29. --JOIN
  30. SELECT CompanyName,
  31.        ContactName,
  32.        ContactTitle,
  33.        Address,
  34.        City,
  35.        Region,
  36.        PostalCode,
  37.        Country
  38. FROM Customers
  39.        LEFT JOIN Orders
  40.                  ON Orders.CustomerID = Customers.CustomerID AND YEAR(OrderDate) = 1997
  41. WHERE OrderDate IS NULL
  42.  
  43. --IN
  44. SELECT CompanyName,
  45.        ContactName,
  46.        ContactTitle,
  47.        Address,
  48.        City,
  49.        Region,
  50.        PostalCode,
  51.        Country
  52. FROM Customers
  53. WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 1997)
  54.  
  55. --EXIST
  56. SELECT CompanyName,
  57.        ContactName,
  58.        ContactTitle,
  59.        Address,
  60.        City,
  61.        Region,
  62.        PostalCode,
  63.        Country
  64. FROM Customers
  65. WHERE NOT EXISTS(SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID AND YEAR(OrderDate) = 1997)
  66.  
  67. Zad 4:
  68.  
  69. SELECT firstname,
  70.        lastname,
  71.        (SELECT COUNT(*)
  72.         FROM loanhist
  73.         WHERE YEAR(in_date) = 2001
  74.           AND loanhist.member_no = adult.member_no
  75.            OR loanhist.member_no IN (SELECT member_no FROM juvenile WHERE adult_member_no = adult.member_no)
  76.        ) as booksReadIn2001
  77. FROM member
  78.        JOIN adult
  79.             ON adult.member_no = member.member_no
  80. WHERE (state = 'AZ' AND (SELECT COUNT(*) FROM juvenile WHERE adult_member_no = adult.member_no) > 2)
  81.    OR (state = 'CA' AND (SELECT COUNT(*) FROM juvenile WHERE adult_member_no = adult.member_no) > 3)
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