Business Application Systems SQL
Structured Query Language –
SQL
Prof. Dr. Peter Wolf
Business Application Systems Prof. Dr. Peter WolfChart 2
Query language SQL
Query language SQL
To implement a relational model in a certain database system a database language is needed.
The mostly used standard for RDBMS (relational database management systems) is SQL (Structured Query Language).
Problem
More than one exisiting standard versions : SQL86 / SQL89 / SQL92 / SQL99
complexity: the standard SQL99 contains more than 1000 pages manual; no existing RDBMS achieves SQL99.
Some practical used instructions are not standardised
=> compatibillity between exisiting RDBMS is only guaranteed by the use of SQL92
Business Application Systems Prof. Dr. Peter WolfChart 3
Query language SQL
Database languages are differentiated in :
DDL – Data Definition Language
… to implement a relational model in a certain database system. It contains instructions to create, change the definition or delete tables.
examples: CREATE, ALTER, DROP
DML – Data Manipulation Language
… for queries and reports and to update, insert or delete data entries of a database
examples : SELECT, INSERT, UPDATE, DELETE
DCL – Data Control Language
… for the administration of the database e.g. to define access rights, to lock tables etc.
examples : GRANT, REVOKE, COMMIT, ROLLBACK, LOCK
Business Application Systems Prof. Dr. Peter WolfChart 4
example:
CREATE TABLE Vorlesung ( VorlesungNr integer UNIQUE — primary key FächergruppenNr integer — association to Fächergruppe Titel varchar (60) NOT NULL — titel of lecture Kurztitel char(20) — shorttitel CONSTRAINT PRIMARY KEY (VorlesungNr)
);
column constraint : UNIQUE = values can not exist more than one time NOT NULL = value NULL not allowed
table constraint : PRIMARY KEY defines the attribute as primary key attribute
relation name
attribute
data type (domain)
column constraint
commentar
table constraint
Create a table
Query language SQL
Business Application Systems Prof. Dr. Peter WolfChart 5
Query language SQL
SELECT – syntax and clauses
SELECT – schema
SELECT * or table.* or [table.]attribute1[AS alias1] [, … ] FROM table [, …]
[ WHERE … ] [ GROUP BY [table.] attribute1, [, … ] ] [ HAVING … ] [ ORDER BY [table.] attribute2, [, … ] ]
* all attributes of the table table name of the selected table attribute1, attribute2 name of attributes of the table alias1 text string which appears in the result instead of the attribute
name
[…] items in these brackets can be used optional
Business Application Systems Prof. Dr. Peter WolfChart 6
Query language SQL
Queries on a database are formulated with the SELECT – instruction :
selection : selects certain rows of tables which achieve the condition
projektion : selects certain columns of tables
SELECT * FROM employees WHERE name = „Huber“
SELECT name, firstname FROM employees
name first name date of birth
Milke Lise 3.6.1934
Huber Karl 16.12.1964
Trunstein Helga 30.7.1956
name first name date of birth
Kelz Andreas 21.7.1965
Huber Karl 16.12.1964
Ernsbach Elli 29.6.1956
name first name date of birth
Huber Karl 16.12.1964
name first name
Kelz Andreas
Huber Karl
Ernsbach Elli
employees
Business Application Systems Prof. Dr. Peter WolfChart 7
3. query language SQL
Queries on a database are formulated with the SELECT – instruction :
join : combines two tables which have the same structure (number and type of
columns) SELECT * FROM employees UNION SELECT * FROM customers
name first name date of birth
Milke Lise 3.6.1934
Huber Karl 16.12.1964
Trunstein Helga 30.7.1956
name first name date of birth
Kelz Andreas 21.7.1965
Huber Karl 16.12.1964
Ernsbach Elli 29.6.1956
name first name date of birth
Milke Lise 3.6.1934
Huber Karl 16.12.1964
Trunstein Helga 30.7.1956
Kelz Andreas 21.7.65
Ernsbach Elli 29.6.1956
employees customers
Business Application Systems Prof. Dr. Peter WolfChart 8
Operators for comparison of the WHERE – clause
= , <>, <, > <=, >=
BETWEEN min AND max
NOT, IS NOT
AND, OR
LIKE mit „%“ for any characters any times (in ACCESS „*“) mit „-“ for any character on time (in ACCESS „?“)
mit „#“ for any number
IN (attribute value_1, … )
Additional operators outside SQL – standard
IS NULL, TOP number, TOP number PERCENT,
SELECT * FROM employees WHERE name = „Huber“
Query language SQL
Business Application Systems Prof. Dr. Peter WolfChart 9
Aggregate functions
aggregate functions are used to calculate one value as a result of many values of many data records with the same conditions
Average: SELECT AVG(Leistung.Fachnote) … FROM …
Sum: SELECT SUM(Artikel.Lagerbestand) … FROM …
Minimum: SELECT MIN(Leistung.Fachnote) … FROM …
Maximum: SELECT MAX(Leistung.Fachnote) … FROM …
Count: SELECT COUNT(Student.MatrNr) … FROM …
The use of an aggregate function normally leads to the use of the GROUP BY – clause.
Query language SQL
Business Application Systems Prof. Dr. Peter WolfChart 10
GROUP BY – clause
With the GROUP BY – clause you can merge data records which have the same value in one or more attributes to a group. Therefore you have to use an aggregate function in the SELECT :
SELECT Artikel.Lagerbereich, SUM(Artikel.Bestand) FROM Lager GROUP BY Artikel.Lagerbereich
The GROUP BY – clause definies the data records for the group. The HAVING – clause allows a conditional selection of the resulting data records after the building of the group.
SELECT Artikel.Lagerbereich, SUM(Artikel.Bestand) FROM Lager GROUP BY Artikel.Lagerbereich HAVING SUM(Artikel.Bestand) > 1000
Query language SQL
Business Application Systems Prof. Dr. Peter WolfChart 11
ORDER BY – clause
The result of a query can be sorted with the ORDER BY – clause.
SELECT Artikel.Lagerbereich, SUM(Artikel.Bestand) FROM Lager GROUP BY Artikel.Lagerbereich ORDER BY SUM(Artikel.Bestand) DESC
ASC means ASCENDING
DESC means DESCENDING
Query language SQL
