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. ?????
8) 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]
Hallo Mak,
wie wäre es mit einer exakten deutschen Beschreibung der Aufgaben?
Gruß Oma
ich habe nun die Aufgabenstellungen uebersetzt:
danke fuer zukuenftiger hilfe
[Anhang gelöscht durch Administrator]
Hallo Mak,
anbei die ersten 4 Aufgaben, andere folgen evt. heute abend:
1.alle Branch mit Anzahl Eigentümer u. jährliche Miete:
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):
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:
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:
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
Hallo Oma
da hast du dir aber ein Fleissternchen für diese Antwort verdient ;D
Hallo,
also - alle Achtung ;D
Zitatalle queries getestet
Was ist das für eine Musterdatenbank? Die kenn' ich noch gar nicht.
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:
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
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)
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]
Hallo,
Zitatalso - 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:
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
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)
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.
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)
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:
SELECT DISTINCT branch.city
FROM branch
WHERE ((((select count(*)
from PropertyForRent
Where PropertyForRent.city=branch.city))>0));
-------------------------------------------------------------------------------------------------------------
zu 6b.
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
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:
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
UUUPPPSSS, (alte OMA stammt aber NICHT von mir - gelle) ;D ;)
....was ja auch klar ist, da sonst die Summierung nicht richtig abgearbeitet wird .... :'(
sorry
Peter
danke für die, für mich, sehr konstruktive konversation.
fage: ist hier distinct die einzige möglichkeit?
ZitatSELECT 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?
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
Hallo,
du hast Recht; für 5. gibt es auch eine andere (bessere) Lösung:
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:
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
Zitatmusst 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)
ZitatDie 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
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:
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:
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
Hi,
ich studier Finance da. Daher auch meine Anfängerfragen, da ich nur aus reinem Interesse diesen Kurs genommen habe und mich gerne mit Datenbanken auseinandersetzen möchte.
aber nochmals zu meiner Aufgabe:
Zitat
4) Create an alphabetically ordered list of cities where the company has only non-operational (empty) branch offices (but not operational ones with employees)
Zitat
Die Aufgabestellung kann man SO interpretieren:
Erzeuge eine Liste jener Büros, die nicht in der Liste von Büros mit Angestellten enthalten sind.
versteh mich nicht falsch, oma, aber diese Aufabe wurde von dir damals anscheinend übersehen.
dein letzer code habe ich nachvollziehen können und enstpricht bei mir der Aufgabe 5 (bei dir 4)
daher meine Frage, ob es einen Unterschied in der Aufgabenstellung zu deiner 3 gibt
siehe
3) Auflistung aller Städte, die eine Branch Office haben, diese Branch Office aber keine Mitarbeiter
orginal: 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)
Aufgabe 5 enstpricht deiner Abfrage 4
gruss
mak
Hallo mak,
dann haben wir uns wohl falsch verstanden
Interpretiere bitte:
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 Deutung:
Erzeuge eine Liste jener Büros, die nicht in der Liste von Büros mit Angestellten enthalten sind.
kann ich nicht so recht verstehen. Es gibt eine Tabelle Branch mit allen Büros und eine Tabelle Staff, in der die Mitarbeiter mit Ihrem Büro angegeben sind. Alle Büros, die keine Mitarbeiter in Tabelle Staff haben entspricht schon Aufgabe 3
Was soll den nun mit Aufgabe 4 realisiert werden, alle Mitarbeiter ohne Büro geht nicht (da alle eins haben)
Du siehst, einer kann besser DB der andere besser Englich ;D
Gruß Oma
die keine Mitarbeiter in Tabelle Staff haben entspricht schon Aufgabe 3
genau so seh ich es auch
denn 4 besagt nach meiner Meinung:
Erstelle eine Liste von Städten (aber hier nun geordnet), in denen das Unternehmen nur Branch Offices unterhält, die leer sind.
Nach meiner Meinung ist das B002=London und und B004=Bristol.
Ergo das gleiche Ergebnis wie in 3.
Was meinst du?
Ich konnte damals nur darin noch einen unterschied erkennen, dass die 3 mit 3 punkten bewertet wurde und die 4 mit 5 punkten, was auf einen höheren schwierigkeitsgrad hindeutet.
gruss
mak