-- ALTER-TABLE--Nachtrag
-- ---------------------
-- 
-- Index für Nachname löschen 
-- 	ALTER TABLE studenten DROP INDEX name;
--
-- Primärschlüssel löschen
-- 	ALTER TABLE studenten DROP PRIMARY KEY;
-- 
-- Primärschlüssel erstellen
-- 	ALTER TABLE studenten ADD PRIMARY KEY (matrnr);
-- 
-- Ändern der Spaltenbezeichnung
-- 	ALTER TABLE studenten CHANGE name VorUndNachname CHAR(50);
-- 
-- Tabellennamen ändern (Beachte Verknüpfungen mit anderen Tabellen!):
--  	ALTER TABLE studenten RENAME studierende;
-- 
-- Tabellenspalte einen Default-Wert zuweisen:
-- 	ALTER TABLE studenten ALTER semester SET DEFAULT 1;
-- 
-- Standardwert aus Tabelle löschen:
--      ALTER TABLE studenten ALTER semester DROP DEFAULT;



-- SQL zum Einfügen, Modifizieren und Löschen

-- Füge neue Vorlesung mit einigen Angaben ein: 
insert into Vorlesungen (VorlNr, Titel, gelesenVon)
values (4711,'Selber Atmen', 2125)

-- Schicke alle Studenten in die Vorlesung Selber Atmen: 
insert into hoeren
select MatrNr, VorlNr
from   Studenten, Vorlesungen
where  Titel = 'Selber Atmen'

-- Erweitere die neue Vorlesung um ihre Semesterwochenstundenzahl: 
update vorlesungen
set    SWS=6
where  Titel='Selber Atmen'

-- Entferne alle Studenten aus der Vorlesung Selber Atmen: 
delete from hoeren
where vorlnr = 
     (select VorlNr from Vorlesungen
      where  Titel = 'Selber Atmen')

-- Entferne die Vorlesung Selber Atmen: 
delete from Vorlesungen
where titel = 'Selber Atmen'



-- SQL zum Anlegen von Sichten

-- Lege Sicht an für Prüfungen ohne Note: 
create view pruefenSicht as
select MatrNr, VorlNr, PersNr
from   pruefen

-- Lege Sicht an für Studenten mit ihren Professoren: 
create view StudProf (Sname, Semester, Titel, PName) as
select s.Name, s.Semester, v.Titel, p.Name
from   Studenten s, hoeren h, Vorlesungen v, Professoren p
where  s.MatrNr     = h.MatrNr 
and    h.VorlNr     = v.VorlNr
and    v.gelesenVon = p.PersNr

-- Lege Sicht an mit Professoren und ihren Durchschnittsnoten: 
create   view ProfNote (PersNr, Durchschnittsnote) as
select   PersNr, avg (Note)
from     pruefen
group by PersNr

-- Entferne die Sichten wieder: 

drop view pruefenSicht;
drop view StudProf;
drop view ProfNote;



-- Lege Untertyp als Verbund von Obertyp und Erweiterung an: 
create table Angestellte (PersNr integer not null,
                          Name   varchar(30) not null);
						  
create table ProfDaten (PersNr     integer not null,
                        Rang       character(2),
                        Raum       integer);

create table AssiDaten (PersNr     integer not null,
                        Fachgebiet varchar(30),
                        Boss       integer);

-- Inhalte füllen
insert into angestellte
select persnr, name
from professoren

insert into angestellte
select persnr, name
from assistenten

insert into profdaten
select persnr, rang, raum
from professoren

insert into assidaten
select persnr, fachgebiet, boss
from assistenten

-- Anlegen/Füllen der integrierenden Views 
create view Profs as
   select a.persnr, a.name, d.rang, d.raum
   from   Angestellte a, ProfDaten d
   where  a.PersNr = d.PersNr

create view Assis as
   select a.persnr, a.name, d.fachgebiet, d.boss
   from   Angestellte a, AssiDaten d
   where  a.PersNr = d.PersNr



-- Entferne die Tabellen und Sichten wieder: 
drop table Angestellte;
drop table AssiDaten;
drop table ProfDaten;
drop view  Profs; 
drop view  Assis;



-- Lege Obertyp als Vereinigung von Untertypen an (zwei der drei Untertypen sind schon vorhanden): 
create table AndereAngestellte (PersNr integer not null,
                                Name varchar(30) not null)

create view Angestellte as
  (select PersNr, Name from Professoren) union
  (select PersNr, Name from Assistenten) union
  (select PersNr, Name from AndereAngestellte)

-- Entferne die Tabelle und die Sichten wieder: 
drop table andereAngestellte;
drop view Angestellte;



-- Stored Procedures 

create table Messages
   ( MID      integer auto_increment not null, 
     Message  varchar(32),
     primary key (MID) );

create table MessagesBackup
   ( MBID      integer auto_increment not null, 
     MOldID    integer,
     MessageB  varchar(32),
     primary key (MBID) );

insert into Messages (Message) values ('Nachricht--01');
insert into Messages (Message) values ('Nachricht--02');
insert into Messages (Message) values ('Nachricht--03');
insert into Messages (Message) values ('Nachricht--04');
insert into Messages (Message) values ('Nachricht--05');
insert into Messages (Message) values ('Nachricht--06');

select * from Messages;
select * from MessagesBackup;

-- Leere Stored Procedure spezifizieren

-- DELIMITER $$
drop procedure if exists spCopyMessage $$
create procedure spCopyMessage()
begin

end $$
-- DELIMITER ;

show procedure status;

-- Ausgewählte Nachrichten in MessagesBackup kopieren

-- DELIMITER $$
drop procedure if exists spCopyMessage $$
create procedure spCopyMessage( id integer )
begin
    insert into MessagesBackup (MOldID, MessageB)
	select MID, Message from Messages where MID = id;
end $$
-- DELIMITER ;

show procedure status;

call spCopyMessage(1);
call spCopyMessage(3);

select * from Messages;
select * from MessagesBackup;

-- Fuege neue Nachricht in Messages ein; aktualisiere ein bereits 
-- vorhandene Nachticht in Messages und speichere eine Kopie dieser
-- Nachricht in MessagesBackup.

-- DELIMITER $$
drop procedure if exists spInsert_OR_Update $$
create procedure spInsert_OR_Update( id integer, Msg varchar(32) )
begin
    if ( id = 0 ) then
        set id = null;
    end if;

    if ( id is not null ) and 
	   ( exists ( select * from Messages where MID = id ) ) 
	then
	   call spCopyMessage(id);
	   update Messages set Message = Msg where MID = id;
	else
	   insert into Messages (MID, Message) values (id, Msg);
	end if;
end $$
-- DELIMITER ;

show procedure status;

call spInsert_OR_Update( 2, 'Test' );

select * from Messages;
select * from MessagesBackup;

call spInsert_OR_Update( 11, 'Frank' );

select * from Messages;
select * from MessagesBackup;

drop procedure spCopyMessage;
drop procedure spInsert_OR_Update;

drop table Messages;
drop table MessagesBackup;



-- Stored Functions

-- DELIMITER $$
create function Vorlesungsanzahl() returns integer
deterministic
begin
    declare tval integer;
    select count(*) into tval from Vorlesungen;
    return tval;
end $$
-- DELIMITER ;

select Vorlesungsanzahl();



-- Trigger 

create table News
    ( NID integer auto_increment not null,
      Msgs varchar(210),
      primary key (NID) );

create table NewsLog
    ( NLID    integer auto_increment not null,
      NOldID  integer,
	  LMsg    varchar(210),
      primary key (NLID) );

-- DELIMITER $$
drop trigger if exists trLogNews $$
create trigger trLogNews
    after insert on News
    for each row begin
        insert into NewsLog (NOldID, LMsg) select NID, Msgs from News where NID=(select max(NID) from News);
    end $$
-- DELIMITER ;

insert into News(Msgs) values ('News-1');
insert into News(Msgs) values ('News-2');
insert into News(Msgs) values ('News-3');
	
select * from News;
select * from NewsLog;
	
drop trigger trLogNews;

drop table News;
drop table NewsLog;