Exemple de fraze SQL
Enunt: Se da o societate comerciala cu profil de activitate repararea de aparatura informatica (servere, computere personale, imprimante, dispozitive mobile etc). Societatea are in componenta persoane specializate pe domenii. Pentru fiecare reparatie se intocmeste o fisa pe care se inscriu date despre reparatia efectuata.
I. Structura tabelelor
Pentru a administra activitatea companiei, proiectam urmatoarea baza de date:
Angajati
id_angajat - autonumber
nume - text
prenume - text
adresa - text
telefon - text
data_angajare - date
specializare - text
salariu - numeric
|
Reparatii
nr_reparatie - autonumber
data_reparatie - date
obiect - text
an_fabricatie - number
diagnostic - text
tarif - number
id_angajat - number
id_client - number
|
Clienti
id_client - autonumber
denumire - text
adresa - text
telefon - text
e_mail - text
website - text
observatii - text
|
II. Operatii creare, modificare tabele si adaugare de inregistrari
1. Sa se realizeze o fraza SQL pentru crearea tabelei Angajati (mai putin ultimul camp dintre cele enumerate mai sus)
CREATE TABLE Angajati (id_angajat COUNTER PRIMARY KEY, nume TEXT(50), prenume TEXT(50), adresa TEXT(100), telefon TEXT(20), data_angajare DATE, specializare TEXT(20));
2. Sa se adauge tabelei Angajati ultimul camp (salariu)
ALTER TABLE Angajati ADD COLUMN salariu NUMBER
3. Sa se insereze in tabela un angajat nou cu datele:
nume - Popescu
prenume - Marius
adresa - Str. Revolutiei nr.1
telefon - 0722.77.28.55
data_angajare - 24/01/2001
salariu - 1500
specializare - electronica
INSERT INTO Angajati (nume, prenume, adresa, telefon, data_angajare, salariu, specializare) VALUES ("Popescu", "Marius" , "Str. Revolutiei nr.1", "0722.77.28.55", "01/24/2001", 1500, "electronica")
III. Interogari (selectii) simple
4. Sa se afiseze numele, prenumele si telefoanele angajatilor cu salarii mai mari sau egale cu 1000, ordonand alfabetic lista rezultata. In lista nu vor aparea angajatii care nu au telefon.
SELECT nume, prenume, telefon FROM Angajati WHERE salariu>=1000 AND telefon IS NOT NULL ORDER BY nume ASC
5. Sa se selecteze toate datele despre persoanele angajate in martie 2002 si au una dintre specializarile “informatica” sau “electronica”.
SELECT * FROM Angajati
WHERE data_angajare BETWEEN #03/01/2002# AND #03/31/2002# AND specializare IN ("informatica", "electronica")
ORDER BY salariu DESC, data_angajare ASC
sau
SELECT * FROM Angajati
WHERE (data_angajare >= #3/1/2002# AND data_angajare <= #3/31/2002#) AND specializare IN ("informatica", "electronica")
ORDER BY salariu DESC, data_angajare ASC
6. Sa se afiseze toate reparatiile efectuate in ultimele 10 zile.
SELECT * FROM Reparatii WHERE data_reparatie > Date()-10
7. Sa se selecteze numele si prenumele angajatilor, sa se calculeze vechimea in munca pentru fiecare angajat (in ani). Intr-un alt camp, calculat si denumit spor_vechime, sa se afiseze 0 daca vechimea este sub 2 ani si 1 daca vechimea este peste 2 ani.
SELECT nume, prenume, (Date()-data_angajare)/365 AS vechime, IIF(vechime<2,0,1) AS spor_vechime FROM Angajati
IV. Interogari multiple
8. Sa se afiseze denumirea si adresa clientilor care au avut de reparat produsul “imprimanta”.
SELECT DISTINCT denumire, adresa
FROM Clienti, Reparatii
WHERE Reparatii.id_client = Clienti.id_client AND obiect="imprimanta"
sau
SELECT DISTINCT denumire, adresa
FROM Clienti INNER JOIN Reparatii ON Reparatii.id_client = Clienti.id_client
WHERE obiect="imprimanta"
9. Sa se afiseze numele angajatilor care au realizat reparatii pentru clientul “Millenium Trading” si datele la care au fost efectuate aceste reparatii.
SELECT nume, prenume, data_reparatie
FROM Angajati, Reparatii, Clienti
WHERE Angajati.id_angajat = Reparatii.id_angajat AND Reparatii.id_client = Clienti.id_client AND Clienti.denumire = "Millenium Trading"
sau
SELECT nume, prenume, data_reparatie
FROM (Angajati INNER JOIN Reparatii ON Angajati.id_angajat = Reparatii.id_angajat) INNER JOIN Clienti ON Reparatii.id_client = Clienti.id_client
WHERE Clienti.denumire = "Millenium Trading"
V. Interogari de selectie ce utilizeaza functii de grup
10. Sa se calculeze costul mediu al unei categorii de reparatii (medie per calculator, medie per imprimanta, etc). Lista va fi ordonata descrescator dupa cost.
SELECT obiect, AVG(tarif) as cost_mediu
FROM Reparatii
GROUP BY obiect
ORDER BY AVG(tarif) DESC
11. Sa se calculeze costul mediu al unei categorii de reparatii, afisandu-se numai acelea pentru care costul mediu depaseste valoarea 750.
Obs.: Clauza WHERE nu lucreaza cu functii totalizatoare, deci se foloseste clauza HAVING.
SELECT obiect, AVG(tarif) as cost_mediu
FROM Reparatii
GROUP BY obiect
HAVING AVG(tarif)>750
ORDER BY AVG(tarif) DESC
12. Sa se calculeze suma incasata de la fiecare client in anul 2002.
SELECT denumire, SUM(tarif) as suma_incasata
FROM Clienti INNER JOIN Reparatii ON Clienti.id_client = Reparatii.id_client
WHERE Year (data_reparatie)=2002
GROUP BY denumire
VI. Interogari tip UNION
13. Sa se afiseze toti angajatii si toti clientii care au domiciliul in Craiova.
SELECT nume, adresa
FROM Angajati
WHERE adresa LIKE "*Craiova*"
UNION
SELECT denumire as nume, adresa
FROM Clienti
WHERE adresa LIKE "*Craiova*"
VII. Interogari de stergere
14. Sa se stearga din baza de date angajatii care au codul 1 sau 3.
DELETE FROM Angajati
WHERE id_angajat=1 OR id_angajat=3
15. Sa se stearga din baza de date angajatii care au participat la reparatiile cu numerele 1 si 3.
DELETE FROM Angajati WHERE id_angajat IN (SELECT id_angajat FROM Reparatii WHERE nr_reparatie=1 OR nr_reparatie=3)
VIII. Interogari de actualizare (modificare) date
16. Sa se modifice tariful reparatiilor la 1500 pentru obiectul “calculator”.
UPDATE Reparatii
SET tarif=1500
WHERE obiect=”calculator”
17. Sa se mareasca salariile cu 20% pentru angajatii veniti in firma inainte de 1 ianuarie 2002.
UPDATE Angajati
SET salariu = salariu *1.2
WHERE data_angajare<#1/1/2002#
IX. Interogari de tip crosstab
18. Sa se afiseze cate reparatii a efectuat fiecare angajat in fiecare an. Numele angajatilor se vor afisa pe linii, iar anii pe coloanele tabelului.
TRANSFORM COUNT(nr_reparatie)
SELECT nume
FROM Angajati INNER JOIN Reparatii ON Angajati.id_angajat = Reparatii.id_angajat
GROUP BY nume
PIVOT YEAR (data_reparatie)
O interogare de tip crosstab se realizeaza punand valorile la TRANSFORM, randurile la SELECT, iar coloanele la PIVOT. In exemplu de mai sus, COUNT(nr_reparatie) reprezinta valorile, nume reprezinta randurile, iar YEAR (data_reparatie) coloanele.
X. Alte tipuri de interogari
19. Sa se afiseze clientii care incep cu un sir introdus de la tastatura.
SELECT denumire, telefon, e_mail
FROM Clienti
WHERE denumire LIKE [Introduceti numele] & "*"
XI. Recomandari
Clauzele Inner Join, Left Join, Right Join sunt mai rapide decat clauzelor Where.
Pe cat posibil, sa se evite utilizarea operatorului Not.
Nu folositi clauza Order By pentru o subselectie.
Evitati, pe cat posibil, folosirea tabelelor temporale.
Pentru Inner Join, tabelul care are linii mai putine sa fie al doilea în operatia de compunere.
XII. Tipuri si exemple de relatii
studenti - orase-de-nastere, orase - judete, orase - tari, facturi-clienti, produse - categorii, produse - firme-producatoare, jucatori - tari, jucatori - echipe, echipe - tari
studenti - examene, studenti - profesori, facturi - produse, actori - filme, carti - abonati
spectacole - artisti, opere - spectacole (reprezentatii), opere - roluri, opere - compozitori
- pagina -
Dostları ilə paylaş: |