SEC S20W2 || Databases and SQL language - Part 2

canvas

Greetings to you my dear friend from wherever you are reading this post from which I have taken part in this challenge titled Databases and SQL language - Part 2. Stay safe as you continue reading.

---

Exercise 1:

---

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. (1 point)

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

Now for us to address the above question about the data which is shown above in a tabular form, we will have to first examine each of the rows, and identify the outlier element before we can then describe the commonality of the rest elements which is exactly what I did below.

Row 1:

In this row, the Elements are as follows;

• Update
• Select
• Alter and
• Insert

As for the Outlier: we have the following;
Alter Update, select, and insert are all under DML commands whereas Alter is a Data definition language.

Commonality: The following listed are commands used to manipulate the data in existing tables.

• Update
• Select
• Insert.

---

Row 2

In this row, the Elements are as follows;

• Max
• Desc
• Sum
• AVG

As for the Outlier Desc is because Max, sum and AVG are aggregated functions whereas Desc is used for defining sorting order in SQL

Commonality: We have the Max, Sum, and Avg are aggregate functions that are used to perform calculations on multiple rows.

---

Row 3:

In this row, the Elements are as follows;

• Between
• In
• Count
• Like

As for the outlier, Count is because between, in, and like are all conditional operators that are used in SQL, whereas Count is an aggregate function.

Commonality: Between, In, and Like are used to match or filter data in queries.

---

Row 4

In this row, the Elements are as follows;

• Primary key
• Unique
• Distinct

The Outlier is Distinct because the Primary key, foreign key, and unique are constraints on table columns, whereas Distinct is a query keyword, that is used to filter out duplicate (multiple) values.

Commonality: All the keys; primary key, foreign key, and unique are constraints that ensure the uniqueness and integrity of the data that are in the database.

---

B) Answer TRUE or FALSE to the following statements: (1 point)

Here I will be presenting my answer whether true/false in a tabular form as given by the professor which I have done below.

Affirmation	True/False
1. In SQL, it is not possible to delete a table that contains tuples.	FALSE This is because you can drop (delete) a table in respective of whether the table contains tuples (data) or not.
2. A DBMS ensures data redundancy.	FALSE This is because A DBMS reduces data redundancy through normalization and other data integrity measures
3. The Data Definition Language (DDL) allows adding integrity constraints to a table.	TRUE This is because DDL commands like Alter and Create are used to define integrity constraints like; primary and foreign keys.
4. A primary key in one table can be a primary key in another table.	FALSE This is because a primary key uniquely identifies rows in one specific table which cannot be shared 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 Reason is that the ORDER BY clause is used to sort the result set of queries by one or more columns.
6. A foreign key column can contain NULL values.	TRUE Reason is that a foreign key can accept NULL values unless the foreign key constraint is explicitly defined to disallow the NULL values.
7. The PRIMARY KEY constraint includes both UNIQUE and NULL constraints.	FALSE This is because a primary key includes unique constraints but cannot accept NULL values.
8. Referential integrity constraints ensure links between tables in a database.	TRUE This is because referential integrity makes sure the foreign key correctly references the primary key in related tables.

---

Exercise 2:

---

The tables are given below 👇.

Table 1: Books	Table 2: Members
Books Column	Members Column
Title, author, publisher pages, borrower, year & return date last	Last name First name & Email

---

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

Now, based on the information that is given in the table by the professor below are the responses to each of the queries and tasks to obtain the result.

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.

• FALSE: This is because without WHERE clauses, all records will be updated.

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

• TRUE: This is because since there is no WHERE clause, all the rows in the table will have their own Title field updated to Title1.

□ Displays an error message due to the absence of the WHERE clause.

• FALSE: This is because the absence of a WHERE clause is valid SQL, and it will not generate an error, as it simply applies to all the 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";

• TRUE This is because the query is selecting a title where the return date falls between May and June 2014, which is equivalent to using the BETWEEN clause for the given date range.

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

• FALSE: This is because the query gives incorrect results since it uses OR instead of AND. This will help select all records after 2014-05-01 or before 2014-06-30 which is not equivalent to the given query.

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

• FALSE: This is because the syntax is incorrect; BETWEEN requires an AND to specify the date range.

---

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

□ Displays the number of authors per title.

• FALSE because the query doesn't group by Author which means it cannot return the number of authors per title.

□ Displays the number of books per author.

• FALSE This is because the query doesn't support group by `Title, and counts records for each title and not by author.

□ Does not work.

• True This is because the query would likely cause a SQL error because Author isn't part of the GROUP BY clause, and SQL requires all non-aggregated columns in the SELECT clause to appear in the GROUP BY.

---

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

□ Deletes the Pages column.

• FALSE: This is because the DELETE command removes rows and not columns.

□ Deletes the rows where the page count is not provided.

• FALS: This is because SQL uses IS NULL to check for a NULL value.

□ Does not work.

• TRUE: This is because the query would fail to delete any records because pages = NULL isn't a valid condition for checking NULL values in SQL.

---

Exercise 3:

freepik

By considering the medical laboratory database "analysis" defined by the following simplified textual representation. To this, the SQL query for each of the specified tasks given will be written as shown below.

---

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;

---

%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 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;

---

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

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

---

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

SQL query SELECT RR.* FROM RESULT_REPORT RR JOIN REPORT R ON RR.idReport = R.idReport WHERE R.idPatient = 'PA170' AND R.date = '2018-03-12';

---

Update the status of analysis results to 'L' for the analyses with IDs 'AnChol12' and 'AnGlug15' for the report with ID 2020.

SQL UPDATE RESULT_REPORT SET status = 'L' WHERE report = 2020 AND analysis IN ('AnChol12', 'AnGlug15');

---

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

SQL 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;

---

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

SQL 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;

---

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

---

Delete analyses with no name.

SQL DELETE FROM ANALYSIS WHERE name IS NULL;

The above queries which have been shared are capable of addressing each of the tasks that are required.

I am inviting: @pelon53, @dove11, @simonnwigwe, and @ruthjoe

Cc:-
@kouba01