3. SQL Queries

Oracle SQL Data Definition

SQL relations (tables) is a multi-set, NOT a set of tuples. Using PRIMARY KEY or UNIQUE, or the DISTINCT option in a query, constraints the result to a set. DISTINCT requires search to ensure duplicate tuples are not returned, so is slower than using ALL (the default).

Naming:

Expression

An attribute (i.e. column name) or combination of attributes, operators and functions.

Binary Operators

Functions

Scalar:

Aggregate/Set:

Predicates

Misc.

SQL+:

Create Schema

Schemas are used to group tables together. Permissions are set on a per-schema level.

CREATE SCHEMA schema-name
AUTHORIZATION owner

Create Table

CREATE TABLE [schema-name.]table-name
(
  column-name column-specification[,
  column-name column-specification]*

  [constraint-name constraint-specification]*
)

Use CREATE TABLE table-name AS (query) to populate the table with data.

Notes

Constraints can be inline or defined after the columns. If the latter, multiple rows can be referenced.

Inline:

column-definition constraints where constraints can be:

To define a primary key after column definitions: PRIMARY KEY (column-name). A composite primary key can be made by passing multiple arguments.

To define foreign keys after column definitions:

FOREIGN KEY (column-name)
REFERENCES foreign-table (foreign-table-key)
[ON DELETE integrity-option]
[ON UPDATE integrity-option]

To add constraints after defining the table:

ALTER TABLE table-name ADD CONSTRAINT constraint-name constraint-definition

Where integrity-option is one of RESTRICT, CASCADE, SET NULL or SET DEFAULT.

Domain: basically typedef for SQL. NOT in Oracle:

CREATE DOMAIN domain-name type-definition [CHECK check-statement]
/* e.g. `check-statement` could be `(VALUE IS NOT NULL)` */

Drop Table

Drops the relation (base table) and definition: DROP TABLE table-name.

Alter Table

Add, delete or modify attributes/constraints using ALTER commands.

Add/modify: ALTER TABLE table-name ADD|MODIFY column-name column-definition. The NOT NULL constraint is only allowed if it also has a DEFAULT default-value constraint. Otherwise, the column must be added without the NOT NULL constraint then update all the rows, then alter the definition.

Delete column and all constraints: ALTER TABLE table-name DROP [COLUMN] column-name RESTRICT|CASCADE.

Delete constraint: ALTER TABLE table-name DROP [CONSTRAINT] constraint-name RESTRICT|CASCADE. If the constraint is a primary key or unique, CASCADE drops all referencing foreign keys; RESTRICT stops the operation if there are any referencing foreign keys.

Drop Schema

DROP SCHEMA schema-name RESTRICT|CASCADE. RESTRICT only allows the operation to run if the schema is empty; CASCADE drops all children objects as well.

Oracle SQL Queries

SELECT

SELECT [ALL|DISTINCT] attribute-list
FROM table-list
[WHERE condition]
[GROUP BY grouping-attributes]
[HAVING group-condition]
[ORDER BY attribute-list]
[OFFSET n]
[FETCH NEXT m ROWS ONLY] /* Oracle specific */

Six clauses; the last four are optional:

Table Alias

Oracle uses the syntax, tableName alias.

Other SQL implementations use tableName AS alias.

This may be useful when referring to two entries in the a single table (e.g. find rows where the last names the same).

The result of a nested select statement can also be aliased: (SELECT clause that is probably using JOINs) alias.

Attribute list

The attribute list can contain column names, aggregate functions and constants:

Some WHERE predicates

GROUP BY

For applying aggregate functions to groups of tuples; each group is a set of tuples where they have the same value for the grouping attribute(s) (comma-separated list). This is used for summary information, and is often phrased as for each entity ….

e.g. SELECT origin, COUNT(origin) FROM routes GROUP BY origin returns number of times each origin value appears.

e.g. SELECT COUNT(DISTINCT type), director FROM movie GROUP BY director returns the number of genres of movies each director has directed.

When using GROUP BY, all non-aggregate attributes need to be referenced. This is as the grouping reduces the result into a single tuple, which may not be possible if they are not all referenced. For example, if (a, b) = (0, 0), (0, 1) is grouped by a, it must either pick one of the values or pick both, neither of which are allowed.

This can happen even if the primary key is used as the grouping attribute.

HAVING

Retrieves the values of these functions only for groups that satisfy certain conditions - i.e. it filters out groups.

e.g. SELECT origin, COUNT(origin) FROM routes GROUP BY origin HAVING COUNT(origin) > 10.

COUNT(*) gets the count for the group.

ORDER BY

Sorts tuples based on the values of some attributes:

ORDER BY [column-name ASC|DESC] [, column-name ASC|DESC]*

Default is ASC. Sorts by the first column name first, and when the values are equal, it sorts by the second column etc. The sort is not stable.

Nested Queries

A nested query/sub-query be specified within the WHERE and HAVING clauses of an outer query. The sub-query must be enclosed within parentheses.

