Sql Practice Questions

The outer query SELECT empName FROM employee E iterates through each employee.
For each employee E, the NOT EXISTS clause checks if the inner subquery returns any rows.
The inner subquery SELECT custId FROM customer C WHERE C.salesRepId = E.empId AND C.rating <> 'GOOD' selects customer IDs for customers associated with the current employee E who do not have a 'GOOD' rating.
If the inner subquery returns no rows (i.e., NOT EXISTS is true), it means there are no customers associated with employee E who have a rating other than 'GOOD'.
Therefore, this employee E must have all their customers with a 'GOOD' rating. The query returns the names of such employees.

Let's analyze each statement:

S1: A foreign key constraint ensures referential integrity, including actions like ON DELETE CASCADE or ON UPDATE RESTRICT. A CHECK assertion can only validate data at insertion/update time and cannot enforce these cascading actions or prevent deletions/updates in the referenced table. Therefore, a foreign key declaration cannot always be replaced by an equivalent CHECK assertion. Hence, S1 is FALSE.

S2: The table R has a composite primary key (a, b). In table S, attribute a is declared as a foreign key referencing R. However, a foreign key referencing a composite primary key must include all attributes of that composite key. Since a alone cannot uniquely identify a row in R, this foreign key declaration is invalid. Hence, S2 is FALSE.

The nested query Select * from R where a in (select S. a from S) retrieves all tuples from R where the value of attribute a exists in the set of a values from relation S. It preserves duplicates from R and effectively creates a semi-join.

Let's analyze the options:

• Option A: Select R. * from R, S where R. a=S. a performs an inner join. This will multiply tuples in R if there are multiple matching a values in S, which is not what the nested query does.
• Option B: Select distinct R. * from R,S where R. a=S. a performs an inner join and then removes duplicate R tuples. This will not preserve the original duplicates from R as required by the nested query.
• Option C: Select R. * from R, (select distinct a from S) as S1 where R. a=S1.a first selects distinct a values from S (aliased as S1). Then, it performs an inner join with R based on R.a = S1.a. Since S1 contains only distinct a values, R tuples will not be duplicated more than once for each distinct a in S, effectively mimicking the IN clause while preserving original duplicates in R.
• Option D: Select R. * from R, S where R.a = S. a and is unique R is syntactically incorrect; is unique R is not standard SQL.

Option C correctly translates the semantic behavior of the nested query, preserving the multiplicity of R tuples based on matching distinct values from S.

The query aims to find the last names and hire dates of the latest hires in departments located at ID 1700.
The inner subquery first joins employees and departments tables on dept-id (using USING(dept-id)).
It then filters these results to location-id = 1700 and groups them by dept-id. For each group, it selects the dept-id and the MAX(hire-date).
The outer query uses the IN operator to select last-name and hire-date from employees where the (dept-id, hire-date) pair matches any pair returned by the inner subquery. This correctly identifies the latest hires per department within the specified location. The IN operator supports pairwise comparisons.

The query is syntactically correct and logically designed to achieve the stated goal.

The first subquery returns the set of all $pid$ values present in the Catalog (parts supplied by any supplier). The second subquery selects the set of $pid$ values associated with any supplier where the $sname \neq 'Sachin'$. By performing the $MINUS$ operation (set difference), we remove any $pid$ that is supplied by at least one supplier other than 'Sachin'. Consequently, the resulting set contains only those $pid$ values supplied exclusively by 'Sachin' and no one else.

The problem asks to identify which of the given queries are equivalent to the English query: "Find the distinct names of all students who score more than 90% in the course numbered 107". Let's analyze each query:

1. Query I (SQL):

   SELECT DISTINCT S.sname
   FROM Students as S, Registration as R
   WHERE R.rollno=S.rollno AND R.Courseno=107 AND R.percent>90
   

   This query joins Students and Registration tables on rollno, filters for Courseno=107 and percent>90, and then selects distinct student names. This directly matches the English query.

