[infoiasi][fii][bd] model test laborator 2 (ianuarie 2012)

7/21/2019 [InfoIasi][FII][BD] Model test laborator 2 (ianuarie 2012)

http://slidepdf.com/reader/full/infoiasifiibd-model-test-laborator-2-ianuarie-2012 1/3

BD (test la laborator – partea a doua – ianuarie 2012)

grupul I de tabele – setul 1

1. Sa se afiseze dintr-un bloc anonim PL/SQL, folosind un cursor explicit, anul,

matricola, numele si prenumele studentului, media lui, ordonat dupa an si dupa

numele studentului.

2. Dintr-un bloc anonim PL/SQL, folosind un cursor explicit, sa se elimine din tabela

note studenti cea mai mica nota a fiecarui student. Daca sunt mai multe astfel de

note, sa se elimine una singura la intamplare.

grupul II de tabele – setul 3

1. Sa se afiseze dintr-un bloc anonim PL/SQL, folosind un cursor explicit, tipul taxei,

denumirea ei si suma totala achitata pentru acea taxa in ordinea crescatoare a

tipului si codului taxelor.

2. Dintr-un bloc anonim, folosind un cursor explicit, sa se elimine din tabela incasari

toate incasarile corespunzatoare taxelor pentru care s-au platit cele mai mari

sume (daca V este valoarea maxima platita intre sumele platite pentru toate

taxele sa se elimine din incasari toate incasarile pentru taxele pentru care

valoarea totala a platilor este V).



-- grupul 1 de tabele

DROP TABLE studenti CASCADE CONSTRAINTS;CREATE TABLE studenti

(mat NUMBER(4) PRIMARY KEY, -- matricola studentuluinume VARCHAR2(20),prenume VARCHAR2(20),

an NUMBER(1) -- anul de studii al studentului);

DROP TABLE obiecte CASCADE CONSTRAINTS;

CREATE TABLE obiecte(cod NUMBER(3) PRIMARY KEY, -- codul materieiden VARCHAR2(20),

an NUMBER(1) -- anul de studiu al obiectului);

DROP TABLE note_studenti CASCADE CONSTRAINTS;CREATE TABLE note_studenti

(mat NUMBER(4) REFERENCES studenti( mat ),cod NUMBER(3) REFERENCES obiecte( cod ),nota NUMBER(5,2));

INSERT INTO studenti VALUES (1001,'nume1','prenume1',2);INSERT INTO studenti VALUES (1002,'nume2','prenume2',2);

INSERT INTO studenti VALUES (1003,'nume3','prenume3',2);INSERT INTO studenti VALUES (1004,'nume4','prenume4',1);INSERT INTO studenti VALUES (1005,'nume5','prenume5',1);INSERT INTO studenti VALUES (1006,'nume6','prenume6',1);INSERT INTO studenti VALUES (1007,'nume7','prenume7',3);INSERT INTO studenti VALUES (1008,'nume8','prenume8',3);INSERT INTO studenti VALUES (1009,'nume9','prenume9',3);INSERT INTO studenti VALUES (1010,'nume10','prenume10',3);INSERT INTO studenti VALUES (1011,'nume12','prenume11',4);INSERT INTO studenti VALUES (1012,'nume13','prenume12',4);INSERT INTO studenti VALUES (1013,'nume14','prenume13',4);

INSERT INTO obiecte VALUES (101,'den1',2);INSERT INTO obiecte VALUES (102,'den2',2);INSERT INTO obiecte VALUES (103,'den3',1);INSERT INTO obiecte VALUES (104,'den4',1);

INSERT INTO obiecte VALUES (105,'den5',3);INSERT INTO obiecte VALUES (106,'den6',3);INSERT INTO obiecte VALUES (107,'den7',4);INSERT INTO obiecte VALUES (108,'den8',4);

INSERT INTO note_studenti VALUES (1004,103,7);INSERT INTO note_studenti VALUES (1005,104,6);INSERT INTO note_studenti VALUES (1006,103,9);INSERT INTO note_studenti VALUES (1001,103,5);INSERT INTO note_studenti VALUES (1002,103,3);INSERT INTO note_studenti VALUES (1003,103,8);

