StudyCorgi Tech & Engineering
Print Сite this

SQL Statements and Queries

Words: 1495 Pages: 6

SQL Statements

Statements for Proposed Database

Below are the statements for the database (DB) designed for substance management on hospital grounds.

— Student Name

— Course Number

— Date

— This is my own work.

.open HospitalDB

.header on

.mode column

CREATE TABLE groups

(

gr_id INT NOT NULL,

gr_name CHAR(24) NOT NULL,

gr_descript VARCHAR(255) NOT NULL,

PRIMARY KEY (gr_id)

);

CREATE TABLE department

(

dept_id INT NOT NULL,

dept_name VARCHAR(24) NOT NULL,

dept_head VARCHAR(24) NOT NULL,

PRIMARY KEY (dept_id)

);

CREATE TABLE substance

(

sub_id INT NOT NULL,

sub_name VARCHAR(24) NOT NULL,

sub_mng VARCHAR(255) NOT NULL,

sub_therind VARCHAR(255) NOT NULL,

sub_ddi VARCHAR(255) NOT NULL,

sub_adm VARCHAR(255) NOT NULL,

sub_cntind VARCHAR(255) NOT NULL,

sub_overd VARCHAR(255) NOT NULL,

sub_storeq VARCHAR(255) NOT NULL,

gr_id INT NOT NULL,

PRIMARY KEY (sub_id),

FOREIGN KEY (gr_id) REFERENCES groups(gr_id)

);

CREATE TABLE employee

(

emp_id INT NOT NULL,

emp_fname VARCHAR(24) NOT NULL,

emp_lname VARCHAR(24) NOT NULL,

emp_midinit CHAR(1) NOT NULL,

dept_id INT NOT NULL,

PRIMARY KEY (emp_id),

FOREIGN KEY (dept_id) REFERENCES department(dept_id)

);

CREATE TABLE storage

(

sto_id INT NOT NULL,

sto_date DATE NOT NULL,

sto_usedate DATE NOT NULL,

sto_util yesno,

sub_id INT NOT NULL,

emp_id INT,

PRIMARY KEY (sto_id),

FOREIGN KEY (sub_id) REFERENCES substance(sub_id),

FOREIGN KEY (emp_id) REFERENCES epmloyee(emp_id)

);

Below are the statements for DB population with fictional data with screenshots of SELECT * FROM queries.

Groups table.
Figure 1. Groups table.

INSERT INTO groups ( gr_id, gr_name, gr_descript) VALUES ( ‘1’, ‘Antidepressants’, ‘Help to manage stress’ ) ;

INSERT INTO groups ( gr_id, gr_name, gr_descript) VALUES ( ‘2’, ‘Antibiotics’, ‘Kill bacteria’ ) ;

INSERT INTO groups ( gr_id, gr_name, gr_descript) VALUES ( ‘3’, ‘Antiallergy’, ‘Help to manage allergies’ ) ;

INSERT INTO groups ( gr_id, gr_name, gr_descript) VALUES ( ‘4’, ‘Vitamins’, ‘Improve vitamins counts’ ) ;

Department table.
Figure 2. Department table.

INSERT INTO department (dept_id, dept_name, dept_head) VALUES (‘1’, ‘Surgery’, ‘John Brown’ ) ;

INSERT INTO department (dept_id, dept_name, dept_head) VALUES (‘2’, ‘Nursery’, ‘Peter Black’ ) ;

INSERT INTO department (dept_id, dept_name, dept_head) VALUES (‘3’, ‘Pharmacy’, ‘Helen Red’ ) ;

INSERT INTO department (dept_id, dept_name, dept_head) VALUES (‘4’, ‘Therapy’, ‘Paul Yellow’ ) ;

Substance table.
Figure 3. Substance table.

INSERT INTO substance (sub_id, sub_name, sub_mng, sub_therind, sub_ddi, sub_adm, sub_cntind, sub_overd, sub_storeq, gr_id) VALUES (‘1’, ‘Antieviline’, ‘Manage with caution’, ‘Being evil’, ‘Antigoodine’, ‘Orally, twice a day, 1 tablet’, ‘Being good’, ‘Becomes too good. Stop treatment’, ‘6 months, temp between 0 and 5 degrees Celsius’, ‘1’ ) ;

