Introduction

Last day, we were talking about more advanced query processing.
This included:
- How to do subqueries in the FROM clause,
- How to perform joins in SQL (NATURAL JOIN, JOIN..ON, OUTER JOIN..ON),
- How to control whether SQL treats results as sets or bags (DISTINCT),
- How to do aggregation (COUNT, SUM, AVG) and group in SQL (using GROUP BY), and
- How to control which groups are selected to be output (HAVING)
Today, we begin by looking at how to do basic DML and DDL commands in SQL, then look at more advanced DML commands, and active database topics.

Database Modifications

Recently, we have looked at the query language aspects of SQL.
Earlier in the semester we have seen some of the data definition aspects of SQL such as CREATE TABLE and ALTER TABLE.
We have not described the DML (data manipulation) aspects of SQL as much.
There are three basic kinds of DML operations which we now consider in turn: insert, delete, update.
We have seen on homeworks and before that we can insert rows into a table `R` with a syntax like:
```
INSERT INTO R(A_1, A_2, ..., A_n) VALUES (v_1, v_2, ..., v_n); 
```
In the above, the A_i's are used to specify which attributes of `R` are going to be in the item inserted and in what order.

Suppose we had a table created with the syntax:

CREATE TABLE R(
  A VARCHAR(20) PRIMARY KEY,
  B VARCHAR(20),
  C INT DEFAULT 7
);

Then the following would each insert the same tuple into the database:

INSERT INTO R VALUES ('hi', 'there', 7);
INSERT INTO R(A,B,C) VALUES ('hi', 'there', 7);
INSERT INTO R(B,A,C) VALUES ('there', 'hi',  7);
INSERT INTO R(B,A) VALUES ('there', 'hi');
INSERT INTO R(A,B) VALUES ('hi', 'there');

The basic insert can also be used to insert multiple rows in one go using a comma separated list of tuples:
```
INSERT INTO R(A,B) VALUES ('hi', 'there'), ('good', 'bye');
```

INSERTs with a Query

