SQL Database Schema: Doctors, Patients, and Care
SQL Database Schema for Medical Records
Creating Tables
SQL code to create the Medico
(Doctor), Paciente
(Patient), and Atencion
(Care) tables:
CREATE TABLE Medico (
rut VARCHAR2(20) NOT NULL,
nombre VARCHAR2(90) NOT NULL,
apellidos VARCHAR2(80) NOT NULL,
email VARCHAR2(70),
sueldo NUMBER(20) NOT NULL,
CONSTRAINT medico_pk PRIMARY KEY(rut)
);
CREATE TABLE Paciente (
id NUMBER(20) NOT NULL,
rut VARCHAR2(15) NOT NULL,
nombre_completo VARCHAR2(200) NOT NULL,
fecha_nacimiento DATE NOT NULL,
prevision VARCHAR2(50) NOT NULL,
CONSTRAINT paciente_pk PRIMARY KEY(id, rut)
);
CREATE TABLE Atencion (
codigo NUMBER(30) NOT NULL,
fecha_atencion DATE NOT NULL,
valor_bono NUMBER(20) NOT NULL,
medico_rut VARCHAR2(20) NOT NULL,
paciente_id NUMBER(20) NOT NULL,
paciente_rut VARCHAR2(15) NOT NULL,
CONSTRAINT atencion_pk PRIMARY KEY(codigo),
CONSTRAINT medico_fk FOREIGN KEY(medico_rut) REFERENCES Medico(rut),
CONSTRAINT paciente_fk FOREIGN KEY(paciente_id, paciente_rut) REFERENCES Paciente(id, rut)
);
Altering Tables
SQL code to alter the tables:
ALTER TABLE Medico ADD (especialidad VARCHAR2(60));
ALTER TABLE Paciente MODIFY (prevision NULL);
ALTER TABLE Paciente ADD (sexo VARCHAR2(20));
ALTER TABLE Atencion MODIFY (CONSTRAINT valor_bono_check CHECK (valor_bono > 500));
ALTER TABLE Medico RENAME COLUMN email TO correo_electronico;
ALTER TABLE Medico RENAME TO Doctor;
ALTER TABLE Paciente ADD (comuna VARCHAR2(30));
ALTER TABLE Doctor DROP COLUMN correo_electronico;
ALTER TABLE Atencion ADD (telefono_paciente NUMBER NOT NULL);
Dropping Tables
SQL code to drop the tables:
DROP TABLE Paciente CASCADE CONSTRAINT;
DROP TABLE Atencion CASCADE CONSTRAINT;
DROP TABLE Doctor CASCADE CONSTRAINT;
Inserting Data
SQL code to insert data into the tables:
INSERT INTO Medico (rut, nombre, apellidos, email, sueldo) VALUES ('123456-7', 'juan eduardo', 'leal rojas', 'a.leal@gmail.com', 2345000);
INSERT INTO Medico (rut, nombre, apellidos, email, sueldo) VALUES ('444555-7', 'maria rosa', 'contreras', NULL, 3126000);
CREATE SEQUENCE id_paciente
MINVALUE 1
MAXVALUE 99999999999999
START WITH 1
INCREMENT BY 1;
INSERT INTO Paciente (id, rut, nombre_completo, fecha_nacimiento, prevision) VALUES (id_paciente.nextval, '3456699-8', 'cristian cofre roa', TO_DATE('13/05/1975', 'DD/MM/YYYY'), 'cruz blanca');
INSERT INTO Paciente (id, rut, nombre_completo, fecha_nacimiento, prevision) VALUES (id_paciente.nextval, '1087698-3', 'isabel peralta lillo', TO_DATE('11/09/1945', 'DD/MM/YYYY'), 'fonasa');
INSERT INTO Paciente (id, rut, nombre_completo, fecha_nacimiento, prevision) VALUES (id_paciente.nextval, '1198777-4', 'monica ulloa torres', TO_DATE('21/11/1952', 'DD/MM/YYYY'), 'fonasa');
INSERT INTO Atencion (codigo, fecha_atencion, valor_bono, medico_rut, paciente_id, paciente_rut) VALUES ('300', TO_DATE('17/08/2010', 'DD/MM/YYYY'), 5000, '123456-7', '2', '3456699-8');
INSERT INTO Atencion (codigo, fecha_atencion, valor_bono, medico_rut, paciente_id, paciente_rut) VALUES ('430', TO_DATE('22/08/2010', 'DD/MM/YYYY'), 4100, '123456-7', '4', '1198777-4');
INSERT INTO Atencion (codigo, fecha_atencion, valor_bono, medico_rut, paciente_id, paciente_rut) VALUES ('435', TO_DATE('27/08/2010', 'DD/MM/YYYY'), 5000, '123456-7', '2', '3456699-8');
Querying Data
SQL code to query the data:
SELECT * FROM Medico;
SELECT nombre, apellidos FROM Medico WHERE sueldo >= 3000000;
SELECT nombre_completo FROM Paciente WHERE prevision = 'fonasa';
SELECT nombre, apellidos, email FROM Medico WHERE rut = '444555-7';
SELECT COUNT(rut) AS cantidad_pacientes FROM Paciente;
SELECT nombre_completo, fecha_nacimiento FROM Paciente WHERE id = 2 AND rut = '3456699-8';
Updating Data
SQL code to update the data:
UPDATE Atencion SET valor_bono = 7200 WHERE fecha_atencion = TO_DATE('27/08/2010', 'DD/MM/YYYY');
More Queries
More SQL queries:
SELECT medico_rut, paciente_rut FROM Atencion WHERE valor_bono BETWEEN 1000 AND 6000;
SELECT DISTINCT prevision FROM Paciente;
SELECT nombre_completo, rut FROM Paciente;
SELECT apellidos, rut FROM Medico WHERE nombre LIKE 'm%';
SELECT id, rut, nombre_completo FROM Paciente WHERE prevision = 'fonasa' ORDER BY nombre_completo ASC;
SELECT nombre, apellidos FROM Medico WHERE email IS NULL;
SELECT paciente_rut FROM Atencion WHERE valor_bono = 6000 OR valor_bono = 4100;
SELECT SUM(valor_bono) AS suma_bono, paciente_rut FROM Atencion GROUP BY paciente_rut;
SELECT AVG(sueldo) AS promedio_sueldo FROM Medico;
SELECT MIN(valor_bono) AS minimo_bono, MAX(valor_bono) AS maximo_bono FROM Atencion;
Updating and Deleting Data
SQL code to update and delete data:
UPDATE Medico SET sueldo = 2678000 WHERE rut = '123456-7';
DELETE FROM Atencion WHERE fecha_atencion = TO_DATE('17/08/2010', 'DD/MM/YYYY');
Creating a View
SQL code to create a view:
CREATE VIEW promedio_sueldo AS SELECT AVG(sueldo) AS promedio_sueldo FROM Medico;
SELECT * FROM promedio_sueldo;