HW2-JOIN&SubQUERYES.sql

DROP TABLE PACIENT;
DROP TABLE DOCTOR;

CREATE TABLE DOCTOR
(
ID_DOC NUMBER(3),
NUME_DOC VARCHAR2(100),
PRENUME_DOC VARCHAR2(100),
SPECIALIZARE VARCHAR2(100),
CONSTRAINT PK_DOC PRIMARY KEY(ID_DOC, NUME_DOC)
);

CREATE TABLE PACIENT
(
ID_DOC NUMBER(3),
NUME_DOC VARCHAR2(100),
ID_PAC NUMBER(3),
NUME_PAC VARCHAR2(100),
PRENUME_PAC VARCHAR2(100),
DATA_INTERNARE DATE,
DATA_EXTERNARE DATE,
CONSTRAINT PK_PAC PRIMARY KEY(ID_DOC, NUME_DOC, ID_PAC),
CONSTRAINT FK_PAC FOREIGN KEY(ID_DOC, NUME_DOC) REFERENCES DOCTOR(ID_DOC, NUME_DOC)
);

INSERT INTO DOCTOR VALUES (1, 'POPESCU', 'DARIUS', 'PEDIATRU');
INSERT INTO DOCTOR VALUES (2, 'IONESCU', 'MARIAN', 'CHIRURG');
INSERT INTO DOCTOR VALUES (3, 'GEORGESCU', 'GEORGE', 'DENTIST');
INSERT INTO DOCTOR VALUES (4, 'ENESCU', 'RAZVAN', 'PEDIATRU');
INSERT INTO DOCTOR VALUES (5, 'BASESCU', 'ADRIAN', 'CHIRURG');

INSERT INTO PACIENT VALUES (1, 'POPESCU', 1, 'POP', 'CALIN', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-03-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (1, 'POPESCU', 2, 'ANDREI', 'MIHAI', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-03-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (2, 'IONESCU', 3, 'RADU', 'BALOIU', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-04-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (2, 'IONESCU', 4, 'MIHAI', 'BOB', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-04-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (3, 'GEORGESCU', 5, 'VERDE', 'VALENTIN', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-05-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (3, 'GEORGESCU', 6, 'ROSU', 'PETRE', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-05-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (4, 'ENESCU', 7, 'DULGHERU', 'TITI', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-05-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (4, 'ENESCU', 8, 'CASCAVAL', 'DELACO', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-06-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (5, 'BASESCU', 9, 'HAGI', 'GHEORGHE', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-06-2015', 'DD-MM-YYYY'));
INSERT INTO PACIENT VALUES (5, 'BASESCU', 10, 'CRISTIANO', 'RONALDO', TO_DATE('10-03-2015', 'DD-MM-YYYY'), TO_DATE('12-06-2015', 'DD-MM-YYYY'));

---join
SELECT A.NUME_DOC, A.PRENUME_DOC, A.SPECIALIZARE
FROM DOCTOR A, PACIENT B
WHERE
A.ID_DOC = B.ID_DOC
AND
A.NUME_DOC = B.NUME_DOC
AND
B.NUME_PAC = 'RADU';

---subcerere
SELECT A.NUME_DOC, A.PRENUME_DOC, A.SPECIALIZARE
FROM DOCTOR A
WHERE
(A.ID_DOC, A.NUME_DOC) IN
(
SELECT B.ID_DOC, B.NUME_DOC
FROM PACIENT B
WHERE
B.NUME_PAC = 'RADU'
);

---join
SELECT A.NUME_PAC, A.PRENUME_PAC, A.DATA_EXTERNARE
FROM PACIENT A, DOCTOR B 
WHERE
A.ID_DOC = B.ID_DOC
AND
A.NUME_DOC = B.NUME_DOC
AND
B.ID_DOC = 2;

---subcerere
SELECT A.NUME_PAC, A.PRENUME_PAC, A.DATA_EXTERNARE
FROM PACIENT A
WHERE
(A.ID_DOC, A.NUME_DOC) IN
(
SELECT B.ID_DOC, B.NUME_DOC
FROM DOCTOR B
WHERE
B.ID_DOC = 2
);

---variabila susbstituibila
SELECT A.NUME_PAC, A.PRENUME_PAC
FROM PACIENT A 
WHERE
A.ID_DOC = '&&VAR';