Nested_Query - Nested Query (Sub Query) Nested Query...

Info iconThis preview shows pages 1–13. Sign up to view the full content.

View Full Document Right Arrow Icon
Nested Query (Sub Query)
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Nested Query Consider the relations: Employees ( ssn , last_name, first_name, salary, d_id) Department ( dept_id , d_name)
Background image of page 2
Nested Query Which employees have salaries greater than 'john' salary? 1. find what john earns, and then 2. find Who earns more than that john’s amount. Problem can be sloved by placing one query inside another query
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Sub Queries The inner query or the subquery returns a value that is used by the outer query or the main query. A subquery is a SELECT statement that is within in a clause of another SELECT statement. Subquery is very useful when we need to select rows from a table with a condition that depends on the data in the table itself.
Background image of page 4
Nested Query Syntax: SELECT column_list FROM table WHERE expr operator The subquery can be in a number of SQL clauses, including: The FROM clause The WHERE clause The HAVING clause INSERT, UPDATE, DELETE statements
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Nested Query Operator includes a comparison condition Comparison conditions fall into two classes: single-row operators ( >, =, >=, <, <>, <= ) multiple-row operators ( IN, ANY, ALL ) SELECT last_name FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = ’john’ );
Background image of page 6
Nested Query Types of Subqueries Single-row subqueries : Queries that return only one row from the inner SELECT statement Multiple-row subqueries : Queries that return more than one row from the inner SELECT statement There are also multiple-column Subqueries: Queries that return more than one column from the inner SELECT statement.
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Nested Query - Guidelines Enlcose subqueries in paranthesis Place subqueries on right side of the comparison condition Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries
Background image of page 8
Single-row Subqueries Query2: Display the employees who is working in the department as same as that of employee 141.
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Single-row Subqueries Query2: Display the employees who is working in the department as same as that of employee 141. SELECT last_name, d_id FROM employees WHERE d_id = (SELECT d_id FROM employees WHERE ssn = 141) ;
Background image of page 10
Single-row Subqueries Query 3: Display employees whose d_id is same as that of employee 141 and whose salary is greater than that of employee 143.
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Single-row Subqueries Query 3: Display employees whose d_id is same as that of employee 141 and whose salary is greater than that of employee 143.
Background image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 44

Nested_Query - Nested Query (Sub Query) Nested Query...

This preview shows document pages 1 - 13. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online