KPVUCFi5

1create database LantPoliclinici;
2
3use LantPoliclinici;
4
5create table if not exists unitate_medicala(
6id int unique not null primary key,
7nume varchar(20),
8adresa varchar(20));
9
10insert into unitate_medicala values
11(1,'DragMed','Cluj-Napoca'),
12(2,'SebMed','Iasi'),
13(3,'ComMed','Alba-Iulia');
14
15create table if not exists program_unitate_medicala(
16id int primary key,
17ora_deschidere time,
18ora_inchidere time,
19zi_saptamana varchar(20),
20foreign key (id) references unitate_medicala(id)
21);
22
23insert into program_unitate_medicala values
24(1,'08:00:00','18:00:00',"Luni"),
25(2,'09:00:00','17:00:00',"Luni"),
26(3,'08:30:00','16:00:00',"Luni");
27
28insert into program_unitate_medicala values
29(1,'09:00:00','12:00:00',"Sambata");
30
31select * from program_unitate_medicala 
32where(ora_inchidere < '16:30:00');
33
34select zi_saptamana,nume from program_unitate_medicala,unitate_medicala
35where(zi_saptamana = "Sambata" and ora_inchidere > '11:30:00' and unitate_medicala.id = program_unitate_medicala.id);
36
37create table if not exists utilizator(
38CNP varchar(10) unique primary key,
39nume varchar(20),
40prenume varchar(20),
41adresa varchar(20),
42nr_tel varchar(10),
43email varchar(20),
44IBAN varchar(30),
45nr_contact varchar(10),
46data_angajare date,
47functie varchar(20));
48
49insert into utilizator values
50('1000','Chis','Dragos','Cluj-Napoca','00','cdragos','0001','00','2017-01-01','medic');
51
52create table if not exists asistent(
53CNP varchar(20) primary key,
54tip_asistent varchar(10),
55grad_asistent varchar(10),
56foreign key (CNP) references angajat(CNP));
57
58create table if not exists angajat(
59CNP varchar(10) primary key,
60salariu int,
61nr_ore int,
62foreign key (CNP) references utilizator(CNP));
63
64insert into angajat values
65('1000',2500,45);
66
67drop table angajat;
68drop table utilizator;
69drop table user;
70drop table asistent;
71
72create table if not exists user(
73CNP varchar(10) primary key,
74username varchar(20),
75password varchar(20),
76foreign key (CNP) references utilizator(CNP));
77
78insert into user values
79('1000','DragosChis','dragos');
80
81create table if not exists medic(
82CNP varchar(10) primary key,
83grad_medic varchar(10),
84cod_parafa varchar(10) unique,
85titlu_stiintific varchar(20),
86post_didactic varchar(20),
87procent_servicii int,
88foreign key (CNP) references angajat(CNP));
89
90drop table competente_medic;
91drop table medic;
92
93insert into medic values
94('1000','Primar','10001','Doctorand','Conferentiar',15);
95
96create table if not exists competenta(
97id int unique primary key,
98denumire_competenta varchar(20));
99
100insert into competenta values
101(1,'Ecografie'),
102(2,'Endoscopie digestivă'),
103(3,'Bronhoscopie'),
104(4,'EMG');
105
106create table if not exists competente_medic(
107CNP_medic varchar(10),
108id_competenta int,
109foreign key (CNP_medic) references medic(CNP),
110foreign key (id_competenta) references competenta(id));
111
112insert into competente_medic values
113('1000',1),
114('1000',2);
115
116select nume,denumire_competenta from utilizator join competenta
117join competente_medic on utilizator.CNP=competente_medic.CNP_medic and competenta.id=competente_medic.id_competenta;
118
119create table if not exists serviciu_medical(
120id int unique primary key,
121grad_medic varchar(20),
122denumire varchar(20),
123specialitate varchar(20),
124pret int,
125durata int);
126
127create table if not exists concediu(
128CNP varchar(10) primary key,
129data_inceput DATE,
130data_sfarsit DATE,
131foreign key (CNP) references angajat(CNP));
132
133create table if not exists salariu(
134CNP varchar(10) primary key,
135nr_ore_lucrate int,
136foreign key (CNP) references angajat(CNP));