select productid,OrderID,sum(Quantity) as ord
from [Order Details]
where OrderID<10280
group by ProductID, OrderID
with rollup
order by ProductID,OrderID

-------------zadania domowe----------------

--------------slajd 1-----------------------

select top 10 with ties OrderID, sum(UnitPrice*(1-Discount)*Quantity) as 'networth'
from [Order Details]
group by OrderID
order by networth desc

------------slajd 2--------------------------

select productid, sum(quantity) as ilosc
from [Order Details]
group by ProductID
having ProductID <3

select OrderID, sum(quantity*UnitPrice*(1-Discount)) as wartosc
from [Order Details]
group by OrderID
having sum(Quantity) > 250

-------------------slajd 3-------------------

select EmployeeID,count(orderid) as [liczba obslugiwanych zamowien]
from Orders
group by EmployeeID

select ShipVia, sum(Freight)
from Orders
group by ShipVia

select ShipVia, sum(Freight)
from Orders
where datepart(yy,ShippedDate)>1995 and datepart(yy,ShippedDate)<1998
group by ShipVia

select EmployeeID,datepart(yy,OrderDate) as rok,datepart(month,OrderDate) as miesiac, count(OrderID) as [liczba zamowien]
from Orders
group by EmployeeID,datepart(yy,OrderDate),datepart(month,OrderDate)

select CategoryID, min(UnitPrice) as min, max(UnitPrice) as max
from Products
group by CategoryID


--==================================LABY 44444444===========================================
use joindb
select * from Produce

select * from Buyers

select * from Sales

USE joindb
SELECT buyer_name, sales.buyer_id, Sales.qty
FROM buyers, sales
WHERE buyers.buyer_id = sales.buyer_id
GO

USE northwind
SELECT DISTINCT companyname, orderdate
FROM orders
INNER JOIN customers
ON orders.customerid = customers.customerid
WHERE orderdate > ‘3/1/98’
GO

SELECT companyname, customers.customerid, orderdate
FROM customers
LEFT OUTER JOIN orders
ON customers.customerid = orders.customerid
WHERE OrderDate IS NULL


--===========================slajd 17======================

select p.ProductName,p.UnitPrice,s.Address
from Products AS p inner join Suppliers AS [s]
    on p.SupplierID = s.SupplierID
where p.UnitPrice between 20 and 30

select p.ProductName,p.UnitsInStock,s.CompanyName
from Products AS p inner join Suppliers AS s
    on p.SupplierID=s.SupplierID
where s.CompanyName = 'Tokyo Traders' and p.Discontinued=0

--Czy są jacyś klienci którzy nie złożyli żadnego zamówienia w 1997
--roku, jeśli tak to pokaż ich dane adresowe
select c.Address,o.OrderDate
from Customers AS c left outer join Orders AS o
    on c.CustomerID=o.CustomerID
and year(OrderDate)=1997
where OrderID is null

select s.CompanyName, s.Phone,p.UnitsInStock
from Suppliers AS s inner join Products AS p
    on s.SupplierID=p.SupplierID
where p.UnitsInStock = 0 and p.Discontinued=0

--==============================slajd 18=====================

select m.FirstName,m.LastName,j.birth_date
from member AS m inner join juvenile AS j
    on j.member_no = m.member_no

select distinct t.title
from title AS t inner join loan AS l
    on l.title_no=t.title_no

select l.in_date,l.fine_paid,datepart(dd,l.in_date)-datepart(dd,l.due_date) -------------- coś źle
from title AS t inner join loanhist AS l
    on l.title_no=t.title_no
where t.title='Tao Teh King' and l.fine_paid is not null

select r.isbn,r.member_no
from reservation AS r inner join member as m on m.member_no=r.member_no
and m.firstname ='Stephen' and m.middleinitial like 'A' and m.lastname like 'Graff'

--==========================slajd 23======================================

select p.ProductName, p.UnitPrice,s.Address,c.CategoryName
from Products AS p inner join Categories AS c
    on p.CategoryID = c.CategoryID
    inner join Suppliers AS s on p.SupplierID = s.SupplierID
where p.UnitPrice between 20 and 30 and c.CategoryName like 'Meat/Poultry'

select p.ProductName, p.UnitPrice,s.CompanyName,c.CategoryName
from Products AS p inner join Categories AS c
    on p.CategoryID = c.CategoryID
    inner join Suppliers AS s on p.SupplierID = s.SupplierID
where c.CategoryName like 'Confections'

select c.ContactName,c.Phone, o.OrderDate,s.CompanyName
from Customers AS c inner join Orders as o
    on o.CustomerID = c.CustomerID
inner join Shippers as s on s.ShipperID = o.ShipVia
where datepart(yy,o.OrderDate) = 1997 and s.CompanyName like 'United Package'

select distinct c.ContactName,c.Phone, cat.CategoryName
from Orders AS o inner join [Order Details] as od
    on o.OrderID=od.OrderID  --                                   TRZEBA JESZCZE ORDER DETAILS
    --                                                           BO TO NIE JEST TAK ŻE WSZYSTKO Z ORDERID BYŁO ZAMOWIONE CZY COS TAKIEGO
