1. Given a relation R(A, B), write an SQL query to find the tuples t = (a, b) su
ID: 3720454 • Letter: 1
Question
1. Given a relation R(A, B), write an SQL query to find the tuples t = (a, b) such that there does not exist another tuple with A = a. Which of the following SQL queries is the correct one to do this?
select * from R as R1
where unique (select * from R as R2 where R2.A = R1.A)
select * from R as R1
where not exists (select * from R as R2 where R2.A = R1.A and R2.B <> R1.B)
select R1.A, R1.B
from R R1, R R2
where R1.A <> R2.A
select * from R R1
where R1.A not in (select R2.A from R R2)
2. Assume R.a is an integer column. Consider these two queries:
Q1: select * from R where R.a > 1
Q2: (select * from R) except (select * from R where R.a <= 1)
Which of the following statements is NOT correct?
If R.a is a primary key, then the two queries always have the same results.
If R.a is a foreign key, then the two queries always have the same results.
If R.a is nullable, then Q2's results may contain NULLs while Q1's results cannot.
Q2's results may have fewer tuples than Q1's results.
select * from R as R1
where unique (select * from R as R2 where R2.A = R1.A)
select * from R as R1
where not exists (select * from R as R2 where R2.A = R1.A and R2.B <> R1.B)
select R1.A, R1.B
from R R1, R R2
where R1.A <> R2.A
select * from R R1
where R1.A not in (select R2.A from R R2)
Explanation / Answer
Question 1:
select * from R as R1
where unique (select * from R as R2 where R2.A = R1.A)
Question 2
If R.a is a primary key, then the two queries always have the same results.
**Comment for any further queries.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.