INSERT INTO note_studenti VALUES (1001,104,9);INSERT INTO note_studenti VALUES (1002,104,6);INSERT INTO note_studenti VALUES (1003,104,7);INSERT INTO note_studenti VALUES (1001,101,5);

INSERT INTO note_studenti VALUES (1002,101,3);INSERT INTO note_studenti VALUES (1003,102,8);

INSERT INTO note_studenti VALUES (1007,103,7);INSERT INTO note_studenti VALUES (1008,104,6);INSERT INTO note_studenti VALUES (1009,103,9);INSERT INTO note_studenti VALUES (1010,103,5);INSERT INTO note_studenti VALUES (1011,103,3);INSERT INTO note_studenti VALUES (1008,103,8);INSERT INTO note_studenti VALUES (1009,104,9);INSERT INTO note_studenti VALUES (1007,104,6);

INSERT INTO note_studenti VALUES (1010,104,7);INSERT INTO note_studenti VALUES (1009,101,5);INSERT INTO note_studenti VALUES (1007,101,3);INSERT INTO note_studenti VALUES (1008,102,8);INSERT INTO note_studenti VALUES (1008,102,8);INSERT INTO note_studenti VALUES (1012,107,8);INSERT INTO note_studenti VALUES (1012,105,6);INSERT INTO note_studenti VALUES (1012,103,3);



-- grupul 2 de tabele

DROP TABLE contribuabili CASCADE CONSTRAINTS;CREATE TABLE contribuabili

(cod NUMBER(4) PRIMARY KEY,nume VARCHAR2(40),tip NUMBER(1));

DROP TABLE taxe CASCADE CONSTRAINTS;CREATE TABLE taxe

(cod NUMBER(3) PRIMARY KEY,den VARCHAR2(20),tip NUMBER(1));

DROP TABLE incasari CASCADE CONSTRAINTS;CREATE TABLE incasari

(codc NUMBER(4) REFERENCES contribuabili( cod ),codt NUMBER(3) REFERENCES taxe( cod ),suma NUMBER(12,2));

INSERT INTO contribuabili VALUES (1001,'nume1',1);INSERT INTO contribuabili VALUES (1002,'nume2',1);INSERT INTO contribuabili VALUES (1003,'nume3',1);INSERT INTO contribuabili VALUES (1004,'nume4',1);INSERT INTO contribuabili VALUES (1005,'nume5',2);INSERT INTO contribuabili VALUES (1006,'nume6',2);INSERT INTO contribuabili VALUES (1007,'nume7',2);INSERT INTO contribuabili VALUES (1008,'nume8',2);INSERT INTO contribuabili VALUES (1009,'nume9',2);INSERT INTO contribuabili VALUES (1010,'nume10',2);

INSERT INTO taxe VALUES (101,'taxa1',1);INSERT INTO taxe VALUES (102,'taxa2',1);INSERT INTO taxe VALUES (103,'taxa3',1);

INSERT INTO taxe VALUES (104,'taxa4',1);INSERT INTO taxe VALUES (105,'taxa5',2);INSERT INTO taxe VALUES (106,'taxa6',2);INSERT INTO taxe VALUES (107,'taxa7',2);

INSERT INTO incasari VALUES (1001,101,200);INSERT INTO incasari VALUES (1001,102,100);INSERT INTO incasari VALUES (1001,103,300);INSERT INTO incasari VALUES (1001,104,200);INSERT INTO incasari VALUES (1002,101,200);INSERT INTO incasari VALUES (1002,102,100);INSERT INTO incasari VALUES (1002,103,300);INSERT INTO incasari VALUES (1003,104,200);INSERT INTO incasari VALUES (1003,102,100);INSERT INTO incasari VALUES (1003,103,300);

INSERT INTO incasari VALUES (1005,105,300);INSERT INTO incasari VALUES (1005,106,100);INSERT INTO incasari VALUES (1005,107,400);INSERT INTO incasari VALUES (1007,106,100);INSERT INTO incasari VALUES (1007,107,400);

[infoiasi][fii][bd] model test laborator 2 (ianuarie 2012)

Documents