inner join Products as p on od.ProductID = p.ProductID
inner join Categories as cat on cat.CategoryID=p.CategoryID
inner join Customers as c on c.CustomerID= o.CustomerID
where cat.CategoryName like'Confections'

--===========================slajd 24===========================

select m.firstname,m.lastname,j.birth_date,a.city,a.street
from juvenile as j inner join member as m on m.member_no=j.member_no
inner join adult as a on a.member_no=j.adult_member_no

select m.firstname,m.lastname,j.birth_date,a.city,a.street,b.firstname,b.lastname
from juvenile as j inner join member as m on m.member_no=j.member_no
inner join adult as a on a.member_no=j.adult_member_no
--inner join member as b on j.adult_member_no = b.member_no
inner join member as b on a.member_no = b.member_no


--=========================================================

SELECT a.buyer_id AS buyer1, a.prod_id
,b.buyer_id AS buyer2
FROM sales AS a
INNER JOIN sales AS b
ON a.prod_id = b.prod_id
where a.buyer_id <> b.buyer_id

USE northwind
SELECT a.employeeid, LEFT(a.lastname,10) AS name
,a.title AS title
,b.employeeid, b.lastname AS name
,LEFT(b.title,10) AS title
FROM employees AS a
INNER JOIN employees AS b
ON a.title = b.title
WHERE a.employeeid < b.employeeid

use library
--=====================SLAJD 29 =====================

select a.LastName,a.FirstName,a.ReportsTo,b.EmployeeID,b.FirstName,b.LastName
from Employees as a inner join Employees as b
on a.ReportsTo = b.EmployeeID

--!!!!!!!!!Napisz polecenie, które wyświetla pracowników, którzy nie mają
--podwładnych (baza northwind)

select a.EmployeeID,b.EmployeeID,a.ReportsTo,b.ReportsTo from Employees as a
left join Employees as b
    on a.EmployeeID=b.ReportsTo
where b.ReportsTo is null

select a.street, a.City,a.member_no
from adult as a
inner join juvenile as j on a.member_no=j.adult_member_no
and datepart(yy,j.birth_date) < 1996

select distinct a.street, a.City,a.member_no
from adult as a
inner join juvenile as j on a.member_no=j.adult_member_no
and datepart(yy,j.birth_date) < 1996
left outer join loan as l on a.member_no=l.member_no
where l.member_no is null

--============================slajd 31/32========================

SELECT (a.firstname + ' ' + a.lastname) AS name,b.street,b.city,b.state,b.zip
from adult as b inner join member as a on a.member_no = b.member_no

select c.isbn,c.copy_no,c.on_loan,t.title,i.translation,i.cover
from copy as c inner join item as i on c.isbn=i.isbn
inner join title as t on c.title_no=t.title_no
where c.isbn = 1 or c.isbn = 500 or c.isbn = 1000

select m.member_no,m.firstname,m.lastname,r.isbn,r.log_date
from member as m inner join reservation as r on m.member_no=r.member_no

select a.member_no, count(j.member_no)
from adult as a inner join juvenile as j on a.member_no=j.adult_member_no
inner join member as m on m.member_no=a.member_no
where a.state like 'AZ'
group by a.member_no
having count(j.member_no)>2
union all
select a.member_no, count(j.member_no)
from adult as a inner join juvenile as j on a.member_no=j.adult_member_no
where a.state like 'CA'
group by a.member_no
having count(j.member_no)>3


select r.member_no,m.firstname,m.lastname,r.counts
from(select a.member_no, count(j.member_no) as counts
from adult as a inner join juvenile as j on a.member_no=j.adult_member_no
where a.state like 'AZ'
group by a.member_no
having count(j.member_no)>2) as r
inner join member as m on m.member_no=r.member_no
union
select r.member_no,m.firstname,m.lastname,r.counts
from(select a.member_no, count(j.member_no) as counts
from adult as a inner join juvenile as j on a.member_no=j.adult_member_no
where a.state like 'CA'
group by a.member_no
having count(j.member_no)>3) as r
inner join member as m on m.member_no=r.member_no

--jak bez uniona?



--ostatnie z 31 sladu done by marcjan

select m.firstname,m.lastname,count(*)
from member as m join adult as a on m.member_no=a.member_no
inner join juvenile as j on j.adult_member_no=m.member_no
where a.state like 'AZ'
group by m.firstname,m.lastname-- i tu mamy błąd bo nie można grupować po nieunikalnych kluczach
--powinnismy grupować po member_no
having count(*)>2
-- JAK BEZ UNIONA WAAAAAAAAAAAAAAAŻNE
select  m.firstname,m.lastname,count(*)
from member as m join adult as a on m.member_no=a.member_no
inner join juvenile as j on j.adult_member_no=m.member_no
group by m.firstname,m.lastname,m.member_no,a.state
having (count(*)>2 and state like 'az') or (count(*)>2 and state like 'ca')
--=============================zad.dom slajd 1==============

