Exercitiu – SQL interogari complexe

Se considera o baza de date despre informatii despre filialele unei banci, privind clientii,
conturile, creditele, ca in fig.1:

Cc Filiala Cont Depune

NumeFiliala NrCont CodClient
Oras NumeFiliala NrCont
Capital Balanta

Credit Imprumuta Client

NrCredit CodClient CNP

NumeFiliala NrCredit CodClient
Suma Strada
Oras
Telefon

Fig. 1. Baza de date Banca

I.Crearea bazei de date

--creare tabela Filiala
create table Filiala (
numefiliala varchar(20),
oras varchar(50),
capital real
);
--adaugam constrangerea de cheie primara pentru atributul numefiliala
alter table Filiala add constraint pkey_filiala primary key(numefiliala)

--creare tabela Cont

create table Cont(
nrcont int,
numefiliala varchar(20),
balanta real,
primary key(nrcont),
foreign key(numefiliala) references Filiala(numefiliala) on delete cascade on update
cascade
)

--creare tabela Client

create table Client(
cnp varchar(13),
codclient int,
strada varchar(25),
oras varchar(50),
telefon varchar(13),
primary key(cnp)
)
--exemple cu variante ale comenzii Alter
alter table drop cnp;
alter table Client add constraint pkey_client primary key(codclient);
alter table Client add column cnp varchar(13);

--creare tabela Depune

create table Depune(
codclient int,
nrcont int,
foreign key(codclient) references Client(codclient) on delete cascade on update cascade,
foreign key(nrcont) references Cont(nrcont) on delete cascade on update cascade
)

--creare tabela Credit

create table Credit(
nrcredit int,
numefiliala varchar(20),
suma real,
primary key(nrcredit),
foreign key(numefiliala) references Filiala(numefiliala) on delete cascade on update
cascade
)

--creare tabela Imprumuta

create table Imprumuta(
codclient int,
nrcredit int,
foreign key(codclient) references Client(codclient) on delete cascade on update cascade,
foreign key(nrcredit) references Credit(nrcredit) on delete cascade on update cascade
)

--inserari in tabelele create

insert into Filiala values (

'ING1',
'Craiova',
100000
);
insert into Filiala values (
'ING2',
'Craiova',
150000
);
insert into Filiala values (
'ING3',
'Bucuresti',
200000
);
insert into Filiala values (
'ING4',
'Bucuresti',
250000
);
insert into Filiala values (
'ING5',
'Craiova',
200000
);

insert into Cont values(

1,
'ING1',
2000
)
insert into Cont values(
2,
'ING1',
1000
)
insert into Cont values(
3,
'ING1',
2500
)

insert into Cont values(

4,
'ING2',
2000
)
insert into Cont values(
5,
'ING3',
2000
)
insert into Cont values(
6,
'ING4',
2000
)

insert into Credit values(

1,
'ING1',
1000
)
insert into Credit values(
2,
'ING1',
500
)
insert into Credit values(
3,
'ING1',
2500
)

insert into Credit values(

4,
'ING2',
1500
)
insert into Credit values(
5,
'ING3',
1200
);
insert into Credit values(
6,
'ING4',
2000
);

delete From client

insert into Client values(

11,
'Zorilor, nr 1',
'Craiova',
'0741888999',
' 111'
)

insert into Client values(

22,
'Zorilor, nr 4',
'Craiova',
'0741888999',
' 1222'
)
select * from Client
insert into Client values(
33,
'Castanilor, nr 1',
'Bucuresti',
'0741889234',
' 111'
)
insert into Client values(
44,
'Castanilor, nr 1',
'Bucuresti',
'0741889234',
' 1333'
)
insert into Depune values(
11,
1
)
insert into Depune values(
11,
2
)

insert into Depune values(

22,
3
 )
insert into Depune values(
33,
4
)

insert into Depune values(

33,
5
)
insert into Depune values(
44,
6
)

update Credit set NrCredit = NrCredit+10

insert into Imprumuta values(

11,
11
)

insert into Imprumuta values(

22,
12
)

insert into Imprumuta values(

22,
13
)

insert into Imprumuta values(

11,
14
)

II. Sa se formuleze urmatoarele interogari in SQL

