Introduction to Database Management (CS-504 (C)) Notes

How unit 5 is examined

This unit covers the SELECT statement, joins, special operators, subqueries, hierarchical and flashback queries, and PL/SQL-style blocks; the only asked topic is SELECT with GROUP BY and ORDER BY.

SELECT

Low weight

Definition. SELECT retrieves rows and columns from one or more tables without changing the data.

SELECT [DISTINCT] col_list FROM table [WHERE cond]
[GROUP BY col_list [HAVING cond]] [ORDER BY col [ASC|DESC]];

Key points.

GROUP BY groups rows with equal values so aggregates such as COUNT, SUM and AVG work per group, e.g. SELECT dept, AVG(sal) FROM emp GROUP BY dept;.
HAVING filters groups after aggregation, while WHERE filters rows before it.
ORDER BY sorts the result, ascending by default and descending with DESC, e.g. ORDER BY sal DESC.
Execution order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Update and Delete syntax. UPDATE t SET col = val [WHERE cond]; and DELETE FROM t [WHERE cond];

Asked: [7 marks] (Jun 2020) Write syntax of SQL order by and group by clauses. Asked: [7 marks] (Dec 2020) Explain the following commands with syntax: Select, Update, Delete.

SQL queries

Definition. A query is a SQL statement that asks the database for data, and the basic structure of a query is SELECT, FROM and WHERE.

Key points.

SELECT lists the columns, FROM names the tables, and WHERE gives the row condition.
SELECT * returns all columns, and an alias renames a column, e.g. sal AS salary.
A query can be nested inside another query.
The result of a query is itself a table.

Asked: [7 marks] (Dec 2020) Short notes: Basic structure of SQL query.

Data extraction from single, multiple tables: equi-join, non equi-join, self-join, outer join

Definition. A join combines rows of two or more tables on a related column.

Key points.

Equi-join uses = between columns: SELECT e.name, d.dname FROM emp e, dept d WHERE e.dno = d.dno;.
Non-equi-join uses another operator such as BETWEEN, e.g. e.sal BETWEEN g.lo AND g.hi.
Self-join joins a table to itself using two aliases: FROM emp e, emp m WHERE e.mgr = m.empno.
Outer join also keeps unmatched rows: LEFT, RIGHT or FULL OUTER JOIN, with NULL for missing values.

Special operators

Definition. Special operators test patterns, sets and subquery results in a WHERE clause.

Key points.

LIKE matches patterns, where % is any string and _ is one character: name LIKE 'A%'.
IN tests membership of a list or subquery, and EXISTS is true if the subquery returns any row.
ANY is true if the comparison holds for at least one value, e.g. sal > ANY (subquery).
ALL is true only if it holds for every value, e.g. sal > ALL (subquery).

Hierarchical queries

Definition. A hierarchical query retrieves rows of a tree-structured table using START WITH and CONNECT BY.

SELECT empno, ename, LEVEL FROM emp
START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr;

Key points.

START WITH gives the root rows, and CONNECT BY PRIOR gives the parent-child link.
The pseudo-column LEVEL shows depth, with the root at 1.
It is Oracle syntax, used for employee-manager and bill-of-material data.

Inline queries

Definition. An inline query (inline view) is a subquery in the FROM clause that acts as a temporary table.

Key points.

Example: SELECT * FROM (SELECT dno, AVG(sal) a FROM emp GROUP BY dno) WHERE a > 5000;.
The inner query runs first and the outer query reads its result.
It lets a WHERE filter use an aggregate computed in the inner query.
Nothing is stored, so the view exists only for that statement.

Flashback queries

Definition. A flashback query shows table data as it was at an earlier time, using undo data.

Key points.

Syntax: SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);.
AS OF SCN n gives the same view at a system change number.
It helps recover wrongly deleted or updated rows.
It works only while the undo data is still retained.

Introduction of ANSI SQL

Definition. ANSI SQL is the standard SQL language defined by ANSI and ISO so that it works across database products.

Key points.

Versions include SQL-86, SQL-92, SQL:1999 and later.
It defines DDL, DML and DCL statements.
Vendors such as Oracle add extensions like PL/SQL on top of the standard.
Standard code stays portable between systems.

Anonymous block

Definition. An anonymous block is an unnamed PL/SQL block that runs once and is not stored in the database.

DECLARE v NUMBER := 5;
BEGIN
  DBMS_OUTPUT.PUT_LINE(v);
EXCEPTION WHEN OTHERS THEN NULL;
END;

Key points.

DECLARE is optional, BEGIN and END are mandatory, and EXCEPTION is optional.
It is compiled each time it runs.
It cannot be called by name or take parameters.

Nested anonymous block

Definition. A nested anonymous block is a block placed inside the executable or exception part of another block.

Key points.

The inner block has its own DECLARE, BEGIN, EXCEPTION and END.
Outer variables are visible inside, but inner variables are not visible outside.
An exception in the inner block can be handled there so the outer block continues.
An inner variable with the same name hides the outer one.

Branching and looping constructs

Definition. Branching chooses a path by a condition, and looping repeats statements.

IF x > 0 THEN ... ELSIF x = 0 THEN ... ELSE ... END IF;
CASE grade WHEN 'A' THEN ... ELSE ... END CASE;
LOOP ... EXIT WHEN i > 5; END LOOP;
WHILE i <= 5 LOOP ... END LOOP;
FOR i IN 1..5 LOOP ... END LOOP;

Key points.

Branching uses IF-THEN-ELSIF-ELSE and CASE.
LOOP runs until EXIT WHEN is true, WHILE tests before each pass, and FOR runs a fixed range.
Each IF ends with END IF and each loop with END LOOP.

Last-minute revision

SELECT clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
Execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
WHERE filters rows, HAVING filters groups.
ORDER BY is ascending by default; DESC reverses it.
Equi-join uses =, non-equi uses BETWEEN or other operators, self-join uses two aliases.
Outer join keeps unmatched rows with NULLs.
LIKE uses % and _; IN tests a set; EXISTS tests for rows; ANY and ALL compare with a set.
Hierarchical query: START WITH plus CONNECT BY PRIOR, with LEVEL.
Inline view is a subquery in FROM.
Flashback uses AS OF TIMESTAMP or AS OF SCN.
Anonymous block: DECLARE, BEGIN, EXCEPTION, END.

Memory hooks

"Where before Group, Having after": WHERE row filter, HAVING group filter.
"FWGHSO": From, Where, Group, Having, Select, Order is the execution order.
"Self-join = two nicknames" for one table with two aliases.
"Any = one, All = every."
"DBE" for Declare, Begin, Exception in a block.

Coverage checklist

SELECT: Jun 2020 order by and group by syntax; Dec 2020 Select, Update, Delete syntax.
SQL queries: Dec 2020 basic structure of SQL query.
Data extraction from single, multiple tables equi-join, non equi-join, self-join, outer join: covered.
Usage of like, any, all, exists, in Special operators: covered.
Hierarchical queries: covered.
inline queries: covered.
flashback queries: covered.
Introduction of ANSI SQL: covered.
anonymous block: covered.
nested anonymous block: covered.
branching and looping constructs in ANSI SQL: covered.

Account

Introduction to Database Management Systems (CS-504 (C)) - Unit 5 Short Notes

How unit 5 is examined

SELECT

SQL queries

Data extraction from single, multiple tables: equi-join, non equi-join, self-join, outer join

Special operators

Hierarchical queries

Inline queries

Flashback queries

Introduction of ANSI SQL

Anonymous block

Nested anonymous block

Branching and looping constructs

Last-minute revision

Memory hooks

Coverage checklist