3. Relational Algebra and SQL

1 / 15

# 3. Relational Algebra and SQL - PowerPoint PPT Presentation

3. Relational Algebra and SQL. Example: Let the following relations describe point sets: A(x, y), B(x, y), J(x, y) 2D points in the plane H(x, y, z), I(x, y, z) 3D points in space F(z) line on the z-axis

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about '3. Relational Algebra and SQL' - columbia

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
3. Relational Algebra and SQL

Example:

Let the following relations describe point sets:

• A(x, y), B(x, y), J(x, y) 2D points in the plane
• H(x, y, z), I(x, y, z) 3D points in space
• F(z) line on the z-axis
• K(y,z) 2D points in (y, z) plane
3.1 Relation algebra operators

A and B

A  B

A  B

A \ B

 y,z H

 x,y H

 2x+z = 0 I

Example:

Find the SSN and tax for each person.

πSSN,Taxσwages+interest+capital_gain = income Taxrecord × Taxtable

Example:

Find the area of Lincoln reached by a radio station.

( πX,Y ( σName=“Lincoln” Town ) )  ( πX,Y Broadcast )

3.2 SQL

SELECT a1, ..., an

FROM R1, R2, …, Rm

WHERE Con1, …,Conk

This means:

πa1, ..., an( σ Con1( … (σ Conk ( R1× R2 × … ×Rm))…))

Example:

Find the SSN and tax for each person.

SELECT SSN, Tax

FROM Taxrecord, Taxtable

WHERE wages + interest + capital_gain = income

AS – keyword used to rename relations

Two SQL expressions can be combined by:

INTERSECT

UNION

MINUS– set difference

Example:

Find the names of the streets that intersect.

SELECT S.NAME, T.NAME

FROM Streets AS S, Streets AS T

WHERE S.X = T.X and

S.Y = T.Y

Example: Assume we have the relations:

Town ( Name, X, Y )

Find the parts of Lincoln, NE that can

be reached by at least one Radio station.

(SELECT X, Y

FROM Town

WHERE Name = “Lincoln”)

INTERSECT

(SELECT X, Y

Another way of connecting SQL expressions

is using the IN keyword.

SELECT ……..

FROM ……..

WHERE a IN ( SELECT b

FROM …..

WHERE ….. )

SQL with aggregation –

SELECT aggregate_function

FROM …….

WHERE ……

aggregate_function–

Max (c1a1 + ……..+ cnan) where ai are attributes

Min (c1a1 + ……..+ cnan) and ci are constants

Sum(a) where a is an attribute that is

Avg(a) constant in each constraint tuple

Count(a)

Example:

Package(Serial_No, From, Destination, Weight)

Postage (Weight , Fee)

Find the total postage of all packages sent

from Omaha.

SELECT Sum(Fee)

FROM Package, Postage

WHERE Package.Weight = Postage.Weight AND

Package.From = “ Omaha “

GROUP BY–

SELECT a1, …, an, aggregate_function

FROM …..

WHERE ……

GROUP BY a1, ..., ak

• Evaluates basic SQL query
• Groups the tuples according to different values of a1,..,ak
• Applies the aggregate function to each group separately
• {a1, …, ak}  {a1, …, an}
Example:

Find the total postage sent out from each city.

SELECT Package.From, Sum(Postage.Fee)

FROM Package, Postage

WHERE Package.Weight = Postage.Weight

GROUP BY Package.From