USE [BD1d_wt_1A]
GO

--Wielowartościowe podzapytania niezależne

/* 1. Zestawienie zamówień ( ID zamówienia, data zamówienia, IDklienta )
      przyjętych od klientów z Francji i Niemiec. */

/* 2. Wykaz pracowników ( imię i nazwisko ), których zamówienia nie zostały zrealizowane. */

/* 3. Zestawienie produktów ( nazwa produktu ), które nie występowały w niezrealizowanych zamówieniach. */

/* 4. Wykaz pracowników ( imię i nazwisko ), których zamówienia nie zostały zrealizowane. */

/* 5. Sprawdzić, czy Nancy Davolio i Michael Suyama przyjmowali zamówienia od klientów,
      od których kiedykolwiek zamówienia przyjmował Robert King.   ( Pracownik, Nazwa firmy )*/

      --a) wersje z predykatem IN

      --b) wersje z predykatem SOME

      --c) wersje z predykatem ANY

/* 6. Wyświetlić które produkty i w jakiej ilości (poniżejżej 10 sztuk), były sprzedane przez Margaret Peacock do krajów (kraj odbiorcy),
      z których zamówienia przyjmowali Anne Dodsworth lub Andrew Fuller w dniach 3-7. stycznia 1998 roku.
        ( kraj odbiorcy, ID produktu, sprzedaż <szt.> ) */

/* 7. Jaka jest średnia wartość zamówień dla każdego z pracowników? ( imię, nazwisko, wartość średnia */

/* 8. Które firmy złożyły mniejszą niż średnia, liczbę zamówień. ( ID firmy, liczba zamówień, średnia liczba zamówień) */


--Wielowartościowe podzapytania zależne

/* 9. Sporządzić listę zamówień przyjętych przez pracowników w pierwszym tygodniu ich pracy,
      tzn. liczonym od daty przyjęcia pierwszego zamówienia.

/* 10. Ile zamówień przyjął każdy z pracowników w ostatnich 2 tygodniach przed przyjęciem ostatniego zamówienia?
      ( Imię, Nazwisko, Liczba zamówień ) */

/* 11. Zamówienia od ilu różnych klientów przyjął każdy z pracowników w ostatnich
       trzech tygodniach przed przyjęciem ostatniego zamówienia?
      ( Imię, Nazwisko, Liczba klientów ) */

/* 12. Którzy pracownicy przyjęli w ostatnim roku przed przyjęciem
      ostatniego zamówienia co najmniej cztery zamówienia od tego samego klienta?
      ( Imię, Nazwisko, Liczba zamówień, ID klienta ) */

/* 13. Ile zamówień przyjęli pracownicy w swoich najlepszych miesiącach sprzedaży?
       ( Pracownik, [Wartość sprzedaży], (mm.rrrr) [Najlepszy miesiąc] ) */

/* 14. Sporządzić zestawienie wartości sprzedaży wygenerowanej przez poszczególnych pracownicków w ich najlepszych miesiącach sprzedaży?
       ( Pracownik, [Wartość sprzedaży], (mm.rrrr) [Najlepszy miesiąc] ) */

/* 15. Kiedy złożono zamówienie o największej liczbie pozycji zamówień? Podać datę i numer zamówienia. */
      --a) wersja 1 - nieskorelowane podzapytanie skalarne

      --b) wersja 2 - skorelowane podzapytanie skalarne

/* 16.  Kto przyjął zamówienie o największej liczbie pozycji zamówień? Podać imię i nazwisko pracownika i numer zamówienia.    */
        --a) wersja 1 - nieskorelowane podzapytanie skalarne

        --b) wersja 2 - skorelowane podzapytanie skalarne

/* 17  Kto złożył zamówienie o największej wartości? Podać nazwę firmy, kraj odbiorcy oraz wartość zamówienia. */

/* 18.  W jakich terminach zostały złożone ostatnie zamówienia każdej z firm? Podać nazwę firmy i datę zamówienia.     */

/* 19.  Kiedy każdy z klientów złożył swoje pierwsze zamówienie? Podać nazwy firm, daty pierwszych zamówień i ich wartości. */

/* 20.  Które produkty były zamawiane jako pierwsze w swoich kategoriach?
        Podać nazwy kategorii, nazwy produktów, numery, daty i wielkości ich pierwszych zamówień.  */

/* 21.  W które handlowe poniedziałki nie zamówiono żadnych produktów z kategorii Mięso/Drób.
        Podać daty. Napisać zapytanie sprawdzające. */

        -- Sprawdzenie: wyświetlamy wszystkie poniedziałki, w których sprzedawano produkty z kategorii 'Mięso/Drób'
            SELECT CAST(z1.DataZamówienia AS DATE) AS [DATA "poniedziałkowa"]
            FROM Kategorie AS k JOIN Produkty AS p ON k.IDkategorii = p.IDkategorii
                                JOIN PozycjeZamówienia AS pz ON p.IDproduktu = pz.IDproduktu
                                JOIN Zamówienia AS z1 ON pz.IDzamówienia = z1.IDzamówienia
            WHERE k.NazwaKategorii = 'Mięso/Drób' AND DatePart(DW, DataZamówienia) = 2;

/* 22.  Z których kategoriii nie wysyłano do USA żadnych produktów w pierwszej dekadzie czerwca 1997 roku?
        Podać nazwy tych kategorii. */

