SQL - DML

4 operations on data

• Create = INSERT

• Read = SELEECT

• Update = UPDATE

• Delete = DELETE

  • Different than drop since you’re just deleting a few tuples and not the entrire database

Organization of Query

• SELECT c1, c2, .., cm (attrs names)

• FROM R1, R2, .., Rn (relation names)

• WHERE condition

• ORDER BY list of attrs

• GROUP BY

execution order: FROM → WHERE → SELECT

SELECT

• SELECT * - returns all cols

• SELECT x, y - only returns these cols

• Can have simple expressions: SELECT length * 60 AS durationInSec

• Can have constants: SELECT ‘secs’ as unit (will create a new col called unit and will have secs in each row for that)

DISTINCT

doesn’t allow duplication

• SELECT DISTINCT attr

Aliasing attrs

rename attrs of the result

• SELECT movieTitle as name

• SELECT movieTitle name

WHERE

• conditional expressions combined using logical operations (AND, OR, NOT)

• conditional expressions: output a BOOLEAN value (TRUE, FALSE, UNKNOWN) using comparion operations (<>, LIKE, || (string concantation), =, <, +, -)

LIKE - String Comparison: Pattern Matching

Like allows you to use 2 patterns:

• % (0+ chars)

• _ (1 char)

• string LIKE pattern

Ex.

• WHERE movieTitle LIKE ‘Toy%’ (can be Toy Story 2, Toy Story, Toy)

Strings: quotes

• LIKE puts patterns in: ‘‘

• single quote in SQL: ‘‘

