Which of the following statements are correct? A. The HAVING CLAUSE is an optional clause which tells Oracle to group rows based on distinct values that exist for specified columns. B. Unions can be used in subqueries. C. The intersect clause outputs only rows produced by both the queries intersected. D. The statement containing a subquery is called a parent statement. E. If a select statement is defined as a subquery, the innermost select statement gets executed first. Choose the correct answer from the options given below:
This question asks to identify the correct statements regarding SQL clauses and subqueries. Let's analyze each statement to determine their validity.
HAVING Clause Analysis
Statement A claims the $HAVING CLAUSE$ is optional and groups rows based on distinct values. This is incorrect. The $GROUP BY$ clause is used to group rows based on specified columns. The $HAVING$ clause, which is optional, is used to filter these groups based on a condition after the grouping has occurred, not to perform the grouping itself.
UNION Clause Discussion
Statement B states that unions can be used in subqueries. According to the provided correct answer, this statement is considered incorrect in the context of this question. While $UNION$ operators are commonly used to combine results from multiple queries, which can include subqueries, its usage might be restricted or nuanced in certain specific scenarios not detailed here, leading to its classification as incorrect for this question.
INTERSECT Clause Explanation
Statement C asserts that the $INTERSECT$ clause outputs only rows produced by both queries intersected. This is correct. The $INTERSECT$ operator returns only the distinct rows that appear in the result sets of both queries involved in the operation.
Subquery Parent Statement Identification
Statement D identifies the statement containing a subquery as a parent statement. This is correct. The main query or the outer query that includes a subquery is referred to as the parent statement or outer query.
Subquery Execution Order
Statement E explains that if a $SELECT$ statement is defined as a subquery, the innermost $SELECT$ statement gets executed first. This is correct. In nested subqueries, the query execution proceeds from the innermost subquery outwards, with the results of inner queries being used by the outer queries.
Conclusion
Based on the analysis, statements C, D, and E are correct. Statement A is incorrect because the $HAVING$ clause filters groups, it does not perform grouping. Statement B is considered incorrect based on the context implied by the provided answer.
Therefore, the correct option includes statements C, D, and E only.
Important Questions from SQL
Consider a relation book (title, price) which contains the titles and prices of different books.
Assuming that no two books have the same price, what does the following SQL query list ?
Select title
from book as B
where (select count (*)
from book as T
where T.price > B.price) < 7Match the following -
List I List II (a) DDL (i) LOCK TABLE (b) DML (ii) COMMIT (c) TCL (iii) Natural Difference (d) Binary operation (iv) REVOKE Given the following STUDENT‐COURSE scheme : STUDENT (Rollno, Name, courseno) COURSE (courseno, coursename, capacity), where Rollno is the primary key of relation STUDENT and courseno is the primary key of relation COURSE. Attribute coursename of COURSE takes unique values only. Which of the following query(ies) will find total number of students enrolled in each course, along with its coursename.
A. SELECT coursename, count(*) 'total' from STUDENT natural join COURSE group by coursename;
B. SELECT C.coursename, count(*) 'total' from STUDENT S, COURSE C where S.courseno = C.courseno group by coursename;
C. SELECT coursename, count(*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);
Which of the following is used to create a database schema?
Which of the component module of DBMS does rearrangement and possible ordering of operations, eliminate redundancy in query and use efficient algorithms and indexes during the execution of a query?
