Week 4. SQL

SQL

SQL

Structured English Query Language (SQL) represents the implementation of the relational model, it is used for data definitions as well as queries and updates. SQL can also specify authorisation and security, define integrity constraints, define views, and specify transaction controls.

Link to original

Data Definition Language

The Data Definition Language (DDL) has several commands:

CREATE: create a description of the relations

CREATE SCHEMA

Create Schema (SQL)

For example, we can specify a new database schema with a name:

CREATE SCHEMA COMPANY AUTHORIZATION JSMITH;

We can select a schema to be defined:

USE COMPANY;

Link to original

CREATE TABLE

Create Table (SQL)

We can specify a new base relation by giving it a name, and specifying each of its attributes.

CREATE TABLE DEPARTMENT (
	DNAME        VARCHAR(10) NOT NULL,
	DNUMBER      INTEGER     NOT NULL,
	MGRSSN       CHAR(9),
	MGRSTARTDATE DATE
);

In SQL, attributes are ordered based on the order they are specified. Attributes may have initial constraints defined, such as NOT NULL.

Integrity constraints are specified after the attributes:

CREATE TABLE DEPARTMENT {
	...,
	PRIMARY KEY(DNUMBER),
	UNIQUE(DNAME),
	FOREIGN KEY(MGRSSN) REFERENCE EMPLOYEE(SSN),
 
	-- composite primary keys
	PRIMARY_KEY(DNUMBER, DNAME),
 
	-- composite foreign keys
	FOREIGN KEY(MGR_FNAME, MGR_LNAME) REFERENCES EMPLOYEE (FNAME, LNAME)
}

Link to original

CREATE DOMAIN

Create Domain (SQL)

We can specify our own data type to use in the schema.

CREATE DOMAIN SSN AS CHAR(9);
CREATE TABLE DEPARTMENT (MGRSSN SSN, ...);

We need to CREATE DOMAIN before utilising it in CREATE TABLE.

Link to original

![[Reserved Keywords (SQL)]]

DROP: delete the descriptions

DROP TABLE

Drop Table (SQL)

We can remove a relation (base table) and its definition, the relation will no longer be usable as it’s description will no longer exist.

DROP TABLE DEPENDENT;

We can optionally only drop the table if not referenced in any constraints:

DROP TABLE DEPENDNET RESTRICT;

All constraints that reference table are dropped along with table.

DROP TABLE DEPENDENT CASCADE;

Link to original

DROP SCHEMA

Drop Schema (SQL)

Used to remove the entire schema.

DROP SCHEMA COMPANY;

Dropped only if no elements in schema:

DROP SCHEMA COMPANY RESTRICT;

All tables, views, and constraints dropped:

DROP SCHEMA COMPANY CASCADE;

Link to original

ALTER: update the descriptions

ALTER TABLE ADD

Alter Table Add (SQL)

Used to add an attribute to one of the base relations. The new attribute with have NULLs in all existing tuples of the relation.

ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);

Database users will need to update a value for the new job attribute for existing employees.

We may provide a default value:

ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12) DEFAULT 'President';

This is also a prerequisite for NOT NULL.

Depending on the order in which tables are created, circular referential integrity constraints may need to be added later, e.g.:

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT (Dnumber);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE (Ssn);

Link to original

ALTER TABLE DROP

Alter Table Drop (SQL)

We can remove attributes (and data) by:

ALTER TABLE EMPLOYEE DROP JOB;

We can specify a foreign key to drop a constraint:

ALTER TABLE DEPARTMENT DROP FOREIGN KEY (MGRSSN);

Link to original

Attribute Data Types

Attribute Data Types (SQL)

SQL has a number of data types that may be used for attributes:

• Integers: typically we use INT / INTEGER which are implicitly signed, we may used UNSIGNED INTEGER for the unsigned variant. INT(n) specifies the number of digits used.

• Approximate real numbers: use FLOAT / REAL / DOUBLE, can specify digit precision as FLOAT(n) where n is the number of bits used to store the mantissa of the floating point number.

• Exact real numbers: use DECIMAL(i,j) to specify a fixed-point decimal number with i being the precision (total number of digits to store the number), and j denoting the position of the point / the scale.