• match a single quote symbol (‘): WHERE movieTitle LIKE ''''

• two quote symbols (‘‘): WHERE movieTitle LIKE ''''''

• match % or _: using ESCAPE character

ESCAPE

ESCAPE ‘!’ means that putting ‘!’ before special characters make them a string

• can be any character: s LIKE ‘x%%x%’ ESCAPE x makes x into the escape — it means any string that starts or end with % since only the middle % has its any string interpretation

NULL

special value

• any arithmetic operation involving a NULL results in a NULL

• comparison involving NULL results in an UNKNOWN

Logical operations wtih T, F, and U

• True = 1, False = 0, Unknown = 0.5

• p OR q = MAX(p,q)

• p AND q = MIN(p,q)

• NOT p = 1 -p

If the condition evaluates to UNKNOWN, then the tuple will not be returned.

NULL, UNKNWON, and EMPTY SET

• attrs can be NULL

• results of a condition can be UNKNOWN

• results of a query can be Empty Set

ORDER BY

presents the result in a sorted order, by default it’s ascending

• ORDER BY x

• ORDER BY x ASC, y DESC

• can be expressions: ORDER BY quantity * price DESC

• can be NULL: NULL is either the smallest/largest values and will be specificed in exams

Joining multiple tables

• FROM R1, R2

  • Cartesian product (CROSS JOIN).

  • Same as: FROM R1 CROSS JOIN R2 (nobody uses it)

• FROM R1 r1, R2 r2 (self join)

• FROM R1 JOIN R2 ON attr1 = attr2

  • For when two cols has the same behavior but different names, usually on a primary key to foreign key

• FROM R1 INNER JOIN R2

• FROM R1 NATURAL JOIN R2

• FROM R1 FULL OUTER JOIN R2

• FROM R1 LEFT OUTER JOIN R2

• FROM R1 RIGHT OUTER JOIN R2

Disambiguating Attrs

use relation name in attrs that exist in both tables: R1.name

Cross Join ex:

SELECT name

FROM Movies, MovieExec

WHERE title = ‘Jurassic Park’ AND directorC# = cert#;

1. Compute Cartesian product of the tables in FROM

2. Filter tuples that evaluate TRUE for the WHERE clause

3. Evaluate the SELECT clause

FOR each tuple t1 in Movies

FOR each tuple t2 in MovieExec

IF the WHERE-clause is satisfied when the values from t1, t2 are
substituted

Evaluate the SELECT-clause and produce the tuple of values
that results.

Inner Join

Natural Join

• The join condition is that all pairs of attributes from the two relations having a common name are equated, and there are no other conditions.

• One of each pair of equated attributes is projected out

Full Outer Join

Includes all rows from both tables, with NULL where there is no match.

• keyword OUTER is optional

Left Outer Join

All rows from the left table and the matching rows from the

right table

• If there’s no match, the right-side columns will have NULL.

Right Outer Join

All rows from the right table and the matching rows from the

left table

• If there’s no match, the left-side columns will have NULL.

Comparison Operators

• x IN Q

  – Returns true if x occurs in the collection Q.

• x NOT IN Q

  – Returns true if x does not occur in the collection Q.

• x > ANY Q

  – Returns TRUE if x is greater than at least one value in Q. – Can also use >=, <, <=, =, <>

• x > ALL Q

  – Returns TRUE if x is greater than every value in Q. – Can also use >=, <, <=, =, <>

• EXISTS Q

  – Returns true if Q is a non-empty collection.

• NOT EXISTS Q

  – Returns true if Q is an empty collection.

Subquery

a query that is embedded in another query.

• can return a constant (scalar value) which can be compared against another constant in the WHERE clause

• can also return a relation and used as a Collection in the WHERE Clause

• A subquery returning a relation can appear in the FROM clause

Subquery EX

SELECT name

FROM MovieExec

WHERE cert# IN

(SELECT directorC#

FROM Movies

WHERE movieTitle = ‘Jurassic Park’);

2)

SELECT e.name

FROM MovieExec e,

(SELECT m.directorC#

FROM Movies m

WHERE m.movieTitle = ‘Jurassic Park’) p WHERE e.cert# = p. directorC # ;

Set vs Bag

• Set: no dumplicated

• Bag: duplicates allowed

relations are a bag of tuples

Set and Bag Operations

Used between two subqueries which will result in a relation

• Set operations (UNION, INTERSECT, EXCEPT)

• Bag operations (UNION ALL, INTERSECT ALL, EXCEPT ALL)

Set Operation Descriptions

• Union = OR

• Intersect = AND

• Except = NOT

Bag Operation Descriptions

if there are m copies of x in R and n copies of x in R2

• Union → total (m+n) copies

• Intersect: → total MIN(m, n) copies

• Except → total MAX(m-n, 0) copies

Set UNION

All people either a movie executive Pa female movie star

(SELECT name, address

FROM MovieStar

WHERE gender = ‘F’)

UNION

(SELECT name, address

FROM MovieExec)

Set INTERSECT

people who are both movie star and executive

(SELECT name, address

FROM MovieStar )

INTERSECT

(SELECT name, address

FROM MovieExec);

Set EXCEPT

Movie stars who are not executives

(SELECT name, address

FROM MovieStar)

EXCEPT

(SELECT name, address

FROM MovieExec)

5 aggregation operations

SUM, AVG, MIN, MAX, COUNT

Aggergation

• Specified in SELECT

• applied on attrs

  • except COUNT(*) which counts the number of tuples in the table

• ignores NULL (unless it’s COUNT(*))

Aggregation Example

• SELECT COUNT(name) FROM Employee;

• SELECT SUM(salary) FROM Employee;

• SELECT COUNT(name), COUNT(DISTINCT deptName) FROM Employee;

• Second lowest salary

  • SELECT MIN(e.salary)
    FROM Employee e

    WHERE e.salary >

    (SELECT MIN(salary) FROM Employee);

GROUP BY

• comes after WHERE

• one row per group

• NULL is used and counts as a group

• usually aggregation is used within each group

• in SELECT clause with aggregations, only attrs in the GROUP BY can appear unaggregated

HAVING Clause

like a WHERE clause applied to groups

• cant exist without GROUP BY

• Only those attributes that are in the GROUP BY list may appear unaggregated in the HAVING clause

INSERT Statements with Values

A tuple (v1 , …, vn ) is inserted into the relation R, where attribute A = v iand default values are entered for all missing attributes.

INSERT INTO R(A 1 , …, A n )
VALUES (v 1 , …, v n );

The tuple ('The Maltese Falcon', 1942, 'Sydney Greenstreet') will be added to the relation StarsIn.

StarsIn(movieTitle, movieYear, starName)
INSERT INTO StarsIn(movieTitle, movieYear, starName)
   VALUES ('The Maltese Falcon', 1942, 'Sydney Greenstreet');

can be a subquery:

INSERT INTO Studio(studioName)
SELECT DISTINCT m.studioName
FROM Movies m
WHERE m.studioName NOT IN
    (SELECT st.studioName
     FROM Studio st)

DELETE

DELETE FROM R

WHERE <condition>;

The tuple ('The Maltese Falcon', 1942, 'Sydney Greenstreet') will be deleted from the relation StarsIn.

DELETE FROM StarsIn

WHERE movieTitle = 'The Maltese Falcon'

AND movieYear = 1942

AND starName = 'Sydney Greenstreet';

• we cannot simply specify a tuple to be deleted. Rather, we must describe the tuple exactly by a WHERE clause.

UPDATE syntax

UPDATE R

SET <new-value-assignments>

WHERE <condition>;

• <new-value-assignment> :<attribute> = <expression>, …, <attribute> = <expression>

Ex:

UPDATE Employee

SET salary = 9000

WHERE name = ‘Bob';

Ex. with subquery: attaching the title Pres. in front of the name of every movie executive who is the president of a studio:

UPDATE MovieExec

SET execName = 'Pres. ' || execName

WHERE cert# IN ( SELECT presC# FROM Studio );

UPDATE with FROM

• The UPDATE clause specifies the table being updated (just one table).

• UPDATE statement can also have a FROM clause.

• The UPDATE statement's WHERE clause can refer to both the updated

  table and the table(s) in the FROM clause