Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # Aufgabe 1
- SELECT vorname, name, gebtag, gehalt
- FROM personal
- ORDER BY gehalt DESC;
- #Aufgabe 2
- SELECT pnr, name, vorname
- FROM personal
- WHERE geschl = "w";
- #Aufgabe 3
- SELECT vorname, name
- FROM personal
- WHERE kostnr = 02
- ORDER BY name, vorname DESC;
- #Aufgabe 4
- SELECT vorname, name, gehalt
- FROM personal
- WHERE gehalt > 4000;
- #Aufgabe 5
- SELECT pnr, name, gebtag
- FROM personal
- WHERE gebtag < "1980-01-01"
- ORDER BY gebtag DESC;
- #Aufgabe 6
- SELECT name, vorname, gehalt, gehalt * 13 AS JahresGehalt
- FROM personal;
- #Aufgabe 7
- SELECT name, vorname, gehalt, gehalt * 1.035 AS ErhoehtesGehalt
- FROM personal
- WHERE geschl = "w";
- # Aufgabe 8
- SELECT name, vorname, geschl, stkl
- FROM personal
- WHERE stkl = "II"
- AND geschl = "m";
- #Aufgabe 9
- SELECT vorname, name, ortnr, gebtag
- FROM personal
- WHERE geschl = "m"
- AND ortnr = "29675"
- AND gebtag > "1969-12-31";
- #Aufgabe 10
- SELECT vorname, name, ortnr
- FROM personal
- WHERE ortnr = "29740"
- OR ortnr = "29003";
- #Aufgabe 11
- SELECT name, gehalt, stkl
- FROM personal
- WHERE stkl = "III"
- OR stkl = "V"
- ORDER BY name ASC;
- #Aufgabe 12
- SELECT vorname, name, pnr, kostnr
- FROM personal
- WHERE NOT kostnr = "03"
- ORDER BY kostnr, name ASC;
- #Aufgabe 13
- SELECT pnr, name, gehalt
- FROM personal
- WHERE gehalt BETWEEN 2000 AND 3000;
- #Aufgabe 14
- SELECT pnr, name, gebtag, eintritt
- FROM personal
- WHERE name LIKE "Sch%"
- ORDER BY eintritt ASC;
- #Aufgabe 15
- SELECT *
- FROM personal
- WHERE YEAR(eintritt) = 2005;
- #Aufgabe 16
- SELECT name, gebtag
- FROM personal
- WHERE MONTH(gebtag) = 8
- ORDER BY gebtag, name ASC;
- #Aufgabe 17
- SELECT name, vorname, gebtag
- FROM personal
- WHERE YEAR(gebtag) = YEAR(NOW()) - 28;
- #Aufgabe 18
- SELECT SUM(gehalt) AS AlleGehaelter, AVG(gehalt) AS Durchschnitt, MIN(gehalt) AS MINIMUM, MAX(gehalt) AS Maximum
- FROM personal;
- #Aufgabe 19
- SELECT geschl AS Geschlecht, COUNT(pnr) AS Anzahl
- FROM personal
- GROUP BY geschl;
- #Aufgabe 20
- SELECT kostnr AS Kostenstellen, COUNT(pnr) AS Anzahl
- FROM personal
- GROUP BY kostnr;
- #Aufgabe 21
- SELECT kostnr, SUM(gehalt)
- FROM personal
- WHERE kostnr = "03";
- #Aufgabe 22
- SELECT name AS Mitarbeitername, COUNT(pnr) AS Anzahl
- FROM personal
- GROUP BY name;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement