SQL Übung 2 (Uni Köln)

Allgemeine Hinweise

Reihenfolge der Keywords: SELECT, WHERE, GROUP BY, ORDER BY

GROUP BY muss alle Felder aus dem SELECT enthalten, nach denen nicht aggregiert wird!

Strings in einfachen Anführungszeichen angeben.

Datumsregeln: Wird ein Datum angegeben, so muss es mit dem Keyword date gekennzeichnet werden, z.B.: SELECT date '1996-01-01'. Auf dieses Datum lassen sich dann Operationen anwenden, wie z.B. das Addieren von Zeitintervalen: SELECT date '1996-01-01' + interval 3 month.

Will man nur eine bestimmte Anzahl an Ergebnissen haben, so kann man mit rownum <= x die Ergebnissmenge auf x Einträge begrenzen. (Oracle spezifisch, wird am Lehrstuhl benutzt)

Lösungen

Aufgabe 1

Task: Ermitteln Sie die Anzahl an Bestellpositionen mit Kunden in der Region "Europe".

SELECT COUNT(L_LINENUMBER)
FROM REGION, NATION, CUSTOMER, ORDERS, LINEITEM
WHERE

 R_REGIONKEY = N_REGIONKEY AND
 N_NATIONKEY = C_NATIONKEY AND
 C_CUSTKEY = O_CUSTKEY AND
 O_ORDERKEY = L_ORDERKEY AND
 R_NAME = 'EUROPE'

Strings in einfachen Anführungszeichen

Aufgabe 2

Task: Ermitteln Sie die Anzahl an Bestellpositionen bezüglich der Region (r_name) der Kunden

SELECT R_NAME, COUNT(L_LINENUMBER)
FROM REGION, NATION, CUSTOMER, ORDERS, LINEITEM
WHERE

 R_REGIONKEY = N_REGIONKEY AND
 N_NATIONKEY = C_NATIONKEY AND
 C_CUSTKEY = O_CUSTKEY AND
 O_ORDERKEY = L_ORDERKEY

GROUP BY R_NAME

Aufgabe 3

Task: Berechnen Sie den Durchschnittspreis "AvgPrice" aller Bestellungen (orders), die von Kunden in der Region "EUROPE" getätigt wurden. Runden Sie das Resultat auf zwei Nachkommastellen.

SELECT ROUND(AVG(O_TOTALPRICE),2) AS AvgPrice
FROM REGION, NATION, CUSTOMER, ORDERS
WHERE

 R_REGIONKEY = N_REGIONKEY AND
 N_NATIONKEY = C_NATIONKEY AND
 C_CUSTKEY = O_CUSTKEY AND
 R_NAME = 'EUROPE'

GROUP BY R_NAME

Aufgabe 4

Task: Berechnen Sie den Durchschnittspreis AvgPrice aller Bestellungen bezüglich der Region (r_name) der Kunden. Runden Sie das Resultat auf zwei Nachkommastellen.

SELECT R_NAME, ROUND(AVG(O_TOTALPRICE),2) AS AvgPrice
FROM REGION, NATION, CUSTOMER, ORDERS
WHERE

 R_REGIONKEY = N_REGIONKEY AND
 N_NATIONKEY = C_NATIONKEY AND
 C_CUSTKEY = O_CUSTKEY

GROUP BY R_NAME

Aufgabe 5

Task: Berechnen Sie den Durchschnittspreis "AvgPrice" aller Bestellungen (gerundet auf zwei Nachkommastellen) bezüglich des Marktsegments (c_mktsegment) der Kunden und des Teiltyps (p_type). Sortieren Sie die Ergebnisse nach Marktsegment und Teiltyp.

SELECT C_MKTSEGMENT, P_TYPE, ROUND(AVG(O_TOTALPRICE),2) AS AvgPrice
FROM CUSTOMER, ORDERS, LINEITEM, PART
WHERE

 C_CUSTKEY = O_CUSTKEY AND
 O_ORDERKEY = L_ORDERKEY AND
 P_PARTKEY = L_PARTKEY

GROUP BY C_MKTSEGMENT, P_TYPE
ORDER BY C_MKTSEGMENT, P_TYPE

Aufgabe 6

Task: Berechnen Sie die auf zwei Nachkommastellen gerundete durchschnittliche Lieferdauer "ShipInterval" mittels "l_receiptdate - l_shipdate" über alle Bestellpositionen bezüglich des Marktsegments der Kunden und des Teiltyps. Sortieren Sie die Ergebnisse nach Marktsegment und Teiltyp.

