Comenzi SQL de manevrare a datelor

Comenzile SQL de manevrare a datelor sunt SELECT, INSERT, UPDATE si DELETE si vor fi studiate in continuarea acestei lucrari.

1  Comanda SELECT

Blocul de interogare de baza (cerere) il reprezinta comanda SELECT. Aceasta regaseste informatile dorite din tabelele bazei de date. Comanda (instructiunea) SELECT este foarte puternica si are urmatoarea sintaxa generala:

SELECT [DISTINCT] lista_selectie

FROM lista_tabele

[WHERE expresie_booleana]

[clauze_secundare];

Ca rezultat al comenzii SELECT se obtine un tabel care are ca si coloane toate coloanelor date in parametrul lista_selectie; parametrul expresie_booleana al clauzei WHERE trebuie sa fie o expresie care se evalueaza la o valoare booleana (TRUE, FALSE sau NULL). Tabelul rezultat contine atributele (coloanele) din lista_selectie ale acelor linii (tupluri) ale produsului cartezian al tabelelor din lista_tabele pentru care expresia expresie_booleana din clauza WHERE este adevarata (are valoarea TRUE).

Se remarca 3 sectiuni (clauze) importante ale constructiei de interogare: clauza SELECT, clauza FROM si clauza WHERE. Clauza SELECT defineste coloanele tabelului rezultat. Clauza FROM indica unul sau mai multe tabele din care se selecteaza liniile tabelului rezultat. Clauza WHERE defineste conditia pe care trebuie sa o indeplineasca fiecare linie a tabelului rezultat. In afara acestor clauze, comanda SELECT mai poate contine si clauze secundare (ORDER BY, GROUP BY, HAVING), care permit ordonari sau grupari ale tuplurilor (liniilor) rezultate, etc.

Clauza SELECT introduce lista atributelor (coloanelor) unor tabele sau al expresiilor care vor fi selectate si afisate. Coloanele din lista trebuie sa apartina uneia din tabelele specificate in clauza FROM. Ca exemplu, comanda urmatoare va selecta (si afisa) numele si prenumele tuturor angajatilor din tabelul ANGAJAT:

SELECT Nume, Prenume FROM ANGAJAT;

Ca rezultat al comenzii de mai sus se pot obtine doua sau mai multe linii identice, daca exista angajati cu acelasi nume si prenume. In general, daca lista de atribute nu contine o cheie a relatiei, rezultatul operatiei SELECT poate contine linii duplicat (deci nu mai este o “relatie” in sensul definitiei din modelul relational). Pentru eliminarea liniilor duplicat se introduce parametrul DISTINCT si atunci rezultatul este o relatie in sensul definitiei din modelul relational. Deci comanda de mai sus se poate scrie:

SELECT DISTINCT Nume, Prenume FROM ANGAJAT;

Daca lista de atribute este un asterix (*), atunci se selecteaza toate atributele produsului cartezian al tabelelor indicate prin clauza FROM, care indeplinesc conditia din clauza WHERE. De exemplu, comanda:

SELECT *  FROM ANGAJAT;

permite selectarea tuturor coloanele si a liniilor din tabelul ANGAJAT.

Comanda SELECT poate sa contina chiar si numai clauza SELECT, deci fara sa se refere la un tabel (printr-o clauza FROM). In acest caz, comanda SELECT contine o lista de expresii pe care le evalueaza si rezultatele calculate sunt returnate ca o linie a unui un tabel ale carui coloane sunt chiar expresiile date. De exemplu, comanda:

SELECT 3*4, 6+7, cos(45), pow(9.0, 3.0), floor(12.45);

va afisa rezultatul sub forma:

In clauza SELECT se poate redenumi atributele (coloane ale tabelelor) sau se pot specifica nume pentru expresii, folosind urmatoarea sintaxa Sintaxa de redenumire a coloanelor este:

SELECT nume1 [AS] noul _nume1, …, expresie [AS] nume_expresie

FROM lista_tabele [alte_clauze];

Se observa ca noul nume atribuit unei coloane sau expresii urmeaza vechiului nume sau expresiei, precedat (optional, depinzand de implementare) de cuvantul-cheie AS. De exemplu, comanda:

PROIECT: Ce este CSS ? PROIECT: Wamp Server

SELECT Nume, Prenume, Salariu*0.8 SalariulNet FROM ANGAJAT;

va afisa numele, prenumele si  80% din salariul (brut) al angajatului, denumit SalariuNet.

Clauza FROM este obligatorie daca intr-una din clauzele SELECT, WHERE, HAVING apar nume de atribute (coloane ale unor tabele). In acest caz, lista de tabele care insoteste clauza FROM trebuie sa contina numele tuturor tabelelor (separate prin virgula) ale caror coloane se folosesc. Daca lista contine mai mult de un tabel, atunci numele coloanelor din clauza SELECT trebuie sa fie diferite si, daca nu sunt diferite, atunci se califica numele coloanei cu numele tabelului caruia ii apartine (precedand numele atributului cu numele tabelului urmat de operatorul “punct” (.). De exemplu:

SELECT ANGAJAT.Nume, Prenume, SECTIE.Nume FROM ANGAJAT, SECTIE;

De retinut ca, desi limbajul SQL este case-insensitive, totusi este necesar ca numele tabelului cu care se califica numele unui atribut trebuie sa fie identic (inclusiv tipul de caracter, majuscula sau nu) cu cel declarat in clauza FROM.

Clauza WHERE restrictioneaza tuplurile returnate ca rezultat la acele tupluri care indeplinesc conditia introdusa de aceasta clauza. In forma cea mai obisnuita, clauza WHERE este urmata de o expresie booleana sau de o valoare booleana.

O expresie booleana se construieste din valori boolene, operatori logici (AND, OR, NOT) si paranteze. O valoare booleana se obtine, in mod obisnuit, ca rezultat al comparatiei intre doi operanzi folosind un operator de comparatie. Un operand poate fi un nume de coloana dintr-unul din tabelele introduse prin clauza FROM, o constanta, valoarea unei expresii aritmetice sau o valoare returnata de o functie. Operatorii de comparatie utilizati in clauza WHERE pot fi atat operatori aritmetici de comparatie cat si operatori SQL de comparatie. De exemplu, comenzile:

SELECT Nume, Prenume FROM ANGAJAT

WHERE DataNasterii>1940-01-01;

SELECT Nume, Prenume FROM ANGAJAT

WHERE Salariu BETWEEN 1400 AND 1800 AND Functia=’Cercetator’;

Prima comanda va afisa toti angajatii care s-au nascut dupa data de 1 Ianuarie 1940; cea de-a doua comanda va afisa toti angajatii cu functia ‘Cercetator’ si salariul cuprins intre 1400 si 1800.

Clauza ORDER BY introduce numele atributului dupa care se face ordonarea liniilor tabelei rezultat. Ordonarea este implicit in ordine crescatoare; daca numele atributului este urmat de cuvantul DESC, se face ordonarea liniilor in ordine descrescatoare a valorilor aceslui atribut. Ordonarea liniilor astfel obtinuta este ordonare logica, foarte utila in prezentarea (afisarea) rezultatului si nu inseamna ordonarea inregistrarilor in fisierele relatiilor. De exemplu, pentru afisarea listei angajatilor ordonata dupa numele acestora, se introduce comanda:

SELECT * FROM ANGAJAT ORDER BY Nume;

Clauza GROUP BY se foloseste pentru a grupa rezultatele functiilor agregat (totalizatoare) dupa valoarea uneia sau mai multor coloane.

Functiile agregat (totalizatoare) sunt urmatoarele:

COUNT:           returneaza numarul de linii ale rezultatului (care indeplinesc conditia din clauza WHERE)

SUM:   returneaza suma tuturor valorilor dintr-o coloana

MAX:   returneaza valoarea cea mai mare dintr-o coloana

MIN:    returneaza valoarea cea mai mica dintr-o coloana

AVG:    returneaza media valorilor dintr-o coloana

Aceste functii se pot folosi fara clauza GROUP BY daca se calculeaza valoarea dorita (medie, suma, etc.) dintr-o coloana data considerand toate tuplurile relatiei. De exemplu, camanda urmatoare va afisa salariul mediu al tuturor angajatilor:

SELECT AVG(Salariu) FROM ANGAJAT;

Daca se doreste calculul unei valori totalizatoare separat pe grupe de linii, atunci se introduce clauza GROUP BY, urmata de numele uneia sau mai multor coloane. In acest caz, functia totalizatoare se aplica separat acelor linii care au aceeasi valoare a atributelor listate de clauza GROUP BY. De exemplu, salariul mediu calculat separat pe grupe de angajati, fiecare grup fiind compus din linii care au aceeasi valoare a atributului Functie, se obtine cu urmatoarea comanda SQL:

SELECT AVG(Salariu) FROM ANGAJAT GROUP BY(Functie);

Clauza HAVING. Functiile totalizatoare nu pot fi utilizate in clauza WHERE; de exemplu comanda urmatoare (prin care se cere lista angajatilor cu salariu mai mare decat salariul mediu) este eronata:

SELECT Nume, Prenume FROM ANGAJAT WHERE Salariu>=AVG(Salariu);

Problema folosirii unei functii totalizatoare intr-o conditie de selectie se rezolva cu ajutorul clauzei HAVING. Clauza HAVING este asemanatoare clauzei WHERE, adica introduce o conditie pe care trebuie sa o indeplineasca tuplurile rezultat,  dar, in plus, permite utilizarea functiilor agregat in expresia conditionala. Exemplul de mai sus se scrie corect astfel:

SELECT Nume, Prenume FROM ANGAJAT HAVING Salariu>=AVG(Salariu);

In comenzile SELECT se folosesc, de regula, diferite combinatii de clauze, functii, expresii.

2. Introducerea, modificarea si stergerea datelor in limbajul SQL

Introducerea liniilor in tabele. Comanda SQL pentru introducerea datelor este INSERT, care are sintaxa:

INSERT INTO nume_tabel (nume_coloana_1, nume coloana_2, …)

VALUES (valoare_1, valoare_2, …)

Intre valori si numele de coloane trebuie sa existe o corespondenta unu la unu. Valorile din lista pot fi constante (literale) sau expresii aritmetice. De exemplu, introducerea unei linii in tabelul SECTIE se poate face cu comanda:

INSERT  INTO SECTIE (IdSectie, Nume, Buget)

VALUES (‘S1’,‘Productie’, 4000000);

Lista de coloane poate sa lipseasca, daca se introduc valori in toate coloanele tabelului, dar in aceasta situatie ordinea valorilor introduse trebuie sa respecte ordinea atributelor. Aceasta ordine provine din ordinea de definire a atributelor prin comanda CREATE TABLE, precum si din operatiile ulterioare de alterare a tabelului respectiv. De exemplu, pentru introducerea corecta a unei linii in tabelul ANGAJAT, se urmareste ordinea atributelor obtinuta cu comanda precedenta DESCRIBE ANGAJAT, introducand comanda:

INSERT INTO ANGAJAT

VALUES (‘A1’, ‘Ionescu’, ‘Ion’, ‘1944-03-25’,

‘Bucuresti’,‘Cercetator’,‘1969-09-01’);

Modificarea valorilor atributelor. Comanda UPDATE permite actualizarea valorilor coloanelor (atributelor) din una sau mai multe linii ale unui tabel. Aceasta are sintaxa

UPDATE  nume_tabel

SET    nume_coloana_1=expresie_1, nume_coloana_2=expresie_2,….

[WHERE  conditie];

Clauza WHERE impune ca actualizarea valorilor coloanelor sa se efectueze numai asupra acelor linii (tupluri) care indeplinesc conditia data. Daca clauza WHERE este omisa, valorile coloanelor din toate liniile din tabel vor fi modificate. 

De exemplu, pentru a actualiza linia introdusa mai sus in tabelul ANGAJAT, se poate introduce comanda:

UPDATE ANGAJAT

SET      Adresa=‘Bucuresti, Str. Victoriei Nr. 15’

WHERE    IdAngajat=‘A1’;

Prin aceasta comanda se modifica adresa angajatului cu numar de marca A1.

Stergerea liniilor din tabele. Comanda DELETE permite stergerea uneia sau mai multor linii dintr-un tabel

DELETE     FROM nume_tabel

[WHERE     conditie];

Din tabel se sterg acele linii care indeplinesc conditia data in clauza WHERE. Daca clauza WHERE este omisa, atunci toate liniile din tabel vor fi sterse.

De exemplu, pentru a sterge din tabelul ANGAJAT toti angajatii care au numele Ionescu, se introduce comanda:

DELETE     FROM ANGAJAT

WHERE      Nume = ‘Ionescu’;

Exercitii

E1.1. Creati o baza de date cu numele COMPANIE, cu urmatoarele tabele:

SECTIE (IdSectie, Nume, Buget)

ANGAJAT (IdAngajat, Nume, Prenume, DataNasterii, Adresa, DataAngajarii, Functia, Salariul)

PRODUSE (IdProdus, Denumire, Descriere)

COMPONENTE (IdComponenta, Denumire, Descriere)

FURNIZORI (IdFurnizor, Nume, Prenume, Localitatea)

CLIENTI (IdClient, Nume, Prenume, Localitatea)

Atributul subliniat este cheia primara a relatiei. Tipurile atributelor vor fi alese astfel incat sa se potriveasca cel mai bine semnificatiei acestora. Verificati tabelele create cu comenzile SHOW TABLES; si DESCRIBE nume_tabel;

E1.2. In tabelele create introduceti mai multe linii cu valori ale atributelor cat mai variate si verificati datele introduse cu comanda SELECT * FROM nume_tabel;

E1.3. Selectati si afisati urmatoarele date:

a)     Numele, prenumele, data nasterii si adresa tuturor angajatilor companiei, ordonati dupa nume.

