SQL Subquery What is Subquery A Subquery or
SQL Subquery
What is Subquery? • A Subquery or Inner query or a Nested query is a query within another SQL query (main query) • Usually located in WHERE or HAVING clause • A subquery is used to return data or set of data that will be used in the main query as a condition to further restrict the data to be retrieved • Subqueries can be used with the following SQL statements along with the comparison operators like !=, <>, =, <, >, >=, <=, IN, NOT IN
What is Subquery? SCALAR SUBQUERY MULTIPLE-ROW SUBQUERY
What is Subquery? MULTIPLE-COLUMN SUBQUERY
Where do we start? FINAL RESULT • Subquery will be execute first, result from subquery will be passed to the main query for further process • When reading or writing SQL subqueries, you should start from the bottom upwards, working out which data is to be passed to the next query up. R 3 4 MAIN QUERY R 2 3 R 1 2 1 SUBQUERIES
Questions • Display employee number, first name, last name and salary for any employees whose salary is greater than average salary? • Display department number and department name that do not involve in any projects. • Display employee number, first name, last name, sex, education level, and department number who has similar sex and education level with employee number 000280
ROLLBACK VS COMMIT TYPE THESE CODE list command options [ENTER] update command options using c off [ENTER] list command options [ENTER] select empno, firstnme from employee where empno = update employee set firstnme = 'MALIK' where empno select empno, firstnme from employee where empno = commit rollback [ENTER] select empno, firstnme from employee where empno = 000280 [ENTER]
- Slides: 7