Structured Query Language - jest językiem zapytań umożliwiającym definiowanie struktur danych oraz pozyskiwanie danych w relacyjnej bazie danych.

Składa się z:

• języka definiowania danych - DDL,

• języka manipulowania danymi - DML.

Sposoby wydawania zdań SQL:

• statyczny - gdy zdanie jest włączane do programu napisanego w języku wysokopoziomowym (HLL),

• dynamiczny - w trakcie pracy interakcyjnej.

Funkcje realizowane przez SQL:

• definicja struktury bazy danych,

• odczyt danych,

• modyfikacja danych,

• kontrola dostępu,

• współużytkowanie danych,

• integralność danych.

SQL używany może być:

• jako element aplikacji i narzędzi programowych,

• jako interakcyjny język zapytań,

• jako język komunikacji pomiędzy różnymi DBMS.

Zdania SQL służące do definiowania struktur danych:

• CREATE COLLECTION

• CREATE TABLE

• CREATE INDEX

• CREATE VIEW

• DROP

Tworzenie kolekcji:

CREATE COLLECTION ‘nazwa kolekcji’

np.:

CREATE COLLECTION C42S13SQL.

Tworzenie tablicy:

CREATE TABLE ‘nazwa tablicy’
(kolumna = typ {NOT NULL/NOT NULL WITH DEFAULT})

NOT NULL - kolumna nie może zawierać wartości pustych,

NOT NULL WITH DEFAULT - kolumna nie może zawierać wartości pustych

(przy dopisywaniu wierszy system automatycznie wstawi 0 dla pól numerycznych i SPACE dla pól znakowych).

np.: CREATE TABLE WYDZIAL
(NR_WYDZ INT(3) NOT NULL WITH DEFAULT,
NAZWA_WYDZ CHAR(36) NOT NULL WITH DEFAULT,
LICZBA_OSOB INT(3) )

Tworzenie indeksu:

CREATE {UNIQUE} INDEX ‘indeks’ ON ‘tablica’

( kolumna {ASC/DESC} )

;

Fraza UNIQUE wymusza niepowtarzalność wartości danych w kolumnach tworzących indeks.

np.:

CREATE UNIQUE INDEX WYDZ_IND ON WYDZIAL

(NR_WYDZ ASC)

Tworzenie tablicy wirtualnej:

CREATE VIEW ‘nazwa tablicy wirtualnej’

( kolumna ) AS ‘podzbiór’ ,

np.:

CREATE VIEW PRZEKROJ (NR_WYDZ, NAZWA)

AS SELECT NR_WYDZ, NAZWA FROM WYDZIAL

Zdania SQL służące do usuwania struktur danych:

• DROP COLLECTION ‘nazwa kolekcji’

• DROP INDEX ‘nazwa indeksu’

• DROP TABLE ‘nazwa tablicy

• DROP VIEW ‘nazwa tablicy wirtualnej’.

Instrukcja SELECT służy do przeglądania bazy danych.

Składa się z 6 fraz. Dwie pierwsze są obowiązkowe:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

Fraza SELECT - służy do wyspecyfikowania kolumn w tablicy odpowiedzi.

Fraza FROM - służy do podania nazw tablic.

np.: SELECT IMIE, NAZWISKO, DATA_UR

FROM STUDENCI

Wybór wszystkich kolumn z tablicy :

SELECT *

FROM STUDENCI

Fraza WHERE - służy do eliminowania wierszy w tablicy odpowiedzi przy pomocy warunku przeszukiwania.

Podstawowe warunki to:

• predykat porównania (operatory: =,>,<, ...),

• predykat zakresu (BETWEEN ... AND),

• predykat występowania (IN),

• predykat wzoru (LIKE),

• predykat wartości pustych (IS NULL),

• predykat istnienia (EXISTS).

Warunki złożone buduje się przy pomocy operatorów logicznych (AND, OR i NOT) lub zdań podwyboru (subquery).

Predykat porównania - przykłady:

SELECT NAZWA_TOWAR, CENA
FROM PRODUKTY
WHERE CENA > 200

