Data base management system full details

dbms

1. SQL Queries, corelated sub queries and joins

2. What is subquery? A Subquery is a query within another query.The outer query is called as main and inner query is called as sub query The sub query is often referred to as nested SELECT, SUB-SELECT or inner SELECT statement. The subquery generally executes first,and its output is used to complete the query condition for the main or outer query

3. Sub query

4. Guidelines for using subquery Enclose subqueries in parentheses Place subqueries on the right side of the comparison operator Use single-rpw operators with singlerow subqueries Use multiple-row operator with multiple-row subquries.

5. Types of subquery Single row subquery Multiple row subquery Multiple column subquery Co-related subquery

6. Single Row subquery Return only single row Use single row comparison operators like >,<,>=,<=,=. EXAMPLE:Display the number,name and salary of the faculty whose salary is equal to the minimum salary. SELECT F_NO,F_NAME,F_SALARY From Faculty Where F_SALARY=(select MIN(F_SALARY) from Faculty);

7. Multiple row subquery Returns multiple rows of data to the outer query. Used with operators like IN , ANY , ALL , etc., where multiple values are expected. Example: SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM orders WHERE order_date = '2024-05-06');

8. Multiple column sub query So far you have written single-row subqueries and multiple row subqueries where only one column was compared in the where clause or Having clause of the SELECT statement. If you want compare two or more columns.You must write a compound Where clause using logical operation operator Multiple-Column Subqueries enable you to combine Duplicate where condition into a single where clause

9. C orrelated Subqueries: A correlated subquery is one that is executed after the outer Query is executed.So co related Subqueries take an approach opposite to that of normal subqueries. Execution of Co related Subquery: The outer query will get executed first and for every row of outer query,inner query will executed.So the inner query will get executed as many times as no row in result of the outer query The inner query and outer query dependent on each other