Sub Queries CSC 271
Comsats Institute of Information Technology
Popular in Database Management
verified elite notetaker
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.
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 email@example.com 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
Are you sure you want to buy this material for
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'