Inhaltsverzeichnis

ÜBUNGEN

bearbeiten

Übung 1

Aufgabe 1
Übung: Selektieren des Datensatzes für Hamlet, Cleopatra, Romeo oder Julia (einzelne Abfragen) 

SELECT * FROM CHARACTER WHERE charname = 'Hamlet';
SELECT * FROM CHARACTER WHERE charname = 'Cleopatra';
SELECT * FROM CHARACTER WHERE charname = 'Romeo';
SELECT * FROM CHARACTER WHERE charname = 'Julia';  -- liefert die falsche Julia, da sie im Englischen Juliet heißt.

Aufgabe 2
Übung: Selektieren aller Charaktere mit genau 17 speeches. 

SELECT * FROM CHARACTER WHERE speechcount = 17;

Aufgabe 3
Übung: Selektieren aller Charaktere mit 15 bis 20 speeches. 

SELECT * FROM CHARACTER WHERE speechcount BETWEEN 15 AND 20;

Aufgabe 4
Übung: Selektieren aller Charaktere, die eine Beschreibung (description) haben, d.h. wo die Beschreibung nicht NULL ist. 

SELECT * FROM CHARACTER WHERE description IS NOT NULL;

Übung 2

Aufgabe 1
Übung: Gesamtsumme aller speeches aus den Charakteren abfragen 

SELECT SUM(speechcount)
FROM CHARACTER;

Aufgabe 2
Übung: Anzahl aller Werke 

SELECT COUNT(*)
FROM WORK;

Aufgabe 3
Übung: Minimum und Maximum der speeches 

SELECT MIN(speechcount),MAX(speechcount)
FROM CHARACTER;

Aufgabe 4
Übung: Die Anzahl der Werke vor 1600 

-- sinnig:
SELECT COUNT(YEAR)
FROM WORK
WHERE YEAR < '1600';
 
-- unsinnig (aber verschachtelt)
SELECT COUNT(*)
FROM WORK
WHERE YEAR < (
  SELECT DISTINCT(YEAR)
  FROM WORK
  WHERE YEAR = '1600'
);

Übung 3

Aufgabe 1
Übung: Alle Werke vor 1600 nach Jahr sortiert 

SELECT YEAR, title
FROM WORK
WHERE YEAR < '1600'
ORDER BY YEAR ASC;

Aufgabe 2
Übung: Alle Werke nach 1605 nach Anzahl der Worte 

SELECT YEAR, title
FROM WORK
WHERE YEAR > '1605'
ORDER BY totalwords ASC;

Aufgabe 3
Übung: Alle Werke nach Anzahl der Absätze 

SELECT YEAR, title, totalparagraphs
FROM WORK
ORDER BY totalparagraphs;

Übung 4

Aufgabe 1
Übung: alle Absätze aus Hamlet mit mehr als 1000 Zeichen 

SELECT workid, charcount, plaintext
FROM paragraph
WHERE (workid = 'hamlet') AND (charcount > 1000);

Aufgabe 2
Übung: alle Absätze aus Macbeth mit mehr als 250 Wörtern 

SELECT workid, wordcount, plaintext
FROM paragraph
WHERE (workid = 'macbeth') AND (wordcount > 250);

Aufgabe 3
Übung: alle Werke zwischen 1600 und 1605 

SELECT YEAR, title
FROM WORK
WHERE (YEAR >= 1600) AND (YEAR <= 1605);

Übung 5

Aufgabe 1
Übung: alle Absätze aus Hamlet mit mehr als 1000 Zeichen oder 300 Wörtern 

SELECT workid, charcount, wordcount, plaintext
FROM paragraph
WHERE (workid = 'hamlet') AND ((charcount > 1000) OR (wordcount > 300))
ORDER BY wordcount, charcount;

Aufgabe 2
Übung: alle Absätze aus Macbeth mit weniger als 100 Zeichen oder mehr als 250 Wörtern 

SELECT workid, charcount, wordcount, plaintext
FROM paragraph
WHERE (workid = 'macbeth') AND ((charcount < 100) OR (wordcount > 250))
ORDER BY wordcount, charcount;

Übung 6

Aufgabe 1
Übung: alle Absätze aus Macbeth, die das Wort love enthalten 

SELECT workid, charcount, wordcount, plaintext
FROM paragraph
WHERE (workid = 'macbeth') AND (plaintext LIKE '%love%');

Aufgabe 2
Übung: alle Absätze aus Macbeth, die das Wort love enthalten und von einem Charakter mit 'mac' im Namen gesprochen werden 

SELECT workid, charcount, wordcount, plaintext
FROM paragraph
WHERE (workid = 'macbeth') AND ((plaintext LIKE '%love%') AND (charid LIKE '%mac%'));

Aufgabe 3
Übung: was ist wichtiger in Hamlet: Liebe oder Tod? 

SELECT COUNT(*)
FROM paragraph
WHERE (workid = 'macbeth') AND (plaintext LIKE '%love%');
 
SELECT COUNT(*)
FROM paragraph
WHERE (workid = 'macbeth') AND (plaintext LIKE '%death%');

Übung 7

Aufgabe 1
Übung: alle Zahlen (mit Namen) kleiner als 3 

SELECT *
FROM
  (numbers CROSS JOIN names)
WHERE (numbers.id = names.id) AND (VALUE < 3);

Aufgabe 2
Übung: Gewichtstabelle für alle Lebensmittel, die 'flour' im Namen enthalten 

SELECT food_des.ndb_no, long_desc, amount, msre_desc, gm_wgt
FROM
    food_des
    CROSS JOIN weight