/* 23.  Czy istnieją firmy, które jeszcze nie złożyły żadnego zamówienia? Podać nazwy i kraje.  */

/* 24.  Którzy pracownicy nie przyjęli żadnego zamówienia? Podać identyfikatory, imiona, nazwiska i datę zatrudnienia. */
        --a) wersja z predykatem IN

        --b) wersja z predykatem ALL

/* 25.  Czy są miasta, w których mieszkają pracownicy oraz mają tam swoje siedziby jacyś klienci? Podać te miasta. */

/* 26.  Którzy pracownicy mieszkają w miastach, w których mają swoje siedziby jacyś klienci?
        Podać identyfikatory pracowników, ich imiona, nazwiska oraz nazwy miast.  */

/* 27.  Jakie były identyfikatory zamówień - pierwszego i ostatniego - przyjętych w ostatnim miesiącu sprzedaży w 1998 rouku.
        Podać daty, identyfikatory zamówień i nazwy odbiorców. */

/* 28.  Czy 13. stycznia 1998 roku zostały przyjęte zamówienia przyjęte przez pracowników,
        których nazwiska nie zaczynają się od litery „D”.
            Podać identyfikatory i daty tych zamówień oraz imiona i nazwiska tych pracowników. */

/* 29.  Kiedy klienci, których identyfikatory rozpoczynają się od liter 'A' lub 'K',
        złożyli swoje ostatnie zamówienia? Podać identyfikatory klientów, identyfikatory zamówień
          i ich daty przyjęcia do realizacji.  */

/* 30.  Czy istnieją dostawcy ze Szwecji, którzy dostarczyli jakiekolwiek produkty.
        Podać nazwę i adres dostawcy. */

/* 31.  Czy istnieją jacyś dostawcy, którzy nie dostarczyli żadnego produktu.
        Podać ich nazwy i adresy.  */

/* 32. Czy istnieją kategorie, którym nie przypisano żadnego produktu?
       Podać nazwy tych kategorii.  */
        --a) wersja z predykatem EXISTS

        --b) wersja z predykatem IN

/* 33.  Czy istnieją produkty, których jeszcze nikt nie zamawiał?
        Podać nazwy tych produktów.  */

/* 34. Do jakiej kategorii należą produkty, których jeszcze nikt nie zamawiał?
       Podać nazwy tych kategorii.  */

/* 35. W którym kraju (lub krajach) ma swoje siedziby największa liczba firm?
       Podać kraj i liczbę firm.  */

/* 36. W którym kraju (lub krajach) ma swoje siedziby najmniejsza liczba firm?
       Podać kraj i liczbę firm.  */

/* 37. W którym miesiącu (lub miesiącach) złożono najwięcej zamówień?
       Podać lata, miesiące i liczby zamówień.  */

/* 38. W którym miesiącu (lub miesiącach) złożono najmniej zamówień?
       Podać lata, miesiące i liczby zamówień.  */

/* 39. Którym miesiącu (lub miesiącach) złożono najmniej lub najwięcej zamówień?
       Podać lata, miesiące i liczby zamówień.  */

/* 40. Do ilu kategorii nie przypisano żadnego produktu?
       Podać liczbę kategorii.  */

/* 41. Który produkt (lub produkty) sprzedawał się najlepiej?
       Podać nazwy produktów i wartości sprzedanych produktów.  */

/* 42. Która kategoria produktów sprzedawała sie najgorzej?
        Podać nazwę kategorii i wartość przedaży.  */

/* 43. Która kategoria produktów sprzedawała się najlepiej w poszczególnych latach?
       Podać lata, nazwy kategorii i wartości przedaży.  */

    --Sprawdzenie wyniku:
            SELECT YEAR(z.DataZamówienia) AS Rok, k.NazwaKategorii AS Kategoria, SUM(pz.Ilość * pz.CenaJednostkowa) [Wartość sprzedaży]
            FROM Kategorie AS k JOIN Produkty AS p ON k.IDkategorii = p.IDkategorii
                                JOIN PozycjeZamówienia AS pz ON p.IDproduktu = pz.IDproduktu
                                JOIN Zamówienia AS z ON pz.IDzamówienia = z.IDzamówienia
            GROUP BY YEAR(z.DataZamówienia), k.NazwaKategorii
            ORDER BY Rok, [Wartość sprzedaży] DESC, Kategoria;

/* 44. Które produkty przynosiły najmniejszee obroty w poszczególnych latach?
       Podać lata, nazwy produktów, kraj dostawcy i wartości przedaży.  */

        --Sprawdzenie wyniku:
            SELECT YEAR(z.DataZamówienia) AS Rok, p.NazwaProduktu AS Produkt, d.Kraj AS [Kraj dostawcy], SUM(pz.Ilość * pz.CenaJednostkowa) [Wartość sprzedaży]
            FROM Dostawcy AS d JOIN Produkty AS p ON d.IDdostawcy = p.IDdostawcy
                               JOIN PozycjeZamówienia AS pz ON p.IDproduktu = pz.IDproduktu
                               JOIN Zamówienia AS z ON pz.IDzamówienia = z.IDzamówienia
            GROUP BY YEAR(z.DataZamówienia), p.NazwaProduktu, d.Kraj
            ORDER BY Rok, [Wartość sprzedaży] DESC, Produkt;