SQL

Summary

• History
• Operations
• Queries
• Joins
  • Natual Inner Join
  • Left Outer Join
• String Matching
• Sorting
• Set Operations
  • Union
  • Intersection
  • Except
• Built-in Functions
• Group by
• Nested Queries
  • Correlated Query
• Views
  • Definition
  • Updatable Rules
  • Not Updatable Examples
  • Updatable Examples
  • Materialized Views

History

SQL is lingua franca of dbs.

Originally known as SEQUEL: Strucuted English QUEry Language

• Part of System R, 1973
• Chamberlin Boyce

Based on relational tuple calculus and some algebra

ANSI & ISO Standars:

• SQL/86, SQL/89
• SQL/92 or SQL2
• SQL/99 or SQL3
• Revisions in 2003, 2006, 2008, 2011

SQL is supported by a lot of DBs:

• IBM DB2
• Oracle
• SYBASE
• SQLServer
• MySQL
• Postres

Operations

INSERT INTO UserInterests (Email, Interest, SinceAge)
VALUES ('[email protected]', 'Reading', 5);

DELETE FROM UserInterests
Where Interest = 'Swimming';

UPDATE UserInterests
SET Interest = 'Rock Music'
WHERE Email = '[email protected]'
  AND Interest = 'Music';

Queries

SELECT column1, column2, ..., columnn
FROM table1, table2, ..., tablem
WHERE condition;

is equivalent to

$$\pi _\text{column 1, \dots, column n} (\sigma _\text{condition} (table_1 \times \dots \times table_m))$$

If there is no WHERE clause, the query defaults to a cartesian product.

SELECT *
FROM RegularUser
WHERE HomeTown    = 'Atlanta'
   OR CurrentCity = HomeTown;

Joins

Natual Inner Join

Regular User

YearSalary

SELECT Email, RegularUser.BirthYear, Salary
FROM RegularUser, YearSalary
WHERE RegularUser.BirthYear = YearSalary.BirthYear;

or

SELECT Email, RegularUser.BirthYear, Salary
FROM RegularUser NATURAL JOIN YearSalary;

or

SELECT Email, R.BirthYear, Salary
FROM RegularUser AS R, YearSalary AS Y
WHERE R.BirthYear = Y.BirthYear;

Result

If there are no same-named attributes, the NATURAL JOIN defaults to cartesian product.

Left Outer Join

Return everything from the left-hand side, include right-hand side data if available.

SELECT EMAIL, R.BirthYear, Salary
FROM RegularUser AS R
LEFT OUTER JOIN YearSalary AS Y;

String Matching

Use LIKE and the % wildcard to match text.

SELECT *
FROM RegularUser
WHERE CurrentCity LIKE 'San%';

% matches any string, including empty string.

_ matches any single character.

Sorting

SELECT *
FROM RegularUser
WHERE Sex = 'M'
ORDER BY CurrentCity ASC;

Set Operations

Union

Set union projection. Useful for combining multiple columns into one.

SELECT CurrentCity
FROM RegularUser
UNION
SELECT HomeTown
FROM RegularUser;

Running UNION ALL instead of just UNION will return all instances of the values, not just the distinct.

Intersection

Set intersectoin projection.

SELECT CurrentCity
FROM RegularUser
INTERSECT
SELECT HomeTown
FROM RegularUser;

Running INTERSECT ALL instead of just INTERSECT will return all instances of the values, not just the distinct.

Except

Set intersectoin projection.

SELECT CurrentCity
FROM RegularUser
EXCEPT
SELECT HomeTown
FROM RegularUser;

Running EXCEPT ALL instead of just EXCEPT will return all instances of the values, not just the distinct.

Built-in Functions

count, sum, avg, min, max

Group by

If you group by fields, then any field you select needs to be grouped by or an aggregate function.

HAVING is similar to WHERE, but only applies to groups.

Nested Queries

and

SELECT CurrentCity
FROM RegularUser R, YearSalary Y
WHERE R.BirthYear = Y.BirthYear
  AND Y.Salary > ALL
  (SELECT Salary
   FROM RegularUser R, YearSalary Y
   WHERE R.BirthYear = Y.BirthYear
     AND R.HomeTown = 'Austin');

