SCCb0ZaP

1DROP TABLE IF EXISTS clinica CASCADE;
2DROP TABLE IF EXISTS tipos_de_dieta CASCADE;
3DROP TABLE IF EXISTS servicio CASCADE;
4DROP TABLE IF EXISTS cliente CASCADE; 
5DROP TABLE IF EXISTS dieta CASCADE;
6DROP TABLE IF EXISTS dietista CASCADE;
7DROP TABLE IF EXISTS examina CASCADE;
8
9
10CREATE TABLE clinica(
11    ID_clinica integer CONSTRAINT PK_clinica PRIMARY KEY,
12    nombre char(15) UNIQUE not null,
13    ciudad char (15) not null
14    );
15INSERT INTO clinica VALUES (
16'0', 'Salut', 'Madrid'
17);
18
19INSERT INTO clinica VALUES (
20'1', 'Fitness', 'Madrid'
21);
22INSERT INTO clinica VALUES (
23'2', 'En forma', 'Barcelona'
24);
25INSERT INTO clinica VALUES (
26'3', 'Vallejo', 'Valladolid'
27);
28INSERT INTO clinica VALUES (
29'4', 'Healthy', 'Burgos'
30);
31INSERT INTO clinica VALUES (
32'5', 'Nutrix', 'Valencia'
33);
34 CREATE TABLE tipos_de_dieta(
35    ID_tipo_de_dieta integer CONSTRAINT PK_tipos_de_dieta PRIMARY KEY,
36    nombre_dieta CHAR (20) UNIQUE NOT NULL,
37    descripcion CHAR(100)
38    );
39
40INSERT INTO tipos_de_dieta VALUES (
41'0', 'Dieta hipocalorica', 'Destinada para bajar de peso, ingeriendo menos calorias de las que gastas diariamente'
42);
43
44INSERT INTO tipos_de_dieta VALUES (
45'1', 'Dieta hipercalorica', 'Destinada para ganar de peso, ingeriendo más calorias de las que gastas diaramente'
46);
47
48INSERT INTO tipos_de_dieta VALUES (
49'2', 'Dieta Vegatariana', 'Dieta que no incluye carne animal'
50);
51
52INSERT INTO tipos_de_dieta VALUES (
53'3', 'Dieta Vegana', 'Dieta que no incluye carne animal, ni alimentos deberivados de animales'
54);
55
56INSERT INTO tipos_de_dieta VALUES (
57'4', 'Dieta cetogenica', 'Destinada para bajar de peso, reduciendo mucho los hidratos de carbono y aumentando las grasas'
58);
59
60
61CREATE TABLE servicio(
62    ID_clinica integer references clinica ON DELETE CASCADE ON UPDATE CASCADE,
63    ID_tipo_de_dieta integer references tipos_de_dieta ON DELETE CASCADE ON UPDATE CASCADE,
64    precio_€ numeric(4,2) not null,
65    PRIMARY KEY (ID_clinica, ID_tipo_de_dieta)
66);
67INSERT INTO servicio VALUES (
68'0', '0', '12.35'
69);
70
71INSERT INTO servicio VALUES (
72'0', '1', '11.35'
73);
74
75INSERT INTO servicio VALUES (
76'0', '3', '12.35'
77);
78
79INSERT INTO servicio VALUES (
80'0', '4', '25.35'
81);
82
83INSERT INTO servicio VALUES (
84'1', '0', '12.1'
85);
86
87INSERT INTO servicio VALUES (
88'1', '1', '9'
89);
90
91INSERT INTO servicio VALUES (
92'2', '0', '16'
93);
94
95INSERT INTO servicio VALUES (
96'2', '4', '11.05'
97);
98
99INSERT INTO servicio VALUES (
100'3', '0', '12.35'
101);
102
103INSERT INTO servicio VALUES (
104'3', '1', '12.55'
105);
106
107INSERT INTO servicio VALUES (
108'3', '3', '17.35'
109);
110
111INSERT INTO servicio VALUES (
112'3', '4', '42.35'
113);
114
115INSERT INTO servicio VALUES (
116'4', '1', '22.05'
117);
118
119INSERT INTO servicio VALUES (
120'4', '3', '12.35'
121);
122
123INSERT INTO servicio VALUES (
124'4', '4', '13.9'
125);
126
127CREATE TABLE cliente(
128    DNI_cliente char(9)CONSTRAINT PK_cliente PRIMARY KEY check( DNI_cliente similar to
129'[0-9]{8}[A-Z]'),
130    Nombre char(15)not null,
131    Apellido1 char(15)not null,
132    Apellido2 char(15)not null
133    
134    );
135INSERT INTO cliente VALUES (
136'71454564N', 'Juan', 'Ortiz', 'Delgado'
137);
138INSERT INTO cliente VALUES (
139'71114566S', 'Jose', 'Fernandez', 'Tapia'
140);
141INSERT INTO cliente VALUES (
142'71977564D', 'Ana', 'Garcia', 'Albiol'
143);
144INSERT INTO cliente VALUES (
145'71003260P', 'David', 'Hernando', 'Suarez'
146);
147INSERT INTO cliente VALUES (
148'71657853K', 'Sara', 'Iglesias', 'Alarcon'
149);
150INSERT INTO cliente VALUES (
151'71436555C', 'Maria', 'Blanco', 'Perez'
152);
153INSERT INTO cliente VALUES (
154'73445454P', 'Alejandro', 'Delgado', 'Arribas'
155);
156INSERT INTO cliente VALUES (
157'72449249J', 'Estela', 'Montero', 'Rivera'
158);
159
160CREATE TABLE dietista(
161    DNI_dietista char(9) check( DNI_dietista similar to '[0-9]{8}[A-Z]'),
162    Nombre_Dietista char(15) not null,
163    ID_clinica integer not null references clinica,
164    DNI_mentor char(9) check( DNI_dietista similar to '[0-9]{8}[A-Z]') references dietista,
165    PRIMARY KEY (DNI_dietista)
166    );
167INSERT INTO dietista VALUES (
168'72977563M', 'Alvaro', '0'
169);
170INSERT INTO dietista VALUES (
171'72911883L', 'Andres', '0', '72977563M'
172);
173INSERT INTO dietista VALUES (
174'72900065J', 'Amanda', '0', '72977563M'
175);
176INSERT INTO dietista VALUES (
177'72032461M', 'Pedro', '1'
178);
179INSERT INTO dietista VALUES (
180'72773567Q', 'Lucia', '2'
181);
182INSERT INTO dietista VALUES (
183'72977000W', 'Rebeca', '3' 
184);
185INSERT INTO dietista VALUES (
186'72111321X', 'Lorena', '3', '72977000W'
187);
188INSERT INTO dietista VALUES (
189'72946542V', 'Juan', '4'
190);
191INSERT INTO dietista VALUES (
192'72087652D', 'Jose', '4', '72946542V'
193);
194INSERT INTO dietista VALUES (
195'71343534V', 'Natalia', '4', '72087652D'
196);
197
198CREATE TABLE dieta(
199     DNI_cliente char(9) check( DNI_cliente similar to '[0-9]{8}[A-Z]') references cliente ON DELETE CASCADE ON UPDATE CASCADE,
200     ID_clinica integer,
201     ID_tipo_de_dieta integer,
202     DNI_dietista char(9) check( DNI_dietista similar to '[0-9]{8}[A-Z]') references dietista not null  REFERENCES dietista ON DELETE CASCADE ON UPDATE CASCADE,
203     FOREIGN KEY (ID_clinica, ID_tipo_de_dieta) references servicio ON DELETE CASCADE ON UPDATE CASCADE,
204     PRIMARY KEY (DNI_cliente, ID_clinica, ID_tipo_de_dieta)
205     );
206INSERT INTO dieta VALUES (
207'72449249J', '0', '0', '72977563M'
208);
209INSERT INTO dieta VALUES (
210'71454564N', '1', '1','72032461M'
211);
212INSERT INTO dieta VALUES (
213'71454564N', '0', '0','72900065J'
214);
215INSERT INTO dieta VALUES (
216'73445454P', '2', '4', '72773567Q'
217);
218INSERT INTO dieta VALUES (
219'71436555C', '2', '0', '72773567Q'
220);
221INSERT INTO dieta VALUES (
222'71657853K', '3', '1','72977000W'
223);
224INSERT INTO dieta VALUES (
225'71003260P', '3', '3', '72111321X'
226);
227INSERT INTO dieta VALUES (
228'71977564D', '4', '3', '72087652D'
229);
230
231
232CREATE TABLE examina(
233    fecha_reunion DATE not null,
234    DNI_cliente char(9) check( DNI_cliente similar to '[0-9]{8}[A-Z]') references cliente,
235    DNI_dietista char(9) check( DNI_cliente similar to '[0-9]{8}[A-Z]')references dietista not null,
236    PRIMARY KEY (fecha_reunion,DNI_cliente)
237    );
238INSERT INTO examina VALUES(
239DATE '3/12/2017', '71977564D', '72087652D'
240);
241INSERT INTO examina VALUES(
242DATE '21/11/2017', '71003260P', '72111321X'
243);
244INSERT INTO examina VALUES(
245DATE '20/11/2017', '71003260P', '72977000W'
246);
247INSERT INTO examina VALUES(
248DATE '20/11/2017', '71657853K', '72977000W'
249);
250
251/*
252--Consulta 1
253--Enunciado:¿cual es el precio medio de la clinica más barata? (entendemos por la clinica más barata aquella cuyo precio medio sea el menor)
254SELECT MIN(preciomedio) FROM (
255SELECT ID_clinica, AVG(precio_€) preciomedio
256FROM servicio
257GROUP BY ID_clinica
258) AS preciominimo;
259--Resultado: el precio medio de la clinica más barata deberia ser 10,55
260*/
261/*
262--Consulta 2
263--Enunciado:¿Qué clinicas (nombre) ofrecen en su catalogo al menos el 80% de los tipos de dieta que existen en la base de datos de la empresa?
264SELECT nombre FROM servicio natural join clinica
265GROUP BY nombre
266HAVING count(*) >= 0.8*( SELECT COUNT(*) FROM tipos_de_dieta);
267--Resultado: Salut y Vallejo
268*/
269/*
270--Consulta 3
271--Enunciado:¿Qué clinicas (nombre) ofrecen en su catalogo la dieta hipocalorica y la dieta vegana?
272Select  nombre FROM clinica natural join servicio natural join tipos_de_dieta Where (nombre_dieta='Dieta hipocalorica')
273INTERSECT
274Select  nombre FROM clinica natural join servicio natural join tipos_de_dieta Where  (nombre_dieta='Dieta Vegana')
275--Resultado: Salut y Vallejo
276*/
277/*
278--consulta 4
279--Enunciado: clinicas que no tengan ningun tipo de dieta hipocalorica
280select nombre from clinica except(
281select nombre from clinica natural join servicio natural join tipos_de_dieta where nombre_dieta = 'Dieta hipocalorica')
282--Resultado: Healty y Nutrix (nutrix al no ofrecer tipos de dieta, no tine tampoco ninguna dieta hipocalorica)
283*/
284/*
285--consulta 5
286--Enunciado: Muestras todos los dietistas y el numero de dietas que hace cada dietista
287select Nombre_Dietista, DNI_dietista, count(dieta.DNI_dietista) from dietista natural left join dieta group by DNI_dietista
288--Resultado, Lucia 2 dietas, Pedro, Rebeca, Jose, Lorena, Amanda y Alvaro 1 dieta y Juan, Natalia y Andres ninguna dieta
289*/
290/*
291--Consulta 6
292--Enunciado: Muestra el nombre de la clinica que tiene el tipo de dieta más caro junto al nombre de ese tipo de dieta
293select nombre, nombre_dieta from  tipos_de_dieta natural join servicio natural join clinica where precio_€ = (select max(precio_€) from servicio)
294--Resultado: En la clinica Vallejo la Dieta cetogenica
295*/