SEC S20W2 || Databases and SQL language - Part 2

Hello everyone! I hope you will be good. Today I am here to participate in the contest of Dynamic Devs about the Databases and SQL language. It is really an interesting and knowledgeable contest. There is a lot to explore. If you want to join then:

Join Here: SEC S20W2 || Databases and SQL language - Part 2

Designed with ^Canva

Exercise 1

Here is the table with the outliers and common points filled in:

Element 1	Element 2	Element 3	Element 4	Outlier	Common Point
Update	Select	Alter	Insert	Update	The remaining three are SQL commands for data manipulation and retrieval.
Max	Desc	Sum	Avg	Desc	The remaining three are SQL aggregate functions .
Between	In	Count	Like	Count	The remaining three are SQL conditional operators used in WHERE clauses.
Primary key	Foreign key	Unique	Distinct	Distinct	The remaining three are database constraints.

Here is the table with explanations for each statement's TRUE or FALSE answer:

Affirmations	True/False	Description
1. In SQL, it is not possible to delete a table that contains tuples.	FALSE	It is possible to delete a table regardless of its contents using the `DROP TABLE` command.
2. A DBMS ensures data redundancy.	FALSE	A DBMS is designed to minimize data redundancy through normalization and constraints.
3. The Data Definition Language (DDL) allows adding integrity constraints to a table.	TRUE	DDL commands like `CREATE` and `ALTER` allow defining integrity constraints such as `PRIMARY KEY` or `FOREIGN KEY`.
4. A primary key in one table can be a primary key in another table.	FALSE	A primary key uniquely identifies records within one table. It cannot be reused as a primary key in another table.
5. In SQL, the ORDER BY clause is used to sort selected columns of a table.	TRUE	The `ORDER BY` clause is used to sort query results based on one or more columns.
6. A foreign key column can contain NULL values.	TRUE	Foreign key columns can contain `NULL` values, as long as they are not part of a composite key.
7. The PRIMARY KEY constraint includes both UNIQUE and NULL constraints.	FALSE	The `PRIMARY KEY` constraint enforces uniqueness and disallows `NULL` values.
8. Referential integrity constraints ensure links between tables in a database.	TRUE	Referential integrity ensures relationships between tables are maintained through foreign keys.

Exercise 2:

Given the following tables of books and members:

Id	Title	Author	Publisher	Pages	Year	Borrower	Return_Date
1	Notre-dame de Paris	Victor Hugo	Gosselin	636	1831	1	2014-05-13
2	Les Misérables	Victor Hugo	Lacroix	1662	1862	2	2014-08-28
3	Journey to the Center of the Earth	Jules Verne	Hetzel		1864	1	2014-07-10
4	Around the World in 80 Days	Jules Verne	Hetzel	223	1872	1	2014-06-10
5	House of the Dead	Fyodor Dostoevsky	Mikhail	1276	1862	2	2014-05-13

Table 1: Books

Id	Last Name	First Name	Email
1	DUPONT	Jean	[email protected]
2	MARTIN	Paul	[email protected]

Table 2: Members

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

Query	Result
SELECT count(Pages) "Count", sum(Pages) "Total Pages" FROM books
SELECT Id, Title, Return_Date FROM books WHERE Return_Date IS NOT NULL ORDER BY Return_Date ASC;
SELECT * FROM books WHERE Year IN (SELECT Year FROM books WHERE Id=2) AND Id<>2
SELECT CONCAT(Last_Name, ' ', First_Name) AS "Last and First Name", CASE WHEN Last_Name = 'DUPONT' AND First_Name = 'Jean' THEN 3 WHEN Last_Name = 'MARTIN' AND First_Name = 'Paul' THEN 2 ELSE NULL END AS Nombre FROM Members;

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

I have added the query and the picture here again for the better view and understanding.