SELECT *
FROM PRODUKTY
WHERE ROK_PROD = ‘2000’

Predykat zakresu - przykład:

SELECT NAZWA_TOWAR, CENA

FROM PRODUKTY
WHERE CENA BETWEEN 200 AND 400

Predykat występowania - przykład:

SELECT NR_STUDENTA, NAZWISKO
FROM STUDENCI
WHERE NR_STUDENTA IN (240, 354, 521)

Predykat wzorca -wykorzystuje się dwa znaki specjalne:

% - zastępuje dowolny ciąg znaków,

_ - zastępuje pojedynczy znak.

Przykład:

SELECT NR_PRODUKTU, NAZWA, CENA
FROM PRODUKTY
WHERE NAZWA LIKE ‘HP%’

Predykat wartości pustych - przykład:

SELECT NR_STUDENTA, NAZWISKO
FROM STUDENCI
WHERE OCENA IS NULL

Złożone wyrażenia logiczne - przykład:

SELECT NAZWA_PROD, CENA
FROM TOWARY
WHERE (CENA < 100) AND (ROK_PROD=‘2000’)

Usuwanie zduplikowanych wierszy (DISTINCT) - przykład:

SELECT DISTINCT NAZWISKO
FROM OCENY
WHERE OCENA = ‘NDST’

Fraza ORDER BY - służy do uporządkowania wierszy w tablicy odpowiedzi. Należy podać zestaw kolumn definiujący porządek. SQL przez domniemanie porządkuje według kolejności rosnącej.

Słowo kluczowe DESC po nazwie kolumny zmienia porządek na malejący. Zamiast nazw kolumn można podawać numer kolejny na liście frazy SELECT.

Przykłady:

SELECT NR_STUDENTA, NAZWISKO
FROM STUDENCI
WHERE ROK_URODZ = ‘1970’

ORDER BY NAZWISKO

SELECT NAZWA_PROD, CENA, ROK_PROD
FROM TOWARY
ORDER BY 3, 2 DESC

Fraza UNION - służy do łączenia rezultatów dwóch lub więcej instrukcji SELECT w jedną tablicę odpowiedzi.

Łączone instrukcje SELECT muszą spełniać określone warunki:

• muszą zawierać tą samą liczbę kolumn,

• odpowiednie kolumny ze wszystkich połączonych instrukcji SELECT muszą

mieć zgodne typy,

• fraza ORDER BY może wystąpić tylko w ostatniej instrukcji SELECT.

Przykład:

SELECT NAZWISKO, OCENA_KON

FROM STUDENCI98

WHERE GRUPA = ‘C52’

UNION

SELECT NAZWISKO, OCENA_KON

FROM STUDENCI99

WHERE GRUPA = ‘C52’

Instrukcja SELECT z wieloma tablicami:

Przy formułowaniu zapytań z kilku tablic należy pamiętać o:

• umieszczeniu nazw wszystkich tablic we frazie FROM,

• stosowaniu kwalifikowanych nazw pól w celu uniknięcia niejednoznaczności,

• umieszczeniu we frazie WHERE warunku wiążącego zbiory.

Nazwa kwalifikowana kolumny to:

• nazwa kolumny poprzedzona nazwą tablicy:
  np.: STUDENCI.NR_STUDENTA,

• nazwa kolumny poprzedzona aliasem tablicy:
  np.: S.NR_STUDENTA.

Alias tablicy należy zdefiniować we frazie FROM:
np. : FROM STUDENCI S, PRZEDMIOTY P.

Przykłady:

SELECT STUDENCI.NAZWISKO, WYNIKI.PRZEDMIOT, WYNIKI.OCENA
FROM STUDENCI, WYNIKI
WHERE

(STUDENCI.NR_STUDENTA = WYNIKI.NR_STUDENTA)

SELECT S.NAZWISKO, P.PRZEDMIOT, OCENA

FROM STUDENCI S, PRZEDMIOT P, WYNIKI W