SELECT C_MKTSEGMENT, P_TYPE, ROUND(AVG(L_RECEIPTDATE - L_SHIPDATE), 2) AS ShipInterval
FROM CUSTOMER, ORDERS, LINEITEM, PART
WHERE

 C_CUSTKEY = O_CUSTKEY AND
 O_ORDERKEY = L_ORDERKEY AND
 P_PARTKEY = L_PARTKEY

GROUP BY C_MKTSEGMENT, P_TYPE
ORDER BY C_MKTSEGMENT, P_TYPE

Aufgabe 7

Task: Ermitteln Sie den Namen, die Marke, den Typ und den Hersteller aller Teile, die sowohl zu europäischen (Region "Europe") als auch zu asiatischen (Region "Asia") Kunden geliefert wurden.

SELECT DISTINCT P_NAME AS NAME, P_BRAND AS BRAND, P_TYPE AS PRODUCT_TYPE, P_MFGR AS MANUFACTURER
FROM REGION, NATION, CUSTOMER, ORDERS, LINEITEM, PARTSUPP, PART
WHERE

 R_REGIONKEY = N_REGIONKEY AND
 N_NATIONKEY = C_NATIONKEY AND
 C_CUSTKEY = O_CUSTKEY AND
 O_ORDERKEY = L_ORDERKEY AND
 L_PARTKEY = PS_PARTKEY AND
 L_SUPPKEY = PS_SUPPKEY AND
 PS_PARTKEY = P_PARTKEY AND
 R_NAME = 'EUROPE' AND
 L_PARTKEY IN (
  SELECT DISTINCT L_PARTKEY
  FROM REGION, NATION, CUSTOMER, ORDERS, LINEITEM
  WHERE 
   R_NAME = 'ASIA' AND
   R_REGIONKEY = N_REGIONKEY AND
   N_NATIONKEY = C_NATIONKEY AND
   C_CUSTKEY = O_CUSTKEY AND
   O_ORDERKEY = L_ORDERKEY
 )

Aufgabe 8

Task: Ermitteln Sie den durchschnittlichen Rabatt (gerundet auf vier Nachkommastellen) aller Bestellungen bezüglich der Region der Kunden

SELECT R_NAME, ROUND(AVG(L_DISCOUNT),4) AS AVGDISCOUNT
FROM REGION, NATION, CUSTOMER, ORDERS, LINEITEM
WHERE

 R_REGIONKEY = N_REGIONKEY AND
 N_NATIONKEY = C_NATIONKEY AND
 C_CUSTKEY = O_CUSTKEY AND
 O_ORDERKEY = L_ORDERKEY

GROUP BY R_NAME

Aufgabe 9

Task: Ermitteln Sie den europäischen Lieferanten (Region "EUROPE"), der zum Umsatz des ersten Quartals 1996 den größten Anteil beigetragen hat (beachten Sie bei Ihrer Umsatzberechnung evtl. gewährte Rabatte). Listen Sie die Nummer (s_suppkey), Name (s_name), Adresse (s_address), Telefonnummer (s_phone) des Lieferanten sowie den Umsatz (total_revenue) des Lieferanten auf. Sollte dies für mehrere Lieferanten zutreffen, sortieren Sie Ihre Auswertung aufsteigend nach der Lieferantennummer. Hinweis: Mit "l_shipdate >= date '1996-01-01' and l_shipdate < date '1996-01-01' + interval '3' month" selektieren Sie alle Bestellvorgänge des ersten Quartals 1996.

SELECT * FROM (

 SELECT S_SUPPKEY, S_NAME, S_ADDRESS, S_PHONE, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS TOTAL_REVENUE
 FROM REGION, NATION, SUPPLIER, LINEITEM
 WHERE
  R_REGIONKEY = N_REGIONKEY AND
  N_NATIONKEY = S_NATIONKEY AND
  S_SUPPKEY = L_SUPPKEY AND
  R_NAME = 'EUROPE' AND
  L_SHIPDATE >= DATE '1996-01-01' AND L_SHIPDATE < DATE '1996-01-01' + INTERVAL '3' MONTH
 GROUP BY S_SUPPKEY, S_NAME, S_ADDRESS, S_PHONE
 ORDER BY TOTAL_REVENUE DESC, S_SUPPKEY

) WHERE rownum <= 1

Aufgabe 10

