Radosław Wolicki Kierunek: ETI Rok: III Grupa: II	Lab. Bazy Danych	Prowadzący : Andrzej Z. Sikorski
Data Ćwiczenia: 19-11-2012	Ćwiczenie nr.8

Bazy danych - Laboratorium zestaw 5

Zaawansowane techniki wykorzystywania zapytań zagnieżdżonych

Baza Northwind

1) Wyznaczyć najlepsze miesiące po względem obrotów dla poszczególnych lat, które obejmuje baza danych Northwind

select SUM(Quantity*UnitPrice) as 'Cena',Year(o.OrderDate)as 'Rok',Month(o.OrderDate) as 'Miesi¹c' from [Order Details] od , Orders o

where

od.OrderID=o.OrderID

group by

Year(o.OrderDate),Month(o.OrderDate)

2) Dla każdego miesiąca (w poszczególnych latach) wyznaczyć pracownika, który wygenerował największe dochody

select YEAR(o1.OrderDate) as 'Rok',MONTH(o1.orderDate) as 'Miesi¹c',

(

select EmployeeID from [Order Details] od,Orders o

where

od.OrderID=o.OrderID and

YEAR(OrderDate)=YEAR(o1.OrderDate) and MONTH(OrderDate)=MONTH(o1.OrderDate)

group by

EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)

having SUM(Quantity*unitPrice)=

(

select max(total) from

(

select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o

where

od.OrderID=o.OrderID and

YEAR(OrderDate)=YEAR(o1.OrderDate) and MONTH(OrderDate)=MONTH(o1.OrderDate)

group by

EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)

) t

)

) [The Best Sales Representative of the Month]

from Orders o1 group by YEAR(o1.OrderDate),MONTH(o1.orderDate)

3) Zmodyfikować zapytanie z p.2 tak by znajdowało najlepszą: kategorię, produkt,

klienta, kraj (pole country w tabelce Customers)

select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o

where

od.OrderID=o.OrderID

group by

EmployeeID,YEAR(OrderDate),MONTH(OrderDate)

order by 3,4

select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o

where

od.OrderID=o.OrderID and

YEAR(OrderDate)=1996 and MONTH(OrderDate)=7

group by

EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)

select max(total) from

(

select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o

where

od.OrderID=o.OrderID and

YEAR(OrderDate)=1996 and MONTH(OrderDate)=7

group by

EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)

) t

select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o

where

od.OrderID=o.OrderID and

YEAR(OrderDate)=1996 and MONTH(OrderDate)=7

group by

EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)

having SUM(Quantity*unitPrice)=

(

select max(total) from

(

select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o

where

od.OrderID=o.OrderID and

YEAR(OrderDate)=1996 and MONTH(OrderDate)=7

group by

EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)

) t

)

select YEAR(o1.OrderDate),MONTH(o1.orderDate) from Orders o1 group by YEAR(o1.OrderDate),MONTH(o1.orderDate)

select YEAR(o1.OrderDate),MONTH(o1.orderDate) ,

(

select EmployeeID from [Order Details] od,Orders o

where

od.OrderID=o.OrderID and

YEAR(OrderDate)=YEAR(o1.OrderDate) and MONTH(OrderDate)=MONTH(o1.OrderDate)

group by

EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)

having SUM(Quantity*unitPrice)=

(

select max(total) from

(

select SUM(Quantity*unitPrice)total ,EmployeeID ,YEAR(OrderDate) y,MONTH(OrderDate) m from [Order Details] od,Orders o

where

od.OrderID=o.OrderID and

YEAR(OrderDate)=YEAR(o1.OrderDate) and MONTH(OrderDate)=MONTH(o1.OrderDate)

group by

EmployeeID,EmployeeID ,YEAR(OrderDate) ,MONTH(OrderDate)

) t

)

) [The Best Sales Representative of the Month]

from Orders o1 group by YEAR(o1.OrderDate),MONTH(o1.orderDate)

4) Dla poszczególnych miesięcy wyznaczyć kraj o największej średniej wartości

zamówienia

5) Dla każdego miesiąca wyznaczyć kategorie, których produkty nie były zamówione

6) To samo co p.5 dla produktów

7) Rozwiązać zadania 5 i 6 nie posługując się operatorem exists (ani też zagnieżdżonym

zapytaniem z funkcją count – co w sumie na jedno wychodzi)

8) Korzystając z zapytania ułożonego w zadaniu 5 , znaleźć dla miesięcy liczbę

produktów niesprzedawanych

9) Idziemy za ciosem: w jakim miesiącu tych produktów było najwięcej

10) Łagodne wprowadzenie do tematu następnego wykładu: Znaleźć pracowników

którzy sprzedają wszystkie produkty (tj. dla każdego produktu istnieje pozycja Orders/[Order Details] tego pracownika) Baza CJDate

1) Statystyki dwuwymiarowe: w kolumnach miasta projektów (J), w wierszach miasta

dostawców (S), na przecięciu sumaryczny ciężar (iloczyn SPJ.QTY i P.WEIGHT)

2) Zadanie z książki (CJDate) polskie wyd. zadanie 7.5 (w pliku PDF zadanie 8.5 wyd. 8)

(FORALL – dla każdego, EXISTS istnieje) ułożyć zapytanie w SQL odpowiadające

formule rachunku krotek użytej w książce. Podaj nazwę części i miasto dla części

dostarczanych z Paryża przez każdego dostawcę z Londynu w liczbie <500 Visual Studio

1) Korzystając z przykładów z poprzednich ćwiczeń uruchomić wybrane rozwiązania

zadań 1-10 dla bazy Northwind w postaci aplikacji C#/.NET.

2) Utworzyć aplikację Okienkową i nawiązać z niej połączenie z bazą danych.

Korzystając z dostępnych komponentów wizualnych wyświetlić zbiory wynikowe dla

wybranych rozwiązań zadań lub dla zapytań w wykładu.

Pytania powtórkowe

Określić jaki zbiór wynikowy znajduje poniższe zapytanie

select m,y,count(*) from

(

select m,y,ProductName from

(select Year(OrderDate) y,Month(OrderDate) m

from Orders o

group by Year(OrderDate),Month(OrderDate)) t,Products p

where

exists

(

select * from Products,[Order Details], Orders

where

p.ProductID=[Order Details].ProductID and

[Order Details].OrderID=Orders.OrderID and

t.m=Month(OrderDate) and

t.y=Year(OrderDate)

)

)t1 group by y,m

Przekształcić zapytanie do postaci bez operatora exists, tak aby znajdowało ten sam zbiór

wynikowy.