Ex 2019

April 26, 2019

CREATE TABLE SPEC( CODS NUMBER(2), NUME VARCHAR2(10), DOMENIU VARCHAR2(15)

);

INSERT INTO SPEC VALUES(11, ‘MATEMATICA’,’STIINTE EXACTE’);

INSERT INTO SPEC VALUES(21, ‘GEOGRAFIE’, ‘UMANIST’);

INSERT INTO SPEC VALUES(24, ‘ISTORIE’, ‘UMANIST’);

CREATE TABLE STUD( MATR NUMBER(4), NUME VARCHAR2(10), AN NUMBER(1), GRUPA VARCHAR2(6), DATAN DATE,

LOC VARCHAR2(10), TUTOR NUMBER(4), PUNCTAJ NUMBER(4), CODS NUMBER(2)

);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘1456′,’GEORGE’,’4′,’1141A’,to_date(’12- MAR-82′,’DD-MON- YY’),’BUCURESTI’,”,’2890′,’11’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘1325′,’VASILE’,’2′,’1122A’,to_date(’05- OCT-84′,’DD-MON- YY’),’PITESTI’,’1456′,’390′,’11’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘1645′,’MARIA’,’3′,’1131B’,to_date(’17-JUN- 83′,’DD-MON-YY’),’PLOIESTI’,”,’1400′,’11’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘3145′,’ION’,’1′,’2112B’,to_date(’24-JAN- 85′,’DD-MON- YY’),’PLOIESTI’,’3251′,’1670′,’21’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘2146′,’STANICA’,’4′,’2141A’,to_date(’15- MAY-82′,’DD-MON- YY’),’BUCURESTI’,”,’620′,’21’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘3251′,’ALEX’,’5′,’2153B’,to_date(’07-NOV- 81′,’DD-MON-YY’),’BRASOV’,”,’1570′,’21’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘2215′,’ELENA’,’2′,’2122A’,to_date(’29- AUG-84′,’DD-MON- YY’),’BUCURESTI’,’2146′,’890′,’21’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘4311′,’ADRIAN’,’3′,’2431A’,to_date(’31- JUL-83′,’DD-MON- YY’),’BUCURESTI’,”,’450′,’24’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘3514′,’FLOREA’,’5′,’2452B’,to_date(’03- FEB-81′,’DD-MON- YY’),’BRASOV’,”,’3230′,’24’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘1925′,’OANA’,’2′,’2421A’,to_date(’20- DEC-84′,’DD-MON- YY’),’BUCURESTI’,’4311′,’760′,’24’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘2101′,’MARIUS’,’1′,’2412B’,to_date(’02- SEP-85′,’DD-MON- YY’),’PITESTI’,’3514′,’310′,’24’);

insert into STUD (MATR, NUME, AN, GRUPA, DATAN, LOC, TUTOR, PUNCTAJ, CODS) values

(‘4705′,’VOICU’,’2′,’2412B’,to_date(’19- APR-84′,’DD-MON- YY’),’BRASOV’,’4311′,’1290′,’24’);

CREATE TABLE BURSA( TIP VARCHAR2(20), PMIN NUMBER(4), PMAX NUMBER(4), SUMA NUMBER(4)

);

INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(0, 399, ‘FARA BURSA’, NULL);

INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(400, 899, ‘BURSA SOCIALA’, 100);

INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(900, 1799, ‘BURSA DE STUDIU’, 150);

INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(1800, 2499, ‘BURSA DE MERIT’, 200);

INSERT INTO BURSA (PMIN, PMAX, TIP, SUMA) VALUES(2500, 9999, ‘BURSA DE EXCEPTIE’, 300);

select MATR||’-‘||NUME STUDENT, ‘ESTE IN’, GRUPA
FROM STUD;

STUDENT
  GRUPA

1456-GEORGE
   1141A

select DISTINCT CODS FROM STUD;

CODS 11 21 24

'ESTE IN'
 ESTE IN

select DISTINCT CODS,NUME
FROM STUD
ORDER BY CODS DESC;

CODS NUME

24 ADRIAN

24 FLOREA

24 MARIUS

24 OANA

24 VOICU

21

….

ALEX

select DISTINCT CODS,NUME FROM STUD
ORDER BY NUME, CODS DESC;

CODS NUME
24 ADRIAN 21 ALEX
21 ELENA 24 FLOREA 11 GEORGE 21 ION
11 MARIA 24 MARIUS 24 OANA
21 STANICA 11 VASILE 24 VOICU

