Greetings you all, hope you all are well and enjoying a happy moment of life with steem. I'm also good Alhamdulillah. |
|---|
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.
Exercise 01 |
|---|
(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. |
|---|
| Element 1 | Element 2 | Element 3 | Element 4 | Outlier | Common Point |
|---|---|---|---|---|---|
| Update | Select | Alter | Insert | ||
| Max | Desc | Sum | Avg | ||
| Between | In | Count | Like | ||
| Primary key | Foreign key | Unique | Distinct |
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.
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.
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.
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.
(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. | |
| 2. A DBMS ensures data redundancy. | |
| 3. The Data Definition Language (DDL) allows adding integrity constraints to a table. | |
| 4. A primary key in one table can be a primary key in another table. | |
| 5. In SQL, the ORDER BY clause is used to sort selected columns of a table. | |
| 6. A foreign key column can contain NULL values. | |
| 7. The PRIMARY KEY constraint includes both UNIQUE and NULL constraints. | |
| 8. Referential integrity constraints ensure links between tables in a database. |
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.
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.
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.
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.
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.
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.
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.
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.
Exercise 02 |
|---|
| Table: 01 Books | Table: 02 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: |
|---|
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
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
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.
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.
For each of the following propositions, validate each answer by marking the box with V for true or F for false. |
|---|
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.
WHERE clause, it doesn't target the first record specifically.WHERE clause, it updates every record.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"; → TrueSELECT Title FROM books WHERE Return_Date >= "2014-05-01" OR Return_Date <= "2014-06-30"; → FalseOR 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"; → FalseOR cannot be used like this in a BETWEEN clause.c) SQL query:
SELECT Author, count(*) FROM books GROUP BY Title;
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;
DELETE query does not delete columns; it deletes rows.Pages = NULL will not work as NULL values need to be checked using IS NULL.Pages = NULL is incorrect; the correct form is Pages IS NULL.Exercise 03 (Write the SQL queries) |
|---|
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;
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.
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;
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.
SELECT MAX(r.date) AS last_report_date
FROM REPORT r
WHERE r.idPatient = 'PA161';
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.
SELECT *
FROM RESULT_REPORT rr
JOIN REPORT r ON rr.idReport = r.idReport
WHERE r.idPatient = 'PA170'
AND r.date = '2018-03-12';
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.
UPDATE RESULT_REPORT
SET status = 'L'
WHERE idReport = 2020
AND idAnalysis IN ('AnChol12', 'AnGlug15');
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.
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;
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.
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;
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.
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;
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.
DELETE FROM ANALYSIS
WHERE name IS NULL;
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.