2. Query II (Relational Algebra):
   $\Pi_{sname} (\sigma_{courseno=107 \land percent > 90} (Registration \bowtie Students))$
   This query first performs a natural join between Registration and Students, then filters the results based on courseno=107 and percent>90, and finally projects the distinct sname. This is equivalent to Query I and the English query.

3. Query III (Tuple Relational Calculus - TRC):
   $\{T | \exists S \in Students, \exists R \in Registration (S.rollno = R.rollno \land R.courseno = 107 \land R.percent > 90 \land T.sname = S.name)\}$
   This query defines a set of tuples T such that there exist a student S and a registration R satisfying the join condition (S.rollno = R.rollno), the course number and percentage conditions, and T.sname is equal to S.sname. This correctly represents the English query.

4. Query IV (Domain Relational Calculus - DRC):
   $\{<S_{sname}> | \exists S_{rollno}, \exists R_{rollno}, R_{courseno}, R_{percent} (<S_{rollno},S_{sname}> \in Students \land <R_{rollno},107,R_{percent}> \in Registration \land R_{percent} > 90)\}$
   This query defines a set of sname values. It states that for each sname, there must exist corresponding rollno from Students and rollno, courseno, percent from Registration that satisfy the conditions: the rollnos match, courseno is 107, and percent is greater than 90. This correctly represents the English query.

All four queries are equivalent to the given English query.

Problem Analysis

We need to evaluate the truth of four statements (P, Q, R, S) regarding SQL query structure, specifically focusing on the HAVING, GROUP BY, and SELECT clauses.

Statement Evaluation

• P: An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause.

  • TRUE. While HAVING is typically used with GROUP BY, it is technically valid without it. In such cases, the HAVING clause treats the entire result set as a single group. For example: SELECT SUM(sal) FROM emp HAVING SUM(sal) > 1000;.

• Q: An SQL query can contain a HAVING clause only if it has a GROUP BY clause.

  • FALSE. As shown in the analysis of Statement P, GROUP BY is not a mandatory prerequisite for HAVING.

• R: All attributes used in the GROUP BY clause must appear in the SELECT clause.

  • FALSE. You can group by a column without actually projecting it in the result set. For example, you can group employees by DeptID to find the average salary but only select the AvgSalary.

• S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause.

  • TRUE. This is the logical inverse of Statement R. SQL allows grouping by attributes that are not present in the SELECT list.

Conclusion

Statements P and S are true.

Correct Answer: C

First, evaluate the subquery: SELECT B.Age FROM B WHERE B.Name = 'Arun'.
Scanning table $B$, there is no row where $Name = 'Arun'$, which results in an empty set.
In SQL, the predicate $A.Age > ALL (subquery)$ is vacuously TRUE for every row if the subquery returns an empty set.
Since the condition evaluates to TRUE for all three tuples in table $A$ (having $Id$ values 12, 15, and 99), all these rows are selected.
Therefore, the total number of tuples returned by the query is 3.

The SQL query performs a NATURAL JOIN between two subqueries, S and T, and then counts the total number of rows in the result.

1. Subquery S: (SELECT Borrower, Bank_Manager FROM Loan_Records)
   This subquery selects Borrower and Bank_Manager from the Loan_Records table. The result will have rows:

   • (Ramesh, Sunderajan)
   • (Suresh, Ramgopal)
   • (Mahesh, Sunderajan)

2. Subquery T: (SELECT Bank_Manager, Loan_Amount FROM Loan_Records)
   This subquery selects Bank_Manager and Loan_Amount from the Loan_Records table. The result will have rows:

   • (Sunderajan, 10000.00)
   • (Ramgopal, 5000.00)
   • (Sunderajan, 7000.00)

