程序代写代做代考 database SQL SQL: An Implementation of the Relational Algebra

SQL: An Implementation of the Relational Algebra

P.J. McBrien

Imperial College London

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 1 / 47

SQL and RA

SQL

Development of Relational Database Systems

Relation Model and Algebra proposed by C.J.Codd in 1970

IBM deleveped a prototype relational database called System R with a query
language Structured English Query Language (SEQUEL)

SEQUEL later renamed SQL

Various commercial versions of SQL launched in late 1970’s/early 1980s

DB2
Oracle

Sybase

. . .

SQL Language Components

Data Definition Language (DDL): a relational schema with data
Data Manipulation Language (DML): a relational query and update language

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 2 / 47

SQL and RA RM to SQL DDL

SQL DML: Definition of Tables

CREATE TABLE branch
( s o r t c ode INTEGER NOT NULL ,

bname VARCHAR(20) NOT NULL ,
cash DECIMAL(10 , 2) NOT NULL

)

branch
sortcode bname cash

CREATE TABLE account (
no INTEGER NOT NULL ,
type VARCHAR(8) NOT NULL ,
cname VARCHAR(20) NOT NULL ,
r a t e DECIMAL(4 , 2 ) NULL ,
s o r t c ode INTEGER NOT NULL

)

account
no type cname rate sortcode

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 3 / 47

SQL and RA RM to SQL DDL

SQL DML: SQL Data Types

Some SQL Data Types
Keyword Semantics
BOOLEAN A logical value (TRUE, FALSE, or UNKNOWN)
BIT 1 bit integer (0, 1, or NULL)
INTEGER 32 bit integer
BIGINT 64 bit integer
FLOAT(n) An n bit mantissa floating point number
REAL 32 bit floating point number (≡ FLOAT(24))
DOUBLE PRECISION 64 bit floating point number (≡ FLOAT(53))
DECIMAL(p,s) A p digit number with s digits after the decimal point
CHAR(n) A fixed length string of n characters
VARCHAR(n) A varying length string of upto n characters
DATE A calendar date (day, month and year)
TIME A time of day (seconds, minutes, hours)
TIMESTAMP time and day together
ARRAY An ordered list of a certain datatype
MULTISET A bag (i.e. unordered list) of a certain datatype
XML XML text

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 4 / 47

SQL and RA RM to SQL DDL

SQL DML: Definition of Keys

CREATE TABLE branch
( s o r t c ode INTEGER NOT NULL ,

bname VARCHAR(20) NOT NULL ,
cash DECIMAL(10 , 2) NOT NULL ,
CONSTRAINT branch pk PRIMARY KEY ( so r t code )

)

branch
sortcode bname cash

CREATE TABLE account (
no INTEGER NOT NULL ,
type VARCHAR(8) NOT NULL ,
cname VARCHAR(20) NOT NULL ,
r a t e DECIMAL(4 , 2 ) NULL ,
s o r t c ode INTEGER NOT NULL ,
CONSTRAINT account pk PRIMARY KEY ( no ) ,
CONSTRAINT accoun t f k FOREIGN KEY ( so r t code )
REFERENCES branch

)

account
no type cname rate sortcode

account(sortcode)
fk
⇒ branch(sortcode)

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 5 / 47

SQL and RA RM to SQL DDL

Keys and the Primary Key

Keys

The alternative keys of a table are called candidate keys

Primary Key

Choose the key most often used to access a table as the primary key

Has no logical impact on the relational model

Has an operation impact: index created that accesses the data faster

All other keys are called secondary keys

Declaring Primary Keys after table creation

ALTER TABLE branch
ADD CONSTRAINT branch pk PRIMARY KEY ( so r t c od e ) ;

Declaring Secondary Keys for a table

CREATE UNIQUE INDEX branch bname key ON branch ( bname )

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 6 / 47

SQL and RA RM to SQL DDL

SQL DML: Inserting, Updating and Deleting Data

INSERT INTO account
VALUES (100 , ’ c u r r e n t ’ , ’ McBrien , P . ’ ,NULL , 67 ) ,
(101 , ’ d e po s i t ’ , ’ McBrien , P . ’ , 5 . 2 5 , 6 7 ) ,
(103 , ’ c u r r e n t ’ , ’Boyd , M. ’ ,NULL , 34 ) ,
(107 , ’ c u r r e n t ’ , ’ P o u l o v a s s i l i s , A . ’ ,NULL , 56 ) ,
(119 , ’ d e po s i t ’ , ’ P o u l o v a s s i l i s , A . ’ , 5 . 5 0 , 5 6 ) ,
(125 , ’ c u r r e n t ’ , ’ Ba i l e y , J . ’ ,NULL , 56 )

account
no type cname rate sortcode

100 ’current’ ’McBrien, P.’ NULL 67
101 ’deposit’ ’McBrien, P.’ 5.25 67
103 ’current’ ’Boyd, M.’ NULL 34
107 ’current’ ’Poulovassilis, A.’ NULL 56
119 ’deposit’ ’Poulovassilis, A.’ 5.50 56
125 ’current’ ’Bailey, J.’ NULL 56

UPDATE account
SET type=’ d e p o s i t ’
WHERE no=100

account
no type cname rate sortcode

100 ’deposit’ ’McBrien, P.’ NULL 67
101 ’deposit’ ’McBrien, P.’ 5.25 67
103 ’current’ ’Boyd, M.’ NULL 34
107 ’current’ ’Poulovassilis, A.’ NULL 56
119 ’deposit’ ’Poulovassilis, A.’ 5.50 56
125 ’current’ ’Bailey, J.’ NULL 56

DELETE
FROM account
WHERE no=100

account
no type cname rate sortcode

101 ’deposit’ ’McBrien, P.’ 5.25 67
103 ’current’ ’Boyd, M.’ NULL 34
107 ’current’ ’Poulovassilis, A.’ NULL 56
119 ’deposit’ ’Poulovassilis, A.’ 5.50 56
125 ’current’ ’Bailey, J.’ NULL 56

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 7 / 47

SQL and RA RA to SQL DML

SQL DML: An Implementation of the RA

SQL SELECT statements: Rough Equivalence to RA

SELECT A1, …,An
FROM R1, …,Rm
WHERE P1
AND …
AND Pk

πA1,…,AnσP1∧…∧PkR1 × . . .× Rm

SQL SELECT implements RA π, σ and ×

πbname,no σbranch.sortcode=account.sortcode∧account.type=‘current’(branch× account)

SELECT branch . bname ,
account . no

FROM account , b ranch
WHERE account . s o r t c od e=branch . s o r t c od e
AND account . type= ’ c u r r e n t ’

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 8 / 47

SQL and RA RA to SQL DML

Naming columns in SQL

Column naming rules in SQL

You must never have an ambiguous column name in an SQL statement

You can use SELECT * to indicate all columns (i.e. have no projection)

You can use tablename.* to imply all columns from a table

✓

SELECT branch . bname ,
account . s o r t c od e

FROM account , branch
WHERE account . s o r t c od e=

branch . s o r t c od e
AND account . t ype=’ c u r r e n t ’

✗

SELECT bname ,
s o r t c od e

FROM account , branch
WHERE account . s o r t c od e=

branch . s o r t c od e
AND type=’ c u r r e n t ’

✓

SELECT bname ,
account . s o r t c od e

FROM account , branch
WHERE account . s o r t c od e=

branch . s o r t c od e
AND type=’ c u r r e n t ’

✓

SELECT branch .∗ ,
no

FROM account , branch
WHERE account . s o r t c od e=

branch . s o r t c od e
AND type=’ c u r r e n t ’

sortcode bname cash no
67 ’Strand’ 34005.00 100
34 ’Goodge St’ 8900.67 103
56 ’Wimbledon’ 94340.45 107
56 ’Wimbledon’ 94340.45 125

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 9 / 47

SQL and RA RA to SQL DML

Quiz 1: Translating RA into SQL

Which SQL query implements πbname,no σtype=‘deposit’(account ⋊⋉ branch)?

SELECT ∗
FROM account , branch
WHERE type=’ d e p o s i t ’

SELECT bname , no
FROM account , branch
WHERE type=’ d e p o s i t ’

