Operations on Tables
A database is a collection of tables
Database Operations
Operations on tables produce tables
The questions we ask of a database are answered with a
whole table
Users specify what they want to know and the
Chapter 16 database software finds it
Operations specified using SQL (Structured Query
Language)
SQL is a language for querying and modifying data and
managing databases
Example Database Select
Takes rows from one table to create a new table
Specify the table from which rows are to be taken,
and the test for selection
Test is applied to each row of the table to determine
if it should be included in result table
Test uses attribute names, constants, and relational
operators
If the test is true for a given row, the row is included
in the result table
3 4
Select Select
Syntax: SELECT *
SELECT * FROM Nations
FROM <table> WHERE Interest = 'Beach'
WHERE <test>
The asterisk (*) means "anything"
Example:
SELECT *
FROM Nations
WHERE Interest = 'Beach'
5 6
Project Project
Builds a new table from the columns of an Syntax:
existing table
SELECT <field list>
Specify name of existing table and the FROM <table>
columns (field names) to be included in the
new table
The new table will have the same number of Example:
rows as the original table, unless … SELECT Name, Domain, Interest
… the new table eliminates a key field. Duplicate FROM Nations
rows in the new table are eliminated.
7 8
Project Select And Project
SELECT Name, Domain, Interest Can use Select and Project operations
FROM Nations together to "trim" base tables to keep only
some of the rows and some of the columns
Example:
SELECT Name, Domain, Latitude
FROM Nations
WHERE Latitude >= 60 AND NS = 'N'
9 10
Select And Project Results Exercise
SELECT Name, Domain, Latitude
FROM Nations What is the capital of countries whose
WHERE Latitude >= 60 AND NS = 'N' "interest" is "history" or "beach"?
Solution:
SELECT Capital
FROM Nations
WHERE Interest = 'History'
OR Interest = 'Beach'
11 12
Union Union Results
SELECT *
Combines two tables (that have the same set FROM Nations
of attributes) WHERE Lat >= 60 AND NS = 'N'
UNION
SELECT *
FROM Nations
Syntax: WHERE Lat >= 45 AND NS = 'S'
<table1>
UNION
<table2>
13 14
Product Another Table
Creates a super table with all fields from both
tables
Puts the rows together
Each row of Table 2 is appended to each row of
Table 1
General syntax:
SELECT *
FROM <table1>, <table2>
15 16
Product Results Join
SELECT * Combines two tables, like the Product Operation,
FROM Nations, Travelers but doesn't necessarily produce all pairings
Join operation:
Table1 Table2 On Match
Match is a comparison test involving fields from
each table ([Link])
A match for a row from each table produces a result
row that is their concatenation
17 18
Join Join Applied
General syntax: Northern
SELECT *
FROM <table1> INNER JOIN <table2>
ON <table1>.<field> = <table2>.<field>
Master
Can be written with product operation:
SELECT *
FROM <table1>, <table2>
WHERE <table1>.<field> = <table2>.<field>
19 20
Join Applied Exercise
For each row in one table, locate a row (or Suppose you have the
rows) in the other table with the same value following tables:
performers, events, and
in the common field
venues
If found, combine the two.
If not found, look up the next row.
Write a query to find
what dates the
Possible to join using any relational operator, Paramount is booked.
not just = (equality) to compare fields
21 22
Solution Exercise
SELECT PerformanceDate Write a query to find
FROM events INNER JOIN venues
ON [Link] = [Link] which performers are
WHERE Venue = 'Paramount Theater' playing at the
Paramount and when.
23 24
Solution
SELECT Performer, PerformanceDate
FROM (events INNER JOIN venues
ON [Link] = [Link])
INNER JOIN performers
ON [Link] = [Link]
WHERE Venue = 'Paramount Theater'
25