select od.OrderID,o.CustomerID,sum (od.Quantity)as 'quantity'
from Orders as o inner join [Order Details] as od
    on o.OrderID=od.OrderID
group by od.orderid,o.customerid
order by od.orderid

select od.OrderID,o.CustomerID, sum (od.Quantity)as 'quantity'
from Orders as o inner join [Order Details] as od
    on o.OrderID=od.OrderID
group by od.orderid,o.customerid
having sum (od.quantity) > 250

select od.OrderID,o.CustomerID,sum(od.unitprice*od.quantity*(1-od.discount)) as 'worth'
from Orders as o inner join [Order Details] as od
    on o.OrderID=od.OrderID
group by od.orderid,o.customerid

select od.OrderID,o.CustomerID,sum(od.unitprice*od.quantity*(1-od.discount)) as 'worth'
from Orders as o inner join [Order Details] as od
    on o.OrderID=od.OrderID
group by od.orderid,o.customerid
having sum (od.quantity) > 250

select od.OrderID,o.CustomerID,sum(od.unitprice*od.quantity*(1-od.discount)) as 'worth',
e.firstname,e.lastname
from Orders as o inner join [Order Details] as od
    on o.OrderID=od.OrderID
    inner join employees as e on e.employeeid = o.employeeid
group by od.orderid,o.customerid,e.firstname,e.lastname
having sum (od.quantity) > 250
order by od.orderid

select od.OrderID,o.CustomerID,od.worth,e.FirstName,e.LastName
from (select OrderID,sum(UnitPrice*Quantity*(1-Discount)) as worth,sum(quantity) as quantity
from [Order Details] group by OrderID) as od inner join orders as o
    on o.OrderID=od.OrderID
    inner join employees as e on e.employeeid = o.employeeid
where od.quantity > 250
order by od.OrderID


--============================ zad. dom slajd 2 ===========================

select c.categoryid, sum(a.suma) as ilosc
from (select ProductID,sum(Quantity) as suma from [order details] group by productid) as a
inner join Products as p on a.ProductID=p.ProductID
inner join Categories as c on c.CategoryID=p.CategoryID
group by c.CategoryID
order by ilosc desc

select c.categoryid, sum(a.wartosc) as worth
from (select ProductID,sum(Quantity*UnitPrice*(1-Discount)) as wartosc from [order details] group by productid) as a
inner join Products as p on a.ProductID=p.ProductID
inner join Categories as c on c.CategoryID=p.CategoryID
group by c.CategoryID
order by worth desc
-- zrobić podpunkt b

select a.OrderID,a.wartosc+b.freight
from (select OrderID,sum(Quantity*UnitPrice*(1-Discount)) as wartosc
from [order details] group by orderid) as a
inner join Orders as b on a.OrderID=b.OrderID

--============================ zad. dom slajd 3 ===========================

select b.companyname,a.ShipVia,a.liczba
from (select ShipVia,count (OrderID) as liczba
from Orders
where datepart(yy,ShippedDate) = 1997
group by shipvia) as a
inner join Shippers as b on b.ShipperID=a.ShipVia

select top 1 b.companyname,a.liczba
from (select ShipVia,count (OrderID) as liczba
from Orders
where datepart(yy,ShippedDate) = 1997
group by shipvia) as a
inner join Shippers as b on b.ShipperID=a.ShipVia
order by a.liczba desc

select e.FirstName,e.LastName,a.razem
from (select o.EmployeeID,sum(od.suma) as razem
from(select orderid,sum(Quantity*UnitPrice*(1-Discount)) as suma
from [Order Details]
group by orderid) as od
inner join Orders as o on o.OrderID=od.orderid
group by o.EmployeeID) as a
inner join Employees as e on a.EmployeeID=e.EmployeeID

select e.FirstName,e.LastName,a.liczba
from(select EmployeeID,count (OrderID) as liczba
from Orders where datepart(yy,OrderDate)= 1997
group by EmployeeID) as a
inner join Employees as e on a.EmployeeID=e.EmployeeID
order by a.liczba desc

select e.FirstName,e.LastName,a.liczba
from(select EmployeeID,count (OrderID) as liczba
from Orders where datepart(yy,OrderDate)= 1997
group by EmployeeID) as a
inner join Employees as e on a.EmployeeID=e.EmployeeID
order by a.liczba desc

select top 1 e.FirstName,e.LastName,a.suma as razem
from(select o.EmployeeID, sum(od.Quantity*od.UnitPrice*(1-od.Discount)) as suma
from [order details] as od
inner join orders as o on od.orderid=o.orderid
 and datepart(yy,o.OrderDate)= 1997
group by o.EmployeeID) as a
inner join Employees as e on a.EmployeeID=e.EmployeeID
order by a.suma desc

--============================ zad. dom slajd 4 ===========================

select e.FirstName,e.LastName,a.suma as razem
from(select o.EmployeeID, sum(od.Quantity*od.UnitPrice*(1-od.Discount)) as suma
from [order details] as od
inner join orders as o on od.orderid=o.orderid
 and datepart(yy,o.OrderDate)= 1997
group by o.EmployeeID) as a
inner join Employees as e on a.EmployeeID=e.EmployeeID and e.ReportsTo is not null
order by a.suma desc

