PROBLEMA 8 BD

a)
CREATE TABLE CLIENTE(
    ID INTEGER PRIMARY KEY,
    NUME VARCHAR2(50) NOT NULL,
    PRENUME VARCHAR2(50) NOT NULL,
    ADRESA VARCHAR2(50) NOT NULL
)

CREATE TABLE RECLAME (
    ID INTEGER PRIMARY KEY,
    ID_CLIENT INTEGER REFERENCES CLIENTE(ID),
    DENUMIRE VARCHAR2(50) NOT NULL,
    DURATA SMALLINT NOT NULL,
    NR_DIFUZARI SMALLINT NOT NULL,
    TARIF SMALLINT NOT NULL
)

INSERT INTO CLIENTE VALUES(1, 'POP', 'INA', 'IAŞI')
INSERT INTO CLIENTE VALUES(2, 'DASCAL', 'GINA', 'DEVA')
INSERT INTO CLIENTE VALUES(3, 'MIRON', 'DANA', 'CLUJ')
INSERT INTO CLIENTE VALUES(4, 'MIRON', 'DAN', 'CLUJ')
INSERT INTO CLIENTE VALUES(5, 'CARP', 'VLAD', 'BACĂU')
INSERT INTO CLIENTE VALUES(6, 'POPA', 'ANA', 'BACĂU')

INSERT INTO RECLAME VALUES(11, 1, 'COCA-COLA', 2, 4, 200)
INSERT INTO RECLAME VALUES(22, 2, 'LENOR', 1, 2, 150)
INSERT INTO RECLAME VALUES(33, 2, 'ARIEL', 1, 2, 140)
INSERT INTO RECLAME VALUES(44, 4, 'ORANGE', 2, 3, 220)
INSERT INTO RECLAME VALUES(55, 5, 'URSUS', 1, 1, 123)
INSERT INTO RECLAME VALUES(66, 3, 'COLGATE', 1, 1, 125)
INSERT INTO RECLAME VALUES(77, 6, 'POIANA', 2, 1, 100)
INSERT INTO RECLAME VALUES(88, 5, 'CIUC', 1, 1, 130)

b)
SELECT CLIENTE.ID, CLIENTE.NUME, CLIENTE.PRENUME, CLIENTE.ADRESA, RECLAME.DENUMIRE FROM CLIENTE
INNER JOIN RECLAME ON RECLAME.ID_CLIENT = CLIENTE.ID
ORDER BY CLIENTE.NUME, CLIENTE.PRENUME

c)
SELECT CLIENTE.NUME, CLIENTE.PRENUME, CLIENTE.ADRESA FROM CLIENTE
INNER JOIN RECLAME ON RECLAME.ID_CLIENT = CLIENTE.ID
WHERE RECLAME.NR_DIFUZARI = (SELECT MAX(RECLAME.NR_DIFUZARI) FROM RECLAME)