DBMS Module 4 Part 1 - SQL Nested Queries PDF

MODULE -4 SQL-2 and NORMALIZATION  CONTENTS SQL -2 – Joins and its types – Nested queries - correlated and uncorrelated, – Aggregation functions, group by and having clauses. NORMALIZATION : – Functional dependencies, – Features of good relational database...

MODULE -4 SQL-2 and NORMALIZATION  CONTENTS SQL -2 – Joins and its types – Nested queries - correlated and uncorrelated, – Aggregation functions, group by and having clauses. NORMALIZATION : – Functional dependencies, – Features of good relational database design, – Atomic domain  Introduction to Nested Queries In nested queries, a query is written inside a query. Nested query is also called subquery or an inner query. The result of inner query is used in execution of outer query. A subquery can be used anywhere that expression is used and must be closed in parentheses. OuterQuery(Inner/Nested/Sub Query); A subquery may occur in:- – A SELECT clause – A FROM clause – A WHERE clause  Introduction to Nested Queries In MySQL, subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. Outerquery.....where v1 IN (innerquery) Comparison operators can be used such as >, =,=, ANY V) returns TRUE if the value v is greater than any of the values in the set V.  Types of Nested Queries Subqueries with ALL, ANY, IN, or SOME [Multi-row operators] ALL & ANY are logical operators in SQL. They return boolean value as a result. ALL Operator The ALL operator returns TRUE iff all of the subqueries values meet the condition. The ALL must be preceded by comparison operators and evaluates true if all of the subqueries values meet the condition. ALL is used with SELECT, WHERE, HAVING statement. Syntax: SELECT column_name(s) FROM table_name WHERE column_name comparison_operator ALL (SELECT column_name FROM table_name WHERE condition(s)); eg: SELECT department_id, AVG(SALARY) FROM EMPLOYEES GROUP BY department_id HAVING AVG(SALARY)>=ALL (SELECT AVG(SALARY) FROM EMPLOYEES GROUP BY  Types of Nested Queries Subqueries with ALL, ANY, IN, or SOME ANY Operator ANY compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row. ANY return true if any of the subqueries values meet the condition. A N Y m u s t b e p r e c e d e d b y c o m p a r i s o n o p e r a t o r s. Syntax: SELECT column_name(s) FROM table_name W H E R E c o l u m n _ n a m e comparison_operator ANY (SELECT column_name FROM table_name WHERE condition(s)); eg: SELECT department_id, AVG( SALARY) FROM EMPLOYEES GROUP BY department_id HAVING AVG(SALARY)>=ANY (SELECT AVG(SALARY) FROM Key Difference Between ANY and ALL: ANY: True if any one value in the subquery satisfies the condition. ALL: True only if all values in the subquery satisfy the condition. Visualizing with Example Data: Assume the Sales department salaries are [3000, 4000, 5000]. For ANY:salary > ANY(3000, 4000, 5000) is true if salary > 3000 OR salary > 4000 OR salary > 5000. Example: A salary of 4500 satisfies this because it is greater than 3000 and 4000. For ALL:salary > ALL(3000, 4000, 5000) is true only if salary > 3000 AND salary > 4000 AND salary > 5000. Example: A salary of 5500 satisfies this because it is greater than all three values. ANY Keyword Purpose: Compares a value to any one of the values in a subquery. Behavior: The condition is true if the comparison holds for at least one value in the set. Example: Find employees who earn more than any employee in the Sales department. SELECT name, salary FROM employees WHERE salary > ANY ( SELECT salary FROM employees WHERE department = 'Sales’); How it works:The subquery gets the salaries of employees in the Sales department.The ANY keyword checks if the employee's salary is greater than at least one of those salaries. ALL KeywordPurpose: Compares a value to all of the values in a subquery. Behavior: The condition is true only if the comparison holds for every value in the set. Example : Find employees who earn more than all employees in the Sales department.  Types of Nested Subqueries with ALL, ANY Comparison Queries  Types of Nested Subqueries with ALL, ANY Comparison Queries 404 && 502, =, , >=,must ALL: value 40 BY 000 Dno HAVING COUNT (*) > 5; Combining the WHERE and the HAVING Clause (continued) Correct Specification of the Query: Note: the WHERE clause applies tuple by tuple whereas HAVING applies to entire group of tuples