GeoDatabases: lecture 3 Simple Queries in SQL

1
Geo-Databases lecture 3Simple Queries in SQL

• Prof. Dr. Thomas H. Kolbe
• Institute for Geodesy and Geoinformation Science
• Technische Universität Berlin

Credits This material is mostly an english
translation of the course module no. 8
(Geo-Datenbanksysteme) of the open e-content
platform www.geoinformation.net.
2
Simple queries in SQL
3
Motivation

• The user wants to describe which information is
  to be extracted from a table. Example queries
• What are the names and cities of all students?
• (select certain columns of a table)
• Which students live in Berlin?
• (select certain rows of a table)
• How many students live in Potsdam?
• (combine information of a table)

These queries refer to a single table
4
Navigating vs. Set-oriented

• The first, pre-relational DBMS offered simple
  navigating access. Database operations were
  dataset-oriented, that means in every operation
  a single dataset is processed.
• Example
• FIND FIRST Student find first entry in the
  student data sets
• While not end-of-file do
• begin
• GET Student S_Name copy name of the current
  set into S_Name
• operate on S_Name
• FIND NEXT Student go to next entry
• end
• In contrast, SQL databases operate
  (tuple-)set-oriented. An SQL operation is
  declarative and normally processes sets of
  datasets.

5
Projection

• The cutback of a table in vertical direction is
  called projection.
• Using this operator may produce duplicates!

6
Selection (1)
The cutback of a table in horizontal direction is
called selection. The special character
(wildcard) can be used as a synonym for all
columns. The WHERE clause of a SELECT
command is used to specify the selection
condition.
7
Selection (2)

• The selection condition consists of atomic
  conditions linked by the (logical) Boolean
  operators AND, OR, NOT.
• Example We want to know all students from Bonn
  or Berlin.
• Comparison operators in atomic conditions , ltgt,
  lt, lt, gt, gt

8
Selection (3)

• In case of character strings SQL allows for
  fuzzy queries.
• (comparison operator LIKE)
• Question
• Which students live in a city that starts with
  B?

9
Aggregate functions
Aggregate functions perform operations on tuple
sets. Question How many students are from
Cologne?
Other important aggregate functions are SUM
compute sum Attention in case of NULL values!
AVG compute average value MIN / MAX find
minimum / maximum value
10
Grouping

• The group by function is suited to enhance the
  applicability of the aggregate functions.
• Question
• How many students live in each city?

11
Grouping (2)

• The GROUP BY operator can be used to group the
  tuples of a table on the basis of the occuring
  values wrt. selected columns.
• Example
• How many students from the same city do the same
  course?
• SELECT Ort, Fach, COUNT(Fach) AS Anz
• FROM Studenten
• GROUP BY Ort, Fach
• In case a GROUP BY command is used, the
  SELECT-list is subject to the following
  restrictions
• It may only comprise attributes that are
  contained in the GROUP BY-list, and
• It may refer to other attributes solely via
  aggregate functions.

12
Elimination of Duplicates

• The Theory of the relational query languages does
  not allow for duplicates (relation as a
  mathematical set).
• In SQL
• No automatic duplicate elimination
• Forced duplicate elimination with the key word
  DISTINCT
• Example Overview of all cities that students
  live in.

13
Sorting

• In the theory of the relational query languages,
  the tuples of a relation do not have a specified
  order.
• In SQL
• Sorting of the data sets during output can be
  forced using the ORDER BY clause.
• Example List of all students, sorted by
  residence and name

14
References

• Jim Melton, Alan R. Simon, SQL 1999
  Understanding Relational Language Components,
  Morgan Kaufmann Publishers, 2001