Examples:

EXISTS

Used to check if result of correlated sub-query is empty.

e.g. SELECT * FROM director D1 WHERE EXISTS (SELECT * FROM movie WHERE D1.id = director AND type='comedy') finds directors that have directed at least one comedy.

Multi-table queries

Queries used nested queries using the = or IN conditions can always be expressed as a single query. This does a JOIN in the background, and so is much more efficient than correlated nested queries. The FROM clause may contain more than one table.

e.g. SELECT title FROM movie, director WHERE dnumber=director AND lname='Nolan'.

In general, if you have n tables, will need n-1 join conditions.

If there is no WHERE clause, all tuples of the relations are selected and the result is equivalent to the Cartesian product, most of which will be nonsense.

Multi-table query for the same table: SELECT DISTINCT M1.director FROM movie M1, movie M2 WHERE M1.director = M2.director AND M1.type='comedy' and M2.type='drama'. A similar result could be done using an INTERSECT operation.

Specifying Joins in SQL2

... FROM table1 JOIN_TYPE table2 ON col1=col2

Where the join type is one of:

The result of a join statement can also be used as a table e.g.:

SELECT title FROM
  (SELECT * FROM movie JOIN director ON movie.director=director.id) movieDirector
WHERE movieDirector.lname='Nolan' /* single quotes only */

To select all from both tables, use SELECT movie.*, director.*.

Set operations

UNION, EXCEPT (MINUS in Oracle) and INTERSECT. The results are sets - no duplicates, unless the keyword ALL is appended.

The two operands - select statements (in brackets), must return a table with the same number of properties and compatible data types.

ANY/ALL

Used with comparison operators and nested queries.

ANY is equivalent to IN. It is sometimes called SOME.

NOT ALL is equivalent to NOT IN.

The following will return movies that got more awards than all movies by a particular director:

SELECT * FROM movie WHERE awards > ALL (
  SELECT awards FROM movie, director WHERE movie.director = director.id AND lname = 'Nolan'
)

Division

R÷S=T(A) R \div S = T(A)

