-- 4a
CREATE VIEW Aufg4a AS
SELECT busid,(SELECT fahrtziel FROM fahrtziel AS i WHERE i.fahrtzielid = a.fahrtzielid) AS [Fahrtziel], fahrtdatum, gebuchte_plaetze
FROM fahrt AS a WHERE gebuchte_plaetze < 50
-- SELECT * FROM Aufg4a

-- 4b
CREATE VIEW Aufg4b AS
SELECT TOP 5 * FROM wartung WHERE maengel LIKE '%Bremsen%' AND year(gewartetam) = 2006 ORDER BY kosten DESC
-- SELECT * FROM Aufg4b

-- 4c
CREATE VIEW Aufg4c AS
SELECT count(a.fahrtzielid) AS [Anzahl Buchungen], i.fahrtziel 
FROM fahrt AS a 
JOIN fahrtziel AS i 
ON a.fahrtzielid = i.fahrtzielid
GROUP BY i.fahrtziel
-- SELECT * FROM Aufg4c

-- 4d
CREATE VIEW Aufg4d AS
SELECT avg(a.gebuchte_plaetze) AS [Durchschnittliche Belegung], i.fahrtziel FROM fahrt AS a JOIN fahrtziel AS i 
ON a.fahrtzielid = i.fahrtzielid
GROUP BY i.fahrtziel
-- SELECT * FROM Aufg4d

-- 4e
CREATE VIEW Aufg4e AS
SELECT a.vorname + ' ' + a.name AS [Name], a.wohnort, a.strasse, i.fahrtdatum, i.fahrtzielid 
FROM Fahrer AS a
JOIN Fahrt AS i
ON i.fahrerid = a.fahrerid
-- SELECT * FROM Aufg4e

-- 4f 
CREATE VIEW Aufg4f AS
SELECT a.name, a.gehalt, i.fahrtzielid, count(i.fahrtzielid) AS [Wie oft zu Fahrtziel], min(i.fahrtdatum) AS [Erste Fahrt zu diesem Ziel], max(i.fahrtdatum) AS [Letzte Fahrt zu diesem Ziel]
FROM Fahrer AS a
JOIN Fahrt AS i
ON i.fahrerid = a.fahrerid AND year(i.fahrtdatum) IN (2005,2006)
GROUP BY a.name, a.gehalt, i.fahrtzielid
-- SELECT * FROM Aufg4f

-- 5a
CREATE PROC Aufg5a
@fahrtid int, @busid int, @fahrerid int, @fahrtzielid nvarchar(5), @fahrtdatum datetime, @gebuchte_plaetze int
AS INSERT INTO fahrt
VALUES (@fahrtid, @busid, @fahrerid, @fahrtzielid, @fahrtdatum, @gebuchte_plaetze)

/* 
EXEC Aufg5a
@fahrtid = 14,
@busid = 21,
@fahrerid = 1,
@fahrtzielid = 'ROMA',
@fahrtdatum = '01.01.2008',
@gebuchte_plaetze = 50

SELECT * FROM fahrt
*/

-- 5b
CREATE PROC Aufg5b
@fahrtid int, @gebuchte_plaetze int
AS UPDATE fahrt
SET gebuchte_plaetze = @gebuchte_plaetze
WHERE fahrtid = @fahrtid

/*
EXEC Aufg5b
@fahrtid = 14,
@gebuchte_plaetze = 40

SELECT * FROM fahrt
*/

-- 5c
CREATE PROC Aufg5c
@fahrtid int 
AS DELETE FROM fahrt
WHERE fahrtid = @fahrtid

/*
EXEC Aufg5c
@fahrtid = 14

SELECT * FROM fahrt
*/

-- 6
CREATE TABLE Wartungsfirmen 
(FirmenID nvarchar(55) NOT NULL
,Firmenname nvarchar(255) NOT NULL
,Standort nvarchar(255) NULL
,Ansprechpartner varchar(255) NULL
,Telefon nvarchar(255) NULL
,Fax nvarchar(255) NULL)

-- 7a
ALTER TABLE Wartungsfirmen
ADD PRIMARY KEY(FirmenID)
-- sp_helpconstraint Wartungsfirmen

-- 7b
ALTER TABLE Wartung
-- WITH NOCHECK
ADD FOREIGN KEY (gewartetvon)
REFERENCES Wartungsfirmen
-- sp_helpconstraint Wartung
/* WITH NOCHECK erforderlich wenn keine passenden Daten 
   in der Tabelle mit dem Primary Key vorhanden sind! */

-- 7c
ALTER TABLE Wartung
ADD DEFAULT getdate() FOR gewartetam
-- sp_helpconstraint Wartung

-- 7d
ALTER TABLE Wartung
ADD CHECK (kosten > 0)
-- sp_helpconstraint Wartung

-- 7e
ALTER TABLE Wartungsfirmen
ADD UNIQUE (Firmenname)
-- sp_helpconstraint Wartungsfirmen