Software Specification KXA233 Lecture 6A Subqueries and Interactive Operations

Software SpecificationKXA233Lecture 6ASubqueries and Interactive Operations Paul Crowther School of Computing University of Tasmania

Today... • Multiple - Row subqueries • Multiple Column subqueries • Introduction to interactive reports

Multiple - Row Subqueries • Return more than one row • Use multiple-row comparison operators • IN equal to any member in a list • ANY compare value to each value returned by the subquery • ALL compare value to every value returned by the subquery

Example - IN SQL>SELECT ename, sal, deptno2 FROM emp3 WHERE sal IN (SELECT MIN(sal)4 FROM emp5 GROUP BY deptno); ENAME SAL DEPTNO---------- --------- ---------SMITH 800 20JAMES 950 30MILLER 1300 10

Example - ANY SQL>SELECT empno, ename, job2 FROM emp3 WHERE sal < ANY (SELECT (sal)4 FROM emp5 WHERE job = ‘CLERK’); EMPNO ENAME JOB------------------------------------------ 7654 MARTIN SALESMAN 7521 WARD SALESMAN

Example - ALL SQL>SELECT empno, ename, job2 FROM emp3 WHERE sal >ALL (SELECT AVG(sal)4 FROM emp5 GROUP BY deptno); EMPNO ENAME JOB------------------------------------------ 7839 KING PRESIDENT 7566 JONES MANAGER 7902 FORD ANALYST 7788 SCOTT ANALYST

Multiple-Column Subqueries • Display the order number, product number and quantity of any item in which the product number and quantity both match the product number and quantity in order 605 SQL> SELECT ordid, prodid, qty2 FROM item3 WHERE (prodid, qty) IN4 (SELECT prodid, qty5 FROM item6 WHERE ordid = 605)7 AND ordid < > 605;

SQL> SELECT ordid, prodid, qty2 FROM item3 WHERE (prodid, qty) IN4 (SELECT prodid, qty5 FROM item6 WHERE ordid = 605)7 AND ordid < > 605; ORDID PRODID QTY -------------------------------------- 617 100861 100 617 100870 500 616 102130 10 SQL> SELECT prodid, qty2 FROM item3 WHERE ordid = 605; PRODID QTY --------------------------------------100861 100100870 500100890 5101860 50101863 100102130 106 rows selected Multiple-Column Subqueries

Pairwise PROID QTY100861 100100870 500100890 5101860 50101863 100102130 10 Produced by a single subquery in the WHERE clause Nonpairwise PROID QTY100861 100100870 500100890 5101860 50101863 100102130 10 Produced by a multiple subqueries in the WHERE clause Column Comparisons

Nonpairwise Subqueries SQL> SELECT ordid, prodid, qty2 FROM item3 WHERE prodid IN (SELECT prodid4 FROM item 5 WHERE ordid = 605) 6 AND qty IN (SELECT qty7 FROM item 8 WHERE ordid = 605) 7 AND ordid < > 605; ORDID PRODID QTY -------------------------------------- 609 100870 5 616 100861 10 616 102130 10 621 100861 10 618 100870 10 618 100861 50

Interactive Reports • Input query values at runtime • Use SQL*Plus substitution variables to temporarily store variables • Pass variable values between SQL statements • Dynamically alter headers and footers

Substitution Variables • Use a variable prefixed with an ampersand (&) to prompt the user for a value SQL>SELECT empno, ename, sal, deptno2 FROM emp3 WHERE empno = &employee_num; Enter value for employee_num: 7369 EMPNO ENAME SAL DEPTNO--------------------------------------------------------- 7369 SMITH 800 20

User Variables • You can specify Column Names, Expressions and text at runtime • Use substitution variables to supplement • WHERE • ORDER BY • Column expression • Table name • Entire SELECT statement

Example SQL> SELECT empno, ename, job, &column_name2 FROM emp3 WHERE &condition4 ORDER BY &order_column; Enter value for column_name: sal Enter value for condition: sal>3000 Enter value for order_column: ename EMPNO ENAME JOB SAL--------------------------------------------------------- 7902 FORD ANALYST 3000 7839 KING PRESIDENT 5000 7788 SCOTT ANALYST 3000

User Variables • You can predefine variables using one of two SQL*Plus commands: • DEFINE: Create a CHAR datatype user variable • ACCEPT: Read usr input and store it a variable • If you need to predefine a variable that includes spaces, you must enclose the value within single quotation marks when using the DEFINE command

ACCEPT • Creates a customized prompt when accepting user input • Explicitly defines NUMBER or DATE datatype variable • Hides user input for security reasons ACCEPT variable [datatype] [FORMAT format] [PROMPT text] [HIDE] ACCEPT dept PROMPT ‘Provide the department name’ SELECT * FROM dept WHERE dname = UPPER(‘&dept’); Provide the department name: SalesDEPTNO DNAME LOC-------------------------------------------------------- 30 SALES HOBART

DEFINE and UNDEFINE • A variable remains defined until you either: • Use the UNDEFINE command to clear it • Exit SQL*Plus • You can verify your changes with the DEFINE command DEFINE variable = value Creates a CHAR datatype user variable and assigns a value to it DEFINE variable displays variable and its datatype DEFINE displays all user variables with value and datatype

DEFINE SQL> DEFINE deptname = sales SQL> DEFINE deptname DEFINE DEPTNAME = “sales” (CHAR) SQL> SELECT *2 FROM dept3 WHERE dname = UPPER(‘&deptname’); DEPTNO DNAME LOC-------------------------------------------------------- 30 SALES HOBART

Next Week • Report Format Commands • Manipulating Data • Creating Tables

Software Specification KXA233 Lecture 6A Subqueries and Interactive Operations