SQL Queries

[smog]

Hy Volks,

erst einmal hallo an alle. Bin ja neu hier und kenn mich noch nicht so aus. Ich hoffe mir kann jemand mit meiner Frage helfen, ich selbst bin schon am Verzweifeln.

Ich muss für ein Studienprojekt SQL Queries ausarbeiten und schaffe fünf davon einfach nicht. Ich versuch sie in VS zu schreiben aber bei den fünf beiß ich mir schon seit Tagen die Zähne aus.

Ich hab die Tabellen mit den Argumenten:

STUDENT (sid, sname, sex, age, year, averagegrade)
SCHOOL (dname, numhonours)
PROF (pname, dname)
COURSE (dname, cno, cname)
MAJOR (dname, sid)
CLASS (dname, cno, classno, pname)
ENROLL (sid, dname, cno, classno, grade)

Die fünf Abfragen die ich einfach nicht schaffe:

5. List the professors in which every class they teach has at least one student who is younger than twenty.

6. Find the names and majors of the students who are taking at least two database related courses, i.e, courses containing the word "database".

7. List the name of the schools and its number of honours students if the school has no major containing “database” courses.

8. For each class from the SIT school, display the course number, class number, and the lowest average grade of the students enrolled in that class. Sort the results in ascending order of lowest average grade.

9. List the student names with the second highest average grade in their major schools.

10. List the student names who are taking courses from either the SIT school or the business school, but not from both.

Vielleicht kann mir jemand helfen, ich bin wirklich schon am verzweifeln und bin auch wirklich kein DB Spezialist, eher ein Noop....

mfg
smog

[Bernd Schulz]

Hallo!

Die kleine Hausaufgabenhilfe:
5. Sieh Dir mal Subselects an
Beispiel Alle Klassen, die einen Schüler Namens Hans haben
select * from class where cno in(select cno from enroll where sid in((select sid from student where sname like '%Hans%')))

6. Wieder Subselects diesmal in Kombination mit HAVING
7. Sieh Dir mal EXISTS an
8. Das muss aber machbar sein
9. Ist zweitgrößter Grade das Problem? Das Maximum, das kleiner als der größte Wert entspricht dem zweitgrößten Wert
10. Ein Subselect mit den Mitgliedern der beiden Schulen und ein Subselect mit den Schülern, die in beiden Schulen sind

Genug Hinweise?
Hilfe gibts immer! Lösungen für Hausaufgaben? NIE!

BYE BERND

[smog]

Erstmal Danke Bernd!

Ich komm aber trotzdem leider nicht drauf. Ich hab das Problem das mir gesagt wurde ich muss JOINS verwenden.
Ich weiß aber nicht was ich "joinen" muss.

Vielleicht kannst du mir ja die Query für die Frage 5 zeigen und ich schaff es dann die andern Fragen auszuarbeiten.

Wie gesagt, blutiger Anfänger....

thx

[smog]

Ich hab jetzt nach langer harter Arbeit einmal die Frage 5 mit Subselects gemacht. Bin mir aber nicht sicher ob das stimmt. Kann sich das vielleicht jemand ansehen?

SELECT pname
FROM dbo.prof
WHERE (dname IN
(SELECT dname
FROM dbo.class
WHERE (cno IN
(SELECT cno
FROM dbo.enroll
WHERE (sid IN
(SELECT sid
FROM dbo.student
WHERE (age < 20)))))))

lg
Smog

[smog]

Bei 7. und 10. bin ich mir auch nicht sicher:

7.
SELECT dbo.school.dname, dbo.school.numhonours
FROM dbo.enroll INNER JOIN
dbo.school ON dbo.enroll.dname = dbo.school.dname INNER JOIN
dbo.course ON dbo.enroll.cno = dbo.course.cno
WHERE (NOT (dbo.course.cname LIKE '%database%'))
GROUP BY dbo.school.dname, dbo.school.numhonours

10.
SELECT dbo.student.sname
FROM dbo.enroll INNER JOIN
dbo.student ON dbo.enroll.sid = dbo.student.sid
WHERE (dbo.enroll.dname = 'SIT') AND (NOT (dbo.enroll.dname = 'Business')) OR
(dbo.enroll.dname = 'Business') AND (NOT (dbo.enroll.dname = 'SIT'))
GROUP BY dbo.student.sname

bei 9. bin ich jetzt soweit