1.Sa se afiseze denumirea toturor filialelor bancii.

select numefiliala from Filiala
2. Sa se afiseze toate conturile bancii.
select * from Cont
3.Sa se afiseze filialele care au dat imprumuturi.
select * from Filiala, Credit where Filiala.numefiliala=Credit.numefiliala
--echivalenta cu
select * from Filiala INNER JOIN Credit on Filiala.numefiliala=Credit.numefiliala
4. Sa se afiseze cnp –urile clientilor de la sucursala „ING1”.
select Distinct Client.codclient,Client.cnp from Client, Imprumuta, Depune,Credit,Cont
where (((Credit.numefiliala='ING1') and (Imprumuta.codclient = Client.codclient) and
(Imprumuta.nrcredit=Credit.nrcredit)) or ((Cont.numefiliala = 'ING1') and
(Client.codclient=Depune.codclient) and (Cont.nrcont=Depune.nrcont)))
5. Sa se afiseze conturile care au balanta intre 1500 si 10000 euro.
select nrcont,balanta from cont where (balanta between 1500 and 10000)
6.Sa se afiseze creditele de la filiala „ING1” cu valoare mai mare de 2000 euro.
select nrcredit from Credit where numefiliala = 'ING1' and suma>2000;

7. Sa se afiseze codul clientilor, valoarea creditului de la filiala „ING2”.

select codclient,suma from Credit, Imprumuta where numefiliala = 'ING2' and
Credit.nrcredit=Imprumuta.nrcredit;

8. Sa se afiseze denumirile tuturor filialelor care au capital mai mare decat cel putin una din
filialele cu locatia in Craiova.

select numefiliala from Filiala where capital > ANY(select capital from Filiala where Oras =
'Craiova');
--echivalenta cu
select numefiliala from Filiala where capital > SOME(select capital from Filiala where Oras =
'Craiova');
--echivalenta cu
select Distinct F1.numefiliala from Filiala F1, Filiala F2 where (F2.Oras= 'Craiova' and F1.capital
>F2.capital);

9. Sa se afiseze clientii a caror adresa contine ‘Cra’.

select * from Client where Oras like 'Cra%';
10. Sa se afiseze denumirile filialelor care au cel putin 3 caractere.
select numefiliala from Filiala where numefiliala like '___%';

11. Sa se afiseze in ordine alfabetica toti clientii care au credite la filiala ING1.

select Client.codclient, Client.cnp from Client,Credit, Imprumuta

where ((Client.codclient=Imprumuta.codclient) and (Credit.nrcredit=Imprumuta.nrcredit )and
(Credit.numefiliala = 'ING1') )
order by Client.codclient DESC

12. Sa se afiseze toti clientii care au fie credite, fie conturi, sau ambele la banca.

Operatia UNION elimina automat duplicatele, spre deosebire de clauza SELECT. In

interogarea precendenta, daca Popescu are mai multe conturi sau credite la banca, va aparea
o singura data in rezultatul interogarii. Daca dorim sa afisam si duplicatele, trebuie sa
folosim UNION ALL, in loc de UNION.
Numarul de tupluri duplicate din rezultate este egal cu numarul total de tupluri duplicate
care apar atat in Depune si Imprumuta.
(select Client.codclient from Client,Credit, Imprumuta
where ((Client.codclient=Imprumuta.codclient) and (Credit.nrcredit=Imprumuta.nrcredit )))
union
(select Client.codclient from Client,Cont, Depune
where ((Client.codclient=Depune.codclient) and (Cont.nrcont=Depune.nrcont )))

--echivalenta cu

select distinct Client.codclient from Client,Credit, Imprumuta, Cont, Depune

where( ((Client.codclient=Imprumuta.codclient) and (Credit.nrcredit=Imprumuta.nrcredit ) )
or ((Client.codclient=Depune.codclient) and (Cont.nrcont=Depune.nrcont )))

--fara a elimina duplicatele

(select Client.codclient from Client,Credit, Imprumuta

where ((Client.codclient=Imprumuta.codclient) and (Credit.nrcredit=Imprumuta.nrcredit )))
union all
(select Client.codclient from Client,Cont, Depune
where ((Client.codclient=Depune.codclient) and (Cont.nrcont=Depune.nrcont )))

