Greetings to my all STEEMIT members. Hopefully you al are fine and enjoying your best life on STEEMIT. I am also fine ALHAMDULILLAH. And by the grace of GOD , today I am going to part in this homework task. No doubt that it is little bit difficult . But I try my best to full fill all tasks

• Reasons:

1. In SQL, it is not possible to delete a table that contains tuples

Ans: FALSE

It is possible to delete a table that contains tuples using the DROP TABLE command. However, if there are foreign key constraints referencing that table, you may need to address those constraints first.

2. A DBMS ensures data redundancy

Ans: FALSE

Reason
A DBMS (Database Management System) is designed to reduce data redundancy, not ensure it. By using normalization and other techniques, a DBMS aims to organize data efficiently and eliminate unnecessary duplication.

3. The Data Definition Language (DDL) allows adding integrity constraints to a table

.Ans: TRUE

Reason:

The Data Definition Language (DDL) allows adding integrity constraints, such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK, to a table when defining or modifying its structure using commands like CREATE TABLE or ALTER TABLE

4. A primary key in one table can be a primary key in another table

Ans: FALSE.

Reason:
A primary key in one table cannot be a primary key in another table because the primary key uniquely identifies rows within its own table. However, the primary key of one table can be used as a foreign key in another table to establish relationships between the two tables.

5. In SQL, the ORDER BY clause is used to sort selected columns of a table.

Ans: TRUE

Reason:
In SQL, the ORDER BY clause is used to sort the result set of a query based on one or more columns, either in ascending (ASC) or descending (DESC) order.

6. A foreign key column can contain NULL values.

Ans: TRUE

Reason:
A foreign key column can contain NULL values, as long as the column is not part of a PRIMARY KEY or marked with the NOT NULL constraint. NULL in a foreign key indicates that the row does not have a related row in the referenced table.

7. The PRIMARY KEY constraint includes both UNIQUE and NULL constraints.
Ans: FALSE

Reason
The PRIMARY KEY constraint enforces UNIQUE values and automatically implies a NOT NULL constraint, meaning it does not allow NULL values. Therefore, a primary key must have unique, non-null values in a table.

8. Referential integrity constraints ensure links between tables in a database

Ans: TRUE

Reason:
Referential integrity constraints ensure that the relationships between tables in a database are maintained. They do this by enforcing rules, such as foreign key constraints, to ensure that a value in a foreign key column corresponds to a valid primary key value in the related table.

Exercise 02

Given the following tables of books and members:

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

Query 1:

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

• Explanation:

count(Pages) will return the number of books that have non-null entries in the Pages field.
sum(Pages) will calculate the total number of pages for all books where the page count is available.
The Pages column has values: 636, 1662, NULL, 223, 1276.
**Count will ignore the NULL, so it counts four entries (Id 1, 2, 4, and 5).**
Sum of the Pages will be
636+1662+223+1276=3797

Thus, the result would be:

Count	Total Pages
4	3797

• Query 2:

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

Explanation:
The inner query SELECT Year FROM books WHERE Id=2 returns the year of the book with Id = 2, which is 1862.
The outer query selects all books where the Year is 1862, excluding the book with Id = 2 itself.
From the table, the book with Id = 5 ("House of the Dead") also has the year 1862. Therefore, this is the only book that matches the condition.

The result for this query would be:

Id	Title	Author	Publisher	Pages	Year	Borrower	Return_Date
5	House of the Dead	Fyodor Dostoevsky	Mikhail	1276	1862	2	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.
□ Modifies the Title field of all records in the books table to Title1.
□ Displays an error message due to the absence of the WHERE clause.

Here’s the validation for each of the propositions:**

• Modifies the Title field of the first record in the books table to Title1

False
The query will not only modify the first record because there is no WHERE clause. Without any condition, it updates all records.

• Modifies the Title field of all records in the books table to Title1

True
Since the query lacks a WHERE clause, it will update the Title field for every record in the books table to "Title1".

• Displays an error message due to the absence of the WHERE clause

False
The query will not display an error due to the missing WHERE clause. The query is valid and will run successfully, affecting all rows.

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

Ans: Now, let's analyze each alternative:**

* **SELECT Title FROM books WHERE Return_Date between "2014-05-01" AND "2014-06-30";**

True
This query correctly selects records with return dates between May 1, 2014, and June 30, 2014. It captures the same date range as the original query (all of May and June 2014), so it is equivalent.

