Anwendung von Subqueries

[Mak]

Guten Morgen,

ich sitze nun seit einer Woche an folgenden Aufgaben (siehe Worddatei). Und komme bei manchen Aufgaben nicht weiter.
Es soll bitte nicht den Eindruck erwecken, dass ich das Forum zum lösen meiner Hausarbeiten missbrauchen möchte. Ich kann versichern, dass ich zahlreiche stunden in Access verbracht habe, um selbst mir die Lösung zu erarbeiten.
Aber bei diesem Set komme ich mit meinen Fähigkeiten aus dem dreiwöchigen Uni-Crashkurs nicht so recht weiter.
Vieleicht hat ja jemand die Motivation, Zeit, etc. mir weiterzuhelfen, denn die deadline ist Freitag früh und ansonsten müsste ich wohl nur die ersten drei aufgaben abgeben.
Ich würde mich bezüglich Unterstützung sehr freuen.

Viele Grüße aus Schweden
Mak


p.s. falls jemand die datei nicht öffnen möchte


Using subqueries –Instead of multi-table queries- from the Staff, Branch and PropertyForRent relations implementing the following queries in SQL
Anmerkung: Möglichst einfache SQL codes verwenden.

1) List the number of properties offered for rent at each branch office along with their total annual rent. Arrange the list in alphabetical order of the branch office codes.
Do not list branches that do not offer any property.

Mein Code:

Select branchNo,
                            (Select Count(*)
                             From PropertyForRent
                             Where branchNo=Branch.branchNo) As [blaba],

                              (Select rent*12 As [dddd]
                              From PropertyForRent
                              Where branchNo=Branch.branchNo) As [ghgh]
From Branch
Where branchNo In (Select branchNo
                                     From PropertyForRent)
Order by branchNo;

Problem:
2) Create the same list as in Problem 1, but now also list the branch offices that do not offer any property for rent.                                                                                                        

Mein Code:

Select branchNo,
                             (Select Count(*)
                             From PropertyForRent
                             Where branchNo=Branch.branchNo) As [number of properties at each branch],
                             (Select 'rent*12'
                              From PropertyForRent
                              Where branchNo=Branch.branchNo) As [Annual Rent]
From Branch
Order by branchNo;

Problem 3) List all cities where there is a branch office with no employee.

Mein Code:

SELECT city
             From Branch
             where (select branchNo
                           from staff
             group by branchNo Having count(*)<1);

Problem 4) Create an alphabetically ordered list of cities where the company has only non-operational  (empty) branch offices (but not operational ones with employees) ??

5)Create a list of cities (without repeating any city) where there is a branch office and a property for rent.
Ergebnis sollte sein: Aberdeen, Glasgow, London

6) List all cities where there is a branch office that offers a property for rent in the same city
Ergebnis sollte sein:  Aberdeen, Glasgow, London

7) Produce a list of all staff who manage some property with their branch office codes.                    For each such employee also present the number of properties he or she manages as well as the amount of monthly rent on those properties. ??

Create a similar list to the result of Problem 7, but now order the rows in descending order according to the total monthly rent per property managed by employee.


[Anhang gelöscht durch Administrator]

[oma]

Hallo Mak,

wie wäre es mit einer exakten deutschen Beschreibung der Aufgaben?

Gruß Oma

[Mak]

ich habe nun die Aufgabenstellungen uebersetzt:
danke fuer zukuenftiger hilfe

[Anhang gelöscht durch Administrator]

[oma]

Hallo Mak,

anbei die ersten 4 Aufgaben, andere folgen evt. heute abend:

1.alle Branch mit Anzahl Eigentümer u. jährliche Miete:

Code [Auswählen] Erweitern

SELECT b.branchNo, (select count(*) from PropertyForRent as a Where a.branchNo=b.branchNo) AS anz, (select sum(rent) from PropertyForRent as a Where a.branchNo=b.BranchNo) AS RentYear
FROM branch AS b
WHERE ((((select count(*) from PropertyForRent as a Where a.branchNo=b.branchNo))<>0))
ORDER BY b.branchNo;

2. Gleiches Ergebis, aber alle Branch (ohne Properties):

Code [Auswählen] Erweitern

SELECT b.branchNo, (select count(*) from PropertyForRent as a Where a.branchNo=b.branchNo) AS anz, NZ((select sum(rent) from PropertyForRent as a Where a.branchNo=b.BranchNo),0) AS RentYear
FROM branch AS b
ORDER BY b.branchNo;

