PROBLEMA 8 BD

Dec 10th, 2020
125
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
1. a)
2. CREATE TABLE CLIENTE(
3.     ID INTEGER PRIMARY KEY,
4.     NUME VARCHAR2(50) NOT NULL,
5.     PRENUME VARCHAR2(50) NOT NULL,
7. )
8.
9. CREATE TABLE RECLAME (
10.     ID INTEGER PRIMARY KEY,
11.     ID_CLIENT INTEGER REFERENCES CLIENTE(ID),
12.     DENUMIRE VARCHAR2(50) NOT NULL,
13.     DURATA SMALLINT NOT NULL,
14.     NR_DIFUZARI SMALLINT NOT NULL,
15.     TARIF SMALLINT NOT NULL
16. )
17.
18. INSERT INTO CLIENTE VALUES(1, 'POP', 'INA', 'IAŞI')
19. INSERT INTO CLIENTE VALUES(2, 'DASCAL', 'GINA', 'DEVA')
20. INSERT INTO CLIENTE VALUES(3, 'MIRON', 'DANA', 'CLUJ')
21. INSERT INTO CLIENTE VALUES(4, 'MIRON', 'DAN', 'CLUJ')
22. INSERT INTO CLIENTE VALUES(5, 'CARP', 'VLAD', 'BACĂU')
23. INSERT INTO CLIENTE VALUES(6, 'POPA', 'ANA', 'BACĂU')
24.
25. INSERT INTO RECLAME VALUES(11, 1, 'COCA-COLA', 2, 4, 200)
26. INSERT INTO RECLAME VALUES(22, 2, 'LENOR', 1, 2, 150)
27. INSERT INTO RECLAME VALUES(33, 2, 'ARIEL', 1, 2, 140)
28. INSERT INTO RECLAME VALUES(44, 4, 'ORANGE', 2, 3, 220)
29. INSERT INTO RECLAME VALUES(55, 5, 'URSUS', 1, 1, 123)
30. INSERT INTO RECLAME VALUES(66, 3, 'COLGATE', 1, 1, 125)
31. INSERT INTO RECLAME VALUES(77, 6, 'POIANA', 2, 1, 100)
32. INSERT INTO RECLAME VALUES(88, 5, 'CIUC', 1, 1, 130)
33.
34. b)
35. SELECT CLIENTE.ID, CLIENTE.NUME, CLIENTE.PRENUME, CLIENTE.ADRESA, RECLAME.DENUMIRE FROM CLIENTE
36. INNER JOIN RECLAME ON RECLAME.ID_CLIENT = CLIENTE.ID
37. ORDER BY CLIENTE.NUME, CLIENTE.PRENUME
38.
39. c)
40. SELECT CLIENTE.NUME, CLIENTE.PRENUME, CLIENTE.ADRESA FROM CLIENTE
41. INNER JOIN RECLAME ON RECLAME.ID_CLIENT = CLIENTE.ID
42. WHERE RECLAME.NR_DIFUZARI = (SELECT MAX(RECLAME.NR_DIFUZARI) FROM RECLAME)