|
Radosław Wolicki Kierunek: ETI Rok: III Grupa: II |
Lab. Bazy Danych |
Prowadzący : Andrzej Z. Sikorski |
|
Data Ćwiczenia: 03-12-2012
|
Ćwiczenie nr.10 |
6) Dla każdego miesiąca wyznaczyć kategorie, których produkty nie były zamówione:
select YEAR(OrderDate)"Rok" , MONTH(OrderDate)"Miesi¹c",ProductName from Orders o,Products
where
not exists(
select * from [Order Details] join Orders on Orders.OrderID=[Order Details].OrderID
where YEAR(OrderDate)=YEAR(o.OrderDate) and MONTH(OrderDate)
=MONTH(o.OrderDate)
and Products.ProductID=[Order Details].ProductID
)
group by YEAR(OrderDate) , MONTH(OrderDate),ProductName
7) To samo co p.6 dla produktów:
select * from Products
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=3 and 1998=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
select month(Orders.OrderDate)"Miesi¹c",YEAR(Orders.OrderDate)"ROK",ProductName from Products,Orders
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=MONTH(Orders.OrderDate) and YEAR(Orders.OrderDate)=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
group by
MONTH(Orders.OrderDate),YEAR(Orders.OrderDate),ProductName
order by 2,1
select y,m,COUNT(*) from
(
select MONTH(Orders.OrderDate) m,YEAR(Orders.OrderDate) y,ProductName from Products,Orders
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=MONTH(Orders.OrderDate) and YEAR(Orders.OrderDate)=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
group by
MONTH(Orders.OrderDate),YEAR(Orders.OrderDate),ProductName ) t
group by y,m
order by 1,2
8) Rozwiązać zadania 6 i 7 nie posługując się operatorem exists (ani też zagnieżdżonym
zapytaniem z funkcją count – co w sumie na jedno wychodzi):
with mths as
(
select YEAR(OrderDate) y ,MONTH(OrderDate) m from Orders
group by YEAR(OrderDate),MONTH(OrderDate)
)
select y,m,Products.ProductName,Products.ProductID from mths cross join products left outer join
(orders o1 join [Order Details] on o1.OrderID=[Order Details].OrderID) on
month(o1.OrderDate)=mths.m and
YEAR(o1.OrderDate)=mths.y and
Products.ProductID=[Order Details].ProductID
where
o1.OrderDate is null
--and y=1996 and m=7
order by 3
select * from Products
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=7 and 1996=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
order by 2
select * from products
select month(Orders.OrderDate),YEAR(Orders.OrderDate),ProductName from Products,Orders
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=month(Orders.OrderDate) and YEAR(Orders.OrderDate)=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
group by
month(Orders.OrderDate),YEAR(Orders.OrderDate),ProductName
order by 2,1
9) Korzystając z zapytania ułożonego w zadaniu 6 , znaleźć dla miesięcy liczbę
produktów niesprzedawanych:
select Miesiac,Rok,COUNT(*) from
(select MONTH(Orders.OrderDate) Miesiac, Year(Orders.OrderDate) Rok , ProductName from Products,Orders
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=MONTH(Orders.OrderDate) and Year(o.OrderDate)=Year(Orders.OrderDate) and Products.ProductID=[Order Details].ProductID
)
group by MONTH(Orders.OrderDate), Year(Orders.OrderDate), ProductName) p
group by Miesiac, Rok
10) Idziemy za ciosem: w jakim miesiącu tych produktów było najwięcej:
Pierwszy sposób:
select top 1 m,y,count(*) c from
(
select month(Orders.OrderDate) m,YEAR(Orders.OrderDate) y,ProductName from Products,Orders
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=month(Orders.OrderDate) and YEAR(Orders.OrderDate)=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
group by
month(Orders.OrderDate),YEAR(Orders.OrderDate),ProductName ) t
group by m,y order by 3 desc
Drugi sposób:
select m,y,count(*) c from
(
select month(Orders.OrderDate) m,YEAR(Orders.OrderDate) y,ProductName from Products,Orders
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=month(Orders.OrderDate) and YEAR(Orders.OrderDate)=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
group by
month(Orders.OrderDate),YEAR(Orders.OrderDate),ProductName ) t
group by m,y
having count(*)=
( select MAX(c) from
(
select COUNT(*) c from
(
select month(Orders.OrderDate) m,YEAR(Orders.OrderDate) y,ProductName from Products,Orders
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=month(Orders.OrderDate) and YEAR(Orders.OrderDate)=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
group by
month(Orders.OrderDate),YEAR(Orders.OrderDate),ProductName ) t
group by y,m
)t
)
11) Posłużyć się techniką z zadania 5 (iteracja w c#) w celu uproszczenia SQL.
select YEAR(OrderDate),MONTH(OrderDate) from Orders
group by YEAR(OrderDate),MONTH(OrderDate)
select * from Products
where
not exists(
select * from Orders o join [Order Details] on o.OrderID=[Order Details].OrderID
and MONTH(o.OrderDate)=8 and 1996=YEAR(o.OrderDate) and Products.ProductID=[Order Details].ProductID
)
select YEAR(OrderDate),MONTH(OrderDate),ProductName from Orders,Products
group by YEAR(OrderDate),MONTH(OrderDate),ProductName
except
select YEAR(OrderDate),MONTH(OrderDate),ProductName from Orders o join [Order Details] od on o.OrderID=od.OrderID
join Products p on p.ProductID=od.ProductID
group by YEAR(OrderDate),MONTH(OrderDate),ProductName
select * from
(select YEAR(OrderDate) y,MONTH(OrderDate) m,ProductName p from Orders,Products
group by YEAR(OrderDate),MONTH(OrderDate),ProductName) t1
left outer join
(select YEAR(OrderDate) y1,MONTH(OrderDate) m1,ProductName p1 from Orders o join [Order Details] od on o.OrderID=od.OrderID
join Products p on p.ProductID=od.ProductID
group by YEAR(OrderDate),MONTH(OrderDate),ProductName) t2 on
y=y1 and m=m1 and p=p1
where p1 is null