1) What do mean by Subquery?
Solution:
A Select Query embedded in the clause of another select statement is called as Subquery.
The SELECT query of a subquery is always enclosed in parentheses. A subquery can be nested inside the
WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another
subquery.
Types of subqueries:
1)correlated subquery
2)non correlated subquery.
2) Define noncorrelated subquery.
A noncorrelated subquery is subquery that is independent of the outer query and it can be executed on its
own without relying on main outer query.
e.g.:
Select id,name from students_subject_info
where subject_id= (select subject_id form subjects where sub_name=’SQL’);
3) List three statements which are true regarding subqueries?
Solution:
1) A Main query can have many subqueries.
2) the subquery and main query can retrieve data from different tables.
3) Multiple columns or expressions can be compared between the subquery and main query.
4) Write a SQL Query to display that year in which the student scored the maximum total marks.
TABLE: STUDENT
Solution:
select * from student o where (total_marks,year) in
(
select max(total_marks),year
from student i
group by year
)
output:
Explanation:
Here we are using multiple columns in WHERE CLAUSE, this type of query id known as
MULTI-COLUMN SUBQUERY.
5) What are the advantages and disadvantages of using a subquery?
Solution:
Advantages:
Subqueries allow you to use the results of another query in the outer query.
Subqueries in some complex SQL queries can simplify coding and improve maintainability by breaking
down the complex query into a series of logical steps.
In some cases, subqueries are easier to understand than complex joins and unions.
Disadvantages:
When a subquery is used, the query optimizer of the database server may have to perform additional
steps like sorting the results, etc. Hence, in some cases subqueries can be less efficient than using
joins. So, favour joins to subqueries.
6) Display the top highest paid employees working in each department.
Solution:
TABLE: EMPLOYEE_INFORMATION
Solution:
select id,name,monthly_salary,job_role,deptno
from employee_information where monthly_salary
in (select max(monthly_salary) from employee_information group by deptno)
order by deptno;
Output:
Explanation:
The subquery will execute first and give the maximum salary value in the where clause of outer query and all
those rows which evaluate to true for the condition is given in the output.
7) Write a query to find the second-highest salary from the employee table.
SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);
8) We are given a transaction table that consists of transaction_id, user_id, transaction_date,
product_id, and quantity. We need to query the number of users who purchased products on
multiple days(Note that a given user can purchase multiple products on a single day).
SELECT COUNT(user_id)
FROM
SELECT user_id
FROM orders
GROUP BY user_id
HAVING COUNT(DISTINCT DATE(date)) > 1
) t1
9) What is the use case of EXISTS and NOT EXISTS in subquery ?
The EXISTS operator is a Boolean operator that returns either true or false result. It is used with a
subquery and checks the existence of data in a subquery. If a subquery returns any record at all, this
operator returns true. Otherwise, it will return false. The NOT EXISTS operator used for negation that
gives true value when the subquery does not return any row. Otherwise, it returns false. Both EXISTS
and NOT EXISTS used with correlated subqueries. The following example illustrates it more clearly.
10) What is ALL,ANY,SOME in subqueries ?
We can use a subquery which is followed by the keyword ALL, ANY, or SOME after a comparison
operator. The following are the syntax to use subqueries with ALL, ANY, or SOME:
operand comparison_operator ANY (subquery)
operand comparison_operator ALL (subquery)
operand comparison_operator SOME (subquery)
The ALL keyword compares values with the value returned by a subquery. Therefore, it returns TRUE
if the comparison is TRUE for ALL of the values returned by a subquery. The ANY keyword returns
TRUE if the comparison is TRUE for ANY of the values returned by a subquery. The ANY and SOME
keywords are the same because they are the alias of each other.
Example: