SEC S20W2 || Databases and SQL language - Part 2

Greetings Steemit friends

Exercise 1

Alter: Outlier
Update, Select, Insert: Data Query and Manipulation Language

Desc: Outlier
Max, Sum, Avg: Query Functions

Count: Outlier
Between, In, Like: Query Restrictions selector

Distinct: Outlier
Primary key, Foreign key, Unique: Unique field

---

Exercise 1

Affirmations	True/False
In SQL, it is not possible to delete a table that contains tuples	(False)
A DBMS ensures data redundancy.	(False)
The Data Definition Language (DDL) allows adding integrity constraints to a table.	(True)
A primary key in one table can be a primary key in another table.	(False)
In SQL, the ORDER BY clause is used to sort selected columns of a table.	(False)
A foreign key column can contain NULL values.	(False)
The PRIMARY KEY constraint includes both UNIQUE and NULL constraints.	(False)
Referential integrity constraints ensure links between tables in a database.	(True)

---

---

Exercise 3

Given the following tables of books and members:

CREATE TABLE Books (
id INT AUTO_INCREMENT PRIMARY KEY,
title varchar(255),
author varchar(255),
publisher varchar(255),
pages INT,
Year INT,
borrower INT,
return_Date Date
);

CREATE TABLE members (
id INT AUTO_INCREMENT PRIMARY KEY,
last_name varchar(255),
first_name varchar(255),
email varchar(255)
);

1. Fill in the table below by providing the result returned or the query to obtain the result: (1 point)

SELECT COUNT(pages) AS counts,
SUM(pages) AS total_pages
FROM books;

SELECT id, title, return_date FROM books
ORDER BY return_date ASC;

SELECT * FROM books WHERE Year IN (SELECT Year FROM books WHERE Id=2) AND Id<>2

For each of the following propositions, validate each answer by marking the box with V for true or F for false. (1.5 points)

a) By executing the SQL query: UPDATE books SET Title = "Title1"; the DBMS:
□ (F) Modifies the Title field of the first record in the books table to Title1.
□ (F) Modifies the Title field of all records in the books table to Title1.
□ (V) Displays an error message due to the absence of the WHERE clause.

b) The SQL query:
SELECT Title FROM books WHERE MONTH(Return_Date) in (5,6) AND YEAR(Return_Date) = 2014; is equivalent to:
□ (V) SELECT Title FROM books WHERE Return_Date between "2014-05-01" AND "2014-06-30";
□ (F)SELECT Title FROM books WHERE Return_Date >= "2014-05-01" OR Return_Date <= "2014-06-30";
□ (V)SELECT Title FROM books WHERE Return_Date between "2014-05-01" OR "2014-06-30";

c) By executing the SQL query: SELECT Author, count(*) FROM books GROUP BY Title; the DBMS:
□ (F) Displays the number of authors per title.
□ (F) Displays the number of books per author.
□ (V) Does not work.

d) By executing the SQL query:
DELETE FROM books WHERE Pages = Null; the DBMS:
□ (F) Deletes the Pages column.
□ (F) Deletes the rows where the page count is not provided.
□ (V) Does not work.

Exercise 3: (5.5 points)

CREATE TABLE patient (
idPatient varchar(11) PRIMARY KEY,
last_name varchar(255),
first_name varchar(255),
age INT,
city varchar(255)
);

CREATE TABLE analysis (
idAnalysis varchar(11) PRIMARY KEY,
name varchar(255),
price decimal,
min_value decimal,
max_value decimal
);

CREATE TABLE doctor (
idDoctor varchar(11) PRIMARY KEY,
last_name varchar(255),
city varchar(255),
specialty varchar(255)
);

CREATE TABLE Report (
idReport INT AUTO_INCREMENT PRIMARY KEY,
idPatient varchar(11),
idDoctor varchar(11),
date date,
FOREIGN KEY (idPatient) REFERENCES patient (idPatient),
FOREIGN KEY (idDoctor) REFERENCES doctor (idDoctor)
);

CREATE TABLE Result_Report (
idResult INT AUTO_INCREMENT PRIMARY KEY,
idReport INT,
idAnalysis varchar(11),
value varchar(255),
status char(1),
FOREIGN KEY (idReport) REFERENCES report (idReport),
FOREIGN KEY (idAnalysis) REFERENCES analysis (idAnalysis)
);

Write the SQL queries to:

Before going ahead to write the SQL queries, I will add dummy data for test purposes. This will help me have accurate results of my query.

• Determine the IDs, last names, and first names of patients who have undergone 'Cholesterol' analyses, sorted in ascending order by last names and first names.

SELECT patient.idPatient, patient.last_name, patient.first_name, analysis.idAnalysis, analysis.name, report.idReport, report.idPatient, result_report.idReport, result_report.idAnalysis
FROM patient
JOIN report ON patient.idPatient = report.idPatient
JOIN result_report ON report.idReport = result_report.idReport
JOIN analysis ON result_report.idAnalysis = analysis.idAnalysis
WHERE analysis.name = 'Cholesterol'
ORDER BY patient.last_name ASC, patient.first_name ASC;

