SQL-Abfragen Thema: Einführung in die Datenbanken
Versuchsziele
Vertiefung in SQL-Abfragen (Große Datenbank).
SQL-Abfragen mit der Oracle-Datenbank:
Verwendete Tabellen:
Countries
Departments
Employees
Job_grades
Locations
Regions
1. Aufgabe
Anzeige der Mitarbeiter (Vor- und Nachname, Abteilungsnummer) aller Abteilungen, deren Nummern größer 60 ist?
FIRST_NAME LAST_NAME DEPARTMENT_ID --- --- ---
Eleni Zlotkey 80
Ellen Abel 80
Jonathon Taylor 80
Steven King 90
Neena Kochhar 90
Lex De Haan 90
Shelley Higgins 110
William Gietz 110
2. Aufgabe Anzeige der Summe der Gehälter aller Mitarbeiter nach Abteilungsnummer( Absteigend sortiert). DEPARTMENT_ID SUM(SALARY) --- --- 90 58000
80 30100
110 20300
60 19200
20 19000
50 17500
10 4400
3. Aufgabe
Anzeige der Gehälter aller Mitarbeiter.
Hinweis:
Die Vertreter haben zu ihren Gehalt noch einen zusätzlichen prozentualen Anteil.
FIRST_NAME LAST_NAME GEHALT PROVISION GESAMT
Steven King 24000 0 24000
Neena Kochhar 17000 0 17000
Lex De Haan 17000 0 17000
Alexander Hunold 9000 0 9000
Bruce Ernst 6000 0 6000
Diana Lorentz 4200 0 4200
Kevin Mourgos 5800 0 5800
Trenna Rajs 3500 0 3500
Curtis Davies 3100 0 3100
Randall Matos 2600 0 2600
Peter Vargas 2500 0 2500
Eleni Zlotkey 10500 2100 12600
Ellen Abel 11000 3300 14300
Jonathon Taylor 8600 1720 10320
Kimberely Grant 7000 1050 8050
Jennifer Whalen 4400 0 4400
Michael Hartstein 13000 0 13000
Pat Fay 6000 0 6000
Shelley Higgins 12000 0 12000
William Gietz 8300 0 8300
4. Aufgabe
Geben Sie nur den Vornamen aller Mitarbeiter aus, die im Nachnamen die beiden Buchstaben
„lo“ haben. Verwendet Sie dazu die Funktion „lower“.
FIRST_NAME
--- Diana Jonathon Eleni
Anzeige der Mitarbeiter, die nach dem 1. Januar 1999 eingestellt wurden.
LAST_NAME HIRE_DATE --- Lorentz 07.02.99 Mourgos 16.11.99 Zlotkey 29.01.00 Grant 24.05.99
6. Aufgabe
Zählen Sie die Anzahl der in jedem Monat angestellten Mitarbeiter, unabhängig vom laufenden Jahr. Verwenden Si eine Konvertierung nach von Datum nach Characters.
MONAT ANZAHL --- ---
01 4
02 2
03 2
05 3
06 3
07 1
08 1
09 2
10 1
11 1
7. Aufgabe Zählen Sie die Anzahl der in jedem Quartal angestellten Mitarbeiter, unabhängig vom laufenden Jahr. Hinweis: Lösung a über Case Lösung b über to_Char ANZAHL --- 8
6
4
2
Anzeige der Liste aller Mitarbeiter mit dem Nachnamen und dem Nachnamen des Chefs.
a) Subselect b) Join
LAST_NAME CHEF --- ---
King nur Subselect
Kochhar King De Haan King Hunold De Haan Ernst Hunold Lorentz Hunold Mourgos King Rajs Mourgos Davies Mourgos Matos Mourgos Vargas Mourgos Zlotkey King Abel Zlotkey Taylor Zlotkey Grant Zlotkey Whalen Kochhar Hartstein King
Fay Hartstein Higgins Kochhar Gietz Higgins
Anzeige der Liste aller Mitarbeiter mit der Anzahl der Untergebenen.
LAST_NAME ANZAHL --- ---
Abel 0
Davies 0
De Haan 1
Ernst 0
Fay 0
Gietz 0
Grant 0
Hartstein 1
Higgins 1
Hunold 2
King 5
Kochhar 2
Lorentz 0
Matos 0
Mourgos 4
Rajs 0
Taylor 0
Vargas 0
Whalen 0
Zlotkey 3
10. Aufgabe Legen Sie eine Liste der Beschäftigungsgruppe an, deren mittleres Gehalt größer ist, als das mittlere Gehalt der einfachen Vertreter (SA_REP). JOB_ID MITTLERES_GEHALT --- --- SA_MAN 10500
AC_MGR 12000
MK_MAN 13000
AD_VP 17000
AD_PRES 24000
Geben Sie die Namen, die Abteilungsnamen und die Gehälter jener Beschäftigten aus, deren Gehalt höher ist, als das mittlere Gehalt in ihrer Jobgruppen. Das Ergebnis soll in
aufsteigender Reihenfolge der Namen angezeigt werden.
LAST_NAME DEPARTMENT_ID SALARY JOB_ID --- --- --- Hunold 60 9000 IT_PROG Rajs 50 3500 ST_CLERK Davies 50 3100 ST_CLERK Abel 80 11000 SA_REP
12. Aufgabe
Anzeige der Liste aller Mitarbeiter mit der Anzahl der Untergebenen, wobei nur die Mitarbeiter angezeigt werden sollen, die mehr als zwei Untergebene haben.
LAST_NAME ANZAHL --- ---
King 5
Mourgos 4
Zlotkey 3
13. Aufgabe Anzeige einer Liste aller Abteilungen mit der Anzahl der Mitarbeiter. DEPARTMENT_ID ANZAHL --- --- 10 1
20 2
50 5
60 3
80 3
90 3
110 2
1
Anzeige der Summe aller Grundgehälter pro Abteilung. Es sollen nur Mitarbeiter gezählt werden, die eine gerade Mitarbeiternummer haben. Außerdem dürfen nur Abteilungen angezeigt werden, die eine gültige Abteilungsnummer haben.
Hinweis:
Verwenden Sie die Funktion mod 2=mod(5,3) DEPARTMENT_ID SUM(SALARY)
--- --- 10 4400 20 6000 50 11400 60 6000 80 19600 90 41000 110 8300
15. Aufgabe
Anzeige der Mitarbeiter mit dem Abteilungsnamen.
LAST_NAME DEPARTMENT_ID ABTEILUNGSNAME --- King 90 Executive
Kochhar 90 Executive De Haan 90 Executive Hunold 60 IT
Ernst 60 IT Lorentz 60 IT
Mourgos 50 Shipping Rajs 50 Shipping Davies 50 Shipping Matos 50 Shipping Vargas 50 Shipping Zlotkey 80 Sales Abel 80 Sales Taylor 80 Sales Grant
Whalen 10 Administration Hartstein 20 Marketing
Fay 20 Marketing Higgins 110 Accounting Gietz 110 Accounting Hinweis:
Grant erhält man nur beim SubSelect
Anzeige der Mitarbeiter mit ihrer Abteilungsadresse. Es sollen nur Mitarbeiter vorkommen, die eine Mitarbeiternummer größer 110 haben (Join oder Subselect).
Hinweis:
Mit einem Subselect bekommt man vierzehn Werte. Mit einem Join dreizehn Werte.
LAST_NAME DEPT_ID STRAßE PLZ STADT
Mourgos 50 2011 Interiors Blvd 99236 South San
Francisco
Rajs 50 2011 Interiors Blvd 99236 South San
Francisco
Davies 50 2011 Interiors Blvd 99236 South San
Francisco
Matos 50 2011 Interiors Blvd 99236 South San
Francisco
Vargas 50 2011 Interiors Blvd 99236 South San
Francisco
Zlotkey 80 Magdalen Centre, The
Oxford Science Park
OX9 9ZB Oxford
Abel 80 Magdalen Centre, The
Oxford Science Park OX9 9ZB Oxford
Taylor 80 Magdalen Centre, The
Oxford Science Park OX9 9ZB Oxford Grant
Whalen 10 2004 Charade Rd 98199 Seattle
Hartstein 20 460 Bloor St. W. ON M5S 1X8 Toronto
Fay 20 460 Bloor St. W. ON M5S 1X8 Toronto
Higgins 110 2004 Charade Rd 98199 Seattle
Gietz 110 2004 Charade Rd 98199 Seattle
Anzeige der Mitarbeiter mit ihrer Job_Stufe.
LAST_NAME SALARY JOBSTUFE --- --- --- King 24000 E
Kochhar 17000 E De Haan 17000 E Hunold 9000 C Ernst 6000 C Lorentz 4200 B Mourgos 5800 B Rajs 3500 B Davies 3100 B Matos 2600 A Vargas 2500 A Zlotkey 10500 D Abel 11000 D Taylor 8600 C Grant 7000 C Whalen 4400 B Hartstein 13000 D Fay 6000 C Higgins 12000 D Gietz 8300 C 20 Zeilen ausgewählt.
18. Aufgabe
Welche Mitarbeiter arbeiten in Europa?
Bitte keine Abkürzungen in den Tabellen vornehmen!
LAST_NAME --- Zlotkey Abel Taylor
Bilden Sie einen Inner-Join mit den Tabellen Locations und Countries. Geben Sie die Stadt und das Land aus.
Attribute:
COUNTRY_ID
STADT LAND
Toronto Canada
Oxford United Kingdom
Southlake United States of America
South San Francisco United States of America
Seattle United States of America
21. Aufgabe
Bilden Sie einen Right-Outer-Join mit den Tabellen Locations und Countries. Geben Sie die Stadt und das Land aus.
Attribute:
COUNTRY_ID
STADT LAND
Toronto Canada
Germany
Oxford United Kingdom
Southlake United States of America
South San Francisco United States of America
Seattle United States of America
Case-Anweisung:
SELECT last_name, job_code, salary, CASE job_code
WHEN "VP" THEN (1.10*salary) WHEN "Eng" THEN (1.20*salary) ELSE salary
END Neues_Gehalt FROM employee
Datums-Funktionen
TO_DATE(char [ , format] [ , nlsparams] Konvertiert eine Zeichenfolge, die ein gültiges Datum enthält in einen Datumswert im Format der Formatmaske format. Wird format nicht angegeben, ist das Format DD- MM-YY.
Elemente der Datumsformatmaske:
Abkürzung Beschreibung
YYYY Komplettes Jahr als Zahl
YEAR Das Jahr in Worten
MM Zweistelliger Zahlenwert für den Monat MONTH Vollständiger Name des Monats
DY Abkürzung aus drei Buchstaben für den Wochentag
DAY Vollständiger Name des Tags
DD Tag des Monats als numerischer Wert
Q Quartal
Beispiele:
SELECT emp_no, TO_CHAR(hire_date, "MM/YY") Anfangsmonat FROM employee
WHERE last_name = "Yamamoto"
EMP_NO ANFANGSMONAT
118 07/93
SELECT e.full_name, e.emp_no, e.dept_no, d.department FROM employee e, department d
WHERE e.dept_no = d.dept_no;
SELECT *
FROM emp e INNER JOIN phone p ON e.empno = p.empno
Left Outer Join
SELECT e.empno Nr, e.last_name Mitarbeiter, p.phone Telefon from emp e LEFT OUTER JOIN phone p
ON e.empno = p.empno;
Right Outer Join
SELECT *
from emp e RIGHT OUTER JOIN phone p ON e.empno = p.empno;
