-- 4aCREATE VIEW Aufg4a ASSELECT busid,(SELECT fahrtziel FROM fahrtziel AS i WHERE i.fahrtzielid = a.fahrtzielid) AS [Fahrtziel], fahrtdatum, gebuchte_plaetzeFROM fahrt AS a WHERE gebuchte_plaetze < 50-- SELECT * FROM Aufg4a-- 4bCREATE VIEW Aufg4b ASSELECT TOP 5 * FROM wartung WHERE maengel LIKE '%Bremsen%' AND year(gewartetam) = 2006 ORDER BY kosten DESC-- SELECT * FROM Aufg4b-- 4cCREATE VIEW Aufg4c ASSELECT count(a.fahrtzielid) AS [Anzahl Buchungen], i.fahrtziel FROM fahrt AS a JOIN fahrtziel AS i ON a.fahrtzielid = i.fahrtzielidGROUP BY i.fahrtziel-- SELECT * FROM Aufg4c-- 4dCREATE VIEW Aufg4d ASSELECT avg(a.gebuchte_plaetze) AS [Durchschnittliche Belegung], i.fahrtziel FROM fahrt AS a JOIN fahrtziel AS i ON a.fahrtzielid = i.fahrtzielidGROUP BY i.fahrtziel-- SELECT * FROM Aufg4d-- 4eCREATE VIEW Aufg4e ASSELECT a.vorname + ' ' + a.name AS [Name], a.wohnort, a.strasse, i.fahrtdatum, i.fahrtzielid FROM Fahrer AS aJOIN Fahrt AS iON i.fahrerid = a.fahrerid-- SELECT * FROM Aufg4e-- 4f CREATE VIEW Aufg4f ASSELECT 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 aJOIN Fahrt AS iON i.fahrerid = a.fahrerid AND year(i.fahrtdatum) IN (2005,2006)GROUP BY a.name, a.gehalt, i.fahrtzielid-- SELECT * FROM Aufg4f-- 5aCREATE PROC Aufg5a@fahrtid int, @busid int, @fahrerid int, @fahrtzielid nvarchar(5), @fahrtdatum datetime, @gebuchte_plaetze intAS INSERT INTO fahrtVALUES (@fahrtid, @busid, @fahrerid, @fahrtzielid, @fahrtdatum, @gebuchte_plaetze)/* EXEC Aufg5a@fahrtid = 14,@busid = 21,@fahrerid = 1,@fahrtzielid = 'ROMA',@fahrtdatum = '01.01.2008',@gebuchte_plaetze = 50SELECT * FROM fahrt*/-- 5bCREATE PROC Aufg5b@fahrtid int, @gebuchte_plaetze intAS UPDATE fahrtSET gebuchte_plaetze = @gebuchte_plaetzeWHERE fahrtid = @fahrtid/*EXEC Aufg5b@fahrtid = 14,@gebuchte_plaetze = 40SELECT * FROM fahrt*/-- 5cCREATE PROC Aufg5c@fahrtid intAS DELETE FROM fahrtWHERE fahrtid = @fahrtid/*EXEC Aufg5c@fahrtid = 14SELECT * FROM fahrt*/-- 6CREATE 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)-- 7aALTER TABLE WartungsfirmenADD PRIMARY KEY(FirmenID)-- sp_helpconstraint Wartungsfirmen-- 7bALTER TABLE Wartung-- WITH NOCHECKADD FOREIGN KEY (gewartetvon)REFERENCES Wartungsfirmen-- sp_helpconstraint Wartung/* WITH NOCHECK erforderlich wenn keine passenden Daten in der Tabelle mit dem Primary Key vorhanden sind! */-- 7cALTER TABLE WartungADD DEFAULT getdate() FOR gewartetam-- sp_helpconstraint Wartung-- 7dALTER TABLE WartungADD CHECK (kosten > 0)-- sp_helpconstraint Wartung-- 7eALTER TABLE WartungsfirmenADD UNIQUE (Firmenname)-- sp_helpconstraint Wartungsfirmen