b)     Numele, prenumele si data nasterii tuturor angajatilor nascuti dupa 1 martie 1970.

c)      Numele si bugetul tuturor sectiilor institutiei.

d)     Denumirea tuturor produselor realizate de compania respectiva.

e)     Denumirea tuturor componentelor folosite de compania respectiva.

f)      Numele si prenumele tuturor furnizorilor din localitatile Bucuresti, Ploesti si Craiova.

E1. Calculati si afisati urmatoarele date:

a)     Numarul de salariati ai companiei.

b)     Salariul mediu, minim si maxim al angajatilor companiei.

c)      Salariul mediu, minim si maxim pentru diferite categorii (functii) ale salariatilor (cercetator, proiectant, etc.)

d)     Numele si prenumele salariatilor care au salariul mai mare decat salariul mediu din companie.

e)     Numele si prenumele salariatilor care au salariul mai mare decat salariul mediu pentru functia careia apartin.

f)      Denumirile functiilor angajatilor din companie si numarul de angajati din fiecare functie.

g)     Bugetul total al companiei.

E1.5. Modificati datele stocate in tabele si verificati rezultatul acestor operatii:

a)     Acordati o marire de salariu cu 10% tuturor angajatilor cu functia ‘cercetator’ si afisati numele, prenumele si noul salariul al acestora.

b)     Acordati o marire de buget cu 10% sectiei cu numele ‘Productie’ si afisati denumirea si bugetul fiecarei sectii.