select c.firstname,c.lastname,b.EmployeeID,b.razem
from(select a.EmployeeID,a.suma as razem
from(select o.EmployeeID, sum(od.Quantity*od.UnitPrice*(1-od.Discount)) as suma
from [order details] as od
inner join orders as o on od.orderid=o.orderid
 and datepart(yy,o.OrderDate)= 1997
group by o.EmployeeID) as a
left join Employees as e on a.EmployeeID=e.ReportsTo
where e.EmployeeID is null) as b
inner join Employees as c on b.EmployeeID=c.EmployeeID


-- zajęc ciąg dalszy

select (firstname + ' ' +lastname) as name , city , postalcode
from Employees union all
select companyname,city,postalcode
from customers


/*
============================================================================
================================       =====================================
==============================   Laby 4   ==================================
================================       =====================================
============================================================================
 */

USE northwind
SELECT productname, UnitPrice
,( SELECT AVG(UnitPrice) FROM products) AS average
,UnitPrice-(SELECT AVG(UnitPrice) FROM products) AS difference
FROM products

USE northwind
SELECT productname, unitprice,CategoryID
,( SELECT AVG(unitprice)
FROM products as p_wew
WHERE p_zew.categoryid = p_wew.categoryid ) AS
average
FROM products as p_zew

select p.ProductName,p.UnitPrice,p.CategoryID,s.sr
from Products p join
(select CategoryID,avg(UnitPrice) sr
from Products
group by CategoryID) s on p.CategoryID=s.CategoryID
order by 1

--== EXIST lub notEXIST

SELECT lastname, employeeid
FROM employees AS e
WHERE EXISTS (SELECT * FROM orders AS o
WHERE e.employeeid = o.employeeid
AND o.orderdate = '9/5/97')

SELECT DISTINCT lastname, e.employeeid
FROM orders AS o
INNER JOIN employees AS e
ON o.employeeid = e.employeeid
WHERE o.orderdate = '9/5/1997'

SELECT lastname, employeeid
FROM employees AS e
WHERE not EXISTS (SELECT * FROM orders AS o
WHERE e.employeeid = o.employeeid
AND o.orderdate = '9/5/97')

SELECT DISTINCT lastname, e.employeeid
FROM orders AS o
right JOIN employees AS e
ON o.employeeid = e.employeeid
and o.orderdate = '9/5/1997'
where o.OrderID is null


-- in =================================

SELECT lastname, employeeid
FROM employees AS e
WHERE employeeid IN (SELECT employeeid FROM orders AS o
WHERE o.orderdate = '9/5/97')




SELECT productname, unitprice
,( SELECT AVG(unitprice) FROM products as p_wew
WHERE p_zewn.categoryid = p_wew.categoryid ) AS
average
FROM products as p_zewn


select e.LastName,e.EmployeeID
from Employees as e
inner join Orders as o on o.EmployeeID=e.EmployeeID
where o.OrderDate = '9/5/1997'

--=======================                ============================
--====================    Zadania domowe   ==========================
--=======================       Slajd 1     =========================


select c.ContactName,c.Phone
from (select distinct o.CustomerID
from Orders as o
inner join Shippers as s
on o.ShipVia=s.ShipperID
where datepart(yy,o.ShippedDate)= '1997'
and s.CompanyName like 'United Package')
as a
inner join Customers as c on c.CustomerID=a.CustomerID

select c.ContactName,c.Phone
from Customers as c
inner join Orders as o
on o.CustomerID=c.CustomerID and datepart(yy,o.ShippedDate)= '1997'
inner join Shippers as s on o.ShipVia=s.ShipperID and s.CompanyName like 'United Package'

select cus.ContactName,cus.Phone
from(select distinct c.CustomerID
from (select o.CustomerID
from orders as o inner join
(select od.OrderID
from [Order Details] as od
inner join (select p.productid
from Products as p
where categoryid not in (select categoryid from categories as c
where c.CategoryName like 'Confections'))
as a on od.productid=a.productid) as b
on o.OrderID=b.OrderID) as d
inner join Customers as c
on c.CustomerID=d.CustomerID) as cu
inner join Customers as cus on
cu.CustomerID=cus.CustomerID

--========================== slajd 2 ===========================

select distinct o.productid,quantity
from [order details] as o
where quantity = (select max(quantity)
from [Order Details] as od
where od.productid=o.productid)

select distinct o.productid,o.UnitPrice
from [order details] as o
where unitprice < (select avg(unitprice)
from [Order Details])

select pr.productid,pr.unitprice,b.avr
from Products as pr
inner join (select c.categoryid,avg(p.UnitPrice) as avr --zrobic nie korzystajc z unitprice
from products as p
inner join categories as c
on c.categoryid=p.categoryid
group by c.categoryid) as b
on pr.CategoryID=b.categoryid and pr.UnitPrice<avr

