Advertisement
halexandru11

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,
  6.     ADRESA 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)
Advertisement
RAW Paste Data Copied
Advertisement