Advertisement
halexandru11

in caz ca ai chef de niste puncte la info

Apr 27th, 2021
409
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE VAMI (
  2.     ID_VAMA INTEGER PRIMARY KEY,
  3.     NUME_VAMA VARCHAR2(50) NOT NULL,
  4.     JUDET VARCHAR2(50) NOT NULL
  5. )
  6.  
  7. CREATE TABLE TRECERI (
  8.     ID_TRECERE INTEGER PRIMARY KEY,
  9.     NR_AUTOTURISM VARCHAR2(10) NOT NULL,
  10.     COND_AUTO VARCHAR2(50) NOT NULL,
  11.     NR_PERSOANE SMALLINT NOT NULL,
  12.     TIP CHAR NOT NULL,
  13.     DATA DATE NOT NULL,
  14.     ID_VAMA INTEGER REFERENCES VAMI(ID_VAMA)
  15. )
  16.  
  17.  
  18. INSERT INTO VAMI VALUES(102, 'MORAVITA', 'TIMIS')
  19. INSERT INTO VAMI VALUES(104, 'BORS', 'BIHOR')
  20. INSERT INTO VAMI VALUES(100, 'SIRET', 'SUCEAVA')
  21. INSERT INTO VAMI VALUES(105, 'CENAD', 'TIMIS')
  22. INSERT INTO VAMI VALUES(101, 'NADLAC', 'ARAD')
  23. INSERT INTO VAMI VALUES(103, 'RUSE', 'GIURGIU')
  24.  
  25. INSERT INTO TRECERI VALUES(12, 'SV09MNN', 'MIHAI OLGA' , 3, 'I', '3/12/2011', 102)
  26. INSERT INTO TRECERI VALUES(14, 'VS08AAT', 'IGNATESCU CORINA' , 2, 'E', '2/10/2011', 102)
  27. INSERT INTO TRECERI VALUES(16, 'IS09RRE', 'JURAVLE MARCEL' , 4, 'I', '5/12/2011', 102)
  28. INSERT INTO TRECERI VALUES(19, 'CJ11QQW', 'ALBOTA CRISTINA' , 3, 'E', '8/29/2011', 101)
  29. INSERT INTO TRECERI VALUES(20, 'MM07SSW', 'MANCIU EMANUEL' , 2, 'I', '4/11/2011', 101)
  30. INSERT INTO TRECERI VALUES(13, 'TM12XYY', 'CORLATEAN MIRCEA' , 1, 'E', '1/09/2010', 100)
  31. INSERT INTO TRECERI VALUES(15, 'MM06JST', 'MACOVEI TIBERIU' , 5, 'I', '2/10/2011', 105)
  32. INSERT INTO TRECERI VALUES(17, 'CJ11YYW', 'BOCA EMILIANA' , 5, 'E', '1/22/2011', 103)
  33. INSERT INTO TRECERI VALUES(18, 'VS02UUF', 'CRAINICIUC TOEDORA' , 1, 'I', '8/29/2011', 102)
  34. INSERT INTO TRECERI VALUES(21, 'IS10UTY', 'SOROCEANU MIRCEA' , 3, 'E', '10/23/2011', 102)
  35.  
  36.  
  37. SELECT COND_AUTO FROM TRECERI
  38. WHERE EXTRACT(YEAR FROM DATA) = 2011
  39.     AND ID_VAMA = (SELECT ID_VAMA FROM VAMI WHERE UPPER(NUME_VAMA) LIKE '%MORAVITA%')
  40.  
  41. SELECT SUM(NR_PERSOANE) AS PERSOANE, NUME_VAMA FROM TRECERI INNER JOIN VAMI ON VAMI.ID_VAMA = TRECERI.ID_VAMA
  42. GROUP BY NUME_VAMA
Advertisement
RAW Paste Data Copied
Advertisement