3. Alle Städte mit Office aber keine Mitarbeiter:

Code [Auswählen] Erweitern

SELECT b.city, (select count(*) from staff  as a Where a.branchNo=b.branchNo) AS Staff
FROM branch AS b
WHERE ((((select count(*) from staff  as a Where a.branchNo=b.branchNo))=0));

4. Alle Städte mit Office und Mietgebäude:

Code [Auswählen] Erweitern

SELECT b.city, (select count(*) from PropertyForrent  as a Where a.branchNo=b.branchNo) AS Rent
FROM branch AS b
WHERE ((((select count(*) from PropertyForrent  as a Where a.branchNo=b.branchNo))<>0));

alle queries getestet!

falls du die Lösungen als Code-Text abgeben musst, kannst du zahlreiche Klammern in den Access-Abfragen löschen!

Gruß Oma

[Hondo]

Hallo Oma
da hast du dir aber ein Fleissternchen für diese Antwort verdient

[database]

Hallo,

also - alle Achtung 

alle queries getestet

Was ist das für eine Musterdatenbank? Die kenn' ich noch gar nicht.

[oma]

Hallo,

@Peter: die "Musterdatenbank" war im Anhang von Mak, habe hiermit die Abfragen getestet.

@Andreas: ehrlich gesagt, ist das ja immer das gleiche, in den subqueries müdssen immer nur die Objekte umbenannt werden.

@Mak: anbei die restlichen:

5: Alle städte , die ein Office haben u. auch Vermietungen für die gleiche Stadt anbieten:

Code [Auswählen] Erweitern

SELECT b.city, (select count(*) from PropertyForrent  as a Where a.city=b.city) AS CityRent
FROM branch AS b
WHERE ((((select count(*) from PropertyForrent  as a Where a.city=b.city))<>0));


6a: Mitarbeiter mit Anzahl der betreuten Objekte und Summe der Miete

Code [Auswählen] Erweitern

SELECT b.staffNo, b.fName, b.iName, (select count(*) from PropertyForrent  as a Where a.staffNo=b.StaffNo) AS Properties, (select sum(rent) from PropertyForrent  as a Where a.staffNo=b.StaffNo) AS SummeRent
FROM staff AS b
WHERE ((((select count(*) from PropertyForrent  as a Where a.staffNo=b.StaffNo))<>0));

6b: Gleiches wie in 6b nur nach Miete absteigend sortiert (gemiene Aufgabenstellung, da 6a nicht nach SummeRent sortierbar ist)

Code [Auswählen] Erweitern

SELECT b.StaffNo, Sum(b.rent) AS SummeRent, (select first(fName) from staff as a where a.staffNo=b.StaffNo) AS fName, (select first(iName) from staff as a where a.staffNo=b.StaffNo) AS iName
FROM PropertyForRent AS b
GROUP BY b.StaffNo
ORDER BY Sum(b.rent) DESC;


Ich hoffe nur, dass die Lösungen genau den Aufgabenstellungen entspricht.
Wenn nicht, einfach noch einmal melden.

Ansonsten: du musst bedenken, dass alle Abfragen praktisch wohl anders gelöst werden. Man wird meistens mit Verknüpfungen zwischen den Tabellen arbeiten. Dies war ja aber ausdrücklich nicht gestattet u. so hast du nun genügend Beispiele mit subqueries!

Anbei die Lösungen in DB


Gruß Oma

[Anhang gelöscht durch Administrator]

[Mak]

Hallo,

also - alle Achtung 

dem ist wirklich nichts hinzuzufügen!!
Oma, vielen Dank für deine Hilfe. Durch deine codes sind mir subqueries viel plausibler geworden.

Dennoch möchte ich noch ein paar Anmerkungen/Fragen stellen.

Aufgabenstellung:
Create an alphabetically ordered list of cities where the company has only non-operational  (empty) branch offices (but not operational ones with employees)
Es soll eine alphabetische Liste von Städten erstell werden, in denen "leere" Branch offices ansässig sind (auch keine MA haben)

Mein Versuch:

Code [Auswählen] Erweitern

SELECT Branch.city
FROM branch
WHERE ((((select count(*)
                  from staff
                  Where staff.branchNo=branch.branchNo And staff.branchNo=PropertyForRent.branchNo))=0))
ORDER BY Branch.city;

________________

5: Alle städte , die ein Office haben u. auch Vermietungen für die gleiche Stadt anbieten:

In Access wird folgendes angezeigt:

city
London
Glasgow
London
Aberdeen

somit wird auch London mit der BranchNo B002 (allerste) aufgeführt.
Ich kann mir aber nicht erklären was an oma's code falsch sein soll. Es müsste ja Glasgów, London und Aberdeen rauskommen

Code [Auswählen] Erweitern

SELECT branch.city
           
FROM branch

WHERE ((((select count(*)
                  from PropertyForRent
                  Where PropertyForRent.city=branch.city))>0));

__________________

6b: Gleiches wie in 6b nur nach Miete absteigend sortiert (gemiene Aufgabenstellung, da 6a nicht nach SummeRent sortierbar ist)

Code [Auswählen] Erweitern

SELECT PropertyForRent.StaffNo,
             (select first(fName)
              from staff     
              where staff.staffNo=PropertyForRent.StaffNo) AS fName,
         
             (select first(iName)
              from staff
              where staff.staffNo=PropertyForRent.StaffNo) AS iName,
Sum(PropertyForRent.rent) AS SumRent

FROM PropertyForRent

GROUP BY PropertyForRent.StaffNo

ORDER BY Sum(PropertyForRent.rent) DESC;


Wie kann ich hier noch BranchNo einfügen (denn wenn ich PropertyForRent.branchNo einfüge, kommt eine Fehlermeldung)
und ist wäre es möglich die Namen der MA in einer Zelle zusammen zufügen.

[database]

Hallo,

dann wollen wir mal schauen ob der alte database OMA ein wenig entlasten kann 


zur Aufgabenstellung:
Create an alphabetically ordered list of cities where the company has only non-operational  (empty) branch offices (but not operational ones with employees)
Es soll eine alphabetische Liste von Städten erstell werden, in denen "leere" Branch offices ansässig sind (auch keine MA haben)

Code [Auswählen] Erweitern


SELECT branch.city
FROM branch
WHERE branch.id Not In (SELECT Distinct branch.ID
FROM branch INNER JOIN staff ON branch.branchNo = staff.branchNo)
ORDER BY branch.city;


Die Aufgabestellung kann man SO interpretieren:
Erzeuge eine Liste jener Büros, die nicht in der Liste von Büros mit Angestellten enthalten sind.

Ergebnis:  Bristol, London
-------------------------------------------------------------------------------------------------------------
zu 5.
Kein wirklicher Fehler in OMA's Code, füge einfach ein DISTINCT ein um Duplikate auszuschließen:

Code [Auswählen] Erweitern


SELECT DISTINCT branch.city
FROM branch
WHERE ((((select count(*)
                  from PropertyForRent
                  Where PropertyForRent.city=branch.city))>0));


-------------------------------------------------------------------------------------------------------------
zu 6b.

Code [Auswählen] Erweitern


SELECT b.StaffNo, Sum(b.rent) AS SummeRent,
(select first(iName) from staff as a where a.staffNo=b.StaffNo) & " " & (select first(fName) from staff as a where a.staffNo=b.StaffNo) AS SName, b.branchNo
FROM PropertyForRent AS b
GROUP BY b.StaffNo, b.branchNo
ORDER BY Sum(b.rent) DESC;


Vornamen und Nachnamen einfach zusammensetzen wie Textteile,
jedes weitere hinzuzufügende Feld (b.branchNo) muss mit in die Gruppierung sonst setzt es einen Fehler

HTH

Peter

[oma]

Hallo,

Danke für die Entlastung der alten OMA

1. für 5. fehlt tatsächlich das DISTINCT !

2. Peter, ich denke, dein Code liefert nicht das richtige Ergebnis für 6b.
Die Zusammenführung der Vor- und nachnamen ist natürlich richtig, aber für die branchNo musst es heissen:
First(b.branchNo) AS BranchNo 

Zusammen:

Code [Auswählen] Erweitern

SELECT b.StaffNo, Sum(b.rent) AS SummeRent,
(select first(fName) from staff as a where a.staffNo=b.StaffNo) & "
" & (select first(iName) from staff as a where a.staffNo=b.StaffNo) AS Namen,
First(b.branchNo) AS BranchNo
FROM PropertyForRent AS b
GROUP BY b.StaffNo
ORDER BY Sum(b.rent) DESC;


Gruß Oma

[database]

UUUPPPSSS,  (alte OMA stammt aber NICHT von mir - gelle)