SELECT dbo.student.sname
FROM dbo.student INNER JOIN
dbo.enroll ON dbo.student.sid = dbo.enroll.sid
WHERE (MAX(dbo.student.averagegrade) <
(SELECT MAX(student_1.averagegrade) AS Expr1
FROM dbo.student AS student_1 INNER JOIN
dbo.enroll AS enroll_1 ON student_1.sid = enroll_1.sid))

die funktioniert aber irgendwie nicht, und ich weiß nicht wiese. Bitte um Hilfe, übermorgen ist Prüfung....

lg
smog

[Falk Prüfer]

Ich hab jetzt nach langer harter Arbeit einmal die Frage 5 mit Subselects gemacht. Bin mir aber nicht sicher ob das stimmt. Kann sich das vielleicht jemand ansehen?

SELECT pname
FROM dbo.prof
WHERE (dname IN
(SELECT dname
FROM dbo.class
WHERE (cno IN
(SELECT cno
FROM dbo.enroll
WHERE (sid IN
(SELECT sid
FROM dbo.student
WHERE (age < 20)))))))

lg
Smog

Hallo Smog,

ich habe es jetzt nicht im einzelnen geprüft, aber die Abfrage würde so keiner schreiben
Stichwort Subselect ist schon Richtig und diese Aufgabe ist am besten mit einem Subselect zu lösen, aber eben nicht ausschließlich. Die Kombination aus Subselect und Join macht es.
[highlight=sql]
SELECT p.pname FROM PROF
WHERE p.pname in (
SELECT c.pname
from CLASS c LEFT JOIN (ENROLL e left join STUDENT s on s.sid = e.sid) on c.cno = e.cno
where s.age < 20)
[/highlight]
Das innere Select ist ein ganz normaler mehrfacher JOIN, um die Namen der Professoren zu jedem Student der jünger als 20 ist zu ermitteln. Dafür wird kein Subselect benötigt! Das Subselect dient jetzt nur dazu, die Namen der Professoren (und ggfs. weitere Daten zum Prof) einzeln - also nicht mehrfach wie im JOIN - auszugeben.
Das gleiche Ergebnis könnte man für diesen konkreten Fall natürlich auch mit einem DISTINCT erreichen
[highlight=sql]
SELECT DISTINCT c.pname
from CLASS c LEFT JOIN (ENROLL e left join STUDENT s on s.sid = e.sid) on c.cno = e.cno
where s.age < 20
[/highlight]

Gruß Falk

[smog]

Danke!

Das mit den Joins wird mir jetzt schon klarer und das mit den Subselects auch. Ich hab nur noch Probleme wie bei Frage 6. und Frage 9 weil ich da nicht weiß wie ich was verknüpfen muss (in SQL) damit ich das Ergebnis bekomme.

lg
smog

[Falk Prüfer]

7.
SELECT dbo.school.dname, dbo.school.numhonours
FROM dbo.enroll INNER JOIN
dbo.school ON dbo.enroll.dname = dbo.school.dname INNER JOIN
dbo.course ON dbo.enroll.cno = dbo.course.cno
WHERE (NOT (dbo.course.cname LIKE '%database%'))
GROUP BY dbo.school.dname, dbo.school.numhonours

