Application Systems
Structured Query Language –
SQL
Prof. Dr. Peter Wolf
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
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
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
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
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
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
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
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
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
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
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more