create database dbname;

drop database dbname;

IF OBJECT_ID('tbl_kunde', N'U') is not null
	drop table tbl_kunde;
GO
create table tbl_kunde (
  id_kunde int not null primary key,
  fi_moral_nr int,
  name varchar(25) not null,
  vorname varchar not null,
  wohnort varchar
);
GO

ALTER TABLE tbl_kunde ADD PRIMARY KEY (id_kunde);

ALTER TABLE tbl_kunde ADD CONSTRAINT FK_fi_moral_nr FOREIGN KEY (fi_moral_nr)
  REFERENCES tkey_moral
  ON UPDATE CASCADE
  ON DELETE SET NULL;

ALTER TABLE tkey_moral ADD CONSTRAINT PK_id_moral_nr PRIMARY KEY (id_moral_nr);
ALTER TABLE tbl_kunde ADD CONSTRAINT FK_fi_moral_nr FOREIGN KEY (fi_moral_nr)
  REFERENCES tkey_moral
  ON UPDATE CASCADE
  ON DELETE SET NULL;

insert into tkey_moral (id_moral_nr, moral_bez) values (1, 'gut'), (2, 'schlecht'), (3, 'schlecht');

INSERT INTO tbl_kunde VALUES (3838,1,'Meier','Laura','Waldibrücke')

update tbl_kunde set name = 'Menzer' where name = 'Waltenspühl-Menzer'
update tass_police set praem_stufe = 101 where praem_stufe = 108

delete from tbl_kunde

delete from tkey_moral where id_moral_nr = 4
delete from tbl_kunde where vorname = 'Peter' and name = 'Fischer' or vorname = 'Martin' and name = 'Müller'

create unique index ix_kund_name on tbl_kunde (name)

alter index ix_kund_name on tbl_kunde disable

alter index ix_kund_name on tbl_kunde rebuild

alter index ix_kund_name on tbl_kunde reorganize

drop index ix_kund_name on tbl_kunde

drop index ix_kund_name on tbl_kunde

create type tp_moralisches from numeric(9,0)

alter login stud23 with password = 'hello' old_password = 'pass_wd23'

create user romulus from login romulus

drop user romulus

grant select, insert, delete, references, update to romulus

grant references (id_moral_nr) on tkey_moral to romulus

revoke insert, delete, references, update to romulus

create role verkauf

exec sp_addrolemember 'verkauf', 'anna'

grant select, update, insert, delete on tbl_kunde to verkauf

CREATE VIEW v_kunden (id, name, vorname, ort, police, gebiet) AS
  SELECT k.id_kunde, k.name, k.vorname, k.wohnort, v.vers_bez,
         v.vers_gebiet
    FROM tbl_kunde AS k, tkey_versicherung AS v, tass_police AS p
      WHERE k.id_kunde = p.id_fi_kunde
        AND p.id_fi_vers_art = v.id_vers_art;

create view plain as select id_kunde, name, vorname from tbl_kunde

create view v_umsatz as select sum(cast(bezahlt as float)) as 'Umsatz aus Policen' from tass_police

select dt_jahr from dbo.tbl_stueck

select dt_jahr from dbo.tbl_stueck where dt_jahr is not null

select dt_jahr from dbo.tbl_stueck
where dt_jahr is not null
order by dt_jahr desc

select distinct dt_jahr from dbo.tbl_stueck
where dt_jahr is not null
order by dt_jahr asc

select * from tbl_stueck where dt_jahr = 1970 and dt_zeit > 3

select * from tbl_stueck where dt_jahr = 1970 and dt_zeit between 3 and 10

select * from tbl_stueck where dt_stueck_titel like 'Let%'

select count(dt_stueck_titel) from tbl_stueck where dt_stueck_titel like 'Let%'

select i.dt_name, count(s.dt_stueck_titel) from tbl_stueck as s, tkey_interpret as i
where  s.fi_interpret = i.id_interpret
group by i.dt_name
having count(s.dt_stueck_titel) >10
order by count(s.dt_stueck_titel) de

select s.dt_stueck_titel, i.dt_name
from tbl_stueck as s, tkey_interpret as i
where  s.fi_interpret = i.id_interpret
order by s.dt_stueck_titel

select sa.id_jahr, st.dt_stueck_titel, ip.dt_name
from tbl_stueck as st, tass_stueck_sampler as ss, tkey_sampler as sa, tkey_interpret as ip
where ss.id_fi_stueck_nr = st.id_stueck_nr 
and ss.id_fi_jahr = sa.id_jahr
and  st.fi_interpret = ip.id_interpret
order by  st.dt_stueck_titel

select dt_stueck_titel, dt_name
from tbl_stueck join tkey_interpret 
on fi_interpret = id_interpret
order by dt_stueck_titel

select id_fi_jahr, dt_stueck_titel, dt_name
from tbl_stueck
join tass_stueck_sampler on id_fi_stueck_nr = id_stueck_nr
join tkey_interpret on fi_interpret = id_interpret
order by  dt_stueck_titel

SELECT customers.id, customers.name, items.name, customers.state 
FROM customers, items
WHERE customers.id=seller_id
ORDER BY customers.id

SELECT customers.name, items.name FROM customers
LEFT OUTER JOIN items ON customers.id=seller_id

select dt_stueck_titel as Titel, dt_zeit as Zeit
from tbl_stueck
where dt_zeit = (select max(dt_zeit) from tbl_stueck)
or dt_zeit = (select min(dt_zeit) from tbl_stueck)
order by dt_zeit;

select dt_stueck_titel as Titel, dt_zeit as Zeit
from tbl_stueck
where dt_zeit between (select avg(dt_zeit) from tbl_stueck)*0.9
and (select avg(dt_zeit) from tbl_stueck)*1.1
order by dt_zeit;

select dt_stueck_titel as Titel,
dt_zeit/(select avg(dt_zeit) from tbl_stueck)*100 as Zeit
from tbl_stueck
where dt_stueck_titel = 'You Shook Me'

select *  from
(select dt_stueck_titel as titel, 'stück' as 'type' from tbl_stueck
union
select dt_name as titel,  'inter' as 'type' from tkey_interpret
union
select dt_stao as titel,  'stao' as 'type' from tkey_standort) as t
where t.titel like '%boy%'

begin transaction
update tass_police set bezahlt = 0 where id_fi_kunde = 3533 and id_fi_vers_art = 1700
commit
-- or rollback

create function f_plic_bez()
  returns decimal(10,2)
  as begin
  return (select sum(bezahlt) from tass_police)
  end;
go
select  dbo.f_plic_bez() AS 'Summe aller bezahlten Leistungen'
go

drop function f_bezahlt_versich

create function f_rabatt(@name varchar(40), @vers varchar(30))
  returns int
  as begin
  return (select (praem_stufe-100)*10 from  tass_police
  join tbl_kunde on id_fi_kunde = id_kunde
  join tkey_versicherung on id_fi_vers_art = id_vers_art
  where name = @name and vers_bez = @vers)
  end

CREATE PROCEDURE p_polic_del @fname VARCHAR(30), @versich VARCHAR(30)
  AS
  BEGIN
    DELETE FROM tass_police
      FROM tbl_kunde, tkey_versicherung
        WHERE name = @fname
          AND id_kunde = id_fi_kunde
          AND id_fi_vers_art = id_vers_art
          AND vers_bez = @versich
    IF @@ROWCOUNT = 0
      PRINT 'Police existiert nicht.'
      ELSE PRINT 'Löschung vollzogen.'
  END
GO

EXECUTE p_polic_del 'Meier', 'Taggeld';

DECLARE @veraenderung SMALLINT = 180;
DECLARE @neue_summe INT;

create trigger t_ort on tbl_kunde
  after insert, update
  as beginn
    set @ort = select wohnort from inserted)
    if((@ort like '/^[A-Z]') && len(@ort) < 2)
      rollback transaction
  end
end

drop trigger t_ort

create trigger t_bst_mut on bst
	for insert, update
	as
	begin
		set nocount on;
		if(select id_fi_k from inserted) NOT IN (select id_kund from knd)
			begin
				raiserror('Es besteht kein entsprechender Kunde.', 15, 1);
				rollback transaction;
			end
	end;

create trigger t_ku_del on knd
	for delete
	as
	begin
		set nocount on;
		if((select id_kunde from deleted) IN (select id_fi_k from bst))
			begin
				raiserror('Löschung verwert; es bestehen noch Bestelungen für diesen Kunden.', 15, 1)
				rollback transaction
			end
	end;

create trigger t_update_knd on knd
	after update
	as
	begin
		set nocount on;
		update bst set id_fi_k = (select id_kund from inserted)
	end;

create trigger t_delete_knd on knd
	after delete
	as
	begin
		delete from bst where id_fi_k = (select id_kund from deleted)
	end;