_{Made in Canva}

Hope everyone is doing well and enjoying their lives. Today is our second week of learning SQL language. And as our teachers have explained very well here understanding has come to us with great ease. And now they have given us some tasks so it's time to complete those tasks. I am performing these tasks as per my understanding as per the understanding from here. Hope I live up to those expectations.

Row 01:

Elements:
The elements included in the first row are Update, Select, Alter, and Insert.

Outlier: Alter

Reason:

Update, Select, and Insert are DML which means data manipulation language commands that are used to modify or retrieve data. But the Alter is a DDL which means data definition language commands that are used to modify the structures of tables that are given in the database.

Common Point:
The common point is DML commands. DML commands include Update, Select, and Insert which are used to manipulate data.

Row 02:

Elements:
The elements included in the second row are Max, Desc, Sum, and Avg.

Outlier: Desc

Reason:
Max, Sum, and Avg functions are used for calculations except for the Desc order which is a keyword that sorts our results in descending order.

Common Point:
The common point is aggregate functions that include Max, Sum, and Avg which are used for calculation.

Row 03:

Elements:
The elements included in the third row are Between, In, Count, and Like.

Outlier: Count

Reason:
Between, In, and Like these are operators that are used to filter queries. And count is a function that is an aggregate function that is used to count rows.

Common Point:
The common point is conditional operators when the queries are filtered.

Row 04:

Elements:
The elements included in the fourth row are the Primary key, Foreign key, Unique, and Distinct.

Outlier: Distinct

Reason:
Primary key, Foreign key, and Unique are constraints applied to columns to enforce rules on data integrity. Distinct is used to eliminate duplicate results in a query.

Common Point:
The common point is Data Integrity Constraints that ensure the data relationship.

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

Answer: False

Exp: In SQL you can drop a table either in table rows or by using the drop table command which removes the table and its data even if it was created using it. If you just want to delete the data and you want to keep the table then you can use the delete command to keep the table and delete the data.

2. A DBMS ensures data redundancy.

Answer: False

Exp: The main purpose of a database management system is to normalize the data and give an efficient arrangement to the data and it ensures elimination of unnecessary duplicates and reduces redundancy.

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

Answer: True

Exp: DDL in SQL stands for Data Definition Language which includes commands like CREATE ALTER and DROP. These commands completely define the structure of your database objects and you can also modify it.

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

Answer: False

Exp: A primary key can never be duplicated, it is a unique identifier for each record. Yes, even the primary key you can use as a foreign key in another table.

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

Answer: True

Exp: ORDER BY is used in SQL to sort one or more columns in any query result. Either in ASC or DESC order.

6. A foreign key column can contain NULL values.

Answer: True

Exp: Another table in a foreign key has a reference to the primary key. It usually contains nulls because the foreign key does not need a corresponding entry in each row.

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

Answer: False

Exp: A primary key constraint enforces uniqueness but never allows nulls. Any primary key must have an un-nulled value in the column next to it.

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

Answer: True

Exp: A foreign key corresponds to a primary key in another table. In this way, the relationship between tables is maintained. Referential integrity is enforced by Foreign Key constraints.

(01) The provided query is given below:

SELECT COUNT(Pages) "Count", SUM(Pages) "Total Pages" FROM books;

This query will give us two values, one of which will provide us with the page count, which will give us the page count of the total books listed. Second, it will count the total number of books.

Expected Result of this query

From the provided data in the previous table, books with Pages information:

"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).
So, we have 4 books with page information.

Count: 04
Total Pages: 636 + 1662 + 223 + 1276 = 3797.

Count Total Pages
4 3797

(02) The provided query is given below:

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

Expected Result of this query

SELECT Year FROM books WHERE Id=2:
Retrieves the Year of the book with Id = 2 (which is "Les Misérables"). The year of this book is 1862.

Id<>2:
Ensures that the query excludes the book with Id = 2 (i.e., "Les Misérables").

So, the main query looks for books that were published in the year 1862 but are not the book with Id = 2.

From the previous table, the only other book published in 1862 is "House of the Dead" with Id = 5.

Id: 5, Title: House of the Dead, Year: 1862

(03) The result provided and the query find:

The result table shows specific books and their return dates. We need to extract the Titleand Return Date of certain books.

Here is the query:

SELECT Id, Title, Return_Date FROM books WHERE Return_Date IS NOT NULL;

Exp: The query retrieves the Id, Title, and Return_Date from the books table where the Return_Date is provided (i.e., not NULL). This will result in the output showing books with a return date.

(04) The result provided and the query find:

The result table shows the number of books borrowed by each member. We need to count the number of books borrowed by each member, linking the Borrower field from the Books table to the Members table.

SELECT CONCAT(FirstName, ' ', LastName) AS "Last and First Name", COUNT(*) AS "Nombre"
FROM members
JOIN books ON members.Id = books.Borrower
GROUP BY members.Id;

Exp: This query uses a JOIN to combine the Members table and the Books table on the Id field, which is common in both tables. It then groups the results by members.Id and counts the number of books borrowed by each member. The CONCAT function is used to combine the first name and last name of each member in the output.