WHERE ( S.NR_STUDENTA = W.NR_STUDENTA )
AND ( P.KOD_PRZEDM = W.KOD_PRZEDM )

Instrukcja SELECT dla podsumowania:

Podsumowania służą do uzyskiwania odpowiedzi sumarycznych jako rezultatów instrukcji SELECT.

Służą do tych celów:

• funkcje kolumnowe,

• fraza GROUP BY,

• fraza HAVING.

Funkcje kolumnowe - mogą występować w miejscu nazw kolumn we frazie SELECT.

Argumentem dla funkcji kolumnowej jest zestaw wartości występujących w kolumnie.

Funkcja taka zwraca pojedynczą sumaryczną wartość.

Instrukcja SELECT, w której występują funkcje kolumnowe daje w tablicy odpowiedzi dane sumaryczne, zamiast poszczególnych pól tablicy.

Rodzaje funkcji kolumnowych:

SUM() - oblicz sumę kolumny,

AVG() - oblicza średnią kolumny,

MIN() - oblicza najniższą wartość kolumny,

MAX() - oblicza najwyższą wartość kolumny,

COUNT() - oblicza liczbę wartości w kolumnie,

COUNT(*) - oblicza liczbę wierszy w tablicy.

Przykłady:

SELECT MAX(SREDNIA)
FROM WYNIKI

SELECT COUNT(*)
FROM WYNIKI
WHERE OCENA = ‘NDST’

Fraza GROUP BY - służy do otrzymywania wartości sumarycznych dla poszczególnych grup.

Po słowie kluczowym GROUP BY wystepuje zestaw nazw kolumn, które definiują grupę.

Nazwy te muszą wystąpić we frazie SELECT.

Przykłady:

SELECT GRUPA, AVG(OCENA)
FROM WYNIKI
GROUP BY GRUPA

SELECT NAZWISKO, AVG(OCENA)
FROM STUDENCI S, WYNIKI W
WHERE S.NR_STUDENTA = W.NR_STUDENTA
GROUP BY NAZWISKO

Fraza HAVING - ma analogiczne zastosowanie jak fraza WHERE.

Służy do eliminowania wierszy podsumowań.

Przykłady:

SELECT GRUPA, AVG(OCENY)
FROM WYNIKI
GROUP BY GRUPA
HAVING AVG(OCENY) > 4.0

SELECT S.NAZWISKO, AVG(OCENY)
FROM STUDENCI S, WYNIKI W
WHERE S.NR_STUDENTA = W.NR_STUDENTA
GROUP BY S.NAZWISKO
HAVING AVG(OCENY) > 4.5
ORDER BY 2 DESC

Podrzędne instrukcje SELECT (Subqueries):

Podrzędna instrukcja SELECT to taka, która występuje we frazie WHERE lub HAVING innej instrukcji SELECT. Rezultaty podrzędnej instrukcji SELECT służą do eliminacji wierszy odpowiedzi nadrzędnej instrukcji SELECT.

Podrzędna instrukcja SELECT musi spełniać dodatkowe wymagania:

• musi być ujęta w nawiasy okrągłe,

• tablica odpowiedzi musi zawierać tylko jedną kolumnę,

• nie może zawierać frazy ORDER BY,

• nie może być unią (UNION) kilku instrukcji SELECT.

Przykłady:

1. Podaj wszystkich klientów, którzy dotychczasowe zamówienia mieli większe od średniej:

SELECT NR_KLIEN, NAZWA_KLIEN, IL_ZAMOW
FROM ZAMOWIENIA
WHERE IL_ZAMOW >
> ( SELECT AVG(IL_ZAMOW)
FROM ZAMOWIENIA )

Podrzędna instrukcja SELECT oblicza średnią liczbę zamówień wszystkich towarów występujących w tabeli ZAMOWIENIA.

Instrukcja nadrzędna wybiera tylko tych klientów, których zamówienia spełniają zadane kryterium.

2. Podaj wszystkie produkty (numery, nazwy, stany magazynowe), dla których aktualne sumaryczne zamówienia przekraczają 80% stanu w magazynie:

SELECT NR_PROD, NAZWA_PROD, STAN_MAG
FROM PRODUKTY P
WHERE 0.8 * STAN_MAG <=
<= (SELECT SUM( IL_ZAMOW )
FROM ZAMOWIENIA Z
WHERE Z.NR_PROD = P.NR_PROD )

W podrzędnej instrukcji SELECT można użyć nazw kolumn z nadrzędnej instrukcji.

Dla każdego NR_PROD występującego w tabeli PRODUKTY obliczana jest suma zamówień z tabeli ZAMOWIENIA.

3. Podaj wszystkich studentów (nazwisko, grupę, ocenę studiów), którzy uzyskali ocenę ze studiów większą od średniej oceny grupy, w której studiowali:

SELECT NAZWISKO, GRUPA, OCENA
FROM STUDENCI S
WHERE OCENA >
> ( SELECT AVG( OCENA )
FROM STUDENCI
WHERE GRUPA = S.GRUPA )

Dla każdego studenta z tablicy STUDENCI (instrukcja nadrzędna) przeglądana jest cała tabela STUDENCI i obliczana jest średnia ocen dla odpowiedniej grupy szkoleniowej (instrukcja podwyboru).

4. Podaj wszystkie produkty (numery i nazwy), których zamówienie przyjął pracownik o numerze 5:

SELECT NR_PROD, NAZWA_PROD
FROM PRODUKTY
WHERE NR_PROD IN
( SELECT NR_PROD
FROM ZAMOWIENIA
WHERE NR_PRACOW = 5 )

5. Podaj wszystkie produkty (numery i nazwy), dla których nie ma aktualnych zamówień:

SELECT NR_PROD, NAZWA_PROD
FROM PRODUKTY P
WHERE NOT EXISTS
( SELECT NR_PROD
FROM ZAMOWIENIA Z
WHERE P.NR_PROD = Z.NR_PROD )

Predykaty w języku SQL

Predykat określa warunek, który dla danego wiersza lub grupy wierszy może być spełniony (TRUE), niespełniony (FALSE) lub którego wynik może być nieokreślony (NULL).

Predykaty dzielą się na podstawowe i kwalifikowane.

Predykaty podstawowe:

• porównują dwie wartości,

• występują w następujących postaciach:

• <wyrażenie> <oper. porówn.> <wyrażenie>
  np.: NR_PRAC = ‘5204’,
  ZAROBKI + PREMIA > 800.

• wyrażenie, po którym występuje operator porównania i instrukcja podwyboru

(bez SOME, ANY czy ALL). Podwybór musi określać pojedynczą kolumnę i nie może zwracać więcej niż jednej wartości.

np. :

ZAROBKI > ( SELECT AVG ( ZAROBKI )
FROM PRACOWNICY )

wynik predykatu jest nieokreślony, jeżeli którykolwiek z operandów jest NULL lub podwybór nie zwraca żadnej wartści.

Predykaty kwalifikowane:

• porównują wartość ze zbiorem wartości,

• występują w postaci:

wyrażenie, po którym występuje operator porównania i instrukcja

podwyboru poprzedzona słowem SOME, ANY lub ALL,

np.:

SELECT *
FROM TABLICA_1

WHERE KOLUMNA_A =
= ANY ( SELECT KOLUMNA_B
FROM TABLICA_2)

Podwybór musi określać pojedynczą kolumnę i może zwracać więcej niż jedną wartość:

słowo ALL - wynik predykatu jest:

TRUE - gdy podwybór nie zwraca żadnej wartości lub warunek

określony w predykacie jest spełniony dla każdej wartości zwracanej przez podwybór,

FALSE - gdy warunek określony w predykacie nie jest spełniony dla

co najmniej jednej wartości zwracanej przez podwybór,

NULL (nieokreślony) - gdy warunek określony w predykacie jest spełniony

dla co najmniej jednej wartości zwracanej przez podwybór i co najmniej

jedno porównanie jest nieokreślone z powodu występowania wartości

NULL.

