Week 5. SQL 2

Data Manipulation Language

Continuation of looking at Data Manipulation Language commands.

`SELECT` queries

This continues from Week 4. SQL.

Arithmetic Operations

In arithmetic operations, (cont. Week 4. SQL > Arithmetic Operations):

Constants are allowed
We can use AS to alias results as an attribute
We can use conditional statements: IF (<condition>, <true value>, <false value>)
```
SELECT IF(DEEZ > 5, True, False) AS IS_DEEZED FROM CREWMATES
```

We can convert data types of attributes using CAST(<expr> AS <type>):

SELECT CAST((SALARY / 1000) AS UNSIGNED) AS SALARY_K FROM EMPLOYEE

`INSERT` queries

Insert Query (SQL)
In the simplest form, INSERT is used to add one or more tuples to a relation.
INSERT INTO <table>
VALUES      <tuple>
Attribute values should be listed in the same order as the attributes were specified in the Create Table (SQL) command.

Although, you can specify an attribute list to account for default or Null (SQL) values.
INSERT INTO <table> (<attr list>)
VALUES      <tuple>
For example:
INSERT INTO deez (name)
VALUES      ('John Discord')
Link to original

Inserting multiple values from a `CREATE`

Inserting multiple values from a Create query (SQL)
Consider that we want to make a temporary table with some information from an existing table.

Simple two-query method:

Create the table (Create Table (SQL))
CREATE TABLE WORKS_ON_INFO (
  NAME           VARCHAR(15),
  PROJECT        VARCHAR(15),
  HOURS_PER_WEEK DECIMAL(3,1),
)
Load the table with the results of a joined query (Insert Query (SQL))
INSERT INTO WORKS_ON_INFO (NAME, PROJECT, HOURS_PER_WEEK)
SELECT      E.name, P.name, W.hours
FROM        PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE       P.number = W.no AND W.essn = E.ssn;
Complex one-query method: (Create Table (SQL) with Select Query (SQL))
CREATE TABLE WORKS_ON_INFO AS
SELECT       E.name, P.name, W.hours
FROM         PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE        P.number = W.no AND W.essn = E.ssn;
Link to original

`UPDATE` queries

Update Query (SQL)
The UPDATE query is used to modify attribute values of one or more selected tuples, a WHERE clause is used to select the tuples to be modified. An additional SET clause specifies attributes to be modified and their new values.
UPDATE <table>
SET    <attribute>=<value>, ...
WHERE  <condition>
Referential integrity should be enforced. Each command modifies tuples in the same relation.
Link to original

`DELETE` queries

Delete Query (SQL)
The DELETE command removes tuples from a relation, it includes a WHERE clause to select the tuples to be deleted. Referential integrity should be enforced.
DELETE FROM <table>
WHERE       <condition>
Typically:

Tuples are deleted from only one table at a time unless CASCADE is specified on an integrity constraint.

A missing WHERE clause will delete all tuples.

The number of tuples deleted depends on the number of tuples that satisfy the clause.

Link to original

Nested queries

Nested Queries (SQL)
A complete Select Query (SQL), otherwise known as a nested query, can be specified within the WHERE clause of another query (called the outer query).

There are different ways we can nest queries:
IN Clause (SQL)
The comparison operator IN compares a value $v$ with a set (or multi-set) of values $V$ and evaluates to TRUE if $v$ is one of the elements in $V$ :
SELECT NAME, ADDRESS
FROM   EMPLOYEE
WHERE  DNO IN (
	SELECT DNUMBER
	FROM   DEPARTMENT
	WHERE  DNAME = 'Research'
)
The nested query selects the number of the ‘Research’ department, the other query selects an employee if its department number is in the result of the nested query.

