copiere info: oracle

Pb1 Oracle

a) Crearea tabelelor pacienti si consultatii

CREATE TABLE pacienti

(

cnp_p NUMBER(13) PRIMARY KEY,

nume VARCHAR2(30) NOT NULL,

data_nasterii DATE NOT NULL,

sex CHAR(1) NOT NULL

)

desc pacienti

INSERT INTO pacienti VALUES (2921904555777, 'Grig Adela', '19-Mar-1990', 'F')

INSERT INTO pacienti VALUES (2890207555777, 'Alexa Mara', '22-Jun-1989', 'F')

INSERT INTO pacienti VALUES (2910410555777, 'Darie Ada', '02-Feb-1991', 'F')

INSERT INTO pacienti VALUES (1892411555777, 'Vlad Cristi', '27-Nov-1998', 'B')

INSERT INTO pacienti VALUES (2902512555777, 'Pop Ana', '12-May-1990', 'F')

INSERT INTO pacienti VALUES (1911708555777, 'Georgescu Paul', '11-Jul-1992', 'B')

SELECT cnp_p, nume, to_char(data_nasterii,'DD-MM-YYYY') AS "DATA_NASTERII", sex FROM pacienti

CREATE TABLE consultatii

(

id NUMBER(2) PRIMARY KEY,

cnp_pacient NUMBER(13) REFERENCES pacienti(cnp_p),

data_consult DATE NOT NULL,

medic VARCHAR2(30) NOT NULL,

specialitate VARCHAR2(30) NOT NULL,

pret NUMBER(4) NOT NULL,

diagnostic VARCHAR2(25)

)

INSERT INTO consultatii

VALUES(11,2902512555777, '04 April 2009', 'Filip Dan', 'generalist', 130, 'diabet')

INSERT INTO consultatii

VALUES(12,1892411555777, '12 MAR 2010', 'Zota Ana', 'dentar', 110, NULL)

INSERT INTO consultatii

VALUES(13,1911708555777, '01 dec 2012', 'Popescu Ion', 'generalist', 75, 'obezitate')

INSERT INTO consultatii

VALUES(14,1911708555777, '05 oct 2010', 'Zota Ana', 'dentar', 130, NULL)

INSERT INTO consultatii

VALUES(15,2921904555777, '15 feb 2010', 'Zota Ana', 'dentar', 120, NULL)

INSERT INTO consultatii

VALUES(16,2890207555777, '01 dec 2012', 'Zota Ana', 'dentar', 90, NULL)

SELECT id, cnp_pacient,to_char(data_consult,'DD-MM-YYYY') AS "DATA_CONSULT",specialitate,pret,diagnostic

FROM consultatii

SELECT medic,specialitate

FROM consultatii

WHERE data_consult='01 dec 2012'

ORDER BY medic

SELECT SUM(pret)

FROM pacienti, consultatii

WHERE UPPER(nume)='GEORGESCU PAUL' AND cnp_p=cnp_pacient

//Problema 2 Oracle

CREATE TABLE destinatii

(

id NUMBER(3) PRIMARY KEY,

numeD VARCHAR2(40) NOT NULL,

pret NUMBER(3) NOT NULL

)

INSERT INTO destinatii VALUES (1, 'Viena', 110)

INSERT INTO destinatii VALUES (2, 'Paris', 230)

INSERT INTO destinatii VALUES (3, 'Budapesta', 50)

INSERT INTO destinatii VALUES (4, 'New York', 600)

INSERT INTO destinatii VALUES (5, 'Londra', 400)

SELECT id AS "Id", numeD, pret AS "PRE?" FROM destinatii

CREATE TABLE clienti

(

idd NUMBER(3) NOT NULL REFERENCES destinatii(id),

idc NUMBER(3) PRIMARY KEY,

nume VARCHAR2(35) NOT NULL,

prenume VARCHAR2(40) NOT NULL,

adresa VARCHAR2(50) NOT NULL,

telefon VARCHAR2(10)

)

INSERT INTO clienti VALUES (1,11,'POP','INA','IA?I','0744123567')

INSERT INTO clienti VALUES (2,22,'DASCAL','GINA','DEVA','0744123566')

INSERT INTO clienti VALUES (2,33,'MIRON','DANA','CLUJ','0744123565')

INSERT INTO clienti VALUES (3,44,'MIRON','DAN','CLUJ','0744123564')

INSERT INTO clienti VALUES (4,55,'CARP','VLAD','BACAU','0744123563')

INSERT INTO clienti VALUES (5,66,'POPA','ANA','BACAU','0744123562')

INSERT INTO clienti VALUES (1,77,'POP','ION','IA?I','0744123561')

