Advanced SQL - PowerPoint PPT Presentation

1 / 37

About This Presentation

Title:

Advanced SQL

Description:

Review basic DDL, DML, and DCL SQL commands used in Oracle. Learn advanced SQL commands useful for DBA's and database programmers ... – PowerPoint PPT presentation

Number of Views:80

Avg rating:3.0/5.0

Slides: 38

Provided by: jaylig

Category:

more less

Transcript and Presenter's Notes

Title: Advanced SQL

1
Advanced SQL

Overview Advanced DDL, DML, and DCL Commands

2
Lecture Objectives

Review basic DDL, DML, and DCL SQL commands used
in Oracle
Learn advanced SQL commands useful for DBAs and
database programmers

3
Oracle SQL

DDL
Basic Object Manipulation Set
Create Table (Alter, Drop)
Create View (Alter, Drop)
Create Index (Alter, Drop)
Create Sequence (Alter, Drop)

4
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))

5
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 gt
500),
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL
CONSTRAINT fk_deptno REFERENCES
scott.dept(deptno))

6
Alter Table Commands

ALTER TABLE emp
ADD (CONSTRAINT sal_com_cc CHECK (sal comm lt
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)

7
Create View Commands

CREATE VIEW dept20 AS
SELECT ename, sal12 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

8
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

9
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

10
Oracle SQL

DML
Select
Insert
Update
Delete

11
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 gt 45000 and Status Full Time
Order By SSN

12
More Complex Selects

SELECT deptno, MIN(sal), MAX (sal)
FROM emp
WHERE job 'CLERK'
GROUP BY deptno
HAVING MIN(sal) lt 1000
Would print
DEPTNO MIN(SAL) MAX(SAL)
---------- ---------- ----------
20 800 1100
30 950 950

13
SQL Aggregate Functions

AVG Average of non-null values
NUMBER
COUNT Count of non-null values
All types
MINIMUM Minimum value
NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR),
NVARCHAR2 (NCHAR VARYING)
MAXIMUM Maximum value
NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR),
NVARCHAR2 (NCHAR VARYING)
NUMBER Count of rows
All types
SUM Sum of non-null values
NUMBER
STD Standard deviation of non-null values
NUMBER
VARIANCE Variance of non-null values
NUMBER

14
Other Select Operators

Logical Operators
And
Or
Not
Comparison Operator
,ltgt,lt,lt,gt,gt
IS NULL
BETWEENAND
IN
LIKE

15
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

16
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')

17
Sub-query operators

The following operators can be used to select one
or more tuples in a sub-query.
Single row queries
, ltgt, gt,gt,lt,lt
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

18
Single Row Sub-Query

SELECT deptno, UPPER(ename), sal
FROM emp x
WHERE sal gt
(SELECT AVG(sal)
FROM emp
WHERE x.deptno deptno)
ORDER BY deptno

19
Multiple Row Query Operators

gtALL means more than the maximum value in list
ltALL means less than the minimum value in list
gtANY means less than the maximum value in list
ltANY 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.

20
Multiple Row Sub-queries