SQL Notes

1. SQL and RDBMS

SQL (Structured Query Language) is the standard language used to store, manipulate and retrieve data from relational databases.

Common RDBMS products include MySQL, Oracle, SQL Server, PostgreSQL, IBM DB2 and Microsoft Access.

Query

A query is a command/instruction used to retrieve, insert, update or delete data in a database.

RDBMS

RDBMS stands for Relational Database Management System. It stores related data in tables consisting of rows and columns.

Basic table terminology

2. SQL Command Categories

3. CREATE TABLE and INSERT

Create table

CREATE TABLE student (
    no NUMBER(2),
    name VARCHAR2(10),
    marks NUMBER(3)
);

Insert using values

INSERT INTO student VALUES (1, 'Sudha', 100);
INSERT INTO student VALUES (2, 'Saketh', 200);

Insert using substitution/address variables

INSERT INTO student VALUES (&no, '&name', &marks);

In SQL*Plus, substitution variables prompt the user for values. The / command can execute the statement again with new values.

4. SELECT — Retrieving Data

SELECT * FROM student;

SELECT no, name, marks
FROM student;

* represents all columns. Specific column names can be supplied when only selected columns are required.

5. WHERE Clause and Operators

The WHERE clause filters rows according to a condition.

SELECT * FROM student
WHERE no = 2;

Operator groups

• Arithmetic: +, -, *, /
• Comparison: =, !=, >, <, >=, <=, <>
• Range/list/null/pattern: BETWEEN, IN, IS NULL, LIKE
• Logical: AND, OR, NOT

6. WHERE Examples — AND, OR, BETWEEN and IN

-- AND: all conditions must be true
SELECT * FROM student
WHERE no = 2 AND marks >= 200;

-- OR: at least one condition is true
SELECT * FROM student
WHERE no = 2 OR marks >= 200;

-- BETWEEN: inclusive range
SELECT * FROM student
WHERE marks BETWEEN 200 AND 400;

-- NOT BETWEEN
SELECT * FROM student
WHERE marks NOT BETWEEN 200 AND 400;

-- IN
SELECT * FROM student
WHERE no IN (1, 2, 3);

-- NOT IN
SELECT * FROM student
WHERE no NOT IN (1, 2, 3);

7. NULL and LIKE

NULL

SELECT * FROM student WHERE marks IS NULL;
SELECT * FROM student WHERE marks IS NOT NULL;

Use IS NULL and IS NOT NULL rather than ordinary equality operators for NULL checks.

LIKE

LIKE searches according to a pattern. % represents zero or more characters and _ represents one character.

-- Starts with S
SELECT * FROM student WHERE name LIKE 'S%';

-- Ends with h
SELECT * FROM student WHERE name LIKE '%h';

-- Second character is a
SELECT * FROM student WHERE name LIKE '_a%';

-- Third character is d
SELECT * FROM student WHERE name LIKE '__d%';

-- Contains t with at least one character before it
SELECT * FROM student WHERE name LIKE '%_t%';

8. ORDER BY

ORDER BY sorts query results. Ascending order is the default; DESC gives descending order.

SELECT * FROM student ORDER BY no;

SELECT * FROM student ORDER BY no DESC;

9. UPDATE and DELETE

UPDATE

UPDATE student
SET marks = 500;

UPDATE student
SET marks = 500
WHERE no = 2;

UPDATE student
SET marks = 500, name = 'Venu'
WHERE no = 1;

If no WHERE condition is supplied, the update applies to all rows.

DELETE

DELETE FROM student;

DELETE FROM student
WHERE no = 2;

Without a condition, all rows are deleted.

10. ALTER TABLE

ALTER TABLE changes an existing table structure.

-- Add column
ALTER TABLE student ADD sdob DATE;

-- Drop column
ALTER TABLE student DROP COLUMN sdob;

-- Modify datatype/precision
ALTER TABLE student MODIFY marks NUMBER(5);

-- Mark column unused
ALTER TABLE student SET UNUSED COLUMN marks;

-- Drop all unused columns
ALTER TABLE student DROP UNUSED COLUMNS;

-- Rename column
ALTER TABLE student
RENAME COLUMN marks TO smarks;

11. TRUNCATE, DROP and RENAME

TRUNCATE TABLE student;

DROP TABLE student;

RENAME student TO stud;

12. CREATE TABLE AS SELECT and INSERT SELECT

