Solución al examen practico de sql

--Examen práctico modulo bases de datos.

--Crear un script de sql para realizar las siguientes tareas.

--1. Crear una base de datos llamada clínica:

--a. La base de datos tendrá dos archivos.

--b. El archivo de datos se llamara clínica_data (.mdf) con tamaño inicial de 20 mb, tamaño máximo 50mb y crecimiento del 20%.

--c. El archivo de registro (log) se llamara clínica_log (.ldf) con tamaño inicial de 15 mb, tamaño máximo 30 mb y crecimiento del 10%.

use master;

drop database ClinicaGPV;

go

create database ClinicaGPV on ( name ='ClinicaGPV_data', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ ClinicaGPV_data.mdf', size=20mb, maxsize= 50mb, filegrowth=20% )

log on ( name = 'ClinicaGPV_log', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ ClinicaGPV_log.ldf', size= 15mb, maxsize= 30mb, filegrowth=10% ); go use ClinicaGPV;

 go

--2. En la base de datos crear las siguientes tablas:

--a. Tabla Pacientes con la siguientes columnas:

--i. PacienteID entero clave principal

--ii. Nombre texto requerido.

--iii. Apellidos texto requerido.

--iv. Dirección texto opcional

--v. CodigoPostal texto requerido

--vi. Provincia texto requerido

--vii. Teléfono texto opcional

--viii. FechaNacimiento Fecha requerido.

create table Pacientes( PacienteID int not null primary key, Nombre varchar(50) not null, Apellidos varchar(50) not null, Dirección varchar(100) null, CodigoPostal char(5) not null, Provincia varchar(50) not null, Telefono varchar(12) null, FechaNacimiento Date not null);

 go

--b. Tabla Médicos con la siguientes columnas:

--i. MedicoID entero clave principal

--ii. Nombre texto requerido.

--iii. Apellidos texto requerido.

--iv. Teléfono texto requerido.

create table Medicos( MedicoID int not null primary key, Nombre varchar(50) not null, Apellidos varchar(50) not null, Telefono varchar(12) not null, );

--c. Tabla Ingresos con la siguientes columnas:

--i. NumIngreso entero auto incrementado clave principal

--ii. PacienteID entero requerido clave ajena Pacientes

--iii. Habitación entero requerido

--iv. Cama texto requerido

--v. FechaIngreso FechaHora requerido

create table Ingresos( NumIngreso int not null identity(1,1) primary key, PacienteID int not null, Habitacion int not null, Cama char(1) not null, FechaIngreso DateTime not null, constraint Ingresos_PacientesFk Foreign key (PacienteID) references Pacientes(PacienteID) );

go

--d. Tabla Especialidades con la siguientes columnas:

--i. EspecialidadID entero clave principal

--ii. Especialidad texto requerido

create table Especialidades( EspecialidadID int not null identity(1,1) primary key, Especialidad varchar(50) not null );

go

--e. Tabla MedicosEspecialidades con la siguientes columnas:

--i. EspecialidadID entero clave principal, clave ajena a Especialidades

--ii. MedicoID entero clave principal clave ajena a Médicos

create table MedicosEspecialidades ( EspecialidadID int not null, MedicoID int not null, constraint MedicosEspecialidadesPK primary key (EspecialidadID, MedicoID), constraint MedicosEspecialidades_EspFK foreign key (EspecialidadID) references Especialidades(EspecialidadID), constraint MedicosEspecialidades_MedFK foreign key (MedicoID) references Medicos(MedicoID), );

go

 

alter table Pacientes add constraint ProvinciaDEF Default 'Alicante' for Provincia;

go

--b. El Teléfono tiene que empezar por 9.

alter table Pacientes add constraint TelefonoCheck Check (Telefono like '9%'); go

--4. Crear las siguientes restricciones en la tabla Ingresos:--a. El campo cama solo puede tener o ‘P’ o ‘V’.

 alter table Ingresos add constraint CamaCheck Check (Cama in ('P', 'V'));

go

--b. El valor por defecto del campo FechaIngreso es la fecha y hora actual. alter table Ingresos add constraint FechaIngresoDef Default getDate() for FechaIngreso;

Go

--5. Insertar datos en la tabla Pacientes.

insert into Pacientes( PacienteID, Nombre, Apellidos, CodigoPostal, Provincia, Telefono, FechaNacimiento ) values (100,'José','Romero','28935','Murcia','912563256','12/03/1975'), (101,'Juan','Romero','28935','Murcia','912563256','12/03/1975'), (102,'Carmen','Gómez','28936','Alicante','9125631110','12/09/2012'), (103,'María','Romero','28935','Murcia','912563222','15/03/1985'), (110,'Alberto','Saboya','28935','Murcia','912563333','12/08/1975'), (120,'Jaime','Perles','28936','Alicante','913563444','17/03/1975'), (130,'José','Romero','28935','Alicante','913563555','12/03/1995'), (131,'Ana','Martin','28935','Murcia','913563666','17/06/1975'), (140,'Carlos','Romero','28936','Alicante','912563256','12/03/1995'), (141,'Pedro','Perles','28938','Alicante','912563777','11/05/1993'), (150,'José','Alamar','28935','Alicante','913563256','12/03/1975');

go--6. Insertar datos en la tabla Médicos.

insert into Especialidades(Especialidad) values ('Pediatría'), ('Psiquiatría'), ('General'), ('Intensivos'), ('Cirugía'),('Ginecología');

go

insert into Medicos( MedicoID, Nombre, Apellidos, Telefono) values (1,'Antonio', 'Gómez','653998877'), (2,'Carmen','Sánchez','666999885'), (3,'Eva','Moreno','666584523'), (4,'Juana','Romero','633998877'), (5,'Olga','Pardos','633948877'), (6,'Santiago','Gómez','654798877');

 go

insert into MedicosEspecialidades( MedicoID, EspecialidadID) values (1,1), (2,2),(3,1),(3,3),(4,1), (4,4), (5,3), (5,5), (6,3), (6,6);

go

--7.

insert into Ingresos(PacienteID, Habitacion,Cama, FechaIngreso) values (100, 101, 'P','12/06/2010 16:48:00'), (102, 111,'P','12/06/2010 18:55:00'), (110, 105, 'P','19/06/2010 12:00:00'), (130, 105, 'V','19/06/2010 20:00:00'), (100, 110, 'P','31/12/2010 20:48:00'), (140, 106, 'P','31/12/2010 22:48:00'), (102, 101, 'P','12/06/2011 16:48:00'), (100, 103, 'P','15/06/2011 16:48:00'), (150,101,'P','19/06/2011 16:48:00');

go

--8. Sacar un listado de los médicos que tienen más de una especialidad.

select m.Nombre, m.Apellidos, COUNT(me.EspecialidadID ) as NumEspec from Medicos m inner join MedicosEspecialidades me on m.MedicoID = me.MedicoID group by m.Nombre, m.Apellidos having COUNT(me.EspecialidadID ) > 1

 go

--9. Mostar el nombre del paciente con más ingresos en el año 2010

select top 1 p.Nombre, p.Apellidos, COUNT(i.NumIngreso) as NumIngresos from Pacientes p inner join Ingresos i on p.PacienteID = i.PacienteID where DATEPART(year, i.fechaingreso) = 2010 group by p.Nombre, p.Apellidos order by NumIngresos desc

 go

--10. Mostrar los pacientes que no han tenido ingresos.

select * from Pacientes p where p.PacienteID not in ( select distinct PacienteID from Ingresos)

 go

--11. Mostrar los pacientes que han estado en la habitación 101.

select distinct p.PacienteID, p.Nombre, p.Apellidos from Pacientes p inner join Ingresos i on p.PacienteID = i.PacienteID where i.Habitacion = 101

go

--12 Crear lo necesaria para almacenar esta información.

create table Visitas( NumVisita int not null identity(1,1) primary key, PacienteID int not null, MedicoID int not null, Fecha date not null, Diagnostico varchar(50) not null, Tratamiento varchar(100) not null, constraint Visitas_PacintesFK foreign key (PacienteID) references pacientes(PacienteId),

constraint Visitas_MedicosFK foreign key (MedicoID) references Medicos(MedicoID));

go

insert into Visitas(PacienteID,MedicoID, Fecha , Diagnostico,Tratamiento) values (100,2,'12/06/2010','Posible Loco','Ingresar para seguimiento'), (100,5,'15/06/2010','Heridas','Tratar con antibióticos'), (102,1,'19/06/2010','Gastroenteritis','Tratar antibióticos y suero'), (131,6,'20/06/2010','Infección','Tratar antibióticos y revisión 1 mes'), (150,2,'22/06/2010','Depresión','Tratar con antidepresivos'), (131,6,'12/07/2010','revisión','Alta');

go

--13. Mostrar un listado de los pacientes de Psiquiatría,

 --incluyendo la fecha y el diagnostico.

select p.Nombre , p.Apellidos, v.Fecha, v.Diagnostico from Pacientes p inner join Visitas v on p.PacienteID = v.PacienteID inner join Medicos m on m.MedicoID = v.MedicoID inner join MedicosEspecialidades me on m.MedicoID = me.MedicoID inner join Especialidades e on e.EspecialidadID = me.EspecialidadID where e.Especialidad = 'Psiquiatría'

 

--14. Mostrar los pacientes ingresados en junio de 2011 y

--sus tratamientos, ordenados por la fecha de nacimiento y la fecha del

--tratamiento.

select p.Nombre, p.Apellidos,v.Tratamiento from Pacientes p inner join Ingresos i on p.PacienteID = i.PacienteID inner join Visitas v on v.PacienteID = i.PacienteID where i.FechaIngreso >= '01/06/2011' and i.FechaIngreso <'01/07/2011' order by p.FechaNacimiento, v.Fecha

--15. Atender a Pedro Perles en medicina general --por el doctor de la especialidad que tenga menos visitas.

insert into Visitas(PacienteID,MedicoID, Fecha , Diagnostico,Tratamiento) select top 1

(select MAX( PacienteID) from Pacientes where Nombre='Pedro' and Apellidos='Perles' ) as PID

, m.MedicoID, GETDATE(), 'agotamiento', 'reposo absoluto' from Medicos m left join Visitas v on m.MedicoID = v.MedicoID inner join MedicosEspecialidades me on m.MedicoID = me.MedicoID inner join Especialidades e on e.EspecialidadID=me.EspecialidadID where e.Especialidad = 'General' group by m.MedicoID order by COUNT(v.MedicoID)

 

select * from Visita