• Strings: We can use a number of types here:

  • CHAR(n) / CHARACTER(n): fixed length, right padded with spaces
  • VARCHAR(n) / CHAR VARYING(n): varying length
  • CLOB / TEXT: character large object

• Binary data: Again we can use similar notation:

  • BIT(n): fixed length
  • BIT VARYING(n): varying length
  • BLOB: binary large object

  title: Best practice: hash binary data and store that in the database with the metadata.

• Boolean: use BOOLEAN or BIT(1), choice may vary from implementation to implementation, and in some scenarios it may be `NULL`

• Date and Time: We have multiple different types to specify time:

  • DATE: made up of year-month-day (“yyyy-mm-dd”)
  • TIME: made up of hour:minute:second (“hh:mm:ss”)
  • TIME(i): TIME plus i additional digits for fractions of a second (“hh:mm:ss:ii…i”)
  • DATETIME / TIMESTAMP: both DATE and TIME components

• Interval: we can use INTERVAL to specify relative time value as opposed to absolute, can be day/time intervals or year/month intervals. Can be positive or negative when added to or subtracted from an absolute value, the result is an absolute value.

• There are additional domain specific / complex types which aren’t relevant for the course:

  • Special Types: CURRENCY / MONEY
  • Spatial Types (GIS): GEOMETRY type
  • Enumerated Types: ENUM("One", "Two", "Three")
  • Collection Types: SET / VALUE_MAP

Link to original

Specify Integrity Constraints

We can specify a variety of integrity constraints for attributes:

• Constraint NOT NULL (SQL)

  NOT NULL: enforce attributes cannot take `NULL` values.

  DNUMBER INTEGER NOT NULL;

  Link to original

• Constraint DEFAULT (SQL)

  DEFAULT <value>: specify a default value if one is not provided

  MGRSSN CHAR(9) DEFAULT '123456789';

  Link to original

• Constraint AUTO_INCREMENT (SQL)

  AUTO_INCREMENT: for integer types, usually used for IDs

  DNUMBER INTEGER NOT NULL AUTO_INCREMENT;

  Link to original

Specify Domain Constraints

Domain Constraints (SQL)

We can also specify domain constraints using CHECK by providing a valid conditional expression:

DNUMBER INT CHECK (DNUMBER > 0 AND DNUMBER < 21);

We can also use Create Domain (SQL) to specify checks:

CREATE DOMAIN D_NUM AS INTEGER CHECK (VALUE > 0 AND VALUE < 21);

Check is unable to compare against other attributes / relations, in those cases we use an ASSERTION.

Link to original

Specify Key and Referential Integrity Constraints

• Constraint PRIMARY KEY (SQL)

  PRIMARY KEY: used to specify that an attribute should be non-null and used as the primary key.

  DNUMBER INT PRIMARY KEY;

  Link to original

• Constraint UNIQUE (SQL)

  UNIQUE: used for secondary / alternate keys

  DNAME CHAR(9) UNIQUE;

  Link to original

• Constraint FOREIGN KEY (SQL)

  FOREIGN KEY: used for referential integrity

  FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE (SSN);

  Link to original

Referential Integrity Options

Referential Integrity Options (SQL)

A referential integrity constraint may be violated when tuples in the referenced tuple are updated / deleted. By default REJECT is used for operations that violate constraints.

However, we can specify what should happen on different events such as:

• ON DELETE
• ON UPDATE

And trigger actions such as:

• RESTRICT
• CASCADE
• SET NULL
• SET DEFAULT

Example:

CREATE TABLE EMPLOYEE (
	...,
	FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER)
	ON DELETE SET DEFAULT ON UPDATE CASCADE,
	FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN)
	ON DELETE SET NULL ON UPDATE CASCADE
);

Link to original

Data Manipulation Language

Construct SELECT queries

Select Query (SQL)

The basic form of the SQL SELECT statement is called a mapping or SELECT-FROM-WHERE block.

SELECT <attr list> FROM <table list> WHERE <condition>