--=================slajd 3===============

select ProductName, UnitPrice, (select avg(UnitPrice) from Products) as srednia, (UnitPrice - (select avg(UnitPrice)from Products)) as roznica
from Products

select p.productname,c.CategoryName,p.UnitPrice,a.avgs,(p.UnitPrice-a.avgs) as roznica
from Products as p
inner join Categories as c
on c.CategoryID=p.CategoryID
inner join (select c.CategoryID, avg(pr.UnitPrice) as avgs
from Products as pr
inner join Categories as c
on c.CategoryID=pr.CategoryID
group by c.CategoryID) as a
on a.CategoryID=c.CategoryID

--=============== slajd 4 ===============================

select a.orderid,(a.suma+o.Freight) as suma
from Orders as o
inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
from [order details] as od
group by od.orderid
having od.orderid=10248) as a
on o.OrderID=a.orderid

select a.orderid,(a.suma+o.Freight) as suma
from Orders as o
inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
from [order details] as od
group by od.orderid) as a
on o.OrderID=a.orderid

select c.Address,c.City,c.CustomerID
from Customers as c
where not exists(select *
from orders as o
where o.CustomerID=c.CustomerID
and datepart(yy,o.OrderDate) = 1997)

select distinct a.ProductID,count(o.CustomerID) as clients
from (select od.ProductID,od.OrderID
from [Order Details] as od
group by od.productid,od.orderid) as a
inner join Orders as o
on o.OrderID=a.OrderID
group by a.ProductID
having count(o.CustomerID) > 1

--===================== Slajd 5 =============================

select e.FirstName,e.LastName,b.sums,b.EmployeeID
from Employees as e
inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
from Orders as o
inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
from [order details] as od
group by od.orderid) as a
on o.OrderID=a.orderid
group by o.EmployeeID) as b on
e.EmployeeID=b.EmployeeID

select top 1 e.FirstName,e.LastName,b.sums,b.EmployeeID
from Employees as e
inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
from Orders as o
inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
from [order details] as od
group by od.orderid) as a
on o.OrderID=a.orderid and datepart(yy,o.OrderDate) = '1997'
group by o.EmployeeID) as b on
e.EmployeeID=b.EmployeeID
order by b.sums desc

select employ.FirstName,employ.LastName,c.sums
from Employees as employ
inner join (select distinct e.EmployeeID,b.sums
from Employees as e
inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
from Orders as o
inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
from [order details] as od
group by od.orderid) as a
on o.OrderID=a.orderid
group by o.EmployeeID) as b on
e.EmployeeID=b.EmployeeID
left join employees as em
on em.reportsto=e.employeeid
where em.ReportsTo is not null) as c
on c.EmployeeID=employ.EmployeeID

select employ.FirstName,employ.LastName,c.sums
from Employees as employ
inner join (select distinct e.EmployeeID,b.sums
from Employees as e
inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
from Orders as o
inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
from [order details] as od
group by od.orderid) as a
on o.OrderID=a.orderid
group by o.EmployeeID) as b on
e.EmployeeID=b.EmployeeID
left join employees as em
on em.reportsto=e.employeeid
where em.ReportsTo is null) as c
on c.EmployeeID=employ.EmployeeID

-- zad 4 =======================

select employ.FirstName,employ.LastName,c.sums,d.maxdata
from Employees as employ
inner join (select distinct e.EmployeeID,b.sums
from Employees as e
inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
from Orders as o
inner join (select od.orderid,sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
from [order details] as od
group by od.orderid) as a
on o.OrderID=a.orderid
group by o.EmployeeID) as b on
e.EmployeeID=b.EmployeeID
left join employees as em
on em.reportsto=e.employeeid
where em.ReportsTo is not null) as c
on c.EmployeeID=employ.EmployeeID
inner join (select max(o.OrderDate) as maxdata,ed.EmployeeID
from Orders as o
inner join Employees as ed
on ed.EmployeeID=o.EmployeeID
group by ed.EmployeeID) as d
on d.EmployeeID=employ.EmployeeID

select employ.FirstName,employ.LastName,c.sums,d.maxdata
from Employees as employ
inner join (select distinct e.EmployeeID,b.sums
from Employees as e
inner join (select o.employeeid,sum(a.suma+o.Freight) as sums
from Orders as o
inner join (select od.orderid,
sum(od.UnitPrice*od.Quantity*(1-od.Discount)) as suma
from [order details] as od
group by od.orderid) as a
on o.OrderID=a.orderid
group by o.EmployeeID) as b on
e.EmployeeID=b.EmployeeID
left join employees as em
on em.reportsto=e.employeeid
where em.ReportsTo is null) as c
on c.EmployeeID=employ.EmployeeID
inner join (select max(o.OrderDate) as maxdata,ed.EmployeeID
from Orders as o
inner join Employees as ed
on ed.EmployeeID=o.EmployeeID
group by ed.EmployeeID) as d
on d.EmployeeID=employ.EmployeeID

--================================================================================================================
--================================================================================================================
select lower ('imie: '+ FirstName+ ' nazwisko: '+substring(LastName,1,2)) as dane
from Employees
SELECT sum (unitprice)
FROM products