CREATE TABLE AS SELECT (CTAS)

CREATE TABLE student1 AS
SELECT * FROM student;

CREATE TABLE student2(sno, sname, smarks) AS
SELECT * FROM student;

CREATE TABLE student3 AS
SELECT no, name FROM student;

-- Structure without data
CREATE TABLE student4 AS
SELECT * FROM student WHERE 1 = 2;

INSERT SELECT

INSERT INTO student1
SELECT * FROM student;

INSERT INTO student1(no, name)
SELECT no, name FROM student;

13. Column and Table Aliases

Column alias

SELECT no AS sno
FROM student;

SELECT no "sno"
FROM student;

Table alias

SELECT s.no, s.name
FROM student s;

Table aliases make qualified column references shorter and are especially useful in joins.

14. Group Functions

Group/aggregate functions process multiple rows and return a summarized value.

15. Constraints

Constraints enforce rules on table data.

Constraints can be defined at column level, table level, or by using ALTER TABLE where supported.

16. NOT NULL and CHECK

NOT NULL

Prevents a column from containing NULL values. It is defined at column level.

CREATE TABLE student(
    no NUMBER(2) NOT NULL,
    name VARCHAR2(10),
    marks NUMBER(3)
);

CHECK

Restricts values according to a condition.

CREATE TABLE student(
    no NUMBER(2),
    name VARCHAR2(10),
    marks NUMBER(3),
    CONSTRAINT ch CHECK (marks > 300)
);

17. UNIQUE and PRIMARY KEY

UNIQUE

A UNIQUE constraint prevents duplicate values while allowing NULL values according to the database's rules.

CREATE TABLE student(
    no NUMBER(2) CONSTRAINT un UNIQUE,
    name VARCHAR2(10),
    marks NUMBER(3)
);

ALTER TABLE student ADD CONSTRAINT un UNIQUE(no);

PRIMARY KEY

A primary key uniquely identifies a row and combines the concepts of uniqueness and non-nullability.

CREATE TABLE student(
    no NUMBER(2) CONSTRAINT pk PRIMARY KEY,
    name VARCHAR2(10),
    marks NUMBER(3)
);

ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY(no);

18. FOREIGN KEY and ON DELETE CASCADE

A foreign key references a key in a parent table and is defined on the child table.

CREATE TABLE emp(
    empno NUMBER(2),
    ename VARCHAR2(10),
    deptno NUMBER(2),
    CONSTRAINT pk PRIMARY KEY(empno),
    CONSTRAINT fk FOREIGN KEY(deptno)
        REFERENCES dept(deptno)
);

By default, a parent row referenced by existing child rows cannot be removed. ON DELETE CASCADE can be used when dependent child rows should be removed automatically with the parent.

CREATE TABLE emp(
    empno NUMBER(2),
    ename VARCHAR2(10),
    deptno NUMBER(2),
    CONSTRAINT pk PRIMARY KEY(empno),
    CONSTRAINT fk FOREIGN KEY(deptno)
        REFERENCES dept(deptno)
        ON DELETE CASCADE
);

19. Composite Keys

A composite key is a constraint defined using a combination of two or more columns.

CREATE TABLE student(
    no NUMBER(2),
    name VARCHAR2(10),
    marks NUMBER(3),
    CONSTRAINT un UNIQUE(no, name)
);

CREATE TABLE student(
    no NUMBER(2),
    name VARCHAR2(10),
    marks NUMBER(3),
    CONSTRAINT pk PRIMARY KEY(no, name)
);

CREATE TABLE emp(
    empno NUMBER(2),
    ename VARCHAR2(10),
    deptno NUMBER(2),
    dname VARCHAR2(10),
    CONSTRAINT pk PRIMARY KEY(empno),
    CONSTRAINT fk FOREIGN KEY(deptno, dname)
        REFERENCES dept(deptno, dname)
);

20. Deferrable Constraints

Deferrable constraints can defer constraint checking.

CREATE TABLE student(
    no NUMBER(2),
    name VARCHAR2(10),
    CONSTRAINT un UNIQUE(no)
        DEFERRABLE INITIALLY DEFERRED
);

SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS ALL DEFERRED;

21. GROUP BY and HAVING

GROUP BY creates groups of related rows. Aggregate functions can then calculate a result for each group.

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY deptno, job;

HAVING filters groups after aggregation.