• <attr list> is a list of attribute names whose values are to be retrieved by the query
• <table list> is a list of the relation names required to process the query
• <condition> is a conditional boolean expression that identifies the tuples to be retrieved by the query. We have access to basic logical operators in the <condition>: =, <, <=, >, >=, <>, !=, AND, OR, NOT, IS NULL, and IS NOT NULL.

Link to original

JOIN query

Join Condition (SQL)

We can retrieve the name and address of all employees who work for the ‘Research’ department by specifying a join condition:

SELECT FNAME, LNAME, ADDRESS
FROM   EMPLOYEE, DEPARTMENT
WHERE  DNAME = 'Research' AND DNUMBER = DNO

We can specify as many join conditions as we want.

We can also use an explicit JOIN clause:

SELECT location, mgrssn
FROM   DEPARTMENT
JOIN   DEPT_LOCATIONS
ON     DEPARTMENT.number=DEPT_LOCATIONS.number
WHERE  DNAME = 'Research';

Link to original

Qualification of Relation Names

Qualification of Relation Names (SQL)

In SQL, we can use the same name for multiple attributes as long as the attributes are in different relations. If two or more attributes in different relations have the same name, we need to specify them by the relation name. We can qualify the attribute name with the relation nae by prefixing the relation name to the attribute name.

SELECT EMPLOYEE.FNAME, EMPLOYEE.LNAME, EMPLOYEE.ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.DNAME='Research' AND DEPARTMENT.DNUMBER=EMPLOYEE.DNO

Link to original

Aliases

Alias (SQL)

Some queries may need to refer to the same relation twice, we can give aliases to relation names to not have to write as much: (we can do the same to the projection)

SELECT E.FNAME AS EMPLOYEE_FNAME,
	   E.LNAME AS EMPLOYEE_LNAME,
	   S.FNAME,
	   S.LNAME
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.SUPERSSN=S.SSN

Link to original

Unspecified WHERE clause

If no WHERE clause is specified, all tuples from the relations in the FROM-clause are selected and returned.

Cartesian product

Cartesian Product (SQL)

If more than one relation is specified in the FROM-clause and there is no join condition, then the Cartesian product of tuples is selected.

SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT

Link to original

Use of *

Wildcard Operator (SQL)

You can use * to retrieve all the attribute values of the selected tuples.

SELECT * FROM EMPLOYEE WHERE DNO=5

Link to original

Use of DISTINCT

Distinct Modifier (SQL)

As SQL does not treat a relation as a set, duplicate tuples can appear. To eliminate these, the DISTINCT keyword is used.

SELECT SALARY FROM EMPLOYEE
-- { 1200, 1400, 1400, 1600 }
 
SELECT DISTINCT SALARY FROM EMPLOYEE
-- { 1200, 1400, 1600 }

Link to original

Set Operations

Set Operations (SQL)

SQL provides some set operations for us to use: UNION, EXCEPT, INTERSECTION. The resulting relations of these set operations are sets of tuples - duplicates are eliminated from the result.

Set operations apply only to union compatible relations:

1. Two relations must have the same number of attributes.
2. Each corresponding pair of attributes has the same domain.

Set operations:

• Union (SQL)
• Except (SQL)

Link to original

Pattern Matching

Pattern Matching (SQL)

We can use the LIKE comparison operator to compare partial strings. Two reserved characters are used:

• % which replaces an arbitrary number of characters
• _ which replaces a single arbitrary character We can use an escape character to use these, e.g. LIKE '%15\%%'.

Examples:

• Retrieve all employees whose address is in Houston, Texas.

  SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston,TX%'

• Retrieve all employees who were born during the 1950s.

  SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDATE LIKE ’__5_______’

Link to original

Arithmetic Operations

We can use standard arithmetic operators, +, -, * and / applied to numeric values in an SQL query result.

SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX'

Use ORDER BY to create ordered relations

Sorting (SQL)

The ORDER BY clause is used to sort the tuples in a query result based on the values of some attribute(s). By default it is sorted in ascending order.

-- Default Sort
SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER
ORDER BY DNAME, LNAME
 
-- Ascending and Descending Sort
SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER
ORDER BY DNAME DESC, LNAME ASC

Link to original