TPLAB1

CREATE DATABASE TPenunciado1

USE TPenunciado1

CREATE TABLE provincias
(id_provincia INT,
 provincia VARCHAR(15)
 CONSTRAINT pk_provincias PRIMARY KEY (id_provincia))

CREATE TABLE localidades
(id_localidad INT,
localidad VARCHAR (15),
id_provincia INT,
CONSTRAINT pk_localidades PRIMARY KEY (id_localidad),
CONSTRAINT fk_provincias_localidades FOREIGN KEY (id_provincia)
REFERENCES provincias (id_provincia))

CREATE TABLE barrios
(id_barrio INT,
barrio  VARCHAR(15),
id_localidad INT
CONSTRAINT pk_barrios PRIMARY KEY (id_barrio),
CONSTRAINT fk_localidades_barrios FOREIGN KEY (id_localidad)
REFERENCES localidades (id_localidad))


CREATE TABLE obras_sociales
(id_obra_social INT,
obra_social VARCHAR(20),
CONSTRAINT pk_obras_sociales PRIMARY KEY (id_obra_social))

CREATE TABLE calles
(id_calle INT,
calle VARCHAR (20),
CONSTRAINT pk_calles PRIMARY KEY (id_calle))

CREATE TABLE pacientes
(id_paciente INT,
nombre VARCHAR (20),
apellido VARCHAR(20),
nro_doc VARCHAR(12),
telefono VARCHAR(30),
fecha_nac datetime,
e_mail VARCHAR(40),
id_calle INT,
altura INT,
id_barrio INT,
id_obra_social INT
CONSTRAINT pk_pacientes PRIMARY KEY (id_paciente),
CONSTRAINT fk_calles_pacientes FOREIGN KEY(id_calle) REFERENCES calles (id_calle),
CONSTRAINT fk_barrios_pacientes FOREIGN KEY(id_barrio) REFERENCES barrios (id_barrio),
CONSTRAINT fk_obras_sociales_pacientes FOREIGN KEY(id_obra_social) REFERENCES obras_sociales (id_obra_social))

CREATE TABLE especialidades
(id_especialidad INT,
especialidad VARCHAR(15),
CONSTRAINT pk_especialidades PRIMARY KEY (id_especialidad))

CREATE TABLE medicos
(id_medico INT,
nombre VARCHAR(20),
apellido VARCHAR(20),
id_especialidad INT,
matricula INT
CONSTRAINT pk_medicos PRIMARY KEY (id_medico),
CONSTRAINT fk_especialidades_medicos FOREIGN KEY (id_especialidad) REFERENCES especialidades (id_especialidad))


CREATE TABLE turnos
(id_turno INT,
id_paciente INT,
id_medico INT,
fecha_hora datetime,
CONSTRAINT pk_turnos PRIMARY KEY (id_turno),
CONSTRAINT fk_pacientes_turnos FOREIGN KEY (id_paciente) REFERENCES pacientes (id_paciente),
CONSTRAINT fk_medicos_pacientes FOREIGN KEY(id_medico) REFERENCES medicos (id_medico))

CREATE TABLE estudios
(id_estudio INT,
estudio VARCHAR(20)
CONSTRAINT pk_estudios PRIMARY KEY (id_estudio))

CREATE TABLE consultas
(id_consulta INT,
id_paciente INT,
fecha_hora datetime,
id_obra_social INT,
id_medico INT,
id_especialidad INT,
CONSTRAINT pk_consultas PRIMARY KEY (id_consulta),
CONSTRAINT fk_pacientes_consultas FOREIGN KEY (id_paciente)REFERENCES pacientes (id_paciente),
CONSTRAINT fk_medicos_consultas FOREIGN KEY (id_medico)REFERENCES medicos (id_medico),
CONSTRAINT fk_obras_sociales_consultas FOREIGN KEY (id_obra_social)REFERENCES obras_sociales (id_obra_social),
CONSTRAINT fk_especialidades_consultas FOREIGN KEY (id_especialidad)REFERENCES especialidades (id_especialidad))

CREATE TABLE diagnosticos
(id_diagnostico INT,
id_consulta INT,
id_estudio INT,
diagnostico VARCHAR(50),
CONSTRAINT pk_diagnosticos PRIMARY KEY (id_diagnostico),
CONSTRAINT fk_consultas_diagnosticos FOREIGN KEY (id_consulta) REFERENCES consultas (id_consulta),
CONSTRAINT fk_estudios_diagnosticos FOREIGN KEY(id_estudio) REFERENCES estudios (id_estudio))