SELECT deptno, job, SUM(sal) AS tsal
FROM emp
GROUP BY deptno, job
HAVING SUM(sal) > 3000
ORDER BY job;

22. Set Operators

SELECT * FROM student1
UNION
SELECT * FROM student2;

SELECT * FROM student1
UNION ALL
SELECT * FROM student2;

SELECT * FROM student1
INTERSECT
SELECT * FROM student2;

SELECT * FROM student1
MINUS
SELECT * FROM student2;

23. Views

A view is a logical/virtual representation of query results. It stores the query definition rather than its own copy of the base table data.

Why use views?

• Restrict access to selected rows/columns.
• Hide data complexity.
• Simplify queries for users.

CREATE VIEW dept_v AS
SELECT * FROM dept
WITH READ ONLY;

CREATE VIEW dept_v AS
SELECT deptno, SUM(sal) AS t_sal
FROM emp
GROUP BY deptno;

CREATE VIEW stud AS
SELECT ROWNUM no, name, marks
FROM student;

24. Views with DML and CHECK OPTION

DML support depends on the view definition. Views involving expressions, certain functions, grouping or multiple tables may restrict INSERT/UPDATE operations.

FORCE view

CREATE FORCE VIEW stud AS
SELECT * FROM student;

The view can be created before the base table exists and becomes valid when the required object is available.

WITH CHECK OPTION

CREATE VIEW stud AS
SELECT * FROM student
WHERE marks = 500
WITH CHECK OPTION CONSTRAINT ck;

The check option ensures DML through the view respects its defining condition.

Drop a view

DROP VIEW dept_v;

25. Synonyms

A synonym is an alias for a database object such as a table, view or sequence.

CREATE SYNONYM s1 FOR emp;
CREATE PUBLIC SYNONYM s2 FOR emp;

DROP SYNONYM s1;

26. Sequences

A sequence is a database object that generates sequential numeric values and is commonly used for unique identifiers.

CREATE SEQUENCE s;

CREATE SEQUENCE s
    INCREMENT BY 10
    START WITH 100
    MINVALUE 5
    MAXVALUE 200
    CYCLE
    CACHE 20;

Using a sequence

CREATE TABLE student(no NUMBER(10), name VARCHAR2(10));

INSERT INTO student
VALUES (s.NEXTVAL, 'Saketh');

NEXTVAL generates the next sequence value. After a value has been generated in a session, CURRVAL represents the current sequence value for that session.

Alter and drop

ALTER SEQUENCE s INCREMENT BY 2;
ALTER SEQUENCE s CACHE 10;

DROP SEQUENCE s;

27. Database Objects

The notes list the following common database objects:

28. Joins — Overview and INNER JOIN

A join combines related data from multiple tables using a join condition.

INNER JOIN

Returns rows where the join condition matches in both tables.

SELECT suppliers.supplier_id,
       suppliers.supplier_name,
       orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

The result contains only the intersection of matching supplier IDs. The notes also show the older implicit form:

SELECT suppliers.supplier_id,
       suppliers.supplier_name,
       orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

29. OUTER JOINS

LEFT OUTER JOIN

Returns all rows from the left table and matching rows from the right table. Non-matching right-side columns become NULL.

SELECT suppliers.supplier_id,
       suppliers.supplier_name,
       orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

RIGHT OUTER JOIN

Returns all rows from the right table and matching rows from the left table.

SELECT orders.order_id,
       orders.order_date,
       suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

FULL OUTER JOIN

Returns all rows from both tables, using NULL where there is no match.

SELECT suppliers.supplier_id,
       suppliers.supplier_name,
       orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

30. SELF, NATURAL and CROSS JOIN

SELF JOIN

A table is joined with itself, often to represent relationships such as employee-to-manager.

SELECT e1.empno, e2.ename, e1.job, e2.deptno
FROM emp e1, emp e2
WHERE e1.empno = e2.mgr;

NATURAL JOIN

Automatically joins tables using columns with the same names and compatible types.

SELECT empno, ename, job, dname, loc
FROM emp NATURAL JOIN dept;

CROSS JOIN

Produces the Cartesian product: every row of the first table is combined with every row of the second.

SELECT empno, ename, job, dname, loc
FROM emp CROSS JOIN dept;

31. Subqueries

A subquery is a query nested inside another query. The containing statement is called the parent query.

Single-row subquery

Returns one value.

SELECT * FROM emp
WHERE sal > (
    SELECT sal FROM emp
    WHERE empno = 7566
);

Multi-row subquery

Returns multiple values and can be combined with operators such as IN, ANY and ALL.

SELECT * FROM emp
WHERE sal > ANY (
    SELECT sal FROM emp
    WHERE sal BETWEEN 2500 AND 4000
);

32. Multiple and Correlated Subqueries

Multiple/Nested subqueries

SELECT * FROM emp
WHERE sal = (
    SELECT MAX(sal)
    FROM emp
    WHERE sal < (
        SELECT MAX(sal) FROM emp
    )
);

Correlated subquery

A normal subquery may be evaluated once for the parent statement, whereas a correlated subquery refers to the current parent-row values and can be evaluated for each row processed by the parent query.

SELECT DISTINCT deptno
FROM emp e
WHERE 5 <= (
    SELECT COUNT(ename)
    FROM emp
    WHERE e.deptno = deptno
);

33. EXISTS and NOT EXISTS

EXISTS tests whether the subquery returns at least one row. NOT EXISTS tests that no matching row exists.

SELECT deptno, ename
FROM emp e1
WHERE EXISTS (
    SELECT *
    FROM emp e2
    WHERE e1.deptno = e2.deptno
    GROUP BY e2.deptno
    HAVING COUNT(e2.ename) > 4
)
ORDER BY deptno, ename;

SELECT deptno, ename
FROM emp e1
WHERE NOT EXISTS (
    SELECT *
    FROM emp e2
    WHERE e1.deptno = e2.deptno
    GROUP BY e2.deptno
    HAVING COUNT(e2.ename) > 4
)
ORDER BY deptno, ename;

34. Indexes

An index is a database structure that can speed up retrieval by helping the database locate rows containing indexed values.

Unique index

Ensures that indexed values are unique. A unique index is automatically associated with primary-key or unique-constraint enforcement in Oracle.

CREATE UNIQUE INDEX stud_ind
ON student(sno);

Non-unique index

CREATE INDEX stud_ind
ON student(sno);

Indexes are especially useful for larger tables and frequently searched columns.

35. SQL*Plus SET Commands

36. DESCRIBE and SQL*Plus Reporting

DESCRIBE

DESC dept;

Displays the structure of a database object, such as column names, nullability and datatypes.

Page number and titles

TTITLE LEFT xtoday RIGHT 'page' SQL.PNO;

SQL*Plus formatting commands can be combined with column formatting and NEW_VALUE to build report titles and page numbers.

37. Important SQL Queries

Find the first N rows

SELECT * FROM emp
WHERE ROWNUM <= 4;

Find duplicate values

SELECT ename, COUNT(*)
FROM emp
GROUP BY ename
HAVING COUNT(*) > 1;

Employees drawing maximum salary in each department

SELECT *
FROM emp
WHERE (deptno, sal) IN (
    SELECT deptno, MAX(sal)
    FROM emp
    GROUP BY deptno
);

Reset time to beginning of day

SELECT TO_CHAR(
    TRUNC(SYSDATE),
    'DD-MON-YYYY HH:MI:SS AM'
) FROM dual;

38. Procedures

A procedure is a stored PL/SQL module that performs one or more actions. It may accept parameters and can contain declarations, executable statements and exception handling.

CREATE OR REPLACE PROCEDURE sample(
    a IN NUMBER,
    b OUT NUMBER,
    c IN OUT NUMBER
) IS
BEGIN
    b := 10;
    c := 20;
END sample;

The AUTHID clause can determine whether the procedure executes with definer or invoker rights.

39. Functions

A function is a stored module that returns a value.

CREATE OR REPLACE FUNCTION fun(
    a IN NUMBER,
    b OUT NUMBER,
    c IN OUT NUMBER
) RETURN NUMBER IS
BEGIN
    b := 5;
    c := 7;
    RETURN a * b * c;
END fun;

Oracle function definitions may also use clauses such as AUTHID, DETERMINISTIC and PARALLEL_ENABLE.

40. Parameter Modes — IN, OUT and IN OUT

procedure sample(
    a IN NUMBER,
    b OUT NUMBER,
    c IN OUT NUMBER
);

41. Default Parameters and Parameter Notation

Default parameter values allow callers to omit arguments for parameters that have defaults. Once a parameter without a default is followed by one with a default, later parameters generally need compatible defaults as well.

