Sub Queries B
Sub Queries B CSC 271
Comsats Institute of Information Technology
Popular in Database Management
verified elite notetaker
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.
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 firstname.lastname@example.org 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
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'