Define subquery. Explain with an example
A subquery is a query within a query. They can be within WHERE, FROM or SELECT CLAUSE.
Example: To fetch data from employee and department tables to display employees with location as Chicago.
select empno,empname,job,sal,deptno from employee where deptno in (select deptno from dept where loc = 'CHICAGO');
What is correlated query? Explain its uses.
In a correlated query, the outer query is related to the inner query. This means one or more columns in the outer query are referenced. It’s used when the outer queries value is being used by inner query. For example, we need to find which employee had more perks in the current month than they did in the previous month. The correlated subquery is executed for each row of perks information in the parent query to first determine what the perks were for each employee in the previous month. This data, in turn, is compared to perks for each employee in the current month, and only those employees whose perks in the current month were greater that their previous month's perks are returned.
What are Set Operators? Explain union and union all with an example for each.
Set operators are used to combine results of two compound queries into a single result set. Queries that use set operators are compound queries.
Union set operator- Selects distinct rows from the tables
Example:
SELECT employee_id FROM employee
UNION
SELECT employee_id FROM salary;
Union all – selects all rows from either queries. Doesn’t check if they are distinct.
Example:
SELECT student_id FROM student
UNION
SELECT subject_id FROM subject;