INSERT INTO clienti VALUES (1,88,'AVRAM','ANCA','CLUJ','0744123560')

SELECT * FROM clienti

ORDER BY idc

SELECT nume, prenume

FROM destinatii, clienti

WHERE UPPER(numeD)='VIENA' AND idd=id

ORDER BY nume,prenume

SELECT NUMED FROM destinatii

WHERE pret=(SELECT MAX(pret) FROM destinatii)

UNION

SELECT numeD FROM destinatii

WHERE pret=(SELECT MIN(pret) FROM destinatii);

//pb 3 Oracle

a) Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE abonati

(

cnp NUMBER(13) PRIMARY KEY,

nume VARCHAR2(40) NOT NULL,

prenume VARCHAR2(40) NOT NULL

)

INSERT INTO abonati VALUES (2921904555777,'Grig', 'Adela')

INSERT INTO abonati VALUES (2890207555777,'Alexa', 'Mara')

INSERT INTO abonati VALUES (2910410555777,'Darie', 'Ada')

INSERT INTO abonati VALUES (1892411555777,'Vlad', 'Cisti')

INSERT INTO abonati VALUES (2902512555777,'Pop', 'Ana')

SELECT * FROM abonati

CREATE TABLE interventii

(

idi VARCHAR2(5) PRIMARY KEY,

cnp NUMBER(13) REFERENCES abonati(cnp) NOT NULL,

tip_interventie VARCHAR2(30) NOT NULL,

data_inregistrarii DATE NOT NULL,

termen_limita NUMBER(5) NOT NULL,

stare VARCHAR2(20) NOT NULL

)

INSERT INTO interventii VALUES('1.', 2921904555777, 'deranjament', '04-APR-2013', 2, 'neefectuat')

INSERT INTO interventii VALUES('2.', 2890207555777, 'Instalare', '12-MAR-2012', 3, 'efectuat')

INSERT INTO interventii VALUES('3.', 2910410555777, 'deranjament', '01-DEC-2012', 2, 'efectuat')

INSERT INTO interventii VALUES('4.', 1892411555777, 'Mutare', '05-NOV-2012', 2, 'efectuat')

INSERT INTO interventii VALUES('5.', 2902512555777, 'Instalare', '15-FEB-2013', 3, 'efectuat')

INSERT INTO interventii VALUES('6.', 2890207555777, 'deranjament', '01-JAN-2013', 4, 'efectuat')

INSERT INTO interventii VALUES('7.', 2921904555777, 'deranjament', '01-JAN-2013', 4, 'efectuat')

INSERT INTO interventii VALUES('8.', 2902512555777, 'deranjament', '21-DEC-2012', 2, 'neefectuat')

SELECT idi,cnp, tip_interventie AS "TIP", TO_CHAR(data_inregistrarii,'DD-MM-YYYY') AS "DATA INREGISTRARII", termen_limita AS "TERMEN LIMITA", stare

FROM interventii

ORDER BY idi

b)Sa se afiseze alfabetic abonatii care au solicitat interventii оn data de 1 ianuarie 2013.

SELECT nume, prenume

FROM abonati a, interventii b

WHERE data_inregistrarii='01-Jan-2013' AND a.cnp=b.cnp

ORDER BY nume, prenume

c)Afisati data оnregistrarii interventiilor care au termenul limita depasit?

SELECT TO_CHAR(data_inregistrarii, 'DD-MM-YYYY') AS "DATA INREGISTRARII"

FROM interventii

WHERE stare='neefectuat'

//Pb 4 Oracle

a) Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE filme

(

idf NUMBER(5) PRIMARY KEY,

nume VARCHAR2(40) NOT NULL,

data_lansarii DATE NOT NULL

)

INSERT INTO filme VALUES (1,'KING-KONG','04-APR-2008')

INSERT INTO filme VALUES (2,'PE ARIPILE VВNTULUI','12-MAR-1950')

INSERT INTO filme VALUES (3,'FRATII JDERI','01-DEC-1965')

INSERT INTO filme VALUES (4,'MARGELATU','05-NOV-1983')

INSERT INTO filme VALUES (5,'VERONICA','15-FEB-1977')

SELECT idf AS "ID", nume, TO_CHAR(data_lansarii, 'DD-MM-YYYY') AS "DATA LANSARII"

FROM filme

CREATE TABLE actori

(

idf NUMBER(5) REFERENCES filme(idf),

ida NUMBER(5) PRIMARY KEY,

nume_artist VARCHAR2(40) NOT NULL,

nationalitate VARCHAR2(30) NOT NULL

)

INSERT INTO actori VALUES (1,11,'Fay Wray','Americana')

