Transcript 486 Advanced SQL - University of Northern Colorado

Advanced SQL
Overview Advanced DDL, DML,
and DCL Commands
BACS 485—Database Management
Lecture Objectives


Review basic DDL, DML, and DCL SQL
commands used in Oracle
Learn advanced SQL commands useful for
DBA’s and database programmers
BACS 485—Database Management
Oracle SQL

DDL

Basic Object Manipulation Set




Create
Create
Create
Create
Table (Alter, Drop)
View (Alter, Drop)
Index (Alter, Drop)
Sequence (Alter, Drop)
BACS 485—Database Management
Create Table Commands
CREATE TABLE name (col-name type [(size)][CONSTRAINT],...);
CONSTRAINT name {PRIMARY KEY | UNIQUE | REFERENCES foreign_table
[(foreignfield)] }
Basic Create Table:
CREATE TABLE STUDENT
(STUID
CHAR (5) PRIMARY KEY,
SSN
CHAR (9),
LNAME
VARCHAR2 (25),
FNAME
VARCHAR2 (15),
MAJOR
VARCHAR2 (7),
CREDITS
NUMBER (1));
BACS 485—Database Management
Create Table Commands
CREATE TABLE scott.emp
(empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL
CONSTRAINT upper_ename CHECK (ename =
UPPER(ename)),
job VARCHAR2(9),
mgr NUMBER CONSTRAINT fk_mgr REFERENCES
scott.emp(empno) on delete cascade,
hiredate DATE DEFAULT SYSDATE,
sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500),
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL
CONSTRAINT fk_deptno REFERENCES scott.dept(deptno));
BACS 485—Database Management
Alter Table Commands
ALTER TABLE emp
ADD (CONSTRAINT sal_com_cc CHECK (sal + comm <=
5000))
DISABLE CONSTRAINT sal_com_cc;
ALTER TABLE EMP DROP (SSN) CASCADE CONSTRAINTS;
ALTER TABLE dept ADD CONSTRAINT manager_fk FOREIGN KEY
(manager) REFERENCES emp (mgr);
ALTER TABLE emp
ADD (thriftplan NUMBER(7,2),
loancode CHAR(1) NOT NULL);
BACS 485—Database Management
Create View Commands
CREATE VIEW dept20 AS
SELECT ename, sal*12 annual_salary
FROM emp
WHERE deptno = 20;
CREATE VIEW clerk (id_number, person, department, position) AS
SELECT empno, ename, deptno, job
FROM emp
WHERE job = ’CLERK’
WITH READ ONLY;
ALTER VIEW customer_view COMPILE;
DROP VIEW dept20;
BACS 485—Database Management
Index Commands
CREATE INDEX emp_idx ON scott.emp
(ename);
CREATE INDEX emp_i ON emp (UPPER(ename));
ALTER INDEX emp_ix REBUILD REVERSE;
DROP INDEX monolith;
BACS 485—Database Management
Sequence Command
CREATE SEQUENCE seq1 INCREMENT BY 10;
CREATE SEQUENCE acct_seq
INCREMENT BY 10 START WITH 100 NOMAXVALUE
NOCYCLE CACHE 10;
ALTER SEQUENCE seq1
MAXVALUE 1500;
DROP SEQUENCE elly.seq1;
BACS 485—Database Management
Oracle SQL

DML




Select
Insert
Update
Delete
BACS 485—Database Management
Select Commands
SELECT {field-list | * | DISTINCT field}
FROM table-list
WHERE expression
GROUP BY group-fields
HAVING group-expression
ORDER BY field-list;
Basic Select Example:
Select *
From Emp
Where Salary > 45000 and Status = “Full Time”
Order By SSN;
BACS 485—Database Management
More Complex Selects
SELECT deptno, MIN(sal), MAX (sal)
FROM emp
WHERE job = 'CLERK'
GROUP BY deptno
HAVING MIN(sal) < 1000;
Would print:
DEPTNO
---------20
30
MIN(SAL)
---------800
950
MAX(SAL)
---------1100
950
BACS 485—Database Management
SQL Aggregate Functions

AVG







Standard deviation of non-null values
NUMBER
VAR[IANCE]

Sum of non-null values
NUMBER
STD

Count of rows
All types
SUM

Maximum value
NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
NUM[BER]

Minimum value
NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING)
MAX[IMUM]

Count of non-null values
All types
MIN[IMUM]


NUMBER
COU[NT]

Average of non-null values
Variance of non-null values
NUMBER
BACS 485—Database Management
Other Select Operators

Logical Operators




And
Or
Not
Comparison Operator





=,<>,<,<=,>,>=
IS NULL
BETWEEN…AND
IN
LIKE
BACS 485—Database Management
Multi-Table Joins

