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)