INSERT INTO substance (sub_id, sub_name, sub_mng, sub_therind, sub_ddi, sub_adm, sub_cntind, sub_overd, sub_storeq, gr_id) VALUES (‘2’, ‘Antigoodine’, ‘Manage with caution’, ‘Being good’, ‘Antieviline’, ‘Orally, twice a day, 1 tablet’, ‘Being evil’, ‘Becomes too evil. Stop treatment’, ‘6 months, temp between 0 and 5 degrees Celsius’, ‘1’ ) ;

INSERT INTO substance (sub_id, sub_name, sub_mng, sub_therind, sub_ddi, sub_adm, sub_cntind, sub_overd, sub_storeq, gr_id) VALUES (‘3’, ‘Antisadine’, ‘Manage with caution’, ‘Being sad’, ‘Antihappyine’, ‘Orally, once a day, 1 tablet’, ‘Being happy’, ‘Becomes too happy. Stop treatment’, ‘6 months, temp between 0 and 5 degrees Celsius’, ‘1’ ) ;

INSERT INTO substance (sub_id, sub_name, sub_mng, sub_therind, sub_ddi, sub_adm, sub_cntind, sub_overd, sub_storeq, gr_id) VALUES (‘4’, ‘Antihappyine’, ‘Manage with caution’, ‘Being happy’, ‘Antisadine’, ‘Orally, once a day, 1 tablet’, ‘Being sad’, ‘Becomes too sad. Stop treatment’, ‘6 months, temp between 0 and 5 degrees Celsius’, ‘1’ ) ;

Employee table.
Figure 4. Employee table.

INSERT INTO employee (emp_id, emp_fname, emp_lname, emp_midinit, dept_id) VALUES (‘1’, ‘Harry’, ‘Potter’, ‘A’, ‘4’) ;

INSERT INTO employee (emp_id, emp_fname, emp_lname, emp_midinit, dept_id) VALUES (‘2’, ‘Hermione’, ‘Granger’, ‘B’, ‘3’) ;

INSERT INTO employee (emp_id, emp_fname, emp_lname, emp_midinit, dept_id) VALUES (‘3’, ‘Ron’, ‘Weasley’, ‘C’, ‘2’) ;

INSERT INTO employee (emp_id, emp_fname, emp_lname, emp_midinit, dept_id) VALUES (‘4’, ‘Tom’, ‘Riddle’, ‘D’, ‘1’) ;

Storage table.
Figure 5. Storage table.

INSERT INTO storage (sto_id, sto_date, sto_usedate, sto_util, sub_id, emp_id) VALUES (‘1’, ‘10-02-2019’, ’10-08-2019’, ‘yes’, ‘1’, ‘1’ ) ;

INSERT INTO storage (sto_id, sto_date, sto_usedate, sto_util, sub_id, emp_id) VALUES (‘2’, ‘10-02-2019’, ’10-08-2019’, ‘yes’, ‘2’, ‘1’ ) ;

INSERT INTO storage (sto_id, sto_date, sto_usedate, sto_util, sub_id, emp_id) VALUES (‘3’, ‘10-02-2019’, ’10-08-2019’, ‘yes’, ‘3’, ‘1’ ) ;

INSERT INTO storage (sto_id, sto_date, sto_usedate, sto_util, sub_id, emp_id) VALUES (‘4’, ‘10-02-2019’, ’10-08-2019’, ‘yes’, ‘4’, ‘2’ ) ;

Addition of Deleted Date Attribute

Over time, there may appear a need to delete rows of data from a DB for various reasons. The primary concern is not removing a row and using a soft deletion instead is safety. There is always a possibility of human error and data can be deleted by accident. Another advantage of using a marker column is preserving historical data for future analysis (Coronel & Morris, 2019). However, there are also tradeoffs for using this method of deletion, which includes privacy concerns (Ben-Assuli, 2015). Moreover, a DB using soft removal can become too sizeable, and there may appear problems with storage. In short, while there are clear benefits of using a marker-column for deletion, as opposed to deleting a row from a table, a DB administrator should also be aware of the tradeoffs of the method.