SELECT bname , no
FROM branch , account
WHERE branch . s o r t c ode=

account . s o r t c ode
AND type=’ d e p o s i t ’

SELECT bname , no
FROM account , branch
WHERE branch . s o r t c ode=

account . no
AND type=’ d e p o s i t ’

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 10 / 47

SQL and RA RA to SQL DML

Connectives Between SQL SELECT statements

Binary operators between SELECT statements

SQL UNION implements RA ∪

SQL EXCEPT implements RA −

SQL INTERSECT implements RA ∩

Note that two tables must be union compatible: have the same number and type
of columns

πnoaccount− πnomovement

SELECT no
FROM account
EXCEPT
SELECT no
FROM movement

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 11 / 47

SQL and RA Joins

SQL Joins: Four ways of asking branch ⋊⋉ account

‘Classic’ SQL Join Syntax

SELECT branch . ∗ , no , type , cname , r a t e
FROM branch , account
WHERE branch . s o r t c ode=account . s o r t c ode

Modern SQL Join Syntax

SELECT branch . ∗ , no , type , cname , r a t e
FROM branch JOIN account ON branch . s o r t c ode=account . s o r t c ode

Special Syntax for Natural Join

SELECT ∗
FROM branch NATURAL JOIN account

Another Special Syntax for Natural Join

SELECT branch . ∗ , no , type , cname , r a t e
FROM branch JOIN account USING ( so r t code )

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 12 / 47

SQL and RA Joins

Overview of RA and SQL correspondances

RA and SQL
RA Operator SQL Operator
π SELECT
σ WHERE
R1 × R2 FROM R1,R2 or FROM R1 CROSS JOIN R2
R1 ⋊⋉ R2 FROM R1 NATURAL JOIN R2

R1
θ

⋊⋉ R2 FROM R1 JOIN R2 ON θ
R1 − R2 R1 EXCEPT R2
R1 ∪ R2 R1 UNION R2
R1 ∩ R2 R1 INTERSECT R2

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 13 / 47

SQL and RA Joins

Try some examples yourself . . .

medusa-s2(pjm)-4$ psql -h db -U lab -d bank branch -W
Password:
bank branch=> SELECT *

bank branch-> FROM branch NATURAL JOIN account;

67 | Strand | 34005.00 | 100 | current | McBrien, P. |
67 | Strand | 34005.00 | 101 | deposit | McBrien, P. | 5.25
34 | Goodge St | 8900.67 | 103 | current | Boyd, M. |
56 | Wimbledon | 94340.45 | 107 | current | Poulovassilis, A. |
56 | Wimbledon | 94340.45 | 119 | deposit | Poulovassilis, A. | 5.50
56 | Wimbledon | 94340.45 | 125 | current | Bailey, J. |

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 14 / 47

SQL and RA Joins

. . . and find out that not all DBMSs are the same

medusa-s2(pjm)-4$ sqsh -S sqlserver -X -U lab -D bank branch
Password:

[21] sqlserver.bank branch.1> SELECT *
[21] sqlserver.bank branch.2> FROM branch NATURAL JOIN account

[21] sqlserver.bank branch.3> \go

Msg 102, Level 15, State 1

Server ’DOWITCHER’, Line 2

Line 2: Incorrect syntax near ’account’.

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 15 / 47

Bags and Sets Bags or Sets

SQL: Bags and Sets

SELECT ALL sortcode
FROM account

≈ πsortcodeaccount
sortcode

67
67
56
56
56
34

SELECT DISTINCT sortcode
FROM account

πsortcodeaccount
sortcode

34
56
67

SQL SELECT: Bag semantics

By default, an SQL SELECT (equivalent to an RA π) does not eliminate
duplicates, and returns a bag (or multiset) rather than a set.

Any SELECT that does not cover a key of the input relation, and requires a set
based answer, should use DISTINCT.

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 16 / 47

Bags and Sets Bags or Sets

Quiz 2: Correct use of SELECT DISTINCT (1)

branch(sortcode,bname,cash)
key branch(sortcode)
key branch(bname)

Which SQL query requires the use of DISTINCT in order to avoid the
possibility of a bag being produced?