....was ja auch klar ist, da sonst die Summierung nicht richtig abgearbeitet wird ....    

sorry

Peter

[Mak]

danke für die, für mich, sehr konstruktive konversation.

fage: ist hier distinct die einzige möglichkeit?
         

SELECT DISTINCT branch.cityFROM branchWHERE ((((select count(*)                   from PropertyForRent                  Where PropertyForRent.city=branch.city))>0));

dh. man kann es nicht so programmieren, dass automatisch die Branch002 = London als diejenige Branch herausgeworfen wird, die keine MA enthält?

______________________

danke database für den code.
aber ich glaube es handelt sich hier um multi table abfagen?

Code [Auswählen] Erweitern

SELECT branch.city
FROM branch
WHERE branch.id Not In (SELECT Distinct branch.ID
FROM branch INNER JOIN staff ON branch.branchNo = staff.branchNo)
ORDER BY branch.city;


aber wie schreibe ich es nur mit subqueries??

danke
gruss
mak

[oma]

Hallo,

du hast Recht; für 5. gibt es auch eine andere (bessere) Lösung:

Code [Auswählen] Erweitern

SELECT b.city, (select count(*) from PropertyForrent  as a
Where a.city=b.city) AS CityRent
FROM branch AS b
GROUP BY b.city
HAVING ((((select count(*) from PropertyForrent  as a Where a.city=b.city))<>0));

das mit den "multi-table queries" in Peters Lösung stimmt; wenn du so etwas nicht benutzen sollst, musst du eben meine Lösung 3 benutzen, dort fehlte nur die Sortierung der Städte:

Code [Auswählen] Erweitern

SELECT b.city, (select count(*) from staff  as a
Where a.branchNo=b.branchNo) AS Staff
FROM branch AS b
WHERE ((((select count(*) from staff  as a Where a.branchNo=b.branchNo))=0))
ORDER BY b.city;

Gruß Oma

[Mak]

musst du eben meine Lösung 3 benutzen, dort fehlte nur die Sortierung der Städte:

Code: In Zwischenablage kopierenSELECT b.city, (select count(*) from staff  as a Where a.branchNo=b.branchNo) AS StaffFROM branch AS bWHERE ((((select count(*) from staff  as a Where a.branchNo=b.branchNo))=0))ORDER BY b.city;
   

gilt dann dieser code für Aufgabe 3 und 4: ?

3) Auflistung aller Städte, die eine Branch Office haben, diese Branch Office haben aber keine Mitarbeiter
    (List all cities where there is a branch office with no employee.)

4) Create an alphabetically ordered list of cities where the company has only non-operational  (empty) branch offices (but not operational ones with employees)

Die Aufgabestellung kann man SO interpretieren:
Erzeuge eine Liste jener Büros, die nicht in der Liste von Büros mit Angestellten enthalten sind.

habe ich es so richtig Verstanden, dass ich deinen Code, oma, sowohl für 3 als auch für 4 nehmen kann, nur mit dem unterschied, dass bei 4 ein Order by branch.city hinzugefügt wird?

gruss

mak

[oma]

Hallo, alter Schwede

was studierst du überhaupt in Schweden?
was haben denn nun deine DB-Test für Noten ergeben?


Aufgabe 3: Alle Städte mit Office aber keine Mitarbeiter:

Code [Auswählen] Erweitern

SELECT b.city, (select count(*) from staff  as a Where a.branchNo=b.branchNo) AS Staff
FROM branch AS b
WHERE ((((select count(*) from staff  as a Where a.branchNo=b.branchNo))=0))
ORDER BY b.city;

Es wird nach city in branch selektiert und geprüft, ob zu jeder Stadt in Tabelle staff ein Mitarbeiter existiert.
Die vorhandenen Städte werden aufsteigend sortiert.


Aufgabe 4: Alle Städte mit Office und Mietgebäude in gleicher Stadt:

Code [Auswählen] Erweitern

SELECT b.city, (select count(*) from PropertyForrent  as a Where a.branchNo=b.branchNo) AS Rent
FROM branch AS b
WHERE ((((select count(*) from PropertyForrent  as a Where a.branchNo=b.branchNo))<>0));

Es wird nach city in branch selektiert und geprüft, ob jede Stadt auch in Tabelle PropertyForRent existiert.

Insofern sind 3 und 4 doch völlig verschiedene Aufgaben u. damit verschiedene Lösungen!

Gruß Oma