As the DB described above is not designed for soft deletion, there is a need to introduce a new attribute to the tables where data is most likely to be deleted. I chose to modify the employee table to acknowledge employee turnover. Additionally, I decided to change the substance table, as new drugs may appear on the market. I added emp_deletedate and sub_deletedate attributes respectively to the two entities. Below are the SQL queries for the procedure with a screenshot of .schema command.

ALTER TABLE substance ADD COLUMN sub_deletedate date ;

Altered substance table.
Figure 6. Altered substance table.

ALTER TABLE employee ADD COLUMN emp_deletedate date ;

Altered employee table.
Figure 7. Altered employee table.

SQL Queries

Database Schema

Proposed Database Schema.
Figure 8. Proposed Database Schema.

SQL Statements

— Student Name

— Course Number

— Date

— This is my own work.

.open Assignment2

.header on

.mode column

CREATE TABLE Patient

(

PatientID INT NOT NULL,

Name VARCHAR(50) NOT NULL,

DOB DATE NOT NULL,

PRIMARY KEY (PatientID)

);

CREATE TABLE Doctor

(

DoctorID INT NOT NULL,

Name VARCHAR(50) NOT NULL,

MedLicenseNumber INT NOT NULL,

Phone VARCHAR(20) NOT NULL,

PRIMARY KEY (DoctorID)

);

CREATE TABLE Medication

(

MedicationID INT NOT NULL,

BrandName VARCHAR(24) NOT NULL,

GenericName VARCHAR(24) NOT NULL,

PRIMARY KEY (MedicationID)

);

CREATE TABLE Prescription

(

PrescriptionID INT NOT NULL,

Date DATE NOT NULL,

MedicationID INT NOT NULL,

PatientID INT NOT NULL,

DoctorID INT NOT NULL,

FOREIGN KEY (MedicationID) REFERENCES Medication(MedicationID),

FOREIGN KEY (PatientID) REFERENCES PatientID(PatientID),

FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)

);

DB Population

INSERT INTO Patient (PatientID, Name, DOB) VALUES (‘1’, ‘Severus Snape’, date(‘1965-02-20’) ) ;

INSERT INTO Patient (PatientID, Name, DOB) VALUES (‘2’, ‘Albus Dumbledore’, date(‘1901-01-01’ ) ) ;

INSERT INTO Patient (PatientID, Name, DOB) VALUES (‘3’, ‘Dobby Elf’, date(‘1995-03-07’) ) ;

INSERT INTO Patient (PatientID, Name, DOB) VALUES (‘4’, ‘Albus-Severus Potter’, date(‘2010-03-12’) ) ;

Patient table.
Figure 8. Patient table.

INSERT INTO Doctor (DoctorID, Name, MedLicenseNumber, Phone) VALUES (‘1’, ‘Harry Potter’, ‘1234567’, ‘7707894561’ ) ;

INSERT INTO Doctor (DoctorID, Name, MedLicenseNumber, Phone) VALUES (‘2’, ‘Hermione Granger’, ‘7654321’, ‘7701234567’ ) ;

INSERT INTO Doctor (DoctorID, Name, MedLicenseNumber, Phone) VALUES (‘3’, ‘Ron Weasley’, ‘2345678’, ‘7707654321’ ) ;

INSERT INTO Doctor (DoctorID, Name, MedLicenseNumber, Phone) VALUES (‘4’, ‘Tom Riddle’, ‘3456789’, ‘7702345678’ ) ;

Doctor table.
Figure 8. Doctor table.

INSERT INTO Medication (MedicationID, BrandName, GenericName) VALUES (‘1’, ‘Antieviline’, ‘Goodness’ ) ;

INSERT INTO Medication (MedicationID, BrandName, GenericName) VALUES (‘2’, ‘Antigoodine’, ‘Evil’ ) ;

INSERT INTO Medication (MedicationID, BrandName, GenericName) VALUES (‘3’, ‘Antisadine’, ‘Happiness’ ) ;

INSERT INTO Medication (MedicationID, BrandName, GenericName) VALUES (‘4’, ‘Antihappine’, ‘Sadness’ ) ;

Medication table.
Figure 9. Medication table.

INSERT INTO Prescription (PrescriptionID, Date, MedicationID, PatientID, DoctorID) VALUES (‘1’, ‘2019-02-15’, ‘1’, ‘1’, ‘2’ ) ;