select orderid , count (*) as  liczba
from [Order Details]
group by orderid

select orderid,sum(Quantity)as liczba,sum(quantity*UnitPrice*(1-Discount)) as sums
from [Order Details]
group by orderid
having sum(Quantity)>250
order by sums desc

select ShipVia, sum(Freight) as oplata
from Orders
where datepart(yy,OrderDate) between 1996 and 1997
group by ShipVia

select em.FirstName,em.LastName,a.sums
from Employees as em
inner join (select e.EmployeeID,
count(*) as sums
from [Order Details] as od
inner join orders as o
on o.orderid=od.orderid
and datepart(yy,o.orderdate)='1997'
inner join employees as e
on e.employeeid=o.employeeid
group by e.employeeid) as a
on a.EmployeeID=em.EmployeeID
order by a.sums desc

select e.EmployeeID
from Employees as e
left join Employees as a
on a.ReportsTo=e.EmployeeID
where a.EmployeeID is null



--===========================================================

select o.orderid, a.suma+o.Freight as sums, c.CompanyName
from Orders as o
inner join (select od.orderid,isnull(sum(od.quantity*od.UnitPrice*(1-od.Discount)),0) as suma
from [Order Details] as od
group by od.orderid) as a on a.orderid=o.OrderID
inner join Customers as c
on c.CustomerID=o.CustomerID


select c.CompanyName, o.OrderDate
from Customers as c
left join orders as o
on o.CustomerID=c.CustomerID and year (o.orderdate) = '1997'
where o.orderdate is null

select c.CompanyName
from Customers as c
where c.CustomerID not in
(select CustomerID
from Orders as o
where YEAR(o.OrderDate) = '1997')

select c.CompanyName
from Customers as c
where not exists
(select CustomerID
from Orders as o
where c.CustomerID=o.CustomerID
and YEAR(o.OrderDate) = '1997')

select juv.member_no, count (*)
from juvenile as juv
inner join loanhist as l
on l.member_no = juv.member_no
and datepart(yy,l.in_date) = '2001' and datepart(mm,l.in_date) = '12'
group by juv.member_no
having count(*) >1
order by juv.member_no


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
where ju.member_no=l.member_no and month(l.in_date) = 12 and year(l.in_date)=2001)
+
(select count(*)
from loanhist as l
where pm.member_no=l.member_no and month(l.in_date) = 12 and year(l.in_date)=2001)
)as ilosc from juvenile as ju
inner join member as m on m.member_no=ju.member_no
inner join adult as a on a.member_no=ju.adult_member_no
inner join member as pm on pm.member_no=ju.adult_member_no
where ((select count(*) from loanhist as l
where ju.member_no=l.member_no and month(l.in_date) = 12 and year(l.in_date)=2001)
+
(select count(*)
from loanhist as l
where ju.adult_member_no=l.member_no and month(l.in_date) = 12 and year(l.in_date)=2001)
) > 1


select ch.firstname, ch.lastname, a.street, a.city, a.state, a.zip, par.firstname as 'parent_firstname', par.lastname as 'parent_lastname',
    ((select count(*)
    from loanhist as lh
    where lh.member_no = ch.member_no and month(in_date) = 12 and year(in_date)=2001)
    +
    (select count(*)
    from loanhist as lh
    where lh.member_no = par.member_no and month(in_date) = 12 and year(in_date)=2001))
    as liczba_ksiazek
    from juvenile as j
    join member as ch
    on j.member_no = ch.member_no
    join adult as a
    on j.adult_member_no = a.member_no
    join member as par
    on j.adult_member_no = par.member_no
    where ((select count(*)
    from loanhist as lh
    where lh.member_no = ch.member_no and month(in_date) = 12 and year(in_date)=2001)
    +
    (select count(*)
    from loanhist as lh
    where lh.member_no = par.member_no and month(in_date) = 12 and year(in_date)=2001))>1

--==zadanie 4

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
from [order details] as od
inner join orders as o
on o.orderid= od.orderid and (datepart(yy,o.orderdate) = 1996 or datepart(yy,o.orderdate) = 1997)
inner join products as p on od.productid=p.productid
inner join categories as c on c.categoryid=p.categoryid
group by c.categoryid,datepart(mm,o.orderdate),datepart(yy,o.orderdate)
    ORDER BY c.Categoryid


--== Grupa A
-- 1
select c.CategoryName,p.ProductName,p.UnitPrice,
(select avg(unitprice)
from products as prod
where prod.categoryid=c.categoryid) as aver,
(p.unitprice - (select avg(unitprice)
from products as prod
where prod.categoryid=c.categoryid)) as roznica,
(select isnull(sum(od.quantity*od.UnitPrice*(1-od.Discount)),0)
from [Order Details] as od
inner join orders as o on o.orderid=od.orderid and month(o.orderdate) = '3' and year(o.orderdate)='1997'
where od.productid=p.productid) as sums
from products as p
inner join categories as c on c.categoryid=p.categoryid
order by p.productname
-- 2 produkty nie sprzedawane w 1997

