SQL Server Subqueries

Define Subqueries.

A subquery is a query within a query. These sub queries are created with SQL statements. These subqueries are embedded within SELECT, FROM or the WHERE clause.

Explain with examples for the Subqueries with IN and NOT IN.

Sub Query Example with IN: Displays employees from employee table with bonus > 1000. Using IN first all employees are selected and compared to each row of the subquery. Select first_name from employee

Where bonus_id IN (select id from bonus Where bonus_amt > 1000);

Sub Query Example with NOT IN: Displays employees from employee table with bonus < 1000. Using NOT IN first all employees are selected and compared to each row of the subquery.

Select first_name from employee Where bonus_id NOT IN (select id from bonus Where bonus_amt < 1000);

Explain the subqueries with comparison operators.

Answer
Comparison operators can be used (like <, >, =, !> etc). Sub queries used with comparison operators must return a single value rather than a list to avoid error. Hence the nature of the database must be knows before executing such sub queries.

Example: To display employees who have been referred by John whose id 276

SELECT employeeID FROM employee. employee_name WHERE referenceID = (SELECT referenceID FROM employee.firstname WHERE EmpID = 276)

Example: names of all employees whose salary is greater than the average salery

SELECT Employee_ID FROM Employee.Emp_name WHERE salary > (SELECT AVG (salary) FROM Employee.Emp_name)

Explain with examples for the Subqueries with Exists and NOT Exists.

Answer
A subquery with Exist does not really return any data; it returns TRUE or FALSE.

Example: This select statement will return all records from the sales table where there is at least one record in the orders table with the same sales _id.

SELECT * FROM sales WHERE EXISTS (select * from orders where sales.sales_id = orders.sales_id);

Example for NOT EXIST: This query will work exactly the opposite to above. I.e except for the sane sales_id all other records will be returned

SELECT * FROM sales WHERE NOT EXISTS (select * from orders where sales.sales_id = orders.sales_id); < P >   

SQL Server sub-query - 

What is sub-query? Explain properties of sub-query.

A sub-query is a query embedded in another query. The sub query can be embedded in another SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub query.

Properties:-

• The SELECT query of a subquery is always enclosed in parentheses
• View created by using a subquery cannot be updated.
• The ntext, text, and image data types cannot be used in the select list of sub queries
• If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output