SQL Server data definition language

Send request to get interview tips in PDF format Personal Interview - Answers to frequently asked questions Tips to earn promotions and perks You can also get your answers published How to write a good CV? How to write a good Cover Letter? Tips to become boss's favorite CareerRide is hiring

SQL Server Subqueries interview questions

---

<<Previous  Next>>

SQL Server Subqueries - Nov 20, 2008 at 18:00 PM by Rajmeet Ghai

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);