Übungen zu SQL

Teacher

Wir möchten eine Abfrage erstellen, die einem Programm die Namen aller Lehrer für jeden Kurs und jeden Schüler übergibt. Im späteren Ausdruck gibt es im Formular allerdings nur Platz für 2 Lehrernamen.

Wenn es nur einen Lehrer gibt, schreibe den NAmen in die erste zurüclgegebene Spalte und setze die zweite Spalte mit Leerzeichen bzw. NULL
Bei genau zwei Lehrern gib die Lehernamen in aufsteigender Reihenfolge zurück
Bei mehr als zwei Lehrern soll die Ausgabe in der ersten Spalte den ersten Lehrernmen zeigen, in der folgenden Spalte soll das Wort "Mehr" erscheinen.

Die Ausgangstabelle sieht we folgt aus.

CREATE TABLE Register

(course_nbr INTEGER NOT NULL,

student_name CHAR(10) NOT NULL,

teacher_name CHAR(10) NOT NULL, ..);

![image.png](attachment:image.png)



In [17]:

    
%load_ext sql









    



The sql extension is already loaded. To reload it, use:
  %reload_ext sql



In [18]:

    
%sql mysql://steinam:steinam@localhost/celko









    Out[18]:





'Connected: steinam@celko'



In [23]:

    
%%sql

select * from Register;









    



6 rows affected.






    Out[23]:





    
        course_nbr
        student_name
        teacher_name
    
    
        1
        schueler1
        STE
    
    
        2
        schueler2
        ZOB
    
    
        2
        schueler2
        ZIL
    
    
        3
        schueler3
        WAL
    
    
        3
        schueler3
        SOE
    
    
        3
        schueler3
        TRO

Lösung 1



In [22]:

    
%%sql

SELECT R1.course_nbr, R1.student_name,
MIN(R1.teacher_name) as Teacher_1, NULL
FROM Register AS R1
GROUP BY R1.course_nbr, R1.student_name
HAVING COUNT(*) = 1
UNION
SELECT R1.course_nbr, R1.student_name,
MIN(R1.teacher_name) as Teacher_1,
MAX(R1.teacher_name) as Teacher_2
FROM Register AS R1
GROUP BY R1.course_nbr, R1.student_name
HAVING COUNT(*) = 2
UNION
SELECT R1.course_nbr, R1.student_name,
MIN(R1.teacher_name) as Teacher_1, '--More--' as Teacher_2
FROM Register AS R1
GROUP BY R1.course_nbr, R1.student_name
HAVING COUNT(*) > 2;









    



3 rows affected.






    Out[22]:





    
        course_nbr
        student_name
        Teacher_1
        NULL
    
    
        1
        schueler1
        STE
        None
    
    
        2
        schueler2
        ZIL
        ZOB
    
    
        3
        schueler3
        SOE
        --More--



In [ ]:

Lösung 2

Benutzt ein Case-Konstrukt zur Abfrage der Anzahl der gefundenen Lehrer



In [25]:

    
%%sql
SELECT course_nbr, student_name, MIN(teacher_name) as Teacher_1,
CASE COUNT(*)   WHEN 1 THEN NULL
                WHEN 2 THEN MAX(teacher_name)
                ELSE '--More--' END as Teacher_2
FROM Register
GROUP BY course_nbr, student_name;









    



3 rows affected.






    Out[25]:





    
        course_nbr
        student_name
        Teacher_1
        Teacher_2
    
    
        1
        schueler1
        STE
        None
    
    
        2
        schueler2
        ZIL
        ZOB
    
    
        3
        schueler3
        SOE
        --More--



In [31]:

    
%%sql
-- andere Syntax, evtl verständlicher

SELECT course_nbr, student_name, MIN(teacher_name) as Teacher_1,
CASE    WHEN COUNT(*) = 1 THEN NULL
        WHEN COUNT(*) = 2 THEN MAX(teacher_name)
        ELSE '--More--' END as Teacher_2
FROM Register
GROUP BY course_nbr, student_name;









    



3 rows affected.






    Out[31]:





    
        course_nbr
        student_name
        Teacher_1
        Teacher_2
    
    
        1
        schueler1
        STE
        None
    
    
        2
        schueler2
        ZIL
        ZOB
    
    
        3
        schueler3
        SOE
        --More--

Lösung 3

Freie Plätze im Restaurant

Stellen Sie sich vor, dass Sie ein großes Restaurant mit 1000 Sitzplätzen führen. Sie möchten eine Übersicht der freien Plätze bzw. der freien Blöcke zwischen besetzten Plätzen.

Bsp:

Besetzt ist Tisch 101, dann würde eine Abfrage wie folgt aussehen.

Start	Ende	Frei
1	100	100
102	1000	999

Schreiben Sie eine Routine in ihrer Lieblingsprogrammiersprache zur Lösung des Problems.

Aufgabe

Schreiben Sie je ein SQL-Statement, welches Ihnen die jeweils ersten und letzen Plätze einer Lücke findet.

Im obigen Beispiel wären dies die Zahl 1 und 102 für die ersten freien Plätze, sowie 100 und 1000 für die letzten freien Pätze.



In [ ]:

    
%%sql


create table seats
(seat integer)



insert into seats(seat) values(0);
insert into seats(seat) values(1001);

insert into seats(seat) values(101);



CREATE VIEW Firstseat (seat)
AS SELECT (seat + 1)
FROM seats
WHERE (seat + 1) NOT IN
(SELECT seat FROM seats)
AND (seat + 1) < 1001;

CREATE VIEW Lastseat (seat)
AS SELECT (seat - 1)
FROM seats
WHERE (seat - 1) NOT IN
(SELECT seat FROM seats)
AND (seat - 1) > 0;


-- nutzt die beiden Views
SELECT F1.seat AS start, L1.seat AS finish,
((L1.seat - F1.seat) + 1) AS available
FROM Firstseat F1, Lastseat L1
WHERE L1.seat = (SELECT MIN(L2.seat)
FROM Lastseat AS L2
WHERE F1.seat <= L2.seat)
order by start;

-- braucht keinen view

SELECT (R1.seat + 1) AS start,
(MIN(R2.seat) - 1) AS finish,
abs((R1.seat + 1) - (MIN(R2.seat))) as free
FROM seats AS R1
INNER JOIN
seats AS R2
ON R2.seat > R1.seat
GROUP BY R1.seat
HAVING (R1.seat + 1) < MIN(R2.seat);

course_nbr	student_name	teacher_name
1	schueler1	STE
2	schueler2	ZOB
2	schueler2	ZIL
3	schueler3	WAL
3	schueler3	SOE
3	schueler3	TRO