Business Application systems SQL

Business Application Systems

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