BDD 2023

Which of the following word pairs are synonyms:

• A) table and relation.

• В) table and tuple.

• C) column and tuple

• D). Attribute and row

Which of the following statements are true regarding the comparison between primary key and unique key?

HELP: https://www.shiksha.com/online-courses/articles/primary-key-vs-unique-key/#:~:text=The%20main%20difference%20between%20a,and%20can%20store%20NULL%20values.

• A) unique key attribute can be null, a primary key attribute cannot

• B) There can be more than one unique key in a table, but there can only be one primary key

• C) A primary key attribute can be null, a unique key attribute cannot

• D) That can be multiple primary keys in a table, but there can only be one unique key

4)

Wich of the following SQL statements allows you to drop table T?

• A. drop table T

• B. delete from T

• C. delete from T where true

• D. truncate table T

Answer explanation

The DROP TABLE statement is used to permanently delete a table from a database. It removes all data, indexes, triggers, and constraints associated with the table. Once a table is dropped, it cannot be recovered.

The other options are not correct:

• DELETE FROM T deletes all rows from the table T, but the table itself remains.

• DELETE FROM T WHERE TRUE is equivalent to DELETE FROM T, as the WHERE TRUE clause always evaluates to true.

• TRUNCATE TABLE T deletes all rows from the table T, but the table itself remains and the table ID is reset to 0.

Therefore, the only statement that allows you to drop table T is DROP TABLE T.

Consider table R(A, B, C). With (A,B) Primary Key The query "select A, B from R" returns the same result as:

• A) select distinct A, B from R

• B) select A, B from R where A <> null

• C) select distinct A, B from R where B <> null

• D) select A, B from R where A <> null and B <> null

Answer explanation

SELECT Name, Family_Name FROM Students;

A) SELECT DISTINCT Name, Family_Name FROM Students;

B) SELECT Name, Family_Name FROM Students WHERE Name IS NOT NULL;

C) SELECT DISTINCT Name, Family_Name FROM Students WHERE Family_Name IS NOT NULL;

D) SELECT Name, Family_Name FROM Students WHERE Name IS NOT NULL AND Family_Name IS NOT NULL;

Consider the table T (A: int, B: int). Which query below can produce duplicates?

• A) select * from T

• B) select B from T

• C) select * from T

  Where A = 0

• D) select * from T where B = 0

Answer explanation

A) SELECT * FROM T;

B) SELECT B FROM T; ✅

C) SELECT * FROM T WHERE A = 0;

D) SELECT * FROM T WHERE B = 0;

B ) can produce duplicate

proof of answere D on the image, the rest is obviou

Consider tables R(A, B) and S(B, C). The query "select A from R natural join S" returns the same result as:

• A) select A from R cross join S

• B) select A from R, S

• D) select A from R, S where R.B = S.B

• D)

  select A from R cross join S where R.B = S.B

Answer explanation

C) SELECT A FROM R, S WHERE R.B = S.B ✅

Explanation: This query performs an inner join between tables R and S based on the common column B (R.B = S.B). This is equivalent to the natural join condition, where the join is automatically performed based on common columns between the two tables.

D) La requête D utilise un cross join pour combiner toutes les lignes des tables R et S, puis utilise une clause WHERE pour filtrer les résultats pour n'inclure que les lignes où les valeurs des colonnes B sont égales.

Consider tables R(A, B, C) and S(B, D). Then:

• A) "R natural inner join S" contains more tuples than "R natural join S".

• B) "R natural outer join S" contains at least as many tuples as "R natural join S".

• C) "R natural outer join S" contains at least as many tuples as "R natural left outer join S"

• D) "R natural left outer join S" contains at least as many tuples as "R natural right outer join S".

Answer explanation

Natural inner join combines two tables based on their common columns and includes only the rows where the values in the common columns match. In this case, the natural inner join of tables R and S would include only the rows where the values in column B of R match the values in column B of S.

Natural join also combines two tables based on their common columns but includes all rows from both tables, regardless of whether the values in the common columns match. This means that the natural join of tables R and S would include all rows from table R, along with all rows from table S that have a matching value in column B.