In this example, the nested query is not correlated with the outer query.
Link to original
Correlated Nested Query (SQL)
A correlated nested query is where the condition in the WHERE clause of a nested query references an attribute of a relation declared in the outer query. The result of a correlated nested query is different for each tuple of the relation in the outer query.
SELECT E.name
FROM   EMPLOYEE AS E
WHERE  E.ssn IN (
	SELECT essn
	FROM   DEPENDENT
	WHERE  essn=E.ssn
	  AND  E.name=dependent_name
)
Although these can still be re-written as a single block query with a simple Join Condition (SQL).
Link to original
Exists Function (SQL)
EXISTS is used to check whether the result of a Correlated Nested Query (SQL) is empty or not.
SELECT name
FROM   EMPLOYEE E
WHERE  EXISTS (
	SELECT *
	FROM   DEPENDENT
	WHERE  E.ssn=essn
      AND  E.name=dependent_name
)
Similarly, you can use NOT EXISTS to check for the reverse condition.
Link to original
All Comparison Operator (SQL)
The comparison operator ALL compares a single value to a set or multi-set (nested query).
SELECT name
FROM   employee
WHERE  salary > ALL(
	SELECT salary
	FROM   employee
	WHERE  department_number=5
)
Link to original
Link to original

Types of joins

Joined Relations (SQL)
Using the JOIN keyword, we can specify “joined relations”. Two joined relations look like any other relation.

There are many different types of joins:
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
Natural Join (SQL)
Natural Join (NATURAL JOIN): (same as JOIN) no join condition may be specified, implicit condition to join on attributes with the same name
Link to original

Inner Join (SQL)
Inner Join (INNER JOIN): tuple is included in the result only if a matching tuple exists in the other relation (this is the default type of JOIN)
Link to original
Outer Join (SQL)
Outer Join: all matching tuples are returned are returned (depending on type)
Left Outer Join (SQL)
Left Outer Join (LEFT OUTER JOIN): Keeps values from the left table even if they are not matched.
SELECT E.name, S.fname
FROM (
					EMPLOYEE E
	LEFT OUTER JOIN EMPLOYEE AS S
				 ON E.super_ssn=S.ssn
)
This will match employees without supervisors.
Link to original
Right Outer Join (SQL)
Right Outer Join (RIGHT OUTER JOIN): Keeps values from the right table even if they are not matched.
SELECT E.name, S.fname
FROM (
		             EMPLOYEE E
	RIGHT OUTER JOIN EMPLOYEE AS S
				  ON E.super_ssn=S.ssn
)
This will match employees who are not supervising anyone.
Link to original
Full Outer Join (SQL)
Full Outer Join (FULL OUTER JOIN): Keeps values from the both tables even if they are not matched.
SELECT E.name, S.fname
FROM (
					EMPLOYEE E
	FULL OUTER JOIN EMPLOYEE AS S
				 ON E.super_ssn=S.ssn
)
This will match employees without supervisors and employees not supervising anyone.
Link to original
Link to original
Cross Join (SQL)
Cross Join (CROSS JOIN): Returns the Cartesian Product of two or more tables.
SELECT E.name, S.name
FROM (
	           EMPLOYEE E
	CROSS JOIN EMPLOYEE S
)
Link to original
Multi-way joins: we can chain multiple joins in one query for multiple tables.
Link to original

Grouping and Aggregation Functions

Aggregate Functions (SQL)
Aggregate functions allow you to compute values from all or a subset of all of the data.
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM   EMPLOYEE
There are different types of aggregations we can use:

SUM(x): find the summation of all of the values of $x$ in the rows

MIN(x): find the smallest value of $x$ in the rows

MAX(x): find the largest value of $x$ in the rows

AVG(x): find the average value of $x$ in the rows

COUNT(x): count the number of rows of $x$ in the data This will not include NULL values.

COUNT(DISTINCT x): count the number of rows of distinct occurrences of $x$ in the data

COUNT(*): count the total number of rows

We may also use aggregations in sub-queries:
SELECT name
FROM   EMPLOYEE
WHERE (
	SELECT COUNT(*)
	FROM   DEPENDENT
	WHERE  ssn=ess
) >= 2
This will find all the names of the employees who have two or more dependents.
Link to original

Grouping (SQL)
In many cases, we want to apply Aggregate Functions (SQL) to subgroups of tuples in a relation, each subgroup of tuples consists of the set of tuples that have the same value for the grouping attributes.