Data from 2 or more tables can be
combined into a single select by several
methods.


Use ‘where’ clause to combine all data
Use Suq-query




Equi-join
Non-Equi-join
Outer Join
Self-Join
BACS 485—Database Management
Select Sub-Query

Sub-queries allow a component of a simple select to be an
embedded select statement.
SELECT Ename, sal
FROM emp
WHERE deptno =
(SELECT deptno
FROM dept
WHERE dname = 'ACCOUNTING');
BACS 485—Database Management
Sub-query operators

The following operators can be used to
select one or more tuples in a subquery.

Single row queries


=, <>, >,>=,<,<=
Multiple row queries



IN – equal to any values in a list
ALL – compare to all values in list
ANY – compare to each value in list
BACS 485—Database Management
Single Row Sub-Query
SELECT deptno, UPPER(ename), sal
FROM emp x
WHERE sal >
(SELECT AVG(sal)
FROM emp
WHERE x.deptno = deptno)
ORDER BY deptno;
BACS 485—Database Management
Multiple Row Query Operators




>ALL means more than the maximum value in list
<ALL means less than the minimum value in list
>ANY means less than the maximum value in list
<ANY means more than the minimum value in list

Note, from the diagram below, it is obvious that overlap is possible.
The key to understanding is to remember that these operators are
used to determine how a specific value relates to a list. Different
operators would produce different relations.
14
<Any
5
< All
> All
18
>Any
10
20
BACS 485—Database Management
30
Multiple Row Sub-queries
SELECT SSN, Lname FROM student WHERE FacID IN
(SELECT FacID FROM faculty WHERE deptID = 1);
SELECT empno, lname, fname, salary
FROM employee
WHERE salary >ANY
(SELECT salary FROM employee WHERE posID = 2)
AND posID <> 2;
SELECT empno, lname, fname, salary
FROM employee
WHERE salary <ALL
(SELECT AVG(salary) FROM employee GROUP BY deptID);
BACS 485—Database Management
Character Functions









Upper (col) – changes all to uppercase
Lower (col) – changes all to lowercase
Initcap (col) – first character of each word uppercase
Concat (col1,col2) – joins 1st value to 2nd value
Substr (col, x, y) – returns substring starting at ‘x’ for ‘y’ characters
Instr (col, c) – returns position of 1st ‘c’ character
Trim(‘c’ FROM col) – removes ‘c’ leading and trailing characters
Length (col) – returns length
Lpad(col,n,str) – pads value with ‘str’ to the left to total width of ‘n’
BACS 485—Database Management
Date Manipulation

Months_Between (date1,date2) –
dates









Number months between 2
Add_Months (date, m) – Add calendar months to a date
Next_Day (date, ‘day’) – Find next day after a date
Last_Day (date) – Find last day of the month
Round (date) – Round to nearest day, month, or year
Trunc (date) – Truncate date to nearest day, month, or year
Date + number – Add days to a date
Date – number – Subtract days from a date
Date + number/24 – Add hours to a date
Date1 – Date 2 – Find number of days between 2 dates
BACS 485—Database Management
Math Manipulation


Round (col, n) – Round column to n decimal places
Trunc (col, n) – Truncate the column to n decimal
places