WHERE
    (food_des.ndb_no = weight.ndb_no)
    AND (long_desc ILIKE '%flour%');

Aufgabe 3
Übung: Inhaltsstoffe Ihres Lieblingsessens 

SELECT food_des.ndb_no, long_desc, nutr_val, units, nutrdesc, nut_data.nutr_no
FROM 
    food_des
    CROSS JOIN nut_data
    CROSS JOIN nutr_def
WHERE
    food_des.ndb_no = nut_data.ndb_no
    AND nut_data.nutr_no = nutr_def.nutr_no
    AND (long_desc ILIKE '%McDONALD_S, Cheeseburger%')
;

Übung 8

Aufgabe 1
Übung: Finde das Lebensmittel, das am meisten Koffein enthält. 

SELECT ndb_no, long_desc, nutr_val, units, nutrdesc, nutr_no
FROM 
    food_des
    INNER JOIN nut_data USING (ndb_no)
    INNER JOIN nutr_def USING (nutr_no)
WHERE
    (nutrdesc = 'Caffeine')
ORDER BY nutr_val DESC
LIMIT 1
 
-- Alternative Lösung 1
 
SELECT ndb_no, long_desc, nutr_val, units, nutrdesc, nutr_no
FROM 
    food_des
    INNER JOIN nut_data USING (ndb_no)
    INNER JOIN nutr_def USING (nutr_no)
WHERE
    nutrdesc = 'Caffeine'
    AND nutr_val = (
        SELECT MAX(nutr_val)
        FROM 
            nut_data
            INNER JOIN nutr_def USING (nutr_no)
        WHERE
            (nutrdesc = 'Caffeine')
    )
 
-- Alternative Lösung 2
 
SELECT ndb_no, long_desc, nutr_val, units, nutrdesc, nut_data.nutr_no
FROM 
    food_des
    INNER JOIN nut_data USING (ndb_no)
    INNER JOIN nutr_def USING (nutr_no)
    INNER JOIN
        (SELECT MAX(nutr_val) AS maxval, nutr_no
        FROM
            nut_data
            INNER JOIN nutr_def USING (nutr_no)
        GROUP BY nutr_no) AS max_nutr
        ON
            (max_nutr.nutr_no = nut_data.nutr_no
            AND max_nutr.maxval = nut_data.nutr_val)
WHERE
    nutr_val > 0
    AND nutrdesc = 'Caffeine';

Übung 9

Aufgabe 1
Übung: Gibt es Zahlen, die keinen Namen haben? Gibt es Namen, die keine Zahl haben? In einer Antwort, bitte! 

SELECT *
FROM
  numbers
  FULL OUTER JOIN names USING (id)
WHERE
  (VALUE IS NULL)
  OR (name IS NULL)
;

Aufgabe 2
Übung: Gibt es ein Lebensmittel, von dem wir gar nichts wissen? 

SELECT ndb_no, long_desc
FROM 
    food_des
    LEFT OUTER JOIN nut_data USING (ndb_no)
WHERE
  (nutr_no IS NULL);

Übung 10

Aufgabe 1
Übung: alle Teilnehmer 

SELECT *
FROM employee
WHERE (rolle = 'Teilnehmer')

Aufgabe 2
Übung: alle Teilnehmer unter Johannes 

SELECT employee.name AS name, boss.name AS boss
FROM
    employee
    INNER JOIN employee AS boss ON employee.manager = boss.id
WHERE
  (boss.id = 2)
  AND (employee.rolle = 'Teilnehmer')

Aufgabe 3
Übung: alle Trainer unter Ingo 

SELECT employee.name AS name, boss.name AS boss
FROM
    employee
    INNER JOIN employee AS boss ON employee.manager = boss.id
WHERE
  (boss.id = 1)
  AND (employee.rolle = 'Trainer')

Aufgabe 4
Übung: alle Angestellten ohne Manager, und deren Untertanen 

SELECT nomanager.name, employee.name
FROM
  employee
RIGHT OUTER JOIN (
  SELECT *
  FROM employee
  WHERE manager IS NULL
) AS nomanager ON ( nomanager.id = employee.manager)
 
-- Alternative Lösung
 
SELECT nomanager.name, employee.name
FROM
  employee
  RIGHT OUTER JOIN employee AS nomanager 
  ON ( nomanager.id = employee.manager)
WHERE manager.manager IS NULL

Übung (eigen)

Aufgabe 1
Übung: Erstelle eine Tabelle chars und fülle sie mit denselben Inhalten wie die Tabelle character. 

CREATE TABLE chars (
  charid CHARACTER VARYING(32) NOT NULL,
  cahrname CHARACTER VARYING(64) NOT NULL,
  abbrev CHARACTER VARYING(32),
  description CHARACTER VARYING(2056),
  speechcount INTEGER
);
 
INSERT INTO chars
SELECT * FROM CHARACTER;

Aufgabe 2
Übung: Lösche Tabellen, die mit einem bestimmten Prefix beginnen 

SET @TABLE_PREFIX = 'patrick\_2\_\_pm';
SET @DATABASE_SOURCE = 'd0173311';
SET GROUP_CONCAT_MAX_LEN=10000;
SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME)
               FROM information_schema.TABLES
              WHERE TABLE_SCHEMA = @DATABASE_SOURCE
                AND TABLE_NAME LIKE CONCAT(@TABLE_PREFIX,'%'));
SET @delStmt = CONCAT('DROP TABLE ',  @tbls);
PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SHOW TABLES;