SQL 2

DefinitionvonViews:Syntax
ViewDefinition =
CREATE VIEW ViewName [ "(" ColumnName { "," ColumnName } ")" ]
AS Query
EineSichtbzw.eineViewisteineTabelle,diedemResultateinerAnfrage(Query),
alsodemResultateinerSELECT Anweisungentspricht.
EinesolcheTabellekannwieeine"gewöhnliche"(gespeicherte)Tabelleverwendetwerden,
wobeifürDatenbank-Modifikationsoperationen(INSERT,UPDATE,DELETE,siehefolgende
Seiten)gewisseEinschränkungengelten.
DasKonzepteinerViewistvergleichbarmitdemKonzepteinerFunktionineiner
prozeduralenProgrammiersprache.
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
59
DefinitionvonViews:Beispiele
Annahme:TabelleCustomers besitzenurfolgendeSpalten:CusNo,CusName,CusCity
undDiscount (nichtaberBalanceDue,diesichausanderenDatenberechnenlässt)
CREATE VIEW CusBalances(CusNo, CusName, CusCity, Discount, BalanceDue)
AS SELECT CusNo, CusName, CusCity, Discount, SUM(Amount)
FROM
Customers
JOIN Orders ON Customers.CusNo = Orders.CusNo
WHERE Orders.Status = '2 shipped'
GROUP BY CusNo, CusName, CusCity, Discount
CREATE VIEW OrderDetails(
OrdNo, OrderDate, CusNo, CusName, Discount,
PrdNo, PrdDescr, Qty, Amount, Status
) AS SELECT o.OrdNo, o.OrderDate,
c.CusNo, c.CusName, c.Discount,
p.PrdNo, p.PrdDescr, p.Qty,
o.Amount, o.Status
FROM
Orders o
JOIN Customers c ON c.CusNo = o.CusNo
JOIN Products p ON p.PrdNo = o.PrdNo
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
summiereAmount der
BestellungenmitStatus =
'2 shipped' fürjeden
Kunden
Bestellungeninkl.
Kundennamen,ProduktBeschreibungenetc.
60
VerwendungvonViews:Beispiele
• Anfrage
SELECT
FROM
WHERE
ORDER
OrderDate, OrdNo, CusName, PrdDescr
OrderDetails
PrdDescr = 'Disk Drive'
BY OrderDate, OrdNo
• View-Definition
CREATE VIEW LargeOrderDetails AS
SELECT * FROM OrderDetails WHERE Amount > 10000.0
• Modifikation
(inSQL:1999theoretischmöglich,abervonvielenProduktennichtunterstützt)
UPDATE OrderDetails SET ProdDescr = 'Apple Magic Mouse'
WHERE PrdNo = 901
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
61
Anfrage aufView:SubstitutionViewName→Query,dieViewdefiniert
SELECT OrderDate, OrdNo, CusName, PrdDescr
FROM
OrderDetails
WHERE PrdDescr = 'Disk Drive'
SubstitutiondesView-NamensdurchseineDefinition:
SELECT OrderDate, OrdNo, CusName, PrdDescr
FROM (
SELECT OrdNo, OrderDate, c.CusNo, CusName, Discount,
p.PrdNo, PrdDescr, p.Qty, o.Amount, o.Status
FROM
Orders o
JOIN Customers c ON c.CusNo = o.CusNo
JOIN Products p ON p.PrdNo = o.PrdNo
)
WHERE PrdDescr = 'Disk Drive'
logischeVereinfachung:
SELECT OrderDate, OrdNo, c.CusName, p.PrdDescr
FROM
Orders o
JOIN Customers c ON c.CusNo = o.CusNo
JOIN Products p ON p.PrdNo = o.PrdNo
WHERE PrdDescr = 'Disk Drive'
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
62
ViewsalsTeilkomplexerAnfragen
Finde dieStädte,deren Kunden denhöchsten durchschnittlichen Saldo aufweisen(d.h.Städte,deren
Kunden unsimDurchschnittammeistenschulden),zusammenmitdiesemdurchschnittlichenSaldo
1)
Definiere Viewfür durchschnittlichen SaldoproStadt:
CREATE VIEW AvgBalDuePerCity(CusCity, AvgBalDue) AS
SELECT CusCity, AVG(BalanceDue)
FROM
Customers
GROUP BY CusCity
2)
Formuliere ursprüngliche Anfrage aufdiesem View:
SELECT CusCity, AvgBalDue
FROM
AvgBalDuePerCity
WHERE AvgBalDue = ( SELECT MAX(AvgBalDue) FROM AvgBalDuePerCity )
NB:
Diese Anfrage kann inSystemen,welche inder FROM-Klausel keine Subquery zulassen,nur mit Hilfe
einersolchenViewbeantwortet werden.
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
63
ViewUpdates(1)
Bei verschiedenen Viewsist es nicht möglich,eine Modifikation (INSERT,UPDATE,DELETE)eindeutig auf
diedarunter liegenden Relationen abzubilden.
Beispiel:
Wie würde eine Änderung der BalanceDueinderViewCusBalances (vgl.FrühereFolie) aufdiebeteiligten
Relationen abgebildet?
–
durch VerändernvonAmounts?Vonwelchen?
–
durch Einfügen,Löschen oder “Umhängen”vonBestellungen?Vonwelchen?
–
durch Verändern desStatus-AttributesvonBestellungen?Vonwelchen?
InSQL-92ist eine Viewmodifzierbar (updatable),fallsdieSubquery der View-Spezifikation
1. inder FROM-Klausel nur eine Tabelle enthält
2. keine GROUP BY- undHAVING-Klauseln enthält
3. inder SELECT-Klausel DISTINCT nicht spezifiziert wurde
4. dieWHERE-Klausel keine korrelierte Subquery enthält
5. alle Ausdrücke inder SELECT-Klausel Attributnamen sind (keine komplexen Ausdrücke)
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
64
ViewUpdates(2)
BeimodifizierbarenViewsistesprinzipiellmöglich,dasseineviaINSERT eingefügteZeile
nachderAusführungderAnweisungzwarinderDatenbankist,abertrotzdemnichtinder
betreffendenViewsichtbarsind.
Beispiel:
CREATE VIEW GoodCustomers(CusNo, CusName, CusCity, Discount)
AS SELECT CusNo, CusName, CusCity, Discount
FROM Customers WHERE Discount > 0.2
INSERT INTO GoodCustomers(CusNo, CusName, CusCity, Discount)
VALUES (100, 'Meier', 'Zürich', 0.1)
WennamEndederView-DefinitionWITH CHECK OPTION spezifiziertwird,
dannwirdeinsolcherUpdatezurückgewiesen.
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
65
EinfügenvonZeilen:SyntaxundSemantik
InsertStatement =
INSERT INTO TableName [ "(" ColumnName { "," ColumnName } ")" ]
( VALUES "(" Literal { "," Literal } ")" ) | Subquery .
DieINSERT Anweisungfügteine (allenfalls mehrere)Zeile(n)ineineTabelleein,
sofernalleaufderTabelledefiniertenIntegritätsbedigungenerfülltsind,
insbesondere:
–
diePrimärschlüsselbedingung(PRIMARY KEY constraint)
–
weitereEindeutigkeitsbedingungen(UNIQUE constraints)
–
NOT NULL undCHECK constraints
[Bemerkung:x DataType NOT NULL ≣ x DataType CHECK ( x IS NOT NULL ) ]
–
R.Marti
Fremdschlüsselbedingungen(FOREIGN KEY constraints):
Fürjeden(NOT NULL)WerteinesFremdschlüsselsmussinderreferenziertenTabelle
eineZeilemitdementsprechendenPrimärschlüsselexistieren
InformationssystemefürIngenieure2016 – 3SQL
66
EinfügenvonZeilen:Beispiele
• Annahme:DieAttributevonCustomers wurdeninfolgenderReihenfolgedefiniert:
(mitDEFAULT Wertenvon0fürBalanceDue undDiscount)
• FügeeinenKundenmitNr.7namensImhof ausLuzernhinzu
INSERT INTO Customers VALUES (7, 'Imhof', 'Luzern', 0, 0)
oder: INSERT INTO Customers(CusNo, CusName, CusCity)
VALUES (7, 'Imhof', 'Luzern')
oder: INSERT INTO Customers(CusCity, CusNo, CusName)
VALUES ('Luzern', 7, 'Imhof')
•
äquivalent zum ersten
INSERT-Statement falls
BalanceDue und Discount
mit DEFAULT 0 deklariert
FügeausgelieferteBestellungenseit1.09.2013 zurTabelleRemindershinzu
INSERT INTO Reminders
SELECT *
FROM
Orders
WHERE ShipDate >= DATE '2013-01-01'
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
Annahme:
Reminders hat gleiche
Attribute wie Orders
67
Ändern vonZeilen:SyntaxundSemantik
UpdateStatement =
UPDATE TableName SET Assignment { "," Assignment }
[ WHERE SearchCondition ] .
Assignment = ColumnName "=" ( ScalarExpr | NULL ) .
• DieUPDATE AnweisungändertallegemässWHERE-BedingungqualifiziertenZeilenineiner
Tabelle,sofernnachAbschlussderÄnderungenalle aufdieserTabelleund aufanderen
Tabellen definiertenIntegritätsbedigungenerfülltsind,insbesondere:
R.Marti
–
diePrimärschlüsselbedingung(PRIMARY KEY constraint)
–
weitereEindeutigkeitsbedingungen(UNIQUE constraints)
–
NOT NULL undCHECK constraints
–
Fremdschlüsselbedingungen(FOREIGN KEY constraints):
Fürjeden(NOT NULL)WerteinesFremdschlüsselsmussinderreferenziertenTabelleeinTupelmit
dementsprechendenPrimärschlüsselexistieren.
EbensomüssenalleFremdschlüsselbedingungenvonanderenTabellenaufdiegeänderteTabelle
erfülltbleiben.
InformationssystemefürIngenieure2016 – 3SQL
68
Ändern vonZeilen:Beispiele
UpdateStatement =
UPDATE TableName SET Assignment { "," Assignment }
[ WHERE SearchCondition ] .
Assignment = ColumnName "=" ( ScalarExpr | NULL ) .
•
KundemitNummer1wohntneuinLausanne
UPDATE Customers
SET
CusCity = 'Lausanne'
WHERE CusNo = 1
•
Kunden,diewenigerals10’000schulden undbisherfürmehrals100’000eingekauft haben,
erhalten5ProzentpunktemehrRabatt
UPDATE Customers
SET
Discount = Discount + 0.05
WHERE BalanceDue < 10000
AND CusNo IN ( SELECT CusNo FROM Orders
GROUP BY CusNo
HAVING SUM(Amount) > 100000 )
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
69
AbfragenundÄndern vonZeilen– Vergleich
•
Nummern undDiscountder Kunden,deren Bestellungen mehr als 100’000Wertsind
SELECT c.CusNo, c.Discount
FROM
Customers c, Orders o
WHERE o.CusNo = c.CusNo
GROUP BY c.CusNo, c.Discount
HAVING SUM(o.Amount) > 100000
•
Dieskann nicht für UPDATE verwendet werden,da nach UPDATE nur 1 Tabellenname stehen kann.
Þ Alternativemit Subquery,welche dieKundennummern derjenigen Kundenliefert,
deren Bestellungen mehr als 100’000Wertsind
SELECT CusNo, Discount
FROM
Customers
WHERE CusNo IN ( SELECT CusNo FROM Orders
GROUP BY CusNo
HAVING SUM(Amount) > 100000 )
•
Dieskann für folgenden Updateverwendet werden:
Kunden,diefür mehr als 100’000eingekauft haben erhalten 5Prozentpunkte mehr Rabatt
UPDATE Customers
SET
Discount = Discount + 0.05
WHERE CusNo IN ( SELECT CusNo FROM Orders
GROUP BY CusNo
HAVING SUM(Amount) > 100000 )
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
70
Ändern vonZeilenaufgrundvonDateninandererTabelle
Gegeben sei folgende Tabelle NewProductPrices:
CREATE TABLE NewProductPrices (
PrdNo
INTEGER,
NewPrice
FLOAT NOT NULL,
PRIMARY KEY (PrdNo),
FOREIGN KEY (PrdNo) REFERENCES Products
)
•
ÜbernehmealleProdukte-Preise ausderTabelleNewProductPrices indieTabelleProducts
(derinNewProductPrices exisitierendenProdukte,nichtaberandererProdukte).
LösungmitHilfeeinerskalaren Subquery:
UPDATE Products
SET
Price = ( SELECT n.NewPrice FROM NewProductPrices n
WHERE n.PrdNo = Products.PrdNo )
WHERE PrdNo IN ( SELECT n.PrdNo FROM NewProductPrices n )
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
71
Löschen vonZeilen:SyntaxundSemantik
DeleteStatement =
DELETE FROM TableName
[ WHERE SearchCondition ] .
• DieDELETE AnweisunglöschtallegemässWHERE-BedingungqualifiziertenZeilenin
einerTabelle,sofernnachDurchführungallerÄnderungendieaufanderenTabellen
definiertenIntegritätsbedigungenerfülltsind,insbesondere:
–
R.Marti
Fremdschlüsselbedingungen(FOREIGN KEY constraints):
AlleFremdschlüsselbedingungenvonanderenTabellenaufdiegeänderteTabelle
müssenerfülltbleiben.
InformationssystemefürIngenieure2016 – 3SQL
72
Löschen vonTupeln:Beispiele
DeleteStatement =
DELETE FROM TableName
[ WHERE SearchCondition ] .
• LöscheBestellungen,dievordem1.Januar2011 getätigtwurden
DELETE FROM Orders
WHERE OrderDate < DATE '2011-01-01'
• LöscheBestellungenallerProduktemitText'Paper'inderBeschreibung
DELETE FROM Orders
WHERE PrdNo IN ( SELECT PrdNo FROM Products
WHERE LOWER(Descr) LIKE '%paper%' )
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
73
InSQLineffizient/schwierig/unmöglichzulösendeProbleme
FolgendeAussagensindteilweiseabhängigvondendurchdasDBMSunterstützten
SQLStatementsundFunktionen
• Rangierung/bestenResultategemässvorgegebenemKriterium
• BerechnungdesMediansbzwvonQuantilen
• PivotierenvonTabellen(einebeliebteExcelFunktion)
• RekursiveAnfragenwiedietransitiveHülleeinerRelation,z.B.
– FindenallerVorfahreneinerPerson
aufgrundeinergegebenenEltern-KinderRelation
– ErreichbarkeitallerKnotenvoneinemAusgangsknotenineinemVerkehrsnetz
aufgrundeinervorgegebenenRelationderKanten
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
74
Beispiel:Rangierungin(ursprünglichem)StandardSQL
Products
PrdNo PrdDescr
901 'Mouse*Logitech'
902 'Hard*Drive*LaCie'
903 'Speaker*Bose'
Weight Price Warehouse Stock
20
20.00 'Zürich'
10000
1500 450.00 'Bern'
700 600.00 'Luzern'
400
100
SELECT ( SELECT COUNT(*) + 1
FROM
Products otherProduct
WHERE otherProduct.Price > givenProduct.Price ) AS WhatAmI,
givenProduct.Price, givenProduct.PrdDescr
FROM
Products givenProduct
ORDER BY 1
Diesisttypischerweisesehrineffizient.
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
75
Beispiel:Rangierungmitsog.WindowFunction
Products
PrdNo PrdDescr
Weight Price Warehouse Stock
901 'Mouse*Logitech'
902 'Hard*Drive*LaCie'
903 'Speaker*Bose'
20
20.00 'Zürich'
1500 450.00 'Bern'
700 600.00 'Luzern'
10000
400
100
SELECT RANK() OVER (ORDER BY Price DESC) AS "Rank",
Price,
PrdDescr
FROM
Products
ORDER BY 1
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
76
Beispiel:Pivotieren
• Pivotieren(Privoting,CrossTabulation/Crosstab)istbeliebteExcelFunktion:
Umordnung einerTabelle,sodassbestimmteDatenalsSpaltenüberschrift
verwendetwerden(imBeispielsinddiesJahrzahlen)
• SchwieriginSQL,abernichtunmöglich(siehefolgendeSeite)
YEARLY_PRODUCT_REVENUE
YR
2008
2008
2009
2009
2010
2010
2010
PRODUCT
Apple
Banana
Apple
Banana
Apple
Banana
Lemon
R.Marti
REVENUE
200
20
250
22
280
15
5
SumofREVENUE
PRODUCT
Apple
Banana
Lemon
GrandTotal
YR
2008
200
20
2009
250
22
220
272
InformationssystemefürIngenieure2016 – 3SQL
2010GrandTotal
280
730
15
57
5
5
300
792
77
Beispiel:PivotiereninSQL
select
case grouping(PRODUCT)
when 0 then PRODUCT
when 1 then ‘TOTAL’
else ‘***ERROR’
end as PRODUCT,
sum(case YR when 2008 then REVENUE else 0 end) as R_2008,
sum(case YR when 2009 then REVENUE else 0 end) as R_2009,
sum(case YR when 2010 then REVENUE else 0 end) as R_2010,
sum(REVENUE) as TOTAL
from
YEARLY_PRODUCT_REVENUE
group by
rollup(PRODUCT)
order by
PRODUCT
R.Marti
PRODUCT
Apple
Banana
Lemon
TOTAL
R_2008
R_2009
R_2010
TOTAL
200
250
280
730
20
22
15
57
0
0
5
5
220
272
300
792
InformationssystemefürIngenieure2016 – 3SQL
78
Beispiel:VorbereitungfürThema“RekursiveAnfragen”
Mögliche(nicht-zyklische) Routen
bestehendausgenau2
Teilstrecken(„legs“)undder
AnzahlTeilstrechen
BedeutungderAttribute
RelationAIRPORTS
SELECT leg_1.Src_Apt_IATA,
leg_2.Dest_Apt_IATA,
APT_IATA:
IATACodedesFlughafens
1 +
1 AS Leg_Count
APT_NAME:
NamedesFlughafens
FROM
Routes
leg_1, Routes
leg_2
CITY_NAME:
NamederStadt,indemsichderFlughafenbefindet
CTRY_NAME: NamederLandes,indemsichderFlughafenbefindet
WHERE leg_2.Src_Apt_IATA
= leg_1.Dest_Apt_IATA
LATITUDE:
GeographischeBreitedesOrts,wosichderFlughafenbefindet
AND
leg_2.Dest_Apt_IATA
<> leg_1.Src_Apt_IATA
LONGITUDE: GeographischeLängedesOrts,wosichderFlughafenbefindet
ALTITUDE:
HöhedesOrts,wosichderFlughafenbefindet
TIMEZONE: ZeitzonedesOrts,wosichderFlughafenbefindet
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
79
Beispiel:RekursiveAnfragen
Mögliche(nicht-zyklische) Routen
bestehendausbeliebigvielen
Teilstrecken(„legs“)undder
AnzahlTeilstrechen
WITH RECURSIVE rest(Src_Apt_IATA, Dest_Apt_IATA, Leg_Count) AS (
-- (initial) non-recursive subquery
SELECT Src_Apt_IATA, Dest_Apt_IATA, 1 AS Leg_Count
BedeutungderAttribute
FROM
Routes
UNION ALLRelationAIRPORTS
-- (repetitive)
recursive subquery
SELECT leg_1.Src_Apt_IATA,
rest.Dest_Apt_IATA,
APT_IATA:
IATACodedesFlughafens
rest.Leg_Count
AS Leg_Count
APT_NAME: + 1 NamedesFlughafens
FROM
Routes
leg_1, rest NamederStadt,indemsichderFlughafenbefindet
CITY_NAME:
CTRY_NAME: WHERE rest.Src_Apt_IATA
=NamederLandes,indemsichderFlughafenbefindet
leg_1.Dest_Apt_IATA
LATITUDE:
GeographischeBreitedesOrts,wosichderFlughafenbefindet
AND
rest.Dest_Apt_IATA
<> leg_1.Src_Apt_IATA
LONGITUDE: GeographischeLängedesOrts,wosichderFlughafenbefindet
)
HöhedesOrts,wosichderFlughafenbefindet
SELECT * FROMALTITUDE:
rest -- query
on
temporary recursive view named rest
R.Marti
TIMEZONE:
ZeitzonedesOrts,wosichderFlughafenbefindet
InformationssystemefürIngenieure2016 – 3SQL
80
Zusammenfassung(1)
SQLrealisiertdieTheoriedesRelationenmodellsweitgehend,abermitAbstrichen.
InsbesonderelässtSQL(nichtaberdasRelationenmodell)inTabellenDuplikatezu.
StrukturundIntegritätsbedingungenwerdenim"DDLTeil"vonSQLabgedeckt
(DDL=DataDefinitionLanguage).
• Struktur:
- RelationalsTabelle(TABLE),mitBefehlenwieCREATE,ALTER undDROP TABLE
- Attributalscolumn
• Integritätsbedingungen:
- Primärschlüssel(PRIMARY KEY)zurIdentifikationvonTupel
- weitereSchlüsselkandidaten(UNIQUE)
- NOT NULL undCHECK Bedingungen
- Fremdschlüssel(FOREIGN KEY)zumVerweis(REFERENCES)aufTupel
eineranderenTabelle
IntegritätsbedingungenerhöhendieDatenqualität,beeinträchtigenaberdiePerformance.
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
81
Zusammenfassung(2)
DieAuswahlvonDatenineinerodermehrerenTabellenwerdenim"DMLTeil"vonSQL
abgedeckt(DML=DataManipulationLanguage).
• Anfragen(AuswahldergewünschtenDaten)
– alleOperationenderelementarenunddererweitertenRelationenalgebrawerden
unterstützt(SELECT-FROM-WHERE "Block"plusUNION,EXCEPT,INTERSECT)
– inderWHERE–KlauselfliessenauchKonzepteausderLogikersterStufebzw.
demRelationenkalkülein– insbesonderederQuantorEXISTS –
ohneaberdieAusdruckskraftzuerhöhen
– zusätzlichwirddieGruppierungvonTupelnaufgrundgemeinsamerDatenwerte
unterstützt(GROUP BY-Klausel,AggregatsfunktionenundHAVING-Klausel)
– dieResultatekönnenaufWunschsortiertwerden(ORDER BY-Klausel)
• ÄndernderDateninderDatenbank:INSERT /UPDATE /DELETE
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
82
Zusammenfassung(3)
JenachSQLStandardbzw.DBMSProduktbietetSQLnochvielmehr!
• Struktur:"Objekt-Orientierung","LargeObjects"(LOBs),temporaleRelationen
• "allgemeine"Integritätsbedingungen(ASSERTIONs)
• Datenbank-Anfragen
– "Abspeichern"vonAnfragen(nichtderenResultate)alsVIEWs
– RekursiveAnfragen,erweiterteAggregatsfunktionen,…
• Datenbank-Änderungen:MERGE
• KontrolledesDatenzugriffs:GRANT /REVOKE
• ProzeduraleElemente:PROCEDUREs /FUNCTIONs /TRIGGERs
• SchnittstellenzugängigenProgrammiersprachen
R.Marti
InformationssystemefürIngenieure2016 – 3SQL
83