Natural outer join combines two tables based on their common columns and includes all rows from both tables, even if there is no matching value in the common columns. This means that the natural outer join of tables R and S would include all rows from table R, along with all rows from table S, regardless of whether the values in column B match.

Now, let's compare the statements:

A) "R natural inner join S" contains more tuplesthan "R natural join S".

This statement is incorrect because the natural inner join of two tables always contains fewer tuples or rows than the natural join of the same tables. The natural inner join only includes rows where the values in the common columns match, while the natural join includes all rows from both tables, regardless of whether the values in the common columns match.

B) "R natural outer join S" contains at least as many tuples as "R natural join S".

This statement is correct. The natural outer join of two tables always contains at least as many tuples or rows as the natural join of the same tables. The natural outer join includes all rows from both tables, regardless of whether the values in the common columns match, while the natural join only includes rows where the values in the common columns match.

C) "R natural outer join S" contains at least as many tuples as "R natural left outer join S"

This statement is also correct. The natural outer join of two tables always contains at least as many tuples or rows as the natural left outer join of the same tables. The natural outer join includes all rows from both tables, regardless of whether the values in the common columns match, while the natural left outer join only includes all rows from the left table and matching rows from the right table.

D. "R natural left outer join S" contains at least as many tuples as "R natural right outer join S".

This statement is incorrect because the natural left outer join of two tables always contains at least as many tuples or rows as the natural right outer join of the same tables. The natural left outer join includes all rows from the left table and matching rows from the right table, while the natural right outer join only includes matching rows from the left table and all rows from the right table.

Therefore, the correct statements are:

• B) "R natural outer join S" contains at least as many tuples as "R natural join S".

• C) "R natural outer join S" contains at least as many tuples as "R natural left outer join S".

Consider relation R(A, B, C) with the following instance.

The query "select avg(distinct b), min(C) from R" returns:

• A) Tuple (2,1)

• B) Tuple (3,1).

• c). Tuple (2.66,1)

• D) Tuple (2, null)

Answer explanation

1. Average of distinct values in column B:

   To calculate the average of distinct values in column B, we first need to identify the distinct values. Since NULL is considered a distinct value, we have two distinct values: 2 and 4. The average of these two values is (2 + 4) / 2 = 3.

2. Minimum value in column C:

   To find the minimum value in column C, we need to consider all non-NULL values. The only non-NULL value in column C is 1. Therefore, the minimum value is 1.

Conclusion:

The query "select avg(distinct b), min(C) from R" returns the tuple (3, 1). This means that the average of distinct values in column B is 3 and the minimum value in column C is 1.

Therefore, the correct answer is A. Tuple (3,1).

Considering the same table The query "select count(distinct B), max(distinct B) from R" returns:

• A) Tuple (null, null)

• B) Tuple (2, 4).

• C) Tuple (3, 4).

• D) An error

Answer explanation

https://stackoverflow.com/questions/40679208/count-includes-null-values

The query "select B, count(distinct(C) from R group by B, C having count(A) = 1" returns:

• A) Tuples (4, null), (2, 1), (null, null).

• B) Tuples (4, 0), (2, 1), (null, 0).

• C) Tuples （4,0),(null,0).

• D) An error.

Answer explanation

• SELECT B, COUNT(DISTINCT C): This part selects the column B and counts the distinct values in column C for each group formed by the GROUP BY clause.

• FROM R: This specifies that the data is being selected from the table R.

• GROUP BY B, C: This part groups the rows of the table based on the values in columns B and C. It creates separate groups for each unique combination of B and C.

• HAVING COUNT(A) = 1: This is a filtering condition applied after the grouping. The HAVING clause filters the groups based on a specified condition. In this case, it includes only those groups where the count of values in column A is equal to 1.

After the GROUP BY clause, we have these groups:

1. Group with B = 4 and C = NULL.

2. Group with B = 2 and C = 1.

3. Group with B = NULL and C = NULL.

The HAVING clause filters out groups where the count of A is not equal to 1. It retains only the second group because for B = 2 and C = 1, the count of A is 1.

So, the final result includes only the second group:

• For B = 2, count(A) = 1, distinct count(C) = 1.