select p.productid
from Products as p
left join (select distinct od.productid
from [Order Details] as od
inner join orders as o on o.orderid=od.orderid
and year(o.orderdate)=1997) as a on p.ProductID=a.productid
where a.productid is not null

-- 3
--Dla każdego pracownika (imie i nazwisko) podaj
-- łączną wartość zamówień obsłużonych
-- przez tego pracownika (z ceną za przesyłkę).
-- Uwzględnij tylko pracowników, którzy mają podwładnych.


select distinct e.FirstName,e.LastName,a.summs
from Employees as e
left join Employees as em
on e.EmployeeID=em.ReportsTo
inner join (select o.EmployeeID,sum(a.sums+o.Freight) as summs
from Orders as o
inner join (select od.orderid,sum(od.quantity*od.UnitPrice*(1-od.Discount)) as sums
from [order details] as od
group by od.orderid) as a on a.orderid=o.OrderID
group by o.EmployeeID) as a on e.employeeid=a.employeeid
where em.ReportsTo is not null

--4
-- Czy są jacyś klienci, którzy nie złożyli żadnego zamówienia w 1997, jeśli tak pokaż
--ich nazwy i dane adresowe (3 wersje - join, in, exists).

select c.CompanyName,c.City,c.Address,c.Region,c.PostalCode
from Customers as c
left join (select cc.CustomerID
from Customers as cc
inner join orders as o
on o.CustomerID=cc.CustomerID
where year(o.orderdate) = 1997) as a
on a.CustomerID=c.CustomerID
where a.CustomerID is null

select c.CompanyName,c.City,c.Address,c.Region,c.PostalCode
from Customers as c
where c.CustomerID not in (select cc.CustomerID
from Customers as cc
inner join orders as o
on o.CustomerID=cc.CustomerID
where year(o.orderdate) = 1997)

select c.CompanyName,c.City,c.Address,c.Region,c.PostalCode
from Customers as c
where not exists (select cc.CustomerID
from Customers as cc
inner join orders as o
on o.CustomerID=cc.CustomerID
where year(o.orderdate) = 1997 and c.CustomerID=cc.customerid)


--4
-- Podaj listę członków biblioteki (imię, nazwisko)
-- mieszkających w Arizonie (AZ), którzy mają
-- więcej niż dwoje dzieci zapisanych do biblioteki
-- oraz takich, którzy mieszkają w Kalifornii (CA)
-- i mają więcej niż troje dzieci zapisanych do bibliotek.
--  Dla każdej z tych osób podaj liczbę książek
-- przeczytanych (oddanych) przez daną osobę i jej dzieci
-- w grudniu 2001 (użyj operatora union).

select j.adult_member_no, count (j.member_no),
(select j.adult_member_no,count(distinct isbn) as suma
from loanhist as l
where l.member_no=j.member_no and year(l.out_date) = 2001 and month(l.out_date)= 12
group by j.adult_member_no)+ (select count(distinct isbn)
from loanhist as l
where l.member_no=a.member_no and  year(l.out_date) = 2001 and month(l.out_date)= 12) as liczba_pozyczonych_ksiazek_dzieci
from adult as a
inner join juvenile as j on j.adult_member_no = a.member_no
where a.state like 'AZ'
group by j.adult_member_no
having count(j.member_no)>=2

select a.member_no,(b.suma+(select count(distinct isbn)
from loanhist as l
where l.member_no=a.member_no and  year(l.out_date) = 2001 and month(l.out_date)= 12)) as sums
from adult as a
inner join (select j.adult_member_no,count(distinct isbn) as suma
from loanhist as l
inner join juvenile as j on l.member_no=j.member_no
group by j.adult_member_no) as b on b.adult_member_no=a.member_no
inner join loanhist as l on l.member_no=a.member_no

select j.adult_member_no,count(distinct isbn)
from loanhist as l
inner join juvenile as j
on l.member_no=j.member_no
group by j.adult_member_no

select j.adult_member_no,count(*)
from juvenile as j
group by j.adult_member_no
--===============
select a.member_no,
(select count(*)
from juvenile as j
where j.adult_member_no = a.member_no) as il_dzieci,
(select(select count(distinct title_no)
from loanhist as l
inner join juvenile as j
on l.member_no=j.member_no
where j.adult_member_no=a.member_no and year(l.in_date)=2001 and month(l.in_date)=12)
+
(select count(distinct title_no)
from loanhist as l
where l.member_no=a.member_no)) as il_ksiazek
from adult as a
--===========
select a.member_no,m.firstname,m.lastname,
(select count(*)
from juvenile as j
where j.adult_member_no = a.member_no) as il_dzieci,
(select count(distinct title_no)
from loanhist as l
inner join juvenile as j
on l.member_no=j.member_no or l.member_no=a.member_no
where (j.adult_member_no=a.member_no) and year(l.in_date)=2001 and month(l.in_date)=12)
 as il_ksiazek
