SQL - deel 1
-
Upload
katrien-verbert -
Category
Documents
-
view
2.870 -
download
3
description
Transcript of SQL - deel 1
SQL – deel 1 Katrien Verbert [email protected]
SQL
• vandaag: basisconcepten, voorbeelden, oefeningen • volgende week: geavanceerde concepten, extra voorbeeld
2
geschiedenis
• SEQUEL (Structured English Query Language) – voor experimentele gegevensbank "System R" (IBM)
• → SQL (Structured Query Language) – voor DB2, Oracle, INGRES/SQL, ... – eerste commerciële versie: Oracle in 1979
3
standaardisatie: ANSI - ISO
• SQL1 : 1986 • SQL2 of SQL92 : 1992
– “levels of conformance”: entry – intemediate - full
• SQL99 : 1999 – aanvullingen met o.a. nieuwe data types, object-georiënteerde
concepten en triggers – levels of conformance: Core SQL:1999 - Enhanced SQL:1999
4
de meeste implementaties van SQL verschillen in meer of mindere mate van de standaard
terminologie
• relationeel model
– schema – relatie – tupel – attribuut
• SQL
– schema – tabel – rij – kolom
5
schema
• bevat – schema-naam – eigenaar en – beschrijving van elementen
• tabellen • restricties (constraints) • views • domeinen • autorisaties • …
• creatie van schema: CREATE SCHEMA
CREATE SCHEMA COMPANY AUTHORIZATION Jsmith ;!
6
cataloog
• = een verzameling gegevensbankschema's in een “SQL omgeving” • de cataloog bevat één speciaal schema: INFORMATION_SCHEMA • integriteitsrestricties kunnen opgelegd worden tussen relaties in cataloog • schema’s binnen eenzelfde cataloog kunnen elementen delen
7
creatie van een tabel
• naam • voor alle attributen:
– naam + SQL-gegevenstype + restricties
• restricties
CREATE TABLE [ <schema name >. ] <table name> ( { <column name> <column type> [<aAribute constraint>] } {<table constraint>} *)
8
numerieke gegevenstypes en domeinen • gehele waarden:
– INTEGER - INT – SMALLINT
• niet gehele waarden: – FLOAT – REAL – DOUBLE PRECISION
• geformateerde getallen: – DECIMAL (i, j) – DEC (i, j) – NUMERIC (i, j) met
i: precisie (= totaal aantal dec.cijfers) j: schaal (= aantal cijfers na dec. punt)
10
karakter strings • string met vaste lengte:
– CHAR (n) – CHARACTER (n)
• string met variabele lengte: – VARCHAR(n) – CHAR VARYING (n) – CHARACTER VARYING (n)
• notatie van waarden: tussen apostrofs: ‘abc‘
11
gegevenstypes en domeinen in SQL
• bit strings: – met vaste lengte: BIT (n) – met variabele lengte: BIT VARYING (n) – notatie van waarden: B’10101’
• logische waarden – BOOLEAN – mogelijke waarden: TRUE FALSE UNKNOWN
12
datum en tijd
• DATE – 10 posities: YYYY-MM-DD – DATE’2004-02-23’
• TIME – ten minste 8 posities: HH:MM:SS – TIME’09:12:47’
• TIMESTAMP – DATE en TIME en 6 posities voor fracties van seconden: – TIMESTAMP’2004-02-23 09:12:47 648302’
13
in verschillende implementaties
• namen kunnen verschillen • soms nog meer variaties in types
• voorbeeld: – http://en.wikipedia.org/wiki/
Comparison_of_relational_database_management_systems
14
CREATE DOMAIN SSN_TYPE AS CHAR(9) ;!
definitie van type van een attribuut
• ofwel rechtstreeks • ofwel via definitie van domein
15
definiëren van restricties op tabel
• Soorten: – primaire sleutel: PRIMARY KEY <attrlist> – alternatieve sleutel: UNIQUE <attrlist> – verwijssleutel: FOREIGN KEY <attrlist> REFERENCES
<table><attrlist>
• Actie bij schending van referentiële integriteit: – bij trigger: ON DELETE / ON UPDATE – actie: SET NULL / CASCADE / SET DEFAULT
16
restricties op attribuut
• NOT NULL (automatisch voor primaire sleutels) • DEFAULT <value> • CHECK (voorwaarde)
– kan ook samen met domein declaratie
Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);
18
specificeren van restricties
• twee mogelijkheden – onmiddellijk bij tabel of attribuut – met sleutelwoord ‘CONSTRAINT’ en het geven van een naam
• geeft de mogelijkheid om de restrictie later gemakkelijk aan te passen, weg te laten,….
19
weglaten van tabel / schema
• Drop behaviour voor schema: – CASCADE:
• schema + alle tabellen erin weglaten
– RESTRICT: • schema enkel weglaten als het leeg is
• Drop behaviour voor tabel: – CASCADE:
• alle restricties en views die aan de tabel refereren worden mee weggelaten
– RESTRICT: • tabel enkel weglaten als er geen restricties of views naar verwijzen
DROP SCHEMA <name> <drop behaviour> DROP TABLE <name> <drop behaviour>
21
– <drop behaviour> =
• CASCADE | RESTRICT
– <default option> = • DROP DEFAULT | SET DEFAULT <value>
wijzigen van tabel
ALTER TABLE <name> { ADD <column name> <column type>
| DROP <column name> <drop behaviour> | ALTER <column name> <default opVon> | ADD CONSTRAINT <constraint definiVon> | DROP CONSTRAINT <constraint name> <drop
behaviour> }
22
ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12) ;!
ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE ;!
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT ;!
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT ‘33445555’ ;!
ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE ;!
23
• Basisvorm van een vraag (query):
• vgl. met relationele algebra: – SELECT projectie – FROM carthesisch product – WHERE selectie
opvragingen (queries) in SQL
SELECT <aAributen lijst> FROM <tabellen lijst> WHERE <condiVes> ;
24
In relaVonele algebra:
πBdate,Address (σ Fname = 'John' AND Minit = ‘John’ AND Lname = ‘Smith’ (EMPLOYEE) )
voorbeeld
• Q_0: geef de geboortedatum en het adres van werknemer John B. Smith
Bdate Address ---------- ------------------------ 1965-01-09 731 Fondren, Houston, TX!
SELECT Bdate, Address FROM EMPLOYEE WHERE Fname='John' AND Minit='B' AND Lname='Smith’ ;!
• Q1B: geef naam en adres van alle werknemers die voor het "research" departement werken
• vergelijk met uitdrukking in tupel relationele calculus
SELECT E.Fname, E.Lname, E.Address FROM EMPLOYEE E, DEPARTMENT D WHERE D.Dname = 'Research' AND D.Dnumber = E.Dno ;!
26
{ t.Fname, t.Lname, t.Address | EMPLOYEE(t) AND (∃d) (DEPARTMENT(d) AND d.Dname = 'Research’ AND d.Number = t.Dno) }
geef namen van projecten die gecontroleerd worden door departement 5
27
select ? from ? where ?
28
select ? from ? where ?
SELECT pname FROM project WHERE dnum=5;
geef social security numbers van werknemers die werken aan projecten die gecontroleerd worden door departement 5
geef voornaam en familienaam van werknemers die werken aan projecten die gecontroleerd worden door departement 5
29
select ? from ? where ?
SELECT pname, essn FROM project, works_on WHERE dnum=5 and pno=pnumber;
30
SELECT pname, fname, lname FROM project, works_on, employee WHERE dnum=5 and pno=pnumber and essn=ssn;
SELECT pname, essn FROM project, works_on WHERE dnum=5 and pno=pnumber;
SELECT pname FROM project WHERE dnum=5;
SELECT Pnumber, Dnum, Lname, Address, Bdate FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND PlocaVon = 'Stafford’ ;
voorbeeld • geef voor elk project dat als locatie Stafford heeft, het
projectnummer, nummer van het departement dat het leidt, en de familienaam, adres en geboortedatum van de manager van dat departement
resultaat:
Pnumber Dnum Lname Address Bdate ------- ---- ------- ----------------------- ---------- 10 4 Wallace 291 Berry, Bellaire, TX 1941-06-20 30 4 Wallace 291 Berry, Bellaire, TX 1941-06-20
SELECT Fname, EMPLOYEE.Name, Address FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.Name = 'Research' AND DEPARTMENT.Dnumber = EMPLOYEE.Dnumber ;!
dubbelzinnige attribuutnamen
• Tot nog toe: steeds duidelijk bij welke relatie een attribuut behoorde
• Wat indien dit niet het geval is? vb. attributen Name en Dnumber (i.p.v. Dno) in EMPLOYEE en
DEPARTMENT tabel
• Oplossing: attribuutnaam = <tabel>.<attribuut>
aliasen
• voorbeeld: – geef voor elke werknemer de voor- en familienaam en de
familienaam van zijn/haar supervisor – 2 verwijzingen naar EMPLOYEE: een voor werknemer zelf, een
voor supervisor
• hoe van elkaar onderscheiden? • "aliases": de verwijzingen krijgen aparte namen
SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn = S.Ssn ;!
33
aliasen – Q 8: for each employee, retrieve the employee’s first and last
name and the last name of his or her immediate supervisor
– Definitie: - onmiddellijk na de naam van de relatie - na sleutelwoord AS
SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn = S.Ssn ;!
SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE E S WHERE E.Super_ssn = S.Ssn ;
34
SELECT Ssn FROM EMPLOYEE ;!
SELECT Ssn, Dname FROM EMPLOYEE, DEPARTMENT ;!
weglaten van WHERE en van attributenlijst
• WHERE kan weggelaten worden: geen selectie
carthesisch product
35
SELECT * FROM EMPLOYEE WHERE Dno = 5 ;!
SELECT * FROM EMPLOYEE, DEPARTMENT WHERE Dname = 'Research' AND Dno = Dnumber ;!
SELECT * FROM EMPLOYEE, DEPARTMENT ;!
weglaten van WHERE en van attributenlijst
• Alle attributen selecteren (met *) : geen projectie
36
Verschil tussen tabellen en relaties
• een relatie uit het relationeel model is een verzameling tupels en bevat dus geen dubbels
• SQL elimineert niet automatisch dubbels
37
SELECT ALL Salary FROM EMPLOYEE ;!
SELECT DISTINCT Salary FROM EMPLOYEE ;!
Salary ------ 30000 40000 25000 43000 38000 25000 25000 55000!
Salary ------ 30000 40000 25000 43000 38000 55000!
• Vermijden van dubbels: SELECT DISTINCT
SELECT Salary FROM EMPLOYEE ;!
equivalent met
38
(SELECT DISTINCT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith') UNION (SELECT DISTINCT Pnumber FROM PROJECT, WORKS_ON, EMPLOYEE WHERE Pno = Pnumber AND Essn = Ssn AND Lname = 'Smith') ;!
Verzameling-bewerkingen in SQL
– UNION, INTERSECT, EXCEPT (= verschil) – relaties moeten vergelijkbaar zijn
• zelfde attribuuttypes in zelfde volgorde – dubbels worden automatisch verwijderd
– vb: Q 4: • geef alle projecten waarbij Smith betrokken is als manager van het
controlerend departement of waaraan hij meewerkt
operaties die slaan op multisets
• sleutelwoord ALL na de operator: – UNION ALL – EXCEPT ALL – INTERSECT ALL
R A
a2 a1
a2 a3
V A
a3 a2
S A
a2 a1
a4 a5
W A
a2 a1
T A
a1 a1
a2 a2 a2 a3 a4 a5
T ← R UNION ALL S V ← R EXCEPT ALL S W ← R INTERSECT ALL S
SELECT Fname,Lname FROM EMPLOYEE WHERE Address LIKE '%Houston,TX%’ ;
SELECT Fname,Lname FROM EMPLOYEE WHERE Bdate LIKE '_ _5_ _ _ _ _ _ _’ ;!
string- en rekenkundige operatoren
• Test string op patroon: LIKE – _ : één willekeurig teken – % : willekeurige rij tekens
Q12: Geef de naam van alle werknemers die in Houston, Texas wonen
Q12A: Geef de naam van alle werknemers die in de jaren 1950 geboren zijn
SELECT Fname, Lname, 1.1 * Salary AS Increased_sal FROM EMPLOYEE, WORKS_ON, PROJECT WHERE Ssn = Essn AND Pno = Pnumber AND Pname = 'ProductX’ ;!
rekenkundige operatoren: +, -, *, /
• kunnen gebruikt worden op numerieke waarden
• Q13: Geef het salaris van elke werknemer die werkt aan project ‘ProductX’, verhoogd met 10 %
42
andere operatoren
• strings – concatenatie | |
• datum, tijd, tijdsstempel: – verhogen of verlagen met een interval – verschil tussen data, tijden: → een interval
de salaris-voorwaarde is equivalent met ((Salary >= 30 000) AND (Salary <= 40 000))
SELECT * FROM EMPLOYEE WHERE (Salary BETWEEN 30 000 AND 40 000) AND Dno = 5 ;!
• BETWEEN: – Q14: Geef alle werknemers van departement 5 met een
salaris tussen 30 000 en 40 000 EUR
43
SELECT Dname, Fname, Lname, Pname FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE Dnumber = Dno AND Ssn = Essn
AND Pno = Pnumber ORDER BY Dname, Lname, Fname ;
ordenen van query resultaten • ORDER BY <attributen> [ ASC | DESC ]
– Q 15: Geef een lijst van de namen van de werknemers en de namen van de projecten waarop zij werken, per departement, en in elk departement alfabetisch geordend volgens familienaam, voornaam
• default ordening is stijgend (ASC) • dalende orde (DESC) moet steeds expliciet vermeld worden: ORDER BY Dname DESC, Lname ASC, Fname ASC
• nagaan of een waarde null is: – IS NULL – IS NOT NULL
• Q 18 : retrieve the names of all employees who have no
supervisors
SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL ;!
45
SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary) FROM EMPLOYEE;
aggregaatfuncties
• Ingebouwde functies: – COUNT, SUM, MAX, MIN, AVG
• voorbeeld: zoek som, maximum, minimum en gemiddelde van alle salarissen
46
SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary) FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber) WHERE DNAME = 'Research’ ;
– Q 20 • zoek som, maximum, minimum, gemiddelde, van de salarissen van alle
werknemers uit het 'Research' departement
aggregaatfuncties
47
• bepaal totaal aantal werknemers
• bepaal aantal werknemers in departement 'Research’
• tel het aantal verschillende waarden voor salaris
SELECT COUNT(*) FROM EMPLOYEE ;
SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE Dno = Dnumber AND Dname = 'Research’ ;!
SELECT COUNT (DISTINCT Salary) FROM EMPLOYEE ;
aggregaatfuncties
48
SELECT Lname, Fname FROM EMPLOYEE WHERE ( SELECT COUNT(*) FROM DEPENDENT WHERE Ssn = Essn ) >= 2 ;!
aggregaatfuncties
– gebruik binnen WHERE (voor geneste queries): – voorbeeld: geef alle werknemers die minstens 2 personen ten
laste hebben
49
SELECT DNO, COUNT(*), AVG(Salary) FROM EMPLOYEE GROUP BY DNO ;
groepering
• Groeperen van tupels : GROUP BY – aggregaatfuncties worden voor elke groep afzonderlijk toegepast
• voorbeeld: geef voor elk departement het nummer, het aantal werknemers en het gemiddelde salaris
50
aanpassingen maken in SQL
• Toevoegen: INSERT • Weglaten: DELETE • Wijzigen: UPDATE
51
INSERT INTO EMPLOYEE VALUES ('Richard', 'K', 'Marini', '653298653', ‘1962-‐12-‐30', '98 Oak Forest, Katy', TX', 'M', 37000, '987654321', 4) ;
INSERT INTO EMPLOYEE (Fname, Lname, Ssn) VALUES ('Richard', 'Marini', '653298653') ;
toevoegen
• INSERT commando • verschillende vormen
– zonder expliciete vermelding van attributen: • volgorde respecteren
– met expliciete vermelding van attributen: • nulls en defaults kunnen weggelaten worden
52
CREATE TABLE DEPTS_INFO ( Dept_name VARCHAR(15),
No_of_emps INTEGER, Total_sal INTEGER ); INSERT INTO DEPTS_INFO (Dept_name,No_of_emps,Total_sal)
SELECT Dname, COUNT(*), SUM(Salary) FROM ( DEPARTMENT JOIN EMPLOYEE ON
Dnumber = Dno ) GROUP BY Dname ;!
In dit voorbeeld niet zo'n goed idee redundantie + mogelijk verlies van integriteit beter : view definiëren (zie later)
Meerdere tupels tegelijk toevoegen
• vaak meteen na creatie van tabel, laden van resultaat van een query
53
DELETE FROM EMPLOYEE ;!
DELETE FROM EMPLOYEE WHERE Ssn =‘123456789' ;!
DELETE FROM EMPLOYEE WHERE Dno IN (SELECT Dnumber FROM DEPARTMENT WHERE Dname = 'Research') ;!
weglaten van tupels • DELETE FROM <tabel> WHERE <conditie>
– alle tupels die aan conditie voldoen worden weggelaten – geen WHERE deel: alle tupels weg
• ! tabel verdwijnt niet, wordt enkel leeg • tabel kan weggelaten worden met DROP TABLE
DELETE FROM EMPLOYEE WHERE Lname =‘Brown' ;!
54
UPDATE PROJECT SET PlocaVon = 'Bellaire', Dnum = 5 WHERE Pnumber = 10 ;
UPDATE EMPLOYEE SET Salary = Salary * 1.1 WHERE Dno IN (SELECT Dnumber FROM DEPARTMENT WHERE Dname = 'Research') ;!
wijzigen van tupels
• UPDATE <tabel> SET { < attr > = < expr > , … } WHERE <conditie>
• selecteer te wijzigen tupels met WHERE-conditie • ken voor die tupels nieuwe waarden aan attributen toe: in SET deel
55
VRAGEN?
56