INSERT INTO Prescription (PrescriptionID, Date, MedicationID, PatientID, DoctorID) VALUES (‘2’, ‘2019-02-16’, ‘1’, ‘2’, ‘3’ ) ;

INSERT INTO Prescription (PrescriptionID, Date, MedicationID, PatientID, DoctorID) VALUES (‘3’, ‘2019-02-17’, ‘3’, ‘3’, ‘4’ ) ;

INSERT INTO Prescription (PrescriptionID, Date, MedicationID, PatientID, DoctorID) VALUES (‘4’, ‘2019-02-16’, ‘3’, ‘4’, ‘2’ ) ;

Prescription table.
Figure 10. Prescription table.

SQL Queries

Below is the query to retrieve a list of all doctors:

SELECT Name FROM Doctor ;

A list of all doctors.
Figure 10. A list of all doctors.

Below is the query to retrieve the number of patients who were born before 01/01/1994.

SELECT count (DOB)

FROM Patient

WHERE DOB > ‘1994-01-01’ ;

Count before date.
Figure 11. Count before date.

Below is the query to retrieve the patient information along with the ID of any prescribing doctor for each patient.

SELECT Name, DOB, DoctorID FROM Patient, Prescription

WHERE Patient.PatientID=Prescription.PatientID ;

Select from multiple tables.
Figure 12. Select from multiple tables.

Below is the query to retrieve the prescription information along with the patient name, DOB, medication brand name, and prescribing doctor name sorted by most recent date.

SELECT PrescriptionID, Date, Prescription.MedicationID, Prescription.PatientID, Prescription.DoctorID, Patient.Name, DOB, BrandName, Doctor.Name

FROM Prescription, Patient, Medication, Doctor

WHERE Patient.PatientID=Prescription.PatientID

AND Medication.MedicationID=Prescription.MedicationID

AND Prescription.DoctorID=Doctor.DoctorID

ORDER BY Date DESC ;

Select from multiple tables.
Figure 13. Select from multiple tables. Part 2.

Below is the query to retrieve the most prescribed generic medication name.

SELECT GenericName,

COUNT(Prescription.MedicationID) AS value_occurrence

FROM Medication, Prescription

WHERE Medication.MedicationID=Prescription.MedicationID

GROUP BY Prescription.MedicationID

ORDER BY value_occurrence DESC

LIMIT 1;

Most Prescribed Medication.
Figure 14. Most Prescribed Medication.

References

Ben-Assuli, O. (2015). Electronic health records, adoption, quality of care, legal and privacy issues, and their implementation in emergency departments. Health Policy, 119(3), 287-297. Web.

Coronel, C., & Morris, S. (2019). Database systems: Design, implementation, and management. (13th ed.). Web.

Cite this paper

Select style

Reference

StudyCorgi. (2021, April 23). SQL Statements and Queries. https://studycorgi.com/sql-statements-and-queries/

Work Cited

"SQL Statements and Queries." StudyCorgi, 23 Apr. 2021, studycorgi.com/sql-statements-and-queries/.

* Hyperlink the URL after pasting it to your document

References

StudyCorgi. (2021) 'SQL Statements and Queries'. 23 April.

1. StudyCorgi. "SQL Statements and Queries." April 23, 2021. https://studycorgi.com/sql-statements-and-queries/.


Bibliography


StudyCorgi. "SQL Statements and Queries." April 23, 2021. https://studycorgi.com/sql-statements-and-queries/.

References

StudyCorgi. 2021. "SQL Statements and Queries." April 23, 2021. https://studycorgi.com/sql-statements-and-queries/.

Powered by StudyCorgi's free citation machine.
Copy to clipboard

This paper, “SQL Statements and Queries”, was written and voluntary submitted to our free essay database by a straight-A student. Please ensure you properly reference the paper if you're using it to write your assignment.

Before publication, the StudyCorgi editorial team proofread and checked the paper to make sure it meets the highest standards in terms of grammar, punctuation, style, fact accuracy, copyright issues, and inclusive language. Last updated: .

If you are the author of this paper and no longer wish to have it published on StudyCorgi, request the removal. Please use the “Donate your paper” form to submit an essay.