13. Sa se afiseze toti clientii care au si credit si cont la banca.

Ca si in cazul operatiei UNION, operatia INTERSECT elimina duplicatelor. Daca se doreste

ca duplicatele sa nu fie eliminate, se foloseste INTERSECT ALL.

(select Client.codclient, Client.cnp from Client,Credit, Imprumuta

where ((Client.codclient=Imprumuta.codclient) and (Credit.nrcredit=Imprumuta.nrcredit )))
intersect
(select Client.codclient, Client.cnp from Client,Cont, Depune
where ((Client.codclient=Depune.codclient) and (Cont.nrcont=Depune.nrcont )))

--echivalenta cu

select distinct Client.codclient from Client,Credit, Imprumuta, Cont, Depune

where ((Client.codclient=Imprumuta.codclient) and (Credit.nrcredit=Imprumuta.nrcredit )
and (Client.codclient=Depune.codclient) and (Cont.nrcont=Depune.nrcont ))

--daca nu se doreste eliminarea duplicatelor se foloseste intersect all

14. Sa se afiseze toti clientii care au conturi dar nu au imprumuturi la banca.

Operatia EXCEPT elimina duplicatele. In interogarea precendenta, daca Popescu are mai
multe conturi la banca, va aparea o singura data in rezultatul interogarii.

(select Client.codclient, Client.cnp from Client, Depune

where ((Client.codclient=Depune.codclient) ))
except
(select Client.codclient, Client.cnp from Client,Imprumuta
where Client.codclient=Imprumuta.codclient)
15. Sa se afiseze valoarea medie a conturilor de la filiala ING1.

select avg(balanta) from Cont

where numefiliala ='ING1'

16. Sa se afiseze valoarea medie a conturilor de la fiecare filiala.

select numefiliala, avg(balanta) from Cont

group by numefiliala

17. Sa se afiseze numarul de clienti cu conturi ai fiecarei filiale.

select numefiliala, count(distinct codclient)

from Depune, Cont
where Depune.nrcont=cont.nrcont
group by numefiliala

18. Pentru fiecare filiala sa se afiseze valorile medii peste 1200 de euro a balantelor conturilor.

select numefiliala, avg(balanta) from Cont

group by numefiliala
having avg(balanta) >1200
order by avg(balanta)

19. Sa se afiseze media balantelor conturilor pentru fiecare client care locuieste in Craiova si
are mai mult de 2 conturi.

select Client.codclient, avg(balanta)

from Cont,Client,Depune
where ((Client.Oras = 'Craiova' )and (Client.codclient=Depune.codclient) and (Depune.nrcont =
Cont.nrcont))
group by Client.codclient
having (count(Depune.nrcont)>=2)

20. Sa se afiseze codul clientilor care au credite la banca si ale caror coduri client nu sunt 33 si
44.

21. Sa se afiseze denumirile filialelor care au capital mai mare decat toate filialele cu locatia in
Craiova.

select numefiliala from Filiala where capital > ALL(select capital from Filiala where Oras = 'Craiova');

22. Sa se afiseze filiala care are cea mai mare valoare medie a balantelor conturilor.

select numefiliala , avg(balanta)

from Cont
group by numefiliala
having avg(balanta)>= all (select avg(balanta)
from Cont
group by numefiliala)

23. Sa se afiseze clientii care au atat conturi cat si imprumuturi la banca.

select Depune.codclient from Depune

where exists (select Imprumuta.codclient from Imprumuta
where (Depune.codclient =Imprumuta.codclient))

24. Sa se afiseze clientii care au cel mult un cont.

select D1.codclient from Depune D1

where unique(select D2.codclient from Depune D2 where (D2.codclient=D1.codclient))

--echivalenta cu

select codclient, Count(nrcont) from Depune

group by codclient
having Count(nrcont)<=1

25. Sa se afiseze clientii care au cel putin doua conturi.

select D1.codclient from Depune D1

where not unique(select D2.codclient from Depune D2 where (D2.codclient=D1.codclient))

--echivalenta cu

select codclient, Count(nrcont) from Depune

group by codclient
having Count(nrcont)>1