from adult as a
inner join member as m on m.member_no=a.member_no
where a.state like 'CA' and (select count(*)
from juvenile as j
where j.adult_member_no = a.member_no)>3
union
select a.member_no,m.firstname,m.lastname,
(select count(*)
from juvenile as j
where j.adult_member_no = a.member_no) as il_dzieci,
(select count(distinct title_no)
from loanhist as l
inner join juvenile as j
on l.member_no=j.member_no or l.member_no=a.member_no
where (j.adult_member_no=a.member_no) and year(l.in_date)=2001 and month(l.in_date)=12)
 as il_ksiazek
from adult as a
inner join member as m on m.member_no=a.member_no
where a.state like 'AZ' and (select count(*)
from juvenile as j
where j.adult_member_no = a.member_no)>2
--============ wersja B
-- ad 1
select m.FirstName,m.LastName,j.birth_date,a.city,a.street,a.state,par.firstname,par.lastname,
(select count(distinct isbn)
from loan as l
where l.member_no=j.member_no) as "il_ksiazek"
from juvenile as j
inner join member as m on m.member_no=j.member_no
inner join adult as a on a.member_no=j.adult_member_no
inner join member as par on par.member_no=j.adult_member_no
--ad 2

select empl.FirstName,empl.LastName,b.suma
from (select e.EmployeeID,sum(a.worth+o.Freight) as suma
from orders as o
inner join (select od.orderid ,sum(od.quantity*od.UnitPrice*(1-od.Discount)) as worth
from [Order Details] as od
group by od.orderid) as a on a.orderid=o.orderid
inner join Employees as e on e.EmployeeID=o.EmployeeID
group by e.EmployeeID) as b
left join Employees as em on b.EmployeeID=em.ReportsTo
inner join Employees as empl on b.EmployeeID=empl.EmployeeID
where em.ReportsTo is null
-- ad 3
-- join
select c.CompanyName
from Customers as c
left join (select cus.CompanyName
from customers as cus
inner join Orders as o on o.CustomerID=cus.CustomerID
where year(o.orderdate)=1997) as a on a.CompanyName=c.CompanyName
where a.CompanyName is null
-- in
select c.CompanyName
from Customers as c
where c.companyname not in (select cus.CompanyName
from customers as cus
inner join Orders as o on o.CustomerID=cus.CustomerID
where year(o.orderdate)=1997)

select c.CompanyName
from Customers as c
where not exists (select cus.CompanyName
from customers as cus
inner join Orders as o on o.CustomerID=cus.CustomerID
where year(o.orderdate)=1997 and c.CompanyName=cus.companyname)

--ad 4

select m.FirstName,m.LastName,m.member_no,
(select count(*)
from juvenile as j where j.adult_member_no=m.member_no) as dzieci,
(select count(distinct title_no)+(select count(distinct title_no)
from loanhist as l
where l.member_no=m.member_no) as ksiazki
from loanhist as l
inner join juvenile as jj
on jj.member_no=l.member_no
where jj.adult_member_no=m.member_no)
from member as m
inner join adult as a
on a.member_no=m.member_no and (((select count(*)
from juvenile as j where j.adult_member_no=m.member_no)>2 and a.state like 'AZ')
or ((select count(*)
from juvenile as j where j.adult_member_no=m.member_no)>3 and a.state like 'CA'))

--dla kazdego pracownika wyznacz najczesciej obslugiwana kategorie 1997

select e.employeeid,p.CategoryID,count(*) as suma
from [order details] as od
inner join products as p
on od.productid=p.productid
inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
inner join employees as e on e.employeeid=o.employeeid
group by e.employeeid,p.categoryid
order by suma desc

select top 1 p.CategoryID,count(*) as suma
from [order details] as od
inner join products as p
on od.productid=p.productid
inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
inner join employees as e on e.employeeid=o.employeeid
group by e.employeeid,p.categoryid
order by suma desc

select e.EmployeeID,
(select top 1 p.CategoryID
from products as p
inner join [order details] as od
on od.productid=p.productid
inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
where o.employeeid=e.employeeid
group by p.categoryid
ORDER BY count(*) desc),
(select top 1 count(*) as suma
from [order details] as od
inner join products as p
on od.productid=p.productid
inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
where o.employeeid=e.employeeid
group by p.categoryid
order by suma desc)
from Employees as e

select e.EmployeeID,
(select TOP 1 P.CategoryID
from [order details] as od
inner join products as p
on od.productid=p.productid
inner join orders as o on o.orderid=od.orderid and year(o.orderdate)=1997
where o.employeeid=e.employeeid
group by p.categoryid
order by count(*) desc)
from Employees as e

select c.CompanyName,a.categoryid
from Customers as c
inner join
(select top 1 cus.customerid,p.categoryid
from [order details] as od
inner join orders as o on
o.orderid=od.orderid and year(o.orderdate)=1997
inner join products as p on p.productid=od.productid
inner join customers as cus on o.customerid = cus.customerid
group by cus.customerid,p.categoryid
order by count(*) desc) as a on a.CustomerID=c.CustomerID
group by c.CompanyName