SELECT ∗
FROM branch
WHERE cash >10000

SELECT so r t c od e
FROM branch
WHERE cash >10000

SELECT bname , cash
FROM branch

SELECT cash
FROM branch
WHERE cash >10000

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 17 / 47

Bags and Sets Bags or Sets

Quiz 3: Correct use of SELECT DISTINCT (2)

branch(sortcode,bname,cash)
account(no,type,cname,rate,sortcode)

key branch(sortcode)
key branch(bname)
key account(no)

Which SQL query requires the use of DISTINCT in order to avoid the
possibility of a bag being produced?

SELECT ∗
FROM branch NATURAL JOIN

account

SELECT branch . so r tcode , type , r a t e
FROM branch NATURAL JOIN

account

SELECT branch . so r tcode , no
FROM branch NATURAL JOIN

account

SELECT branch . so r tcode , no , cash
FROM branch NATURAL JOIN

account

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 18 / 47

Bags and Sets Bags or Sets

Quiz 4: Operators that might produce bags

If R and S are sets, which RA operator could produce a bag result if the
implementation did not check for duplicates?

σR

R ∪ S

R− S

R × S

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 19 / 47

Bags and Sets Bags or Sets

Bag and Set operations in SQL

RA Operator Set Based SQL Bag Based SQL
πA1,…,An SELECT DISTINCT A1, . . . , An SELECT ALL A1, . . . , An
R1 × . . .×Rm FROM R1, . . . , Rm FROM R1, . . . , Rm
σP1,…,Pk WHERE P1 AND . . .AND Pk WHERE P1 AND . . .AND Pk
R1 ∪ R2 R1 UNION DISTINCT R2 R1 UNION ALL R2
R1 − R2 R1 EXCEPT DISTINCT R2 R1 EXCEPT ALL R2
R1 ∩ R2 R1 INTERSECT DISTINCT R2 R1 INTERSECT ALL R2

Chosing between set and bag semantics

If you omit DISTINCT or ALL, then the defaults are:
SELECT ALL
UNION DISTINCT
EXCEPT DISTINCT
INTERSECT DISTINCT

No FROM DISTINCT or WHERE DISTINCT?

There is no need for DISTINCT or ALL around FROM (×) and WHERE (σ) cannot
introduce any duplicates, and any existing duplicates can be removed in the SELECT

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 20 / 47

Bags and Sets Bags or Sets

Project-Select-Product Queries

SQL SELECT statements: Exact Equivalence to RA

SELECT DISTINCT A1, …,An
FROM R1, …,Rm
WHERE P1
AND …
AND Pk

≡ πA1,…,AnσP1∧…∧PkR1 × . . .× Rm

SQL SELECT implements RA π, σ and ×

Omit DISTINCT when either

you known A1, …,An cover a key
you want a bag (rather than set) answer

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 21 / 47

Bags and Sets Bags or Sets

Quiz 5: SQL EXCEPT

SELECT no
FROM movement
EXCEPT
SELECT no
FROM account

movement
mid no amount tdate
1000 100 2300.00 5/1/1999
1001 101 4000.00 5/1/1999
1002 100 -223.45 8/1/1999
1004 107 -100.00 11/1/1999
1005 103 145.50 12/1/1999
1006 100 10.23 15/1/1999
1007 107 345.56 15/1/1999
1008 101 1230.00 15/1/1999
1009 119 5600.00 18/1/1999

account
no type cname rate sortcode

What is the result of the above SQL query?

no
100
101
103
107
119
125

no
100
101
103
107
119

no
100
100
101
107

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 22 / 47

Bags and Sets Bags or Sets

Quiz 6: SQL EXCEPT ALL

SELECT no
FROM movement
EXCEPT ALL
SELECT no
FROM account

account
no type cname rate sortcode

What is the result of the above SQL query?

no
100
101
103
107
119
125

no
100
101
103
107
119

no
100
100
101
107

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 23 / 47

Bags and Sets Bags or Sets

Table Aliases and Self Joins

Table and Column Aliases

The SQL operator AS allows a column or table name to be renamed.
Essential when needing to join a table with itself