INSERT INTO actori VALUES (2,22, 'Vivien Leigh','Americana')

INSERT INTO actori VALUES (3,33, 'Florin Piersic','Romвna')

INSERT INTO actori VALUES (1,44, 'Bill Williams','Americana')

INSERT INTO actori VALUES (4,88, 'Florin Piersic','Romвna')

INSERT INTO actori VALUES (5,55, 'Margareta Pislaru','Romвna')

INSERT INTO actori VALUES (4,66, 'Mircea Diaconu','Romвna')

INSERT INTO actori VALUES (2,77, 'Clark Gable','Americana')

SELECT * FROM actori

ORDER BY ida

b) Sa se afiseze cel mai vechi film si actorii care au jucat оn acesta.

SELECT nume, nume_artist

FROM filme a, actori b

WHERE data_lansarii=(SELECT MIN(data_lansarii) FROM FILME) AND a.idf=b.idf

c) Pentru actorul “Florin Piersic” sa se afiseze toate informatiile detinute precum si toate filmele оn care joaca.

SELECT nume_artist, nationalitate, nume, TO_CHAR(data_lansarii, 'DD-MM-YYYY') AS "DATA LANSARII"

FROM filme a, actori b

WHERE UPPER(nume_artist)='FLORIN PIERSIC' AND a.idf=b.idf

//Pb 5 Oracle 2013

a)Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE stapani

(

cnp NUMBER(13) PRIMARY KEY,

nume VARCHAR2(40) NOT NULL,

adresa VARCHAR2(50) NOT NULL

)

DESC stapani

INSERT INTO stapani VALUES (2921904555777,'Grig Adelina','Cluj')

INSERT INTO stapani VALUES (2890207555777,'Alexa Mara','Brasov')

INSERT INTO stapani VALUES (2910410555777,'Darie Ada','Bacau')

INSERT INTO stapani VALUES (1892411555777,'Vladoi Cristinel','Bucuresti')

INSERT INTO stapani VALUES (2902512555777,'Pop Ana','Iasi')

INSERT INTO stapani VALUES (1911708555777,'Antonescu Paul','Bucuresti')

CREATE TABLE caini

(

id NUMBER(5) PRIMARY KEY,

cnp NUMBER(13) REFERENCES stapani(cnp),

nume_caine VARCHAR2(40) NOT NULL,

premiu VARCHAR2(30) NOT NULL

)

DESC caini

INSERT INTO caini VALUES (1,2921904555777,'Fay',1)

INSERT INTO caini VALUES (2,2890207555777, 'Dino', 2)

INSERT INTO caini VALUES (3,2910410555777, 'Figaro',1)

INSERT INTO caini VALUES (4,1892411555777, 'Lord',3)

INSERT INTO caini VALUES (5,2902512555777, 'Mozart',3)

INSERT INTO caini VALUES (6,1911708555777, 'Niko',2)

INSERT INTO caini VALUES (7,2921904555777, 'Oscar',0)

INSERT INTO caini VALUES (8,1892411555777, 'Spot',2)

SELECT * FROM caini

ORDER BY id

b)Pentru stapвnul “Cristinel” sa se afiseze toate informatiile detinute precum si toti cвinii cu care a participat la campionat si premiile obtinute de acestia.

SELECT a.cnp, nume, adresa, nume_caine, premiu

FROM stapani a, caini b

WHERE UPPER(nume) LIKE '%CRISTINEL%' AND a.cnp=b.cnp

c)Sa se afiseze lista cвinilor alfabetic dupa premii, оmpreuna cu numele stapвnilor.

SELECT nume_caine, premiu, nume

FROM stapani a, caini b

WHERE a.cnp=b.cnp

ORDER BY premiu, nume_caine

//Pb 6 Oracle 2013

a)Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE specii

(

ids NUMBER(4) PRIMARY KEY,

denumire VARCHAR2(40) NOT NULL,

caracteristici VARCHAR2(200)

)

DESCRIBE specii

INSERT INTO specii VALUES(1, 'ovine', 'corp acoperit de blana')

INSERT INTO specii VALUES(2, 'marsupiale', 'prezenta unei pungi, numita marsupiu')

INSERT INTO specii VALUES(3, 'cabaline', 'erbivor, marime considerabila')

SELECT * FROM specii

CREATE TABLE animale

(

ida NUMBER(4) PRIMARY KEY,

ids NUMBER(4) REFERENCES specii(ids) NOT NULL,

nume VARCHAR2(40) NOT NULL,

data_nasterii DATE NOT NULL

)

DESCRIBE animale

INSERT INTO animale VALUES(11, 1, 'Oaie', '04-APR-2008')