Let's go through each SQL query one by one to validate the statements.

a) SQL query: UPDATE books SET Title = "Title1";
This query modifies the Title field in the books table without a WHERE clause, which affects all records.

• □ Modifies the Title field of the first record in the books table to Title1. → False
  Without a WHERE clause, it doesn't target the first record specifically.
• □ Modifies the Title field of all records in the books table to Title1. → True
  Without a WHERE clause, it updates every record.
• □ Displays an error message due to the absence of the WHERE clause. → False
  The absence of the WHERE clause doesn’t cause an error; it just updates all records.

b) 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"; → True
  This statement correctly matches the original query by including all dates between May 1 and June 30, 2014.
• □ SELECT Title FROM books WHERE Return_Date >= "2014-05-01" OR Return_Date <= "2014-06-30"; → False
  The use of OR is incorrect here because it would select dates outside the range.
• □ SELECT Title FROM books WHERE Return_Date between "2014-05-01" OR "2014-06-30"; → False
  This is syntactically incorrect because OR cannot be used like this in a BETWEEN clause.

c) SQL query:
SELECT Author, count(*) FROM books GROUP BY Title;

• □ Displays the number of authors per title. → False
  The query does not group by author, so it doesn’t display the number of authors.
• □ Displays the number of books per author. → False
  It doesn’t group by author, so it won’t show the count of books per author.
• □ Does not work. → True
  The query would cause an error because it groups by Title but selects Author without it being part of an aggregate function or the GROUP BY clause.

d) SQL query:
DELETE FROM books WHERE Pages = Null;

• □ Deletes the Pages column. → False
  The DELETE query does not delete columns; it deletes rows.
• □ Deletes the rows where the page count is not provided. → False
  The condition Pages = NULL will not work as NULL values need to be checked using IS NULL.
• □ Does not work. → True
  The query does not work because Pages = NULL is incorrect; the correct form is Pages IS NULL.

Final Validation:

• a) Second option: True
• b) First option: True
• c) Third option: True
• d) Third option: True

Pixabay

(01): 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

This query retrieves the distinct IDs, last names, and first names of patients who have undergone a "Cholesterol" analysis. It joins the PATIENT, REPORT, RESULT_REPORT, and ANALYSIS tables. The WHERE clause filters results to only include patients who had a "Cholesterol" analysis, and the ORDER BY clause sorts the results alphabetically by last and first names.

(02): 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.

(03): 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

The query retrieves the date of the last analysis report for the patient with ID 'PA161'. It selects the maximum date from the REPORT table where the patient ID matches 'PA161'. The MAX function ensures the latest date is returned, giving us the most recent report for this patient.

(04): 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

This query retrieves all information about analysis results for the patient with ID 'PA170' on March 12, 2018. It joins the RESULT_REPORT and REPORT tables and filters results by the patient's ID and the specific date of the analysis. The * ensures that all columns are selected in the final result.

(05): 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 updates the status of analysis results to 'L' (low) for two specific analysis IDs ('AnChol12' and 'AnGlug15') for the report with ID 2020. The UPDATE statement modifies the status field in the RESULT_REPORT table, and the WHERE clause ensures that only results matching the specific report and analysis IDs are updated.

(06): 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

This query identifies reports with at least two abnormal analysis results (either high 'H' or low 'L'). It joins the RESULT_REPORT and REPORT tables and filters by reports with abnormal statuses. The GROUP BY groups the data by report and patient ID, and the HAVING COUNT ensures only reports with two or more abnormal results are returned.

(07): Count the number of reports per doctor living in the city of Sousse.

SQL Query

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

Explanation

This query counts the number of reports per doctor who live in the city of Sousse. It joins the DOCTOR and REPORT tables and filters by doctors whose city is 'Sousse'. The COUNT function counts how many reports each doctor has, and the GROUP BY groups the results by doctor ID.

(08): 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

This query retrieves the IDs, names, and cities of patients aged between 20 and 40 who have undergone more than five analyses after May 26, 2015. It joins the PATIENT, REPORT, and RESULT_REPORT tables and filters patients by age and analysis date. The HAVING COUNT ensures that only patients with more than five analyses are included in the results.

(09): Delete analyses with no name.

SQL Query

DELETE FROM ANALYSIS
WHERE name IS NULL;

Explanation

This query deletes analyses from the ANALYSIS table that have no name. The DELETE statement removes rows where the name field is NULL. This is useful for cleaning up incomplete data in the database.

This is my explanation of today's task, hope you guys understand. If there is something wrong somewhere, there is a defect, then you guys can fix it, because being a human being, one makes mistakes. And now I am learning this SQL language for the first time. So I can't do anything wrong so I apologize to you for this mistake. You can correct my mistake. Thank you all so much for stopping by. See you in the next task, then I want your permission, Allah Hafiz.

Finally, I want to invite my friends @rumaisha, @muhammad-ahmad, @josepha, and @bossj23 to join this challenge task.

_{Dated: 16-09-2024 About Me}