słowo ANY (SOME) - wynik predykatu jest:

TRUE - gdy warunek określony w predykacie jest spełniony dla co

najmniej jednej wartości zwracanej przez podwybór,

FALSE - gdy podwybór nie zwraca żadnej wartości lub warunek

określony w predykacie nie jest spełniony dla każdej wartości

zwracanej przez podwybór.

NULL (nieokreślony) - gdy warunek określony w predykacie nie jest

spełniony dla każdej wartości zwracanej przez podwybór i co

najmniej jedno porównanie jest nieokreślone z powodu

występowania wartości NULL.

Przykłady:

TAB_1 KOL_A ... TAB_2 KOL_B ...

1 2

2 3

3

4

1. SELECT *
FROM TAB_1

WHERE KOL_A = ANY ( SELECT KOL_B FROM TAB_2)

Podwybór zwraca wartości (2,3).

Wynik jest zbiorem wartości (2,3).

2. SELECT *
FROM TAB_1

WHERE KOL_A > ANY ( SELECT KOL_B FROM TAB_2)

Podwybór zwraca wartości (2,3),

Wynik jest zbiorem wartości (3,4).

3. SELECT *
FROM TAB_1

WHERE KOL_A > ALL ( SELECT KOL_B FROM TAB_2)

Podwybór zwraca wartości (2,3),

Wynik jest zbiorem wartości (4).

4. SELECT *

FROM TAB_1

WHERE KOL_A > ALL ( SELECT KOL_B FROM TAB_2

WHERE KOL_B<0)

Podwybór nie zwraca żadnej wartości,

Wynik jest zbiorem wartości (1,2,3,4).

Instrukcje modyfikacji tablic

Instrukcje SQL służące do modyfikacji bazy danych to:

• INSERT - pozwala dopisywać nowe wiersze do tablicy,

• UPDATE - pozwala modyfikować istniejące dane,

• DELETE - pozwala usunąć wiersze z tablicy.

Instrukcja INSERT:

• powoduje dopisanie wiersza do tablicy lub projekcji,

• lista nazw kolumn po nazwie tablicy musi być zgodna z listą wartości po frazie VALUES,

zamiast frazy VALUES można zastosować podrzędny SELECT, którego wynikowe wiersze będą dopisywane do tablicy (liczba kolumn musi się zgadzać z liczbą kolumn instrukcji INSERT).

Przykłady:

1. INSERT INTO STUDENCI
(NR_STUD, IMIE, NAZWISKO, MIASTO)
VALUES (177, ‘Adam’, ‘Abacki’, ‘Kraków’)

2. INSERT INTO STUDENCI
(NR_STUD, IMIE, NAZWISKO)
VALUES (177, ‘Adam’, ‘Abacki’)

UWAGA: Nie trzeba wypełniać wszystkich kolumn.

3. INSERT INTO ARCHIWUM
(NR_STUD, IMIE, NAZWISKO, MIASTO)
SELECT NR_STUD, IMIE, NAZWISKO, MIASTO
FROM STUDENCI
WHERE GRUPA = ‘C52’

Instrukcja UPDATE - pozwala zmodyfikować wartość jednej lub kilku kolumn tablicy lub projekcji.

We frazie SET podaje się nazwy kolumn i przypisane im nowe wartości.

Fraza WHERE określa, które wiersze będą modyfikowane.

Przykłady:

1. UPDATE STUDENCI
SET MIASTO = ‘Warszawa’, OCENA = 4.5
WHERE NR_STUD = 154

2. UPDATE ARCHIWUM
SET ROK_UKON = ‘1995’
WHERE GRUPA = ‘C52’

Instrukcja DELETE - pozwala na usuwanie wierszy z wybranej tablicy.

Przykłady:

1. DELETE FROM PRODUKTY

2. DELETE FROM STUDENCI
WHERE GRUPA = ‘C52’

3. DELETE FROM STUDENCI
WHERE NR_STUD IN
( SELECT NR_STUD
FROM WYNIKI
WHERE OCENA = ‘NDST’ )