We can also have INSERTs which make use of a query.
For example, suppose we want to add to Studio(name, address, presC#) all movie studios that are mentioned in Movies(title, year, length, genre, studioName, producerC#) but do not appear in Studio.

We could do this with the command:

INSERT INTO Studio(name) 
  SELECT DISTINCT studioName 
  FROM Movies
  WHERE studioName NOT IN 
    (SELECT name
     FROM Studio);

Deletion

The SQL syntax to delete some rows from a database is:
```
   DELETE FROM R WHERE <condition>;
```
The WHERE clause above is allowed to have anything that a SELECT FROM WHERE clause can have (including subqueries).
For example, suppose we want to delete all movies from before 1970 whose title contains the word 'Star'. We could do this with:
```
DELETE FROM Movies
WHERE title LIKE '%Star%'
  AND year < 1970;
```

Updates

The SQL command to update an existing tuple is UPDATE which has the syntax:
```
UPDATE R SET <new-value asssignment > WHERE <condition>
```
<new-value asssignment > is a comma separated list of items of the form: <attribute> = <value-expression>
<condition> is any condition allowed to appear in a SELECT FROM WHERE clause.
Suppose we wanted to precede all names of MovieExec's which are presidents of Studios with the honorific 'Pres. '. We could do this using the update command:
```
UPDATE MovieExec
SET name = 'Pres. ' || name  /*Remember || is concatenation in SQL */
WHERE cert# IN 
  (SELECT presC# 
   FROM Studio);
```

In-Class Exercise

Suppose we have SQL tables for the three relations EMPLOYEE(id, name, address, salary), PROJECT(id, name, manager_id), WORKS_ON(eid, pid, hours).
Give the SQL needed to give a 10% raise to all managers whose name contains "Smith" that supervise projects on which more than 50 hours of work has been done.
Please post your solution to the Nov 8 In-Class Exercise Thread.

SQL Data Definition Commands

The three SQL data definition language commands we have seen so far are CREATE TABLE, ALTER TABLE, DROP TABLE.
A database in Sqlite is basically a file containing several tables.
Most relational DBMSs (Mysql, Postgres, ORACLE, DB2) are not single file based and have a command:
```
CREATE DATABASE <some_database_name>;
```
to create a database, then have some syntax (for example, USE <some_database_name>) to specify which database our CREATE TABLE commands are intended for.
To get rid of such a database we use the DROP DATABASE command.
Some RDBMSs (Postgres, Oracle, DB2) let you specify the physical location you are storing your databases and tables by declaring tablespaces with the syntax:
```
CREATE TABLESPACE <some_name>
LOCATION 'some_folder';
```
This can be useful if we want some tables on SSD versus spin drive.
You can then specify the tablespace a database or table uses with a WITH clause:
```
CREATE DATABASE foo WITH TABLESPACE = blah;
```
Some databases such as Mysql have different possible file formats for the tables in a database. These can be specified in the CREATE TABLE statement:
```
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;
```

Specifying Integrity Constraints in SQL

We have seen how to specify using PRIMARY KEY what are the primary key attributes of a relation in SQL.
We have also mentioned we can use the keyword UNIQUE to specify something is a key.
We can also have a constraint that some attribute is not empty/null using NOT NULL.
We said in the relational model context that an attribute has a foreign key constraint (referential integrity constraint) on it if its values are restricted to be values that appear as attributes values of some attributes of a different relation.

We can specify these kind of constraints in SQL using the FOREIGN KEY .. REFERENCES syntax. For example:

CREATE TABLE Studio (
  name CHAR(30) PRIMARY KEY, 
  address VARCHAR(255) NOT NULL, 
  presC# INT, 
  FOREIGN KEY (presC#) REFERENCES MovieExec(cert#)
);

Data Modification and Integrity Constraints

SQL lets you specify what happens to a table that has a referential integrity constraint on it when a tuple is inserted, updated or modified.
Three common choices are:
1. Reject the modification: This is the SQL default policy.
2. Cascade Policy. Under this policy changes to the referenced attributes are mimicked at the foreign key.
3. Set-Null Policy. Here, when a modification to the referenced relation affects a foreign key value, the latter is changed to NULL.

The policy used by SQL can be specified in an ON INSERT, ON DELETE, or ON UPDATE clause. For example:

CREATE TABLE Studio (
  name CHAR(30) PRIMARY KEY,
  address VARCHAR(255),
  presC# INT REFERENCES MovieExec(cert#) 
    ON DELETE SET NULL
    ON UPDATE CASCADE 
);

CHECK Constraints

Suppose we want to restrict a base type SQL like INT so that the tuples inserted all are within a subset of the type.
For example, we might want that if we insert a MovieExec that their salary should be at least $10000000, or that their gender is specified using a single letter among M, F, or O.

We can enforce this constraint using an attribute based CHECK clause:

CREATE TABLE MovieExec (
   name VARCHAR(30) PRIMARY KEY,
   gender CHAR(1) CHECK (gender IN ('F', 'M', 'O')),
   salary INT CHECK (salary >= 10000000);
);

We are allowed to specify CHECK on whole tuples:

CREATE TABLE MovieStar (
  name CHAR(30) PRIMARY KEY,
  address VARCHAR(255),
  gender CHAR(1),
  birthdate DATE,
  CHECK (gender = 'F' OR name NOT LIKE 'Boring %') );

Naming Constraints and Altering Them

We can give a name to a constraint using the keyword CONSTRAINT:

CREATE TABLE MovieExec (
   name VARCHAR(30) PRIMARY KEY,
   gender CHAR(1) CHECK (gender IN ('F', 'M', 'O')),
   salary INT CONSTRAINT KEEP_RICH CHECK (salary >= 10000000);
);

You can change named constraints using ALTER CONSTRAINT:
```
ALTER CONSTRAINT KEEP_RICH CHECK(salary >= 15000000);
```

We can also drop or add constraints from/to tables using ALTER TABLE:

ALTER TABLE MovieExec DROP CONSTRAINT KEEP_RICH;
ALTER TABLE MovieExec ADD 
  CONSTRAINT NOT_TOO_RICH CHECK (salary <= 10000000);

Active Databases

A DBMS that can perform checks or actions automatically when an event occurs on it is called an active database.
CHECK, ON INSERT, ON DELETE, ON UPDATE are all simple actions.
SQL supports two more general ways of making databases active:
1. Assertions, which are boolean-valued SQL expressions that must be true at all times.
2. Triggers, which are a series of actions that are associated with an event in the DBMS (such as an insert, etc).

Assertions

A standard SQL assertion has the syntax:

CREATE ASSERTION <assertion-name> 
  CHECK (<condition>);

Any database operation that causes an assertion to be false will be rejected by the database.

For example,

CREATE ASSERTION RichPres CHECK 
  (NOT EXISTS 
    (SELECT Studio.name 
     FROM Studio, MovieExec 
     WHERE presC# = cert# AND netWorth < 10000000 ) 
);

If we want to get rid of an assertion we have previously defined, we can use the command DROP ASSERTION:
```
DROP ASSERTION <assertion-name>
```
Not all DBMSs support assertions. For example, DB2 and Oracle both do. Mysql and Postgres do not.

Triggers

To specify a trigger (aka event-condition-action (ECA) rule), you need to say the time (BEFORE, AFTER, INSTEAD OF) some event (INSERT, UPDATE [OF], DELETE, TRUNCATE) to check for, what before and after values should be called, and what to do.

Below is a simple example of this from the book:

CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
  OLD ROW AS OldTuple,
  NEW ROW AS NewTuple
FOR EACH ROW 
WHEN (OldTuple.netWorth > NewTuple.netWorth)
  UPDATE MovieExec
  SET netWorth = OldTuple.netWorth 
  WHERE cert# = NewTuple.cert#;

The DROP TRIGGER command can be used to get rid of a trigger.
Sqlite, Mysql, PostgreSQL, Oracle, DB2 all support triggers, however, their syntaxes differ.
The above code is the books. It most closely matches DB2's syntax, but still is not the same. For example, OLD ROW would be OLD TABLE.
PostgresSQL only supports one REFERENCING line, Mysql, Oracle, Sqlite do not support REFERENCING.
Sqlite, DB2, PostgreSQL, Oracle support WHEN, Mysql doesn't appear to.
So to get a working trigger you probably need to check your particular DBMSs documentation.
Let's look at a very small example for Sqlite and Mysql...

Sqlite/Mysql Trigger Example

CREATE TABLE LOG_DATA (
   LEVEL CHAR(1),  
   MESSAGE VARCHAR(30)
);

CREATE TABLE LOG_COUNTS_BY_LEVEL (
  LEVEL CHAR(1),
  NUM_MESSAGES INT
);

CREATE TRIGGER UPDATE_LOG_COUNTS
AFTER INSERT ON LOG_DATA
BEGIN
  UPDATE LOG_COUNTS_BY_LEVEL 
  SET NUM_MESSAGES = NUM_MESSAGES + 1
  WHERE LEVEL = NEW.LEVEL; 
END;

INSERT INTO LOG_COUNTS_BY_LEVEL VALUES
  ('E', 0), ('W', 0), ('I', 0);
INSERT INTO LOG_DATA VALUES
   ('W', 'Oh! No! I forgot the camembert!');
SELECT * FROM LOG_COUNTS_BY_LEVEL;

Between the BEGIN and END commands above we could have had multiple SQL commands.

To make the above code work for Mysql, we replace the trigger definition as:

DELIMITER //
CREATE TRIGGER UPDATE_LOG_COUNTS
AFTER INSERT ON LOG_DATA
FOR EACH ROW
BEGIN
  UPDATE LOG_COUNTS_BY_LEVEL 
  SET NUM_MESSAGES = NUM_MESSAGES + 1
  WHERE LEVEL = NEW.LEVEL; 
END //
DELIMITER ;

DML, Constraints, Assertions, Triggers

Outline