GeoDatabases: lecture 5 Data Manipulation in SQL

1
Geo-Databases lecture 5Data Manipulation 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
Data manipulation in SQL
3
Motivation

• There are various reasons for manipulating a
  database system.
• The stored data is to be changed.
• In this lecture insertion, deletion, and
  updating of datasets
• The data structure (the schema) is to be changed
• In this lecture creation, dropping, and altering
  tables
• The performance is to be changed (tuning).
  Install an index, etc.
• Not in this lecture.

The first two types of manipulation can be
formulated using Standard-SQL.
4
Insertion of Datasets
SQL-command INSERT given A table containing
information about students scenario A new
student has to be added to the database
5
Set-based Insertion

• Datasets from other tables can be imported
  using a subquery.
• Task
• All students with approved enrolment are to be
  inserted into the table
• Studenten.
• INSERT INTO Studenten
• SELECT Nr,Name,Ort FROM ImmatrApplications WHERE
  Statusapproved
• Important
• Changes to the database are applied in 2 steps
• create the set of candidates (temporary)
• make changes visible (all at the same time)
• Thus no dependencies between the table to
  be changed and the subquery!

6
Default Values
Problem What happens if we omit certain values
in an INSERT comand?
7
Updating Datasets (1)

• SQL-command UPDATE
• Scenario
• The dataset of student number 5 is to be updated,
  because he has moved from Köln (Cologne) to
  Hannover.

8
Updating Datasets (2)

• The WHERE clause is used to specify the set of
  tuples to be changed. This way multiple datasets
  can be changed simultaneously.
• scenario Lecturer Meier (PersNr 2) is leaving
  the university. Lecturer Schmitz (PersNr 3) takes
  over his lectures.

• without where clause all datasets are changed
• multiple simultaneous assignments are possible
• SET NameWeber, OrtBerlin,
  StrasseHauptstr.

9
Updating Datasets (3)
The UPDATE operation can refer to old values.
Example Increase the examination score of
student Schneider (Nr5) by 10! UPDATE
Klausurergebnisse SET Punkte 1.1Punkte WHERE
Nr 5
10
Deleting Datasets

• SQL-command DELETE
• All tuples from a table T can be deleted using
  DELETE FROM T.
• Scenarios
• Student number 1 is leaving the university.
• All students from Bonn are leaving the university.

11
Schema Manipulation (1)

• SQL-commands DROP TABLE, CREATE TABLE, ALTER
  TABLE
• Tables may be created any time with CREATE TABLE
• Tables may be removed any time with DROP TABLE
• on the distinction of DROP TABLE T and DELETE
  FROM T

12
Schema Manipulation (2)

• The structure of tables can be altered any time.
• adding a column
• ALTER TABLE Vorlesungen
• ADD COLUMN Wochenstunden integer
• deleting a column
• ALTER TABLE Vorlesungen
• DROP COLUMN Wochenstunden
• Integrity constraints (see lecture on data
  integrity) can also be added and removed at any
  time.

13
References

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