Ü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;
Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information