3. NATURAL JOIN (S NATURAL JOIN T):
   A NATURAL JOIN automatically joins tables based on common column names, which in this case is Bank_Manager.

   • When Bank_Manager = 'Sunderajan':
     • (Ramesh, Sunderajan) from S joins with (Sunderajan, 10000.00) from T $\rightarrow$ (Ramesh, Sunderajan, 10000.00)
     • (Ramesh, Sunderajan) from S joins with (Sunderajan, 7000.00) from T $\rightarrow$ (Ramesh, Sunderajan, 7000.00)
     • (Mahesh, Sunderajan) from S joins with (Sunderajan, 10000.00) from T $\rightarrow$ (Mahesh, Sunderajan, 10000.00)
     • (Mahesh, Sunderajan) from S joins with (Sunderajan, 7000.00) from T $\rightarrow$ (Mahesh, Sunderajan, 7000.00)
   • When Bank_Manager = 'Ramgopal':
     • (Suresh, Ramgopal) from S joins with (Ramgopal, 5000.00) from T $\rightarrow$ (Suresh, Ramgopal, 5000.00)

4. SELECT count(*):
   The NATURAL JOIN results in 5 rows. The count(*) function will return the total number of these rows.

Final Count: 5.

The problem describes a process of inserting new records into a table T, starting with (X=1, Y=1). Each new record has X = MX + 1 and Y = 2*MY + 1, where MX and MY are the maximum X and Y values seen so far. We need to find the value of Y when X is 7.

Let's trace the values of X and Y:

1. Initial record: (X=1, Y=1). So, MX=1, MY=1.
2. After 1st insertion (MX=1, MY=1): X = 1+1=2, Y = 2*1+1=3. New record: (2,3). MX=2, MY=3.
3. After 2nd insertion (MX=2, MY=3): X = 2+1=3, Y = 2*3+1=7. New record: (3,7). MX=3, MY=7.
4. After 3rd insertion (MX=3, MY=7): X = 3+1=4, Y = 2*7+1=15. New record: (4,15). MX=4, MY=15.
5. After 4th insertion (MX=4, MY=15): X = 4+1=5, Y = 2*15+1=31. New record: (5,31). MX=5, MY=31.
6. After 5th insertion (MX=5, MY=31): X = 5+1=6, Y = 2*31+1=63. New record: (6,63). MX=6, MY=63.
7. After 6th insertion (MX=6, MY=63): X = 6+1=7, Y = 2*63+1=127. New record: (7,127). MX=7, MY=127.

The query SELECT Y FROM T WHERE X=7; will return the Y value from the record where X is 7. From our trace, when X=7, Y=127.

Although the table data is not provided in the question, we can deduce the result by analyzing the SQL query's logic. The query's purpose is to find the passenger IDs (pid) of all passengers who are older than 65 and have a reservation in 'AC' class.

Let's break down the query:

1. SELECT pid FROM Reservation: The query will return passenger IDs from the Reservation table.
2. WHERE class = 'AC': This is the first filter. It selects only those reservations where the class is 'AC'.
3. AND EXISTS (...): This is the second condition. For each 'AC' reservation found in step 2, the database checks if the subquery returns at least one row.
4. SELECT * FROM Passenger WHERE age > 65 AND Passenger.pid = Reservation.pid: This is a correlated subquery. It checks the Passenger table for a record that has the same pid as the current reservation record being checked, and also verifies if that passenger's age is greater than 65.

In essence, the query finds a pid if and only if that pid is associated with an 'AC' reservation AND that same pid is associated with a passenger older than 65.

Given the correct answer is {1, 3}, the underlying data must look something like this:

Reservation Table
| pnum | pid | class |
|---|---|---|
| ... | 1 | 'AC' |
| ... | 2 | 'SL' |
| ... | 3 | 'AC' |
| ... | 0 | 'AC' |
| ... | 5 | 'SL' |

Passenger Table
| pid | name | age |
|---|---|---|
| 1 | ... | 70 |
| 2 | ... | 45 |
| 3 | ... | 68 |
| 0 | ... | 60 |
| 5 | ... | 72 |

Execution Trace:

• For pid=1: class = 'AC' is true. Does a passenger with pid=1 and age > 65 exist? Yes (age 70). So, 1 is selected.
• For pid=3: class = 'AC' is true. Does a passenger with pid=3 and age > 65 exist? Yes (age 68). So, 3 is selected.
• For pid=0: class = 'AC' is true. Does a passenger with pid=0 and age > 65 exist? No (age 60). So, 0 is not selected.
• Other pids like 2 and 5 would fail the class = 'AC' condition.

