1
INTERSECT and EXCEPT: (may no be in MySQL)
(SELECT R.A, R.B
FROM R) INTERSECT
(SELECT S.A, S.B
FROM S)
SELECT R.A, R.B
FROM R WHERE EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B)
(SELECT R.A, R.B
FROM R) EXCEPT
(SELECT S.A, S.B
FROM S)
SELECT R.A, R.B
FROM R WHERE NOT EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B)
2
Null Values and Outerjoins
If x=Null then 4*(3-x)/7 is still NULL If x=Null then x=00oe00nbsp; is UNKNOWN In SQL there are three boolean values:FALSE = 0
UNKNOWN = 0.5
TRUE = 1
3
Null Values and Outerjoins
C1 AND C2 = min(C1, C2) C1 OR C2 = max(C1, C2) NOT C1 = 1 00C1Rule in SQL: include only tuples that yield TRUE
SELECT *
FROM Person
WHERE (age < 25) AND
(height > 6 OR weight > 190)
E.g. age=20 height=NULL weight=200
4
Null Values and Outerjoins
Unexpected behavior:
Some Persons are not included !
SELECT *
FROM Person
WHERE age < 25 OR age >= 25
5
Null Values and Outerjoins
Can test for NULL explicitly:
x IS NULL x IS NOT NULLNow it includes all Persons
SELECT *
FROM Person
WHERE age < 25 OR age >= 25 OR age IS NULL
6
Null Values and Outerjoins
Explicit joins in SQL:
Product(name, category)
Purchase(prodName, store)
Same as:
But Products that never sold will be lost !
SELECT Product.name, Purchase.store
FROM Product JOIN Purchase ON
Product.name = Purchase.prodName
SELECT Product.name, Purchase.store
FROM Product, Purchase
WHERE Product.name = Purchase.prodName
7
Null Values and Outerjoins
Left outer joins in SQL:
Product(name, category)
Purchase(prodName, store)
SELECT Product.name, Purchase.store
FROM Product LEFT OUTER JOIN Purchase ON
Product.name = Purchase.prodName
8
Photo
OneClick
Photo
Camera
gadget
Gizmo
Category
Name
Wiz
Camera
Ritz
Camera
Wiz
Gizmo
Store
ProdName
NULL
OneClick
Wiz
Camera
Ritz
Camera
Wiz
Gizmo
Store
Name
Product
Purchase
9
Outer Joins
Left outer join: Include the left tuple even if there00 no match Right outer join: Include the right tuple even if there00 no match Full outer join: Include the both left and right tuples even if there00 no match10
Modifying the Database
Three kinds of modifications
Insertions Deletions UpdatesSometimes they are all called 00pdates00/font>
11
Insertions
General form:
Missing attribute 00/font> NULL.
May drop attribute names if give them in order.
INSERT INTO R(A1,00, An) VALUES (v1,00, vn)
INSERT INTO Purchase(buyer, seller, product, store)
VALUES (00oe00 00red00 00akeup-clock-espresso-machine00 00he Sharper Image00
Example: Insert a new purchase to the database:
12
Insertions
INSERT INTO PRODUCT(name)
SELECT DISTINCT Purchase.product
FROM Purchase
WHERE Purchase.date > 000/26/0100/font>
The query replaces the VALUES keyword.
Here we insert many tuples into PRODUCT
13
Insertion: an Example
prodName is foreign key in Product.name
Suppose database got corrupted and we need to fix it:
gadgets
100
gizmo
category
listPrice
name
225
Smith
camera
80
Smith
gizmo
200
John
camera
price
buyerName
prodName
Task: insert in Product all prodNames from Purchase
Product
Product(name, listPrice, category)
Purchase(prodName, buyerName, price)
Purchase
14
Insertion: an Example
INSERT INTO Product(name)
SELECT DISTINCT prodName
FROM Purchase
WHERE prodName NOT IN (SELECT name FROM Product)
-
-
camera
Gadgets
100
gizmo
category
listPrice
name
15
Insertion: an Example
INSERT INTO Product(name, listPrice)
SELECT DISTINCT prodName, price
FROM Purchase
WHERE prodName NOT IN (SELECT name FROM Product)
-
225 ??
camera ??
-
200
camera
Gadgets
100
gizmo
category
listPrice
name
Depends on the implementation
16
Deletions
DELETE FROM PURCHASE
WHERE seller = 00oe00nbsp; AND
product = 00rooklyn Bridge00/font>
Factoid about SQL: there is no way to delete only a single
occurrence of a tuple that appears twice
in a relation.
Example:
17
Updates
UPDATE PRODUCT
SET price = price/2
WHERE Product.name IN
(SELECT product
FROM Purchase
WHERE Date =00ct, 25, 199900;
Example:
18
Data Definition in SQL
So far we have see the Data Manipulation Language, DML
Next: Data Definition Language (DDL)
Data types:
Defines the types.
Data definition: defining the schema.
Create tables Delete tables Modify table schemaIndexes: to improve performance
19
Data Types in SQL
Characters: CHAR(20) -- fixed length VARCHAR(40) -- variable length Numbers: INT, REAL plus variations Times and dates: DATE, DATETIME (SQL Server only) To reuse domains: CREATE DOMAIN address AS VARCHAR(55)20
Creating Tables
CREATE TABLE Person(
name VARCHAR(30),
social-security-number INT,
age SHORTINT,
city VARCHAR(30),
gender BIT(1),
Birthdate DATE
);
Example:
21
Deleting or Modifying a Table
Deleting:
ALTER TABLE Person
ADD phone CHAR(16);
ALTER TABLE Person
DROP age;
Altering: (adding or removing an attribute).
What happens when you make changes to the schema?
Example:
DROP Person;
Example:
Exercise with care !!
22
Default Values
Specifying default values:
CREATE TABLE Person(
name VARCHAR(30),
social-security-number INT,
age SHORTINT DEFAULT 100,
city VARCHAR(30) DEFAULT 00eattle00
gender CHAR(1) DEFAULT 0000
Birthdate DATE
The default of defaults: NULL
23
Indexes
REALLY important to speed up query processing time.
Suppose we have a relation
Person (name, age, city)
Sequential scan of the file Person may take long
SELECT *
FROM Person
WHERE name = 00mith00/font>
24
Create an index on name: B+ trees have fan-out of 100s: max 4 levels !Indexes
Smith
00
00
Charles
Betty
Adam
25
Creating Indexes
CREATE INDEX nameIndex ON Person(name)
Syntax:
26
Creating Indexes
Indexes can be created on more than one attribute:
CREATE INDEX doubleindex ON Person (age, city)
SELECT * FROM Person WHERE age = 55 AND city = 00eattle00/font>
SELECT * FROM Person WHERE city = 00eattle00/font>
Helps in:
But not in:
Example:
27
Creating Indexes
Indexes can be useful in range queries too:
B+ trees help in:
Why not create indexes on everything?
CREATE INDEX ageIndex ON Person (age)
SELECT * FROM Person WHERE age > 25 AND age < 28
28
Defining Views
Views are relations, except that they are not physically stored.
For presenting different information to different users
Employee(ssn, name, department, project, salary)
Payroll has access to Employee, others only to Developers
CREATE VIEW Developers AS
SELECT name, project
FROM Employee
WHERE department = 00evelopment00/font>
29
A Different View
Person(name, city)
Purchase(buyer, seller, product, store)
Product(name, maker, category)
We have a new virtual table:
Seattle-view(buyer, seller, product, store)
CREATE VIEW Seattle-view AS
SELECT buyer, seller, product, store
FROM Person, Purchase
WHERE Person.city = 00eattle00nbsp; AND
Person.name = Purchase.buyer
30
A Different View
SELECT name, store
FROM Seattle-view, Product
WHERE Seattle-view.product = Product.name AND
Product.category = 00hoes00/font>
We can later use the view:
31
What Happens When We Query a View ?
SELECT name, Seattle-view.store
FROM Seattle-view, Product
WHERE Seattle-view.product = Product.name AND
Product.category = 00hoes00/font>
SELECT name, Purchase.store
FROM Person, Purchase, Product
WHERE Person.city = 00eattle00nbsp; AND
Person.name = Purchase.buyer AND
Purchase.poduct = Product.name AND
Product.category = 00hoes00/font>
32
Types of Views
Virtual views: Used in databases Computed only on-demand 00slow at runtime Always up to date Materialized views Used in data warehouses Precomputed offline 00fast at runtime May have stale data33
Updating Views
How can I insert a tuple into a table that doesn00 exist?
Employee(ssn, name, department, project, salary)
CREATE VIEW Developers AS
SELECT name, project
FROM Employee
WHERE department = 00evelopment00/font>
INSERT INTO Developers VALUES(00oe00 00ptimizer00
INSERT INTO Employee VALUES(NULL, 00oe00 NULL, 00ptimizer00 NULL)
If we make the
following insertion:
It becomes:
34
Non-Updatable Views
CREATE VIEW Seattle-view AS
SELECT seller, product, store
FROM Person, Purchase
WHERE Person.city = 00eattle00nbsp; AND
Person.name = Purchase.buyer
How can we add the following tuple to the view?
(00oe00 00hoe Model 1234500 00ine West00
We need to add 00oe00to Person first, but we don00 have all its attributes
