c4vxXdEX

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
58insert into asistent values
59('Generalist','Principal');
60
61create table if not exists angajat(
62CNP varchar(10) primary key,
63salariu int,
64nr_ore int,
65foreign key (CNP) references utilizator(CNP));
66
67insert into angajat values
68('1000',2500,45);
69
70drop table angajat;
71drop table utilizator;
72drop table user;
73drop table asistent;
74
75create table if not exists user(
76CNP varchar(10) primary key,
77username varchar(20),
78password varchar(20),
79foreign key (CNP) references utilizator(CNP));
80
81insert into user values
82('1000','DragosChis','dragos');
83
84create table if not exists medic(
85CNP varchar(10) primary key,
86grad_medic varchar(10),
87cod_parafa varchar(10) unique,
88titlu_stiintific varchar(20),
89post_didactic varchar(20),
90procent_servicii int,
91foreign key (CNP) references angajat(CNP));
92
93drop table competente_medic;
94drop table medic;
95
96insert into medic values
97('1000','Primar','10001','Doctorand','Conferentiar',15);
98
99create table if not exists competenta(
100id int unique primary key,
101denumire_competenta varchar(20));
102
103insert into competenta values
104(1,'Ecografie'),
105(2,'Endoscopie digestivă'),
106(3,'Bronhoscopie'),
107(4,'EMG');
108
109create table if not exists competente_medic(
110CNP_medic varchar(10),
111id_competenta int,
112foreign key (CNP_medic) references medic(CNP),
113foreign key (id_competenta) references competenta(id));
114
115insert into competente_medic values
116('1000',1),
117('1000',2);
118
119select nume,denumire_competenta from utilizator join competenta
120join competente_medic on utilizator.CNP=competente_medic.CNP_medic and competenta.id=competente_medic.id_competenta;