SEMINAR

Operatorul LIKE, BETWEEN, IN

Sa se afiseze clientii pentru care denumirea localitatii incepe cu 'B'.

SELECT denc FROM clienti WHERE loc LIKE 'B%';

Sa se afiseze comenzile a caror valoarea este intre 500000 si 1500000 lei.

SELECT nrcom FROM comenzi WHERE val BETWEEN 500000 AND 1500000;

Sa se afiseze clientii din Bucuresti si Cluj.

SELECT denc FROM clienti WHERE loc in ('Bucuresti', 'Cluj');

Operatorii Set combina doua relatii intr-o singura relatie (cereri compuse

Nota: Relatiile trebuie sa aiba aceeasi schema si se obtine o relatie rezultat cu aceeasi

schema

UNION (reuniune)

UNION ALL (reuniune, include duplicatele)

INTERSECT (intersectie)

MINUS (diferenta)

FUNCTII

Functii single-row (sau scalare). O functie single-row intoarce un singur rand rezultat pentru fiecare rand al tabelei interogate sau view

Functii de grup (sau agregate). O functie de grup intoarce un singur rand rezultat pentru un grup de randuri interogate. Functiile de grup pot apare in clauza HAVING

Functii de tip caracter

Operatorul de concatenare(||)

Sa se afiseze denumirea produsului si pretul unitar sub forma:

produsul : Mouse Pad are pretul unitar: 21000 lei.

SELECT 'produsul:'|| initcap(denp)||' are pretul unitar:'||pu|| 'lei'

FROM produse;

Functia CONCAT(), functia LENGTH(), functia SUBSTR()

Sa se afiseze denumirea clientului, denumirea clientului concatenata cu localitatea si lungimea atributului denc, numai pentru localitatile al caror nume au prima litera "B".

SELECT denc, concat(denc,loc), length(denc) FROM clienti WHERE substr(loc,1,1)='B';

Functii de tip numeric

Functia ROUND()

Sa se afiseze numarul 45,923 rotunjit la doua zecimale si la 0 zecimale .

SELECT round(45.923,2), round(45.923,0) FROM dual;

Functii de tip data calendaristica

Functia SYSDATE

Sa se afiseze comanda a carei valoare este de 420000 lei si perioada de timp corespunzatoare (in saptamani) intre data incheierii comenzii si data curenta.

SELECT nrcom, (sysdate-datai)/7 sapt FROM comenzi WHERE val=420000;

Functiile MONTH_BETWEEN(), ADD_MONTHS(), NEXT_DAY(), LAST_DAY()

Sa se afiseze comenzile, data incheierii comenzilor, numarul de luni intre data curenta si data incheierii, data corespunzatoare dupa 6 luni de la data incheierii, urmatoarea zi de vineri dupa data incheierii si ultima zi din luna din care face parte data incheierii comenzii.

SELECT nrcom, datai, MONTHS_BETWEEN(sysdate, datai) luni,

ADD_MONTHS(datai,6), NEXT_DAY(datai, 'FRIDAY'), LAST_DAY(datai)

FROM comenzi;

Functia ROUND()

Sa se afiseze comenzile a caror termen de livrare este 2002. Se va rotunji data incheierii la prima zi din luna corespunzatoare, daca data incheierii este in prima jumatate a lunii sau la prima zi din luna urmatoare.

SELECT nrcom, datai, ROUND(datai, 'MONTH') data FROM comenzi WHERE termen LIKE '%02';

Functii de conversie

Functia TO_CHAR(d [, fmt ])

Functia TO_DATE(char [, fmt ])

Functia TO_NUMBER(char [,fmt ])

Sa se afiseze comenzile si data incheierii in format "MM/YY".

SELECT nrcom, TO_CHAR(datai, 'MM/YY') data FROM comenzi;

Sa se afiseze comenzile si data incheierii in formatul " Month dd, YYYY" pentru comenzile incheiate in 10 februarie 2002.

SELECT nrcom, datai FROM comenzi

WHERE datai=TO_DATE( 'February 10, 2002', 'Month dd,YYYY');

Sa se afiseze valoarea comenzilor precedata de ($) si in formatul respectiv

SELECT to_char(val, '$9999999') valoare

FROM comenzi;

Functia USER intoarce numele utilizatorului curent (tip data VARCHAR2).

Functia Decode

Sa se afiseze codul produsului si pretul unitar marit cu 2 daca produsul este 'Mouse pad', marit cu 1.5 daca produsul este 'Cablu audio CR-ROM, marit cu 1.20 daca produsul este 'Cablu IDE'. Pentru celelalte produse, pretul ramane neschimbat.

SELECT codp, pu pret,

DECODE (denp, 'Mouse pad', pu*2, 'Cablu audio CD-ROM', pu*1.5, 'Cablu IDE', pu*1.20, pu) pret_marit FROM produse;

La data incheierii se adauga 7 luni, se dateaza urmatoarea zi de luni dupa acea data si se schimba formatul ei de afisare (Monday, August 12th, 2002). Ordonarea tuplurilor dupa data incheierii.

SELECT to_char(next_day(add_months(datai,7),'MONDAY'),'fmDay,

Month ddth, YYYY') data FROM comenzi ORDER BY datai;

Realizarea Join-urilor intre relatii. Tipuri de join-uri

Join de egalitate

Sa se afiseze denumirea produsului, pretul unitar corespunzator si valoarea totala a vanzarilor pentru fiecare produs.

SELECT p.denp, p.pu, sum(p.pu*r.cant) vanzari

FROM produse p, rand_comanda r WHERE p.codp=r.codp

GROUP BY p.denp, p.pu;

Sa se afiseze comenzile (data incheierii, termen de livrare) in care apare produsul 'Cablu IDE'

Join extern

Sa se afiseze denumirea produsului si cantitatea totala vanduta, corespunzatoare pentru toate produsele din nomenclatorul de produse.

SELECT p.denp denumire, sum(r.cant) cantitate

FROM produse p, rand_comanda r WHERE p.codp=r.codp(+)

GROUP BY p.denp ;

Join tabela cu aceeasi tabela

Sa se afiseze numele fiecarui angajat si numele sefului direct superior.

Tabela ANGAJATI (marca, nume, sal, functie, data_ang, codsef).

CREATE TABLE angajati

(marca number(3) primary key,

nume varchar2(30),

sal number(10),

functie varchar2(10),

data_ang date,

codsef number(3) references angajati(marca));

SELECT muncitori.nume||' lucreaza pentru: '||sefi.nume

FROM angajati muncitori, angajati sefi

WHERE muncitori.codsef=sefi.marca;

Functii de grup

AVG([DISTINCT|ALL] n)

COUNT()

MAX([DISTINCT|ALL] expr)

MIN([DISTINCT|ALL] expr)

SUM([DISTINCT|ALL] n)

Sa se afiseze valoarea maxima, valoarea medie, valoarea minima si valoarea totala pentru comenzile cu termen de livrare februarie 2002.

SELECT avg(val), max(val), min(val), sum(val) FROM comenzi

WHERE termen like '%JUN%';

Sa se afiseze data ultimei comenzi incheiate si data celei mai vechi comenzi incheiata

SELECT min(datai), max(datai)

FROM comenzi;

Sa se afiseze numarul de produse cu stocul zero

SELECT count(*) FROM produse WHERE stoc=0;

Sa se afiseze numarul de comenzi

SELECT count(codc) FROM comenzi;

Sa se afiseze numarul de produse vandute

SELECT count(distinct(codp)) FROM rand_comanda;

Sa se afiseze cantitatea medie vanduta din fiecare produs. Ordonare dupa cantitate.

SELECT codp, avg(cant) cant_medie FROM rand_comanda

GROUP BY codp ORDER BY avg(cant);

Sa se afiseze produsele si cantitatea medie vanduta numai pentru acele produse a caror cantitate medie vanduta este mai mare de 100

SELECT codp, avg(cant) FROM rand_comanda

GROUP BY codp HAVING avg(cant)>100;

Sa se afiseze numarul de clienti din fiecare localitate cu exceptia orasului Bucuresti si pentru acele localitati care au mai multi de 10 clienti

SELECT count(codc) , loc

FROM clienti

WHERE loc<>'Bucuresti'

GROUP BY loc

HAVING count(codc)>10

ORDER BY loc;

Realizarea Subcererilor

Sa se afiseze clientii din aceeasi localitate cu clientul Python.

SELECT denc FROM clienti

WHERE loc=(select loc from clienti where denc='Python');

Sa se afiseze produsele care au pretul unitar cel mai mic

SELECT codp, denp FROM produse

WHERE pu=(select min(pu) from produse);

Sa se afiseze produsele (codp) si cantitatea minima vanduta corespunzatoare (numai acele produse care au aceasta cantitate minima mai mare decat cantitatea minima a produsului cu codul "MP1" ).

SELECT codp, min(cant) cant_min FROM rand_comanda

GROUP BY codp HAVING min(cant)>(select min(cant) from rand_comanda where codp='MP1');

Sa se afiseze produsele a caror cantitate vanduta este minima

SELECT codc, codp FROM rand_comanda

WHERE cant IN (select min(cant) from rand_comanda group by codp);

Sa se afiseze comenzile a caror valoare este mai mica decat cea mai mare valoare a comenzilor incheiate cu clientul pentru care codc este 1.

SELECT nrcom, val FROM comenzi

WHERE val<any(select val from comenzi where codc=1);

>any mai mare decat minim din lista

<any mai putin decat maxim din lista

=any in

>all mai mare decat maxim din lista

<all mai mic decat minim din lista

Sa se afiseze comenzile a caror valoare este mai mare decat cea mai mare valoare medie a comenzilor grupate dupa client.

SELECT nrcom, val FROM comenzi

WHERE val>all(select avg(val) from comenzi group by codc);

TEMA

1. Sa se afiseze urmatoarele: denumirea clientilor, valoarea comenzilor incheiate si denumirea bancii la care au cont. Ordonarea se va face dupa denumirea bancii. Gruparea clientilor se va face dupa denumirea bancii .

2. Sa se afiseze clientii care nu au comenzi incheiate. Ordonare dupa denumirea clientului.

3. Sa se afiseze produsele necontractate.Ordonare dupa denumirea produsului.

4. Sa se afiseze clientii care au termenul de livrare octombrie 2000. Ordonare dupa denumirea clientului.

5. Sa se afiseze clientii cu comenzi nelivrate .Ordonare dupa denumirea clientului.

6. Sa se afiseze produsul(produsele) cu cel mai mic stoc.

7. Sa se afiseze clientii cu o valoare totala a comenzilor mai mica decat valoarea totala a comenzilor incheiate de firma Python.Ordonare dupa denumirea clientului.

8. Sa se afiseze produsele cu un pret unitar mai mare decat pretul unitar al produsului cu codul MP1

9. Sa se afiseze urmatoarele: numar comanda, valoarea comandata ( pret* cantitate) pentru fiecare produs ce apare in comanda. Grupare dupa numar comanda. Ordonare dupa numar comanda.

10. Sa se afiseze denumirea clientului, valoarea totala a comenzilor incheiate, valoarea totala a comenzilor restante. Ordonare dupa denumirea clientului.