• SELECT Title FROM books WHERE Return_Date >= "2014-05-01" OR Return_Date <= "2014-06-30";

False

The OR operator means that the query will select books where the return date is either on or after May 1, 2014, or on or before June 30, 2014. This would include all dates in 2014 (or beyond), not just those between May and June, so this is not equivalent.

* **SELECT Title FROM books WHERE Return_Date between "2014-05-01" OR "2014-06-30";**

False
The OR used in this syntax is invalid for a BETWEEN clause. A correct BETWEEN query requires two dates with AND, not OR, so this would result in a syntax error.

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

**Ans: SELECT Author, count(*) FROM books GROUP BY Title;**

• Explanation:

The query attempts to count the number of rows (books) for each Title in the books table, but it groups by Title, not Author.
This means that for each distinct Title, it will return one row showing the Author and the count of how many times that title appears.

Now, let's evaluate the propositions:

• Displays the number of authors per title

False

The query doesn't display the number of authors per title. It counts how many records (books) share the same title, but it doesn't group by Author.

• Displays the number of books per author

False
The query groups by Title, not Author, so it does not display the number of books per author.

• Does not work

True
This query would result in an error because Author is not part of the GROUP BY clause. In SQL, any selected column that is not an aggregate function (like COUNT) must be in the GROUP BY clause, so the query would fail.

d) By executing the SQL query: DELETE FROM books WHERE Pages = Null; the DBMS:

□ Deletes the Pages column.
□ Deletes the rows where the page count is not provided.
□ Does not work.

Ans: DELETE FROM books WHERE Pages = Null;

Explanation:

In SQL, NULL is treated differently. To check for NULL values, you need to use IS NULL, not =. The query, as written, tries to delete rows where Pages is equal to NULL, which won't work because NULL cannot be compared using the equality operator.
Now, let's evaluate the propositions:

• Deletes the Pages column.

False
The query doesn’t delete the Pages column; it is trying to delete rows, not columns.

• Deletes the rows where the page count is not provided

False
The query would not delete rows where Pages is not provided because = NULL does not work in SQL. To achieve this, you would need to use WHERE Pages IS NULL.

• **Does not work.

True
The query does not work because the condition Pages = Null is incorrect. The correct condition is Pages IS NULL.

Exercise 3: (5.5 points)

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

Write the SQL queries to:

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 patients.Id, patients.Last_Name, patients.First_Name
FROM patients
JOIN analyses ON patients.Id = analyses.Patient_Id
WHERE analyses.Test_Name = 'Cholesterol'
ORDER BY patients.Last_Name ASC, patients.First_Name ASC;

Explanation:
FROM patients: We start by selecting from the patients table.
JOIN analyses ON patients.Id = analyses.Patient_Id: This joins the patients and analyses tables on the Id column in patients and the Patient_Id column in analyses. This assumes that each analysis is associated with a specific patient.
WHERE analyses.Test_Name = 'Cholesterol': We filter the records to include only those analyses where the Test_Name is 'Cholesterol'.
ORDER BY patients.Last_Name ASC, patients.First_Name ASC: Finally, we order the results in ascending order by last name and then first name.

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 patients.First_Name, patients.Last_Name
FROM patients
JOIN analyses ON patients.Id = analyses.Patient_Id
JOIN doctors ON analyses.Doctor_Id = doctors.Id
WHERE doctors.Id = 'DR2015'
AND patients.City <> doctors.City;

Explanation:
FROM patients: We start by selecting from the patients table, where patient details are stored.
JOIN analyses ON patients.Id = analyses.Patient_Id: This joins the patients and analyses tables on the patient ID to find which analyses the patient has undergone.
JOIN doctors ON analyses.Doctor_Id = doctors.Id: We join the analyses table with the doctors table to get details about the doctor who prescribed the analyses.
WHERE doctors.Id = 'DR2015': We filter the records to include only those where the analyses were prescribed by the doctor with ID 'DR2015'.
AND patients.City <> doctors.City: We further filter the results to include only those patients who do not reside in the same city as the doctor.

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

SELECT MAX(analysis_date) AS Last_Analysis_Date
FROM analyses
WHERE Patient_Id = 'PA161';