procedure p(
    a IN NUMBER DEFAULT 5,
    b IN NUMBER DEFAULT 6,
    c IN NUMBER DEFAULT 7
);

Positional notation

EXEC proc(v1, v2, v3);

Named notation

EXEC proc(a => v1, b => v2, c => v3);

Positional arguments should not be placed after named notation.

42. Formal and Actual Parameters

Actual parameters are the values/variables supplied by the caller. Formal parameters are the parameters declared by the called subprogram.

sample(v1, v2, v3);

After the subprogram call completes, output/in-out parameter values can be copied back to the corresponding actual variables.

43. NOCOPY

NOCOPY is a compiler hint for OUT and IN OUT parameters. It requests pass-by-reference behavior where possible, reducing copying between formal and actual parameters.

CREATE OR REPLACE PROCEDURE proc(
    a IN OUT NOCOPY NUMBER
) IS
BEGIN
    -- statements
END proc;

44. CALL and EXEC

CALL

CALL is a SQL statement used to execute stored subprograms. Parentheses are used even when there are no arguments.

CALL proc();

CALL proc(5, 6);

CALL fun() INTO :v;

EXEC

EXEC is a SQL*Plus command used to execute a procedure conveniently.

EXEC proc(5, 6);

The notes emphasize that CALL/EXEC are SQL*Plus/client commands and are not used as ordinary statements inside a PL/SQL block in the same way as a direct procedure call.

45. Packages

A package is a container for related PL/SQL objects. It has two main parts:

• Package specification — public declarations, signatures, cursors and public variables/types.
• Package body — implementations, private declarations and initialization/exception sections.

CREATE OR REPLACE PACKAGE pkg IS
    PROCEDURE emp_proc;
END pkg;

CREATE OR REPLACE PACKAGE BODY pkg IS
    PROCEDURE emp_proc IS
    BEGIN
        NULL;
    END emp_proc;
END pkg;

46. Package Runtime and Dependencies

• A package is instantiated when a packaged subprogram, variable or type is first referenced in a session.
• Each session has its own package state.
• An initialization section can initialize package state when the package is instantiated.
• The package specification and body are stored separately.
• The package body depends on its specification and referenced objects.
• The body can often be changed without changing the specification.
• Packages can contain private/local subprograms.

Compile package

ALTER PACKAGE pkg COMPILE;
ALTER PACKAGE pkg COMPILE SPECIFICATION;
ALTER PACKAGE pkg COMPILE BODY;

47. Serially Reusable vs Non-Serially Reusable Packages

Serially reusable

PRAGMA SERIALLY_REUSABLE can be specified in the package specification and body. Package state is reset/released between calls according to the serially reusable model.

CREATE OR REPLACE PACKAGE pkg IS
    PRAGMA SERIALLY_REUSABLE;
    PROCEDURE emp_proc;
END pkg;

Non-serially reusable

This is the default package behavior. Package state, including cursor state and package variables, can persist across calls within a session.

The source examples demonstrate that a serially reusable cursor starts again for each call, while a non-serially reusable cursor can continue from its previous state. fileciteturn24file0L174-L221

48. Package Runtime State Dependencies

Anonymous blocks can have compile-time dependencies on packages and runtime dependencies on package state.

• Package variables and cursors contribute to package runtime state.
• Each session maintains its own copy of package state.
• Recompiling a package can invalidate dependent program units when package state is involved.
• If a package has no global state, the runtime-state dependency issue is reduced.

49. Cursors

A cursor represents a context area used to process a SQL statement and its result set. It contains information such as the parsed statement, rows processed and the active set of returned rows.

Cursor parts

• Header: cursor name, parameters and return type.
• Body: the SELECT statement.

CURSOR c(dno IN NUMBER)
RETURN dept%ROWTYPE IS
SELECT * FROM dept;

Cursor types

• Implicit / SQL cursor
• Explicit cursor
• Parameterized cursor
• REF cursor

50. Cursor Stages and Attributes

Stages

Attributes

51. Cursor Declaration and Simple Cursor Loop

DECLARE
    CURSOR c IS SELECT * FROM student;
    v_stud student%ROWTYPE;
BEGIN
    OPEN c;

    LOOP
        FETCH c INTO v_stud;
        EXIT WHEN c%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(
            'Name = ' || v_stud.name
        );
    END LOOP;

    CLOSE c;
END;

The cursor-loop pattern is open → fetch → check %NOTFOUND → process → close.

52. Final Quick Revision