SEC S20W2 || Databases and SQL language - Part 2

Greetings dear friends,
It's another week of the Steemit engagement challenge and I'm delighted to share my participation in this week's contest which talks about Databases and SQL language - Part 2. I wasn't able to participate in week 1 of this topic based on the engagement I'm having offline.

Source

I'm glad that I'm making it this time as this is one of the topics I like the most. As a computer scientist, I try as much as I can to learn more and also put into practice all I learn here. Without further delay please go straight into the topic of discussion by attempting the questions one after the other.

Exercise 1:

(A) For each row presented in the table below, extract the outlier element and provide a brief description of the common point between the remaining three elements.

Row 01:

From the screenshot above, the elements included in the first row are Update, Select, Alter, and Insert.

Outlier: Alter

Reason:

The reason for my answer is simple. As you can see, Update, Select, and Insert are all commands used in the Data Manipulation Language (DML) and the purpose of DML is to modify and retrieve data from the database. The Alter on the other hand is a command used in the Data Definition Language (DDL) and as you already know the purpose of DDL is to modify table structures in the database.

Common Point:

The common point between the remaining 3 which are the Update, Select, and Insert is that they are all used in data manipulation and they belong to the DML category.

Row 02:

Based on the above screenshot, the elements included in the second row are Max, Desc, Sum, and Avg.

Outlier: Desc

Reason:

When performing calculations Max, Sum, and Avg functions are used to get new values but the Desc is only used when sorting data from a given group of data. The function is a keyword that helps to arrange any data in a descending order.

Common Point:

The common point between the remaining 3 which are Max, Sum, and Avg is that they are used for arithmetic calculation.

Row 03:

Based on the above screenshot, the elements included in the third row are Between, In, Count, and Like.

Outlier: Count

Reason:

The reason here is that Between, In, and Like are all conditional operators used to filter queries in the database whereas the count function as the name implies is used to count the number of rows in a database.

Common Point:

The common point between the remaining 3 which are Between, In, and Like is that they are all conditional operators which obey certain conditions for their operations when they are queried in the database.

Row 04:

Based on the above screenshot, the elements included in the fourth row are the Primary key, Foreign key, Unique, and Distinct.

Outlier: Distinct

Reason:

The reason here is that the Primary key, Foreign key, and Unique are all constraints that help to emphasize the type of data and the relationship between the data you are to enter in the database. Distinct on the other hand is used to eliminate duplicates.

Common Point:

The common point between the remaining 3 which the Primary key, Foreign key, and Unique is the relationship that exist between the data types entered.

B) Answer TRUE or FALSE to the following statements: (1 point)

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

Exercise 2:

Data present in this exercise can be categorised in table 1 & 2 as seen below. Table 1 which is the Books details and Table 2 is the members details.

Source

Table: 1 Books	Table: 2 Members
Books Column	Members Column
Title, Author, Publisher Pages, Year, Borrower, and Return_Date	Last Name, First Name, and Email

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

---

Query
SELECT count(Pages) "Count", sum(Pages) "Total Pages" FROM books

Result

From the query, the target is to get the number of pages in the books and we have 5 books out of which one of the books doesn't have pages so we will result to calculating for just 4 books and the syntax should be in the format below.

"Notre-dame de Paris" has 636 pages.
"Les Misérables" has 1662 pages.
"Around the World in 80 Days" has 223 pages.
"House of the Dead" has 1276 pages.
"Journey to the Center of the Earth" does not have a value for Pages (likely NULL).

Count: 4 Books
Total Pages: 636 + 1662 + 223 + 1276 = 3797

Count Total Pages: 4 3797

Query
SELECT Id, Title, Return_Date FROM books WHERE Return_Date IS NOT NULL ORDER BY Return_Date ASC;

Result

What this query will do is to retrieve from the books table the Id, Title, and Return_Date where the Return_Date is made available (i.e., not NULL). This will result in the output showing books with their Id, Title, and Return_Date

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

Result

Id=2

From the initial data table given for this task, the year of the book where Id=2 has a title Les Misérables so the query is to retrieve the year of the book which is 1862.

Id<>2

The sign means that WHERE exp1 <> exp2: True if expressions exp1 and exp2 are different. So having this in the query we can conclude that query wants the Id=2 which is Les Misérables to be excluded.