INSERT INTO animale VALUES(22, 2, 'Cangur', '12-FEB-2007')

INSERT INTO animale VALUES(33, 2, 'urs koala', '01-DEC-2007')

INSERT INTO animale VALUES(44, 3, 'Cal', '05-NOV-2008')

INSERT INTO animale VALUES(55, 1, 'Capra', '15-FEB-2009')

INSERT INTO animale VALUES(66, 3, 'Iapa', '03-MAY-2008')

INSERT INTO animale VALUES(77, 2, 'lupul marsupial', '12-MAR-2007')

SELECT ida, ids, nume, TO_CHAR(data_nasterii,'DD-MM-YYYY') AS "DATA_NA?TERII"

FROM animale

ORDER BY ida

b)Care este cel mai bвtrвn animal din gradina zoologica ?i din ce specie face parte?

SELECT nume, TO_CHAR(data_nasterii,'DD-MM-YYYY') AS "DATA_NA?TERII", denumire

FROM animale a, specii b

WHERE data_nasterii=(SELECT MIN(data_nasterii) FROM animale) AND a.ids=b.ids

c)Sa se afiseze toate animalele alfabetic pe specii.

SELECT denumire, nume

FROM animale a, specii b

WHERE a.ids=b.ids

ORDER BY denumire, nume

//Pb 8 Oracle 2013

a)Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE clienti

(

idc NUMBER(3) PRIMARY KEY,

nume VARCHAR2(50) NOT NULL,

prenume VARCHAR2(50) NOT NULL,

adresa VARCHAR2(150) NOT NULL

);

DESC clienti

INSERT INTO clienti VALUES(1, 'POP','INA','IA?I')

INSERT INTO clienti VALUES(2, 'DASCAL','GINA','DEVA')

INSERT INTO clienti VALUES(3, 'MIRON','DANA','CLUJ')

INSERT INTO clienti VALUES(4, 'MIRON','DAN','CLUJ')

INSERT INTO clienti VALUES(5, 'CARP','VLAD','BACAU')

INSERT INTO clienti VALUES(6, 'POPA','ANA','BACAU')

SELECT * FROM clienti

ORDER BY idc

CREATE TABLE reclame

(

idc NUMBER(3) REFERENCES clienti(idc),

idr NUMBER(3) PRIMARY KEY,

denumire VARCHAR2(40) NOT NULL,

durata NUMBER(3) NOT NULL,

nr_difuzari NUMBER(3) NOT NULL,

tarif NUMBER(4) NOT NULL

);

desc reclame

INSERT INTO reclame VALUES(1,11,'COCA-COLA', 2,4,200)

INSERT INTO reclame VALUES(2,22,'LENOR', 1,2,150)

INSERT INTO reclame VALUES(2,33,'ARIEL', 1,2,140)

INSERT INTO reclame VALUES(4,44,'ORANGE', 2,3,220)

INSERT INTO reclame VALUES(5,55,'URSUS', 1,1,123)

INSERT INTO reclame VALUES(3,66,'COLGATE', 1,1,125)

INSERT INTO reclame VALUES(6,77,'POIANA', 2,1,100)

INSERT INTO reclame VALUES(5,88,'CIUC', 1,1,130)

SELECT * FROM reclame

ORDER BY idr

b)Sa se afiseze pentru fiecare client reclamele comandate. Clientii vor fi afisati alfabetic.

SELECT a.idc, nume, prenume, adresa, denumire

FROM clienti a, reclame b

WHERE a.idc=b.idc

c)Afisati numele si prenumele clientului care a comandat reclama cu un numar maxim de difuzari.

ORDER BY nume, prenume

SELECT nume, prenume, adresaa) Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

FROM clienti a, reclame b

WHERE nr_difuzari=(SELECT MAX(nr_difuzari) FROM reclame) AND a.idc=b.idc

//Problema 9 Oracle

PROBLEMA 9

a)Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE masini

(

idm NUMBER(3) PRIMARY KEY,

marca VARCHAR2(50) NOT NULL,

nr_locuri NUMBER(3) NOT NULL,

nr_inmatriculare VARCHAR2(50) NOT NULL

);

DESC masini

INSERT INTO masini VALUES(1, 'RENAULT',18,'SV-09-ABC')

INSERT INTO masini VALUES(2, 'MERCEDES',12,'SV-02-FGH')

INSERT INTO masini VALUES(3, 'VW',8,'SV-03-IJK')

INSERT INTO masini VALUES(4, 'MERCEDES',18,'B-100-ION')

INSERT INTO masini VALUES(5, 'FORD',20,'B-102-LIC')

SELECT * FROM masini

ORDER BY idm

