Subquery In SQL | SQL Subquery Tutorial With Examples | SQL Tutorial For Beginne

2. What’s in It for You? • What is a Subquery? • Types of Subqueries • Subqueries with the SELECT Statement • Subqueries with the INSERT Statement • Subqueries with the UPDATE Statement • Subqueries with the DELETE Statement

3. What is a Subquery? A subquery is a select query that is enclosed inside another query. The inner select query is usually used to determine the results of the outer select query Outer Query Select Dept from Employees where Salary = (Select Max(Salary) from Employees); Inner Query

4. Click here to watch the video

5. What is a Subquery? Write a SQL Query to display department with maximum salary from employees table? Select Dept from employees where salary=(select Max(salary) from employees); Employees Table Output

6. Let’s see how this query works? Find the name of the employee with maximum salary in the Employees table Employees Table Select Emp_name from Employees where salary=(select Max(salary) from employees); Output

7. Let’s see how this query works? select Max(salary) from employees; First the INNER Query is executed Select Emp_name from employees where salary INNER Query gives following result Output of inner query is substituted in outer query On execution, the outer query gives following result:

8. Types of Subqueries Types of Subqueries Subqueries with the SELECT Statement Subqueries with the INSERT Statement Subqueries with the UPDATE Statement Subqueries with the DELETE Statement

9. Subqueries with the SELECT Statement SQL subqueries are majorly used with SELECT statement • Syntax: • SELECT column_name FROM table WHERE column_name OPERATOR (SELECT column_name FROM table [WHERE])

10. Subqueries with the SELECT Statement • select emp_name, dept, salary from employees where salary < (select avg(salary) from employees); Employees Table Output

11. Subqueries with the INSERT Statement The INSERT statement uses the data returned from the subquery to insert into another table • Syntax: • INSERT INTO table_name SELECT * FROM table WHERE VALUE OPERATOR

12. Subqueries with the INSERT Statement • INSERT INTO orders SELECT product_id, p_name, sell_price FROM products WHERE product_id IN (SELECT product_id FROM products WHERE sell_price > 1000); Products Table Output – Orders Table

13. Subqueries with the UPDATE Statement The subquery can be used in conjunction with the UPDATE statement Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement Syntax: UPDATE table SET column_name = new_value WHERE OPERATOR VALUE(SELECT COLUMN_NAME FROM TABLE_NAME) WHERE CONDITION);

14. Subqueries with the UPDATE Statement UPDATE EMPLOYEES SET SALARY = SALARY *0.35 WHERE AGE IN (SELECT AGE FROM EMPLOYEES_B WHERE AGE >=27); Employees Table Output

15. Subqueries with the DELETE Statement The subquery can be used in conjunction with the DELETE statement • Syntax: • DELETE FROM TABLE_NAME WHERE OPERATOR VALUE (SELECT COLUMN_NAME FROM TABLE_NAME) WHERE CONDITION)

16. Subqueries with the DELETE Statement Employees Table • DELETE FROM EMPLOYEES WHERE AGE IN (SELECT AGE FROM EMPLOYEES_B WHERE AGE >=27); Output

17. Join us to learn more! simplilearn.com UNITED STATES Simplilearn Solutions Pvt. Limited 201 Spear Street, Suite 1100 San Francisco, CA 94105 Phone: (415) 741-3319 INDIA Simplilearn Solutions Pvt. Limited #53/1C, 24th Main, 2nd Sector HSR Layout, Bangalore 560102 Phone: +91 8069999471 UNITED STATES Simplilearn Solutions Pvt. Limited 801 Corporate Center Drive, Suite 138 Raleigh, NC 27607 Phone: (919) 205-5565