The focus is to look for other books that are published in 1862 which doesn't have the Id=2 in the table and from my search, we have one book which is House of the Dead and it's Id=5

This query should return the information below to the person who query the database.
Id: 5, Title: House of the Dead, Year: 1862

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.
□ (V) Modifies the Title field of all records in the books table to Title1.
□ (F) 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";
□ (F)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

Source

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.

---

SQL Query

SELECT DISTINCT P.idPatient, P.last_name, P.first_name
FROM PATIENT P
JOIN REPORT R ON P.idPatient = R.idPatient
JOIN RESULT_REPORT RR ON R.idReport = RR.idReport
JOIN ANALYSIS A ON RR.idAnalysis = A.idAnalysis
WHERE A.name = 'Cholesterol'
ORDER BY P.last_name ASC, P.first_name ASC;

Explanation
PATIENT, REPORT, RESULT_REPORTand ANALYSIS table are joined together here to get a patient information who has done Cholesterol analysis. The DISTINCT function ensures that there are no repetition of data selected in one table which also present in another.

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

---

SQL Query

SELECT DISTINCT P.first_name, P.last_name
FROM PATIENT P
JOIN REPORT R ON P.idPatient = R.idPatient
JOIN DOCTOR D ON R.idDoctor = D.idDoctor
WHERE D.idDoctor = 'DR2015'
AND P.city <> D.city;

Explanation

This query finds the names of patients who had analyses prescribed by the doctor with the ID 'DR2015' and are not from the same city as the doctor. It joins the PATIENT, REPORT, and DOCTOR tables. The WHERE clause ensures the doctor's ID matches 'DR2015' and the patient's city is different from the doctor's city.

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

---

SQL Query

SELECT MAX(R.date) AS last_report_date
FROM REPORT R
WHERE R.idPatient = 'PA161';

Explanation
Checking through the report table to know when the patient with ID 'PA161' carried out their last analysis report.

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

---

SQL Query
SELECT * FROM RESULT_REPORT RR
JOIN REPORT R ON RR.idReport = R.idReport
WHERE R.idPatient = 'PA170'
AND R.date = '2018-03-12';

Explanation
The syntax join two tables RESULT_REPORT and REPORT to fetch out data of patient analysis with ID 'PA170' which was carried out on March 12, 2018.

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

---

SQL Query

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

Explanation
This query is meant to go into the RESULT_REPORT table and update the status of a patient with IDs 'AnChol12' and 'AnGlug15' to 'L' and these users ID on report table is 2020

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

---

SQL Query

SELECT RR.idReport, R.idPatient
FROM RESULT_REPORT RR
JOIN REPORT R ON RR.idReport = R.idReport
WHERE RR.status IN ('H', 'L')
GROUP BY RR.idReport, R.idPatient
HAVING COUNT(RR.status) >= 2;

Explanation
The query joins two tables i.e the RESULT_REPORT and REPORT to look for abnormal analysis result and the HAVING COUNT ensures that only results with abnormal analysis are taken into consideration.

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.

---

SQL Query

SELECT P.idPatient, P.last_name, P.first_name, P.city
FROM PATIENT P
JOIN REPORT R ON P.idPatient = R.idPatient
JOIN RESULT_REPORT RR ON R.idReport = RR.idReport
WHERE P.age BETWEEN 20 AND 40
AND R.date > '2015-05-26'
GROUP BY P.idPatient, P.last_name, P.first_name, P.city
HAVING COUNT(RR.idAnalysis) > 5;

Explanation
The above syntax retrieves from the patient table the IDs, names, and cities of patients aged between 20 and 40 who have undergone more than five analyses after May 26, 2015. It get information from three tables i.e PATIENT, REPORT, and RESULT_REPORT and it uses the COUNT to ensure only those who have above 5 analyses after the stipulated date are captured.

Delete analyses with no name.

---

SQL Query

DELETE FROM ANALYSIS
WHERE name IS NULL;

Explanation
The above syntax is meant to delete from the ANALYSIS table where name is NULL.

I want to finally invite @solaymann, @ripon0630, and @suboohi to also join the contest in this community today.

Cc: @kouba01