select DISTINCT CODS,NUME FROM STUD
WHERE CODS = 11
ORDER BY NUME DESC;

CODS NUME
11 VASILE 11 MARIA
11 GEORGE

SELECT NUME, PUNCTAJ
FROM STUD
WHERE PUNCTAJ BETWEEN 1000 AND 2000;

NUME PUNCTAJ MARIA 1400 ION 1670 ALEX 1570 VOICU 1290

SELECT NUME, PUNCTAJ, CODS COD_STUDENT
FROM STUD
WHERE CODS IN (11,21);

NUME PUNCTAJ COD_STUDENT GEORGE 2890 11

VASILE
MARIA
ION
STANICA 620 21 ALEX 1570 21 ELENA 890 21

390 11 1400 11 1670 21

SELECT NUME, PUNCTAJ, CODS COD_STUDENT
FROM STUD
WHERE CODS LIKE ‘2%’;

NUME PUNCTAJ COD_STUDENT ION 1670 21
STANICA 620 21
ALEX 1570 21

ELENA 890 21 ADRIAN 450 24 FLOREA 3230 24 OANA 760 24 MARIUS 310 24 VOICU 1290 24

  Simbol
  Reprezentare
  ------
  --

    %
  secventa de zero sau mai
  multe caractere

    -             un singur
  caracter oarecare

SELECT NUME, PUNCTAJ, CODS COD_STUDENT
FROM STUD
WHERE NUME LIKE ‘____’;

NUME PUNCTAJ COD_STUDENT ALEX 1570 21
OANA 760 24

SELECT NUME
FROM STUD
WHERE TUTOR IS NULL;

NUME GEORGE MARIA STANICA ALEX ADRIAN FLOREA

           Urmatorii operatori
 fac teste de negatie:

           Operator
 Descriere

           ---------
 ----------

           !=
 diferit de(VAX,UNIX,PC)

           ^=
 diferit de(IBM)

           <>
 diferit de(toate o/s)

           NOT NUMECOL=
 diferit de

           NOT NUMECOL>
 mai mic sau egal

           Operatori SQL

           Operator
 Descriere

           --------
 ---------

           NOT BETWEEN
 nu se afla intre doua valori

date
NOT IN

 nu se afla intr-o lista data
 de valori

           NOT LIKE
 diferit de sirul

           IS NOT NULL
 nu este o valoare nula

 SELECT NUME, CODS
 FROM STUD
 WHERE CODS NOT LIKE '2%';

NUME CODS GEORGE 11 VASILE 11 MARIA 11

SELECT NUME, TUTOR FROM STUD
WHERE TUTOR IS NOT NULL;

NUME TUTOR VASILE 1456 ION 3251 ELENA 2146 OANA 4311 MARIUS 3514 VOICU 4311

 SELECT NUME, PUNCTAJ, TUTOR
 FROM STUD
 WHERE PUNCTAJ BETWEEN 1000
 AND 2000

 OR TUTOR = 3514
 ORDER BY PUNCTAJ DESC;

NUME PUNCTAJ TUTOR ION 1670 3251 ALEX 1570 – MARIA 1400 – VOICU 1290 4311 MARIUS 310 3514

SELECT NUME
FROM STUD
WHERE NUME LIKE ‘%AN%’; // PT ‘%AN’ VA AFISA DOAR ADRIAN

NUME STANICA ADRIAN OANA

SELECT NUME, DATAN „DATA DE NASTERE”
FROM STUD
WHERE DATAN LIKE ‘%85’;

NUME DATA DE NASTERE ION 24-JAN-85 MARIUS 02-SEP-85

SELECT NUME, DATAN „DATA DE NASTERE”
FROM STUD
WHERE DATAN LIKE ‘%JU%’;

———-

orice

NUME MARIA ADRIAN

DATA DE NASTERE 17-JUN-83 31-JUL-83

SELECT TIP, SUMA, (SUMA+20)*1.1 „BURSA MARITA”

FROM BURSA;

TIP

FARA BURSA BURSA SOCIALA

BURSA DE STUDIU

SUMA BURSA MARITA

– – 100 132

150 187 BURSA DE MERIT 200 242

BURSA DE EXCEPTIE

300 352

SELECT NUME, LOC, DATAN FROM STUD WHERE LOC IN (‘BUCURESTI’, ‘PLOIESTI’) OR DATAN IN (’02-SEP-85′, ’19-APR-84′, ’29-AUG-84′);