Propositions Validation

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.
Reason: This statement affects all records, not just the first one.
V Modifies the Title field of all records in the books table to Title1.
Reason: The absence of a WHERE clause means all rows are updated.
F Displays an error message due to the absence of the WHERE clause.
Reason: The absence of a WHERE clause does not produce an error; it just updates 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:

V SELECT Title FROM books WHERE Return_Date between "2014-05-01" AND "2014-06-30";
Reason: This correctly represents the same date range.
F SELECT Title FROM books WHERE Return_Date >= "2014-05-01" OR Return_Date <= "2014-06-30";
Reason: This query would include dates outside of May and June 2014.
F SELECT Title FROM books WHERE Return_Date between "2014-05-01" OR "2014-06-30";
Reason: This is syntactically incorrect.

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

F Displays the number of authors per title.
Reason: This query groups by Title, not Author.
F Displays the number of books per author.
Reason: The correct result would be the number of books per title.
F Does not work.
Reason: The query will work but might not provide meaningful results because grouping by Title is not appropriate for counting authors.

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

F Deletes the Pages column.
Reason: This statement does not affect the schema, only the data.
F Deletes the rows where the page count is not provided.
Reason: The correct syntax for checking NULL values should use IS NULL, but if corrected, this would delete rows with NULL in Pages.
V Does not work.
Reason: The correct SQL to check NULL should be Pages IS NULL.

Exercise 3

Here are the SQL Queries for all the tasks:

Query: 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.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

The above sql query joins 4 tables PATIENT, REPORT, RESULT_REPORTand ANALYSIS to find the patients who have undergone the Cholesterol analysis.
DISTINCT keyword makes it sure that each patient should be listed once. It does not matter whether the patient has visited the doctor for multiple cholesterol analyses.

Query: 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.last_name, P.first_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 sql query joins PATIENT, REPORT and DOCTOR tables. It finds patients who have undergone for the analyses prescribed by the doctor with ID DR2015.
The condition P.city <> D.city makes it sure that only patients from a different city than the doctor are selected.

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

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

Explanation

This use of MAX() function in the query is used to find the latest date of analysis for patient PA161.
The WHERE clause is used to filter the reports of the specified patient.

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

SELECT RR., A. FROM RESULT_REPORT 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';

Explanation

This query joins RESULT_REPORT, REPORT, and ANALYSIS tables to get all analysis result information for patient PA170 on a specific date.
The condition R.date = '2018-03-12' ensures that only the results will be returned from that mentioned specific date.

Query: 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');

Explanation

This query updates the status column of the RESULT_REPORT table to L for the specified analyses ('AnChol12', 'AnGlug15') in report 2020.
The IN clause helps match multiple analysis IDs in one query.

Query: 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;

Explanation

This query selects reports where at least two analyses are abnormal. It means their status is either H (high) or L (low).
The GROUP BYclause groups results by report and patient. The HAVING COUNT(*) >= 2 makes it sure that only those with two or more abnormal results are selected.

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

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 associated with doctors living in the city of Sousse.
The GROUP BY clause groups the results by doctor. And COUNT() function counts the number of reports for each doctor.

Query: 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;

Explanation

This query gets the data of patients who have age between of 20 and 40. Another condition is that it will select those patients who have had more than 5 reports after May 26, 2015.
The GROUP BY groups by patient and the HAVING clause filters those with more than 5 reports.

Query: Delete analyses with no name.

DELETE FROM ANALYSIS WHERE name IS NULL;

Explanation

This query deletes rows from the ANALYSIS table where the name column is NULL.
The WHERE name IS NULL ensures only those analyses with missing names are deleted.

Conclusion

Database is important language which is used at the backend of the software applications. In the lecture I have explored the important concepts of SQL and Database. I have used database management concepts including querying and updating the data efficiently and effectively. Data integrity and referential constraints are the important concepts of database. I have tried my best to solve all the queries and statements. I have provided SQL code for each query with best of my knowledge and I hope it will be helpful for the whole community of database lovers.

I invite @alejos7ven, @suboohi, @patjewell to participate in this contest.