To determine and sort the details of those patients who have undergone "Cholesterol". We will have to query four tables, starting from the patient, analyses, report, and result_report. I will try to explain the query, which im not good at.

So I started by selecting the necessary columns needed to collect data. To be specific, I added the table name to each column to understand to which table a column belongs.

Since we can't directly get the patients who have undergone "Cholesterol". We need to find the relationship between the different tables to get the results we require. Every patient is uniquely linked to a report, and every report is uniquely linked to a result_report, and the result_report is uniquely linked to an analysis. So, I used the join statement when working with multiple tables.

Our condition here is to get patients who have undergone "Cholesterol". The final join gives me the possibility to use the condition WHERE clause.

Finally, the ORDER BY clause to order by last names and first names in an ascending manner.

• Determine the names of patients who have undergone analyses prescribed by the doctor with ID 'DR2015' and who are not from his/her city.

SELECT DISTINCT patient.idPatient, patient.last_name, patient.first_name
FROM patient
JOIN report ON report.idPatient = patient.idPatient
JOIN result_report ON result_report.idReport = report.idReport
JOIN analysis ON analysis.idAnalysis = result_report.idAnalysis
JOIN doctor ON report.idDoctor = doctor.idDoctor
WHERE report .idDoctor = 'DR2015'
AND patient.city != doctor.city;

Here primary data to query is the names of patients analyzed by a doctor with ID 'DR2015'. So we need to access the patient table for the names, and for us to get information from the doctor table. I used the join for the report and patient to be able to get the doctor's ID.

To get the analysis undergone by the doctor with ID 'DR2015, I need the data from the result_report table with respect to the report table. From the result_report table, I will be able to get the corresponding idAnalysis for the doctor with ID 'DR2015'.

• Determine the date when the patient with ID 'PA161' had their last analysis report.

SELECT MAX(report.date) AS last_analysis_date
FROM patient
JOIN report ON report.idPatient = patient.idPatient
WHERE patient.idPatient = 'PA161';

Each analysis is inserted after a report has been generated. The report holds more than one entry for a single patient with a different date register. Using the MAX(report.date) select the last or most recent report for the patient with ID 'PA161'.

• Retrieve all information related to the analysis results of the patient with ID 'PA170' performed on March 12, 2018

SELECT patient.idPatient, patient.last_name, patient.first_name, patient .city, report.idReport, report.date,
result_report.idResult, result_report.idAnalysis, result_report.idReport, result_report.value, result_report.status, analysis.idAnalysis, analysis.name, analysis.price, analysis.min_value, analysis.max_value
FROM patient
JOIN report ON patient.idPatient = report.idPatient
JOIN result_report ON report.idReport = result_report.idReport
JOIN analysis ON result_report.idAnalysis = analysis .idAnalysis
WHERE patient.idPatient = 'PA170'
AND report.date = '2018-03-12';

• Update the status of analysis results to 'L' for the analyses with IDs 'AnChol12' and 'AnGlug15' for the report with ID 2020.

UPDATE result_report
SET status = 'L'
WHERE idReport = 2020
AND idAnalysis IN ('AnChol12', 'AnGlug15');

Our result is zero because the only report with ID 2020 available result_report status was L.

• Find the report IDs and patient IDs with at least two abnormal analysis results per report.

SELECT report.idReport, report.idPatient
FROM report
JOIN result_report ON result_report.idReport = report.idReport
WHERE result_report.status != 'N'
GROUP BY report.idReport, report.idPatient
HAVING COUNT(result_report.idAnalysis) >= 2;

As required for this course, the first query gave me an empty set when checking for at least two abnormal analyses. So to be sure about my query, I change to at least one abnormal analysis.

Here we are querying result_report.status equal to either L or H.

• Count the number of reports per doctor living in the city of Sousse.

SELECT doctor.idDoctor, COUNT(report.idReport) AS report_count
FROM report
JOIN doctor ON report.idDoctor = doctor.idDoctor
WHERE doctor.city = 'Sousse'
GROUP BY doctor.idDoctor;

• Retrieve the IDs, last names, first names, and cities of patients aged between 20 and 40 years who have had more than five analyses after May 26, 2015.

SELECT patient.idPatient, patient.last_name, patient.first_name, patient.age, patient.city
FROM patient
JOIN report ON report.idPatient = patient.idPatient
JOIN result_report ON report.idReport = result_report.idReport
JOIN analysis ON result_report.idAnalysis = analysis.idAnalysis
WHERE patient.age BETWEEN 20 AND 40
AND report.date > '2015-05-26'
GROUP BY patient.idPatient
HAVING COUNT(result_report.idAnalysis) > 5;

• Delete analyses with no name.

DELETE FROM analysis
WHERE name IS NULL OR name = ' ';

The query above will delete any row where the column name is empty (' ') or NULL.

Note: I used the MySQL console for the WAMP server in writing my queries and snipping tool for screenshots

---

---

Cheers
Thanks for dropping by
@fombae