SPATIAL DATABASES

AND DATABASE
DESIGN
 DATABASEQUERIES 142

LECTURE OUTLINE

• Query Definition

• Introduction to SQL

• SQL Syntax

• SQL in Action
 Image Source: GIS Geography

2
3. QUERYING A RELATIONAL

DATABASE
3. QUERYING A RELATIONAL DATABASE(1)
What is a Query?
 • A query is a question you ask of your database
• You can:
– Display data from multiple tables
– Control which fields display
– Perform calculations on field values
– Save a query automatically
3. QUERYING A RELATIONAL DATABASE(2)
What is SQL?
• SQL stands for Structured Query Language
• SQL is an ANSI (American National Standards Institute) standard
computer language for accessing and manipulating database
systems.
• SQL statements are used to retrieve and update data in a database.
 • SQL works with database programs like MS Access, DB2, Informix,
MS SQL Server, Oracle, Sybase, etc.

Note: Most of the SQL database programs also have their

own proprietary extensions in addition to the SQL standard
3. QUERYING A RELATIONAL DATABASE(3)
There are two basic SQL data sublanguages:
1. SQL Data Manipulation Language (DML)
• SQL is a syntax for executing queries
• also includes a syntax to update, insert, and delete records
• These query and update commands together form the Data
Manipulation Language (DML)
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database table
3. QUERYING A RELATIONAL DATABASE(4)

2. SQL Data Definition Language (DDL)

• The Data Definition Language (DDL) permits database tables to be
created or deleted
• also define indexes (keys),specify links between tables, and impose
constraints between database tables.
• The most important DDL statements in SQL are:
– CREATE TABLE - creates a new database table
– ALTER TABLE - alters(changes) a database table
– DROP TABLE - deletes a database table
 – CREATE INDEX - creates an index (search key)
– DROP INDEX - deletes an index
3. QUERYING A RELATIONAL DATABASE(5)

The format is:

SELECT attributes
FROM table
WHERE condition
• In which attributes is the list of attribute names and expressions
for which values are to be retrieved by the query
• table is a name of the relation, the so called input relation that
we want to query
• condition is a conditional (boolean) selection condition that
identifies which tuples are to be retrieved by the query
3. QUERYING A RELATIONAL DATABASE(6)

The SQL SELECT Statement

• The SELECT statement is used to select data from a
table. The tabular result is stored in a result table
(called the result-set).
Syntax
SELECT column_name(s)
FROM table_name
3. QUERYING A RELATIONAL DATABASE(7)

Select All Columns

• To select all columns from the “Ownership" table, use
 a * symbol instead of column names, like this:
Syntax
SELECT * FROM Ownership

3. QUERYING A RELATIONAL DATABASE(8)

Query
SELECT * FROM Ownership
Result

3. QUERYING A RELATIONAL DATABASE(9)

Semicolon after SQL Statements?

• Semicolon is the standard way to separate each SQL
statement in database systems that allow more than
 one SQL statement to be executed in the same call
to the server.
• For MS Access the semicolon after each SQL
statement are optional, but some database
programs force you to use it.
3. QUERYING A RELATIONAL DATABASE(10)

WHERE Clause
• The WHERE clause is used to specify a selection
criterion
• i.e. conditionally select data from a table, a WHERE
clause is added to the SELECT statement.
3. QUERYING A RELATIONAL DATABASE(11)
 Database

3. QUERYING A RELATIONAL

DATABASE(12) With the WHERE clause, the following

operators can be used

Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive
range
LIKE Search for a pattern
IN If you know the exact
value you want to return
for at least one of the
columns
3. QUERYING A RELATIONAL DATABASE(13)

Using the WHERE Clause

• To select only the property owners living in
 ‘Mabvuku’, we add a WHERE clause to the
SELECT statement:
SELECT *
FROM Parcel
WHERE Location=‘Mabvuku‘

Query1
3. QUERYING A RELATIONAL DATABASE(14)

Using the WHERE Clause

SELECT *
FROM Parcel
WHERE Location=‘Mabvuku’

• Note that we have used single quotes around the

conditional values in the examples.
• SQL uses single quotes around text values(most
database systems will also accept double quotes).
Numeric values should not be enclosed in quotes
3. QUERYING A RELATIONAL

DATABASE(15)
3. QUERYING A RELATIONAL DATABASE(16)
• Example SQL for an attribute projection

SELECT FirstName, LastName

FROM Persons
Attributes to be included

The relation from which the selection is made from

3. QUERYING A RELATIONAL
DATABASE(17)
3. QUERYING A RELATIONAL DATABASE(18)
3. QUERYING A RELATIONAL DATABASE(19)
3. QUERYING A RELATIONAL DATABASE(19)
3. QUERYING A RELATIONAL DATABASE(19)
3. QUERYING A RELATIONAL

