SQL_cwiczenia

Widoki
Znane z programu MS Access kwerendy wybierające są nazywane w zaawansowanych systemach baz
danych widokami lub perspektywami. Podczas tworzenia widoków w trybie graficznym MS SQL Server
2000 poza informacjami o wybranych tabelach, powiązaniach miedzy nimi oraz wyborem odpowiednich
kolumn jest automatycznie budowany skrypt języka SQL. Skrypt ten oprócz standardowych poleceń
języka zawiera dodatkowo opcje specyficzne dla serwera SQL. Każde z podanych ćwiczeń można
wykonać za pomocą odpowiednich narzędzi serwera SQL oraz stosując polecenia języka SQL. W
podręczniku został przedstawiony sposób pierwszy, dlatego skupimy się na zastosowaniach języka i
składni polecenia SELECT.
Na przykładzie kwerend dla bazy danych hurtownia wykonaj następujące ćwiczenia:
Ćwiczenie 1
Utwórz widok prezentujący zgromadzone w bazie hurtownia i tabeli Towary informacje dotyczące
dysków twardych.
Sposób wykonania:
Zgodnie z kwerendą z programu MS Access widok ten powinien prezentować informacje
o dyskach twardych, prezentowane w następujących kolumnach: IDtowaru, Rodzaj_urządzenia, Model,
Cena, Gwarancja (w miesiącach), Stan_magazynu, Rabat.
Standardowe polecenie języka SQL będzie miało postać:
SELECT IDtowaru, Rodzaj_urządzenia, Model, Cena, [Gwarancja (w
miesiącach)], Stan_magazynu, Rabat
FROM Towary
WHERE(UPPER(Rodzaj_urządzenia) = 'DYSK')
Polecenie SELECT wyświetli dane zgromadzone w wymienionych kolumnach tabeli Towary, gdzie
(WHERE) w polu Rodzaj_urządzenia zostało wpisane słowo DYSK. Ze względu na możliwość zapisu
danych za pomocą różnych kombinacji dużych i małych liter została zastosowana funkcja UPPER,
konwertująca wszystkie litery pola na duże. Funkcja ta jest wykonywana tylko w celu właściwego
porównania i nie wywołuje żadnych zmian w danych zawartych w tabeli. Powyższe polecenie należy
rozumieć następująco: z tabeli Towary wybierz (i wyświetl) wszystkie dane zawarte w kolumnach …,
gdzie w polu Rodzaj_urządzenia wpisano wartość DYSK. 
Ćwiczenie 2
Wzorując się na ćwiczeniu 1, utwórz widok prezentujący informacje o monitorach, zgromadzone w
bazie hurtownia i tabeli Towary. 
Ćwiczenie 3
Wzorując się na ćwiczeniu 1, utwórz widok prezentujący informacje o procesorach, zgromadzone w
bazie hurtownia i tabeli Towary. 
Ćwiczenie 4
Wzorując się na ćwiczeniu 1, utwórz widok prezentujący informacje o sprzedawcach, zgromadzone w
bazie hurtownia i tabeli Pracownicy. 
Ćwiczenie 5
Utwórz widok prezentujący informacje o cenach procesorów, zgromadzone w bazie hurtownia w
tabelach Towary i Dane_tech_procesor.
Sposób wykonania:
W programie MS Access kwerenda wybiera informacje z dwóch powiązanych ze sobą tabel. W
programie MS SQL Server można zdefiniować widok w sposób bardzo zbliżony technicznie do
tworzenia kwerendy w programie MS Access, posługując się narzędziami graficznymi. Stosując jednak
polecenie języka SQL, możemy wykonać następujący skrypt:
SELECT Towary.IDtowaru, Dane_tech_procesor.Nazwa_kodowa,
Towary.Model, Towary.Cena
FROM Towary
INNER JOIN Dane_tech_procesor
ON Towary.IDtowaru = Dane_tech_procesor.Identyfikator
Po wykonaniu tego polecenia zostaną wyświetlone szczegółowe informacje o procesorach, które są
zgromadzone w bazie. W związku z tym, że korzystamy w tym przypadku z dwóch tabel ich nazwy
muszą być wyszczególnione we frazie FROM. Także odwołania do kolumn
w poszczególnych tabelach muszą być jasno sprecyzowane. Jest to dokonywane poprzez umieszczenie
przed nazwą kolumny nazwy konkretnej tabeli. Jednoznaczny wybór tabeli
i kolumny odbywa się za pomocą konstrukcji nazwa_tabeli.nazwa_kolumny. Podczas wykonywania
polecenia tabele są ze sobą łączone przez instrukcję INNER JOIN, przy czym pierwsza na liście tabela
jest nadrzędna w stosunku do kolejnych. Sposób powiązania jest uzależniony od danych zgromadzonych
w obu tabelach. Prezentowany tu widok łączy dwie tabele jeden-do-jednego, w związku z czym nie ma
znaczenia kolejność występowania nazw tabel na liście.
W przypadku relacji jeden-do-wielu należy zwrócić szczególną uwagę na kolejność nazw tabel
wymienionych na liście we frazie FROM. Jeżeli jako pierwszą na liście wymienimy tabelę, która zawiera
tylko jeden wiersz z konkretną daną, a jako drugą tabelę z większą liczbą wierszy z takimi danymi, to w
efekcie dokonamy ograniczenia wyświetlanych informacji. Jeśli jako pierwsza wymieniona zostanie
tabela zawierająca więcej rekordów
z określoną daną, a kolejne zawierają informacje słownikowe, to w rezultacie otrzymamy pełną
informację. Tak więc pierwszy sposób prezentuje relację jeden-do-jednego, co jest oczywistym błędem
powodującym zawężenie informacji, a drugi sposób umożliwia relację prawidłową, pokazując dane w
połączeniu jeden-do-wielu. 
Ćwiczenie 6
Utwórz widok prezentujący informacje o wysokości pensji brutto kierowników, zgromadzone w bazie
hurtownia w tabelach Kierownicy i Pracownicy. Zwróć uwagę na kolejność nazw tabel na liście we
frazie FROM polecenia SELECT 
1
Ćwiczenie 7
Utwórz widok prezentujący informacje o wartości zamówienia, zgromadzone w bazie hurtownia w
tabelach Towary i Zamówienia. Zwróć uwagę na kolejność nazw tabel na liście we frazie FROM
polecenia SELECT.
Sposób wykonania:
Na liście kolumn musi pojawić się wyrażenie Cena * Ilość_sztuk, które podaje informacje na temat
wartości zamówienia. MS SQL Server wymaga nazwania wyrażenia i dlatego pojawia się fraza AS
(jako) i wartość_zamówienia – w nawiasie kwadratowym ze względu na obecność polskich znaków
diakrytycznych. Polecenie języka SQL może mieć postać:
SELECT Zamówienia.IDzamówienia, Zamówienia.IDklienta,
Towary.Model, Towary.Cena, Zamówienia.Ilość_sztuk,
Towary.Cena * Zamówienia.Ilość_sztuk AS [wartość_zamówienia]
FROM Towary
INNER JOIN Zamówienia
ON Towary.IDtowaru = Zamówienia.IDtowaru
Ćwiczenie 8
Utwórz widok prezentujący informacje o zamówieniach poszczególnych firm współpracujących,
zgromadzone w bazie hurtownia w tabelach Towary, Klienci
i Zamówienia. Zwróć uwagę na kolejność nazw tabel na liście we frazie FROM polecenia SELECT.
Sposób wykonania:
Następuje tu połączenie trzech tabel, z których uzyskujemy informacje o zamówieniach złożonych przez
konkretne firmy. Nadrzędna jest tu tabela Klienci, a dane uzupełniające wnoszą tabele Zamówienia i
Towary. Polecenie języka SQL może mieć postać:
SELECT Towary.Rodzaj_urządzenia, Towary.Model, Towary.Cena,
Towary.Stan_magazynu
FROM Towary
WHERE (Model Like 'AMD%')
ORDER BY Towary.Cena;
c)
SELECT Imię, Nazwisko, Dział, Stanowisko, Pensja_brutto
FROM Pracownicy
WHERE (Dział = 'Zarządzania') AND (Pensja_brutto >= 3000) AND
(Pensja_brutto <= 4000) OR (Dział = 'Inż.-Techn.') AND
(Pensja_brutto >= 3000) AND (Pensja_brutto <= 5000);
d)
SELECT Rodzaj_urządzenia, AVG(Cena * (1 - Rabat) *
Stan_magazynu) AS [Wartość_średnia]
FROM Towary
GROUP BY Rodzaj_urządzenia
HAVING AVG(Cena * (1 - Rabat) * Stan_magazynu) > 15000)
e)
SELECT * FROM Zamówienia
WHERE (Data_zamówienia < '1 / 1 / 2002')
f)
SELECT Nazwisko, Imię
FROM Pracownicy
WHERE (Imię LIKE '_____')
Uwaga. W apostrofach znajduje się pięć znaków podkreślenia.
Wprowadź polecenia i sprawdź, czy analiza została przeprowadzona prawidłowo. 
SELECT Klienci.Nazwa_firmy, Zamówienia.Ilość_sztuk,
Towary.Model, Towary.Cena, Towary.Rabat
FROM Klienci
INNER JOIN Zamówienia
ON Klienci.IDklienta = Zamówienia.IDklienta
INNER JOIN Towary
ON Zamówienia.IDtowaru = Towary.IDtowaru
Ćwiczenie 9
Przeanalizuj następujące polecenia języka SQL:
a)
SELECT DISTINCT IDklienta
FROM Zamówienia
WHERE (Ilość_sztuk>= ALL (SELECT Ilość_sztuk FROM Zamówienia))
b)
2