Scribd
Upload
4 views3 pages

Subquery Example

The document provides SQL queries to retrieve information from employees and departments tables. It includes a query to find all employees located in a specific location (id 1700) by first selecting departments in that location and then using their department IDs to find the corresponding employees. Additionally, it includes a query to find employees with salaries greater than the average salary and a union query to combine names from employees and dependents.

Uploaded by

sairabanu.pansare
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views3 pages

Subquery Example

The document provides SQL queries to retrieve information from employees and departments tables. It includes a query to find all employees located in a specific location (id 1700) by first selecting departments in that location and then using their department IDs to find the corresponding employees. Additionally, it includes a query to find employees with salaries greater than the average salary and a union query to combine names from employees and dependents.

Uploaded by

sairabanu.pansare
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Consider the following employees and departments tables from the sample

database

Suppose you have to find all employees who locate in the location
with the id 1700.

1. find all departments located at the location whose id is 1700:


SELECT * FROM departments WHERE location_id = 1700;

2. Second, find all employees that belong to the location 1700 by using
the department id list of the previous query:
SELECT employee_id, first_name, last_name FROM employees WHERE
department_id IN (1 , 3, 8, 10, 11)
SELECT employee_id, first_name, last_name FROM employees WHERE
department_id IN (SELECT department_id FROM departments WHERE
location_id = 1700) ORDER BY first_name , last_name;

finds employees whose salary is greater than the average salary of all
employees:

SELECT employee_id, first_name, last_name, salary FROM employees


WHERE salary > (SELECT AVG(salary)FROM employees);
SELECT first_name,last_name FROM employees UNION SELECT first_name,
last_name FROM dependents ORDER BY last_name;

You might also like