sql scripting sorcerypresentation
DESCRIPTION
oracle foreign key primary key constraints performance tuning MTS IOT 9i block size backup rman corrupted column drop rename recovery controlfile backup clone architecture database archives export dump dmp duplicate rows extents segments fragmentation hot cold blobs migration tablespace locally managed redo undo new features rollback ora-1555 shrink free space user password link TNS tnsnames.ora listener java shutdown sequenceTRANSCRIPT
![Page 1: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/1.jpg)
SQL Scripting Sorcery
RMOUG Training Days 2002Denver, CO
![Page 2: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/2.jpg)
Overview
• Basic Concepts• Inline Query• DECODE• CASE• Stupid SQL Tricks• Special Surprise
![Page 3: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/3.jpg)
Row Source
• Access Path• Result Set• ROWNUM pseudocolumn
– Order in result set, before explicit sorting– After predicate is applied
• ‘WHERE’ clause
![Page 4: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/4.jpg)
Row Source
• All data is retrieved from a row source• May be a base object or the result set from a
previous operation• Oracle will use no more than 2 row sources
for any single operation• Result set may be row source for other
operations
![Page 5: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/5.jpg)
Access Paths
• What is an access path• How access paths affect result sets• Using different access paths
![Page 6: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/6.jpg)
Access Paths
EMP PK_EMPNO
![Page 7: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/7.jpg)
How Access Paths Affect Result Sets
• Table– Physical Order in datafile(s)– ROWID order
• Index– ‘Sorted’ order– Even if table is accessed
• Other– Order of result set
![Page 8: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/8.jpg)
Using Different Access Paths
• Using/Suppressing an Index• Hints• Join Order
![Page 9: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/9.jpg)
Result Sets
• The output of an operation• Records are processed as a group
![Page 10: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/10.jpg)
Sorting the Result Set
• Explicit Sorting– ORDER BY
• Implicit Sorting– DISTINCT– GROUP BY– UNION/INTERSECT/MINUS
![Page 11: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/11.jpg)
Inline Query
• What is an Inline Query?• Hierarchical Query Joins• Top N Queries• Sampling
![Page 12: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/12.jpg)
What is an Inline Query?
• FROM clause contains a ‘SELECT’ statement instead of a table or view– New in 7.2
• Cannot contain ORDER BY– Now Allowed in 8i
• Predicate cannot be dependent upon external condition– Cannot be a correlated subquery
![Page 13: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/13.jpg)
Inline Query
select e2.ename2,
e2.empno2
from (select ename ename2,
empno empno2
from emp) e2;
![Page 14: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/14.jpg)
Hierarchical Query Joins
• Basic Hierarchical Query uses one and only one table
• Join cannot occur in Hierarchical Query• Push query to inline query…it becomes just
another result set!
![Page 15: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/15.jpg)
Base Hierarchical QuerySELECT level,
LPAD(' ',2*level-2)||emp.ename ename,
emp.empno,
emp.mgr,
emp.deptno
FROM emp
CONNECT BY PRIOR emp.empno = emp.mgr
START WITH emp.mgr is null;
LEVEL ENAME EMPNO MGR DEPTNO
---------- --------------- ---------- ---------- ----------
1 KING 7839 10
2 JONES 7566 7839 20
3 SCOTT 7788 7566 20
4 ADAMS 7876 7788 20
3 FORD 7902 7566 20
2 BLAKE 7698 7839 30
3 ALLEN 7499 7698 30
![Page 16: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/16.jpg)
Joining a Hierarchical Query
SELECT level,
LPAD(' ',2*level-2)||emp.ename ename,
emp.empno,
emp.mgr,
dept.dname
FROM emp, dept
where emp.deptno = dept.deptno
CONNECT BY PRIOR emp.empno = emp.mgr
START WITH emp.mgr is null;
FROM emp, dept
*
ERROR at line 2:
ORA-01437: cannot have join with CONNECT BY
![Page 17: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/17.jpg)
Joining A Hierarchical Queryselect e.e_level,
e.ename,
e.empno,
e.mgr,
d.dname
from dept d,
(SELECT level e_level,
LPAD(' ',2*level-2)||emp.ename ename,
emp.empno empno,
emp.mgr mgr,
emp.deptno deptno
FROM emp
CONNECT BY PRIOR emp.empno = emp.mgr
START WITH emp.mgr is null) e
where e.deptno = d.deptno;
![Page 18: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/18.jpg)
Joining A Hierarchical Query
E_LEVEL ENAME EMPNO MGR DNAME
------- -------------- ---------- ---------- --------------
1 KING 7839 ACCOUNTING
2 CLARK 7782 7839 ACCOUNTING
3 MILLER 7934 7782 ACCOUNTING
2 JONES 7566 7839 RESEARCH
3 SCOTT 7788 7566 RESEARCH
4 ADAMS 7876 7788 RESEARCH
3 FORD 7902 7566 RESEARCH
![Page 19: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/19.jpg)
Top N Queries
• What are the Top N values?• Rownum and result set are key• New features introduced in Oracle8i
– ORDER BY in inline query
![Page 20: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/20.jpg)
Top N Values
• Limited number of records based upon a value or set of values– Top 2 salaries in the company– Top 2 salaries in each department– What happens in the case of a tie?
• Implemented in other platforms
![Page 21: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/21.jpg)
Top 2 Salaries
Oracle 8.0.6select ename,
empno,
sal,
deptno,
hiredate
from (select distinct 0-sal,
ename,
empno,
sal,
deptno,
hiredate
from emp)
where rownum <= 2;
Oracle 8.1.6select *
from (select ename,
empno,
sal,
deptno,
hiredate
from emp
order by sal desc)
where rownum <= 2;
![Page 22: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/22.jpg)
Top 2 Salaries
ENAME EMPNO SAL DEPTNO HIREDATE
---------- ---------- ---------- ---------- -----------
KING 7839 5000 10 17-NOV-1981
SCOTT 7788 3000 20 09-DEC-1982
• Only 2 records returned• ….but there are 2 employees with $3000
salary• Should 3 records be returned?
![Page 23: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/23.jpg)
Top 2 Salaries, part 2
Oracle 8.0.6select ename,
empno,
sal,
deptno,
hiredate
from emp
where sal >=
(select min(i_sal)
from (select
distinct 0-sal r_sal,
sal i_sal
from emp)
where rownum <= 2);
Oracle 8.1.6select ename,
empno,
sal,
deptno,
hiredate
from emp
where sal >=
(select min(i_sal)
from
(select sal i_sal
from emp
order by i_sal desc)
where rownum <= 2);
![Page 24: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/24.jpg)
Top 2 Salaries
ENAME EMPNO SAL DEPTNO HIREDATE
---------- ---------- ---------- ---------- -----------
KING 7839 5000 10 17-NOV-1981
FORD 7902 3000 20 03-DEC-1981
SCOTT 7788 3000 20 09-DEC-1982
• 3 records are returned• ….for the top 2 salaries• Is this the correct result?
![Page 25: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/25.jpg)
Top 2 Salaries per Department
• Extraction of data is dependent upon data within query
• Move inline query into correlated subquery
![Page 26: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/26.jpg)
Top 2 Salaries per Departmentselect m.ename,
m.sal,
m.deptno
from emp m
where m.sal >=
(select distinct o.sal
from emp o
where (o.sal,2) in
(select i.sal,
rownum
from (select
distinct 0-i2.sal r_sal,
i2.deptno,
i2.sal,
i2.rowid
from emp i2) i
where i.deptno = m.deptno))
order by deptno, sal desc;
select m.ename,
m.sal,
m.deptno
from emp m
where m.sal >=
(select distinct o.sal
from emp o
where (o.sal,2) in
(select i.sal,
rownum
from (select
i2.deptno,
i2.sal
from emp i2
order by i2.sal desc,
i2.deptno) i
where i.deptno = m.deptno))
order by deptno, sal desc;
![Page 27: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/27.jpg)
Top 2 Salaries per Department
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
CLARK 2450 10
SCOTT 3000 20
FORD 3000 20
BLAKE 2850 30
ALLEN 1600 30
TURNER 1600 30
![Page 28: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/28.jpg)
RANK
• Added in Oracle8i– Added for analytical functions
• Will include ties, but will skip ranks• Can this be used for Top N Queries?
![Page 29: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/29.jpg)
RANK
SELECT deptno, ename, sal, comm,
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC, comm) as rk
FROM emp
where RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC, comm) <= 2;
where RANK() OVER (PARTITION BY deptno ORDER BY salDESC, comm) <= 2
*
ERROR at line 4:
ORA-30483: window functions are not allowed here
![Page 30: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/30.jpg)
RANKselect *
from (SELECT deptno, ename, sal, comm,
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC, comm) as rk
FROM emp )
where rk <=2;
DEPTNO ENAME SAL COMM RK
---------- ---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
20 JONES 3000 1
20 FORD 3000 1
30 BLAKE 3000 1
30 ALLEN 1600 300 2
![Page 31: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/31.jpg)
DECODE
• Deconstructing DECODE• Cross-tab reporting• Conditional DML• Simple menu• Duplicating a CASE function
![Page 32: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/32.jpg)
Deconstructing Decode
• DECODE is a crude decision-making construct
• Implement– IF-THEN-ELSE– CASE
• Comparison must be equality– One or more values
![Page 33: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/33.jpg)
Deconstructing DecodeSELECT ename,
deptno,
DECODE(deptno,
10, ‘ACCOUNTING’,
20, ‘RESEARCH’,
30, ‘SALES’,
‘NOT INDICATED’)
FROM emp;
IF DEPTNO = 10THEN ‘ACCOUNTING’ELSE IF DEPTNO = 20THEN ‘RESEARCH’ELSE IF DEPTNO = 30
THEN ‘SALES’ELSE ‘NOT INDICATED’
ENAME DEPTNO DECODE(DEPTNO
--------- ---------- -------------
TURNER 30 SALES
ALLEN 30 SALES
WARD 30 SALES
ADAMS 20 RESEARCH
JONES 20 RESEARCH
MARTIN 30 SALES
CLARK 10 ACCOUNTING
![Page 34: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/34.jpg)
Cross-Tab Reports
• Spreadsheet type report– Down and Across– Static– Summary
• Each column is processed multiple times– ‘Ignore’ values not required
• GROUP BY important
![Page 35: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/35.jpg)
Cross-Tab ReportsSELECT deptno DEPTNO,
SUM(DECODE(to_char(hiredate, 'YYYY'),
'1980', SAL+(NVL(COMM,0)),
0)) YR_1980,
SUM(DECODE(to_char(hiredate, 'YYYY'),
'1981', SAL+(NVL(COMM,0)),
0)) YR_1981,
SUM(DECODE(to_char(hiredate, 'YYYY'),
'1982', SAL+(NVL(COMM,0)),
0)) YR_1982,
SUM(DECODE(to_char(hiredate, 'YYYY'),
'1983', SAL+(NVL(COMM,0)),
0)) YR_1983
FROM EMP
GROUP BY deptno;
DEPTNO YR_1980 YR_1981 YR_1982 YR_1983
---------- ---------- ---------- ---------- ----------
10 0 7450 1300 0
20 800 5975 3000 1100
30 0 11600 0 0
![Page 36: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/36.jpg)
Conditional DML Dynamic Select
• Select different column based on dynamic values
• Same table, different columns• Different table, different columns
– Traditional – UNION– Cool – OUTER JOIN and DECODE
![Page 37: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/37.jpg)
Conditional DML Dynamic Select
select s.segment_name,
s.segment_type,
decode(s.segment_type,
'INDEX', i.last_analyzed,
'TABLE', t.last_analyzed) analyze_date
from user_segments s,
user_tables t,
user_indexes i
where s.segment_name = t.table_name (+)
and s.segment_name = i.index_name (+);
SEGMENT_NAME SEGMENT_TYPE ANALYZE_DATE
------------------------------ ----------------- ------------
DEPT TABLE 06-DEC-2000
EMP TABLE 06-DEC-2000
IX_DEPTNO INDEX 06-DEC-2000
IX_DNAME INDEX 06-DEC-2000
IX_ENAME INDEX 06-DEC-2000
![Page 38: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/38.jpg)
Conditional DMLDynamic Update
• Updating records based on dynamic value• Multiple update statements?• Single DECODE statement
– Use GREATEST and LEAST to simulate Range Check
![Page 39: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/39.jpg)
Conditional DMLDynamic Update
select ename,
hiredate,
greatest(hiredate, to_date('01-APR-1981')) gt_date,
least(hiredate, to_date('01-APR-1982')) lt_date,
decode(greatest(hiredate, to_date('01-APR-1981')),
least(hiredate, to_date('01-APR-1982')), .1,
.05) pay_raise
from emp;
ENAME HIREDATE GT_DATE LT_DATE PAY_RAISE
---------- ----------- ----------- ----------- ----------
ALLEN 20-FEB-1981 01-APR-1981 20-FEB-1981 .05
WARD 22-FEB-1981 01-APR-1981 22-FEB-1981 .05
JONES 02-APR-1981 02-APR-1981 02-APR-1981 .1
FORD 03-DEC-1981 03-DEC-1981 03-DEC-1981 .1
MILLER 23-JAN-1982 23-JAN-1982 23-JAN-1982 .1
SCOTT 09-DEC-1982 09-DEC-1982 01-APR-1982 .05
ADAMS 12-JAN-1983 12-JAN-1983 01-APR-1982 .05
![Page 40: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/40.jpg)
Conditional DMLDynamic Update
• Use CASE instead– But we haven’t learned that yet!
• Available Oracle 8.1.6• Much Cleaner Code
![Page 41: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/41.jpg)
Conditional DMLDynamic Update
select ename,hiredate,
CASE when TRUNC(hiredate)between ’01-APR-1981’
and ’31-MAR-1982’
then 0.1else
then 0.05END pay_raise
from emp;
![Page 42: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/42.jpg)
CASE
• Finally, a real CASE function!• New in Oracle8i
– ANSI-standard• Limit of 128 comparisons• Nonequality comparisons
![Page 43: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/43.jpg)
CASE
DECODE ApproachSELECT ename,
deptno,
DECODE(deptno,
10, ‘ACCOUNTING’,
20, ‘RESEARCH’,
30, ‘SALES’,
‘NOT INDICATED’)
FROM emp;
CASE ApproachSELECT ename,
deptno,
CASE WHEN deptno = 10
THEN 'ACCOUNTING'
WHEN deptno = 20
THEN 'RESEARCH'
WHEN deptno = 30
THEN 'SALES'
ELSE 'NOT INDICATED'
END
FROM emp;
![Page 44: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/44.jpg)
CASE
• Comparisons– Any Valid Oracle Expression – Do not have to be equality
![Page 45: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/45.jpg)
Decode Approach
select ename,
sal,
decode(sign(sal-1000),
-1, 'Low Pay',
decode(sign(sal-2500),
-1, 'Medium Pay',
decode(sign(sal-4000),
-1, 'High Pay',
'DBA Pay')))pay_scale
from emp
![Page 46: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/46.jpg)
Case Approach
select ename,
sal,
CASE when sal between 0 and 999
then 'Low Pay'
when sal between 1000 and 2500
then 'Medium Pay'
when sal between 2500 and 4000
then 'High Pay'
when sal > 4001
then 'DBA Pay'
END compensation
from emp
![Page 47: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/47.jpg)
CASE
• May be nestedselect job,
comm,
sal,
CASE when comm is not null
then (CASE when comm > 100
then sal + comm
when comm <= 100
then sal + 100
END)
when job = 'CLERK'
then sal
else sal * 1.1
END compensation
from emp
![Page 48: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/48.jpg)
Abandon Hope All Ye Who Enter Here
![Page 49: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/49.jpg)
I’m Not Kidding…You Aren’t Ready For What
Comes Next!
![Page 50: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/50.jpg)
No Looking Ahead…Turn Back NOW!
![Page 51: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/51.jpg)
Column Level Selects
• Can you use a select statement in the column list or expression?
![Page 52: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/52.jpg)
Using SELECT as an Expression
• What values can be returned from a column?– Data– Literal– Result of an Expression– Result of a SELECT statement
![Page 53: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/53.jpg)
Basic Construct
select (select count(*)
from emp) emp_count
from dual;
EMP_COUNT
----------
8
![Page 54: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/54.jpg)
Basic Construct
select (select count(*)
from emp) emp_count,
(select count(*)
from dept) dept_count
from dual;
EMP_COUNT DEPT_COUNT
---------- -----------
8 4
![Page 55: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/55.jpg)
Basic Construct
• Subquery can return one and only one row• Subquery can return one and only one
column– Multiple columns may be concatenated
• The main query will return as many rows as there are in the main table
![Page 56: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/56.jpg)
Basic Construct
• Can– restrict using a WHERE– use a GROUP BY– restrict using a HAVING– join multiple tables– be a correlated subquery
![Page 57: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/57.jpg)
Selecting Multiple Values
select (select dname||loc
from dept
where deptno = 10)dname_loc
from dual;
DNAME_LOC
---------------------------
ACCOUNTINGNEW YORK
![Page 58: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/58.jpg)
Joining Tables
select (select count(e1.empno)
from emp e1,
dept d1
where d1.loc = 'NEW YORK'
and d1.deptno = e1.deptno)
ny_emps
from dual
![Page 59: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/59.jpg)
Using as an expression
select s.segment_name,
s.segment_type,
decode(s.segment_type,
'TABLE', (select t.last_analyzed
from user_tables t
where t.table_name = s.segment_name),
'INDEX', (select i.last_analyzed
from user_indexes i
where i.index_name = s.segment_name),
NULL) date_analyzed
from user_segments s
![Page 60: Sql scripting sorcerypresentation](https://reader034.vdocuments.us/reader034/viewer/2022051610/54926353b47959384d8b45d3/html5/thumbnails/60.jpg)
Using as an expression
select s.segment_name,
s.segment_type,
case when s.segment_type = 'TABLE'
then (select t.last_analyzed
from user_tables t
where t.table_name = s.segment_name)
when s.segment_type = 'INDEX'
then (select i.last_analyzed
from user_indexes i
where i.index_name = s.segment_name)
else NULL
end date_analyzed
from user_segments s