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;