SEC S20W2 || Databases and SQL language - Part 2

Assalam O Alaikum

Hello dear friends! Welcome to my post. How are you all? I hope you will be doing well by the grace of Almighty Allah. I'm also fine and enjoying my day. Today I'm here to participate in this Steemit engagement challenge season 20 week 2 organized by @kouba01 and @starrchris. The topic of this challenge is about "Databases and SQL language - Part 2". So let's start without any more delay of time.

---

_{Created With Canva}

---

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.

Element 1	Element 2	Element 3	Element 4
Update	Select	Alter	Insert
Max	Desc	Sum	Avg
Between	In	Count	Like
Primary Key	Foreign Key	Unique	Distinct

---

First Row: Update, select, alter and insert

Outlier: The outlier element is Alter.

Common Point: In the first row, the remaining elements like update, select and insert are data manipulation language commands (DML). These commands are used to modify the existing data, while the alter is data definition language (DDL) which is used to modify the structures and tables present in a database.

---

Second Row: Max, desc, Sum and Avg.

Outlier: The outlier element is Desc.

Common Point: In that row, the remaining three elements max, sum and avg are the functions in SQL which are used to perform various calculations. Due to that reason, they are aggregator functions but the desc isn't an aggregator function because it is used to sort results in different orders especially in descending order.

---

Third Row: Between, in, count and like.

Outlier: The outlier element is count.

Common Point: The remaining elements, between, in and like are act as operates in SQL language to change or filter the data and queries based on different conditions. Where the count acts as an aggregator function.

---

Fourth Row: Primary key, foreign key, unique and distinct.

Outlier: The outlier is distinct.

Common Point: In the fourth Row, the elements like primary key, foreign key and unique are used to enforce data integrity and in that way, they act as constraints in SQL queries. While the distinct is used to remove duplicate rows from the queries.

---

B) Answer TRUE or FALSE to the following statements:

Affirmations	True/False
1. In SQL, it is not possible to delete a table that contains tuples.	FALSE, because it is possible to delete a table that contains tuples. All the data present in that table will be deleted.
2. A DBMS ensures data redundancy.	FALSE, this is because, the DBMS can't ensure the data redundancy, in fact it reduces the data redundancy through different techniques.
3. The Data Definition Language (DDL) allows adding integrity constraints to a table.	TRUE, because the DDL add integrity to the table through the constraints like primary key, foreign key and check etc.
4. A primary key in one table can be a primary key in another table.	FALSE, because the primary key in one table can't be a primary key in another table but it can be used a foreign key in another table.
5. In SQL, the ORDER BY clause is used to sort selected columns of a table.	TRUE, because in SQL, the ORDER BY Clause can be used to sort selected columns of a table.
6. A foreign key column can contain NULL values.	TRUE, this is because, the foreign key can accept null values.
7. The PRIMARY KEY constraint includes both UNIQUE and NULL constraints.	FALSE, because the PRIMARY KEY accept only null constraints and can't accept not null constraints.
8. Referential integrity constraints ensure links between tables in a database.	TRUE, because the referential integrity constraints ensure links between the different tables on a database.

---

Exercise 02: The Given Tables Are:

---

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

---

Question: Fill in the table below by providing the result returned or the query to obtain the result:

Query: 01

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

Explanation:

• count(Pages) counts the number of non-NULL values in the Pages column.

• sum(Pages) computes the sum of the non-NULL values in the Pages column.
  For the given data:

Pages values: 636, 1662, NULL, 223, 1276
Count = 4 (books with non-NULL Pages)
Total Pages = 636 + 1662 + 223 + 1276 = 3797

Result:

Count :4
Total Pages : 3797

---

Query: 2

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

Explanation:

• Subquery SELECT Year FROM books WHERE Id=2 yields the year 1862.

• The outer query selects all books from the year 1862 except the book with Id=2.

• For the given data, books from the year 1862 are:

Les Misérables (Id=2)
House of the Dead (Id=5)
Excluding Id=2, the remaining book is: House of the Dead

Result:

I'd	5
Title	House of The dead
Author	Fyodor Dostoevsky
Publisher	Mikhail
Pages	1276
Year	1862
Borrower	2
Return Date	2014-05-13

---

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

a) By executing the SQL query: UPDATE books SET Title = "Title1";**The DBMS:

• □ 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. (F)

Explanation: The query updates the Title field for all rows in the books table, as no WHERE clause is specified.

---

b) The SQL query:

SELECT Title FROM books WHERE MONTH``(Return_Date) in (5,6) AND YEAR``(Return_Date) = 2014;

• ✔ SELECT Title FROM books WHERE Return_Date BETWEEN "2014-05-01" AND "2014-06-30"; (V)

• □ 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"; (F)

Explanation: The query selects titles where the Return_Date falls in May or June of 2014. The equivalent date range covers these months.

---

c) The SQL query:

SELECT Author, count(*)FROMbooksGROUPBY Title;`

• □ Displays the number of authors per title. (F)
• □ Displays the number of books per author. (F)
• ✔ Does not work. (V)

Explanation: Grouping by Title while selecting Author and count(*) will result in an error or unexpected result, as it doesn't group by Author.

---

d)The SQL query:

DELETE FROM books WHERE Pages =``Null;

• □ Deletes the Pages column. (F)
• Deletes the rows where the page count is not provided. (F)
• □ Does not work. (V)

Explanation: The query DELETE FROM books WHERE Pages = Null; will not work because NULL should be checked using IS NULL. The correct query should be DELETE FROM books WHERE Pages IS NULL;.

---

Exercise 3: Consider the medical laboratory database "analysis" defined by the following simplified textual representation:

1. 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 DISTINCT p.idPatient, p.lastname, p.firstname
FROM``PATIENT p
JOIN REPORT r ON p.idPatient = r.``idPatient
JOIN RESULTREPORT rr ON r.idReport = rr.idReport
JOIN ANALYSIS a ON rr.idAnalysis = a.idAnalysis
WHERE a.name = 'Cholesterol'
ORDER BY p.lastname ASC, p.first_name ASC;

2. 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 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;

---

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

SELECT MAX(r.date) AS lastreportdate
FROM REPORT r
WHERE``r.idPatient ='PA161';

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

SELECT rr.*, a.name AS analysisname, a.price, a.minvalue, a.maxvalue
FROM RESULTREPORT rr
JOIN REPORT r ON rr.idReport = r.idReport
JOIN ANALYSIS a ON rr.idAnalysis = a.idAnalysis
WHERE r.idPatient = 'PA170'
AND r.date = '2018-03-12';

5. 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');

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

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(*) >= 2;

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

SELECT d.idDoctor, COUNT``(r.idReport)``AS report_count
FROM REPORT r
JOIN DOCTOR d``ON r.idDoctor = d.idDoctor``WHERE d.city = 'Sousse'
GROUP BY d.idDoctor;

8. 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 p.idPatient, p.last_name, p.first_name, p.city
FROM PATIENT p
JOIN REPORT r ON p.idPatient = r.idPatient
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``(r.idReport) > 5;

9. Delete analyses with no name:

DELETE FROM ANALYSIS
WHERE name IS NULL OR TRIM(name) = '';

So friends, that was my entry about that challenge and I hope you will enjoy reading it. Now it is a time to say goodbye to everyone and I want to invite my friends @josepha, @arinaz08 and @ahsansharif to take a part in this amazing challenge.

---

Written by @abdullahw2

---

---