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

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 in Oracle Database
Database Management
Amjad Usman
Class Notes
Database, Oracle, SQL, Computer Science
25 ?




Popular in Database Management

Popular in ComputerScienence

This 25 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 14 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


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 22 Data Retrieval from Multiple Tables [using Sub-queries] Instructor : Amjad Usman Tuesday, December 15, 2015 Objectives – Today’s Lecture  Definition of terms  Write multiple table SQL queries  Understanding Sub-Queries  Guidelines for Sub-queries  Types of Sub-queries 3 Introduction  Querying one table already done & practiced!  Real power of relational database  Storage of data in multiple tables  Necessitates creating queries to use multiple tables  Two Basic approaches for processing multiple tables  Sub-queries  Join 4 Processing Multiple Tables Using Sub-queries  A subquery is a query within a query.  Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time.  Subquery – placing an inner query (SELECT statement) inside an outer query  Inner query provides a set of one or more values for outer query 5 Processing Multiple Tables Using Sub-queries  One of the two basic approaches to process multiple tables  Different people will have different preferences about which technique to use  Joining is useful when data from several tables are to be retrieved and displayed  Subquery when data from tables in outer query are to be displayed only 6 Using a Subquery to Solve a Problem  Who has a salary greater thanAli’s? Main query: Which employees have salaries greater than Ali’s salary? Subquery: What is Ali’s salary? 7 Subquery Syntax SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);  The subquery (inner query) executes once before the main query (outer query).  The result of the subquery is used by the main query. 8 Using a Sub-query SELECT last_name 11000 FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Ali'); 9 The basic concept is to pass a single value or many values from the subquery to the next query and so on. 4 3 2 1 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. 10 Subquery Types  There are three basic types of subqueries. 1. Subqueries that operate on lists by use of the IN operator or with a comparison operator.  These subqueries can return a group of values,but the values must be from a single column of a table. 11 SUBQUERY TYPES 2. Subqueries that use an unmodified comparison operator (=,<,>,<>)  these subqueries must return only a single,scalar value. 3. Subqueries that use the EXISTS operator to test the existence of data rows satisfying specified criteria. 12 Guidelines for Using Subqueries  Enclose subqueries in parentheses.  Place subqueries on the right side of the comparison condition.  The ORDER BY clause in the subquery is not needed.  Subqueries cannot manipulate their results internally.  Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries. 13 Sub-Queries Example  SELECT CUSTOMER_NAME FROM CUSTOMER_T,ORDER_T WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID AND ORDER_ID = 1008;  SELECT CUSTOMER_NAME FROM CUSTOMER_T WHERE CUSTOMER_ID = (SELECT CUSTOMER_ID FROM ORDER_T WHERE ORDER_ID = 1008); 14 SUBQUERIES AND THE IN Operator  Subqueries that are introduced with the keyword IN take the general form:  WHERE expression [NOT] IN (subquery)  The only difference in the use of the IN operator with subqueries is that the list does not consist of hard- coded values. 15 SUBQUERIES AND COMPARISON OPERATORS  The general form of theWHERE clause with a comparison operator is similar to that used thus far in the text.  Note that the subquery is again enclosed by parentheses. WHERE <expression> <comparison_operator> (subquery) 16 SUBQUERIES AND COMPARISON OPERATORS  The most important point to remember when using a subquery with a comparison operator is that the subquery can only return a single or scalar value.  This is also termed a scalar subquery because a single column of a single row is returned by the subquery. 17 To identify the students who have failed in course CSC273 Select student_id From marks Where course_id = ‘CSC273’ And grade < 40; If we want to retrieve a name based on a student id Select stu_name From student Where student_id = 9292145; Select stu_name From Student Where student_id in ( select student_id From marks Where course_id = ‘CSC273’ Why And grade < 40); use IN? 18 Select stuname From Student Where studentid in ( select studentid From marks Where courseid = ‘CSC273’ And grade < 40); Retrieve a list of student Retrieve the name of id’s who have mark < 40 the student id’s in this for CSC273 list. 19 Subquery Example  Show all customers who have placed an order Many programmers simply use IN even if IN operator will test to see if the equal sign (=) would also work CUSTOMER_ID value of a row is included in the list returned from the subquery SELECT CUSTOMER_NAME FROM CUSTOMER_T WHERE CUSTOMER_ID IN (SELECT DISTINCT CUSTOMER_ID FROM ORDER_T); Subquery is embedded in parentheses. In this case it returns a list that will be used in the WHERE clause of the outer query 20 SUBQUERIES AND COMPARISON OPERATORS  If we substitute this query as a subquery in another SELECT statement,then that SELECT statement will fail.  This is demonstrated in the next SELECT statement. Here the SQL code will fail because the subquery uses the greater than (>) comparison operator and the subquery returns multiple values. SELECT emp_ssn FROM employee WHERE emp_salary > (SELECT emp_salary FROM employee WHERE emp_salary > 40000); 21 Aggregate Functions and Comparison Operators  The aggregate functions (AVG,SUM,MAX,MIN, and COUNT) always return a scalar result table.  Thus,a subquery with an aggregate function as the object of a comparison operator will always execute provided you have formulated the query properly. 22 Aggregate Functions and Comparison Operators SELECT emp_last_name "Last Name", emp_first_name "First Name", emp_salary "Salary" FROM employee WHERE emp_salary > (SELECTAVG(emp_salary) FROM employee); Last Name First Name Salary --------------- --------------- ---------- Bordoloi Bijoy $55,000 Joyner Suzanne $43,000 Zhu Waiman $43,000 Joshi Dinesh $38,000 23 Exercise 1. Write a query that will list the names of who is older than the average student. TIPthis should be used then as a filter.erage age of students SELECT stu_name FROM student WHERE age > (SELECT avg(age) FROM student); This will return 25 students of the 74 who are enrolled as being older than the average age. 24 THANK YOU SO MUCH 25


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

Steve Martinelli UC Los Angeles

"There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."

Allison Fischer University of Alabama

"I signed up to be an Elite Notetaker with 2 of my sorority sisters this semester. We just posted our notes weekly and were each making over $600 per month. I LOVE StudySoup!"

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.