List people with a current and a deposit account

SELECT cu r r e n t a c c o u n t . cname ,
c u r r e n t a c c o u n t . no AS cu r r e n t n o ,
d e p o s i t a c c o u n t . no AS d e p o s i t n o

FROM account AS c u r r e n t a c c o u n t
JOIN account AS d e p o s i t a c c o u n t
ON cu r r e n t a c c o u n t . cname=d e p o s i t a c c o u n t . cname
AND cu r r e n t a c c o u n t . type= ’ c u r r e n t ’
AND d e p o s i t a c c o u n t . type= ’ d e p o s i t ’

cname current no deposit no
‘McBrien, P.’ 100 101
‘Poulovassilis, A.’ 107 119

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 24 / 47

Bags and Sets Bags or Sets

Table Aliases

current account
no type cname rate sortcode
100 ’current’ ’McBrien, P.’ NULL 67
101 ’deposit’ ’McBrien, P.’ 5.25 67
103 ’current’ ’Boyd, M.’ NULL 34
107 ’current’ ’Poulovassilis, A.’ NULL 56
119 ’deposit’ ’Poulovassilis, A.’ 5.50 56
125 ’current’ ’Bailey, J.’ NULL 56

deposit account
no type cname rate sortcode
100 ’current’ ’McBrien, P.’ NULL 67
101 ’deposit’ ’McBrien, P.’ 5.25 67
103 ’current’ ’Boyd, M.’ NULL 34
107 ’current’ ’Poulovassilis, A.’ NULL 56
119 ’deposit’ ’Poulovassilis, A.’ 5.50 56
125 ’current’ ’Bailey, J.’ NULL 56

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 25 / 47

Bags and Sets Bags or Sets

Worksheet: Translating Between Relational Algebra and SQL

account
no type cname rate sortcode

movement(no)
fk
⇒ account.no

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 26 / 47

Bags and Sets Set Operations

Set Operations: IN

IN operator tests for membership of a set

SELECT ∗
FROM account
WHERE type=’ c u r r e n t ’
AND no IN (100 ,101)

Can use nested SELECT to generate set

SELECT no
FROM account
WHERE type=’ c u r r e n t ’
AND no IN (SELECT no

FROM movement
WHERE amount>500)

≡

SELECT DISTINCT account . no
FROM account JOIN movement

ON account . no=movement . no
WHERE type=’ c u r r e n t ’
AND amount>500

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 27 / 47

Bags and Sets Set Operations

Quiz 7: SQL Set Membership Testing

SELECT no
FROM account
WHERE type=’ c u r r e n t ’
AND no NOT IN

( SELECT no
FROM movement
WHERE amount>500)

6≡

SELECT DISTINCT account . no
FROM account

JOIN movement
ON account . no=movement . no

WHERE type=’ c u r r e n t ’
AND NOT amount>500

What is the result of the above SQL query?

no
100
103
107
125

no
100
103
107

no
103
107
125

no
103
107

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 28 / 47

Bags and Sets Set Operations

Set Operations: EXISTS

Testing for Existence

IN can be used to test if some value is in a relation, either listed, or produced by
some SELECT statement

EXISTS can be used to test if a SELECT statement returns any rows

List people without a deposit account

SELECT DISTINCT cname
FROM account
WHERE cname NOT IN
( SELECT cname

FROM account
WHERE type=’ d e p o s i t ’ )

≡

SELECT DISTINCT cname
FROM account
WHERE NOT EXISTS
( SELECT ∗

FROM account AS d e p o s i t a c c o u n t
WHERE type=’ d e p o s i t ’
AND account . cname=cname )

cname
‘Boyd, M.’
‘Bailey, J.’

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 29 / 47

Bags and Sets Set Operations

Correlated Subquery

A correlated subquery contains a reference to the columns of the outer query in
which the subquery is contained

Conceptually, result is as if the subquery were executed for each row considered
by the WHERE clause

List people without a deposit account

SELECT DISTINCT cname
FROM account
WHERE NOT EXISTS
( SELECT ∗

FROM account AS d e p o s i t a c c o u n t
WHERE type=’ d e p o s i t ’
AND account . cname=cname )

