Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Datenbank - Anfragen

Dieses Thema im Forum "Oracle" wurde erstellt von eroveli65, 20 April 2012.

  1. eroveli65

    eroveli65 Benutzer

    Hi,

    ich sitze schon den ganzen Abend dran aber komm irgendiwe nicht zum Punkt.

    Sondern will ich eine Datenbank Anfrage starten:

    Ich will wissen wie die Lieferanten heißen, die die schwersten Teile liefern?

    Vorhandene Tabellen: (Spalten habe ich nur mal ins deutsche übersetzt)

    supplier : lieferant, lieferantennr., stadt
    part: teilenr, teilename, gewicht, stadt
    supp_part: teilenr, lieferantennr, menge

    hierzu habe ich als erstes das hier geschrieben:

    select a.suppname as "Lieferant",max(k.weight) as "Schwerstes Teil"
    from company.supplier a,company.part k
    group by a.suppname

    ausgabe: alle lieferanten und alle mit dem max gewicht , daher schonmal FALSCH

    dann ist mir aber bewusst geworden, dass hierzu auch die spalten lieferantennr und teilenr benutzen muss, damit das maximal gewicht auch dem lieferanten zugeordnet werden kann

    sozusagen: Lieferanten => Lieferrantennr => Teilenr. => Gewicht

    dann hab ich folgendes geschrieben:

    select a.suppname as "Lieferant"
    from company.supplier a
    where a.suppno in
    (select a1.suppno
    from COMPANY.supp_part a1
    where a1.partno in
    (select a2.partno
    from COMPANY.part a2
    where max(a2.weight) as "Max Gewicht));

    Ausgabe: error

    Ich weiß auch nicht weiter, abgesehen davon das ich purer anfänger bin, zerbricht das mir den Kopf und kann nicht davon loslassen...

    Ich würde mich freuen, wenn jmd mir paar tipps ggf beispiele geben kann auch wenn es für euch ein kinderspielchen ist.

    Vielen Dank
     
  2. ukulele

    ukulele Datenbank-Guru

    Da gibt es viele Wege, da du kein konkretes Gewicht von bis geschrieben hast, würde ich einfach mal eine Übersicht machen. Die AND p.gewicht >= Bedingung ist nur optional:
    Code:
    SELECT    s.lieferantennr,
            s.lieferant,
            p.teilenr,
            p.teilname,
            p.gewicht
    FROM    supplier s,
            supp_part z,
            part p
    WHERE    s.lieferantennr = z.lieferantennr
    AND        z.teilenr = p.teilenr
    AND        p.gewicht >= 100
    ORDER BY p.gewicht,s.lieferant
    Alternativ geht auch:
    Code:
    SELECT    s.lieferantennr,
            s.lieferant,
            p.teilenr,
            p.teilname,
            p.gewicht
    FROM    supplier s,
            supp_part z,
            part p
    WHERE    s.lieferantennr = z.lieferantennr
    AND        z.teilenr = p.teilenr
    AND        p.gewicht = (    SELECT    max(gewicht)
                            FROM    part )
    ORDER BY p.gewicht,s.lieferant
     
  3. eroveli65

    eroveli65 Benutzer

    Super !!

    genau so habe ich es mir vorgestellt mit mein Überlegung, das sieht schön sauber aus, das hat mir bei einigen anderen Übungen geholfen und hab was dazu gelernt ;-)

    jetzt hänge ich wieder an einer Stelle fest

    hab jetzt folgenden code erstellt:

    Code:
    select p.partname,
          s.suppname,
          z.quantity   
    from  company.supplier s,
          company.supp_part z,
          company.part p         
    where  s.suppno=z.suppno
    and    z.partno=p.partno
    order by p.partname,
            s.suppname    
    dieser code wiedergibt die Tabelle so wie ich es haben will

    Aber jetzt will ich eine extra Zeile unter der Tabelle, die die Summe der Menge(Quantity)ausgibt die wie folgt aussehen soll: Summe: 31000

    also um die Summe zuberechnen ist mir denke ich mal klar glaube ich: sum(menge)

    Kann man dies auch sortieren, dass "Summe:" unter Partname steht und die Summe aus der Menge unter der Menge(Quantity) steht, mit einer Zeile Abstand zur Tabelle

    Hat da vllt jemand ne idee wie man dies elegant hinkriegen würde. Ich wollte dies durch eine einzige Anfrage hinkriegen
     
  4. ukulele

    ukulele Datenbank-Guru

    Ich wüsste da schon einen Weg, aber kann dir nicht sagen ob das der eleganteste ist (eigentlich würde ich eher sagen ein bischen gefrickel, aber sollte laufen):
    Code:
    SELECT    *
    FROM    (    SELECT    p.partname,
                        s.suppname,
                        z.quantity
                FROM    company.supplier s,
                        company.supp_part z,
                        company.part p
                WHERE    s.suppno = z.suppno
                AND        z.partno = p.partno
                UNION ALL
                SELECT    'zzz Summe' AS partname,
                        '' AS suppname,
                        (    SELECT    sum(z.quantity)
                            FROM    company.supplier s,
                                    company.supp_part z,
                                    company.part p
                            WHERE    s.suppno = z.suppno
                            AND        z.partno = p.partno ) AS quantity ) tabelle
    ORDER BY partname, suppname  
    Ist natürlich etwas unkomfortabel aber sollte gehen. Testen kannst du mal:
    Code:
    SELECT    *
    FROM    (    SELECT    p.partname,
                        s.suppname,
                        z.quantity
                FROM    company.supplier s,
                        company.supp_part z,
                        company.part p
                WHERE    s.suppno = z.suppno
                AND        z.partno = p.partno
                UNION ALL
                SELECT    'zzz Summe' AS partname,
                        '' AS suppname,
                        (    SELECT    sum(z.quantity)
                            FROM    tabelle ) ) tabelle
    ORDER BY partname, suppname  
    Geht aber glaube ich nicht. Eine komulierte Summenspalte habe ich noch als Beispiel gefunden :
    Code:
    SELECT
     
      a.wert, (SELECT SUM(b.wert) FROM SummeTest b WHERE b.recid <= a.recid) AS 'kumulative Summe'
     
    FROM
     
      SummeTest a
    Allerdings musst du natürlich ein passendes WHERE Kriterium haben. Im Idealfall ordnest du also nicht nach Namen sondern hast irgendeine ID nach der sortiert werden kann. Ich habs mal versucht mit ROW_NUMBER() aber ich weiss nicht ob es die Funktion bei Oracle gibt und funktionieren tut das auch bei mir nicht so richtig:
    Code:
    SELECT    ROW_NUMBER() OVER (ORDER BY p1.partname, s1.suppname ) AS zeilennr,
            p1.partname,
            s1.suppname,
            z1.quantity,
            (    SELECT    sum(z2.quantity)
                FROM    company.supplier s2,
                        company.supp_part z2,
                        company.part p2
                WHERE    s2.suppno = z2.suppno
                AND        z2.partno = p2.partno
                AND        ROW_NUMBER() OVER (ORDER BY p2.partname, s2.suppname ) <= zeilennr ) AS [kumulative Summe]
    FROM    company.supplier s1,
            company.supp_part z1,
            company.part p1
    WHERE    s1.suppno = z1.suppno
    AND        z1.partno = p1.partno
    ORDER BY p1.partname, s1.suppname 
     
  5. eroveli65

    eroveli65 Benutzer

    musste den code leicht abändern, da ein fehler auftrat

    Code:
    SELECT    *
    FROM    (  SELECT  p.partname,
                        s.suppname,
                        z.quantity
                FROM    supplier s,
                        supp_part z,
                        part p
                WHERE  s.suppno = z.suppno
                AND    z.partno = p.partno
                UNION ALL
                select 'Summe:',' ',sum(quantity)
                from supp_part)
    order by p.partname,s.suppname;
    aber ich habe vergessen das ich zwischen der summe und der tabelle eine leere zeile haben wollte dafür hab ich den code erweitert:

    Code:
    SELECT    *
    FROM    (  SELECT  p.partname,
                        s.suppname,
                        z.quantity
                FROM    supplier s,
                        supp_part z,
                        part p
                WHERE  s.suppno = z.suppno
                AND    z.partno = p.partno
                UNION ALL
                select ' ',' ',NULL
                from dual
                union all
                select 'Summe:',' ',sum(quantity)
                from supp_part)
    order by p.partname,s.suppname;
    Habe für die leere zeile den dual dummy benutzt, weiß nicht ob es noch eine andere methode dafür gibts...aber da hab ich jetzt das problem das durch den order by die leere zeile an erste stelle in der tabelle vorkommt und in der leeren zeile kriege ich in der 3 spalte die ' NULL ' nicht weg kann es nur durch eine Zahl ersetzen wie krieg ich das alles hin??
     
  6. ukulele

    ukulele Datenbank-Guru

    Nunja, du kannst dort eine 0 statt NULL angeben, aber du versuchst hier mehr oder weniger Layout spezifische Dinge über die Abfrage zu regeln, was eigentlich nicht so gedacht ist. Eine andere Möglichkeit ausser NULL oder 0 anzuzeigen sehe ich nicht, es sei denn du machst ein cast(z.quantity) AS VARCHAR(10) und hast somit eine VARCHAR() Spalte, dann geht aber eventuell dein sum() nicht mehr. Das Layout sollte man eventuell dann doch einem Frontend für die DB überlassen.
     
  7. eroveli65

    eroveli65 Benutzer

    und wie kriege ich es hin, das die leere Zeile nicht an erster Stelle landet wenn ich order by benutze??

    siehe code:
    Code:
    SELECT    *
    FROM    (  SELECT  p.partname,
                        s.suppname,
                        z.quantity
                FROM    supplier s,
                        supp_part z,
                        part p
                WHERE  s.suppno = z.suppno
                AND    z.partno = p.partno
                UNION ALL
                select ' ',' ',NULL
                from dual
     
                UNION ALL
                select 'Summe:',' ',sum(quantity)
                from supp_part)
    order by p.partname,s.suppname;
    kann man die Stelle
    Code:
    UNION ALL
    select ' ',' ',NULL
    from dual
    auch als Unterabfrage benutzen? oder wie soll man das hinkriegen?
     
  8. ukulele

    ukulele Datenbank-Guru

    Eigentlich müsste in diesem Fall auch die Summen Spalte irgendwo anders stehen, denn du hast ja das "zzz" bei mir raus genommen. Man könnte aber noch etwas tricksen:
    Code:
    -- Den eigentlichen Spaltennamen kannst du über AS nochmal nach belieben
    -- anpassen. Mit einer CASE Schleife nehme ich die zzz wieder raus, die
    -- ich zur Sortierung nutze.
    SELECT    (    CASE
                WHEN    partname LIKE 'zzz%'
                THEN    right(partname,len(partname)-3)
                ELSE    partname
                END ) AS Artikel,
            suppname AS Lieferant,
            quantity AS Menge
    FROM (    SELECT    p1.partname,
                    s1.suppname,
                    z1.quantity
            FROM    supplier s1,
                    supp_part z1,
                    part p1
            WHERE    s1.suppno = z1.suppno
            AND        z1.partno = p1.partno
            UNION ALL
            -- Du musst den Werten mit AS einen Spaltennamen geben, sonst sind
            -- deine Spalten namenlos. Da wir aber im äußeren SELECT kein * mehr
            -- verwenden sondern Spaltennamen, müssen diese auch vorhanden sein.
            SELECT    'zzz' AS partname,
                    '' AS suppname,
                    0 AS quantity
            UNION ALL
            SELECT    'zzzSumme:' AS partname,
                    '' AS suppname,
                    sum(z2.quantity) AS quantity
            -- An dieser Stelle hattest du nur eine Tabelle ausgewählt um die
            -- Summe aus quantity zu bilden. Das kann aber dazu führen das deine
            -- Summe nicht mit der Ausgabe übereinstimmt wenn z.B. In supp_part
            -- Einträge existieren, die keinen Eintrag in supplier oder part haben.
            -- Auch wenn das eigentlich nicht sein sollte muss man das im Hinterkopf
            -- behalten und auch bei weiteren Bedingungen zur Auswahl von Daten
            -- kann das schnell Verwirrung auslösen. Daher nehme ich das vorsichts-
            -- halber mit rein.
            FROM    supplier s2,
                    supp_part z2,
                    part p2
            WHERE    s2.suppno = z2.suppno
            AND        z2.partno = p2.partno )
    -- Die ORDER BY Bedingung bezieht sich auf die Spalten, daher müsste zzz bei
    -- der Sortierung berücksichtigt werden.
    ORDER BY partname,suppname
     
  9. eroveli65

    eroveli65 Benutzer

    Danke..

    habe es hingekriegt :)

    von dir lerne ich mehr als von meiner Dozenten, und ich weiß es sehr zu schätzen das du dir zeit nimmst mir zu helfen ;-) das macht nicht jeder Danke ;-)

    Jetzt bin ich grad dabei eine weitere Aufgabe zu machen, wo ich noch nichts gemacht habe, sondern

    Will ich de Firmensitzes des Lieferanten (Suppname) „Smith“ nach Berlin(City) und auch die Herstellungsorte(Part --> City) seiner Produkte nach Berlin verlagern. Diejenigen Produkte, die nicht nur von ihm geliefert werden, sollen davon nicht betroffen sein.

    Tabelle:
    [​IMG]

    Das heisst, ich muss in der Tabelle " Supplier die Spalte "City" ändern, und in der Tabelle (Part) die Spalte (City) ändern aber da hab ich das Problem das auch andere Firmen das selbe Produkt liefern, das heisst ich muss neue Zeilen entwerfen, aber dann ändert sich die (Partno) womit ich dann auch die Tabelle (Supp_Part) ändern muss.

    Am besten wäre es beim update-statement eine Unterabfrage zu benutzen.

    Dafür habe ich ein Code geschrieben der nicht elegant ist:

    Code:
    update supplier
    set city = 'Berlin'
    where suppname = 'Smith'
    and suppno = 'S1';
     
    update supp_part
    set partno = 'P7'
    where suppno= 'S1'
    and partno = 'P1';
    update supp_part
    set partno = 'P8'
    where suppno= 'S1'
    and partno = 'P2';
    update supp_part
    set partno = 'P9'
    where suppno = 'S1'
    and partno ='P3';
    update supp_part
    set partno = 'P10'
    where suppno = 'S1'
    and partno = 'P4';
    update supp_part
    set partno = 'P11'
    where suppno= 'S1'
    and partno='P5';
    update supp_part
    set partno ='P12'
    where suppno ='S1'
    and partno = 'P6';
     
    insert into part (partno,partname,color,weight,city)
    values ('P7','Nut','Red','12','Berlin');
    insert into part (partno,partname,color,weight,city)
    values ('P8','Bolt','Green','17','Berlin');
    insert into part (partno,partname,color,weight,city)
    values ('P9','Screw','Blue','17','Berlin');
    insert into part (partno,partname,color,weight,city)
    values ('P10','Screw','Red','14','Berlin');
    insert into part (partno,partname,color,weight,city)
    values ('P11','Cam','Blue','12','Berlin');
    insert into part (partno,partname,color,weight,city)
    values ('P12','Cog','Red','19','Berlin');
    
    aber es funzt nicht ganz.

    - Firmensitz wird geändert = ok
    - aber der Rest nicht
    --> in der Tabelle Part fügt er (P7,P8,P9) ein aber wiederholt es einmal und fügt nicht (P10,P11,P12) dafür ein.
    --> in der Tabelle Supp_part ändert er alle (Partno) die zur (Suppno) der Firma gehören nur in (P8) ab

    wie kann ich die beiden Fehler beheben und wie kann ich beim update statement eine Unterabfrage einbauen, was denke ich sinnvoller wäre?
     
  10. ukulele

    ukulele Datenbank-Guru

    Also das mit dem INSERT muss funktionieren. Du hast eventuell etwas doppelt ausgeführt oder nur teilweise markiert so das nur der markierte Teil ausgeführt wurde. Auch das UPDATE der Partnummern muss gehen.
     
  11. eroveli65

    eroveli65 Benutzer

    Also der Code an sich stimmt meinst du?? gut kann sein das ich beim makrieren und asführen was falsch gemacht habe

    Wie sieht es aus mit einer unterabfrage.. wie baut man sowas in ein update-statement ein im bsp der Änderung der Partno?
     
  12. ukulele

    ukulele Datenbank-Guru

    Da stellt sich jetzt eher die Frage stimmt das Datenmodell. Wenn ein Part (eigentlich gleiche Nummer) an unterschiedlichen Standorten produziert wird muss der Standort eigentlich in eine eigene Tabelle ausgelagert werden. Stichwort Relationales Datenbank http://de.wikipedia.org/wiki/Relationale_Datenbank

    Natürlich kann man das auch so machen wie du es vor hattest und damit leben das dieser Grundsatz in einzelfällen durchbrochen wird. Dann muss man sich aber Fragen ob man für sowas wirklich ein automatisiertes Script braucht. Das geht, ist aber nen bischen Arbeit.
     
  13. eroveli65

    eroveli65 Benutzer

    Habe es hingekriegt :)

    habe jetzt noch ein letztes problem...

    habe diesen code erstellt:

    Code:
    select a.artbez,
            a.artnr,
            b.menge,
            b.preis,
            (select nvl2(sum(b.menge), 'bereits bestellt', 'Ladenhüter')
            from aufpos b where b.artnr = a.artnr) as Status
    from  artst a left join aufpos b on b.artnr = a.artnr
    order by a.artbez;
     
     
    
    jetzt will ich nur noch die werte die als NULL angezeigt werden in den Spalten Preis und Menge durch 0 ersetzen
     
  14. ukulele

    ukulele Datenbank-Guru

    Code:
    select a.artbez,
            a.artnr,
            isnull(b.menge,0),
            isnull(b.preis,0),
            (select nvl2(sum(b.menge), 'bereits bestellt', 'Ladenhüter')
            from aufpos b where b.artnr = a.artnr) as Status
    from  artst a left join aufpos b on b.artnr = a.artnr
    order by a.artbez;
    So wäre es bei MSSQL, gibt sicher isnull() oder etwas ähnliches auch bei Oracle.
     
    PLSQL_SQL gefällt das.
  15. PLSQL_SQL

    PLSQL_SQL Datenbank-Guru

    Bei Oracle heißt der Befehl nvl() !! "No VaLue"

    z.B.: nvl(b.preis, 0)

    Lg
     
    ukulele gefällt das.
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden