This shows you the differences between two versions of the page.
bd:laboratoare:04 [2018/10/06 16:38] ciprian.truica |
bd:laboratoare:04 [2023/03/20 10:05] (current) sorin.ciolofan [Cereri cu clauza WHERE] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Laboratorul 04. ===== | + | ===== Laborator 04 - Cereri SQL ===== |
+ | |||
+ | ==== Obiective ==== | ||
+ | |||
+ | * Prezentarea Cererii SELECT | ||
+ | * Cereri Simple | ||
+ | * Clauza Where | ||
+ | |||
+ | ==== Materiale ajutătoare ==== | ||
+ | |||
+ | [[bd:resurse:tables|Resurse BD]] | ||
+ | |||
+ | {{:bd:laboratoare:laborator04.pdf|Laborator 4}} | ||
====Cereri SQL==== | ====Cereri SQL==== | ||
Line 32: | Line 45: | ||
* **GROUP BY** expressions - stabilește criteriile de grupare a înregistrărilor (numele coloanelor folosite în criteriul de grupare); | * **GROUP BY** expressions - stabilește criteriile de grupare a înregistrărilor (numele coloanelor folosite în criteriul de grupare); | ||
* **HAVING** conditions - restricționarea înregistrărilor din grup la anumite condiții; | * **HAVING** conditions - restricționarea înregistrărilor din grup la anumite condiții; | ||
- | * **UNION [ALL] | INTERSECT | MINUS** - combină rândurile selectate de mai multe comenzi SELECT prin aplicarea anumitor restricții; | + | * **UNION [ALL] | INTERSECT | MINUS** - combină liniile selectate de mai multe comenzi SELECT prin aplicarea anumitor restricții; |
* **ORDER BY** expressions|positions ordonează înregistrările selectate după coloanele din expresie sau în ordinea coloanelor specificate prin poziție; | * **ORDER BY** expressions|positions ordonează înregistrările selectate după coloanele din expresie sau în ordinea coloanelor specificate prin poziție; | ||
* **FOR UPDATE OF** - face o blocare (lock) a înregistrărilor în vederea modificării anumitor coloane; | * **FOR UPDATE OF** - face o blocare (lock) a înregistrărilor în vederea modificării anumitor coloane; | ||
Line 39: | Line 52: | ||
====Cereri simple==== | ====Cereri simple==== | ||
- | Ex. 1. Să se creeze o copie a tabelului DEPT care să se numească departamente cu următoarele coloane: id_dep number(2), den_dep varchar2(14), locatie varchar2(13). | + | <color red>Ex. 1.</color> Să se creeze o copie a tabelului DEPT care să se numească departamente cu următoarele coloane: |
- | <code sql> | + | * id_dep number(2) |
- | CREATE TABLE departamente AS | + | * den_dep varchar2(14) |
- | SELECT | + | * locatie varchar2(13) |
- | deptno id_dep, | + | |
- | name den_dep, | + | |
- | loc locatie | + | |
- | FROM dept; | + | |
- | </code> | + | |
- | Ex. 2. Să se creeze o copie a tabelului EMP care să se numească angajati. | + | {{:bd:laboratoare:carbon_lab4_ex01.png?nolink&320|}} |
- | <code sql> | + | |
- | CREATE TABLE angajati AS | + | |
- | SELECT | + | |
- | empno id_ang, | + | |
- | ename nume, | + | |
- | job functie, | + | |
- | mgr id_sef, | + | |
- | hiredate data_ang, | + | |
- | sal salariu, | + | |
- | comm comision, | + | |
- | deptno id_dep | + | |
- | FROM emp; | + | |
- | </code> | + | |
- | Ex. 3. Să se selecteze toate înregistrările și toate coloanele din tabela angajați | + | <color red>Ex. 2.</color> Să se creeze o copie a tabelului EMP care să se numească angajati. |
- | <code sql> | + | |
- | SELECT * FROM angajati; | + | |
- | </code> | + | |
- | Ex. 4. Să se selecteze id-ul și numele departamentului din tabela departamente. | + | {{:bd:laboratoare:carbon_lab4_ex02.png?nolink&290|}} |
- | <code sql> | + | |
- | SELECT id_dep, den_dep | + | |
- | FROM departamente; | + | |
- | </code> | + | |
- | Ex. 5. Să se selecteze id-ul, numele, funcția și data angajării pentru toți angajații din firmă, ordonați descrescător în funcție de id. Concatenați id-ul cu numele. | + | <color red>Ex. 3.</color> Să se selecteze toate înregistrările și toate coloanele din tabela angajați |
- | <code sql> | + | |
- | SELECT | + | {{:bd:laboratoare:carbon_lab4_ex03.png?nolink&280|}} |
- | id_ang||'-'||nume angajat, | + | |
- | functie, | + | |
- | data_ang | + | |
- | FROM angajati | + | |
- | ORDER BY id_ang desc; | + | |
- | </code> | + | |
+ | <color red>Ex. 4.</color> Să se selecteze id-ul și numele departamentului din tabela departamente. | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex04.png?nolink&280|}} | ||
+ | |||
+ | <color red>Ex. 5.</color> Să se selecteze id-ul, numele, funcția și data angajării pentru toți angajații din firmă, ordonați descrescător în funcție de id. Concatenați id-ul cu numele. | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex05.png?nolink&320|}} | ||
+ | |||
+ | <note important> | ||
Obs. Operatorul de concatenare a șirurilor de caractere este: | Obs. Operatorul de concatenare a șirurilor de caractere este: | ||
* În Oracle || | * În Oracle || | ||
* În Microsoft SQL Server + | * În Microsoft SQL Server + | ||
* În Oracle, MySQL, PostgreSQL și Microsoft SQL Server există funcția concat(diferite implementări) pentru concatenarea șirurilor de caractere (se va prezenta ca funcție în laboratorul 6). | * În Oracle, MySQL, PostgreSQL și Microsoft SQL Server există funcția concat(diferite implementări) pentru concatenarea șirurilor de caractere (se va prezenta ca funcție în laboratorul 6). | ||
+ | </note> | ||
- | Ex. 6. Să se selecteze id-ul, numele, funcția și venitul lunar pentru toți angajații din firmă. Să se adauge la select o coloană goală care să se numească semnătura. Concatenați id-ul cu numele. Ordonați după departament. | + | <color red>Ex. 6.</color> Să se selecteze id-ul, numele, funcția și venitul lunar pentru toți angajații din firmă. Să se adauge la select o coloană goală care să se numească semnătura. Concatenați id-ul cu numele. Ordonați după departament. |
- | <code sql> | + | |
- | SELECT | + | |
- | id_ang||'-'||nume angajat, | + | |
- | functie, | + | |
- | salariu+nvl(comision,0) as "venit lunar", | + | |
- | ' ' as semnatura | + | |
- | FROM angajati | + | |
- | ORDER BY id_dep; | + | |
- | </code> | + | |
- | Ex. 7. Să se selecteze numele și funcția angajaților. | + | {{:bd:laboratoare:carbon_lab4_ex06.png?nolink&450|}} |
- | <code sql> | + | |
- | SELECT nume, 'cu functie', functie | + | |
- | FROM angajati; | + | |
- | </code> | + | |
+ | <color red>Ex. 7.</color> Să se selecteze numele și funcția angajaților. | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex07.png?nolink&360|}} | ||
+ | |||
+ | <color red>Ex. 8.</color> Să se facă o listă cu numele departamentului și codul acestuia. Ordonați după numele departamentului. | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex08.png?nolink&530|}} | ||
- | Ex. 8. Să se facă o listă cu numele departamentului și codul acestuia. Ordonați după numele departamentului. | ||
- | <code sql> | ||
- | SELECT | ||
- | den_dep||' are codul '||id_dep "Lista Departamente" | ||
- | FROM departamente | ||
- | ORDER BY den_dep asc; | ||
- | </code> | ||
====Cereri cu clauza WHERE==== | ====Cereri cu clauza WHERE==== | ||
Line 123: | Line 102: | ||
* Listă de valori | * Listă de valori | ||
- | Pentru a compune expresii logice cu mai multe condiții se folosesc operatorii logici **AND** și **OR**. Predicatul **AND** returnează adevărat dacă toate condițiile sunt adevărate, predicator **OR** este adevărat dacă cel puțin una dintre condiții este adevărată. Se pot combina operatorii **AND** și **OR** în aceeași expresie logică în clauza **WHERE**, iar în acest caz operatorii **AND** sunt evaluați primii și apoi operatorii **OR** (precedența operatorului **AND** este mai mare decât cea a operatorului **OR**). Dacă operatorii au precedență egală atunci ei se evaluează de la stânga la dreapta. | + | <note> |
+ | Pentru a compune expresii logice cu mai multe condiții se folosesc operatorii logici **AND** și **OR**. | ||
+ | |||
+ | **AND** returnează adevărat dacă toate condițiile sunt adevărate. | ||
+ | |||
+ | **OR** este adevărat dacă cel puțin una dintre condiții este adevărată. | ||
+ | |||
+ | Se pot combina operatorii **AND** și **OR** în aceeași expresie logică în clauza **WHERE**, iar în acest caz operatorii **AND** sunt evaluați primii și apoi operatorii **OR** (precedența operatorului **AND** este mai mare decât cea a operatorului **OR**). | ||
+ | |||
+ | Dacă operatorii au precedență egală atunci ei se evaluează de la stânga la dreapta. | ||
+ | </note> | ||
Operatorii de comparație pot fi de două feluri: operatori logici și operatori SQL | Operatorii de comparație pot fi de două feluri: operatori logici și operatori SQL | ||
Line 133: | Line 122: | ||
| ''>='' | Mai mare sau egal decât | | | ''>='' | Mai mare sau egal decât | | ||
| ''<'' | Mai mic decât | | | ''<'' | Mai mic decât | | ||
- | | ''<='' | Mai mic sau egal decât | | + | | ''%%<=%%'' | Mai mic sau egal decât | |
^Operator SQL^Semnificație^ | ^Operator SQL^Semnificație^ | ||
- | | ''val BETWEEN val1 AND val2'' | Verifică daca o valoare se află între două valori date (inclusiv). Echivalent cu ''val >= val1 AND val <= val2'' | | + | | ''val BETWEEN val1 AND val2'' | Verifică daca o valoare se află între două valori date (inclusiv). Echivalent cu ''val >= val1 AND val %%<=%% val2'' | |
| ''IN (list)'' | Compară dacă valoarea se află în lista de valori list. ''val IN (a,b,c)'' este echivalent cu ''val = a OR val = b OR val = c'' | | | ''IN (list)'' | Compară dacă valoarea se află în lista de valori list. ''val IN (a,b,c)'' este echivalent cu ''val = a OR val = b OR val = c'' | | ||
| ''LIKE string'' | Compară cu un model de tip caracter/șir de caractere | | | ''LIKE string'' | Compară cu un model de tip caracter/șir de caractere | | ||
Line 146: | Line 135: | ||
| ''!='' | Diferit de (Unix, Windows) | | | ''!='' | Diferit de (Unix, Windows) | | ||
| ''^='' | Diferit de (IBM) | | | ''^='' | Diferit de (IBM) | | ||
- | | ''<>'' | Diferit de (Doate OS) | | + | | ''<>'' | Diferit de (Toate OS) | |
- | | ''NOT val { = | > | >= | < | <= }'' | Neagă operatorul de comparație | | + | | ''NOT val { = | > | >= | < | %%<=%% }'' | Neagă operatorul de comparație | |
Line 158: | Line 147: | ||
Precedența operatorilor logici este următoarea: | Precedența operatorilor logici este următoarea: | ||
- | - Operatorii de comparație și operatorii SQL au precedență egală (''=,<,<=,>,>=,<>,!=,^=, BETWEEN, IN, LIKE, IS NULL'') | + | - Operatorii de comparație și operatorii SQL au precedență egală (''=,<,%%<=%%,>,>=,<>,!=,^=, BETWEEN, IN, LIKE, IS NULL'') |
- ''NOT'' (pentru inversarea rezultatului unei expresii logice) | - ''NOT'' (pentru inversarea rezultatului unei expresii logice) | ||
- ''AND'' | - ''AND'' | ||
- ''OR'' | - ''OR'' | ||
+ | <note> | ||
Pentru a fi siguri de ordinea de execuție a operațiilor se recomandă folosirea parantezelor rotunde. | Pentru a fi siguri de ordinea de execuție a operațiilor se recomandă folosirea parantezelor rotunde. | ||
+ | </note> | ||
- | Deși comenzile SQL nu sunt case sensitive, datele de tip șir de caractere sunt. Atenție când folosiți operatorii de comparație și negație pe șiruri de caractere. | + | <color red>Ex. 9.</color> Să se listeze toți angajații din departamentul 10. |
- | Funcția ''lower(parameter)'' este o funcție de sistem care transformă toate caracterele din șirul de caractere parameter în litere mici. Funcția inversă este ''upper(parameter)'' care transformă toate caracterele din parameter în litere mari. Parametrul poate să fie un șir de caractere, o expresie(funcție) care întoarce un șir de caractere sau un nume de coloană în care se țin valori de tip șir de caractere. | + | {{:bd:laboratoare:carbon_lab4_ex09.png?nolink&380|}} |
- | Operatorul ''LIKE'' poate fi folosit cu simbolurile ''_'' și ''%'' pentru a selecta rândurile care se potrivesc cu un caracter sau un subșir de caractere. | + | <color red>Ex. 10.</color> Să se selecteze toți angajații care au funcția MANAGER. Ordonați după id-ul departamentului. |
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex10.png?nolink&380|}} | ||
+ | |||
+ | <color red>Ex. 11.</color> Selectați toate persoanele care s-au angajat intr-o anumită perioadă. | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex11a.png?nolink&460|}} | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex11b.png?nolink&520|}} | ||
+ | |||
+ | <color red>Ex. 12.</color> Să se listeze id-ul, numele, funcția, venitul lunar pentru angajații care au următoarele id-uri: 7499,7902, 7876. | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex12a.png?nolink&480|}} | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex12b.png?nolink&520|}} | ||
+ | |||
+ | <note important> | ||
+ | Operatorul ''LIKE'' poate fi folosit cu simbolurile ''_'' și ''%'' pentru a selecta liniile care se potrivesc cu un caracter sau un subșir de caractere. | ||
^Simbol^Semnificație^ | ^Simbol^Semnificație^ | ||
| ''_'' | Înlocuit într-un șir, poate să fie orice caracter | | | ''_'' | Înlocuit într-un șir, poate să fie orice caracter | | ||
| ''%'' | Orice secvență de mai multe caractere | | | ''%'' | Orice secvență de mai multe caractere | | ||
+ | </note> | ||
+ | <color red>Ex. 13.</color> Să se selecteze toate persoanele care au fost angajate în anul 1980. | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex13.png?nolink&360|}} | ||
+ | |||
+ | <color red>Ex. 14.</color> Să se selecteze toate persoanele al căror nume începe cu litera F și numele funcției are 7 caractere. | ||
+ | |||
+ | {{:bd:laboratoare:carbon_lab4_ex14.png?nolink&500|}} | ||
+ | |||
+ | <note warning> | ||
Dacă se compară o coloană sau expresie cu ''NULL'', atunci operatorul de comparație trebuie să fie ''IS [NOT] NULL''. | Dacă se compară o coloană sau expresie cu ''NULL'', atunci operatorul de comparație trebuie să fie ''IS [NOT] NULL''. | ||
Dacă se folosește orice alt operator rezultatul va fi întotdeauna **FALSE**. Dacă în loc de ''IS [NOT] NULL'' se utilizau expresiile ''comision = NULL'' sau ''comision <> NULL'' rezultatul ar fi fost **FALSE**. | Dacă se folosește orice alt operator rezultatul va fi întotdeauna **FALSE**. Dacă în loc de ''IS [NOT] NULL'' se utilizau expresiile ''comision = NULL'' sau ''comision <> NULL'' rezultatul ar fi fost **FALSE**. | ||
+ | </note> | ||
- | Ex. 9. Să se listeze toți angajații din departamentul 10. | + | <color red>Ex. 15.</color> Să se listeze angajații din departamentul 20 care nu au primit comision. |
- | <code sql> | + | |
- | SELECT | + | |
- | a.id_ang ecuson, | + | |
- | a.nume, | + | |
- | a.data_ang AS "Data Angajarii", | + | |
- | a.salariu | + | |
- | FROM angajati a | + | |
- | WHERE id_dep = 10; | + | |
- | </code> | + | |
- | Ex. 10. Să se selecteze toți angajații care au funcția MANAGER. Ordonați după id-ul departamentului. | + | {{:bd:laboratoare:carbon_lab4_ex15.png?nolink&420|}} |
- | <code sql> | + | |
- | SELECT | + | |
- | id_dep "Nr. deptartament", | + | |
- | nume, | + | |
- | functie, | + | |
- | salariu, | + | |
- | data_ang AS "Data Angajarii" | + | |
- | FROM angajati | + | |
- | WHERE lower(functie) = 'manager' | + | |
- | ORDER BY id_dep; | + | |
- | </code> | + | |
- | Ex. 11. Selectați toate persoanele care s-au angajat intr-o anumită perioadă. | + | <note important> |
- | <code sql> | + | Deși comenzile SQL nu sunt case sensitive, datele de tip șir de caractere sunt. |
- | SELECT | + | |
- | id_dep departament, | + | |
- | functie, | + | |
- | nume, | + | |
- | data_ang AS "Data Angajarii" | + | |
- | FROM angajati | + | |
- | WHERE data_ang BETWEEN '1-MAY-1981' AND '31-DEC-1981' | + | |
- | ORDER BY 1, 2 desc; | + | |
- | </code> | + | |
- | <code sql> | + | Atenție când folosiți operatorii de comparație și negație pe șiruri de caractere. |
- | SELECT | + | </note> |
- | id_dep departament, | + | |
- | functie, | + | |
- | nume, | + | |
- | data_ang AS "Data Angajarii" | + | |
- | FROM angajati | + | |
- | WHERE data_ang >= '1-MAY-1981' AND data_ang <= '31-DEC-1981' | + | |
- | ORDER BY 1, 2 desc; | + | |
- | </code> | + | |
- | Ex. 12. Să se listeze id-ul, numele, funcția, venitul lunar pentru angajații care au următoarele id-uri: 7499,7902, 7876. | + | <note> |
- | <code sql> | + | Funcția ''lower(parameter)'' este o funcție de sistem care transformă toate caracterele din șirul de caractere parameter în litere mici. |
- | SELECT | + | |
- | id_ang AS ecuson, | + | |
- | nume, | + | |
- | functie, | + | |
- | salariu + nvl(comision,0) "Venit lunar" | + | |
- | FROM angajati | + | |
- | WHERE id_ang IN (7499,7902,7876) | + | |
- | ORDER BY nume; | + | |
- | </code> | + | |
- | <code sql> | + | Funcția inversă este ''upper(parameter)'' care transformă toate caracterele din parameter în litere mari. |
- | SELECT | + | |
- | id_ang AS ecuson, | + | |
- | nume, | + | |
- | functie, | + | |
- | salariu + nvl(comision,0) "Venit lunar" | + | |
- | FROM angajati | + | |
- | WHERE id_ang = 7499 OR id_ang = 7902 OR id_ang = 7876 | + | |
- | ORDER BY nume; | + | |
- | </code> | + | |
- | Ex. 13. Să se selecteze toate persoanele care au fost angajate în anul 1980. | + | Parametrul poate să fie un șir de caractere, o expresie(funcție) care întoarce un șir de caractere sau un nume de coloană în care se țin valori de tip șir de caractere. |
- | <code sql> | + | </note> |
- | SELECT | + | |
- | id_ang AS ecuson, | + | |
- | nume, | + | |
- | functie, | + | |
- | data_ang AS "Data Angajarii" | + | |
- | FROM angajati | + | |
- | WHERE data_ang LIKE '%80'; | + | |
- | </code> | + | |
- | Ex. 14. Să se selecteze toate persoanele al căror nume începe cu litera F și numele funcției are 7 caractere. | + | <color red>Ex. 16.</color> Să se listeze angajații care au primit comision și au funcția SALESMAN. |
- | <code sql> | + | {{:bd:laboratoare:lab04_16.png?nolink&500|}} |
- | SELECT | + | |
- | id_ang AS ecuson, | + | |
- | nume, | + | |
- | functie, | + | |
- | data_ang AS "Data Angajarii" | + | |
- | FROM angajati | + | |
- | WHERE nume LIKE 'F%' AND functie LIKE '_______'; | + | |
- | </code> | + | |
- | Ex. 15. Să se listeze angajații din departamentul 20 care nu au primit comision. | ||
- | <code sql> | ||
- | SELECT | ||
- | id_ang as ecuson, | ||
- | nume, | ||
- | functie, | ||
- | salariu, | ||
- | comision | ||
- | FROM angajati | ||
- | WHERE | ||
- | (comision = 0 OR comision IS NULL) AND | ||
- | id_dep = 20 | ||
- | ORDER BY nume; | ||
- | </code> | ||
- | Ex. 16. Să se listeze angajații care au primit comision și au funcția SALESMAN. | + | <color red>Ex. 17.</color> Să se selecteze toți angajații care au funcția MANAGER și salariul peste 1500, și toți angajații care au funcția ANALYST. |
- | <code sql> | + | |
- | SELECT | + | |
- | id_ang as ecuson, | + | |
- | nume, | + | |
- | functie, | + | |
- | salariu, | + | |
- | comision | + | |
- | FROM angajati | + | |
- | WHERE | + | |
- | (comision != 0 AND comision IS NOT NULL) AND | + | |
- | functie = upper('salesman') | + | |
- | ORDER BY nume; | + | |
- | </code> | + | |
- | Ex. 17. Să se selecteze toți angajații care au funcția MANAGER și salariul peste 1500, și toți angajații care au funcția ANALYST.<code sql> | + | {{:bd:laboratoare:carbon_lab4_ex17.png?nolink&380|}} |
- | SELECT | + | |
- | id_ang AS ecuson, | + | |
- | nume, | + | |
- | functie, | + | |
- | salariu, | + | |
- | id_dep departament | + | |
- | FROM angajati | + | |
- | WHERE | + | |
- | salariu > 1500 AND | + | |
- | lower(functie) = 'manager' OR | + | |
- | upper(functie) = 'ANALYST' | + | |
- | ORDER BY functie, nume DESC; | + | |
- | </code> | + | |
====Exerciții individuale==== | ====Exerciții individuale==== |