New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

Sub Queries B

by: Ismail Yousuf
Ismail Yousuf
Comsats Institute of Information Technology
GPA 3.7

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Sub Queries of Oracle Database
Database Management
Amjad Usman
Class Notes
Database, Oracle, SQL, Computer Science
25 ?




Popular in Database Management

Popular in ComputerScienence

This 21 page Class Notes was uploaded by Ismail Yousuf on Saturday January 9, 2016. The Class Notes belongs to CSC 271 at Comsats Institute of Information Technology taught by Amjad Usman in Winter 2016. Since its upload, it has received 16 views. For similar materials see Database Management in ComputerScienence at Comsats Institute of Information Technology.

Similar to CSC 271 at Comsats Institute of Information Technology

Popular in ComputerScienence


Reviews for Sub Queries B


Report this Material


What is Karma?


Karma is the currency of StudySoup.

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 01/09/16
CSC271 Database Systems Lecture 23 Data Retrieval from Multiple Tables [using Sub-queries] Instructor : Amjad Usman Tuesday, December 15, 2015 Recap  Processing Multipleables - Using Sub-queries  Sub-query Syntax  Sub-queries type  Guidelines for Using Sub-queries Comparison Operators Modified with the ALL or ANY Keywords • TheALL andANY keywords can modify a comparison operator to allow an outer query to accept multiple values from a subquery. • The general form of theWHERE clause for this type of query is shown here. WHERE <expression> <comparison_operator> [ALL | ANY] (subquery) • Subqueries that use these keywords may also include GROUP BY and HAVING clauses. The ALL Keyword • TheALL keyword modifies the greater than comparison operator to mean greater than all values. SELECT emp_ssn FROM employee WHERE emp_salary > (SELECT emp_salary FROM employee WHERE emp_salary > 40000); SELECT emp_ssn FROM employee WHERE emp_salary >ALL (SELECT emp_salary FROM employee WHERE emp_salary > 40000); Using the ALL Operator in Multiple-Row Subqueries The slide example displays employees whose salary is less than the salary of all employees with a job ID of IT_PROG and whose job is not IT_PROG. >ALL means more than the maximum, and <ALL means less than the minimum. The NOT operator can be used with IN, ANY, and ALL operators. SELECT employee_id, last_name, job_id, salary FROM employees (9000, 6000, 4200) WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; Using the ANY Operator in Multiple-Row Subqueries The slide example displays employees who are not IT programmers and whose salary is less than that of any IT programmer. The maximum salary that a programmer earns is $9,000. <ANY means less than the maximum. >ANY means more than the minimum. SELECT employee_id, last_name, job_id, salary FROM employees (9000, 6000, 4200) WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; An "= ANY" (Equal Any) Example  The "=ANY" operator is exactly equivalent to the IN operator.  For example,to find the names of employees that have male dependents,you can use either IN or "=ANY" – both of the queries shown below will produce an identical result table. SELECT emp_last_name "Last Name",emp_first_name "First Name" FROM employee WHERE emp_ssn IN (SELECT dep_emp_ssn FROM dependent WHERE dep_gender = 'M'); SELECT emp_last_name "Last Name",emp_first_name "First Name" FROM employee WHERE emp_ssn =ANY (SELECT dep_emp_ssn FROM dependent WHERE dep_gender = 'M'); A "!= ANY" (Not Equal Any) Example  The "=ANY" is identical to the IN operator.  However,the "!=ANY" (not equal any) is not equivalent to the NOT IN operator.  If a subquery of employee salaries produces an intermediate result table with the salaries  $38,000,$43,000,and $55,000,  then theWHERE clause shown here means  "NOT $38,000"AND "NOT $43,000"AND "NOT $55,000". WHERE NOT IN (38000,43000,55000);  However,the "!=ANY" comparison operator and keyword combination shown in this nextWHERE clause means  "NOT $38,000" OR "NOT $43,000" OR "NOT $55,000". MULTIPLE LEVELS OF NESTING  Subqueries may themselves contain subqueries.  When theWHERE clause of a subquery has as its object another subquery,these are termed nested subqueries.  Consider the problem of producing a listing of employees that worked more than 10 hours on the project named Order Entry.  employee,  assignment, emp_ssn last_name first_name emp_ssn pro_no work_hours  project pro_no pro_name Example SELECT emp_last_name "Last Name", emp_first_name "First Name" FROM employee WHERE emp_ssn IN (SELECT work_emp_ssn FROM assignment WHERE work_hours > 10AND work_pro_number IN (SELECT pro_number FROM project WHERE pro_name = 'Order Entry') ); Last Name First Name --------------- --------------- Bock Douglas Prescott Sherri Correlated vs. Non-correlated Subqueries  Subqueries can be:  Noncorrelated–executed once for the entire outer query  Correlated–executed once for each row returned by the outer query  Non-correlated subqueries:  Do not depend on data from the outer query  Execute once for the entire outer query  Correlated subqueries:  Make use of data from the outer query  Execute once for each row of the outer query  Usually use the EXISTS operator Processing a noncorrelated subquery A noncorrelated subquery processes completely before the outer query begins Correlated Subquery Example • Show all orders that include furniture finished in natural ash The EXISTS operator will return a TRUE value if the subquery resulted in a non- empty set, otherwise it returns a FALSE SELECT DISTINCT ORDER_ID FROM ORDER_LINE_T WHERE EXISTS (SELECT * FROM PRODUCT_T WHERE PRODUCT_ID = ORDER_LINE_T.PRODUCT_ID AND PRODUCT_FINISH =‘Natural ash’); The subquery is testing for a value that comes from the outer query Subquery refers to outer- query data, so executes once for each row of outer query Processing a correlated subquery Subquery refers to outer- query data, so executes once for each row of outer query Note: only the orders that involve products with Natural Ash will be included in the final results The HAVING Clause with Subqueries  Display all the departments that have a minimum salary greater than that of department 50 emp_id dept_id salary 1001 40 5000 1002 30 4500 1003 50 2500 1004 50 4000 1005 30 3700 1006 40 3500 SELECT department_id, MIN(salary) FROM employees GROUP BY department_id 2500 HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50); Exercise: Executing Single-Row Subqueries display employees whose job ID is the same as that of employee 141 and whose salary is greater than that of employee 143. SELECT last_name, job_id, salary FROM employees ST_CLERK WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > 2600 (SELECT salary FROM employees WHERE employee_id = 143); Subquery – Derived Table Example  Show all products whose standard price is higher than the average price One column of the subquery is an Subquery forms the derived table used in the FROM clause of the outer query aggregate function that has an alias name. That alias can then be referred to in the outer query The WHERE clause normally cannot include aggregate functions, but because the aggregate is performed in the subquery its result can be used in the outer query’s WHERE clause. Derived table is required when we want to display information from subquery e.g here we want to show both the standard price and the average standard price SELECT Sub-query Examples THANK YOU SO MUCH


Buy Material

Are you sure you want to buy this material for

25 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

Janice Dongeun University of Washington

"I used the money I made selling my notes & study guides to pay for spring break in Olympia, Washington...which was Sweet!"

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

Parker Thompson 500 Startups

"It's a great way for students to improve their educational experience and it seemed like a product that everybody wants, so all the people participating are winning."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.