cname
‘Boyd, M.’
‘Bailey, J.’

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 30 / 47

Bags and Sets Set Operations

Set Operations: EXISTS

NOT EXISTS and EXCEPT

Most queries involving EXCEPT can be also written using NOT EXISTS

EXCEPT relatively recent addition to SQL

πnoaccount− πnomovement

SELECT no
FROM account
EXCEPT
SELECT no
FROM movement

≡

SELECT no
FROM account
WHERE NOT EXISTS (SELECT no

FROM movement
WHERE no=account . no )

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 31 / 47

Bags and Sets Set Operations

Set Operations: SOME and ALL

Can test a value against members of a set

V op SOME S is TRUE is there is at least one Vs ∈ S such that V op Vs

V op ALL S is TRUE is there are no values Vs ∈ S such that NOT V op Vs

names of branches that only have current accounts

SELECT bname
FROM branch
WHERE ’ c u r r e n t ’=ALL (SELECT type

FROM account
WHERE branch . s o r t c od e=account . s o r t c od e )

names of branches that have deposit accounts

SELECT bname
FROM branch
WHERE ’ d e p o s i t ’=SOME (SELECT type

FROM account
WHERE branch . s o r t c od e=account . s o r t c od e )

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 32 / 47

Bags and Sets Set Operations

Worksheet: Set Operations

branch
sortcode bname cash

56 ’Wimbledon’ 94340.45
34 ’Goodge St’ 8900.67
67 ’Strand’ 34005.00

account
no type cname rate sortcode

key branch(sortcode)
key branch(bname)
key movement(mid)
key account(no)

movement(no)
fk
⇒ account(no)

account(sortcode)
fk
⇒ branch(sortcode)

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 33 / 47

Bags and Sets Set Operations

Worksheet: Set Operations (3)

Write an SQL query without using any negation (i.e. without the use of NOT or
EXCEPT) that list accounts with no movements on or before the 11-Jan-1999.

SELECT no
FROM account
WHERE ’11− jan −1999 ’=ALL (SELECT amount

FROM movement
WHERE account . no=movement . no )

≡

SELECT no
FROM account
WHERE NOT 500null

What is the result of the SQL query above?

no
100
101
103
107
119
125

no
100
103
107
125

no
101
119

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 39 / 47

Null 3VL

SQL implements three valued logic

AND

P1 AND P2 P2
TRUE UNKNOWN FALSE

TRUE TRUE UNKNOWN FALSE
P1 UNKNOWN UNKNOWN UNKNOWN FALSE

FALSE FALSE FALSE FALSE

P1 OR P2 P2
TRUE UNKNOWN FALSE

TRUE TRUE TRUE TRUE
P1 UNKNOWN TRUE UNKNOWN UNKNOWN

FALSE TRUE UNKNOWN FALSE

NOT

NOT P1
TRUE FALSE

P1 UNKNOWN UNKNOWN
FALSE TRUE

Truth values of SQL Formulae

Formula Result
x=null UNKNOWN
null=null UNKNOWN
x IS NULL TRUE if x has a null value, FALSE otherwise
x IS NOT NULL TRUE if x does not have a null value, FALSE otherwise

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 40 / 47

Null 3VL

‘Correct’ SQL Queries Using null

Correct testing for NULL

SELECT no
FROM account
WHERE rate=NULL

SELECT no
FROM account
WHERE rate IS NULL

SELECT no
FROM account
WHERE rate=NULL
OR rate<>NULL

SELECT no
FROM account
WHERE rate IS NULL
OR rate IS NOT NULL

Testing for logical truth value

SELECT no
FROM account
WHERE (rate=5.50) IS NOT TRUE

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 41 / 47

Null 3VL

Quiz 10: SQL ’Might Be’

SELECT no
FROM account
WHERE (rate=5.25) IS NOT FALSE

account
no type cname rate sortcode

What is the result of the above SQL query?

no
100
101
103
107
125

no
100
103
107
119
125

no
100
103
107
125

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 42 / 47

Null 3VL

Worksheet: Null values in SQL