CREATE TABLE curse

(

idc NUMBER(3) PRIMARY KEY,

idm NUMBER(3) REFERENCES masini(idm),

destinatie VARCHAR2(50) NOT NULL,

data_plecarii DATE NOT NULL,

ora_plecarii NUMBER(3) NOT NULL,

data_intoarcerii DATE NOT NULL

);

desc curse

INSERT INTO curse VALUES(11,1,'BUCURE?TI','03-MAR-2013', 10,'04-MAR-2013')

INSERT INTO curse VALUES(22,2,'IA?I','03-FEB-2013', 10,'03-FEB-2013')

INSERT INTO curse VALUES(33,3,'CLUJ','01-APR-2013', 11,'03-APR-2013')

INSERT INTO curse VALUES(44,1,'IA?I','01-MAY-2013', 15,'01-MAY-2013')

INSERT INTO curse VALUES(55,4,'BUCURE?TI','01-MAY-2013', 6,'02-MAY-2013')

INSERT INTO curse VALUES(66,5,'TIMI?OARA','15-APR-2013', 8,'17-APR-2013')

INSERT INTO curse VALUES(77,1,'CLUJ','10-APR-2013', 10,'11-APR-2013')

INSERT INTO curse VALUES(88,5,'CLUJ','20-MAR-2013', 22,'22-MAR-2013')

SELECT idc,idm,destinatie, TO_CHAR(data_plecarii, 'DD.MM.YYYY') AS "DATA PLECARII", ora_plecarii AS "ORA PLECARII", TO_CHAR(data_intoarcerii,'DD.MM.YYYY') AS "DATA INTOARCERII" FROM curse

ORDER BY idc

b)Sa se afiseze numerele de оnmatriculare ale masinilor care efectueaza curse ce dureaza mai mult de o zi.

SELECT DISTINCT nr_inmatriculare

FROM masini a, curse b

WHERE a.idm=b.idm AND data_intoarcerii-data_plecarii>1

c)Cвte persoane ar putea fi transportate cu masinile care pleaca оn cursa pe 1 mai 2013?

SELECT SUM(nr_locuri) AS "NUMAR PERSOANE"

FROM masini a, curse b

WHERE a.idm=b.idm AND data_plecarii='01-MAY-2013'

//Pb 10 Oracle

a)Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE edituri

(

cod NUMBER(3) PRIMARY KEY,

nume VARCHAR2(30) NOT NULL,

adresa VARCHAR2(200) NOT NULL,

telefon VARCHAR2(10)

)

INSERT INTO edituri VALUES (1, 'NEMIRA', '1 Mai, 40, Bucuresti','0744123567')

INSERT INTO edituri VALUES (2, 'NICULESCU', 'Calea Unirii 23, Bucuresti','0744123566')

INSERT INTO edituri VALUES (3, 'ALL', 'Calea Unirii 67, Bucuresti','0744123565')

INSERT INTO edituri VALUES (4, 'ERC PRESS', 'Florilor 1, Bucuresti','0744123565')

INSERT INTO edituri VALUES (5, 'PETRION', 'Pandurilor 67, Bucuresti','0744123563')

SELECT *

FROM edituri ORDER by cod

CREATE TABLE carti

(

cod NUMBER(3) REFERENCES edituri(cod),

idc NUMBER(3) PRIMARY KEY,

titlu VARCHAR2(50) NOT NULL,

autor VARCHAR2(50) NOT NULL,

pret NUMBER(4) NOT NULL

)

INSERT INTO carti VALUES (1,11, 'ION', 'ION REBREANU',29)

INSERT INTO carti VALUES (2,22, 'MARA', 'IOAN SLAVICI',31)

INSERT INTO carti VALUES (3,33, 'POVE?TI', 'ION CREANGA',19)

INSERT INTO carti VALUES (4,44, 'POEZII', 'MIHAI EMINESCU',15)

INSERT INTO carti VALUES (5,55, 'AMINTIRI DIN COPILARIE', 'ION CREANGA',22)

INSERT INTO carti VALUES (2,66, 'POEZII', 'GEORGE CO?BUC',29)

INSERT INTO carti VALUES (1,77, 'LORELEI', 'IONEL TEODOREANU',20)

INSERT INTO carti VALUES (5,88, 'DUMBRAVA MINUNATA', 'MIHAIL SADOVEANU',18)

SELECT *

FROM carti ORDER by idc

b)Afisati cartile aparute la editura Nemira ordonate alfabetic.

SELECT titlu, autor, pret

FROM edituri a, carti b

WHERE a.cod=b.cod and UPPER(nume)='NEMIRA'

ORDER BY titlu,autor

