Advanced Oracle SQL: Query Overview and Syntax Review Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Introduction

Developers, database administrators and end users apply SELECT query to select data from a database that contains schemas with tables, views and functions. Oracle adds more capabilities than any other engine to the SQL language, such as Top-n queries, scalar subqueries, analytics, modeling and hundreds of functions from ABS to XMLTRANSFORM.

A single SELECT query sometimes contains more business logic than a package a thousand lines long. This chapter provides a review of the syntax of the various elements composing the query.

Syntax Review

SELECT and FROM

SELECT and FROM can be used to select columns from a table containing employee and salary information and might appear as follows:

SELECT
   ENAME,
   SAL
FROM
   EMP;

The results would look like:

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

All rows are selected; each name is displayed with the corresponding salary. In SQL*Plus, the execution plan can be displayed automatically by setting AUTOTRACE.

SET AUTOTRACE ON EXPLAIN

The execution plan of the query above looks like this:

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   140 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

The employee table used here is the one originally created for demonstration purposes by Bruce Scott, one of Oracle's first employees. The tables EMP and DEPT will be widely used in this book.

The Empdept.sql script below can be used to create the tables in a user schema:

-- Id     : $Id: empdept.sql,v 1.5 2015/12/07 11:05:31 lsc Exp $
-- Author : $Author: lsc $
-- Date   : $Date: 2015/12/07 11:05:31 $
--
-- Create EMP and DEPT in current schema
-- 

REM -- only create table and quota on default tablespace is needed
REM --
REM GRANT
REM    CREATE SESSION,
REM    CREATE TABLE,
REM    UNLIMITED TABLESPACE
REM TO
REM    SCOTT
REM IDENTIFIED BY
REM    TIGER;
REM CONNECT SCOTT/TIGER 

WHENEVER SQLERROR EXIT 

EXEC EXECUTE IMMEDIATE 'DROP TABLE EMP'; EXCEPTION WHEN OTHERS THEN NULL
EXEC EXECUTE IMMEDIATE 'DROP TABLE DEPT'; EXCEPTION WHEN OTHERS THEN NULL

-- create the department table

CREATE TABLE
   DEPT
(
   DEPTNO NUMBER(2)
      CONSTRAINT
         PK_DEPT
      PRIMARY KEY,
   DNAME VARCHAR2(14) ,
   LOC VARCHAR2(13)
)
/

-- create the employee table

CREATE TABLE
   EMP
(
   EMPNO NUMBER(4)
      CONSTRAINT
         PK_EMP
      PRIMARY KEY,
   ENAME VARCHAR2(10),
   JOB VARCHAR2(9),
   MGR NUMBER(4),
   HIREDATE DATE,
   SAL NUMBER(7,2),
   COMM NUMBER(7,2),
   DEPTNO NUMBER(2)
      CONSTRAINT
         FK_DEPTNO
      REFERENCES
         DEPT
)
/ 

-- fill the DEPT table

INSERT INTO
   DEPT
(
   DEPTNO,
   DNAME,
   LOC
)
VALUES
(
   10,
   'ACCOUNTING',
   'NEW YORK'
)
/
INSERT INTO
   DEPT
(
   DEPTNO,
   DNAME,
   LOC
)
VALUES
(
   20,
   'RESEARCH',
   'DALLAS'
)
/
INSERT INTO
   DEPT
(
   DEPTNO,
   DNAME,
   LOC
)
VALUES
(
   30,
   'SALES',
   'CHICAGO'
)
/
INSERT INTO
   DEPT
(
   DEPTNO,
   DNAME,
   LOC
)
VALUES
(
   40,
   'OPERATIONS',
   'BOSTON'
)
/

-- fill the EMP table

INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7369,
   'SMITH',
   'CLERK',
   7902,
   DATE '1980-12-17',
   800,
   NULL,
   20
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7499,
   'ALLEN',
   'SALESMAN',
   7698,
   DATE '1981-02-20',
   1600,
   300,
   30
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7521,
   'WARD',
   'SALESMAN',
   7698,
   DATE '1981-02-22',
   1250,
   500,
   30
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7566,
   'JONES',
   'MANAGER',
   7839,
   DATE '1981-04-02',
   2975,
   NULL,
   20
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7654,
   'MARTIN',
   'SALESMAN',
   7698,
   DATE '1981-09-28',
   1250,
   1400,
   30
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7698,
   'BLAKE',
   'MANAGER',
   7839,
   DATE '1981-05-01',
   2850,
   NULL,
   30
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7782,
   'CLARK',
   'MANAGER',
   7839,
   DATE '1981-06-09',
   2450,
   NULL,
   10
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7788,
   'SCOTT',
   'ANALYST',
   7566,
   DATE '1987-04-19',
   3000,
   NULL,
   20
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7839,
   'KING',
   'PRESIDENT',
   NULL,
   DATE '1981-11-17',
   5000,
   NULL,
   10
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7844,
   'TURNER',
   'SALESMAN',
   7698,
   DATE '1981-09-08',
   1500,
   0,
   30
)
/
INSERT INTO
   EMP
(   EMPNO,
    ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7876,
   'ADAMS',
   'CLERK',
   7788,
   DATE '1987-05-23',
   1100,
   NULL,
   20
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7900,
   'JAMES',
   'CLERK',
   7698,
   DATE '1981-12-03',
   950,
   NULL,
   30
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7902,
   'FORD',
   'ANALYST',
   7566,
   DATE '1981-12-03',
   3000,
   NULL,
   20
)
/
INSERT INTO
   EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
VALUES
(
   7934,
   'MILLER',
   'CLERK',
   7782,
   DATE '1982-01-23',
   1300,
   NULL,
   10
)
/
COMMIT;