Ist nach meiner Auffasung falsch, da in jedem Fall die Schulen fehlen würden, die überhaupt keinen Kurs anbieten. Und was soll das GROUP BY?
Die Lösung ist hier wieder ein Subselect. Das innere Select ist ein normaler JOIN der die Schulnamen liefert die einen Kurs LIKE %database% anbieten. Das ganze wird dann mit einem WHERE school.dname NOT IN (... mit dem äußeren Select (nur Tabelle SCHOOL) verknüpft.

10.
SELECT dbo.student.sname
FROM dbo.enroll INNER JOIN
dbo.student ON dbo.enroll.sid = dbo.student.sid
WHERE (dbo.enroll.dname = 'SIT') AND (NOT (dbo.enroll.dname = 'Business')) OR
(dbo.enroll.dname = 'Business') AND (NOT (dbo.enroll.dname = 'SIT'))
GROUP BY dbo.student.sname

Das ist definitv falsch! Wozu wieder das GROUP BY? Logik ist so eine Sache Die WHERE-Klausesl wertet immer EINEN Datensatz aus und wenn da enroll.dname='SIT' ist, dann ist das auch immer ungleich 'Business' und umgekehrt. Die Studenten sollen ja entweder an der einen oder an der anderen Schule sein. Fällt dir zu "entweder oder" was ein? - XOR heißt das Zauberwort! Aber bitte nicht pro Datensatz, sondern die beiden Subselects mit XOR verknüpfen.

bei 9. bin ich jetzt soweit

SELECT dbo.student.sname
FROM dbo.student INNER JOIN
dbo.enroll ON dbo.student.sid = dbo.enroll.sid
WHERE (MAX(dbo.student.averagegrade) <
(SELECT MAX(student_1.averagegrade) AS Expr1
FROM dbo.student AS student_1 INNER JOIN
dbo.enroll AS enroll_1 ON student_1.sid = enroll_1.sid))

Ist auch falsch, für die Verwendung der Aggregatfunktion MAX wär nun diesmal ein GROUP BY notwendig gewesen. Hier mußt du die Abfragen etwas Schachteln. Der zweite (und nur der) ist der Beste von all denen die schlechter als der Allerbeste sind. Du brauchst also eine Abfrage die den Besten pro Schule ermittelt und die dann über NOT IN nochmal mit den Schülern/MajorSchool verknüpft wird und von denen ist es dann der Beste. Alles klar?

...Bitte um Hilfe, übermorgen ist Prüfung....

Tja, da hast du wohl etwas spät angefangen dich mit dem Thema zu befassen *keinverständnisundkeinmitleidhab*

Gruß Falk

[Falk Prüfer]

Danke!

Das mit den Joins wird mir jetzt schon klarer und das mit den Subselects auch. Ich hab nur noch Probleme wie bei Frage 6. und Frage 9 weil ich da nicht weiß wie ich was verknüpfen muss (in SQL) damit ich das Ergebnis bekomme.

lg
smog

OK, du bemühst dich also will ich dir mal zu 6. noch einen Tipp geben.
Das Ganze ist wieder eine JOIN/Subselect Kombination. Das innere Select ist zusätzlich eine Gruppierte Abfrage und wird mit HAVING COUNT eingeschränckt, da ja nur die Studenten gesucht werden die mindestens 2 database-Kurse besuchen (Die kennst du dann wahrscheinlich nicht )
[highlight=sql]
select s.sname, m.dname
from STUDENT s inner join MAJOR m on m.sid = s.sid
where s.sid in (
select e.sid from ENROLL e inner join COURSE c on c.cno = e.cno
where c.cname like '%database%'
group by e.sid
having count(*) >= 2)
[/highlight]

Gruß Falk

[nils007]

Hi guys,

ich arbeite an den selben SQL-Queries wie smog. Hier mal meine Versuche:

zu 7:

SELECT     dname, numhonours
FROM         dbo.school
WHERE     (dname NOT IN
                 (SELECT     dbo.enroll.dname
                   FROM          dbo.enroll INNER JOIN
                                      dbo.course ON dbo.enroll.cno = dbo.course.cno
                   WHERE      (dbo.course.cname LIKE '%database%')))

zu 10:

Ok, dass seh ich ein, dass die Abfrage nich passen kann, wenn immer nur ein Datensatz geprüft wird. Allerdings weiß ich nicht wie man XOR in SQL implementiert. Ich komm eher von der SPS und C++ Seite. Da denkt man ein bisschen anders.

zu 9:

[Falk Prüfer]

...zu 7:

SELECT     dname, numhonours
FROM         dbo.school
WHERE     (dname NOT IN
                 (SELECT     dbo.enroll.dname
                   FROM          dbo.enroll INNER JOIN
                                      dbo.course ON dbo.enroll.cno = dbo.course.cno
                   WHERE      (dbo.course.cname LIKE '%database%')))

Hallo nils007,

sieht auf den ersten Blick ganz gut aus . Kann aber sein, das lt. Aufgabenstellung der innere Join noch auf die MAJOR-Tabelle ausgeweitet werden muß. "...school has no major containing “database” courses."

zu 10:

Ok, dass seh ich ein, dass die Abfrage nich passen kann, wenn immer nur ein Datensatz geprüft wird. Allerdings weiß ich nicht wie man XOR in SQL implementiert. Ich komm eher von der SPS und C++ Seite. Da denkt man ein bisschen anders.

zu 9:

Ich lass jetzt mal den JOIN auf die Studenten weg und beschränk mich nur auf das "XOR"
[highlight=sql]
select e.sid
from ENROLL e
where e.cno in (select ic.cno from COURSE ic where ic.dname = 'SIT')
XOR e.cno in (select ic.cno from COURSE ic where ic.dname = 'business')
[/highlight]

Gruß Falk

[nils007]

zu 8: Die sollte stimmen, oder?

SELECT     dbo.enroll.cno, dbo.enroll.classno, MIN(dbo.student.averagegrade) AS Expr1
FROM         dbo.enroll INNER JOIN
                  dbo.student ON dbo.enroll.sid = dbo.student.sid
WHERE     (dbo.enroll.dname = 'SIT')
GROUP BY dbo.enroll.cno, dbo.enroll.classno
ORDER BY Expr1

[nils007]

Ich hab jetzt mal versucht die 10 zusammen zu basteln.

[highlight=sql]

SELECT student.sname
FROM student INNER JOIN
enroll ON student.sid = enroll.sid
WHERE enroll.cno IN (SELECT course.cno FROM course WHERE course.dname = 'SIT')
XOR (SELECT course.cno FROM course WHERE course.dname = 'Business')
[/highlight]

Zum XOR:

Ich lass jetzt mal den JOIN auf die Studenten weg und beschränk mich nur auf das "XOR"
[highlight=sql]
select e.sid
from ENROLL e
where e.cno in (select ic.cno from COURSE ic where ic.dname = 'SIT')
XOR e.cno in (select ic.cno from COURSE ic where ic.dname = 'business')
[/highlight]

Leider nimmt mein Visual Studio das XOR nicht. Darum hab ich die Abfrage auch nicht testen können. Ich hab schon alles Mögliche probiert (Klammern umsetzten, usw.), aber leider funzt das nicht. Gibts da noch irgendwas, was ich beachten sollte?

das lt. Aufgabenstellung der innere Join noch auf die MAJOR-Tabelle ausgeweitet werden muß. "...school has no major containing “database” courses."

hm, Die Tabelle 'Major' besteht ja nur aus dname, sid. All diese Attribute hab ich ja eigentlich schon berücksichtigt, oder nicht?

sieht auf den ersten Blick ganz gut aus

yeah!


thx
nils

[O. Helper]

Hallo Nils,

wenn Du von Visual Studio schreibst, nutzt Du wahrscheinlich den SQL Server 2000/2005 (bzw. Express).
Tja, MSQL und ANSI SQL, 2 Welten treffen aufeinander.
Der MSQL kennt keinen logischen Operator XOR (bin selbst überrascht darüber). Es gibt nur das bitweise XOR mit dem Zeichen "^" (Siehe BOL unter "^ (Bitweises exklusives OR)". Der hilft Dir hierbei nur nichts.

Zum Testen musst Du es einmal logisch mit OR/AND/NOT auflösen:
[highlight=SQL]
SELECT e.sid
FROM ENROLL e
WHERE (e.cno IN (SELECT ic.cno FROM COURSE ic WHERE ic.dname = 'SIT')
AND NOT e.cno IN (SELECT ic.cno FROM COURSE ic WHERE ic.dname = 'business')
)
OR
(NOT e.cno IN (SELECT ic.cno FROM COURSE ic WHERE ic.dname = 'SIT')
AND e.cno IN (SELECT ic.cno FROM COURSE ic WHERE ic.dname = 'business')
)[/highlight]

Olaf

[nils007]

Danke Olaf!!
Das hilft gleich weiter. Jetzt funzt die Query auch im VS. Die Datenbank läuft auf einem SQL Server Express.

Ich hab jetzt also die 10 nochmals zusammengestöpselt.
[highlight=sql]
SELECT DISTINCT dbo.student.sname
FROM dbo.student INNER JOIN
dbo.enroll ON dbo.student.sid = dbo.enroll.sid
WHERE (dbo.enroll.cno IN
(SELECT cno
FROM dbo.course
WHERE (dname = 'SIT'))) AND (NOT (dbo.enroll.cno IN
(SELECT cno
FROM dbo.course AS course_2
WHERE (dname = 'business')))) OR
(NOT (dbo.enroll.cno IN
(SELECT cno
FROM dbo.course AS course_3
WHERE (dname = 'SIT')))) AND (dbo.enroll.cno IN
(SELECT cno
FROM dbo.course AS course_1
WHERE (dname = 'business')))
ORDER BY dbo.student.sname
[/highlight]

Das Distinct hab ich deswegen reingenommen, weil Namen teilweise doppelt dabei waren. Darum glaub ich auch, dass die Query irgendwo noch nicht ganz stimmt. Oder ist das möglich dass ein Name 2-mal erscheint??

Danke für eure Hilfe! Ihr seid echt ne tolle Truppe!!

Nils