Explanation:
MAX(analysis_date): This function returns the latest (most recent) date of analysis for the specified patient.
FROM analyses: The query retrieves the information from the analyses table.
WHERE Patient_Id = 'PA161': This filters the results to only include analyses related to 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 *
FROM analyses
WHERE Patient_Id = 'PA170'
AND analysis_date = '2018-03-12';

Explanation:
SELECT *: This retrieves all columns (information) from the analyses table.
FROM analyses: The query is targeting the analyses table where all analysis-related data is stored.
WHERE Patient_Id = 'PA170': This filters the analyses to only include those performed for the patient with ID 'PA170'.
AND analysis_date = '2018-03-12': This ensures that the query retrieves only the analyses performed 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

UPDATE analyses
SET status = 'L'
WHERE analysis_id IN ('AnChol12', 'AnGlug15')
AND report_id = 2020;

Explanation:
UPDATE analyses: This specifies that you want to update the analyses table.
SET status = 'L': This sets the status field to 'L'.
WHERE analysis_id IN ('AnChol12', 'AnGlug15'): This filters the records to include only those analyses with the specified IDs.
AND report_id = 2020: This further filters the results to ensure the update only affects analyses associated with report ID 2020.

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

SELECT report_id, patient_id
FROM analyses
WHERE result_status = 'abnormal'
GROUP BY report_id, patient_id
HAVING COUNT(*) >= 2;

Explainaition
SELECT report_id, patient_id: This retrieves the report IDs and patient IDs from the analyses.
FROM analyses: The query targets the analyses table where the analysis results are recorded.
WHERE result_status = 'abnormal': This filters the records to include only those analyses that are classified as "abnormal."
GROUP BY report_id, patient_id: This groups the results by report ID and patient ID, so you can count the number of abnormal results for each patient per report.
HAVING COUNT(*) >= 2: This condition ensures that only groups with at least two abnormal results are included in the final results.

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

SELECT doctors.id AS Doctor_ID, COUNT(reports.id) AS Report_Count
FROM doctors
LEFT JOIN reports ON doctors.id = reports.doctor_id
WHERE doctors.city = 'Sousse'
GROUP BY doctors.id;

Explaination

SELECT doctors.id AS Doctor_ID, COUNT(reports.id) AS Report_Count: This selects the doctor's ID and counts the number of reports associated with each doctor.
FROM doctors: The query starts from the doctors table.
LEFT JOIN reports ON doctors.id = reports.doctor_id: This joins the reports table on the doctor ID, ensuring that even doctors without reports are included in the count (they will show a count of 0).
WHERE doctors.city = 'Sousse': This filters the results to include only doctors who live in the city of Sousse.
GROUP BY doctors.id: This groups the results by doctor ID, so the count is calculated per doctor.

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 patients.Id, patients.Last_Name, patients.First_Name, patients.City
FROM patients
JOIN analyses ON patients.Id = analyses.Patient_Id
WHERE patients.Age BETWEEN 20 AND 40
AND analyses.analysis_date > '2015-05-26'
GROUP BY patients.Id, patients.Last_Name, patients.First_Name, patients.City
HAVING COUNT(analyses.Id) > 5;

Explaination

SELECT patients.Id, patients.Last_Name, patients.First_Name, patients.City: This specifies the columns to retrieve from the patients table.
FROM patients: The query starts from the patients table.
JOIN analyses ON patients.Id = analyses.Patient_Id: This joins the analyses table to get the analysis records for each patient.
WHERE patients.Age BETWEEN 20 AND 40: This filters patients to include only those whose age is between 20 and 40 years.
AND analyses.analysis_date > '2015-05-26': This further filters to include only analyses conducted after May 26, 2015.
GROUP BY patients.Id, patients.Last_Name, patients.First_Name, patients.City: This groups the results by patient details to allow counting.
HAVING COUNT(analyses.Id) > 5: This condition ensures that only patients with more than five analyses after the specified date are included.

Delete analyses with no name.

DELETE FROM analyses
WHERE analysis_name IS NULL OR analysis_name = '';

Explanation:
DELETE FROM analyses: This specifies that you want to delete records from the analyses table.
WHERE analysis_name IS NULL OR analysis_name = '': This condition checks for entries where the analysis_name is either NULL or an empty string (''), ensuring that only analyses without a name are deleted.

Special thanks to to : kouba01

Invite friends: @dexyluz , @katherine12 , @alee75

Regards:@arinaz08