movement
mid no amount tdate
0999 119 45.00 null
1000 100 2300.00 5/1/1999
1001 101 4000.00 5/1/1999
1002 100 -223.45 8/1/1999
1004 107 -100.00 11/1/1999
1005 103 145.50 12/1/1999
1006 100 10.23 15/1/1999
1008 101 1230.00 15/1/1999
1009 119 5600.00 18/1/1999
1010 100 null 20/1/1999
1011 null null 20/1/1999
1012 null 600.00 20/1/1999
1013 null -46.00 20/1/1999

account
no type cname rate sortcode

100 ’current’ ’McBrien, P.’ null 67
101 ’deposit’ ’McBrien, P.’ 5.25 67
119 ’deposit’ ’Poulovassilis, A.’ 5.50 56
125 ’current’ ’Bailey, J.’ null 56

P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 43 / 47

Null 3VL

Quiz 11: SQL EXCEPT and NULL

SELECT r a t e
FROM account
WHERE no<105 EXCEPT SELECT r a t e FROM account WHERE so r t code =56 account no type cname rate sortcode 100 ’current’ ’McBrien, P.’ NULL 67 101 ’deposit’ ’McBrien, P.’ 5.25 67 103 ’current’ ’Boyd, M.’ NULL 34 107 ’current’ ’Poulovassilis, A.’ NULL 56 119 ’deposit’ ’Poulovassilis, A.’ 5.50 56 125 ’current’ ’Bailey, J.’ NULL 56 What is the result of the above SQL query? A rate B rate 5.25 C rate 5.25 null D rate 5.25 null null P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 44 / 47 Null 3VL Equivalences Between EXCEPT, NOT IN and NOT EXISTS R(A) and S(B), A and B are not nullable SELECT A FROM R EXCEPT SELECT B FROM S ≡ SELECT A FROM R WHERE NOT EXISTS (SELECT ∗ FROM S WHERE S .B=R .A) ≡ SELECT A FROM R WHERE A NOT IN (SELECT B FROM S) R(A) and S(B), A or B are nullable SELECT A FROM R EXCEPT SELECT B FROM S 6≡ SELECT A FROM R WHERE NOT EXISTS (SELECT ∗ FROM S WHERE S .B=R .A) 6≡ SELECT A FROM R WHERE A NOT IN (SELECT B FROM S) P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 45 / 47 Null 3VL Quiz 12: SQL EXCEPT and NOT IN SELECT r a t e FROM account WHERE no<105 AND r a t e NOT IN (SELECT r a t e FROM account WHERE so r t code =56) account no type cname rate sortcode 100 ’current’ ’McBrien, P.’ NULL 67 101 ’deposit’ ’McBrien, P.’ 5.25 67 103 ’current’ ’Boyd, M.’ NULL 34 107 ’current’ ’Poulovassilis, A.’ NULL 56 119 ’deposit’ ’Poulovassilis, A.’ 5.50 56 125 ’current’ ’Bailey, J.’ NULL 56 What is the result of the above SQL query? A rate B rate 5.25 C rate 5.25 null D rate 5.25 null null P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 46 / 47 Null 3VL Quiz 13: SQL EXCEPT and NOT EXISTS SELECT r a t e FROM account WHERE no<105 AND NOT EXISTS (SELECT ∗ FROM account AS account 56 WHERE so r t code =56 AND account 56 . r a t e=account . r a t e ) account no type cname rate sortcode 100 ’current’ ’McBrien, P.’ NULL 67 101 ’deposit’ ’McBrien, P.’ 5.25 67 103 ’current’ ’Boyd, M.’ NULL 34 107 ’current’ ’Poulovassilis, A.’ NULL 56 119 ’deposit’ ’Poulovassilis, A.’ 5.50 56 125 ’current’ ’Bailey, J.’ NULL 56 What is the result of the above SQL query? A rate B rate 5.25 C rate 5.25 null D rate 5.25 null null P.J. McBrien (Imperial College London) SQL: An Implementation of the Relational Algebra 47 / 47 SQL and RA RM to SQL DDL RA to SQL DML Joins Bags and Sets Bags or Sets Set Operations Null 3VL

Related Posts