RR, SS has at least one common attribute e.g. The result of division with R(A,B)R(A, B) and S(B,C)S(B, C) will have (A,B)−(B,C)‘=(A)(A, B) - (B, C)` = (A).

Directors that have directed at least one movie of each type:

SELECT fname, lname FROM director WHERE NOT EXISTS (
  SELECT * FROM movie M1 WHERE NOT EXISTS (
    SELECT * FROM movie M2 WHERE M2.director = director.id AND M2.type = M1.type
  )
)

The M1.type = M2.type is able to get every single type of movie as it is not constrained to any particular director.

The following also gives the same result:

SELECT fname, lname FROM DIRECTOR
  JOIN movie ON director.id = movie.director
  GROUP BY director, fname, lname
  HAVING COUNT(DISTINCT type) = (
    SELECT COUNT(DISTINCT type) FROM movie
  )

The nested query is not correlated.

DUAL table

In Oracle, it is a dummy table with a single column and row which is used to get scalar values e.g. SELECT sysdate FROM DUAL.

INSERT

Adds one or more tuples to a relation. The following requires all attributes to be listed in the same order they were specified when the table was created:

INSERT INTO table_name VALUES (attr_1, ..., attr_n)

To specify the attributes to insert:

INSERT INTO table_name (attr_name_1, ..., attr_name_n) VALUES (attr_1, ..., attr_n)

Both forms allow you to insert multiple tuples, separating them with commas.

A select statement can be used to insert multiple tuples:

INSERT INTO table_name (attr_name_list) select_statement

NB: two single quotes escape a quote in a string literal.

UPDATE

UPDATE table_name SET attr_name_1 = expression_1, ... WHERE where_condition

The where condition is optional.

DELETE

DELETE FROM table_name WHERE where_condition

You can only delete data from one table at a time (except if CASCADE is used on a foreign key).

If the where condition is missing, every row gets deleted.

Views

Views are virtual tables - essentially a stored query that allows customized access to data (e.g. remove access to some sensitive attributes of a table).

In general, for a view to be updatable (varies by implementation):

CREATE VIEW view_name AS select_statement

Once a view is created, it can be queried like a normal table.

To drop a view:

DROP VIEW view_name

In Oracle, to find out which views are updatable:

SELECT column_name, updatable FROM user_updatable_columns WHERE table_name=upper('view_name')

Indexes

Indexes are a physical-level feature; access structures to speed up queries.

CREATE INDEX index_name ON table_name (attr_name_1, ...)

In older versions of Oracle, indexes would not be created for keys. In this case, a index would need to be created with the UNIQUE keyword.

DROP INDEX index_name is used to delete indexes.

Notes:

Integrity Component

SQL 92 adds:

Domain Constraints

Not in Oracle:

CREATE DOMAIN domain_name AS data_type DEFAULT default_option CHECK (search_option)

Both DEFAULT and CHECK are optional, and search_option should reference the value of the value using VALUE.

Use DROP DOMAIN domain_name, optionally appending RESTRICT or CASCADE.

Referential Integrity Violation

The qualifiers are ON DELETE and ON UPDATE.

The options are SET NULL, CASCADE and SET DEFAULT.

Oracle only supports ON DELETE CASCADE and ON DELETE SET NULL.

Enterprise Constraints

Constraints
CONSTRAINT constraint_name CHECK (condition)

This is for a specific table, so the condition can reference multiple attributes. In SQL2, the condition may include a nested SELECT statement.

Assertions

Constraints spanning multiple tables can be made: these are called assertions.

CREATE ASSERTION assertion_name CHECK (search_condition)

The search condition will be a SQL query, probably using the EXISTS keyword.

This is not supported in Oracle.

Triggers

Triggers monitors the state of the database and performs some action when a specific condition occurs.

Triggers can:

A trigger has an event - a change that activates a trigger. This can be:

For DML events, there are row-level and statement-level triggers; the former occurs for each modified row while the latter runs once for each statement.

Once an appropriate event occurs, it must pass a condition; a query which determines if the trigger should be activated.

If the condition is met, the action will occur. This can be one or more statements (procedures).

The action can occur BEFORE, AFTER, or INSTEAD OF.

CREATE|REPLACE TRIGGER [schema.]trigger_name
BEFORE|INSTEAD OF|AFTER
DELETE|INSERT|UPDATE OF attr_name_1, attr_name_2... OR DELETE|INSERT|UPDATE OF ...
ON [schema.]table_name|view_name
[REFERENCING OLD AS old_row_alias NEW AS new_row_alias]
FOR EACH ROW|STATEMENT
WHEN (condition)
  body
/

Within the SQL statements, if REFERENCING is not specified, :new.attribute_name and :old.attribute_name are used to reference attributes values for the row in question.

The / is used to end the trigger action. It must be on a new line.

Limitations:

To alter the trigger:

ALTER TRIGGER [schema.]trigger_name ENABLE|DISABLE|COMPILE [DEBUG]

SHOW ERRORS shows any compilation errors.

PL-SQL Example Code
DECLARE
  varname type := initial_value;
  varname2 type;
BEGIN
  SELECT attribute INTO varname FROM table_name WHERE ...;
  RAISE_APPLICATION_ERROR(num => error_code, msg => 'Message');
  -- Error code between -20999 and -20000
  ...;
EXCEPTION
  handlers;
END;

Cannot use EXISTS: must store COUNT(*) into variable.

Example: Audit Trigger
CREATE [OR REPLACE] TRIGGER audit_trigger BEFORE INSERT OR DELETE OR UPDATE ON table_name
FOR EACH ROW
begin
  if INSERTING then
    INSERT INTO audit_table VALUES (USER || 'inserting' || 'new id:' || :new.id);
  elsif DELETING then
    INSERT INTO audit_table VALUES (USER || 'deleting' || 'old id:' || :old.id);
  elsif UPDATING('attribute_name') then
    INSERT INTO audit_table VALUES 
      (USER || 'updating' || 'old value:' || :new.attribute_name || 'new value:' || :new.attribute_name);
  elsif UPDATING then
    INSERT INTO audit_table VALUES 
      (USER || 'updating' || 'old id:' || :old.id || 'new id:' || :new.id);
  end if;
end;
/
Procedures

Collection of statements; basically functions:

CREATE|REPLACE PROCEDURE [schema].procedure_name
argument_name [IN|IN OUT|OUT] datatype, ...
IS|AS
  body

The body cannot contain DDL statements.

Compiling a procedure: ALTER PROCEDURE [schema.]procedure_name COMPILE.

Database Security

Discretionary and mandatory security mechanisms.

Control measures:

The DBA is responsible for the overall security of the database. They have the responsibility to grant privileges to users and assign security levels users and data.

They can set privileges at the account and relation level:

Each table is assigned an owner account; they have all privileges for the table, and can give privileges to other users.

Privileges can also be specified using views, allowing, for example, users access to only some attributes of a table.

GRANT account_privilege|table_privilege|ALL PRIVILEGES
ON object
TO user|role|PUBLIC
WITH GRANT OPTION;


REVOKE account_privilege|table_privilege|ALL PRIVILEGES
ON object
FROM user|role|PUBLIC;

/* EXAMPLES */
GRANT CREATE TABLE TO user WITH GRANT OPTION;
GRANT INSERT, DELETE ON table1, table2 TO user;
GRANT UPDATE ON table1(attribute1) TO user;

Table privileges: SELECT; INSERT; UPDATE; ALTER; DELETE; INDEX; DEBUG; REFERENCES. INSERT, UPDATE and REFERENCES (allows user to have a FK to the table) can be constraint to specific attributes.

If GRANT OPTION is set, the user granted the privilege can grant the privileges to other users; propagation. However, if the privilege is revoked, all propagated privileges are automatically revoked.