Class News HW2 due on Monday email SQL scripts to Xueyuan CSPP - - PDF document

1

CSPP 53001: Databases

Svetlozar Nestorov

Class News

• HW2 due on Monday

– email SQL scripts to Xueyuan

• HW3 will be out this weekend
• Midterm in first half of class on November 5

(5:15pm - 6:45pm)

– open book/notes

Overview

• Multirelation SQL queries
• Subqueries
• Aggregation
• Schema declarations
• Modifications

Multirelation Queries

• List of relations in the FROM clause
• Relation-dot-attribute disambiguates

attributes with the same name from several relations.

• Example: find all

beers that Hopleaf regulars like. Frequents(drinker,bar) Likes(drinker,beer)

Formal Semantics

1. Start with the product of all relations in the FROM clause. 2. Apply σ (bag), using condition in WHERE clause. 3. Apply π (extended, bag) using attributes in the SELECT clause. The same as single-relation query with the addition of product of all relations.

Equivalent Operational Semantics

• Consider a tuple variable for each relation

in the FROM clause

• Imagine a series of nested loops over these

variables that produces every possible combination of tuples, one from each of the relations in the FROM clause.

• 1. For each tuple combination, check if it

satisfies the WHERE clause

• 2. If so, print the values of terms in the

SELECT clause

2

Explicit Tuple Variables

• Sometimes we need to refer to two or

more copies of the same relation.

• Use tuple variables as aliases of the

relations.

• Example: find pairs of beers liked by

the same drinker. Likes(drinker,beer)

Example Query

SELECT A.beer, B.beer FROM Likes A, Likes B WHERE A.drinker = B.drinker AND A.beer <> B.beer

More Examples

• Find all bars that sell two different

beers at the same price.

• Find all bars that sell three different

beers at the same price.

• Find all drinkers that frequent a bar

that serves their favorite beer.

Subqueries

• Result of a query can be used in the

WHERE clause of another query.

• The former is called a subquery.
• Simplest case: subquery returns a

single unary tuple (a single atomic value).

Example

• Find bars that serve Bud at the same price

Hopleaf charges for Stella. SELECT bar FROM Sells WHERE beer = “Bud” AND price = (SELECT price FROM Sells WHERE beer = “Stella” AND bar = “Hopleaf”)

Scoping Rules

• Attribute name refers to the most

closely nested relation with that attribute.

• Parenthesis around subquery are

essential.

3

Problems with Example Solution?

• What can go wrong?
• How do we fix it?

The IN Operator

• tuple IN relation
• Returns true if the tuple is in the relation.
• Find the names and manufactures of beers

that Janos likes. Likes(drinker,beer) Beers(name,manf)

The EXISTS Operator

• EXISTS(relation)
• Returns true if the relation contains at

least one tuple (nonempty)

• Find the beers that are the unique beer

by their manufacturer in the Beers relation.

Unique Beers

SELECT name FROM Beers B WHERE NOT EXISTS (SELECT * FROM Beers WHERE manf = B.manf AND name <> B.name)

Correlated Subquery

• Refer to outer Beers in the subquery by

giving it a tuple variable (alias) to distinguish from Beers in the subquery.

• A suqbquery that refers to values of a

surrounding query is called a correlated subquery.

• A correlated subquery must be evaluated (by

the database system) for every tuple in the

• uter query.

Quantifiers

• ANY is existential quantifier.

attribute condition ANY (subquery) At least one tuple in the relation satisfies the condition.

• ALL is universal quantifier.

attribute condition ALL (subquery) All tuples in the relation satisfy the condition.

4

Quantifier Example 1

• Find the beer(s) sold for the highest

price (among all bars).

Quantifier Example 2

• Find the beer(s) not sold for the lowest

price (among all bars).

Set Operators

• UNION, INTERSECT, EXCEPT
• (subquery) UNION (subquery)
• Only UNION is supported in MySQL

but you can write equivalent queries for the the other two set operators.

Intersect Example

• Find the drinkers and beers such that the

drinker likes the beer and frequents a bar that serves it. (SELECT * FROM Likes) INTERSECT (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar = Sells.bar)

Forcing Set or Bag Semantics

• Default for select-from-where queries

is bag; default for union is set.

– Bag is chosen for performance.

• Force set semantics (no duplicates)

with DISTINCT after SELECT.

• Force bag semantics with ALL after

UNION.

Distinct Example

• Find all different prices charged for

beers.

5

Aggregation

• Aggregate-function(attribute)
• SUM, AVG, MIN, MAX, COUNT
• COUNT(*) applies to tuples
• Use them in SELECT clause.

Average Price of Bud

• Find the average price of Bud.

SELECT AVG(price) FROM Sells WHERE beer = “Bud”

Eliminating Duplicates Before Aggregation

• Find the number of different prices at

which Bud is sold. SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = “Bud”

• DISTINCT can be used in any

aggregation but typically only makes sense with COUNT.

Grouping

• Aggregation applied to several subsets
• f your relation grouped together by

some condition.

– So far, the entire relation was aggregated.

• GROUP BY clause with a list of

attributes.

Grouping Example 1

Find the average price for each beer. SELECT beer, AVG(price) FROM Sells GROUP BY beer

Grouping Explanation

• The relation that is the result of the

FROM and WHERE clauses is grouped according to the values of the attributes of the GROUP BY clause and aggregations take place within each group.

• One result tuple is produced from each

group.

6

Grouping Example 2

• For each drinker, find the average

price of Bud at the bars they frequent.

Restrictions on SELECT Lists with Aggregation

• If aggregation with grouping is used,

then each element of the SELECT clause must be either:

• 1. Aggregated
• 2. Appear in the GROUP BY clause

Cheapest Place for Bud

• First attempt:

SELECT bar, MIN(price) FROM Sells WHERE beer = “Bud”

• Illegal query in SQL!
• How do we find the bar?

HAVING clause

• Selections on groups just as WHERE clauses

are selections on tuples.

• Conditions can use tuple variables or

relations in the FROM clause and their attributes.

– Tuple variables range only over the group. – Attributes must be grouping attributes or be aggregated.

HAVING example

• Find the average price of those beers that are

either served in at least 3 bars or manufactured by InBev. SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(*) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = “InBev”)

Another HAVING example

• For each manufacturer, find the beer

with the highest average price.

7

Defining a Database Schema

CREATE TABLE name (list-of-elements)

• Principal elements are attributes and their

types but key declarations and constraints also appear.

• Similar CREATE X commands for other

schema elements X: indexes, views, assertions, triggers.

• DROP TABLE name

DROP X name for schema elements X.

Create/Drop Example

DROP TABLE IF EXISTS Sells; CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL )

Data Types

• INT or INTEGER

– Varying precision

• REAL or FLOAT
• CHAR(n) fixed length character string.
• VARCHAR(n) variable length character

string with up to n characters

• TEXT, BLOB
• ENUM(val1,val2,…)

Date and Time Types

• Default date format is

DATE’yyyy-mm-dd’ but can be changed.

• Default time format is

TIME ‘hh:mm:ss[.ss…]’

• DATETIME
• TIMESTAMP

– In MySQL, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation except when the

• peration explicitly changed it.

Declaring Keys

• PRIMARY KEY or UNIQUE
• Only one primary key is allowed, but

there can be many uniques.

• SQL does not allow nulls in primary

key, but allows them in unique attributes (more than one null value is allowed).

Where to Declare Keys

• Two places to declare keys:
• 1. After an attribute’s type, if the

attribute is a key by itself.

• 2. As a separate element
• Essential for multi-attribute keys.

8

Key/Unique Example

DROP TABLE IF EXISTS Sells; CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL, invoice INT UNIQUE, PRIMARY KEY (bar, beer) )

Attribute Properties

• NOT NULL the attribute value cannot

be null.

• DEFAULT value, the value is used

whenever no other value of the attribute is known (without default null will be used).

Database Modifications

• Change the current relation instance!
• Results of modifications last beyond

your current session!

• Insert a new tuple
• Delete a current tuple
• Update a current tuple

– Change the value of one or more of its attributes

Insert

INSERT INTO relation VALUES(lits-of-values)

• Insert the tuple defined by the list of values,

associating values with attributes in the

• rder the attributes were declared.

– You can also list the attributes as arguments of the relation.

INSERT INTO Likes(beer,drinker) VALUES(“Bud”,”Jane”)

Insert the Result of a Query

INSERT INTO relation (subquery)

• Example: Find potential drinking buddies

for Jane by selecting all drinkers who frequent a bar that Jane frequents. CREATE TABLE PotBuddies ( name char(30) )

Potential Buddies

INSERT INTO PotBuddies (SELECT DISTINCT B.drinker FROM Frequents A, Frequents B WHERE A.drinker = “Jane” AND B.drinker <> “Jane” AND A.bar = B.bar)

9

Delete

DELETE FROM relation WHERE condition Deletes all tuples from the relation satisfying the condition. If no condition is given all tuples are deleted (usually DROP TABLE is better). DELETE FROM Likes WHERE drinker = “Jane” AND beer = “Bud”

Update

UPDATE relation SET list-of-assignments WHERE condition

• Example: make beer cheap!

UPDATE Sells SET price = 4 WHERE price > 4

Schema Modifications

• Avoid them!
• Never modify the schema of a live database!
• Better approach: recreate the table (or

database)

• Schema modification may result in:

– Disk fragmentation – Rebuilding indexes – Re-optimizing queries – Run time errors of existing SQL queries

Changing Columns (if you must)

ALTER TABLE relation ADD column-declaration

• Add an attribute to an existing relation

ALTER TABLE Bars ADD phone CHAR(16) DEFAULT “unlisted”

• Dropping columns (recipe for disaster)

ALTER TABLE Bars DROP phone