We can use the GROUP BY clause for specifying the grouping attributes, which must also appear in the SELECT clause:
SELECT   <attr list>
FROM     <table>
[WHERE   <cond>]
GROUP BY <grouping attr>
Link to original

Having Clause (SQL)
The HAVING clause can be used when we want to retrieve the values of these functions for only those groups that satisfy certain conditions.
SELECT   <attr list>
FROM     <table>
[WHERE   <cond>]
GROUP BY <grouping attr>
HAVING   <cond>
For example, we could use the HAVING condition COUNT(*) > 2 to select all the sub-groups that have 3 or more rows.
Link to original

Summary of query steps

Query Steps (SQL)
An SQL query is evaluated by:

Including tables in FROM clause

Applying conditions in WHERE clause

Performing Grouping (SQL)

Applying conditions in Having Clause (SQL)

Selecting attributes in the SELECT clause

Running ORDER BY on the resulting tuples

Link to original

Assertions

General Constraints (SQL)
General constraints are those that do not fit in the basic SQL categories.

It is useful for schema assertions: outside of the scope of the built-in relational model constraints (primary / unique keys, entity integrity, referential integrity)

Defines whether the state of the database is valid at any given point in time.
Create Assertion (SQL)
We can create a new assertion using CREATE ASSERTION, which includes a name, a check keyword, and a condition clause. Enforcing the assertion is up to the database implementation, such as rejecting a query that violates it.
CREATE ASSERTION SALARY_CONSTRAINT
CHECK (
	NOT EXISTS (
		SELECT *
		FROM   EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
		WHERE  E.salary > M.salary
		  AND  E.dno=D.number
		  AND  D.mgr_ssn=M.ssn
	)
)
In this example, the salary of an employee must not be greater than the salary of the manager of the department that the employee works for.
Link to original
Link to original

Views

Views (SQL)
A view is a virtual table derived from other tables.

There are two ways they are implemented in implementation:

Query modification: copy and paste queries

View materialisation: short-term physical implementation

They are limited in terms of update operations they can perform.

Useful for security and authorisation.

Prevents redundant data storage.

Operations we can perform:
Create View (SQL)
To create a new view, we use the following syntax:
CREATE VIEW WORKS_ON1 AS
SELECT name, pname, hours
FROM   EMPLOYEE, PROJECT, WORKS_ON
WHERE  ssn=essn
  AND  pno=pnumber
Link to original
Select from the view: we can select from the view as if it were an ordinary table.
SELECT name
FROM   WORKS_ON1
WHERE  pname='productX';
Drop the view: we can drop the view when it’s no longer needed.
DROP WORKS_ON1;
Link to original

Archive Notes

Explorer

Week 5. SQL 2

Week 5. SQL 2

Data Manipulation Language

SELECT queries

Arithmetic Operations

INSERT queries

Insert Query (SQL)

Inserting multiple values from a CREATE

Inserting multiple values from a Create query (SQL)

UPDATE queries

Update Query (SQL)

DELETE queries

Delete Query (SQL)

Nested queries

Nested Queries (SQL)

IN Clause (SQL)

Correlated Nested Query (SQL)

Exists Function (SQL)

All Comparison Operator (SQL)

Types of joins

Joined Relations (SQL)

Join Condition (SQL)

Natural Join (SQL)

Inner Join (SQL)

Outer Join (SQL)

Left Outer Join (SQL)

Right Outer Join (SQL)

Full Outer Join (SQL)

Cross Join (SQL)

Grouping and Aggregation Functions

Aggregate Functions (SQL)

Grouping (SQL)

Having Clause (SQL)

Summary of query steps

Query Steps (SQL)

Assertions

General Constraints (SQL)

Create Assertion (SQL)

Views

Views (SQL)

Create View (SQL)

Graph View

Table of Contents

`SELECT` queries

`INSERT` queries

Inserting multiple values from a `CREATE`

`UPDATE` queries

`DELETE` queries