Task: Ermitteln Sie den Gesamtumsatz "revenue", der für drei bestimmte Marken (p_brand) unter Berücksichtigung von Rabatten erzielt wurde. Es sollen nur die Bestellpositionen berücksichtigt werden, die per Luftfracht (l_shipmode IN ('AIR', 'AIR REG')), persönlich (l_shipinstruct = "DELIVER IN PERSON") und in bestimmten Verpackungen (p_container) ausgeliefert wurden. Zudem sollte die Größe der Produkte in einem bestimmten Intervall liegen und die Bestellmenge (l_quantity) eine bestimmte Höhe nicht übersteigen. Die Größenintervalle, Verpackungsarten und Mengenbeschränkungen für die jeweiligen Sorten sind der folgenden Tabelle zu entnehmen. Ihre Ergebnisrelation soll nur das Attribut "revenue" enthalten.

Marke Größe Packungstyp, -sorte Anzahl
Brand#15 1-15 'SM JAR', 'SM BOX', 'SM PACK', 'SM PKG' <= 40
Brand#51 30-50 'MED BAG', 'MED JAR', 'MED CASE', 'MED PKG' <=30
Brand#41 1-5 'LG BAG', 'LG BOX', 'LG PACK', 'LG PKG' <= 10

SELECT SUM(l_extendedprice * (1-l_discount)) AS Revenue
FROM lineitem, part
WHERE
(p_partkey = l_partkey
AND p_brand = 'Brand#15'
AND p_size BETWEEN 1 AND 15
AND p_container IN ('SM JAR', 'SM BOX', 'SM PACK', 'SM PKG')
AND l_quantity <= 40
AND l_shipmode IN ('AIR', 'AIR REG')
AND l_shipinstruct = 'DELIVER IN PERSON'
)OR
(p_partkey = l_partkey
AND p_brand = 'Brand#51'
AND p_size BETWEEN 30 AND 50
AND p_container IN ('MED BAG', 'MED JAR', 'MED CASE', 'MED PKG')
AND l_quantity <= 30
AND l_shipmode IN ('AIR', 'AIR REG')
AND l_shipinstruct = 'DELIVER IN PERSON'
)

OR(p_partkey = l_partkey
AND p_brand = 'Brand#41'
AND p_size BETWEEN 1 AND 5
AND p_container IN ('LG BAG', 'LG BOX', 'LG PACK', 'LG PKG')
AND l_quantity <= 10
AND l_shipmode IN ('AIR', 'AIR REG')
AND l_shipinstruct = 'DELIVER IN PERSON'
)

Aufgabe 11

Task:Ermitteln Sie den durch eine bestimmte Produktlinie erzielten Gesamtgewinn "sum_profit" aufgeschlüsselt nach Zuliefererland (n_nation) und Jahr (o_year) der Bestellung und weisen Sie ihr Ergebnis pro Nation in aufsteigender Reihenfolge und pro Jahr in absteigender Reihenfolge aus. Die zu untersuchende Produktlinie besteht aus allen Teilen, die den Teilstring "green" in ihrem Namen enthalten. Beachten Sie bei Ihrer Umsatzberechnung evtl. gewährte Nachlässe und geben Sie nur die ersten 20 Datensätze aus. Die Ergebnisrelation soll demnach folgende Attribute beinhalten: n_nation, o_year, sum_profit. Hinweise: Verwenden Sie den "like" Operator, um bzgl. eines Teilstrings "str" zu selektieren (p_name like '%str%'). Mit extract(year from o_orderdate) können Sie das Attribut o_year erzeugen. Zum Ausgeben der ersten 20 Datensätze betten Sie die eigentliche Abfrage X in "SELECT * FROM (SELECT X) WHERE ROWNUM < 21" ein. Berechnen Sie sum_profit als "Gewinn = Umsatz unter Berücksichtigung von Rabatten - Kosten".

SELECT *
FROM
(SELECT nation, o_year, sum(Profit) AS Sum_profit
FROM
(SELECT n_name AS nation, EXTRACT(YEAR FROM o_orderdate) AS o_year,
(l_extendedprice * (1-l_discount) - ps_supplycost * l_quantity) AS Profit
FROM part, supplier, lineitem, partsupp, orders, nation
WHERE s_suppkey = l_suppkey
AND ps_suppkey = l_suppkey
AND ps_partkey = l_partkey
AND p_partkey = l_partkey
AND o_orderkey = l_orderkey
AND s_nationkey = n_nationkey
AND p_name LIKE '%green%'
)GROUP BY nation, o_year
ORDER BY nation, o_year DESC
)

WHERE rownum < 21;