c)Care este valoarea cartilor scrise de Ion Creanga?

SELECT SUM(pret) AS "VALOARE"

FROM carti

WHERE UPPER(autor)='ION CREANGA'

//Pb 11 Oracle 2013

a)Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE departamente

(

cod NUMBER(3) PRIMARY KEY,

denumire VARCHAR2(30) NOT NULL

)

INSERT INTO departamente VALUES (1,'CONTABILITATE')

INSERT INTO departamente VALUES (2,'APROVIZIONARE')

INSERT INTO departamente VALUES (3,'DESFACERE')

INSERT INTO departamente VALUES (4,'TRANSPORTURI')

INSERT INTO departamente VALUES (5,'PRODUC?IE')

SELECT *

FROM departamente ORDER by cod

CREATE TABLE angajati

(

cod NUMBER(3) REFERENCES edituri(cod),

idc NUMBER(3) PRIMARY KEY,

titlu VARCHAR2(50) NOT NULL,

autor VARCHAR2(50) NOT NULL,

pret NUMBER(4) NOT NULL

)

CREATE TABLE angajati

(

cnp NUMBER(13) PRIMARY KEY,

nume VARCHAR2(50) NOT NULL,

functia VARCHAR2(20) NOT NULL,

salar NUMBER(4) NOT NULL,

cod_depart NUMBER(3) REFERENCES departamente(cod)

);

INSERT INTO angajati VALUES (2921904555777,'Grig Adelina','COORDONATOR DEPARTAMENT',1800,5)

INSERT INTO angajati VALUES (2890207555777,'Alexa Mara','ECONOMIST',1500,2)

INSERT INTO angajati VALUES (2910410555777,'Darie Ada','COORDONATOR DEPARTAMENT',1800,3)

INSERT INTO angajati VALUES (1892411555777,'Vladoi Cristinel','INGINER',1602,5)

INSERT INTO angajati VALUES (2902512555777,'Pop Ana','INGINER',1710,4)

INSERT INTO angajati VALUES (1911708555777,'Antonescu Paul','ECONOMIST',1403,1)

INSERT INTO angajati VALUES (2901907555777,'Ionescu Ana','COORDONATOR DEPARTAMENT',1800,1)

INSERT INTO angajati VALUES (2890909555777,'Alex Dana','INGINER',1653,5)

INSERT INTO angajati VALUES (1910210555777,'Darie Mihai','COORDONATOR DEPARTAMENT',1800,4)

INSERT INTO angajati VALUES (2882411555777,'Vlad Cristina','INGINER',1689,5)

INSERT INTO angajati VALUES (2922512555777,'Radu Anda','ECONOMIST',1500,3)

INSERT INTO angajati VALUES (1911709555777,'Anton Mihai','COORDONATOR DEPARTAMENT',1800,2)

SELECT cnp, nume AS "NUME ?I PRENUME", functia AS "FUNC?IA", salar, cod_depart

FROM angajati

b)Sa se afiseze lista departamentelor cu sefii acestora(se va afisa denumirea departamentului si numele coordonatorului).

SELECT denumire AS "DEPARTAMENT", nume AS "COORDONATOR DEPARTAMENT"

FROM departamente a, angajati b

WHERE a.cod=b.cod_depart and UPPER(functia)='COORDONATOR DEPARTAMENT'

c)Sa se afiseze numarul de angajati din fiecare departament.

SELECT denumire AS "DEPARTAMENT", COUNT (*) AS "NUMARUL DE ANGAJA?I"

FROM departamente a, angajati b

WHERE a.cod=b.cod_depart

GROUP BY denumire

CREATE TABLE diriginti

(

id_d NUMBER(2) PRIMARY KEY,

nume VARCHAR2(25) NOT NULL,

prenume VARCHAR2(30) NOT NULL,

specialitate VARCHAR(15) NOT NULL

)

DESCRIBE diriginti

SELECT * FROM DIRIGINTI

INSERT INTO diriginti VALUES(1, 'POP', 'INA', 'FIZICA')

INSERT INTO diriginti VALUES(2, 'DASCAL', 'GINA', 'MATEMATICA')

INSERT INTO diriginti VALUES(3, 'MIRON', 'DANA', 'CHIMIE')

INSERT INTO diriginti VALUES(4, 'MIRON', 'DAN', 'INFORMATICA')

INSERT INTO diriginti VALUES(5, 'CARP', 'VLAD', 'ISTORIE')

INSERT INTO diriginti VALUES(6, 'POPA', 'ANA', 'BIOLOGIE')

CREATE TABLE elevi

(

cnp NUMBER(13) PRIMARY KEY,

nume VARCHAR2(50) NOT NULL,

adresa VARCHAR2(50) NOT NULL,

media NUMBER(4,2) NOT NULL,

id_d NUMBER(2) REFERENCES diriginti(id_d)

)

INSERT INTO elevi VALUES(2961904555777, 'Grig Adelina', '1 Mai, 40, Bucuresti', 9.40, 5)

INSERT INTO elevi VALUES(2950207555777, 'Alexa Mara', 'Calea Unirii 23, Bucuresti', 10.00, 2)

INSERT INTO elevi VALUES(2960410555777, 'Darie Ada', 'Calea Unirii 67, Bucuresti', 9.00, 3)

INSERT INTO elevi VALUES(1972411555777, 'Vladoi Cristinel', 'Florilor 1, Bucuresti', 8.80, 6)

INSERT INTO elevi VALUES(2962512555777, 'Pop Ana', 'Pandurilor 67, Bucuresti', 9.50, 4)

INSERT INTO elevi VALUES(1971708555777, 'Antonescu Paul', 'Calea Unirii 76, Bucuresti', 10.00, 1)

INSERT INTO elevi VALUES(2981907555777, 'Ionescu Ana', 'Trandafirilor 1, Bucuresti', 8.80, 1)

INSERT INTO elevi VALUES(2980909555777, 'Alex Dana', 'P. Rares 67, Bucuresti', 9.00, 5)

INSERT INTO elevi VALUES(1990210555777, 'Darie Mihai', '1 Mai, 46, Bucuresti', 10.00, 4)

INSERT INTO elevi VALUES(2972411555777, 'Vlad Cristina','Calea Unirii 123, Bucuresti', 8.90, 6)

INSERT INTO elevi VALUES(2962912555777, 'Radu Anda', '1 Mai, 489, Bucuresti', 9.22, 3)

INSERT INTO elevi VALUES(1951709555777, 'Anton Mihai', 'Calea Unirii 237, Bucuresti', 9.10, 2)

SELECT *

FROM elevi

b) Sa se. afiseze numele dirigintilor a caror elevi au media 10. Se va afisa numele elevului, numele dirigintelui si specialitatea dirigintelui.

SELECT a.nume AS "Numele elevului", b.nume AS "Nume diriginte", prenume AS "Prenume diriginte", specialitate

FROM elevi a, diriginti b

WHERE media='10.00' AND a.id_d=b.id_d

c) Sa se afiseze media generala a fiecareiclase. Se va afisa numele dirigintelui si media generala a clasei.

SELECT a.id_d, b.nume, b.prenume, a."Media Clasei"

FROM(SELECT id_d, AVG(media) AS "Media Clasei" FROM elevi GROUP BY id_d) a, diriginti b

WHERE a.id_d=b.id_d

ORDER BY id_d

Pb 13

a) Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

create table cladiri

(

cod_cladire number(2) primary key,

adresa varchar2(50) not null,

nr_nivele number(2) not null

)

insert into cladiri values(1,'1 Mai,40,Bucuresti',1)

insert into cladiri values(2,'Calea Uniri 23,Bucuresti',4)

insert into cladiri values(3,'Calea Unirii 67,Bucuresti',2)

insert into cladiri values(4,'Florilor 1,Bucuresti',3)

insert into cladiri values(5,'Pandurilor 67, Bucuresti',2)

SELECT * FROM cladiri

ORDER BY cod_cladire

create table apartamente

(

cod_cladire number(1) references cladiri(cod_cladire) not null,

cod_apartamente number(2) primary key,

nr_camere number(2)not null,

etaj number(2) not null,

obs varchar2(50)

)

insert into apartamente values(2,1,2,1,'Are centrala proprie')

insert into apartamente values(1,12,1,2,' ')

insert into apartamente values(1,11,3,1,' ')

insert into apartamente values(2,2,3,2,'Reanovat')

insert into apartamente values(3,10,2,1,'Are centrala proprie')

insert into apartamente values(4,5,2,3,' ')

insert into apartamente values(5,6,4,1,'Renovat')

insert into apartamente values(3,3,1,1,' ')

select * from apartamente

b) Afisati apartamentele care se afla оn cladiri care au mai mult de 2 nivele.

select a.adresa, b.cod_apartamente, b.nr_camere, b.etaj, b.obs

from cladiri a, apartamente b

where nr_nivele>'2' AND a.cod_cladire=b.cod_cladire

c) Sa se afiseze la ce adresa se gaseste apartamentul care are cele mai multe camere.

select adresa

from cladiri a,apartamente b

where nr_camere=(select max(nr_camere)from apartamente) and a.cod_cladire=b.cod_cladire

Problema 14

a) Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE cluburi

(

cod_club NUMBER(2) PRIMARY KEY,

denumire VARCHAR2(30) NOT NULL,

localitate VARCHAR2(20) NOT NULL

)

desc cluburi

INSERT INTO cluburi VALUES(1,'Club Zenit','Cluj')

INSERT INTO cluburi VALUES(2,'Club DINAMO','Bucuresti')

INSERT INTO cluburi VALUES(3,'Club Steaua','Bacau')

INSERT INTO cluburi VALUES(4,'Club Rapid','Bucuresti')

INSERT INTO cluburi VALUES(5,'Club Piticot','Suceava')

INSERT INTO cluburi VALUES(6,'Club Ardealul','Cluj')

SELECT * FROM cluburi

ORDER BY cod_club

CREATE TABLE sportivi

(

cod_club NUMBER(2) REFERENCES cluburi(cod_club),

cods NUMBER(3) Primary Key,

nume VARCHAR2(30) NOT NULL,

prenume VARCHAR2(30) NOT NULL,

varsta NUMBER(3) NOT NULL

)

INSERT INTO sportivi VALUES (1, 11, 'Pop', 'Ina', 15)

INSERT INTO sportivi VALUES (2,22,'Dascal','Gina',20)

INSERT INTO sportivi VALUES (2,33,'Miron','Dana',21)

INSERT INTO sportivi VALUES (4,44,'Miron','Dan',19)

INSERT INTO sportivi VALUES (5,55,'Carp','Vlad',17)

INSERT INTO sportivi VALUES (3,66,'Popa','Ana',21)

INSERT INTO sportivi VALUES (6,77, 'Avram', 'Mona', 17)

INSERT INTO sportivi VALUES (5,88,'Enache','Cristi',18)

SELECT * FROM sportivi

ORDER BY cods

b) Sa se afiseze cluburile alfabetic ?i media de vвrsta a sportivilor fiecarui club.

SELECT denumire, a."media de varsta" FROM

(SELECT cod_club, AVG(varsta) AS "media de varsta" FROM sportivi GROUP BY cod_club) a, cluburi b

WHERE a.cod_club=b.cod_club

ORDER BY denumire

c) Sa se afiseze alfabetic cei(cel) mai batrвni sportivi оmpreuna cu cluburile lor.

SELECT nume, prenume,varsta,denumire

FROM sportivi b, cluburi a

WHERE varsta=(SELECT MAX(VARSTA) FROM sportivi) AND a.cod_club=b.cod_club

ORDER by nume asc,prenume asc;

Problema 15

a) Construiti baza de date cu tabele corespunzatoare si relatiile reprezentate оn ERD. Populati tabelele cu оnregistrari relevante pentru cerintele urmatoare.

CREATE TABLE apartamente15

(

coda NUMBER(2) PRIMARY KEY,

nr_camere NUMBER(2) NOT NULL,

nr_ap NUMBER(2) NOT NULL,

suma_restante NUMBER(4) NOT NULL

)

INSERT INTO apartamente15 VALUES(1, 2, 20, 0)

INSERT INTO apartamente15 VALUES(2, 3, 21, 75)

INSERT INTO apartamente15 VALUES(3, 4, 22, 100)

INSERT INTO apartamente15 VALUES(4, 3, 23, 0)

INSERT INTO apartamente15 VALUES(5, 4, 24, 200)

INSERT INTO apartamente15 VALUES(6, 2, 25, 50)

select * from apartamente15

CREATE TABLE locatari

(

codl NUMBER(3) PRIMARY KEY not null,

coda NUMBER(2) REFERENCES apartamente15(coda) not null,

nume VARCHAR2(20) not null,

prenume VARCHAR2(30) not null,

loc_munca VARCHAR2(50)

)

INSERT INTO locatari VALUES(111, 1, 'POP', 'INA', 'SC ASTAR')

INSERT INTO locatari VALUES(112, 2, 'DASCAL', 'GINA', 'GPN2')

INSERT INTO locatari VALUES(113, 4, 'MIRON', 'DANA', 'GPN2')

INSERT INTO locatari VALUES(114, 4, 'MIRON', 'DAN', 'SC EMPOS')

INSERT INTO locatari VALUES(115, 3, 'CARP', 'VLAD', 'SC GEN 1')

INSERT INTO locatari VALUES(116, 3, 'CARP', 'ANA', 'SC GEN 1')

INSERT INTO locatari VALUES(117, 5, 'AVRAM', 'MONA', 'GPN2')

INSERT INTO locatari VALUES(118, 6, 'ENACHE', 'CRISTI', 'AUTOSERVICE')Pb1 Oracle