Equivalent statement

SELECT CurrentCity
FROM RegularUser R, YearSalary Y
WHERE R.BirthYear = Y.BirthYear
  AND Y.Salary > 
  (SELECT max(Salary)
   FROM RegularUser R, YearSalary Y
   WHERE R.BirthYear = Y.BirthYear
     AND R.HomeTown = 'Austin');

Correlated Query

SELECT R.Email, BirthYear
FROM RegularUser R
WHERE NOT EXIST
  (SELECT *
   FROM UserInterest U
   WHERE U.Email = R.Email);

Views

PDF Notes

Definition

A view is a virtual table.

The definition is stored in the catalog.

CREATE VIEW AtlantaUserInterests AS
SELECT U.Email, Interest, SinceAge
FROM UserInterests I, RegularUser U
WHERE I.Email = U.Email
  AND HomeTown = 'Atlanta';

Columns in a view:

• are inherited from the base table
• may be explicitly named in the definition
• must be explicitly named if ambiguous
• must be explicitly named if computed

Since a view is a virtal table, “updates” to a view can only be done by the DBMS by updating the base tables from which the view is defined.

Updatable Rules

A view is updatable if and only if:

• It does not contain any of the following keywords:
  • JOIN
  • UNION
  • INTERSECT
  • EXCEPT
  • DISTINCT
• Every column from the view corresponds to a uniquely identifiable base table column.
• The FROM clause references exactly one table which must be a base table or updatable view.
• The table referenced in the FROM clause cannot be referenced in the FROM clause of a nested WHERE clause.
• It does not have a GROUP BY/HAVING clause

Updatable means INSERT, DELETE, and UPDATE are ok.

Not Updatable Examples

-- Not Unique
CREATE VIEW SexHomeTown AS
SELECT Sex, HomeTown
FROM RegularUser
WHERE BirthYear >= 1969;

-- Computed Attribute
CREATE VIEW AvgUserInterestSinceAge AS
SELECT Email, AVG(SinceAge)
FROM UserInterests
GROUP BY Email;

-- Join
CREATE VIEW AtlantaUserInterests AS
SELECT U.Email, Interest, SinceAge
FROM UserInterests I, RegularUser U
WHERE I.Email = U.Email AND HomeTown=‘Atlanta’;

Updatable Examples

CREATE VIEW UserInterestsSinceTeen AS
SELECT *
FROM UserInterests
WHERE SinceAge >= 13;

-- Moves row(s) outside the view
UPDATE UserInterestsSinceTeen
SET SinceAge = 12
WHERE Interest = ‘DIY’;

-- Creates row outside the view
INSERT INTO UserInterestsSinceTeen
VALUES (‘[email protected]’, ‘Soccer’, 8);

CREATE VIEW UserInterestsSinceTeen AS
SELECT *
FROM UserInterests
WHERE SinceAge >= 13
WITH CHECK OPTION; -- Prevents updates outside the view

Materialized Views

To create a materialized view, we use the following syntax:

CREATE MATERIALIZED VIEW ViewName
[REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]] [BUILD IMMEDIATE|BUILD DEFERRED]
AS Select Query;

• The materialized view definition is stored in the catalog.
• The Select Query is run and the results stored in the materialized view table.

Options includes:

• REFRESH FAST: uses an incremental refresh method which uses changes made to the underlying tables in a log file.
• REFRESH COMPLETE: a complete refresh by re-running the query in the materialized view.
• REFRESH FORCE: a fast refresh should be performed if possible, but if not, a complete refresh is performed.
• REFRESH ON DEMAND: a refresh will occur manually whenever specific package functions are called.
• REFRESH ON COMMIT: a fast refresh occurs whenever a transaction commits that makes changes to any of the underlying tables.
• BUILD IMMEDIATE: the materialized view will be populated immediately. This is the default.
• BUILD DEFERRED: the materialized view is populated on the next refresh operation.

Source

Remember that the syntax and semantics may vary in different commercial DBMSs.