SQL In Nested Query
A nested query in SQL, often referred to as a subquery, is a query inside another query. It allows you to retrieve data from one table or use the result of one query as part of another query.
Example:
SELECT Column_Name1, Column_Name2, …., Column_NameN FROM Table_Name WHERE Column_Name Comparison_Operator ( SELECT Column_Name1, Column_Name2, …., Column_NameN FROM Table_Name WHERE condition;
Let’s take the following table named Student_Details:
The following SQL query returns the record of those students whose marks are greater than the average total marks:
SELECT * FROM Student_Details WHERE Stu_Marks> ( SELECT AVG(Stu_Marks ) FROM Student_Details);
Output:
This example uses the Greater than comparison operator with the Subquery.
Let’s take the following two tables named Faculty_Details and Department tables.
Faculty_Details table:
Department table:
SELECT TOP 4 * FROM Employee WHERE Emp_City = Goa ;
Output:
Course Video
1. Write SQL Query to get all details from
Purchasing.PurchaseOrderDetail table where
Product Name = ‘Adjustable Race’.
2. Write SQL Query to get All Details from
Product where RedorderPoint are greater than avg (RedorderPoint).
3. Write SQL Query to get ProductCategory, Name from Production.ProductCategory table where ProductCategoryID from 1 to 3.
4. Write SQL Query to get PurchaseOrdeID, OrderQTY, UnitPrice from Purchasing.PurchaseOrderDetail table where ModifiedDate Greater than ‘2015-01-01’
5. Write SQL Query to get ProductID, Name, ProductNumber, Color, SafetyStockLevel, SellStartDate from Product table where ReorderPoint are less than avg (ReorderPoint).
6. Compose a SQL query to retrieve all production products from the database, focusing specifically on those associated with the product model named ‘LL Road Frame’. [12 ROWS]
7. Craft a SQL query to fetch currency rates from the database, selecting those entries where the average rate exceeds the overall average of average rates. [OUTPUT: 18ROWS]
8. Identify the email address associated with the most recently modification date recorded in the database. [OUTPUT: 2 ROWS]
9. Write a query to get all product records whose category is ‘Dairy products’, or ‘seafood’. [output: 22 rows]
10. Compose a SQL query to extract all product information where suppliers with company names matching with ‘Plutzer Lebensmittelgroßmärkte AG’ and ‘Specialty Biscuits, Ltd.’ [output: 9rows]
YouTube Reference:
1) SQL In Nested Query in Hindi/Urdu
2) SQL In Nested Query in English