--Provincias
INSERT INTO provincias (id_provincia, provincia) VALUES (1, 'Córdoba')
INSERT INTO provincias (id_provincia, provincia) VALUES (2, 'Tucumán')
INSERT INTO provincias (id_provincia, provincia) VALUES (3, 'Salta')
INSERT INTO provincias (id_provincia, provincia) VALUES (4, 'Catamarca')
INSERT INTO provincias (id_provincia, provincia) VALUES (5, 'Buenos Aires')

--select * from provincias

--Localidades
INSERT INTO localidades (id_localidad, localidad, id_provincia) VALUES (1, 'Alta Gracia', 1)
INSERT INTO localidades (id_localidad, localidad, id_provincia) VALUES (2, 'Simoca', 2)
INSERT INTO localidades (id_localidad, localidad, id_provincia) VALUES (3, 'San Pedro', 3)
INSERT INTO localidades (id_localidad, localidad, id_provincia) VALUES (4, 'Ambato', 4)
INSERT INTO localidades (id_localidad, localidad, id_provincia) VALUES (5, 'Chillar', 5)

--select * from localidades

--Calles
INSERT INTO calles (id_calle, calle) VALUES (1, 'Belgrano')
INSERT INTO calles (id_calle, calle) VALUES (2, 'Esmeralda')
INSERT INTO calles (id_calle, calle) VALUES (3, 'Corrientes')
INSERT INTO calles (id_calle, calle) VALUES (4, 'Urquiza')
INSERT INTO calles (id_calle, calle) VALUES (5, 'Abasto')

--select * from calles

--Barrios
INSERT INTO barrios (id_barrio, barrio, id_localidad) VALUES (1, 'Jardín', 1)
INSERT INTO barrios (id_barrio, barrio, id_localidad) VALUES (2, 'Alta Córdoba', 2)
INSERT INTO barrios (id_barrio, barrio, id_localidad) VALUES (3, 'Centro', 3)
INSERT INTO barrios (id_barrio, barrio, id_localidad) VALUES (4, 'Norte', 4)
INSERT INTO barrios (id_barrio, barrio, id_localidad) VALUES (5, 'Eva Perón', 5)

--select * from barrios

--Obras Sociales
INSERT INTO obras_sociales (id_obra_social, obra_social) VALUES (1, 'OSDE')
INSERT INTO obras_sociales (id_obra_social, obra_social) VALUES (2, 'Medifé')
INSERT INTO obras_sociales (id_obra_social, obra_social) VALUES (3, 'Galeno')
INSERT INTO obras_sociales (id_obra_social, obra_social) VALUES (4, 'Sancor Salud')
INSERT INTO obras_sociales (id_obra_social, obra_social) VALUES (5, 'Luis Pasteur')

--select * from obras_sociales

--Pacientes
INSERT INTO pacientes (id_paciente, nombre, apellido, nro_doc, telefono, fecha_nac, e_mail, id_calle, altura, id_barrio, id_obra_social) VALUES (1, 'Martín', 'Gonzalez', '23.492.315', '3513729361', '04/05/1979', 'martingonzalez@gmail.com', 1, 144, 1, 1)
INSERT INTO pacientes (id_paciente, nombre, apellido, nro_doc, telefono, fecha_nac, e_mail, id_calle, altura, id_barrio, id_obra_social) VALUES (2, 'Juan', 'García', '41.422.903', '3878584634', '10/01/1999', 'juangarcia@gmail.com', 2, 3436, 2, 2)
INSERT INTO pacientes (id_paciente, nombre, apellido, nro_doc, telefono, fecha_nac, e_mail, id_calle, altura, id_barrio, id_obra_social) VALUES (3, 'Joaquín', 'Clavería', '44.800.354', '3514687249', '12/09/2004', 'joaquinclaveria@gmail.com', 3, 2124, 3, 3)
INSERT INTO pacientes (id_paciente, nombre, apellido, nro_doc, telefono, fecha_nac, e_mail, id_calle, altura, id_barrio, id_obra_social) VALUES (4, 'Mariela', 'López', '28.700.318', '3512469378', '11/09/1982', 'marielalopez@gmail.com', 4, 1400, 4, 4)
INSERT INTO pacientes (id_paciente, nombre, apellido, nro_doc, telefono, fecha_nac, e_mail, id_calle, altura, id_barrio, id_obra_social) VALUES (5, 'Malen', 'Flores', '35.441.946', '3514726324', '08/02/1987', 'malenflores@gmail.com', 5, 800, 5, 5)

--select * from  pacientes

--Especialidades
INSERT INTO especialidades (id_especialidad, especialidad) VALUES (1,'Cardiologia')
INSERT INTO especialidades (id_especialidad, especialidad) VALUES (2,'Pediatria')
INSERT INTO especialidades (id_especialidad, especialidad) VALUES (3,'Traumatologia')
INSERT INTO especialidades (id_especialidad, especialidad) VALUES (4,'Oftalmologia')
INSERT INTO especialidades (id_especialidad, especialidad) VALUES (5,'ORL')

--select * from especialidades

--Medicos
INSERT INTO medicos (id_medico,id_especialidad,apellido,nombre,matricula) VALUES (1,5,'Cardozo', 'Sergio',63625)
INSERT INTO medicos (id_medico,id_especialidad,apellido,nombre,matricula) VALUES (2,4,'Lancioni', 'Horacio',84951)
INSERT INTO medicos (id_medico,id_especialidad,apellido,nombre,matricula) VALUES (3,3,'Quiroga', 'Octavio',66805)
INSERT INTO medicos (id_medico,id_especialidad,apellido,nombre,matricula) VALUES (4,2,'Albornoz', 'Alejandro',70580)
INSERT INTO medicos (id_medico,id_especialidad,apellido,nombre,matricula) VALUES (5,1,'Favaloro', 'Rene',80269)

--select * from medicos

--Consultas
INSERT INTO consultas (id_consulta, id_paciente, fecha_hora , id_obra_social, id_medico, id_especialidad) VALUES (1,1,'05/06/2018 16:00', 5, 4, 2)
INSERT INTO consultas (id_consulta, id_paciente, fecha_hora , id_obra_social, id_medico, id_especialidad) VALUES (2,2,'09/06/2018 12:00', 4, 5, 1)
INSERT INTO consultas (id_consulta, id_paciente, fecha_hora , id_obra_social, id_medico, id_especialidad) VALUES (3,3,'07/06/2018 19:30', 3, 3, 3)
INSERT INTO consultas (id_consulta, id_paciente, fecha_hora , id_obra_social, id_medico, id_especialidad) VALUES (4,4,'08/06/2018 14:35', 2, 2, 4)
INSERT INTO consultas (id_consulta, id_paciente, fecha_hora , id_obra_social, id_medico, id_especialidad) VALUES (5,5,'10/06/2018 10:20', 1, 1, 5)

--select * from consultas

--Estudios
INSERT INTO estudios (id_estudio,estudio) VALUES (1,'Radiografia')
INSERT INTO estudios (id_estudio,estudio) VALUES (2,'Ecografia')
INSERT INTO estudios (id_estudio,estudio) VALUES (3,'Doppler')
INSERT INTO estudios (id_estudio,estudio) VALUES (4,'Resonancia')
INSERT INTO estudios (id_estudio,estudio) VALUES (5,'Tomografia')

--select * from estudios

--Diagnosticos
INSERT INTO diagnosticos (id_diagnostico, id_consulta, id_estudio, diagnostico) VALUES (1,3,4,'Osteoporosis')
INSERT INTO diagnosticos (id_diagnostico, id_consulta, id_estudio, diagnostico) VALUES (2,2,2,'Gastroenteritis')
INSERT INTO diagnosticos (id_diagnostico, id_consulta, id_estudio, diagnostico) VALUES (3,1,NULL,'Reaccion Alergica')
INSERT INTO diagnosticos (id_diagnostico, id_consulta, id_estudio, diagnostico) VALUES (4,5,NULL,'Sinusitis Aguda')
INSERT INTO diagnosticos (id_diagnostico, id_consulta, id_estudio, diagnostico) VALUES (5,4,NULL,'Anemia')

SELECT * FROM diagnosticos

--Turnos
INSERT INTO turnos (id_turno, id_paciente, id_medico,fecha_hora) VALUES (1, 4, 5, '06/20/18 10:30')
INSERT INTO turnos (id_turno, id_paciente, id_medico,fecha_hora) VALUES (2, 4, 3, '06/24/18 18:00')
INSERT INTO turnos (id_turno, id_paciente, id_medico,fecha_hora) VALUES (3, 5, 3, '06/14/18 20:00')
INSERT INTO turnos (id_turno, id_paciente, id_medico,fecha_hora) VALUES (4, 2, 2, '06/15/18 15:00')
INSERT INTO turnos (id_turno, id_paciente, id_medico,fecha_hora) VALUES (5, 2, 1, '06/16/18 08:30')

SELECT pacientes.nombre + ' '+ pacientes.apellido Paciente, medicos.nombre + ' ' + medicos.apellido Medico, fecha_hora
FROM pacientes, medicos, turnos
WHERE turnos.id_paciente = pacientes.id_paciente AND turnos.id_medico = medicos.id_medico