NUME LOC DATAN GEORGE BUCURESTI 12-MAR-82 MARIA PLOIESTI 17-JUN-83 ION PLOIESTI 24-JAN-85 STANICA BUCURESTI 15-MAY-82 ELENA BUCURESTI 29-AUG-84 ADRIAN BUCURESTI 31-JUL-83 OANA BUCURESTI 20-DEC-84

SELECT NUME||’_’||DOMENIU AS „NUME SI DOMENIU”
FROM SPEC
WHERE NUME||’_’||DOMENIU LIKE ‘%\_U%’ ESCAPE ‘\’;

SAU

SELECT NUME||’_’||DOMENIU AS „NUME SI DOMENIU”
FROM SPEC
WHERE NUME||’_’||DOMENIU LIKE ‘%U%’;

NUME SI DOMENIU GEOGRAFIE_UMANIST ISTORIE_UMANIST

SELECT CODS, NUME, PUNCTAJ, (PUNCTAJ+20) PMARIT
FROM STUD
WHERE CODS=11

ORDER BY PMARIT;

CODS NUME
11 VASILE
11 MARIA
11 GEORGE 2890 2910

PUNCTAJ PMARIT 390 410 1400 1420

SELECT CODS, NUME, PUNCTAJ, (PUNCTAJ+20)*1.1 PMARIT, (PUNCTAJ- (PUNCTAJ+20)*1.1) „CRITERIU ORDONARE” FROM STUD

WHERE CODS=11 ORDER BY PUNCTAJ- PMARIT;

CODS NUME PUNCTAJ PMARIT CRITER ORDON

11 GEORGE 2890 11 MARIA 1400 11 VASILE 390

3201 -311 1562 -162 451 -61

!0 “0 #$% ORDER BY 3 DESC, 2;

MATR NUME AN 3251 ALEX 5 3514 FLOREA 5 1456 GEORGE 4 2146 STANICA 4 4311 ADRIAN 3 1645 MARIA 3 2215 ELENA 2 1925 OANA 2 1325 VASILE 2 4705 VOICU 2 3145 ION 1 2101 MARIUS 1

SELECT DISTINCT STUD.NUME, DATAN, DOMENIU, SPEC.NUME, STUD.CODS FROM STUD, SPEC
WHERE STUD.CODS=SPEC.CODS ORDER BY STUD.NUME;

NUME DATAN DOMENIU NUME

31-
ADRIAN JUL- UMANIST ISTORIE

83

07-
ALEX NOV- UMANIST GEOGRAFIE

81

29-
ELENA AUG- UMANIST GEOGRAFIE

84

03-
FLOREA FEB- UMANISTISTORIE

81

12- STIINTE
GEORGE MAR- EXACTE MATEMATIC

82

24-
ION JAN- UMANIST GEOGRAFIE

85

17- STIINTE
MARIA JUN- EXACTE MATEMATIC

83

02-
MARIUS SEP- UMANISTISTORIE

85

20-
OANA DEC- UMANIST ISTORIE

84

15-
STANICA MAY- UMANISTGEOGRAFIE

82

05- STIINTE
VASILE OCT- EXACTE MATEMATIC

84

19-
VOICU APR- UMANIST ISTORIE

84

SELECT S1.NUME „STUDENT 1”, S2.NUME „STUDENT 2”,S2.TUTOR „TUTOR”
FROM STUD S1, STUD S2
WHERE S1.TUTOR=S2.TUTOR

AND S1.MATR > S2.MATR; // DACA NU AVEAM CONDITIA AR FI AFISAT page1image2574784

SELECT MATR, NUME, AN

FROM STUD

STUDENT 1 VOICU

STUDENT 1 VASILE ION ELENA OANA VOICU MARIUS OANA VOICU

STUDENT 2 TUTOR OANA 4311

STUDENT 2 TUTOR VASILE 1456 ION 3251 ELENA 2146 OANA 4311 OANA 4311 MARIUS 3514 VOICU 4311 VOICU 4311

SELECT S.NUME „NUME STUD”, S.AN „AN STUD”, T.NUME „NUME TUTOR”, T.AN „AN TUTOR”
FROM STUD S, STUD T

WHERE S.CODS = 11 AND S.TUTOR=T.MATR(+); // PENTRU A ADAUGA ȘI STUDENTII FARA TUTOR

NUME AN NUME AN

I

A

A

A