test2

DROP TABLE TRATEAZA;
DROP TABLE FUNCTII;
DROP TABLE SPECIALIZARE;
DROP TABLE PERSONAL;
DROP TABLE PACIENTI;

CREATE TABLE PERSONAL
( id_salariat NUMBER(10) NOT NULL,
  nume varchar2(50),
  prenume varchar2(50),
  adresa varchar2(50),
  data_nastere DATE,
  salariu NUMBER(10),
  id_functie NUMBER(10),
  id_specializare NUMBER(10),
  CONSTRAINT id_salariat_pk PRIMARY KEY (id_salariat)
);

CREATE TABLE PACIENTI
( id_pacient NUMBER(10) NOT NULL,
  nume varchar2(50),
  prenume varchar2(50),
  data_nastere DATE,
  CONSTRAINT id_pacient_pk PRIMARY KEY (id_pacient)
);

CREATE TABLE TRATEAZA
( id_salariat NUMBER(10) NOT NULL,
  id_pacient NUMBER(10) NOT NULL,
  data_internare DATE,
  data_externare DATE
);

CREATE TABLE FUNCTII
( id_functie NUMBER(10) NOT NULL,
  nume_functie varchar2(50),
  salariu_minim NUMBER(10),
  salariu_maxim NUMBER(10),
  CONSTRAINT id_functie_pk PRIMARY KEY (id_functie)
);

CREATE TABLE SPECIALIZARE
( id_specializare NUMBER(10) NOT NULL,
  nume_specializare varchar2(50),
  id_manager NUMBER(10),
  CONSTRAINT id_specializare_pk PRIMARY KEY (id_specializare)
);

INSERT INTO FUNCTII (id_functie, nume_functie, salariu_minim, salariu_maxim) VALUES (1, 'Doctor', 5000, 10000);
INSERT INTO FUNCTII (id_functie, nume_functie, salariu_minim, salariu_maxim) VALUES (2, 'Asistent', 500, 5000);

INSERT INTO SPECIALIZARE  (id_specializare, nume_specializare, id_manager) VALUES (1, 'rx', 1);
INSERT INTO SPECIALIZARE  (id_specializare, nume_specializare, id_manager) VALUES (2, 'orl', 2);

INSERT INTO PERSONAL VALUES
(1, 'Gica', 'Contra', 'addr 1', '1994-01-26', 8000, 1, 1), -- doctor la rx
(2, 'Zoidberg', 'John', 'addr 2', '1991-01-01', 7000, 1, 2), -- doctor la orl
(3, 'Xulescu', 'Maria', 'addr 3', '1992-01-01', 2000, 2, 1), -- asistent la rx
(4, 'Yulescu', 'Ana', 'addr 4', '1993-01-01', 3000, 2, 2); -- asistent la orl

INSERT INTO PACIENTI VALUES
(1, 'ionesc1', 'maria', '2000-02-01'),
(2, 'ionesc2', 'maria', '1980-02-02'),
(3, 'ionesc3', 'maria', '1999-02-03'),
(4, 'ionesc4', 'maria', '1970-02-04'),
(5, 'ionesc5', 'maria', '1999-02-05'),
(6, 'ionesc6', 'maria', '1992-02-06'),
(7, 'ionesc7', 'maria', '1988-02-07'),
(8, 'ionesc8', 'maria', '1988-02-08'),
(9, 'ionesc9', 'maria', '1989-02-09'),
(10, 'ionesc10', 'maria', '1999-02-10');

INSERT INTO TRATEAZA VALUES
(1, 1, '2009-02-01', '2009-02-10'),
(1, 2, '2009-02-02', '2009-02-11'),
(1, 3, '2009-02-03', '2009-02-12'),
(1, 4, '2009-02-04', '2009-02-13'),
(2, 5, '2009-02-05', '2009-02-14'),
(3, 6, '2009-02-06', '2009-02-15'),
(4, 7, '2009-02-07', '2009-02-16'),
(2, 8, '2009-02-08', '2009-02-17'),
(3, 9, '2009-02-09', '2009-02-18'),
(3, 10, '2009-02-10', '2009-09-19');