DATABASE(19)
3. QUERYING A RELATIONAL
DATABASE(19)
 3. QUERYING A RELATIONAL
DATABASE(19)
Cartesian product
• Cartesian product of P and PP, written P x PP the
relation obtained from the
concatenating(‘glueing’) any tuple of P with any
tuple of PP
• the resulting tuple has all the attributes of P and all
those of PP
• Dangerous operation: if P has 2500 tuples and PP has
5000 tuples P x PP will have 12 500 000 tuples •
Queries with four or more input relations are not
uncommon
• With N input relations must typically have N-1 join
3. QUERYING A RELATIONAL

DATABASE(20)
3. QUERYING A RELATIONAL DATABASE(20)
The LIKE condition
• The LIKE condition is used to specify a search for a
pattern in a column.
• A “*" sign can be used to define wildcards (missing
letters in the pattern) both before and after the
pattern.
Syntax
SELECT column
FROM table
WHERE column LIKE pattern
3. QUERYING A RELATIONAL

DATABASE(20)
3. QUERYING A RELATIONAL DATABASE(20)
• The LIKE condition
• The following SQL statement will return persons with surname thatstart with an ‘R':
• SELECT *
• FROM Ownership
• WHERE surname LIKE ‘R*’

Query3
3. QUERYING A RELATIONAL DATABASE(20)
 • The INSERT INTO Statement
• The INSERT INTO statement is used to insert new rowsinto a table.
• Syntax
• INSERT INTOtable_name VALUES (value1, value2,....)
• You can also specify the columns for which you want to insert data: •
INSERT INTOtable_name (column1, column2,...)VALUES (value1, value2,....)

3. QUERYING A RELATIONAL DATABASE(20)

The INSERT INTO Statement

• INSERT INTO Ownership(TaxID, Surname, BirthDate)
VALUES('5', 'Sydney', 12/12/2011);
 Query6
3. QUERYING A RELATIONAL DATABASE(20)

The SELECT DISTINCT Statement

• The DISTINCT keyword is used to return only
distinct (different) values
• The SELECT statement returns information from
table columns. But what if we only want to select
 distinct elements?
• With SQL, all we need to do is to add a
DISTINCT keyword to the SELECT
statement:
3. QUERYING A RELATIONAL DATABASE(20)

The SELECT DISTINCT Statement

Syntax

SELECT DISTINCT column_name(s)

FROM table_name
3. QUERYING A RELATIONAL
 DATABASE(20) The SELECT DISTINCT

Statement

SELECT DISTINCT Location

FROM Parcel

Query8
3. QUERYING A RELATIONAL DATABASE(20)
The Update Statement
• The UPDATE statement is used to modify the data in
a table

Syntax

UPDATE table_nameSET column_name =

new_valueWHERE column_name = some_value
3. QUERYING A RELATIONAL

DATABASE(20) The Update Statement

UPDATE Parcel SETAreaSize= 500

WHERE PID= 33

UPDATE Parcel SET Location=

‘Marlborough', PID= 1000
WHEREAreaSize= 500
3. QUERYING A RELATIONAL

DATABASE(20) The SQL ORDER BY CLAUSE

The ORDER BY keyword is used to sort the result.

Sort the Rows

SELECT Location, PID FROM Parcel ORDER BY

 PID
3. QUERYING A RELATIONAL

DATABASE(20) SQL AND & OR

• AND and OR join two or more conditions in a

WHERE clause

• TheAND operator displays a record or records if

ALL conditions listed are true. The OR operator
displays a record ifANY of the conditions listed
are true
3. QUERYING A RELATIONAL DATABASE(20)
 SQL AND & OR
• AND and OR join two or more conditions in a
WHERE clause
• UseAND to display each person with the
surname equal to “Mazonde", and the TaxID
equal to 101

SELECT surname, TaxID

FROM Ownership
WHERE surname=‘Mazonde’ OR
TaxID = 50
3. QUERYING A RELATIONAL DATABASE(20)

SQL IN
• The IN operator may be used if you know the exact
value you want to return for at least one of the
columns

SELECT surname, TaxID

FROM Ownership
WHERE surname IN (‘Mazonde’, ’Rumeu’)
3. QUERYING A RELATIONAL

DATABASE(20) SQL BETWEEN ... AND

The BETWEEN ... AND operator selects a range of

data between two values.
These values can be numbers, text, or dates.
Syntax
SELECT column_name
FROM table_nameWHERE column_nameBETWEEN
value1 AND value2
3. QUERYING A RELATIONAL

DATABASE(20) SQL BETWEEN ... AND

SELECT *
FROM Ownership
WHERE surname BETWEEN ‘Mazonde'AND
‘Sydney‘;
3. SUMMARY QUERYING A
RELATIONAL
DATABASE(21)