1. The innermost subquery, $Q_1 = \{ pid \mid color \neq \text{'blue'} \}$, retrieves the set of all non-blue parts.
2. The middle subquery, $Q_2 = \{ sid \mid pid \notin Q_1 \}$, identifies suppliers of parts that are not non-blue, which corresponds to blue parts.
3. The outer query effectively applies the NOT IN condition to the result of $Q_2$, isolating suppliers that are not associated with supplying only blue parts.
4. Through relational transformation, this logic filters the supplier list to correctly identify those associated with supplying at least one non-blue part.
5. Thus, the query extracts the names of all suppliers who have supplied a non-blue part.

The query defines aliases $C$, $E$, and $R$ for the tables School, Enrolment, and ExamResult in the FROM clause, respectively. The WHERE clause references $S.school-id$, but no table or alias $S$ is defined in the FROM clause, resulting in an "undefined object" or "invalid identifier" error. Furthermore, the GROUP BY school-id clause is ambiguous, as school-id exists in multiple tables (School and Enrolment) without being qualified by an alias. Consequently, the query is syntactically and semantically incorrect.

SQL aggregate functions, such as $AVG()$, $SUM()$, $COUNT()$, $MAX()$, and $MIN()$, perform calculations on a set of values to return a single scalar result. The function $AVG()$ specifically calculates the arithmetic mean of the values in a column. In contrast, $SELECT$ is a DML command used to retrieve data, $ORDER BY$ is a clause used to sort result sets, and $DISTINCT$ is a keyword used to filter unique records. Therefore, $Avg$ is the only option that represents an aggregate function.

The requirement is to find employees whose salary is strictly greater than the maximum salary of any employee in department 5.

Q1: Select e.empId From employee e Where not exists (Select * From employee s where s.department = "5" and s.salary >=e.salary)
This query selects employees e for whom there is no employee s in department 5 with s.salary >= e.salary. This logically means for all employees s in department 5, s.salary < e.salary. This correctly identifies employees whose salary is greater than everyone in department 5.

Q2: Select e.empId From employee e Where e.salary > Any (Select distinct salary From employee s Where s.department ="5")
The ANY keyword means that e.salary must be greater than at least one salary in department 5. This is equivalent to e.salary > MIN(salary in department 5). This does not satisfy the condition of being higher than anyone (i.e., higher than the maximum).

Thus, Q1 is the correct query.

When all values are NULL: $\texttt{SUM(X)}$ ignores NULLs - with no non-NULL values, the result is NULL (not 0). $\texttt{COUNT(X)}$ counts non-NULL values of $X$ - result is 0. $\texttt{COUNT(*)}$ counts all rows regardless of NULLs - result is 1 (the table has one row). This is a classic GATE question testing the distinction between these three aggregate behaviours.

In relational database management systems, Data Control Language (DCL) is specifically designed to manage security and access control. The GRANT command is used to provide specific permissions or privileges to users, allowing them to interact with database objects. Conversely, the REVOKE command is employed to withdraw or remove previously assigned privileges, effectively restricting access to those objects. Because these two commands are the fundamental tools for authorization management in SQL, they are the correct answer. Options A, C, and D are incorrect as they refer to normalization concepts, database constraints, or non-standard terminology rather than primary access control commands.

Query 2 and Query 4 both perform a semi-join operation on the relation $paid$ where $student$ is the primary key, ensuring each qualifying $student$ ID is returned exactly once. Query 1 selects $student$ from $enrolled$, which allows duplicates if a $student$ is enrolled in multiple $courses$; thus, Query 1 returns a multiset of $student$ IDs, while Query 2 and Query 4 return a set. Consequently, Query 2 and Query 4 are identical for any database state. However, if a $student$ has multiple entries in $enrolled$, the result set of Query 1 will contain duplicates, making it distinct from the result set of Query 2.