Power (n,p) – returns np
Abs (n) – Absolute value of n
Mod (x,y) – integer remainder of x/y
+, -, *, / - perform basic mathematical operations
BACS 485—Database Management
Complex Select Sub-Queries
SELECT a.deptno "Department",
a.num_emp/b.total_count "%Employees",
a.sal_sum/b.total_sal "%Salary"
FROM
(SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
FROM scott.emp
GROUP BY deptno) a,
(SELECT COUNT(*) total_count, SUM(sal) total_sal
FROM scott.emp) b ;
BACS 485—Database Management
Complex Select Sub-Queries
SELECT Tablespace_Name, Max_Blocks, Block_Count, Total_Blocks_Free,
((Total_Blocks_Free/Total_Allocated_Blocks)*100) AS Pct_Free
FROM
(SELECT Tablespace_Name, SUM(Blocks) Total_Allocated_Blocks
FROM DBA_DATA_FILES
GROUP BY Tablespace_Name),
(SELECT Tablespace_Name Free_Space, MAX(Blocks) AS Max_Blocks,
COUNT(Blocks) AS Count_Blocks, SUM(Blocks) AS Total_Free_Blocks
FROM DBA_FREE_SPACE
GROUP BY Tablespace_Name)
WHERE Tablespace_Name = Free_Space;
BACS 485—Database Management
Complex Select Sub-Queries
Find the top 2 salaries in each department:
SELECT m.ename, m.sal, m.deptno
FROM emp m
WHERE m.sal >=
(SELECT DISTINCT o.sal
FROM emp 0
WHERE (o.sal, 2) IN (SELECT i.sal, ROWNUM
FROM (SELECT DSTINCT i2.sal r_sal,
i2.deptno, i2.sal, i2.ROWID
FROM emp i2) I
WHERE I.deptno = m.deptno))
ORDER BY deptno, sal DESC;
ENAME
----KING
CLARK
SCOTT
FORD
BLAKE
SAL
--5000
2450
3000
3000
2850
DEPTNO
----10
10
20
20
30
BACS 485—Database Management
Complex Select Sub-Queries
Find top 3 travel agents based on tickets sold:
SELECT id, ticket_price, agent_rank
FROM (SELECT agent_id AS id,
SUM(ticket_price) AS ticket_price,
RANK() OVER (ORDER BY SUM (ticket_price) DESC)
AS agent_rank
FROM invoice
GROUP BY agent_id)
WHERE agent_rank < 4;
ID
-1234
5675
4434
Ticket_Price
-----------8141
6708
5140
Agent_rank
---------1
2
3
BACS 485—Database Management
Select Self-Join
SELECT e1.ename || ‘ works for ’ || e2.ename
"Employees and their Managers"
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno;
Would Print:
Employees and their Managers
------------------------------BLAKE works for KING
CLARK works for KING
JONES works for KING
FORD works for JONES
SMITH works for FORD
ALLEN works for BLAKE
WARD works for BLAKE
…
BACS 485—Database Management
Select Outer Join
SELECT ename, job, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno (+) = dept.deptno;
SELECT custname, TO_CHAR(orderdate, ’MON-DD-YYYY’)
"ORDERDATE", partno, quantity
FROM customers, orders, lineitems
WHERE customers.custno = orders.custno (+)
AND orders.orderno = lineitems.orderno (+);
BACS 485—Database Management
Decode Statement
SELECT ename, deptno,
DECODE (deptno, 10, ‘ACCOUNTING’,
20, ‘RESEARCH’,
30, ‘SALES’,
‘NOT INDICATED’)
FROM emp;
ENAME
----TURNER
ALLEN
JONES
WARD
DEPTNO
-----30
20
10
20
DECODE(DEPTNO
------------SALES
RESEARCH
ACCOUNTING
RESEARCH
BACS 485—Database Management
Insert Commands
INSERT INTO dept
VALUES (50, ’PRODUCTION’, ’SAN FRANCISCO’);
INSERT INTO emp (empno, ename, job, sal, comm, deptno)
VALUES (7890, ’JINKS’, ’CLERK’, 1.2E3, NULL, 40);
INSERT INTO (SELECT ename, deptno FROM emp WHERE deptno < 10)
VALUES (’Taylor’, 20);
INSERT INTO bonus
SELECT ename, job, sal, comm
FROM emp
WHERE comm > 0.25 * sal
OR job IN (’PRESIDENT’, ’MANAGER’);
INSERT INTO emp VALUES (empseq.nextval, ’LEWIS’, ’CLERK’,
7902, SYSDATE, 1200, NULL, 20);
BACS 485—Database Management
Update Commands
UPDATE emp
SET comm = NULL WHERE job = ’TRAINEE’;
UPDATE emp
SET job = ’MANAGER’, sal = sal + 1000, deptno = 20
WHERE ename = ’JONES’;
UPDATE emp
SET sal = sal * 1.1 WHERE empno NOT IN
(SELECT empno FROM bonus);
BACS 485—Database Management
Complex Update Command
UPDATE emp a
SET deptno =
(SELECT deptno
FROM dept
WHERE loc = ’BOSTON’),
(sal, comm) =
(SELECT 1.1*AVG(sal), 1.5*AVG(comm)
FROM emp b
WHERE a.deptno = b.deptno)
WHERE deptno IN
(SELECT deptno
FROM dept
WHERE loc = ’DALLAS’ OR loc = ’DETROIT’);
BACS 485—Database Management
Delete Command
DELETE FROM temp_assign;
DELETE FROM emp
WHERE JOB = ’SALESMAN’
AND COMM < 100;
DELETE FROM (select * from emp)
WHERE JOB = ’SALESMAN’
AND COMM < 100;
BACS 485—Database Management
Oracle SQL

DCL


Commit
Rollback
BACS 485—Database Management
Commit
INSERT INTO dept VALUES (50, ’MARKETING’, ’TAMPA’);
COMMIT;
COMMIT
COMMENT ’In-doubt transaction Code 36, Call x1122’;
BACS 485—Database Management
Rollback
ROLLBACK;
ROLLBACK TO SAVEPOINT sp5;
BACS 485—Database Management