SQL-Tutorial

OFFSET - von Zeile x nach Zeile y

ORDER BY Kontinent ASC, Teilregion DESC
OFFSET 29 ZEILEN HOLT NUR DIE NÄCHSTEN 50 ZEILEN;

WITH TIES - Unentschieden auf dem letzten Platz

WÄHLEN SIE DIE ERSTEN 10 PROZENT MIT KRAWATTEN;

COALESCE - 1. Nicht-NULL-Wert

SELECT COALESCE(NULL,NULL,2,5,NULL)
COALESCE(PickedByPersonID,BackorderOrderID);

WHERE - Zeichenersetzungen

Platzhaltersuche (%) - SQL Server | Microsoft Learn

WHERE Lagerbestandsname LIKE '%ham[^m]%'
WHERE Lagerbestandsname LIKE 'a[l-m]%[l-p]'
WHERE Ländername LIKE 'H__[g,d]%'

WHERE IN - ist es in einer Liste enthalten

WHERE PickedByPersonID IN (3,4,17) AND OrderDate >= '2013-01-01' AND OrderDate <= '2013-12-30'

AGGREGATABFRAGEN - AVG(), SUM(), MIN(), MAX(), COUNT(), COUNT_BIG()

SUM(Menge) AS total_pack, AVG(Stückpreis) AS avg_unitprice, COUNT(OrderLineID) AS cnt_orderline

AGGREGATOREN - NULL nicht behandeln

SELECT COUNT(ISNULL(KommissionierungErledigtWann,'1970-01-01'))
SELECT COUNT_BIG(*) FROM Umsatz.Auftragszeilen;
COUNT(CASE WHEN PickingCompletedWhen IS NULL THEN 1 ELSE NULL END) AS unkown_values

Unterabfrage - in sich geschlossen/einfach

SELECT OrderId, AVG(Menge) as avg_perorderid
FROM Verkäufe.OrderLines
GROUP BY OrderId
HAVING AVG(Menge) > (SELECT AVG(Menge) AS avg_q
FROM Sales.OrderLines)
ORDER BY avg_perorderid ASC;

SELECT OrderId, (SELECT AVG(Menge) AS avg_q FROM Sales.OrderLines)
FROM Sales.OrderLines;

korrelierte Unterabfrage - letzte OrderID 

SELECT CustomerId, OrderId, OrderDate
FROM Verkauf.Aufträge o1
WHERE OrderID=(SELECT MAX(OrderID)
FROM Verkäufe.Aufträge o2
WHERE o1.CustomerId=o2.CustomerId)
Reihenfolge nach orderDate DESC;

Rückläufe mit Liste

SELECT OrderID, CustomerId, OrderDate FROM Sales.Orders
WHERE CustomerID IN (SELECT CustomerID FROM Sales.Customers WHERE CreditLimit IS NULL OR CreditLimit < 2000)
ORDER BY CustomerID ASC;

EXISTS

SELECT DISTINCT KundenID, Kundenname
FROM Vertrieb.Kunden c
WHERE PostalCityID = 33832
AND EXISTS
(SELECT * FROM Verkauf.Aufträge o
WHERE o.CustomerID=c.CustomerID);

running-total - gordulo lekerdezes

SELECT StadtID, Stadtname, (SELECT MIN(StadtID) FROM Anwendung.Städte c2 WHERE c2.StadtID > c1.StadtID) AS nextcityid
FROM Anwendung.Städte c1
ORDER BY CityID ASC;
--javitani
SELECT CityID, CityName, LatestRecordedPopulation, LatestRecordedPopulation + (SELECT SUM(LatestRecordedPopulation) FROM Application.Cities c2 WHERE c1.CityID=c2.CityID) AS run_total
FROM Anwendung.Städte c1
WHERE StateProvinceID IN (SELECT StateProvinceID FROM Application.StateProvinces WHERE StateProvinceName='Colorado')
GROUP BY CityID, CityName, LatestRecordedPopulation;

DERIVED TABLE Version

SELECT auftrag_zahl, monat_nummer
FROM (
SELECT MONAT(Bestelldatum) AS month_number, COUNT(Bestelldatum) AS order_count
FROM Umsatz.Aufträge
GROUP BY MONAT(Bestelldatum)
) AS derivtable
WHERE auftrag_zahl > 6000
ORDER BY month_number ASC;

SELECT auftrag_zahl, monat_nummer
FROM (
SELECT MONAT(Datum der Bestellung), COUNT(Datum der Bestellung)
FROM Umsatz.Aufträge
GROUP BY MONAT(Bestelldatum)
) AS abgeleitete Tabelle (monat_nummer,auftrag_zahl)
WHERE auftrags_zahl > 6000
ORDER BY monat_nummer ASC;

SELECT CustomerID, ORderID, OrderDate
FROM Vertrieb.Aufträge AS a -- a=schema_name.tabellenname
INNER JOIN
(
SELECT MAX(BestellDatum) AS maxBestellDatum
FROM Verkäufe.Aufträge AS b
) AS derivedtable ON a.orderDate=derivedtable.maxorderdate;

CTE

WITH cte_table AS (
SELECT MONAT(Bestelldatum) AS MONAT_NUMMER, COUNT(Bestelldatum) AS BESTELLUNG_ZAHL
FROM Verkauf.Aufträge
GROUP BY MONAT(Bestelldatum)
)
SELECT auftrag_zahl, monat_nummer FROM cte_table
WHERE auftrag_zahl > 6000
ORDER BY monat_nummer ASC;

FALL - NORMAL - ABGELEITETE TABELLE - CTE 

SELECT CASE
WHEN 2013=YEAR(Bestelldatum) THEN '2013y'
WHEN 2014=FORMAT(Bestelldatum,'jjjj') THEN '2014j'
WHEN Bestelldatum wie '2015%' THEN '2015j'
WHEN Bestelldatum >= '20160101' AND Bestelldatum = '20160101' AND Bestelldatum = '20160101' AND Bestelldatum = '20160101' AND Bestelldatum < '2017010101' THEN '2016y'
END as Jahre
FROM Umsatz.Aufträge)
SELECT jahre, COUNT(jahre)
FROM was auch immer
GROUP BY years;

JOIN AND - Dies wäre die Wo in und Beziehung

SELECT *
FROM dbo.Varosok v JOIN dbo.Orszagok o ON v.OrszagID = o.Id AND o.Nev='USA'

Boss Boss JOIN

SELECT untergeordnet.Nev, Zöpfe.Nev, Zöpfe.Nev FROM dbo.people as untergeordnet
INNER JOIN dbo.People as fonok ON subordinate.FonokId = fonok.Id
LEFT JOIN dbo.People as majorfonok ON fonok.Fonok.FonokId = majorfonok.Id

Datenlänge

SELECT emp_name,
LEN(mitarbeiter_name) LEN
, DATALENGTH(mitarbeiter_name) data_length
FROM dbo.mitarbeiter;

EINDEUTIGER BEZEICHNER

CREATE TABLE #testunique(
id UNIQUEIDENTIFIER DEFAULT NEWID()
, name varchar(100)
);

autocreate id

DROP TABLE IF EXISTS HR.Mitarbeiter
CREATE TABLE HR.Mitarbeiter (
id int IDENTITY(1,1)
, emp_name NVARCHAR(200)
)

EIGENER TYP

CREATE TYPE hunschema.hun_mySSN FROM varchar(9);
CREATE TABLE hunschema.testtable (
id INT NOT NULL
, socnumberHUN hunschema.hun_mySSN
);
INSERT INTO hunschema.testtable
WERTE (1, '123456789');

Problem mit Autoinkrement und INSERT vermeiden

SET IDENTITY_INSERT Hr.Employees ON;

INSERT INTO HR.Mitarbeiter (id,emp_name)
VALUES(666,'vorname');

SET IDENTITY_INSERT Hr.Employees OFF;

OUTPUT eingefügt, gelöscht

INSERT INTO HR.Mitarbeiter (mitarbeiter_name)
OUTPUT inserted.mitarbeiter_name INTO HR.Mitarbeiterprotokoll(mitarbeiter_name)
VALUES ('testname')

DELETE Hr.Mitarbeiter
OUTPUT gelöscht.emp_name INTO HR.Emplog
WHERE id=666;

DELETE testlines where orderdate='20130101′ > LOG 

DELETE dbo.testlines
OUTPUT gelöscht.*
FROM dbo.testlinien tol
INNER JOIN dbo.testorders tor ON tor.OrderID=tol.OrderID
WHERE OrderDate='20130101'

PRIMÄRSCHLÜSSEL, FREMDSCHLÜSSEL, EINDEUTIGE SCHLÜSSEL, BESCHRÄNKUNGEN

PRIMARY KEY

/* v1 */
ALTER TABLE dbo.employees
ALTER COLUMN emp_id int NOT NULL;

ALTER TABLE dbo.employees
ADD CONSTRAINT PK_employee_empid PRIMARY KEY (emp_id);
/* v2 */
ALTER TABLE dbo.employees
ADD emp_id int IDENTITY(1,1) NOT NULL
CONSTRAINT PK_employees_emp PRIMARY KEY;
/* v3 */
GO
CREATE TABLE dbo.testtable4(
id INT NOT NULL PRIMARY KEY
);
/* v4 */
DROP TABLE IF EXISTS dbo.testtable4;
CREATE TABLE dbo.testtable4(
id INT NOT NULL IDENTITÄT(1,1
,name varchar(50)
,CONSTRAINT PK_testtable4_id PRIMARY KEY(id)
);

FREMDSCHLÜSSEL - 1 > N - PRIMÄRSCHLÜSSEL

ALTER TABLE dbo.employees
ADD CONSTRAINT FK_employees_depid FOREIGN KEY(dep_id) REFERENCES dbo.departments(dep_id);

Kaskadenversion

ALTER TABLE dbo.employee
ADD CONSTRAINT FK_employees_depid FOREIGN KEY(dep_id) REFERENCES dbo.departments(dep_id) ON UPDATE CASCADE; -- ON DELETE CASCADE

Wechseltabelle - Arbeitnehmer und Ausbildungen - N > M

DROP TABLE IF EXISTS dbo.conn_educations_employees;
CREATE TABLE dbo.conn_educations_employees (
id INT NOT NULL IDENTITÄT(1,1)
CONSTRAINT PK_conn_eduemp_id PRIMARY KEY
,edu_id INT NOT NULL
CONSTRAINT FK_conn_edu_eduid FOREIGN KEY (edu_id) REFERENCES dbo.educations(edu_id)
,emp_id INT NOT NULL
CONSTRAINT FK_conn_edu_empid FOREIGN KEY (edu_id) REFERENCES dbo.employees(emp_id)
);

1 > 1

DROP TABLE IF EXISTS dbo.itusers;
CREATE TABLE dbo.itusers(
id int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_itusers_id PRIMARY KEY
, benutzername VARCHAR(50) NOT NULL
CONSTRAINT UK_itsuser_username UNIQUE
, emp_id INT
CONSTRAINT FK_itusers_empid FOREIGN KEY(emp_id) REFERENCES dbo.employees(emp_id)
CONSTRAINT UK_itusers_empid UNIQUE (emp_id)
);

UNIQUE SCHLÜSSEL

ALTER TABLE dbo.employees
ADD CONSTRAINT UK_employees_person UNIQUE (mitarbeiter_name,geburtsdatum,mutter_name);

PRÜFBESCHRÄNKUNG

ALTER TABLE dbo.employees
ADD CONSTRAINT CK_employees_birthdate CHECK ( birth_date > '19600101');

Funktioniert eine Prüfbeschränkung?

ALTER TABLE dbo.employees WITH CHECK
CHECK CONSTRAINT CK_employees_contacts;

STANDARD-ZWANGSBEDINGUNG

ALTER TABLE dbo.employees
ADD CONSTRAINT DK_phonenumber DEFAULT '0690111111' FOR phone_number;

temporale tabelle sql2017+ - advanced sql server 2019 cu18

CREATE TABLE dbo.persondate (
id int IDENTITY(1,1) PRIMARY KEY NOT NULL
,nachname NVARCHAR(50) NOT NULL
,vor_name NVARCHAR(50) NOT NULL
,telefon_nummer VARCHAR(22)
,email-Adresse VARCHAR(50)
,sysfirstdate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
,sysenddate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME(sysfirstdate,sysenddate)
) WITH (
SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 3 MONATE
,HISTORY_TABLE = dbo.persondate_history)
)
/* temporale Tabelle - gelöscht*/
SELECT * FROM dbo.persondate_history temp
LEFT JOIN dbo.persondate actual ON temp.id=actual.id
WHERE actual.id IS NULL;

INDEX

INDEX-Typen:
- In-Memory-Index: oltp
- columnstore index
- rowstore: geclustert 1 , nicht geclustert

Rowstore-Index-Typen:
- geclusterter Index - Telefonbuch, vollständige Tabelle
- nicht geclustert: heap - heap, wenn es keinen geclusterten Index für die Tabelle gibt
Index, z.B. welche Seite das Wort "Rechtsstaatlichkeit" auf Seite 120,130 enthält

Indexknoten: Indexschlüssel, Adresse
Blattebene: Tisch
Wurzel > Zwischenknoten > Blattknoten

nicht geclusterter Index: B-Baum

Blattebene: Indexschlüssel, geclusterter Index

gut für die Suche im Indexschlüssel, aber Sie können nicht mit Clustering davonkommen

GECLUSTERTER INDEX

DROP INDEX idx_testlines_clustered ON dbo.testlines;
CREATE CLUSTERED INDEX idx_testlines_clustered ON dbo.testlines(OrderLineID DESC);

Nicht-geclusterte Indizes sind gut, wenn Sie eine Abfrage in "Where" machen, eine Abfrage, die Sie oft verwenden

CREATE CLUSTERED INDEX idx_testlines_clustered ON dbo.testlines(OrderLineID DESC);

CREATE NONCLUSTERED INDEX idx_testlines_stockitemid ON dbo.testlines(StockItemID);

die Verwendung eines Indexes erzwingen - nicht verwenden

Kosten: 2,24

SELECT OrderLineID, StockItemID,Stückpreis, Menge
FROM dbo.testlines WITH (INDEX (idx_testlines_stockitemid))
WHERE StockItemID=148;

Blattniveau ist im UnitePrice enthalten, Menge

DROP INDEX idx_testlines_stockitemid ON dbo.testlines;
CREATE NONCLUSTERED INDEX idx_testlines_stockitemid ON dbo.testlines(StockItemID) INCLUDE (UnitPrice,Quantity);

Gefilterter Index

CREATE UNIQUE INDEX idx_testorders_isinprogress ON dbo.testorders (CustomerID,isInProgress) WHERE isInProgress=1;
/*FOREIGN KEY > kein Index ! beste Praxis - nicht geclusterter Index gefiltert testdb istHIgh Bildungstabelle-ben*/
/*vielleicht Masterwork - nicht gut für Datum*/
CREATE INDEX idx_testorders_2016 ON dbo.testorders (OrderDate) INCLUDE(CustomerID) WHERE OrderDate >= '20160101' AND OrderDate<'20170101';

Variablen

Stapelverarbeitung und Variablen (GO)
- zwischen zwei GOs liegt ein Stapel
- Variablen leben nur in einer Charge
DECLARE @stockItemId INT;
SET @stockItemId = 2;

DECLARE @maxValidTo DATETIME2 = '2020-02-02';

Als Filterbedingung, auch bei Mehrfachabfragen

SELECT * FROM Warehouse.StockItems
WHERE StockItemID = @stockItemId;

DECLARE @myStockItemName NVARCHAR(500);

SET @myStockItemName = (SELECT StockItemName FROM Warehouse.StockItems
WHERE StockItemID = @stockItemId);

SELECT @myStockItemName AS MyStockItemName;
/* Kann nur einen Wert speichern */
/* Dasselbe wie oben */
DECLARE @stockItemId INT;
SET @stockItemId = 2;
DECLARE @myStockItemName NVARCHAR(500);
SELECT @myStockItemName=StockItemName FROM Warehouse.StockItems
WHERE StockItemID = @stockItemId;

nimmt den letzten Wert, wenn er in einem select

DECLARE @myStockItemName NVARCHAR(500);
SELECT @myStockItemName=StockItemName FROM Warehouse.StockItems;

DECLARE @myStockItemName NVARCHAR(500);
DECLARE @myStockItemColorId NVARCHAR(500);

SELECT @myStockItemName=StockItemName, @myStockItemColorId=ColorID FROM Warehouse.StockItems;

SELECT @meinBestandsartikelName=BestandsartikelName, @meinBestandsartikelFarbeId=FarbeID
FROM Warehouse.StockItems
ORDER BY LagerartikelName;

SELECT @myStockItemName AS MyStockItemName;
SELECT @myStockItemColorId AS MyStockColorId;

Wenn es kein Ergebnis der Abfrage gibt, nimmt die Variable den Wert NULL an

SELECT StockItemName FROM Warehouse.StockItems
WHERE StockItemID = 10000000;

DECLARE @myStockItemName NVARCHAR(500);
SET @myStockItemName = (SELECT StockItemName FROM Warehouse.StockItems
WHERE StockItemID = 10000000);
SELECT @myStockItemName AS MyStockItemName;

DECLARE @myStockItemName NVARCHAR(500);
SELECT @meinLagerPositionsName=LagerPositionsName FROM Lager.LagerPositionen
WHERE StockItemID = 10000000;
SELECT @myStockItemName AS MyStockItemName;

DATEN

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

numerisch vs. float

Float-Approximator rundet, bei großen Zahlen ist der ganzzahlige Teil möglicherweise nicht genau, nur die ersten 7 sind genau, x*2^y => 199*2*5 - Physik, statistische Berechnungen für große Werte, z.B. Dezimalzahlen reichen nicht aus
numerisch/dezimal, was Sie bekommen, für finanzielle Dinge numerisch z.B. Preis ist 1,99

Geld - es ist eigentlich dezimal, nur mit vier Nachkommastellen, die fest sind - man kann die Währung schreiben, aber sie wird nicht gespeichert

DECLARE @mymoney_sm SMALLMONEY = 3148.29,
        @mymoney MONEY = 3148,29;
SELECT CAST(@mymoney_sm AS VARCHAR) AS 'SM_MONEY varchar',
        CAST(@mymoney AS DECIMAL) AS 'MONEY DECIMAL';

datetime vs datetime2

DECLARE @ido_datetime DATETIME = GETDATE();
DECLARE @ido_datetime2 DATETIME2 = GETDATE();

SELECT @ido_datetime, @ido_datetime2;

datumszeitoffset

DECLARE @ido_datetimeoffset datetimeoffset = GETDATE();
DECLARE @ido_datetimeoffset2 datetimeoffset = SYSDATETIMEOFFSET()
SELECT @ido_datetime, @ido_datetimeoffset,@ido_datetimeoffset2;

DECLARE @mostaniIdoUTC datetime2=GETUTCDATE();
SELECT @ido_datetime, @ido_datetimeoffset,@ido_datetimeoffset2,@mostaniIdoUTC;

char vs varchar

DECLARE @mychar CHAR(3)='test'
SELECT @mychar;

DECLARE @mychar CHAR(3)='te'
SELECT @mychar;

DECLARE @Meinzeichen CHAR(3)='te'
SELECT LEN(@mychar);
SELECT DATALENGTH(@mychar);

DECLARE @myvarchar VARCHAR(3)='test'
SELECT @myvarchar;

DECLARE @myvarchar VARCHAR(3)='te'
SELECT @myvarchar;

DECLARE @myvarchar VARCHAR(3)='te'
SELECT LEN(@myvarchar);
SELECT DATALENGTH(@myvarchar);

GO

nchar vs. nvarchar

DECLARE @mychar NCHAR(3)='test'
SELECT @mychar;

DECLARE @mychar NCHAR(3)='te'
SELECT @mychar;

DECLARE @mychar NCHAR(3)='te'
SELECT LEN(@mychar);
SELECT DATALENGTH(@mychar);

DECLARE @myvarchar NVARCHAR(3)='test'
SELECT @myvarchar;

DECLARE @myvarchar NVARCHAR(3)='te'
SELECT @myvarchar;

DECLARE @myvarchar NVARCHAR(3)='te'
SELECT LEN(@myvarchar);
SELECT DATALENGTH(@myvarchar);

binär

DECLARE @myBinary BINARY(10) = 0x00A1B2C3;
SELECT @myBinary;

Unicode-Zeichen in Ascii umwandeln

DECLARE @mychar CHAR(3) = 'Győr';
DECLARE @mychar2 CHAR(3) = 'Győr';
SELECT @michar, @michar2, LEN(@michar), DATALENGTH(@michar),LEN(@michar2), DATALENGTH(@michar2);

zu hohe Werte

DECLARE @chunk SMALLINT = 32768;
SELECT @darabszam;

 implizite Umrechnung

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16

CAST vs. CONVERT

DECLARE @chunk SMALLINT = 32764;
SELECT @darabszam;
SELECT CAST(@darabszam AS nvarchar)+'?';

DECLARE @ma DATETIME2 =GETUTCDATE();

SELECT @ma,CONVERT(nvarchar,@ma,1)

TRY_CAST gegenüber TRY_CONVERT

DECLARE @chunk SMALLINT = 32764;
SELECT @darabszam;
SELECT TRY_CAST(CAST(@darabszam AS nvarchar)+'?' AS INT);

FORMAT

DECLARE @ma DATETIME2 =GETUTCDATE();

SELECT @ma,FORMAT(@ma,'jjjj. MM. tt.')

DECLARE @datum DATETIME2 = '2022-02-15', @datum2 DATETIME2;
SET @datum2 =CONVERT(DATETIME2, '2/15/2022',101)
SELECT @datum,@datum2

DECLARE @float FLOAT = 2.0 / 5;
SELECT @Gleitkomma,FORMAT(@Gleitkomma,'N2')

DIGITALE Operationen: TRIM,LTRIM,RTRIM

len schaut hier nicht auf das Ende des Textes:

SELECT TRIM(' a '),LEN(' a '), LEN(TRIM(' a '));
SELECT LTRIM(' a '),LEN(' a '), LEN(LTRIM(' a '));
SELECT RTRIM(' a '),LEN(' a '), LEN(RTRIM(' a '));

SELECT TRIM(' a '),LEN(' a '), LEN(TRIM(' a ')));
SELECT LTRIM(' a '),DATALENGTH(' a '), DATALENGTH(LTRIM(' a '));
SELECT RTRIM(' a '),LEN(' a '), LEN(RTRIM(' a '));

OBEN, UNTEN

SELECT UPPER('Akzent')
SELECT LOWER('SATZ')

REPLIZIEREN,LINKS, UMKEHREN, ERSETZEN

SELECT REPLICATE('ho',3)
SELECT LEFT('HALLO WELT', 5)

SELECT REVERSE('hallo')

SELECT REPLACE('HALLO Welt', 'Welt', 'JOE')

UPDATE Warehouse.StockItems
SET StockItemName = REPLACE(StockItemName,'StockItemName', 'launcher')

Datum-Zeit-Operationen: DATEDIFF

SELECT GültigAb, '2022-01-01 00:00', DATEDIFF(jjjj, GültigAb,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT GültigAb, '2022-01-01 00:00', DATEDIFF(s, GültigAb,'2022-01-01 00:00')
FROM Lager.StockItems

SELECT GültigAb, '2022-01-01 00:00', DATEDIFF(s, GültigAb,'2022-01-01 00:00')
FROM Lager.StockItems

SELECT DATEDIFF(s,'2022-01-01 00:00','2022-01-01 05:01')

Rundung

SELECT ROUND(123.45,0)
WÄHLEN SIE ROUND(123.55,0)
WÄHLEN SIE ROUND(123,55,1)

TEMPORÄRE TABELLE, TABELLENVARIABLE

Aushilfstabelle

existiert nur in Session, speichert in tempdb, zu große tempdb für viele, um Abfragen von Tabellen zu speichern, Vorteil: gefilterte Daten, schnellere Abfragen, weniger Code, Performance

CREATE TABLE #myTempTable
(
	Id eindeutiger Bezeichner Primärschlüssel nicht geclustert,
	Name NVARCHAR(100) NOT NULL
);

Globales temporäres Talbe: bleibt zwischen den Sitzungen bestehen, wird nach Beendigung aller Abfragen und Sitzungen beendet

CREATE TABLE ##globalTempTable
(
	Id eindeutiger Bezeichner Primärschlüssel nicht geclustert,
	name NVARCHAR(100) NOT NULL
);
DROP TABLE ##globalTempTable;

Tabellenvariable: gibt es nur im Batch, nicht ideal für große Datenmengen

DECLARE @meineTabelleVariable TABLE (
	Id eindeutiger Bezeichner Primärschlüssel nicht geclustert,
	Name NVARCHAR(100) NOT NULL
);
-- Erstellung einer gemeinsamen Tafel
USE WideWorldImporters;
GO

SELECT COUNT(*) FROM Verkauf.Aufträge;
-- 0,39 Kosten
SELECT COUNT(*) FROM Sales.OrderLines;

SELECT TOP 1 * FROM Verkäufe.Auftragszeilen;

CREATE TABLE #Temp_OrderLines
(
	OrderLineID int PRIMARY KEY,
	StockItemID int NOT NULL, --/*FOREIGN KEY*/ Referenzen Warehouse.StockItems(StockItemID), --dies nicht tun, weil es keinen Namen hat, Sie müssen den Namen finden
	BESCHREIBUNG NVARCHAR(1000),
	Menge int NOT NULL
);
-- 164 Lagerartikel-ID
INSERT INTO #Temp_OrderLines(OrderLineID,StockItemID,[DESCRIPTION],Quantity)
SELECT OrderLineID,StockItemID,[Beschreibung],Menge
FROM Verkauf.OrderLines
WHERE StockItemID=164;

SELECT * FROM #Temp_OrderLines;
--0.013 Kosten
SELECT COUNT(*) FROM #Temp_OrderLines;

INSERT INTO #TTemp_OrderLines(OrderLineID,StockItemID,[DESCRIPTION],Quantity)
VALUES (10000,1,'Test', 1);

SELECT COUNT(*) FROM #Temp_OrderLines;

DECLARE @meineTabelleVariable TABLE (
	Id eindeutiger Bezeichner Primärschlüssel nicht geclustert,
	Name NVARCHAR(100) NOT NULL
);

INSERT INTO @meineTabelleVariable (Id,Name) VALUES (NEWID(), 'Teszt');

SELECT * FROM @meineTabelleVariable;

USE WideWorldImporters;

/*
    Deklaration eines Tabellentyps
*/
DROP TYPE IF EXISTS mytable;
CREATE TYPE mytable AS table
(
    id int NOT NULL,
	name NVARCHAR(100) NOT NULL
);

/*
    Von nun an müssen Sie Ihre Tabellenvariablen nicht mehr mit Spalteninformationen deklarieren
    mit Spalteninformationen deklarieren, sondern nur noch den Typ referenzieren.
*/
DECLARE @t AS mytable;
INSERT INTO @t VALUES(1,'HELLO WORLD');

SELECT * FROM @t;


CREATE TABLE dbo.testtable AS mytable; -- nein

/*das ist ja*/
SELECT *
INTO dbo.testtable
FROM @t
WHERE 1 = 2; -Spalten ja, aber nicht Zeilen


SELECT * FROM dbo.testtecske;

KONTROLLFLUSS-ELEMENTE (IF, WHILE)

Code-Verzweigung

WENN...ELSE
IF-Ausdrücke

- dreiwertige Logik: TRUE, FALSE, UNKNOWN (NULL)
einfaches Wenn-Beispiel

DECLARE @year SMALLINT;
SET @year = 2022;

IF @year = YEAR(GETDATE())
BEGIN
PRINT 'Treffer! Das aktuelle Jahr ist: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    PRINT 'ES GIBT KEINE ÜBEREINSTIMMUNG! DIE Jahresvariable:' + CAST(@year AS varchar(5));
    PRINT 'ES GIBT KEINE ÜBEREINSTIMMUNG! DAS aktuelle Jahr:' + CAST(YEAR(GETDATE()) AS varchar(5));
END

DECLARE @year SMALLINT;
SET @year = (SELECT YEAR(MAX(OrderDate)) FROM WideWorldImporters.Sales.Orders);

IF @year = YEAR(GETDATE())
BEGIN
PRINT 'Treffer! Das aktuelle Jahr ist: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    PRINT 'ES GIBT KEINE ÜBEREINSTIMMUNG! DIE Jahresvariable:' + CAST(@year AS varchar(5));
    PRINT 'ES GIBT KEINE ÜBEREINSTIMMUNG! DAS aktuelle Jahr:' + CAST(YEAR(GETDATE()) AS varchar(5));
END

NULL-Behandlung verschachtelter if-Elemente

DECLARE @year SMALLINT;
SET @year = (SELECT YEAR(MAX(OrderDate)) FROM WideWorldImporters.Sales.Orders WHERE OrderID=500000);

IF @year = YEAR(GETDATE())
BEGIN
PRINT 'Treffer! Das aktuelle Jahr ist: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    IF @year IS NOT NULL
    BEGIN
    PRINT 'ES GIBT KEINE ÜBEREINSTIMMUNG! DIE Variable Jahr:' + CAST(@year AS varchar(5));
    PRINT 'ES GIBT KEINE ÜBEREINSTIMMUNG! DAS aktuelle Jahr:' + CAST(YEAR(GETDATE()) AS varchar(5));
    END
    ELSE
    BEGIN
    PRINT '@year Wert ist NULL'
    END
END
GO

Aktive DB, nur für die eigene, andere Sitzungen können sie noch nutzen, löschen

DECLARE @year SMALLINT;
SET @year = 2023;
IF @year = YEAR(GETDATE())
BEGIN
    USE WideWorldImporters
END

Komplexer if-Ausdruck

letzter Tag des letzten Monats = heute-20 || heute ist IN (montags, dienstags, freitags) || heute = 2023-02-20
richtig oder falsch

PRINT DAY(GETDATE())
PRINT EOMONTH(GETDATE(),-1); -- Februar
PRINT DATEDIFF(dd, GETDATE(),1 )
PRINT CAST(DATEADD(day, -20,GETDATE()) AS date);
PRINT DATENAME(dw,GETDATE())
PRINT GETDATE()

IF EOMONTH(GETDATE(),-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,GETDATE()) IN ('Montag','Dienstag', 'Freitag') OR CAST(GETDATE() AS date) = '2023-02-20'
BEGIN
PRINT 'DAS IST WAHR!'
END

/*testcase-2*/

IF EOMONTH('2023-03-20',-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,'2023-02-19') IN ('Montag','Dienstag', 'Freitag') OR CAST(GETDATE() AS date) = '2023-02-20'
BEGIN
PRINT 'DIES IST WAHR!'
PRINT 'DIES IST IMMER NOCH WAHR!'
END

/*Test-Fall 3*/

IF EOMONTH('2023-03-20',-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,'2023-02-19') IN ('Montag','Dienstag', 'Freitag') OR CAST(GETDATE() AS date) = '2023-01-28'
BEGIN
PRINT 'DIES IST WAHR!'
PRINT 'DIES IST IMMER NOCH WAHR!'
END
ELSE
BEGIN
PRINT 'NICHT WAHR'
END
DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

IF EXISTS

Verwenden Sie WideWorldImporters;
GO

DECLARE @yeara SMALLINT=2015, @yearb SMALLINT=2023;

IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb)
)
BEGIN
PRINT 'Es gibt Aufträge in ' + CAST(@yeara as VARCHAR(5))+ ' oder in '+CAST(@yearb as VARCHAR(5))
END
ELSE
BEGIN
    PRINT 'Es liegen keine Aufträge vor in ' + CAST(@yeara as VARCHAR(5))+ ' oder in '+CAST(@yearb as VARCHAR(5))
END ;
/* Dieselbe Aufgabe mit einem kleinen NULL-Zusatz*/
DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb)
)
BEGIN
PRINT 'Es gibt Aufträge in ' + CAST(@yeara as VARCHAR(5))+ ' oder in '+CAST(@yearb as VARCHAR(5))
END
ELSE
BEGIN
    PRINT 'Es liegen keine Aufträge vor in ' + CAST(@yeara as VARCHAR(5))+ ' oder in '+CAST(@yearb as VARCHAR(5))
END ;

GO
/*NULLBEHANDLUNG*/

DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

IF (@yeara IS NULL OR @yearb IS NULL)
BEGIN
PRINT 'UNBEKANNTE(r) Wert(e) wurde(n) entdeckt';

END
ELSE
BEGIN
IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb) AND (@yeara IS NOT NULL OR @yearb IS NOT NULL)
)
BEGIN
PRINT 'Es gibt Bestellungen in ' + CAST(@yeara as VARCHAR(5))+ ' oder in '+CAST(@yearb as VARCHAR(5));
END
ELSE
BEGIN
    PRINT 'Es liegen keine Aufträge in ' + CAST(@yeara as VARCHAR(5))+ ' oder in '+CAST(@yearb as VARCHAR(5)) vor;
END
END;

LOOPS

Syntax:

WHILE (Ausdruck)
BEGIN

ENDE
(WEITER, PAUSE, GO 1000)

DROP TABLE IF EXISTS dbo.t1,dbo.t2,dbo.t3;
CREATE TABLE dbo.t1 (num int);
CREATE TABLE dbo.t2 (num int IDENTITY(1,1));
CREATE TABLE dbo.t3 (num int IDENTITY(1,1));

/* einfache Einfügeschleife INSERT 1-1000 > dbo.t1 Methode 1*/

DECLARE @cnt int=1;

WHILE @cnt 1000 except 5 WITH Autoincrement column (Methode 2)
 CONTINUE IF @cnt=5
*/

SET IDENTITY_INSERT dbo.t2 ON;

DECLARE @cnt int=0;
WHILE (@cnt < 1001)
BEGIN
    SET @cnt +=1;
    IF @cnt=5 CONTINUE;
    INSERT INTO dbo.t2 (num) VALUES (@cnt);
END

SELECT num FROM dbo.t2;

SET IDENTITY_INSERT dbo.t2 OFF;

/*
Schleife bei @cnt=5 unterbrechen
*/
TRUNCATE TABLE dbo.t2;

SET IDENTITY_INSERT dbo.t2 ON;

DECLARE @cnt int=0;
WHILE (@cnt  dbo.t3 Methode 3
*/
INSERT INTO dbo.t3 DEFAULT VALUES;
GO 1000

SELECT * FROM dbo.t3;

Schleife mit Verzögerung

DECLARE @cnt int=1;

WHILE @cnt <= 10
BEGIN

    PRINT 'Der Zählerstand ist '+CAST(@cnt AS varchar(5));
    WAITFOR VERZÖGERUNG '00:00:01'
     SET @cnt+= 1;
   

END

/*DAS IST GUT*/

DECLARE @cnt int=1;

WHILE @cnt <= 10
BEGIN

    RAISERROR('Der Zählerwert ist %d',0,1,@cnt) WITH NOWAIT;
    WAITFOR VERZÖGERUNG '00:00:01'
     SET @cnt+= 1;
END

@@ROWCOUNT

zählt die letzte Anweisung

DECLARE @rows int;
UPDATE #mytemp
SET Menge=1000
WHERE Menge>100;
SET @rows=@@ROWCOUNT;
Wenn @rows > 0
BEGIN
    SELECT CAST(@rows as varchar(5)) + 'Zeilen wurden aktualisiert'
END
ELSE
BEGIN
SELECT 'Es wurden keine Zeilen aktualisiert'
END

FEHLERBEHANDLUNG

Fehlerarten:
- Datentypkonvertierungsfehler
- Bewertungsfehler (Bewertungsfehler)
- Syntaxfehler > kann in t-sql nicht behandelt werden
- logischer Fehler
- Fehler bei der Namensauflösung > kann in t-sql nicht behandelt werden
- Fehler bei der Verletzung einer Nebenbedingung
- Fehler durch Auslöser

Menge -1000

Warum schreiben wir eine Fehlerbehandlung?
- Ich möchte den Code nicht verletzen
- Ich möchte den Fehler protokollieren
- den Fehlercode in einer leichter verdaulichen Form ausgeben

Wie?
- verlieren Sie den Code nicht
- oder den Code loswerden, um größere Probleme zu vermeiden

Methoden zur Fehlersuche:
- strukturierte Fehlerbehandlung mit: TRY > CATCH
- @@ERROR eingebaute Funktion, nicht verwenden

09:17:15Start der Ausführung der Abfrage in Zeile 42
Msg 8134, Ebene 16, Zustand 1, Zeile 1
Fehler beim Dividieren durch Null aufgetreten.
Ausführungszeit insgesamt: 00:00:00.013

Msg 8134 > Fehlercode (id)
Level > Schweregrade
Zustand > Status
Zeile > Zeile, in der der Fehler auftritt

Beispiel Division durch Null

PRINT 10/0;

TRY...CATCH

BEGIN TRY
    PRINT 10/0;
END TRY
BEGIN CATCH
    PRINT '0 ist dumm!';
END CATCH;

integrierte Funktionen zur Fehlerbehandlung

PRINT 'Fehler aufgetreten!';
PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10));
PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),");

BEGIN TRY
    PRINT 10/0;
END TRY
BEGIN CATCH
    PRINT 'Fehler aufgetreten!';
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),");

   BEGIN TRY
   EXEC dbo.badprocedure
   END TRY
   BEGIN CATCH

   END CATCH

Fehler bei der Namensauflösung

BEGIN TRY
    DECLARE @Stückpreis INT=0;
    SELECT * FROM dbo.notavailable;

    PRINT 10/@Stückpreis;
END TRY
BEGIN CATCH
    PRINT 'Fehler aufgetreten!';
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

Fehlerbehandlung bei der Namensauflösung durch das Modul

GO
CREATE OR ALTER PROCEDURE dbo.badprocedure
AS
    SELECT * FROM dbo.notavailable;
GO
EXEC dbo.badprocedure;
BEGIN TRY
    DECLARE @Stückpreis int=0;
    EXEC dbo.badprocedure;
    PRINT 10/@Einheitspreis;
END TRY
BEGIN CATCH
    PRINT 'Fehler aufgetreten!';
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

negativer Stückpreisfehler durch Behandlung von THROW

BEGIN TRY
    DECLARE @Stückpreis INT=-20;
    IF @Stückpreis < 0
    BEGIN
    -- THROW gültiger Bereich ab 50000, def. Schweregrad 16
        THROW 55555, 'Der Wert des Parameters @unitprice ist kleiner als Null!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Fehler aufgetreten!';
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

THROW in CATCH - Ich breche den Code, aber zuerst lösche ich die Fehlerursache

BEGIN TRY
    DECLARE @Stückpreis INT=-20;
    IF @Stückpreis < 0
    BEGIN
    -- THROW gültiger Bereich ab 50000, def. Schweregrad 16
        THROW 55555, 'Der Wert des Parameters @unitprice ist kleiner als Null!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Fehler aufgetreten!';
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + ' unitprice value causes the error';
    THROW;
END CATCH;

RAISE ERROR - in catch macht den Code nicht kaputt

BEGIN TRY
    DECLARE @myvar int;

    SET @myvar = -1;

    /* Auslösen eines Fehlers mit einem Argument, wenn der Wert nicht akzeptabel ist */
    IF @myvar < 0
        RAISERROR ('Der Wert des Parameters @myvar ist %d!', 16, 1, @myvar);

    PRINT 'Diese Zeile lässt sich nicht ausführen!'
END TRY
BEGIN CATCH
    PRINT 'Fehler aufgetreten!'
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

BEGIN TRY
    DECLARE @Stückpreis INT=-20;
    IF @Stückpreis < 0
    BEGIN
    -- THROW gültiger Bereich ab 50000, def. Schweregrad 16
        THROW 55555, 'Der Wert des Parameters @unitprice ist kleiner als Null!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Fehler aufgetreten!';
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + ' unitprice valuse causes the error';
    RAISERROR ('%d unitprice value causes the error!', 15, 1, @unitprice);
END CATCH;

RAISERROR kann in die Ereignisanzeige > Windows Log > Anwendungen integriert werden
eindeutigen Fehlercode erzeugen

BEGIN TRY
    DECLARE @Stückpreis INT=-20;
    IF @Stückpreis < 0
    BEGIN
    -- RAISERROR
        DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('%d unitprice value verursacht den Fehler! Ihr Fehlercode: %x', 16, 1, @unitprice,@myerrorstring) WITH LOG;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Fehler aufgetreten!
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
    SELECT CAST(@unitprice AS NVARCHAR(100)) + ' unitprice valuse causes the error';
    THROW;
END CATCH;

Fehlerbehandlung bei INSERT

USE testdb;
GO

DROP TABLE IF EXISTS dbo.military;
CREATE TABLE dbo.military(
    id INT IDENTITÄT(1,1) PRIMARY KEY
    ,soldat_name varchar(100) NOT NULL
    ,soldat_bdate date CHECK (soldat_bdate > '19780101')
)

BEGIN TRY
    INSERT INTO dbo.military (soldat_name,soldat_bdate)
    VALUES ('John Ramobo', '19770101')
    -- RAISERROR
END TRY
BEGIN CATCH
    WENN FEHLER_ZAHL()=547
    BEGIN
    DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('Check constraint violation. Ihr Fehlercode: %x', 16, 1,@myerrorstring) WITH LOG;
    END
    PRINT 'Fehler aufgetreten!
    PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
    PRINT 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)); PRINT 'Fehlermodul: ' + CAST(ERROR_LINE() AS nvarchar(10))
    PRINT 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;
/*beliebiger Fehler*/
BEGIN TRY
    INSERT INTO dbo.military (soldat_name,soldat_bdate)
    VALUES ('John Ramobo', '1970101')
    -- RAISERROR
END TRY
BEGIN CATCH
    DECLARE @newerrormessage NVARCHAR(1000);
    SET @newerrormessage = 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + CHAR(13) + 'Fehlermeldung: ' + ERROR_MESSAGE()+ CHAR(13) + 'Fehlerstatus: ' + CAST(ERROR_STATE() AS nvarchar(10))+ CHAR(13) + 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS nvarchar(10))+ CHAR(13) + 'Fehlerzeile: ' + CAST(ERROR_LINE() AS nvarchar(10)) + CHAR(13) + 'Fehlermodul: ' + COALESCE(ERROR_PROCEDURE(),'');
    DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('%s Check constraint violation. Your error code: %x', 16, 1,@newerrormessage,@myerrorstring) WITH LOG;
END CATCH;

verschachtelter try-catch-Block

BEGIN TRY
    PRINT 10/0;
END TRY
BEGIN CATCH
    BEGIN TRY
        INSERT INTO dbo.military (id,soldat_name,soldat_bdate)
        VALUES (1,'John Ramobo', '19790101')
    END TRY
    BEGIN CATCH
        THROW 55555, 'Der Wert des Parameters @unitprice ist kleiner als Null!', 2;
    END CATCH
END CATCH

ANSICHTEN

- Referenz, werden die Werte bei der Berechnung neu berechnet, es sei denn, es handelt sich um indizierte Ansichten

- Ich verstecke eine Ansicht vor den Benutzern, ich füge einige Spalten nicht in die Ansicht ein und gebe den Zugriff auf sie frei
- In der Praxis werden die Daten in einem separaten Schema gespeichert und der Zugriff darauf ermöglicht.

USE WideWorldImporters
GO
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Vertrieb.Kunden as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Lager.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
GO
DROP VIEW IF EXISTS FirsView;
GO
CREATE VIEW FirstView
AS
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Vertrieb.Kunden as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Lager.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
GO
SELECT * FROM FirstView;

MIT SCHEMABINDUNG, VERSCHLÜSSELUNG

SCHEMABINDING: wir können sie nicht so verändern, dass sie unbrauchbar wird, z. B. durch das Löschen von Spalten
ENKRYPTION: wir können Ihren Code nicht sehen (erstellen Sie ihn)

GO
CREATE VIEW SecondView
MIT SCHEMABINDUNG, VERSCHLÜSSELUNG
AS
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum
FROM Vertrieb.Kunden as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Lager.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
GO

ALTER VIEW

- Ansicht ohne Ablegen ändern - DROP VIEW IF EXISTS dbo.VarosokOrsz odok2;
-Verwenden Sie die VarosokOrszegok2Seite noch, es gibt noch SCHEMABIDING

GO
ALTER VIEW dbo.VarosokOrsz odours2
AS
SELECT v.Nev AS VarosNev
,o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id;
GO

INDEXIERTE ANSICHT

fortbestehen?

- die Ansicht muss deterministisch sein
- Schemabildung, auch für Benutzerfunktionen
- können nur Zeichen verwenden

in welchen Fällen? was sind die Vorteile? indexierte Ansicht
- befindet sich auf der Festplatte und ist daher schneller

Problem: schnellere Abfrage, wenn es eine komplizierte Verknüpfung gibt
Wenn Sie etwas ändern, müssen Sie auch die Ansicht ändern.

Wenn Sie mehr oder genauso viel in eine Tabelle schreiben, wie Sie lesen, sollten Sie sich nicht bewerben.

GO
CREATE VIEW dbo.NemDet
AS
SELECT Nev, GetDate() AS RandomDate
FROM dbo.Varosok
GO

SELECT * FROM dbo.NemDet

CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.NemDet(Nev);
-- nicht-deterministisch
GO
ALTER VIEW dbo.NemDet
MIT SCHEMABINDUNG
AS
SELECT Nev, GetDate() AS RandomDate
FROM dbo.Varosok
GO
CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.NemDet(Nev);

-- JOIN, kann nur für innere

DROP VIEW IF EXISTS dbo.TobbTabla;

GO
CREATE VIEW dbo.TobbTabla
MIT SCHEMABINDUNG
AS
SELECT v.Nev as VarosNev, o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id

GO
SELECT * FROM dbo.TobbTabla;

CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.TobbTabla(VarosNev,OrszagNev);

-- CRUD - Erstellen, Lesen, Aktualisieren, Löschen

-- ORDER BY - standardmäßig nicht möglich
-- ORDER BY - mit TOP
GO
CREATE VIEW dbo.ViewOrderBy
MIT SCHEMABINDUNG
AS
SELECT TOP 1 v.Nev as VarosNev, o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id
ORDER BY o.Nev ASC;
GO

Gespeicherte Prozeduren

- Eingaben
- Ausgänge
- Kann mehrmals aufgerufen werden

Vorteile von gespeicherten Prozeduren:

- Parameterübergabe
- erstellt einen > Ausführungsplan für eine gespeicherte Prozedur - Leistung - Parameter-Sniffing - die Verwendung eines anderen Parameters kann sehr langsam sein > MIT RECPOMPILE (neuer Ausführungsplan für jeden Aufruf)
- können Sie daraus einen separaten Fall machen
-https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

- für jeden Parameter muss der Typ

GO
ALTER VIEW dbo.VarosokOrsz odours2
AS
SELECT
v.Nev as VarosNev,
o.Nev as OrszagNev
FROM
dbo.Varosok as v
JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
WHERE v.Nev LIKE 'B%'
GO

CREATE PROCEDURE dbo.TaroltEljarasVarosokOrszagok
AS
BEGIN
	SELECT
	v.Nev as VarosNev,
	o.Nev as OrszagNev
	FROM
	dbo.Varosok as v
	JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
	WHERE v.Nev LIKE 'B%'
END

SELECT * FROM dbo.VarosokOrszagok2;

EXEC dbo.TaroltEljarasVarosokOrszagok;

CREATE TABLE #Eredmeny1
(
	VarosNev NVARCHAR(100),
	OrtNev NVARCHAR(100),
)

INSERT INTO #Eredmeny1
EXEC dbo.TaroltEljarasVarosokOrszagok

SELECT * FROM #Eredmeny1
GO
CREATE PROCEDURE dbo.VarosKereses(@Suchname NVARCHAR(100))
AS
BEGIN
	SELECT
	v.Nev as VarosNev,
	o.Nev as OrszagNev
	FROM
	dbo.Varosok as v
	JOIN dbo.Orszagok as o ON v.OrszagId = o.Id
	WHERE v.Nev LIKE @Suchname
END
GO

EXEC dbo.VarosKereses 'B%'
EXEC dbo.VarosKereses 'V%'

Was ist eine SQL-Injektion?

- Was tippt der Benutzer? Ist das in Ordnung?
- "V" > OK
- "V" ; DROP TABLE dbo.students; SELECT * FROMdbo.Teachers WHERE something LIKE '" > SEHR NICHT OK
- WHERE studentId = 123 AND v.Nev LIKE '"' " oder WHERE v.Nev LIKE '"keiner von diesen ' OR 1=1″ > SEHR NICHT OK
-/**/OR//**/

- Was ist die Lösung?

EXEC dbo.VarosKereses '"keine OR 1=1"'

DECLARE @param NVARCHAR(100) = '"keine solche OR 1=1 "';
EXEC dbo.VarosKereses @param;

GO
CREATE PROCEDURE dbo.VarosKereses2(@Suchname NVARCHAR(100))
AS
BEGIN
	SELECT * FROM dbo.Varosok WHERE Id=1 AND Nev LIKE @Suchname
END
GO

INSERT INTO dbo.Varosok(ID, NEV, OrszagId)
VALUES (100, 'keine' OR ''''=''', 1);

SELECT * FROM dbo.Varosok WHERE Id =100;

Sie müssen den Tabellenparameter der gespeicherten Prozedur angeben
als READONLY

DROP TYPE IF EXISTS mytable;
CREATE TYPE mytable AS Tabelle
(
    id int
);

GO
CREATE OR ALTER PROCEDURE dbo.GetSales(@myt mytable READONLY)
AS
SELECT id FROM @myt WHERE id > 1;
GO

DECLARE @t mytable;
INSERT INTO @t VALUES(1);
INSERT INTO @t VALUES(2);
INSERT INTO @t VALUES(3); INSERT INTO @t VALUES(3);

EXEC dbo.GetSales @t

Wie können wir eine Rückerstattung erhalten?
OUTPUT - Parameter! Wir können mehr als einen Ausgabewert von unserem Speicherverfahren senden

GO
CREATE OR ALTER PROCEDURE dbo.OutputTest (@param1 INT, @outparam int OUTPUT)
AS
BEGIN
SET @outparam=@param1*2;
END
GO

-- NULL zurückgeben
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@doubledValue
SELECT @doubledValue;
GO

-- Das ist gut:
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2, @doubledValue OUTPUT
SELECT @doubledValue;

-- Die Reihenfolge der Parameter kann nicht umgedreht werden
DECLARE @doubledValue int;
EXEC dbo.OutputTest @doubledValue OUTPUT,2;
SELECT @doubledValue;
GO

DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@doubledValue OUTPUT;
SELECT @doubledValue;

-- außer haa wird durch den Namen angegeben
GO
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@outparam = @doubledValue OUTPUT;

SELECT @doubledValue;

Rückgabewert - RETURN

GO
CREATE OR ALTER PROCEDURE dbo.ReturnTest(@varosId INT, @varosNev NVARCHAR(100) OUTPUT )
AS
IF @varosId < 0
	RETURN 1;
ELSE
	SET @varosNev = (SELECT Nev FROM dbo.Varosok WHERE Id = @varosId);
	RETURN 0;
GO

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret, @varos
GO
DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest 1, @varos OUTPUT
SELECT @ret, @varos
GO
DECLARE @ret int;
DECLARE @varos NVARCHAR(100);
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret, @varos

SELECT * FROM dbo.Varosok

Läuft bis zum ersten RETURN

- vorher, so dass dies nicht mehr über die

GO
CREATE OR ALTER PROCEDURE dbo.ReturnTest (@varosId INT, @varosNev nvarchar(100) OUTPUT)
AS
BEGIN
IF @varosID < 0
BEGIN
RETURN 1;
END

SELECT @varosNev= (SELECT Nev FROM dbo.Varosok WHERE Id = @varosID)
SET @varosNev = @varosNev + '!'
RETURN 0;
END
GO

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);

EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret,@varos;
- gab zuvor @varos zurück, so dass dies nicht mehr überschrieben wird
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret,@varos;
----------------MEGOLDÁS
GO
CREATE OR ALTER PROCEDURE dbo.ReturnTest (@varosId INT, @varosNev nvarchar(100) OUTPUT)
AS
SET @varosNev = NULL;
BEGIN
IF @varosID < 0
BEGIN
RETURN 1;
END

SELECT @varosNev= (SELECT Nev FROM dbo.Varosok WHERE Id = @varosID)
SET @varosNev = @varosNev + '!'
RETURN 0;
END
GO

DECLARE @ret int;
DECLARE @varos NVARCHAR(100);

EXEC @ret = dbo.ReturnTest 2, @varos OUTPUT
SELECT @ret,@varos;
--früher eingegeben, damit dies nicht mehr überschrieben wird
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret,@varos;
GO

MISTAKE

GO
CREATE OR ALTER PROCEDURE dbo.PlaceOrder (@PaymentDate datetime2)
AS
IF @Zahlungsdatum > GETDATE()
THROW 50001, 'Eine Bestellung kann nicht in der Zukunft aufgegeben werden',1;
GO
EXEC dbo.PlaceOrder '2024-01-01'
------------------------------------------------
GO
CREATE OR ALTER PROCEDURE dbo.Osztas(@v1 INT, @v2 INT, @hanyados INT OUTPUT)
AS
BEGIN TRY
SET @hanyados = @v1 / @v2
RETURN 0;
END TRY
BEGIN CATCH
SET @hanyados=NULL;
RETURN 1;
END CATCH
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Osztas 10,2, @ertek OUTPUT;
SELECT @ret, @ertek;
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Instal 10,0, @ertek OUTPUT;
SELECT @ret, @ertek;

DEFAULT-Wert

GO
CREATE OR ALTER PROCEDURE dbo.Osztas(@v1 INT, @v2 INT= 3, @hanyados INT OUTPUT)
AS
BEGIN TRY
SET @hanyados = @v1 / @v2
RETURN 0;
END TRY
BEGIN CATCH
SET @hanyados=NULL;
RETURN 1;
END CATCH
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Div 10, @hanyados = @ertek OUTPUT;
SELECT @ret, @ertek;
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Instal 10,5, @ertek OUTPUT;
SELECT @ret, @ertek;

SET NOCOUNT EIN

GO
CREATE OR ALTER PROCEDURE dbo.TaroltEljarasVarosokOrsz odors
AS
BEGIN
SET NOCOUNT ON
SELECT v.Nev AS VarosNev
,o.Nev AS OrszagNev
FROM dbo.Varosok v
JOIN dbo.Orszagok o ON v.OrszagId=o.Id
WHERE v.Nev wie 'B%';
END
GO
EXEC dbo.TaroltEljarasVarosokOrszagok

Wie viele Zeilen wurden geändert?

GO
CREATE OR ALTER PROCEDURE dbo.VarosFrissites (@searchParam nvarchar(10), @affectedRows INT OUTPUT)
AS
BEGIN
UPDATE dbo.Varosok
SET Nev =LOWER(Nev)
WHERE Nev wie @searchParam;
SET @affectedRows=@@ROWCOUNT
END
GO

DECLARE @ar INT;
EXEC dbo.VarosFrissites 'B%', @ar OUTPUT;
PRINT @ar;

UDF:

- immer wieder neu starten
- nicht Zustand, gibt Datentyp
- Eingangsparameter
- gespeicherte Prozeduren können nicht aufgerufen werden

Typen:
- Systemfunktionen
- Tabellenwert: kann in der Ansicht nicht parametrisiert werden
- Skalare Werte: können in Select sein
- Aggregate < externe Sprachen z.B. .Net und C# Funktionen können importiert werden
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-aggregate-transact-sql?view=sql-server-ver15

TRY-CATCH - FUNKTIONIERT NICHT!!!!!!!!!!!!!!!!
aber ich rufe von der Speicherprozedur aus auf und es ist da, dann ja

DDL, Einfügen, Aktualisieren, Löschen funktioniert nicht
USE WideWorldImporters;
Gehe zu
GO
CREATE OR ALTER FUNCTION Sales.function_date()
RETURNS datetime /* Angabe des Datentyps des zurückgegebenen Wertes */
AS
BEGIN
    /* Geben Sie Ihren Wert zurück */
    RETURN CURRENT_TIMESTAMP;
END;
GO
-- Funktion aufrufen
SELECT Umsatz.funktion_datum();

total_sum_orderline

GO
CREATE FUNCTION Sales.total_sum_orderline(
    @ordelineID int
)
RETURNS decimal(20,3) --WITH SCHEMABINDING - wir binden es an die Tabellenstruktur an, hilft manchmal bei der Leistung
AS
BEGIN
    RETURN (SELECT Menge*Einheitspreis FROM Verkauf.Auftragszeilen Where AuftragszeilenID = @ordelineID)
END;
GO
/*
    Verwendung der Skalar-UDF zur Rückgabe des Gesamtwerts der versteuerten Zeile
    in der SELECT-Klausel.
*/

SELECT Verkäufe.gesamtsumme_auftragszeile(2);
SELECT Menge*Einheitspreis FROM Sales.OrderLines Where OrderLineID = 2;
/* für jede Zeile Summe- wird langsam, Zählung pro Zeile und CPU wird hoch sein, wir mögen keine skalaren Funktionen */
SELECT OrderLineID,Sales.total_sum_orderline(2)
FROM Sales.OrderLines;

UDF Skalar-Inlining

- führt keine UDF aus, sondern zerlegt sie in Tabellenoperationen > weniger CPU - ADVANCED FEATURE
Ab 2019 haben wir neu verpackt
Sie müssen dem nachkommen:

Inlinebarer skalarer UDF-Anforderungsteil
https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15
Kiegészítés: https://support.microsoft.com/en-us/topic/kb4538581-fix-scalar-udf-inlining-issues-in-sql-server-2022-and-2019-f52d3759-a8b7-a107-1ab9-7fbee264dd5d

- z.B. kann eine Rückgabe nur sein
- z.B. Scalar UDF inlining funktioniert nicht mit berechneter Spalte!

GO
CREATE OR ALTER FUNCTION dbo.total_sum_orderline(
    @ordelineID int
)
RETURNS decimal(20,3) WITH SCHEMABINDING -- wir binden es an die Tabellenstruktur an, das hilft manchmal der Leistung
AS
BEGIN
    RETURN (SELECT Menge*Einheitspreis FROM dbo.testlines Where OrderLineID = @ordelineID)
END;
GO
SELECT dbo.total_sum_orderline(2);
SELECT Menge*Einheitspreis FROM dbo.testlines Where OrderLineID = 2;
/* berechnete Spalte - wir verlieren das Inlining hier - bleiben innerhalb der Tabelle, wenn wir dies tun
wir verlieren sie auch in der Prüfbeschränkung */
ALTER TABLE dbo.testlines
ADD totalsum AS dbo.total_sum_orderline(OrderID);

SELECT OrderLineID, Gesamtsumme FROM dbo.testlines
WHERE OrderLineID<200;
/*view-n - wir bevorzugen es hier zu verwenden */
GO
CREATE OR ALTER VIEW dbo.testview
MIT SCHEMABINDUNG
AS
SELECT OrderLIneID AS 'lineID
, dbo.total_sum_orderline(OrderLineID) as 'totalsum'
FROM dbo.testlines
GO
SELECT * FROM dbo.testview
WHERE LineID < 200;
/* wenn die Ansicht mit Schemabiding eine skalare Funktion enthält, dann muss die skalare Funktion auch schemabiding sein!!! */
/* Constraint prüfen - Inlining funktioniert nicht */
ALTER TABLE dbo.testlines
ADD CONSTRAINT CK_test_1 CHECK (
(OrderLineID = 200);
UPDATE dbo.testlines
SET OrderLineID=1600
WHERE OrderLineID  500
    RETURN 500;

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testbestellungen
WHERE OrderID  500
    BEGIN
        SET @days=500;
    END

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testbestellungen
WHERE OrderID  500
    BEGIN
        SET @Tage=500;
    END

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID, GETDATE())
FROM dbo.testorders
WHERE OrderID  500
		BEGIN
			SET @Tage = 500;
		END
	RETURN @Tage
END
GO

SELECT OrderID,OrderDate,dbo.datediffer2(OrderDate,GETDATE()) AS 'days'
FROM dbo.testorders;

DEFAULT-Wert für skalare UDF

GO
CREATE OR ALTER FUNCTION dbo.defaultfn(@id int=6)
RETURNS int
AS
BEGIN
    RETURN @id;
END
GO
SELECT dbo.defaultfn(2);
SELECT dbo.defaultfn(default);

Funktionen mit Tabellenwert:

- Rückgabe immer mit Tabelle
- kein JOIN

Typen:
- inline tabellenwertige Funktionen iTVF > 1 SELECT STATEMENT;
- Multianweisung - MSTVF(schrecklich) - nicht verwenden, schlechte Optimierung

USE WideWorldImporters;
GO
/*
    Erstellen einer Inline-TVF zum Abrufen von ColorId und ColorName
*/
CREATE FUNCTION Warehouse.GetColor(@colorid int)
RETURNS TABLE
AS
RETURN
    (SELECT FarbeID, Farbname
     FROM Warehouse.Colors
     WHERE ColorID = @colorid);
GO
/*
    Verwendung der Inline-TVF in einer einfachen SELECT-Abfrage in der FROM-Klausel.
*/
SELECT FarbeId, Farbname
FROM WareHouse.GetColor(36);

SELECT FarbeId, Farbname
FROM WareHouse.GetColor(10);

DEFAULT-Wert bei TVF UDF

GO
CREATE OR ALTER FUNCTION Warehouse.GetColor(@colorid int=10)
RETURNS TABLE
AS
RETURN
(SELECT FarbeID, Farbname
FROM Warehouse.Colors
WHERE ColorID = @colorid);
GO
SELECT FarbeId, Farbname
FROM WareHouse.GetColor(default);

APPLY - CROSS APPLY wie innere Verknüpfung

SELECT StockItemID,StockItemName,ColorName
FROM LagerHaus.StockItems s
CROSS APPLY WareHouse.GetColor(s.ColorID) c;

APPLY - OUTER APPLY wie LEFT OR RIGHT OUTER join

SELECT StockItemID,StockItemName,ColorName
FROM LagerHaus.StockItems s
OUTER APPLY LagerHaus.GetColor(s.ColorID) c;

nichtfunktionaler Teil
Tischbetreiber:
Kreuzanwendung anstelle einer Unterabfrage (Rückgabe)
bestimmte Unterabfrage

cross apply anstelle von subquery (subquery liefert 1 Einzelwert)
zum Beispiel
https://github.com/green-fox-academy/teaching-materials/blob/master/workshop/sql-server-subqueries-aggregations/correlated-quantity/correlated-quantity.md
Ändern Sie die Abfrage wie folgt:
- eine neue Spalte hinzufügen, die die Höchstmenge für die StockItemID anzeigt
- eine weitere Spalte hinzufügen, in der die Differenz zwischen dem maximalen
Menge für die StockItemID und jede Bestellmenge
| OrderID | StockItemID | Menge | Max_StockItem_Qty | Max_Diff_Qty | Max_Diff_Qty

/* Version der Unterabfrage: 0.1452 */
SELECT o.OrderID
				,o.StockItemID
				,o.Menge
				,( SELECT MAX(Menge)
					FROM Umsatz.Auftragszeilen
					WHERE StockItemID = 180
				 ) AS Max_StockItem_Qty
				,(SELECT maxq.menge - Menge
						FROM ( SELECT MAX(Menge) AS qty
										FROM Verkauf.Auftragszeilen
										WHERE StockItemID = 180
								 ) AS maxq
				) AS Max_Diff_Qty
FROM Sales.OrderLines AS o
WHERE StockItemID = 180;
/* CROSS APPLY - Tabellenoperator Version - CROSS APPLY Kosten 0.102256 */
SELECT OrderId,StockItemID,Menge,Max_StockItem_Qty,Max_StockItem_Qty-Quantity AS Max_Diff_Qty
FROM Sales.OrderLines
	CROSS APPLY (SELECT MAX(Menge) AS Max_StockItem_Qty FROM Sales.OrderLines
		WHERE StockItemID=180) temptable
WHERE StockItemID = 180;

TRIGGERS

- Kann ich Trigger ausführen? > NEIN
- ein Ereignis aktiviert den Trigger ! "FEUER_AUSLÖSER"
- Kann ich dem Auslöser einen Parameter geben? -> NEIN
- IF ... ELSE Struktur verwendbar
- try ...catch strukturierte Fehlerbehandlung > YES
- einfachen Trigger erstellen
Auslösende Gruppen:
- DML-Auslöser: INSERT, UPDATE, DELETE > MASTERWORK
- DDL-Auslöser: ALTER,CREATE,DROP
- Anmeldetrigger

LOGON-Trigger: DANGEROUS! Die Anmeldung kann fehlschlagen

USE testdb;
DROP TABLE IF EXISTS dbo.logonsactivity;
CREATE TABLE dbo.logonsactivity (
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    login VARCHAR(50),
    servername VARCHAR(50),
    serverversion VARCHAR(350),
    servicename VARCHAR(50),
    logintime datetime2 DEFAULT GETDATE()
);
-- Anmeldename
SELECT SUSER_NAME();
-- Servername \ (Instanz)
SELECT @@SERVERNAME;
-- Version des Servers
WÄHLEN SIE @@VERSION;
-- Dienstname (Instanzname ohne Server)
WÄHLEN SIE @@SERVICENAME;
/*Auslöser*/
GO
CREATE OR ALTER TRIGGER tr_logtouser
AUF ALLEN SERVERN FÜR DIE ANMELDUNG
AS
BEGIN
    INSERT INTO testdb.dbo.logonsactivity
    SELECT SUSER_NAME(),@@SERVERNAME,@@VERSION,@@SERVICENAME,CURRENT_TIMESTAMP;
END
GO
SELECT * FROM testdb.dbo.logonsactivity;
SELECT * FROM sys.server_triggers;
-- Logon-Trigger löschen
DROP TRIGGER tr_logtouser ON ALL SERVER;

DDL-Auslöser

DROP TABLE IF EXISTS dbo.Invoices;
CREATE TABLE dbo.Invoices (
    id INT PRIMARY KEY,
    kunden_id INT NOT NULL,
    gesamt INT NOT NULL
);
INSERT INTO dbo.Rechnungen
VALUES
(3,5,200),(4,9,10000), (20,9,14000);
GO
CREATE OR ALTER TRIGGER invoiceprotector ON DATABASE FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
PRINT 'Sie können keine Tabellen ändern oder löschen!'
ROLLBACK;
END
PRINT 'hallo';
GO
SELECT * FROM sys.triggers;
ALTER TABLE dbo.Invoices
DROP COLUMN total;
DROP TRIGGER invoiceprotector ON DATABASE;

DML-Auslöser:

- AFTER-Auslöser
- ANSTELLE DES AUSLÖSERS

NACH LÖSCHEN

DROP TABLE IF EXISTS dbo.storno_invoices
CREATE TABLE dbo.storno_Rechnungen (
    id INT, --PRIMARY KEY,
    kunden_id INT NOT NULL,
    total INT NOT NULL,
    delete_time DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP
);
/* AFTER TRIGGER - Trigger erstellen, um gelöschte Rechnungen in die Strono_Tabelle zu verschieben
SQL SERVER Trigger funktioniert per Anweisung!!!!!
- Vermeiden Sie Variablen! */
GO
CREATE TRIGGER dbo.TR_stroni_invoice ON dbo.Invoices AFTER DELETE
AS
NOCOUNT ON SETZEN;
BEGIN
    INSERT INTO dbo.storno_rechnungen (id, kunden_id,gesamt)
    SELECT * FROM gelöscht;
END
GO
SELECT * FROM dbo.Rechnungen;
SELECT * FROM dbo.storno_Rechnungen;

DELETE FROM dbo.Rechnungen
WHERE gesamt > 1000;

SELECT * FROM dbo.Rechnungen;
SELECT * FROM dbo.storno_invoices;

Auslöser für die Ablehnung erstellen: Rechnung ändern

GO
CREATE OR ALTER TRIGGER dbo.tr_forbidden_modify_invoice
ON dbo.Invoices
AFTER UPDATE
AS
NOCOUNT ON SETZEN;
BEGIN
ROLLBACK;
END
GO
SELECT * FROM dbo.Invoices;

UPDATE dbo.Rechnungen
SET gesamt=1000
WHERE id=3;

INSERT INTO dbo.Rechnungen
VALUES (25,9,2000),(92,20,20000),(100,19,21000);
UPDATE dbo.Rechnungen
SET gesamt=1000
WHERE id=3;

Wo sind die DML-Auslöser?

!!!!! TRUNCATE kann nicht mit Triggern aktiviert werden !!!!!!!!!!

INSTAD OF triggers? > DML-Auslöser

z.B. bei samlak gehen die Großen anderswo selten

einen Auslöser erstellen
where gesamt >= 1100 > dbo.Invoces

wo gesamt dbo.smalltotalinvoices

DROP TABLE IF EXISTS dbo.smalltotalinvoices
  CREATE TABLE dbo.smalltotalinvoices (
    id INT PRIMARY KEY,
    kunden_id INT NOT NULL,
    gesamt INT NOT NULL
);
SELECT * FROM dbo.Invoices;
GO
CREATE OR ALTER TRIGGER dbo.tr_smalltotal
ON dbo.Invoices
ANSTELLE VON INSERT
AS
NOCOUNT EINSCHALTEN;
BEGIN
 INSERT INTO dbo.Rechnungen
 SELECT * FROM eingefügt
 WHERE gesamt >=1100;

 INSERT INTO dbo.smalltotalinvoices
 SELECT * FROM eingefügt
 WHERE gesamt < 1100;
END
GO
 INSERT INTO dbo.Rechnungen
 VALUES(3,200,50000);

  INSERT INTO dbo.Rechnungen
 WERTE(5,200,900);

SELECT * FROM dbo.Rechnungen;
SELECT * FROM dbo.smalltotalinvoices;

Bulk Insert ignoriert den Trigger standardmäßig

bulk insert dbo.Invoices
  FROM N'C:\Benutzer\mrhen\Dokumente\READER\MatReview\WEEK-4\w1d1_smalltotal.csv'
  WITH (fieldterminator=';',rowterminator='0x0A'/* nicht '\n', weil es von Zoli stammt, FIRSTROW=2, wenn es eine Kopfzeile gibt*/)
TRUNCATE TABLE dbo.invoices;
TRUNCATE TABLE dbo.smalltotalinvoices;
/* DE FIRE_TRIGGERS */
Bulk-Insert dbo.Invoices
  FROM 'C:\Benutzer\mrhen\Dokumente\READER\MatReview\WEEK-4\w1d1_smalltotal.csv'
  WITH (fieldterminator=';',rowterminator='0x0A', FIRE_TRIGGERS/* nicht '\n', weil es von Zoli war, FIRSTROW=2, wenn es eine Kopfzeile gibt*/)

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.smalltotalinvoices;
SELECT * FROM sys.triggers;
DROP TRIGGER dbo.TR_stroni_invoice, dbo.tr_forbidden_modify_invoice, dbo.tr_smalltotal;

Funktion des Fensters

- Fensteraggregatfunktionen (COUNT, SUM, MIN, MAX)
- Ranking-Funktionen (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
- Offset-Funktionen (LAG, LEAD, FIRST_VALUE, LAST_VALUE)
- Statistische Funktionen (PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, CUME_DIST)

OVER() == Fensterfunktion / SELECT /

Zeilensätze, gegeben einen Zeilensatz des Fensters,

USE WideWorldImporters;
GO
/* stockitemid, sum_stock_quantities */
SELECT StockItemId, SUM(Menge) AS sum_stock_quantities
FROM Verkäufe.OrderLines
GROUP BY StockItemID;
GO
SELECT SUM(Menge) AS sum_all_quantities
FROM Sales.OrderLines;
/* FELADAT
orderlineid stockitems sum_stock sum_all
1 1 20000 9Millionen
2 1 20000
3 2 1000
*/
WITH myCTE AS (
    SELECT StockItemId, SUM(Menge) AS sum_stock_quantities
    FROM Verkauf.Auftragszeilen
    GROUP BY StockItemID
),
myCTE2 AS (
    SELECT SUM(Menge) AS sum_all_quantities
    FROM Sales.OrderLines
)
SELECT sol.StockItemID,OrderLineID, myCTE.sum_stock_quantities,myCTE2.sum_all_quantitis, FORMAT(myCTE.sum_stock_quantities/(myCTE2.sum_all_quantitis*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
INNER JOIN myCTE ON myCTE.StockItemID=sol.StockItemID
CROSS JOIN myCTE2
ORDER BY sol.[StockItemID] ASC, OrderLineID ASC;
/*REPLACING CTE2 mit Fensterfunktion */
SELECT SUM(Menge) OVER ()
FROM Sales.OrderLines;
/*Ersetzen von CTE1 durch neue Funktion*/
SELECT SUM(Menge) OVER (PARTITION BY StockItemID)
FROM Sales.OrderLines;
/* Version der Fensterfunktion */
SELECT sol.StockItemID,
OrderLineID,
SUM(Menge) OVER (PARTITION BY StockItemID) AS sum_stock_quantitis,
SUM(Menge) OVER () AS sum_all_quantities
, FORMAT(SUM(Menge) OVER (PARTITION BY StockItemID)/(SUM(Menge) OVER ()*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
ORDER BY sol.[StockItemID] ASC, OrderLineID ASC;

fensterfunktion nach gruppe nach

SELECT sol.StockItemID,
SUM(Menge) /*OVER (PARTITION BY StockItemID)*/ AS sum_stock_quantitis,
SUM(SUM(Menge)) OVER () AS sum_all_quantities
, FORMAT(SUM(Menge) /*OVER (PARTITION BY StockItemID)*//(SUM(SUM(Menge)) OVER ()*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
GROUP BY StockItemID
ORDER BY sol.[StockItemID] ASC;

Ranking-Funktionen

Syntaxvoraussetzung: ORDER BY innerhalb der OVER-Klausel

ZEILEN_NUMMER, RANG, DENSE_RANK, NTILE

SELECT OrderId, Customerid,OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS ROW_NUMBER
    , Rank() OVER(ORDER BY OrderDate) AS rnk -- tote Rennen werden übersprungen und der nächste Rang in der Zeile
    , DENSE_RANK() OVER(ORDER BY OrderDate) AS dense_rnk -- Sackgassen beim nächsten Rang
    NTILE (20) OVER(ORDER BY OrderDate) AS n_tile -- in 20 Teile unterteilen, Haufen zurückgeben
FROM Verkauf.Aufträge;

Rangfolge nach orderid pro customerid

SELECT OrderId, CUstomerid,OrderDate,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS ROW_NUMBER
    , Rang() OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS rnk
    , DENSE_RANK() OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS dense_rnk
    , NTILE (10) OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS n_tile -- in 20 Teile unterteilen, Haufen zurückgeben
FROM Verkauf.Aufträge;

Webshop-Bestellung, welche Artikel in der Bestellung enthalten sind - Gesamtbetrag, Bestellzeile_Nummer

SELECT OrderId,
StockItemID,
Menge,
StückPreis,
(Stückpreis*Menge) AS OrderLine_SUM,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS orderline_number,
SUM(Stückpreis*Menge) OVER (PARTITION BY OrderID Order BY OrderLineID) as running_total
 FROM Vertrieb.OrderLines;

 SELECT OrderId,
StockItemID,
Menge,
Stückpreis,
OrderLineID,
(Stückpreis*Menge) AS OrderLine_SUM,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS orderline_number,
SUM(Stückpreis*Menge) OVER (PARTITION BY OrderID Order BY OrderLineID DESC) as running_total
 FROM Vertrieb.OrderLines;

laufende Summe

SELECT CityID, CityName, LatestRecordedPopulation AS population,
       (SELECT SUM(AktuellsteBevölkerungszahl)
        FROM Anwendung.Städte c2
        WHERE c2.StateProvinceID = c1.StateProvinceID AND c2.CityID <= c1.CityID) AS run_total
FROM Anwendung.Städte c1
WHERE StateProvinceID = 6
ORDER BY StadtID;
/* Version der Windows-Funktion */
 SELECT CityID, CityName, LatestRecordedPopulation,
 SUM(LatestRecordedPopulation) OVER(ORDER BY CityID) as run_total
FROM Anwendung.Städte
WHERE StateProvinceID = 6

Offset-Funktion
(RÜCKSTAND, VORSPRUNG, ERSTER_WERT, LETZTER_WERT)

/* nächste StadtID */
SELECT StadtID, Stadtname, (SELECT MIN(StadtID) FROM Anwendung.Städte c2 WHERE c2.StadtID > c1.StadtID) AS nextcityid
FROM Anwendung.Städte c1
ORDER by StadtID ASC;
/* windows funktion auftrag */
SELECT StadtID, Stadtname, LEAD(StadtID) OVER(ORDER BY StadtID) AS nextcityid
FROM Anwendung.Städte
ORDER by StadtID ASC;

SELECT StadtID, Stadtname, LAG(StadtID) OVER(ORDER BY StadtID) AS nextcityid
FROM Anwendung.Städte
ORDER BY CityID ASC;

Schätzung der Kardinalität

ALTER DATABASE WideWorldImporters
SET Kompatibilitätsstufe = 150;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET Veraltete_Kardinalität_Schätzung = OFF

--------------
SELECT name, wert
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION' ;
-----------
SELECT *
FROM sys.datenbanken
WHERE name = 'WideWorldImporters';

Erste Probeklausur

/* ÜBUNG 1
Entwerfen Sie eine kleine Teilmenge einer imaginären Bücherdatenbank.

In der Bücher-Datenbank sollen mindestens folgende Daten gespeichert werden:
    - Bücher
    - Autoren
    - bei Bedarf die Beziehung zwischen Büchern und Autoren

Jeder Autor kann mehrere Bücher haben, aber ein Buch kann nur einen Autor haben.

Die folgenden Daten müssen mindestens gespeichert werden:
    - Titel des Buches
    - Länge des Buches in Seiten
    - Erscheinungsdatum des Buches
    - Autor des Buches
    - Name des Genres (nur eines pro Buch: Sci-Fi, Action, Horror usw.)

Erstellen Sie die entsprechenden Tabellen mit den erforderlichen Datenspalten und den entsprechenden Datentypen.

Legen Sie mindestens die folgenden Beschränkungen an:
    - PRIMARY KEY
    - FOREIGN KEY

Stellen Sie sicher, dass es keine Bücher mit demselben Titel gibt.
Geben Sie bei Bedarf zusätzliche Beschränkungen an.

Erstellen Sie ein Skript für die Tabellen.

Wie viele Tabellen werden benötigt: Bücher, Autoren, Genre
Tabellenbeziehung ? 1->N (Bücher, Autoren), 1->N (Bücher, Genre)


*/

CREATE DATABASE matrev20230303;
GO
USE matrev20230303;
GO
CREATE SCHEMA books;
GO

DROP TABLE IF EXISTS books.books, books.authors, book.genres;

CREATE TABLE books.authors (
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,autor_name NVARCHAR(200) NOT NULL
);

CREATE TABLE books.genres (
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,genre_name NVARCHAR(200) NOT NULL
        CONSTRAINT UK_genres_name UNIQUE
);

CREATE TABLE books.books (
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,title NVARCHAR(200) NOT NULL CONSTRAINT UK_books_title UNIQUE
    ,pagenr SMALLINT NOT NULL
        CONSTRAINT CK_books_pagenr CHECK (pagenr > 0)
    ,pubdate DATE
    ,genre_id INT
    ,autor_id INT
    ,CONSTRAINT FK_books_genreid FOREIGN KEY (genre_id) REFERENCES books.genres (id)
    ,CONSTRAINT FK_books_authorid FOREIGN KEY (author_id) REFERENCES books.authors (id)
);

/*
INSERT INTO bücher.autoren
VALUES ('Robert Ludlum'),('Jack Higgins');

INSERT INTO books.genres
VALUES ('Spionagethriller'),('Action');

INSERT INTO bücher.bücher
VALUES('Kanzler Manuskript',700,'19760101',1,1);

INSERT INTO bücher.bücher
VALUES ('Todesengel',300,'19940302',2,2);

SELECT * FROM bücher.autoren ba
INNER JOIN bücher.bücher bb ON ba.id=bb.autor_id
INNER JOIN books.genres bg ON bg.id=bb.genre_id;

*/

/* ÜBUNG 2
-- wie viele Tabellen sollen verwendet werden? 1

-- Weitere "Ergebnismengen" sollten kombiniert werden (Unterabfrage/Satzoperator/CTE/etc...) ANTI SEMI JOIN (WHERE NOT EXISTS/EXCEPT)

-- welche Aggregation soll verwendet werden ? NOPE

-- welche Verknüpfung soll verwendet werden ? NOPE

-- welche Spalte soll verwendet werden ? umbenennen ? NOPE

-- wo soll(en) der/die Filter verwendet werden ? / WO/HABEN, AUSWÄHLEN/FÄLLEN/WO

-- Soll "ORDER BY" verwendet werden? NOPE

Rückgabe der Namen der Lagerartikel und ihrer empfohlenen Verkaufspreise
wenn der Stückpreis größer als 20 und kleiner als 100 ist,
aber schließe "Limited Stock" und leere Etiketten aus.

Verwenden Sie den entsprechenden SET-Operator.

Verwenden Sie die folgende Tabelle:
    - Warehouse.StockItems

    
Der Bericht sollte die folgenden Spalten anzeigen:
    - StockItemName
    - EmpfohlenerEinzelhandelspreis

*/

/* Version 1 */

SELECT LagerartikelName,EmpfohlenerEinzelhandelspreis FROM Lager.Lagerartikel
WHERE StückPreis > 20 AND StückPreis  20 AND Stückpreis  20 AND StückPreis  20 AND StückPreis  20 AND StückPreis  YEAR(MIN(OrderDate)) THEN 'Datum : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [Datum der ersten Bestellung]
FROM Vertrieb.Kunden sc
INNER JOIN Vertrieb.Aufträge so ON sc.KundenID=so.KundenID
GROUP BY sc.CustomerID,CustomerName
ORDER BY [Datum der ersten Bestellung] DESC;

/* Version 2 */

SELECT Kundenname,
    CASE
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Monat: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Tag: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN 'Datum : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [Datum der ersten Bestellung]
FROM Vertrieb.Kunden sc
INNER JOIN Verkauf.Aufträge so ON sc.KundenID=so.KundenID
GROUP BY Kundenname
ORDER BY [Erstes Bestelldatum] DESC;


/* Version 3 */

SELECT Kundenname,
    CASE
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Monat: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Tag: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN 'Datum : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [Datum der ersten Bestellung]
FROM Vertrieb.Kunden sc
INNER JOIN Verkauf.Aufträge so ON sc.KundenID=so.KundenID
GROUP BY Kundenname
ORDER BY MIN(OrderDate) DESC;

/* Version 4 */


WITH FirstOrder AS (
    SELECT KundenID, MIN(Bestelldatum) AS MinBestelldatum
    FROM Verkauf.Bestellungen
    GROUP BY KundenID
)
SELECT Kundenname
, (CASE
WHEN fo.MinOrderDate >= '20160101' AND fo.MinOrderDate < '20170101' THEN 'Monat: ' + CAST(MONTH(fo.MinOrderDate) AS varchar(2)) + ', Tag: ' + CAST(DAY(fo.MinOrderDate) AS varchar(2))
WHEN fo.MinOrderDate < '20160101' THEN 'DATUM: ' + CAST(fo.MinOrderDate AS varchar)
END) AS 'Datum der ersten Bestellung
FROM Verkauf.Kunden c
INNER JOIN FirstOrder fo ON c.CustomerID=fo.CustomerID
ORDER BY [Datum der ersten Bestellung] DESC;

/* Version 5 */

SELECT Kundenname,
    CASE
    WHEN (BESTELLDATUM ZWISCHEN '2016-01-01' UND '2016-12-31') THEN
        'Monat: ' + CAST(MONAT(Best.datum) AS varchar(2)) +
        ', Tag: ' + CAST(DAY(OrderDate) AS varchar(2))
    ELSE
        'Datum: ' + CAST(BESTELLDATUM AS varchar(10))
    END AS 'Datum der ersten Bestellung'
FROM (
    SELECT CustomerID, MIN(OrderDate) AS 'OrderDate
    FROM Verkauf.Bestellungen
    GROUP BY CustomerID) AS first_orders
INNER JOIN Vertrieb.Kunden c
    ON erste_bestellungen.KundenID = c.KundenID
ORDER BY
    OrderDate DESC
;

/* ÜBUNG 5
-- Wie viele Tabellen sollten verwendet werden?   2

-- Sollten weitere "Ergebnismengen" kombiniert werden (Unterabfrage/Satzoperator/CTE/etc...) ? NOPE

-- welche Aggregation soll verwendet werden ? COUNT

-- welche Verknüpfung soll verwendet werden?  INNER

-- welche Spalte soll verwendet werden ? umbenennen ? YES

-- wo soll(en) der/die Filter verwendet werden ? / WO/HABEN, AUSWÄHLEN/FALL/FALL

-- Soll "ORDER BY" verwendet werden? NOPE

Schreiben Sie eine Ansicht im Verkaufsschema mit dem Namen PickupReport.

Sie sollte die Schemabindung verwenden.

Verwenden Sie sie, um die abgeholten und nicht abgeholten Aufträge nach PostalCityID zurückzugeben
geordnet nach den nicht abgeholten Lieferungen absteigend.

Verwenden Sie die folgenden Tabellen:
    - Sales.Orders
    - Verkäufe.Kunden

    
Der Bericht sollte die folgenden Spalten anzeigen:
    [Postal ID]: Spalte PostalCityID aus Sales.Customers
    [Abgeholt]: Anzahl der bereits abgeholten Aufträge
	[Nicht abgeholt]: Anzahl der noch nicht abgeholten Aufträge
*/
GO
CREATE OR ALTER VIEW Vertrieb.PickupReport
MIT SCHEMABINDUNG
AS
SELECT PostalCityID,
    COUNT(CASE
        WHEN PickingCompletedWhen IS NOT NULL THEN 1
    END) AS [Abgeholt]
   ,COUNT(CASE
        WHEN KommissionierungErledigtWenn IST NULL THEN 0
    END) AS [Nicht kommissioniert]
FROM Verkauf.Kunden sc
INNER JOIN Verkauf.Aufträge so
ON sc.KundenID=so.KundenID
GROUP BY PostalCityID;
GO

/* Version 2 */
CREATE OR ALTER VIEW Vertrieb.PickupReport
MIT SCHEMABINDUNG
AS
SELECT c.PostalCityID AS [Post-ID]
		, COUNT(o.PickingCompletedWhen) AS [Abgeholt]
		, COUNT(*)-COUNT(o.PickingCompletedWhen) AS [Nicht abgeholt]
FROM Verkauf.Kunden AS c
INNER JOIN Sales.Orders AS o
ON c.CustomerID=o.CustomerID
GROUP BY c.PostalCityID;
GO
/* SHOWCASE */

SELECT * FROM Verkäufe.AbholungBericht
ORDER BY [Nicht abgeholt] DESC;

/* ÜBUNG 6
-- Wie viele Tabellen sollten verwendet werden?  2

-- Weitere "Ergebnismengen" sollten kombiniert werden (Subquery/Set-Operator/CTE/etc...) ? ???

-- welche Aggregation soll verwendet werden ? ZÄHLEN/SUMME

-- welche Verknüpfung soll verwendet werden?  LINKS

-- welche Spalte soll verwendet werden ? umbenennen ? JA

-- wo soll(en) der/die Filter verwendet werden ? / WO/HABEN, AUSWÄHLEN/GROSS/FORMATIEREN

-- Soll "ORDER BY" verwendet werden? NOPE

Schreiben Sie eine Ansicht im Verkaufsschema mit dem Namen SupplierReport.

Sie sollte die Schemabindung verwenden.

Zeigen Sie Lieferanten und das Verhältnis ihrer Produkte zu den insgesamt verkauften Produkten an.
Wenn ein Lieferant keinen Artikel hat, sollte er 0,00 anzeigen.

Verwenden Sie die folgenden Tabellen:
    - Purchasing.Suppliers
    - Lager.StockItems

    
Der Bericht sollte die folgenden Spalten anzeigen:
    [Lieferant]: Der Name des Lieferanten aus Einkauf.Lieferanten
    [Gesamtprozentsatz]: Zweistelliger Prozentsatz der vom Lieferanten verkauften Artikel
        im Vergleich zu allen Artikeln, die von allen Lieferanten verkauft wurden


*/
GO
CREATE OR ALTER VIEW Sales.SupplierReport
MIT SCHEMABINDUNG
AS
   SELECT
     SupplierName AS [Lieferant]
    ,FORMAT(COUNT(ws.StockItemID)/(SUM(COUNT(ws.StockItemID)) OVER() *1.0),'P2') AS [Gesamtanteil]
    FROM Einkauf.Lieferanten ps
    LEFT JOIN Warehouse.StockItems ws
    ON ps.SupplierID=ws.SupplierID
    GROUP BY Lieferantenname
GO

SELECT
     Lieferantenname
    ,FORMAT(COUNT(ws.StockItemID)/(maxstockitem*1.0),'P2') AS [Gesamtprozent].
FROM Einkauf.Lieferanten ps
    CROSS APPLY (SELECT COUNT(*) AS maxstockitem FROM Warehouse.StockItems) AS temptable
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY Lieferantenname,maxstockitem

 -- Version 2

SELECT
     Lieferantenname
    ,FORMAT(COUNT(ws.StockItemID)/((SELECT COUNT(StockItemID) FROM Warehouse.StockItems)*1.0),'P2') AS [Gesamtprozent]
FROM Einkauf.Lieferanten ps
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY Lieferantenname

-- Version 3
SELECT
     Lieferantenname
    ,COUNT(ws.StockItemID)
    ,FORMAT(COUNT(ws.StockItemID)/(SUM(COUNT(ws.StockItemID)) OVER() *1.0),'P2') AS [Gesamtprozentzahl]
FROM Einkauf.Lieferanten ps
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY Lieferantenname

/* ÜBUNG 7
-- Wie viele Tabellen sollen verwendet werden?  2

-- Wie viele "Ergebnismengen" sollen kombiniert werden (Unterabfrage/Satzoperator/CTE/etc...) ?

-- welche Aggregation soll verwendet werden ?

-- welche Verknüpfung sollte verwendet werden ?  

-- welche Spalte soll verwendet werden ? umbenennen ?

-- wo soll(en) der/die Filter verwendet werden ? / WO/HABEN, AUSWÄHLEN/FALL/

-- soll "ORDER BY" verwendet werden ?

Erstellen Sie eine Inline-Tabellenfunktion im Warehouse-Schema mit dem Namen StockUsbReport
die alle USB-bezogenen Datensätze zurückgibt.

Sie sollte einen Parameter verwenden:
    - @stockitemname of nvarchar(10)

Rückgabe des Lagerartikels, seines Verkaufspreises und eines Gesamtlagerpreises
basierend auf dem Stückpreis und der Menge basierend auf dem Parameterwert.

Verwenden Sie die folgenden Tabellen:
    - Warehouse.StockItemHoldings
    - Warehouse.StockItems

Der Bericht sollte die folgenden Spalten anzeigen:
    [Bestandsartikelbezeichnung] : Spalte StockItemName aus Warehouse.StockItems
    [Verkaufspreis] : Spalte RecommendedRetailPrice aus Warehouse.StockItems
    [Aktueller Bestandswert]: Multiplikation des Stückpreises mit der Menge der Artikel
        Die Werte sollten wie folgt aussehen: $1.234.567

Zeigen Sie, wie Sie die Funktion "Tabellenwert" verwenden!

*/
GO
CREATE OR ALTER FUNCTION Warehouse.StockUsbReport( @stockitemname nvarchar(10))
RETURNS TABLE
AS
RETURN (
    SELECT
         StockItemName AS [Name des Lagerartikels]
        ,RecommendedRetailPrice AS [Verkaufspreis]
        ,FORMAT((QuantityOnHand*UnitPrice),'C0','en-US') AS [Aktueller Bestandswert]
    FROM Warehouse.StockItemHoldings wsh
    INNER JOIN Lager.LagerPositionen wsi
    ON wsh.StockItemID=wsi.StockItemID
    WHERE StockItemName LIKE '%'+@stockitemname+'%'
    )
GO

/* SHOWCASE */

SELECT * FROM Warehouse.StockUsbReport('USB');

----
SELECT
     StockItemName
    ,RecommendedRetailPrice
    ,FORMAT((MengeimBestand*Einheitspreis),'C0','en-US') FROM Lager.StockItemBestände wsh
INNER JOIN Lager.LagerPositionen wsi
ON wsh.StockItemID=wsi.StockItemID
WHERE StockItemName LIKE '%'+'USB'+'%'

/* Version 2 */

SELECT
     StockItemName
    ,RecommendedRetailPrice
    ,FORMAT((MengeimBestand*Einheitspreis),'$###,###,#1T#') FROM Warehouse.StockItemHoldings wsh
INNER JOIN Lager.LagerPositionen wsi
ON wsh.StockItemID=wsi.StockItemID



/* ÜBUNG 8 */
----------------------------------------------------------------------------------------------
 DROP TABLE IF EXISTS Anwendung.Früchte;
    GO
    CREATE TABLE Anwendung.Früchte
    (
        FruitId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
        Fruchtname nvarchar(50) NOT NULL
    );

    INSERT INTO Anwendung.Früchte (Obstname)
        VALUES ('Banane'),('Banane'),('Apfel'),('Pfirsich'),('Pfirsich'),('Pfirsich');
/*
Erstellen Sie im Anwendungsschema eine gespeicherte Prozedur namens DeleteFruit.

Die gespeicherte Prozedur löscht Datensätze aus der Tabelle Application.Fruits.

Die gespeicherte Prozedur sollte die obligatorischen Spalten als Parameter akzeptieren:
    - @Fruchtname nvarchar(50)

Die gespeicherte Prozedur sollte bei Erfolg ausgeben:
	-@FruitsDeleted int

Verwenden Sie die Funktionen zur Fehlerbehandlung und Fehlerbehandlung, um die Fehlernummer
die Fehlermeldung und die Fehlerschwere!
Geben Sie auch die Meldung 'Delete fruit failed!' aus.

Wenn der Prozess ohne Fehler verlief, aber die gelöschten Zeilen 0 waren,
einen Fehler mit der folgenden Meldung auslösen:
'No such fruit found' und mit Schweregrad 16.

Zeigen Sie, wie Sie die gespeicherte Prozedur verwenden!
Zeigen Sie nicht die Anzahl der betroffenen Zeilen in der T-SQL-Abfrage als Informationsmeldung an.
*/
GO
CREATE OR ALTER PROCEDURE Application.DeleteFruit (
     @FruchtName NVARCHAR(50)
    ,@FruitsDeleted INT OUTPUT
    )
AS
    NOCOUNT EINSCHALTEN;
    BEGIN TRY
        DELETE Anwendung.Früchte
        WHERE FruitName=@FruitName;
        SET @FruitsDeleted= @@ROWCOUNT;
        IF @FruitsDeleted = 0
            BEGIN
                RAISERROR('Keine solche Frucht gefunden',16,7);
            END
    END TRY
    BEGIN CATCH
        PRINT 'Frucht löschen fehlgeschlagen!';
        PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));
        PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
        PRINT 'Fehlerschwere: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10));
    END CATCH
GO

/* Version 2 */

GO
CREATE OR ALTER PROCEDURE Anwendung.DeleteFruit (
    @FruchtName nvarchar(50),
    @FruitsDeleted int OUTPUT)
AS
BEGIN
    NOCOUNT EINSCHALTEN;
    XACT_ABORT EINSCHALTEN;
    BEGIN TRY
        TRANSAKTION BEGINNEN;
        DELETE FROM Application.Fruits WHERE FruitName = @FruitName;
        SET @FruitsDeleted = @@ROWCOUNT;
        IF @FruitsDeleted = 0
        BEGIN
            RAISERROR('Keine solche Frucht gefunden', 16, 1);
        END
        TRANSAKTION BESTÄTIGEN;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorNumber int = ERROR_NUMBER();
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        RAISERROR('Frucht löschen fehlgeschlagen! Fehlernummer: %d, Fehlermeldung: %s, Fehlerschwere: %d', 16, 1, @ErrorNumber, @ErrorMessage, @ErrorSeverity);
        ROLLBACK DER TRANSAKTION;
    END CATCH
ENDE;
GO


/* SHOWCASE */

DECLARE @outputSP INT;
EXEC Application.DeleteFruit 'banana',@FruitsDeleted = @outputSP OUTPUT;
SELECT @ausgabesP;
GO
DECLARE @outputSP INT;
EXEC Application.DeleteFruit 'banana',@FruitsDeleted = @outputSP OUTPUT;
SELECT @outputSP;

Erste Prüfung

/*
Übung #1:
Entwerfen Sie eine kleine Teilmenge einer imaginären Restaurant-Datenbank.

In der Restaurant-Datenbank sollen mindestens die folgenden Daten gespeichert werden:

Franchise
Geschäft
Die Beziehung zwischen Franchises und Filialen
Zu einer Franchise können mehrere Filialen gehören, wobei eine Filiale nur eine Art von Franchise haben kann.

Die folgenden Daten müssen mindestens gespeichert werden:

Geschäftsname (planen Sie, auch nicht-englische Zeichen zu speichern)
Art der Franchise (planen Sie hier die Speicherung von Kurzbeschreibungen - z.B. Produkt, Fertigung)
Adresse der Filiale
Datum der Ladeneröffnung
Ob eine Filiale 24/7 geöffnet ist oder nicht (wahr oder falsch)
Erstellen Sie die entsprechenden Tabellen mit den erforderlichen Datenspalten und den entsprechenden Datentypen.

Erstellen Sie mindestens die folgenden Constraints:

PRIMARY KEY
FOREIGN KEY
Geben Sie bei Bedarf zusätzliche Beschränkungen an.

Schreiben Sie die Tabellen aus.
*/
CREATE DATABASE Restaurants;
GO
USE Restraurants;
GO
CREATE SCHEMA RT;
GO

DROP TABLE IF EXISTS RT.FranchiseTypes;
CREATE TABLE RT.FranchiseTypes (
    FranchiseTypeID INT IDENTITY(1,1) PRIMARY KEY,
    Franchisename NVARCHAR(100) NOT NULL
        CONSTRAINT UK_Franchisename UNIQUE
);

DROP TABLE IF EXISTS RT.Franchises;
CREATE TABLE RT.Franchise (
    FranchiseID INT IDENTITY(1,1) PRIMARY KEY,
    Geschäftsname NVARCHAR(100) NOT NULL,
    FranchiseTypeID INT NOT NULL,
    CONSTRAINT FK_Franchises_FranchiseTypeID FOREIGN KEY (FranchiseTypeID) REFERENCES RT.FranchiseTypes (FranchiseTypeID)
);
DROP TABLE IF EXISTS RT.Stores;
CREATE TABLE RT.Store (
    StoreID INT IDENTITY(1,1) PRIMARY KEY,
    FranchiseID INT NOT NULL,
    StoreAddress NVARCHAR(300) NOT NULL,
    OpeningDate DATE NOT NULL,
    IsOpen24Hours BIT NOT NULL,
    CONSTRAINT FK_Store_Franchise FOREIGN KEY (FranchiseID)
        REFERENCES RT.Franchise(FranchiseID)
);

/*
Übung #2
Erstellen Sie einen Bericht über alle Lieferanten, die Anzahl ihrer Transaktionen und Kommentare.

Geben Sie alle Lieferanten zurück, unabhängig davon, wie viele Transaktionen sie haben (null oder mehr)

Gruppieren Sie die Ergebnisse nach dem Namen des Lieferanten und seinen Kommentaren.

Ordnen Sie den Bericht nach der Anzahl der Transaktionen in absteigender Reihenfolge.

Verwenden Sie die folgenden Tabellen:

Purchasing.Suppliers
Purchasing.SupplierTransactions
Der Bericht sollte die folgenden Spalten anzeigen:

[Name des Lieferanten]: Name des Lieferanten aus der Tabelle Purchasing.Suppliers
[Anzahl der Transaktionen]: die Anzahl der Transaktionen jedes Lieferanten
Nicht alle Lieferanten haben erfolgreiche Transaktionen. Wenn ein Lieferant keine erfolgreiche Transaktion hat, wird 0 (Null) angezeigt.
[Anmerkungen]: Interne Kommentare des Lieferanten / InternalComments/
Nicht alle Lieferanten haben Kommentare. Wenn ein Lieferant keinen Kommentar hat, wird 'N/A' angezeigt.
*/

SELECT *
FROM INFORMATION_SCHEMA.SPALTEN
WHERE COLUMN_NAME LIKE '%Transaktion%'

SELECT * FROM Einkauf.Lieferanten
SELECT * FROM Einkauf.LieferantenTransaktionen;

SELECT
    s.SupplierName AS [Name des Lieferanten], st.SupplierTransactionID
FROM
    Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID


SELECT
    s.SupplierName AS [Lieferantenname], COUNT(st.SupplierTransactionID)
FROM
    Purchasing.Suppliers s
    LEFT JOIN Einkauf.LieferantenTransaktionen st ON s.LieferantenID = st.LieferantenID
GROUP BY
    s.Lieferantenname

SELECT s.SupplierName AS [Lieferantenname],
    ISNULL(COUNT(st.FinalizationDate), 0) AS [Anzahl der Transaktionen],
    ISNULL(s.InternalComments, 'N/A') AS [Notizen]
FROM Einkauf.Lieferanten s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.Lieferantenname, s.InterneKommentare
ORDER BY [Anzahl der Transaktionen] DESC;

SELECT s.SupplierName AS [Name des Lieferanten],
    COUNT(st.SupplierTransactionID) AS [Anzahl der Transaktionen],
    ISNULL(s.InternalComments, 'N/A') AS [Anmerkungen]
FROM Einkauf.Lieferanten s
    LEFT JOIN Einkauf.LieferantenTransaktionen st ON s.LieferantenID = st.LieferantenID
GROUP BY s.Lieferantenname, s.InterneKommentare
ORDER BY [Anzahl der Transaktionen] DESC;
-----

USE WideWorldImporters;
GO
SELECT s.SupplierName AS [Name des Lieferanten],
    ISNULL(COUNT(st.SupplierTransactionID), 0) AS [Anzahl der Transaktionen],
    ISNULL(s.InternalComments, 'N/A') AS [Notizen]
FROM Einkauf.Lieferanten s
    LEFT JOIN Einkauf.LieferantenTransaktionen st ON s.LieferantenID = st.LieferantenID
GROUP BY s.Lieferantenname, s.InterneKommentare
ORDER BY [Anzahl der Transaktionen] DESC;


/*
Übung #3
Erstellen Sie einen Bericht über die Lieferantenreferenzen / SupplierReference /, ihre erwarteten Gesamtaußenpreise (Einheitspreise multipliziert mit den bestellten Ausgängen /OrderedOuters*ExpectedUnitPricePerOuter/ )
und die Gesamtzahl der bestellten Abgänge /OrderedOuters / für jede "Lieferantenreferenz".

Geben Sie nur die Lieferanten zurück, bei denen das erwartete Lieferdatum größer als 2014-03-31 ist.

Gruppieren Sie die Ergebnisse nach den Lieferantenreferenzen.

Ordnen Sie den Bericht nach [Lieferantenreferenz] in aufsteigender Reihenfolge.

Geben Sie den Preis in einer beliebigen US-Währungsformatierung zurück.

Verwenden Sie die folgenden Tabellen:

Purchasing.PurchaseOrderLines
Purchasing.PurchaseOrders
Der Bericht sollte die folgenden Spalten anzeigen:

[Lieferantenreferenz] -> aus der Tabelle Purchasing.PurchaseOrders
[Erwarteter äußerer Gesamtpreis] -> der erwartete Preis nach Ausgängen
[Bestellte Ausgänge] -> die Gesamtzahl der bestellten Ausgänge
*/

SELECT * FROM Einkauf.PurchaseOrderLines
SELECT * FROM Einkauf.EinkaufBestellungen;

USE WideWorldImporters;
GO
SELECT
    po.SupplierReference AS [Lieferantenreferenz],
    FORMAT(SUM(pol.OrderedOuters * pol.ExpectedUnitPricePerOuter), 'C') AS [Erwarteter äußerer Gesamtpreis],
    SUM(pol.BestellteAusgänge) AS [BestellteAusgänge]
FROM
    Purchasing.PurchaseOrderLines pol
    INNER JOIN Purchasing.PurchaseOrders po ON pol.PurchaseOrderID = po.PurchaseOrderID
WHERE
    po.ExpectedDeliveryDate > '2014-03-31'
GROUP BY
    po.SupplierReference
ORDER BY
    [Lieferantenreferenz] ASC;


/*
Übung #4
Erstellen Sie einen Bericht über die letzte Bestellung eines jeden Kunden und zeigen Sie, wann diese mit einem bestimmten Zeitpunkt verglichen wurde.

Deklarieren Sie zunächst eine Variable, die einen Datumswert enthält, und setzen Sie ihr das aktuelle Datum zu.

Deklarieren Sie dann eine weitere Variable, die einen String enthält, und setzen Sie den Wert auf '%Toys%'.

Ihr Bericht sollte keinen Kundennamen enthalten, der den Wert der erstellten String-Variable enthält.

Verwenden Sie die folgenden Tabellen:

Sales.Orders
Verkäufe.Kunden
Ordnen Sie den Bericht in [Kundenname] absteigender Reihenfolge.

Der Bericht sollte die folgenden Spalten anzeigen:

[Kundenname]: Spalte Kundenname aus der Tabelle Sales.Customers
[Verstrichene Tage]: Die Differenz in Tagen zwischen der Variable Erstellungsdatum und dem letzten Bestelldatum aus der Tabelle Sales.Orders
*/
USE WideWorldImporters;
GO

DECLARE @currentdate AS DATE = GETDATE();
DECLARE @excludestring AS NVARCHAR(50) = '%Toys%';

SELECT
    c.CustomerName AS [Kundenname],
    DATEDIFF(DAY, o.OrderDate, @currentdate) AS [Vergangene Tage]
FROM
    Sales.Orders o
    INNER JOIN Vertrieb.Kunden c ON o.KundenID = c.KundenID
WHERE
    c.Kundenname NOT LIKE @excludeString
    AND o.OrderDate = (
        SELECT MAX(BestellDatum)
        FROM Verkäufe.Aufträge
        WHERE KundenID = o.KundenID
    )
ORDER BY
    [Kundenname] DESC;

/*
Übung #5:
Erstellen Sie eine skalare benutzerdefinierte Funktion mit Schemabindung im Schema Sales namens SmallestOrderQuantity.

Geben Sie die kleinste Menge zurück, die zu einem Auftrag gehört. Die OrderID wird als Parameter übergeben.

Die Funktion sollte einen Wert vom Typ Int (Menge) zurückgeben.

Verwenden Sie die folgenden Tabellen:

Sales.OrderLines
Die Funktion sollte einen Parameter akzeptieren:

@orderid int
Zeigen Sie, wie Sie die skalare benutzerdefinierte Funktion in einer Abfrage verwenden!
*/
SELECT TOP 1 Menge
    FROM Verkäufe.OrderLines
    WHERE OrderID = 1
    REIHENFOLGE NACH MENGE ABSTEIGEND;

SELECT MIN(Menge)
    FROM Verkäufe.OrderLines
    WHERE OrderID = 1

USE WideWorldImporters;
GO
CREATE OR ALTER FUNCTION Sales.SmallestOrderQuantity(@orderid int)
RETURNS int
MIT SCHEMABINDUNG
AS
BEGIN
    DECLARE @Menge int;
    SELECT @Menge = MIN(Menge)
    FROM Umsatz.Auftragszeilen
    WHERE OrderID = @orderid;
    RETURN @Menge;
END;
GO


-- SHOWCASE
SELECT OrderID, Sales.SmallestOrderQuantity(OrderID) AS SmallestQuantity
FROM Sales.OrderLines
ORDER BY OrderID;

/*
Übung #6:
Erstellen Sie eine Ansicht mit Schemabindung im Verkaufsschema namens CustomerYearlyOrder.

Geben Sie die Kunden und die zugehörigen jährlichen Auftragsnummern zusammen mit dem Auftragserfolg zurück.

Ein Auftrag ist erfolgreich, wenn die Kommissionierung abgeschlossen ist.

Gruppieren Sie sie nach dem Kundennamen und dem Jahr des Auftrags.

Verwenden Sie die folgenden Tabellen:

Vertrieb.Kunden
Sales.Orders
Der Bericht sollte die folgenden Spalten anzeigen:

[Kundenname] : Kundenname aus Sales.Customers
[Auftragsjahr] : Das Jahr des Auftragsdatums aus Sales.Orders
[Aufträge]: Die Anzahl der Aufträge aus Sales.Orders
[Picking Completed]: Die Anzahl der abgeholten Aufträge aus Sales.Orders / PickingCompletedWhen /
[Differenz]: Die Differenz zwischen den Spalten "Aufträge" und "Kommissionierung abgeschlossen".
Zeigen Sie, wie Sie die Ansicht in einer Abfrage verwenden!
*/
GO
CREATE OR ALTER VIEW Sales.CustomerYearlyOrder
MIT SCHEMABINDUNG
AS
SELECT c.CustomerName AS [Kundenname],
       YEAR(o.OrderDate) AS [Order Year],
       COUNT(*) AS [Aufträge],
       COUNT(o.PickingCompletedWhen) AS [Picking Completed],
       COUNT(*) - COUNT(*) AS [Differenz]
FROM Vertrieb.Kunden c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, YEAR(o.OrderDate);
GO
SELECT *
FROM Vertrieb.KundenJahresBestellung


USE WideWorldImporters;
GO
CREATE OR ALTER VIEW Sales.CustomerYearlyOrder
MIT SCHEMABINDUNG
AS
SELECT c.CustomerName AS [Kundenname],
       YEAR(o.OrderDate) AS [Order Year],
       COUNT(o.OrderID) AS [Orders],
       SUM(
           CASE WHEN o.PickingCompletedWhen IS NOT NULL THEN 1
           ELSE 0 END) AS [Kommissionierung abgeschlossen],
       COUNT(o.OrderID) - SUM(CASE WHEN o.PickingCompletedWhen IS NOT NULL THEN 1 ELSE 0 END) AS [Difference]
FROM Vertrieb.Kunden c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, YEAR(o.OrderDate);
GO
--SHOWCASE
SELECT *
FROM Vertrieb.KundenJahresBestellung;

/*
Übung #7:
Erstellen Sie eine Inline-Tabellenfunktion im Anwendungsschema mit dem Namen CityReport.

Sie sollte zwei Parameter verwenden:

@salesterritory of nvarchar(50), mit einem Standardwert von 'New England'
@bigcitylimit of decimal(18,2), mit einem Standardwert von 100000.00
Liefert die Verkaufsgebiete und die dazugehörigen Städte zusammen mit den Einwohnerzahlen, aber nur für die Städte, deren Einwohnerzahlen bekannt sind (und nicht fehlen)!

Verwenden Sie die folgenden Tabellen:

Application.StateProvinces
Anwendung.Städte
Der Bericht sollte die folgenden Spalten anzeigen:

[Sales Territory]: Spalte SalesTerritory aus Application.StateProvinces
[State] : Spalte StateProvinceName aus Application.StateProvinces
[Stadt]: Spalte CityName aus Application.Cities
[City Population]: Spalte LatestRecordedPopulation aus Application.Cities
[Big City Percentage]: City LatestRecordedPopulation percentage of @bigcitylimit parameter value Anzeige des Prozentwerts mit einer Genauigkeit von 2 Ziffern
Zeigen Sie, wie Sie die tabellenbewertete Funktion verwenden!
*/
SELECT * FROM Application.StateProvinces
SELECT * FROM Anwendung.Städte

SELECT sp.SalesTerritory AS [Sales Territory],
        sp.StateProvinceName AS [Bundesland],
        c.CityName AS [Stadt],
        c.LatestRecordedPopulation AS [Stadtbevölkerung],
        CAST((c.LatestRecordedPopulation / 100000.00 * 100) AS decimal(6,2)) AS [Big City Percentage]
FROM Application.StateProvinces sp
INNER JOIN Application.Cities c ON sp.StateProvinceID = c.StateProvinceID
--WHERE sp.SalesTerritory = 'New England' AND c.LatestRecordedPopulation IS NOT NULL



USE WideWorldImporters
GO

CREATE OR ALTER FUNCTION Application.CityReport (@salesterritory nvarchar(50) = 'New England',
                                        @bigcitylimit dezimal(18,2) = 100000.00)
RETURNS TABLE
AS
RETURN
(
    SELECT sp.SalesTerritory AS [Vertriebsgebiet],
        sp.StateProvinceName AS [Bundesland],
        c.CityName AS [Stadt],
        c.LatestRecordedPopulation AS [Stadtbevölkerung],
        CAST((c.LatestRecordedPopulation / @bigcitylimit * 100) AS decimal(18,2)) AS [Big City Percentage]
    FROM Application.StateProvinces sp
    INNER JOIN Anwendung.Städte c ON sp.StateProvinceID = c.StateProvinceID
    WHERE sp.SalesTerritory = @salesterritory AND c.LatestRecordedPopulation IS NOT NULL
)
GO

SELECT *
FROM Application.CityReport(default,default);
SELECT *
FROM Application.CityReport('Plains',1);


/*
Übung #8:
Erstellen Sie die folgende Tabelle:

DROP TABLE IF EXISTS Anwendung.LogAudit;
GO
CREATE TABLE Anwendung.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);
Fügen Sie mit INSERT-Anweisungen Datensätze in diese Tabelle ein:

INSERT INTO Application.LogAudit (LogData) VALUES ('Dies ist ein Log-Daten!');
Erstellen Sie im Anwendungsschema eine gespeicherte Prozedur namens DeleteLogData.

Die gespeicherte Prozedur löscht einen Datensatz aus der Tabelle Application.LogAudit auf der Grundlage von LogAuditId, die als Parameter übergeben wird.

Die gespeicherte Prozedur sollte die obligatorischen Spalten als Parameter akzeptieren:

@logauditid int
Sie sollte auch einen OUTPUT-Parameter akzeptieren:

@deletedlogdata nvarchar(50)
Verwenden Sie die Funktionen zur Fehlerbehandlung, um die Fehlernummer und die Fehlermeldung anzuzeigen!

Wenn der Löschvorgang erfolgreich war, geben Sie den gelöschten LogData-Wert im OUTPUT-Parameter zurück!

Wenn ein Fehler auftritt oder die gelöschten Zeilen 0 waren, behandeln Sie den Fehler:

print: 'Löschen von Log Audit fehlgeschlagen!'
print: 'Fehlernummer: ' und die Fehlernummer
print: 'Fehlermeldung: ' und die Fehlermeldung
-1 als Rückgabewert zurückgeben
Zeigen Sie nicht die Anzahl der betroffenen Zeilen in der T-SQL-Abfrage als Informationsmeldung an.

Zeigen Sie, wie Sie die Stored Procedure verwenden!
*/

DELETE FROM Anwendung.LogAudit
        WHERE LogAuditId = 10;

SELECT * FROM Anwendung.LogAudit

 SELECT LogData
        FROM Anwendung.LogAudit
        WHERE LogAuditId = 3


DECLARE @deletedlogdata nvarchar(50);
SELECT @gelöschteLogdaten = LogData
        FROM Anwendung.LogAudit
        WHERE LogAuditId = 2
SELECT @gelöschteProtokolldaten

USE WideWorldImporters;
GO

DROP TABLE IF EXISTS Anwendung.LogAudit;
GO
CREATE TABLE Anwendung.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);

INSERT INTO Application.LogAudit (LogData) VALUES ('Dies ist ein Log-Daten!');


GO
CREATE OR ALTER PROCEDURE Application.DeleteLogData (
    @logauditid int,
    @deletedlogdata nvarchar(50) OUTPUT
)
AS
BEGIN
    NOCOUNT EINSCHALTEN;

    BEGIN TRY
        SELECT @deletedlogdata = LogData
        FROM Anwendung.LogAudit
        WHERE LogAuditId = @logauditid;

        DELETE FROM Anwendung.LogAudit
        WHERE LogAuditId = @logauditid;
        WENN @@ROWCOUNT = 0
        BEGIN
            --THROW 55555, 'ROWCOUNT=0', 1;
            RAISERROR ('ROWCOUNT=0', 16, 1);
        END
    END TRY
    BEGIN CATCH
        PRINT 'Löschen des Log-Audits fehlgeschlagen!
        PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
        PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
        SET @deletedlogdata = NULL;
        RETURN -1;
    END CATCH
ENDE;
GO
--SHOWCASE
DECLARE @deletedlogdatavar nvarchar(50);
EXEC Application.DeleteLogData @logauditid = 10, @deletedlogdata = @deletedlogdatavar OUTPUT;
SELECT @deletedlogdatavar AS Deleted;
GO
DECLARE @deletedlogdatavar nvarchar(50);
EXEC Application.DeleteLogData @logauditid = 1, @deletedlogdata = @deletedlogdatavar OUTPUT;
SELECT @deletedlogdatavar AS Deleted;
GO

Versuch

hsg-reader-sqlserver-final-trial-exam

Wichtig

  • Sie können jede Online-Ressource verwenden, aber bitte arbeiten Sie allein!
  • Forken Sie das Repository und klonen Sie das Repository auf Ihren eigenen Rechner
  • UTF8 OHNE BOM Zeichencodierung verwenden
  • Kopieren Sie nicht einfach Antworten und Lösungen, sondern verwenden Sie Ihre eigenen Worte
  • Keine Autograder-Bewertung.
  • Aufgabe 4 erfordert mongoDB und kann nach dem Erlernen des NoSQL-Materials gelöst werden
  • Für Aufgabe 4 können Sie mongoDB auf Ihrem System installieren

Aufgabe #1:

Eine führende Fastfood-Kette bittet Sie, eine Datenbank mit Lieferdiensten zu erstellen, die ihre Produkte verkaufen.

In dieser "Homedelivery"-Datenbank müssen Sie nun einen Teil der Datenbank gestalten.

Die folgenden Daten sollten verarbeitet werden:

  • Geschäft (Details zum Geschäft)
  • Menü (Katalog der aktuellen Produkte in den Geschäften)
  • Verbindungen zwischen den Tabellen

Die folgenden Daten sollten verarbeitet werden:

  • Store ID (erhalten Sie eine eindeutige Kennung)
  • Derzeit aktive Katalog-ID
  • Wann hat der Laden das letzte Mal einen erfolgreichen Katalog erhalten?
  • Wann war der letzte erfolgreiche Katalog
  • Lieferfirma aus fester Liste
  • Die Öffnungszeiten
  • Das Geschäft ist 7/24 geöffnet

Ein Geschäft kann mehrere Kataloge haben, aber nur ein Geschäft kann einen einzigen Katalog (nach ID) verwenden. Ein Geschäft kann vorerst nur mit einem Lieferanten zusammenarbeiten.

Erstellen Sie die richtigen Datenbanktabellen mit dem richtigen Typ von Spalten.

Verwenden Sie mindestens die folgenden Beschränkungen:

  • PRIMARY KEY
  • FOREIGN KEY

Definieren Sie zusätzliche Beschränkungen und Indizes, falls erforderlich!

Achten Sie auf die Normalisierung!

Schicken Sie die Bordskripte zurück!

CREATE DATABASE Hauszustellung;
GO
-- Shops, Kataloge, Lieferanten
-- Shop->Katalog 1:N, Shop->Lieferanten 1:N
USE Homedelivery;
GO


DROP TABLE IF EXISTS dbo.catalogs,dbo.stores,dbo.transporters;
GO

CREATE TABLE dbo.transporters(
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	,transp_name NVARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE dbo.stores(
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	,store_name NVARCHAR(150) NOT NULL UNIQUE
	,transporter_id INT
		CONSTRAINT FK_stores_transporterid FOREIGN KEY REFERENCES dbo.transporters(id)
	,eröffnung_start TIME
	,opening_end TIME
	,isOpenAllday BIT NOT NULL
	,CONSTRAINT CK_stores_openingcheck
		CHECK ( ( isOpenAllday = 1 AND opening_start IS NULL AND opening_end IS NULL) OR
		( isOpenAllday = 0 AND opening_start IS NOT NULL AND opening_end IS NOT NULL )
		)
	,CONSTRAINT CK_stores_openingstartend CHECK ( opening_start != opening_end )
);

CREATE TABLE dbo.catalogs (
	 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	,katalog_name NVARCHAR(150) NOT NULL UNIQUE
	,catalog_success_created DATETIME2 NOT NULL
	,last_success_transport DATETIME2 NOT NULL
	,usedbystore_id INT
		CONSTRAINT FK_catalogs_storeid FOREIGN KEY REFERENCES dbo.stores(id)
	,isActive BIT NOT NULL
		INDEX idx_catalogs_activeforstore UNIQUE (usedbystore_id,isActive) WHERE isActive=1
	,CONSTRAINT CK_catalog_successtime CHECK (letzter_Erfolg_transport >catalog_success_created )
);

/*
INSERT INTO dbo.transporter
VALUES ('corleone familie'),('ngdrangheta'),('bratva');
INSERT INTO dbo.stores
VALUES('Jimmy kebap',2,'09:10','09:10',0);
INSERT INTO dbo.stores
VALUES('Jimmy kebap',2,'09:10','09:15',0);
INSERT INTO dbo.stores
VALUES('videki robi',2,NULL,NULL,0);
INSERT INTO dbo.stores
VALUES('videki robi',2,'18:00','07:00',0);
INSERT INTO dbo.stores
VALUES('korcsma',2,NULL,NULL,1);
INSERT INTO dbo.kataloge
VALUES('BBQ Burger','2021-11-10','2021-11-11',2,0);
INSERT INTO dbo.catalogs
VALUES ('Vega Burger','2021-11-10','2021-11-11',2,1);
INSERT INTO dbo.catalogs
VALUES ('Salami Burger','2021-11-10','2021-11-11',2,1);
*/

Aufgabe #2:

Verwenden Sie die WideWorldImporters-Beispieldatenbank!

Schreiben Sie eine einfache gespeicherte Prozedur für die Tabelle Application.Countries, die den Namen des Kontinents und die zugehörige Bevölkerungsübersicht zurückgibt.

Die gespeicherte Prozedur im Anwendungsschema sollte GetContinentRanking heißen.

Die gespeicherte Prozedur benötigt einen Parameter: - @rank tinyint: Standardwert ist 1

Der Wert des Parameters vom Typ Integer gibt an, wie viele Kontinente die gespeicherte Prozedur auf der Grundlage der Rangfolge nach Bevölkerung zurückgeben soll.

Zum Beispiel:

  • Wenn der Parameter auf 3 gesetzt wird, sollten der Name und die Bevölkerung des drittbevölkerungsreichsten Kontinents zurückgegeben werden.
  • Wenn der Parameter auf 5 gesetzt wird, sollten der Name und die Bevölkerung des fünftbevölkerungsreichsten Kontinents zurückgegeben werden.
  • Wenn der Parameter auf 1 gesetzt wird, sollten der Name und die Bevölkerung des bevölkerungsreichsten Kontinents zurückgegeben werden.

Verwenden Sie die nachstehenden Tabellen:

  • Anwendung.Länder

Geben Sie die gespeicherte Prozedur mit den folgenden Spalten zurück:

  • [Rangfolge]: der Wert der Rangfolge
  • [Name des Kontinents]: Kontinent-Spalte in der Tabelle Application.Countries
  • [Bevölkerung]: Zusammenfassung der Spalte LatestRecordedPopulation in der Tabelle Application.Countries

Zeigen Sie, wie Sie die gespeicherte Prozedur verwenden!

USE WideWorldImporters;
GO
CREATE OR ALTER PROCEDURE Anwendung.GetContinentRanking
(@rank TINYINT=1)
AS
	SET NOCOUNT ON;
	SELECT @rank AS ranking
		,Kontinent AS [Kontinentname]
		,SUM(LatestRecordedPopulation) Bevölkerung
	FROM Anwendung.Länder
	GROUP BY Kontinent
	ORDER BY SUM(Zuletzt erfassteBevölkerung) DESC
	OFFSET (@rank-1) ROWS FETCH NEXT 1 ROWS ONLY;
GO

/*
Die gespeicherte Prozedur benötigt einen Parameter: - @rank tinyint: Standardwert 1
Die gespeicherte Prozedur sollte die folgenden Spalten zurückgeben:
    [Rang]: der Wert der Rangliste
    [continent name]: Spalte continent in der Tabelle Application.Countries
    [population]: zusammengefasste Spalte LatestRecordedPopulation in der Tabelle Application.Countries
*/

/*
Demonstrieren Sie, wie Sie die gespeicherte Prozedur verwenden!
*/

EXEC Application.GetContinentRanking DEFAULT;

EXEC Application.GetContinentRanking 3;

-- Version der Fensterfunktion
GO
CREATE OR ALTER PROC Anwendung.GetContinentRanking
    @rank TINYINT = 1
AS
BEGIN
    SELECT rang, kontinent_name, bevölkerung
    FROM (
        SELECT
            RANK() OVER (ORDER BY SUM(LatestRecordedPopulation) DESC) ranking,
            Kontinent AS 'kontinent_name',
            SUM(LatestRecordedPopulation) AS 'Bevölkerung
        FROM Anwendung.Länder
        GROUP BY Kontinent) AS kontinent_bevoelkerung
    WHERE rang = @rank
ENDE;
GO

EXEC Application.GetContinentRanking DEFAULT;

EXEC Application.GetContinentRanking 3;

Aufgabe #3:

Verwenden Sie die WideWorldImporters-Beispieldatenbank!

Schreiben Sie ein T-SQL-Skript mit folgendem Inhalt:

  1. Es wird ein SQL-Login namens "SampleLogin" mit einem Passwort erstellt und WideWorldImporters als Standarddatenbank festgelegt.
  2. Erstellen Sie in der WideWorldImporters-Datenbank einen "SampleUser"-Benutzer für die vorherige Anmeldung.
  3. Fügt den Benutzer zur Datenbankrolle "Externe Verkäufe" hinzu.
  4. Fügen Sie der Tabelle Application.People die Berechtigung SELECT für diese Rolle hinzu.
  5. Testet das Lesen von Daten, indem er den Benutzer "SampleUser" verkörpert und eine SELECT-Abfrage aus der Tabelle Application.People verwendet.
  6. Stellt den Kontext der Ausführung für den vorherigen Benutzer wieder her.
  7. Ermittelt den Namen des aktuellen Benutzers.

Jede Aufgabe ist eine T-SQL-Anweisung.

Kopieren Sie das Folgende nach Github:

  • die Skriptdatei
USE WideWorldImporters;
GO
CREATE LOGIN [SampleLogin] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[WideWorldImporters], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE USER SampleUser FOR LOGIN [SampleLogin];
GO
ALTER ROLE [External Sales] ADD MEMBER [SampleUser];
GO
GRANT SELECT ON Application.People TO [Externer Vertrieb];
GO
EXECUTE AS USER='SampleUser';
GO
SELECT TOP 5 * FROM Application.People;
--SELECT USER_NAME();
GO
REVERT
GO
SELECT NUTZER_NAME();

Aufgabe #5:

Reproduzieren Sie einen Deadlock mit dem folgenden Skript!

Öffnen Sie 3 neue Abfragefenster!

Führen Sie die folgenden Skripte nacheinander in dem angegebenen Fenster aus:

  1. Fenster:
DROP TABELLE IF EXISTS Anmeldung.Deadlock;
GO
CREATE TABELLE Anmeldung.deadlock
(
    DeadlockId int NOT NULL PRIMARY KEY
);
BEGIN TRANSAKTION
INSERT INTO Anmeldung.Deadlock (deadlockId) WERTEN (1);
  1. Fenster:
BEGIN TRANSAKTION
INSERT INTO Anmeldung.Deadlock (deadlockId) WERTEN (2);
  1. Fenster:
SELECT DeadlockId VON Anmeldung.Deadlock WHERE DeadlockId = 2;
  1. Fenster:
BEGIN TRANSAKTION
INSERT INTO Anmeldung.Deadlock (deadlockId) WERTEN (3);
  1. Fenster:
SELECT DeadlockId VON Anmeldung.Deadlock WHERE DeadlockId = 3;
  1. Fenster:
SELECT DeadlockId VON Anmeldung.Deadlock WHERE DeadlockId = 1;

Eines der 3 Fenster zeigt eine rote Deadlock-Fehlermeldung an.

Der Befehl ROLLBACK TRAN kann verwendet werden, um alle noch laufenden Befehle anzuhalten.

  1. Sammeln Sie die Deadlock-Grafik xml für die von Ihnen reproduzierten Deadlocks!

  2. Speichern Sie Deadlock Graph Xml im XDL-Dateiformat!

  3. Stellen Sie den Deadlock grafisch dar (mit einem Werkzeug Ihrer Wahl) und machen Sie einen Screenshot davon.

  4. Konfigurieren Sie die Datenbank so, dass dieser spezielle Deadlock nicht mehr auftritt!

Kopieren Sie das Folgende nach github und nummerieren Sie die Dateien wie folgt::

  • 1: die .XDL-Datei
  • 2: die Bilddatei für den Screenshot
  • 3: die T-SQL-Anweisung
USE DBAHEALTH;
GO

--EXEC dbo.sp_BlitzLock @EndDate='20230423', @StartDate='20230410';

ALTER DATABASE [WideWorldImporters] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

------------------------------------ BAL
--- 1. ----------- verließ Sitzung:
BEGIN TRAN t1;
UPDATE dbo.ExamLefty SET Numbers = Numbers + 1;
--- 4. ----------- left session:
SELECT * FROM dbo.ExamRighty;

ROLLBACK TRAN

------------------------------------ JOBB
--- 2. -- Sitzung auf der rechten Seite:
BEGIN TRAN t2;
UPDATE dbo.ExamRighty SET Numbers = Numbers + 1;
--- 3. -- Rechte Sitzung:
SELECT * FROM dbo.ExamLefty;

Aufgabe #6:

Erstellen Sie die Tabelle SQLTestDB mit dem folgenden Skript!

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABELLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) WERTEN (1, 'test1')
INSERT INTO SQLTest (ID, c1) WERTEN (2, 'test2')
INSERT INTO SQLTest (ID, c1) WERTEN (3, 'test3')
INSERT INTO SQLTest (ID, c1) WERTEN (4, 'test4')
INSERT INTO SQLTest (ID, c1) WERTEN (5, 'test5')
GO

SELECT * VON SQLTest
GO

Die SQLTestDB-Datenbank erfordert eine Point-in-Time-Wiederherstellung nach den Anforderungen des Unternehmens.

Schreiben Sie ein T-SQL-Skript mit folgendem Inhalt:

  1. Konfigurieren Sie das Datenbank-Wiederherstellungsmodell entsprechend den geschäftlichen Anforderungen.

  2. Vor der Sicherung wird eine vollständige Datenbankintegritätsprüfung der SQLTestDB-Datenbank durchgeführt, wobei die Prüfung für nicht geclusterte Indizes übersprungen wird.

  3. Erstellt eine Vollsicherung mit einem festen Pfad und dem Dateinamen SQLTestDB_Full.bak.

  4. Differenzielle Sicherung mit Komprimierung auf festem Pfad mit dem Dateinamen SQLTestDB_Diff.bak.

  5. Erstellen Sie eine Logsicherung mit Komprimierung und Prüfsumme auf einem festen Pfad mit dem Dateinamen SQLTestDB_Log.trn.

  6. Überprüft eine Sicherung, um festzustellen, ob der Sicherungssatz vollständig oder lesbar ist (stellt ihn aber nicht wieder her).

Jede Aufgabe ist eine T-SQL-Anweisung. Die Anweisungen sollten nur die angeforderten Optionen/Einstellungen enthalten!

  1. Planen Sie die oben genannten Sicherungsanweisungen mit SQL-Agent-Jobs (Sie können auch Ola Hallengren-Jobs verwenden) und erstellen Sie einen geeigneten Sicherungsplan, der Folgendes berücksichtigt:
    • die Datenbank kann Hunderte von GB groß sein
    • die Wiederherstellung im Falle einer Katastrophe zu optimieren
    • bei einer Katastrophe können bis zu 5 Minuten an Daten verloren gehen
    • Zeitpläne sollten so benannt werden, dass die Art der Sicherung klar ersichtlich ist

Machen Sie Screenshots von den Sicherungsplänen in SQL Server Management Studio, von den Dialogfeldern für die Auftragsplaneigenschaften der Pläne.

Kopieren Sie das Folgende nach Github:

  • die Skriptdatei
  • die Screenshot-Dateien
USE [master]
GO
ALTER DATABASE [SQLTestDB] SET RECOVERY FULL;
GO
--https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

DBCC CHECKDB (SQLTestDB, NOINDEX);
GO
USE master;
GO
BACKUP DATABASE [SQLTestDB]
TO DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Full.bak';
GO
BACKUP DATABASE [SQLTestDB]
TO DISK = N'S:\Instanz\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Diff.bak'
MIT DIFFERENZIAL, KOMPRESSION;
GO
BACKUP LOG [SQLTestDB]
TO DISK = N'S:S\instance\MSSQL\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Log.trn'
MIT KOMPRIMIERUNG, PRÜFSUMME;
GO

VERIFYONLY WIEDERHERSTELLEN
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Log.trn';
GO
VERIFYONLY WIEDERHERSTELLEN
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Diff.bak';
GO
VERIFYONLY WIEDERHERSTELLEN
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Full.bak';
GO
-- nicht hallegreen

/*
https://github.com/green-fox-academy/teaching-materials/blob/master/workshop/sql-server-database-maintenance/sql-server-database-maintenance.md
https://ola.hallengren.com/
*/
-- JOBS https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql?view=sql-server-ver15
-- FULL BACKUP
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltestdb full backup job',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'User3', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltestdb full backup job', @server_name = N'USER3-VM'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb full backup job', @step_name=N'sqltestdb dbcc check',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=3,
		@on_fail_action=2,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'DBCC CHECKDB (SQLTestDB, NOINDEX);',
		@database_name=N'DBAHealth',
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb full backup job', @step_name=N'sqltestdb full backup job',
		@step_id=2,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_fail_action=2,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE dbo.DatabaseBackup
@Datenbanken = ''SQLTestDB'',
@Verzeichnis = ''C:\MSSQL Server'',
@BackupType = ''FULL'',
@Verify = ''Y'';',
		@database_name=N'DBAHealth',
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'sqltestdb full backup job',
		@enabled=1,
		@start_step_id=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@description=N'',
		@category_name=N''[Uncategorized (Local)]',
		@owner_login_name=N'User3',
		@notify_email_operator_name=N'',
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltestdb full backup job', @name=N'sqltestdb full backup sch',
		@enabled=1,
		@freq_type=8, -weekly
		@freq_interval=1, --sonntag
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relatives_intervall=0,
		@freq_recurrence_factor=1,
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=30000,
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @zeitplan_id
GO

-- DIFF SICHERUNGSAUFTRAG

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltestdb diff backup job',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'User3', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltestdb diff backup job', @server_name = N'USER3-VM'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltestdb diff backup job', @step_name=N'sqltestdb diff job',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_fail_action=2,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE dbo.DatabaseBackup
@Datenbanken = ''SQLTestDB'',
@Verzeichnis = ''C:\MSSQL Server'',
@BackupType = ''DIFF'',
@Compress = ''Y'';',
		@database_name=N'DBAHealth',
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'sqltestdb diff backup job',
		@enabled=1,
		@start_step_id=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@description=N'',
		@category_name=N''[Uncategorized (Local)]',
		@owner_login_name=N'User3',
		@notify_email_operator_name=N'',
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltestdb diff backup job', @name=N'sqltestdb diff job sch',
		@enabled=1,
		@freq_type=8,
		@freq_interval=8, --wednesday
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relatives_intervall=0,
		@freq_recurrence_factor=1,
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=40000,
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @zeitplan_id
GO

-- SICHERUNGSAUFTRAG PROTOKOLLIEREN

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'sqltest log backup',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'User3', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'sqltest log backup', @server_name = N'USER3-VM'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'sqltest log backup', @step_name=N'sqltestdb log backup',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_fail_action=2,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE dbo.DatabaseBackup
@Datenbanken = ''SQLTestDB'',
@Verzeichnis = ''C:\MSSQL Server'',
@BackupType = ''LOG'',
@Compress = ''Y'',
@CheckSum = ''Y'';',
		@database_name=N'DBAHealth',
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'sqltest log backup',
		@enabled=1,
		@start_step_id=1,
		@notify_level_eventlog=0,
		@notify_level_email=2,
		@notify_level_page=2,
		@delete_level=0,
		@description=N'',
		@category_name=N''[Uncategorized (Local)]',
		@owner_login_name=N'User3',
		@notify_email_operator_name=N'',
		@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'sqltest log backup', @name=N'sqltestdb log backup sch',
		@enabled=1,
		@freq_type=4, --daily
		@freq_interval=1,
		@freq_subday_type=4, -- Minuten
		@freq_subday_interval=5, -- fünf ...
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20230419,
		@active_end_date=99991231,
		@active_start_time=0,
		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @zeitplan_id
GO

Aufgabe #7:

Die Datenbank SQLRestoreDB muss aus der Sicherung bis zum letzten möglichen Wiederherstellungspunkt wiederhergestellt werden. Die Datenbank muss wiederhergestellt werden, weil sie versehentlich gelöscht wurde.

Schreiben Sie Wiederherstellungsbefehle, die die Datenbank in einer Wiederherstellungskette und damit den gesamten Inhalt der Tabelle wiederherstellen.

Die verfügbaren Backups sind:

  • SQLRestoreDB_Full.bak: Vollständige Sicherung
  • SQLRestoreDB_Log_2.trn: Protokollsicherung
  • SQLRestoreDB_Diff_3.bak: Differenzielle Sicherung
  • SQLRestoreDB_Log_4.trn: Protokollsicherung

Die Namen der Sicherungsdateien zeigen auch die Art der Sicherung und die Reihenfolge, in der sie erstellt wurden.

Die Datenbank befand sich nicht auf dem Standardlaufwerk, so dass sie möglicherweise verschoben werden muss. Schreiben Sie einen Wiederherstellungsbefehl, der auflistet, welche Dateien in der vollständigen Sicherungsdatei enthalten sind.

Die Daten wurden nach der Logsicherung "SQLRestoreDB_Log_4.trn" gelöscht!

Verwenden Sie die optimalste Wiederherstellungskette!

Sobald Sie die Datenbank wiederhergestellt haben, führen Sie die folgende Abfrage aus und speichern das Ergebnis im .csv-Format.

USE SQLRestoreDB;
GO
SELECT 
    GETDATE() AS [examstimestamp],
    @@SERVERNAME AS [myservername],
    * 
VON dbo.SQLRestoreDB;
USE master;
GO
-- Wiederherstellungsliste
RESTORE FILELISTONLY FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak';
GO
/*
RESTORE HEADERONLY FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak';
GO
*/
-- vollständige sicherung wiederherstellenv
RESTORE DATABASE [SQLRestoreDB]
FROM DISK = N'Y:\x\SQLRestoreDB_Full.bak'
WITH MOVE N'SQLRestoreDB' TO N'T:\sqldata\SQLRestoreDB.mdf',
MOVE N'SQLRestoreDB_log' TO N'L:\sqllogs\SQLRestoreDB_log.ldf', NORECOVERY;
-- diff Sicherung
RESTORE DATABASE [SQLRestoreDB] FROM DISK = N'Y:\x\SQLRestoreDB_Diff_3.bak' WITH NORECOVERY;
-- Log-Sicherung
RESTORE LOG [SQLRestoreDB] FROM DISK = N'Y:\x\SQLRestoreDB_Log_4.trn' WITH RECOVERY;
GO
USE SQLRestoreDB;
GO
SELECT
    GETDATE() AS [examstimestamp],
    @@SERVERNAME AS [myservername],
    *
FROM dbo.SQLRestoreDB;

Aufgabe #8:

Bereiten Sie die folgende Tabelle vor:

DROP TABELLE IF EXISTS Anmeldung.LogAudit;
GO
CREATE TABELLE Anmeldung.LogAudit
(
    LogAuditId int NOT NULL IDENTITÄT(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);

Fügen Sie Daten mit dem folgenden INSERT INTO-Befehl hinzu:

INSERT INTO Anmeldung.LogAudit (LogData) WERTEN ('Dies ist eine Protokolldatei!');

Erstellen Sie im Anwendungsschema eine gespeicherte Prozedur namens DeleteLogData.

Die Prozedur löscht einen Datensatz aus der Tabelle Application.LogAudit basierend auf dem übergebenen Parameter LogAuditId.

Die gespeicherte Prozedur sollte den folgenden Parameter verwenden:

  • @logauditid int

Die gespeicherte Prozedur sollte den folgenden OUTPUT-Parameter verwenden:

  • @deletedlogdata nvarchar(50)

Verwenden Sie die Fehlerbehandlung mit den entsprechenden eingebauten Funktionen für Fehlernummern und Fehlermeldungen.

Wenn die Löschung erfolgreich war, kehrt die Prozedur mit dem gelöschten LogData-Wert im OUTPUT-Parameter zurück.

Wenn ein Fehler passiert, muss man damit umgehen:

  • print: 'Löschen des Protokolls Audit fehlgeschlagen!'
  • druckt: 'Fehlernummer: ' und die Fehlernummer
  • print message: 'Fehlermeldung: ' und die Fehlermeldung
  • Wert -1 zurückgeben

Geben Sie ein Beispiel dafür, wie das von Ihnen vorbereitete Verfahren angewendet werden kann.

USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Anwendung.LogAudit;
GO
CREATE TABLE Anwendung.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);
INSERT INTO Application.LogAudit (LogData) VALUES ('Dies ist ein Log-Daten!');
GO
SELECT * FROM Anwendung.LogAudit;
GO
CREATE OR ALTER PROC Application.DeleteLogData (
	 @logauditid int
	,@deletedlogdata nvarchar(50) OUTPUT
)
AS
	NOCOUNT EINSCHALTEN;
	BEGIN TRY
		DECLARE @Mytemptable TABLE (
			logdatas NVARCHAR(50)
		);
		DELETE Anwendung.LogAudit
		OUTPUT deleted.LogData INTO @Mytemptable
		WHERE LogAuditId=@logauditid;

		WENN @@ROWCOUNT  1
			BEGIN
				RAISERROR ('Löschen von LogAudit fehlgeschlagen!',16,1);
			END;

		SELECT @deletedlogdata = logdatas FROM @Mytemptable;
	END TRY
	BEGIN CATCH
		PRINT 'Löschen des Log-Audits fehlgeschlagen!
		PRINT 'Fehlernummer: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
		PRINT 'Fehlermeldung: ' + ERROR_MESSAGE();
		RETURN -1;
	END CATCH
GO

-- Schaukasten

DECLARE @outputvar NVARCHAR(50),@returnstatus INT;
EXEC @returnstatus=Application.DeleteLogData 1, @deletedlogdata = @outputvar OUTPUT;
SELECT @outputvar AS [Gelöschter Protokollwert],@returnstatus AS [Rückgabestatus];
GO
-- Fehler vorzeigen
DECLARE @outputvar NVARCHAR(50),@returnstatus INT;
EXEC @returnstatus=Application.DeleteLogData 1, @deletedlogdata = @outputvar OUTPUT;
SELECT @outputvar AS [Gelöschter Protokollwert],@returnstatus AS [Rückgabestatus];
GO

HSG Reader Abschlussprüfung Normal

Allgemein

  • Gradescope-Link zum Abschnitt "Writing Test
  • Der Test findet zwischen 9:00 und 10:00 Uhr statt. Sie haben 60 Minuten Zeit, um 40 Fragen zu beantworten.
  • Lösungen zu den folgenden Aufgaben Gradescope auf in den Abschnitt Projektaufgabe hochgeladen werden
  • Die Dateien sollten so benannt werden, wie in den Zuweisungen angegeben
  • Die Lösungen können zusammen als .zip-Dateien auf Grade-scop hochgeladen werden.
  • Es werden nur Gradescope-Inhalte bewertet!
  • Stellen Sie sicher, dass die Kodierung Ihrer hochgeladenen Dateien UTF-8 OHNE BOM (Signatur) ist.
  • Stellen Sie sicher, dass Ihre übermittelten SQL-Dateien von SSMS/Azure Data Studio aus "ausführbar" sind.

Erste Schritte

  • Forken Sie diesen Ordner auf Ihren eigenen Github-Benutzer
  • Klonen Sie das geforkte Repository unter Ihrem eigenen Namen auf Ihren Rechner, arbeiten Sie hier
  • Regelmäßiges Commit und klare Commit-Kommentare verwenden
  • Alle Ihre Lösungen sollten auch hier auf github hochgeladen werden

Was finden Sie in der resultierenden Azure-Umgebung auf dem Rechner?

  • Die WideWorldImporters-Datenbank ist funktionsfähig.
  • Eine funktionsfähige Datenbank namens ExamDB (Sie können auch eine Sicherungsdatei ExamDB_Full.bak unter dem Link Backups finden, wenn Sie in Ihrer eigenen Umgebung arbeiten).
  • Außerdem eine Datenbank namens DBAHealth, auf die die Skripte wie sp_Blitz und die Skripte von Ola Hallengren geladen werden.
  • Außerdem enthält der Standard-Backup-Ordner die Backups einer RestoreDB-Datenbank (Sie finden diese auch unter dem Link Backups am Ende des Dokuments).

Was kann verwendet werden?

  • Jede Online-Quelle kann verwendet werden, aber individuell arbeiten
  • NICHT einfach kopieren die Lösungen, nutzen Sie Ihr eigenes Wissen, Ihre Worte
  • NE Push-olj auf GitHub, bis der Mentor ankündigt, dass es
  • Vergessen Sie nicht, hochzuladen Ihre Lösungen hier rechtzeitig zu finden: Gradescope

Aufgaben

Dateien sichern

Deadlock-Skript

Aufgabe #1:

Sie sind an der Entwicklung einer Schuldatenbank beteiligt. Sie haben darum gebeten, dass die Datenbank Lehrer, Schüler, Fächer und verwandte Daten speichern soll.

Die folgenden Daten sollten verarbeitet werden:

  • Angaben zu den Lehrkräften
  • Liste der Themen
  • Daten der Studenten
  • Verbindungen zwischen den Tabellen

Die folgenden Daten sollten verarbeitet werden:

  • Kennung des Lehrers/Schülers/Fachs (eindeutige Kennung erhalten)
  • Name des Lehrers/Schülers, E-Mail-Adresse, Geburtsdatum
  • Betreffende Namen
  • Hinweis für Studenten
  • Telefonnummer des Lehrers
  • Jahr der Einschulung der Schüler (seit der Einschulung) / nur die Jahreszahl sollte gespeichert werden /
  • Aktuelle Klasse von Studenten

Ein Schüler kann mehr als ein Fach haben, genauso wie ein Fach von mehreren Schülern unterrichtet werden kann, aber in diesem Fall unterrichtet ein Lehrer nur ein Fach.

Erstellen Sie die richtigen Datenbanktabellen mit dem richtigen Typ von Spalten.

Verwenden Sie mindestens die folgenden Beschränkungen:

  • PRIMARY KEY
  • FOREIGN KEY

Definieren Sie bei Bedarf zusätzliche Einschränkungen!

Achten Sie auf die Normalisierung!

Beweise aus SQL-Skript, das den oben beschriebenen Vorgang ausführt
Name der Datei WINSQL-01.sql
DROP DATABASE IF EXISTS SchoolDB;
CREATE DATABASE SchoolDB;
GO
USE SchoolDB;
GO
CREATE TABLE dbo.Subjects (
 ID INT IDENTITY(1,1),
 name NVARCHAR(150) NOT NULL);

CREATE TABLE dbo.Students (
 ID INT IDENTITÄT(1,1),
 Name NVARCHAR(500) NOT NULL,
 Email VARCHAR(255) NOT NULL,
 Geburtsdatum DATE NOT NULL,
 Einschreibejahr SMALLINT NOT NULL,
 Klasse NVARCHAR(60) NOT NULL,
 Kommentar NVARCHAR(5000));

CREATE TABLE dbo.Teachers (
 ID INT IDENTITY(1,1),
 Name NVARCHAR(500) NOT NULL,
 Email VARCHAR(255) NOT NULL,
 TelefonNummer VARCHAR(15) NOT NULL,
Geburtsdatum DATE NOT NULL);

CREATE TABLE dbo.Conn_Students_Subjects (
 ID INT IDENTITY(1,1),
 SubID INT NOT NULL,
 StudID INT NOT NULL);
GO
ALTER TABLE dbo.Subjects
ADD CONSTRAINT PK_Subjects PRIMARY KEY (ID);
GO
ALTER TABLE dbo.Students
ADD CONSTRAINT PK_Students PRIMARY KEY (ID);
GO
ALTER TABLE dbo.Teachers
ADD CONSTRAINT PK_Teachers PRIMARY KEY (ID),
CONSTRAINT FK_Teachers_SubID FOREIGN KEY (SubID) REFERENCES dbo.Subjects(ID);
GO
ALTER TABLE dbo.Conn_Students_Subjects
ADD CONSTRAINT PK_Schüler_Schulfächer PRIMARY KEY (ID),
CONSTRAINT FK_Conn_Students_Subjects_SubID FOREIGN KEY (SubID) REFERENCES dbo.Subjects(ID),
CONSTRAINT FK_Conn_Studenten_Fächer_StudID FOREIGN KEY (StudID) REFERENCES dbo.Studenten(ID);
GO
ALTER TABLE dbo.Subjects
ADD CONSTRAINT UQ_Subjects_Name UNIQUE (Name);
GO
ALTER TABLE dbo.Students
ADD CONSTRAINT UQ_Students_Email UNIQUE (Email),
CONSTRAINT CK_Students_Birthdate CHECK (YearOfEnrolment > YEAR(Birthdate)),
CONSTRAINT CK_Students_YearOfEnrolment CHECK (YearOfEnrolment >= 1900 AND YearOfEnrolment > YEAR(Birthdate));
GO
ALTER TABLE dbo.Teachers
ADD CONSTRAINT UQ_Teachers_Email UNIQUE (Email),
CONSTRAINT UQ_Teachers_PhoneNumber UNIQUE (Telefonnummer);
GO

Aufgabe #2:

Verwenden Sie die WideWorldImporters-Beispieldatenbank!

Schreiben Sie eine Abfrage, die die ID, den Namen und den CreditLimit-Wert eines Kunden mit dem Vornamen "Abel" zurückgibt, wobei der Kunde zwischen dem 01.05.2016 und dem 15.05.2016 keine Bestellung aufgegeben hat (Bestellungen, die am 01.05. und 15.05.2016 aufgegeben wurden, sollten nicht berücksichtigt werden!).

Stellen Sie sicher, dass die Bedingung für die Datumssuche SARG-fähig ist!

Verwenden Sie die nachstehenden Tabellen:

  • Vertrieb.Kunden
  • Verkäufe.Aufträge

Die Abfrage sollte die folgenden Spalten zurückgeben:

  • [Kunden-ID]: die ID des Kunden
  • [Kundenname]: Name des Kunden
  • [Kreditlimit]: das Kreditlimit des Käufers
Beweise aus SQL-Skript, das den oben beschriebenen Vorgang ausführt
Name der Datei WINSQL-02.sql
USE WideWorldImporters;
GO
SELECT
    c.CustomerID AS [Kunden-ID],
    c.CustomerName AS [Kundenname],
    c.KreditLimit AS [Kreditlimit]
FROM
    Vertrieb.Kunden c
WHERE
    c.Kundenname LIKE 'Abel%'
    UND NICHT VORHANDEN IST (
        SELECT 1
        FROM Verkauf.Aufträge o
        WHERE
            o.CustomerID = c.CustomerID
            AND o.OrderDate > '2016-04-30'
            AND o.OrderDate < '2016-05-16'
    );

Aufgabe #3:

Verwenden Sie die ExamDB-Beispieldatenbank!

Führen Sie zuvor das folgende Skript aus:

USE ExamDB;
GO
DROP SCHEMA IF EXISTS Prüfung;
GO
CREATE SCHEMA Prüfung;
GO
PROZEDUR LÖSCHEN, FALLS VORHANDEN Prüfung.Proc1
GO
PROZEDUR ERSTELLEN Prüfung.Proc1
AS
SELECT 1;
 

Schreiben Sie ein T-SQL-Skript mit folgendem Inhalt:

  1. Es wird ein SQL-Login namens "NewExamLogin" mit einem Passwort erstellt und die Datenbank ExamDB als Standarddatenbank festgelegt.
  2. In der ExamDB-Datenbank wird ein Benutzer "NewExamUser" für die vorherige Anmeldung angelegt.
  3. Erstellen Sie eine neue Datenbankrolle in der ExamDB-Datenbank mit dem Namen "Exam Admins".
  4. Erstellen Sie eine weitere neue Datenbankrolle in der ExamDB-Datenbank mit dem Namen "Exam Contributors".
  5. Fügt den Benutzer "NewExamUser" zur Datenbankrolle "Exam Admins" hinzu.
  6. Für das Prüfungsschema gibt EXECUTE der Datenbankrolle "Prüfungsadmins" das Recht.
  7. Im Prüfungsschema wird EXECUTE für die Rolle "Prüfungsmitarbeiter" ausdrücklich deaktiviert.
  8. Testet die Ausführung der gespeicherten Prozedur Exam.Proc1, indem er den Benutzer "NewExamUser" verkörpert.
  9. Stellt den Kontext der Ausführung für den vorherigen Benutzer wieder her.
  10. Fragt den aktuellen Benutzernamen und Anmeldenamen ab.

Jede Aufgabe ist eine T-SQL-Anweisung.

Beweise aus SQL-Skript, das den oben beschriebenen Vorgang ausführt
Name der Datei WINSQL-03.sql
-- Erstellt ein SQL-Login namens "NewExamLogin" mit einem Passwort und legt die Datenbank ExamDB als Standarddatenbank fest
CREATE LOGIN NewExamLogin WITH PASSWORD = 'SomeVeryStrongPassword123!', DEFAULT_DATABASE = [ExamDB];

-- Erzeugt einen Benutzer "NewExamUser" in der ExamDB-Datenbank für die vorherige Anmeldung
USE ExamDB;
CREATE USER NewExamUser FOR LOGIN NewExamLogin;

-- Erzeugt eine neue Datenbankrolle in der ExamDB-Datenbank mit dem Namen "Exam Admins
CREATE ROLE [Exam Admins];

-- Erzeugt eine weitere neue Datenbankrolle in der ExamDB-Datenbank mit dem Namen "Exam Contributors
CREATE ROLE [Exam Contributors];

-- Fügt den Benutzer "NewExamUser" zur Datenbankrolle "Exam Admins" hinzu
ALTER ROLE [Exam Admins] ADD MEMBER NewExamUser;

-- Erteilt der Datenbankrolle "Exam Admins" das Recht, das Prüfungsschema auszuführen
GRANT EXECUTE ON SCHEMA::Exam TO [Exam Admins];

-- Deaktivieren Sie explizit EXECUTE für die Rolle "Exam Contributors" auf dem Prüfungsschema
DENY EXECUTE ON SCHEMA::Exam TO [Exam Contributors];

-- Testet die Ausführung der gespeicherten Prozedur Exam.Proc1, indem er den Benutzer "NewExamUser" verkörpert
EXECUTE AS USER = 'NewExamUser';
EXEC Exam.Proc1;
REVERT;

-- Setzt den Ausführungskontext auf den vorherigen Benutzer zurück
-- (Die REVERT-Anweisung hat den Kontext bereits im vorherigen Schritt zurückgesetzt)

-- Fragt den aktuellen Benutzernamen und den Anmeldenamen ab
SELECT CURRENT_USER AS [Aktueller Benutzer], SUSER_NAME() AS [Anmeldename];

Aufgabe #4:

Schreiben Sie eine Diagnoseabfrage (unter Verwendung von Systemkatalogansichten), die die Parameter aller Dateien (Benutzer und System) in der Datenbank auf dem Server zurückgibt, z. B.: Dateityp (ROWS oder LOG), physischer Pfad zur Datei, Dateigröße und ob sie inkrementiert oder fixiert ist.

Die Abfrage ergibt folgendes:

  • database_id: die ID der Datenbank
  • name: Name der Datenbank
  • recovery_model_desc: Name des Datenbank-Wiederherstellungsmodells (z. B. SIMPLE, FULL)
  • file_id: die ID der Datei in der Datenbank, z. B.: 1, 2 usw.
  • type_desc: Art der Datei, z. B.: ROWS, LOG usw.
  • name: logischer Name der Datei
  • physical_name: physischer Pfad und Name der Datei
  • file_size_MB: Alias und berechneter Wert, die Dateigröße in Megabytes
  • is_percent_growth: der Zuwachs ist prozentual oder fest
Beweise aus SQL-Skript, das den oben beschriebenen Vorgang ausführt
Name der Datei WINSQL-04.sql
SELECT
    DB.database_id,
    DB.name AS datenbank_name,
    DB.wiederherstellungs_modell_desc,
    FM.file_id,
    FM.type_desc,
    FM.name AS datei_name,
    FM.physical_name,
    (FM.size * 8.0 / 1024) AS file_size_MB,
    FM.is_percent_growth
FROM
    sys.datenbanken AS DB
JOIN
    sys.master_files AS FM
ON
    DB.datenbank_id = FM.datenbank_id;

Aufgabe #5:

Für diese Übung benötigen Sie die Datenbank ExamDB. Die Datenbank muss verfügbar sein (ONLINE).

Schreiben Sie ein Wartungsskript mit T-SQL-Anweisungen, die die folgenden Aufgaben erfüllen:

  1. Auf Serverebene werden standardmäßig komprimierte Backups verwendet!
  2. Deaktivieren Sie die Parallelisierung für die ExamDB-Datenbank (nur für diese Datenbank!) (erstellen Sie keine parallelen Abfragepläne für diese Datenbank)
  3. Prüfen (abfragen) Sie die Einstellung auf Serverebene für komprimierte Backups!
  4. Es prüft (fragt) die ExamDB-Datenbank ab, um festzustellen, ob die Parallelisierung deaktiviert ist!
  5. Aktiviert den Abfragespeicher in der ExamDB-Datenbank, im Lese- und Schreibmodus, mit einer maximalen Größe von 500 MB, und damit der Abfragespeicher alle Abfragen auffängt!
Beweise aus SQL-Skript, das den oben beschriebenen Vorgang ausführt
Name der Datei WINSQL-05.sql
-- Auf Serverebene einstellen, dass standardmäßig komprimierte Backups erstellt werden
EXEC sp_configure 'backup compression default', 1;
REKONFIGURIEREN;
-- Deaktiviert die Parallelisierung für die ExamDB-Datenbank (nur!)
USE [ExamDB]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
-- Überprüfen (Abfragen) der Konfiguration auf Serverebene für komprimierte Backups
EXEC sp_configure 'backup compression default';
-- Prüfen (abfragen) der ExamDB-Datenbank, ob die Parallelisierung deaktiviert ist
SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';
-- Aktiviert den Abfragespeicher auf der ExamDB-Datenbank, im Lese- und Schreibmodus, mit einer maximalen Größe von 500MB, und so, dass der Abfragespeicher alle Abfragen abfängt
USE [master]
GO
ALTER DATABASE ExamDB
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 500,
    QUERY_CAPTURE_MODE = ALL
);

Aufgabe #6:

Eine Datenbank mit dem Namen RestoreDB muss aus der Sicherung zu einem bestimmten Wiederherstellungspunkt wiederhergestellt werden (Point-in-Time Recovery).

Schreiben Sie vor der Wiederherstellung einen Befehl, der anzeigt, welche Dateien (Daten und Protokoll) sich im Sicherungssatz RestoreDB_Full.bak befinden!

Schreiben Sie dann Wiederherstellungsanweisungen, die die Datenbank in einer Wiederherstellungskette wiederherstellen.

Die verfügbaren Backups unten (nur diese Speicherungen können verwendet werden):

  • RestoreDB_Full.bak: Vollständige Sicherung
  • RestoreDB_Log_2.trn: Log-Sicherung
  • RestoreDB_Log_3.trn: Log-Sicherung
  • RestoreDB_Log_5.trn: Log-Sicherung
  • RestoreDB_Log_6.trn: Log-Sicherung

Die Namen der Sicherungsdateien zeigen auch die Art der Sicherung und die Reihenfolge, in der sie erstellt wurden.

Die Datenbank sollte mit der Logsicherung "RestoreDB_Log_5.trn" als letzte Sicherung wiederhergestellt werden!

Sobald Sie die Datenbank wiederhergestellt haben, führen Sie die folgende Abfrage aus und speichern das Ergebnis im .csv-Format.

USE RestoreDB;
GO
SELECT 
    GETDATE() AS [examstimestamp],
    @@SERVERNAME AS [myservername],
    * 
VON dbo.RestoreTable;
 
Beweise aus SQL-Skript, das den oben beschriebenen Vorgang ausführt
Name der Datei WINSQL-06.sql
Beweise aus Das gespeicherte Ergebnis im .csv-Format
Name der Datei WINSQL-06.csv
USE master;
GO
-- Wiederherstellungsliste
RESTORE FILELISTONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak';
GO
/*
RESTORE HEADERONLY FROM DISK = N'C:\Programmdateien\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak';
GO
*/
-- vollständige sicherung wiederherstellenv
RESTORE DATABASE [RestoreDB]
FROM DISK = N'C:\Programme\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak'
WITH MOVE N'RestoreDB' TO N'C:\exam\RestoreDB.mdf',
MOVE N'RestoreDB_log' TO N'C:\exam\RestoreDB_log.ldf', NORECOVERY; -- Ich verschiebe es direkt, damit ich den Ordner, den ich auf c erstellt habe, leichter überprüfen kann
/*-- diff backup
RESTORE DATABASE [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Diff_4.bak' WITH NORECOVERY;*/
-- Log-Sicherung
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_2.trn' WITH NORECOVERY;
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_3.trn' WITH NORECOVERY;
RESTORE LOG [RestoreDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Log_5.trn' WITH NORECOVERY;
GO
USE RestoreDB;
GO
SELECT
    GETDATE() AS [examstimestamp],
    @@SERVERNAME AS [myservername],
    *
FROM dbo.RestoreTable;

Aufgabe #7:

Führen Sie das folgende Skript aus, bevor Sie die Aufgabe ausführen:

USE [master];
GO
ALTER DATENBANK [ExamDB] SETZEN READ_COMMITTED_SNAPSHOT AUS
WITH ROLLBACK UNVERZÜGLICH;
 

Verwenden Sie das Deadlock-Skript: deadlock_script_exam.sql zu Schritt 3! In Schritt 3 wartet die SELECT-Abfrage (noch kein Deadlock!).

  • Schreiben Sie eine Abfrage in ein neues Abfragefenster, die anzeigt, welche Sperren die anstehende SELECT-Abfrage (oder die Sitzung, in der sie läuft) derzeit hat und welche Sperren sie benötigt! Geben Sie nur die Sperrdaten für die Sitzung zurück, die gerade wartet!

  • Welche Art von Sperre ist nach Ausführung der Abfrage für die SELECT-Abfrage, auf die Sie warten, erforderlich? Schreiben Sie Ihre Antwort in einen Kommentar neben der Abfrage.

  • Ändern Sie die Serverkonfiguration wie folgt: Es sollten erweiterte Konfigurationsoptionen verfügbar sein, und diese Änderung sollte sofort nach Ausführung des Skripts wirksam werden.

Beweise aus SQL-Skript, das den oben beschriebenen Vorgang ausführt und die Antworten in einem Kommentar enthält
Name der Datei WINSQL-07.sql
/*
SELECT tl.request_session_id, tl.resource_type, tl.request_mode, tl.request_status, tl.resource_database_id, tl.resource_associated_entity_id, tl.resource_description
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_requests er ON tl.request_session_id = er.session_id
WHERE er.wait_type LIKE 'LCK%'
  AND tl.request_status = 'WAIT';
GO
*/
USE DBAHEALTH;
GO
dbo.sp_BlitzLock -- S - Gemeinsame Sperre

EXEC sp_configure 'Erweiterte Optionen anzeigen', 1;
GO
NEU KONFIGURIEREN MIT OVERRIDE;
GO

ALTER DATABASE [ExamDB] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

Aufgabe #8

Verwenden Sie die WideWorldImporters-Beispieldatenbank!

Schreiben Sie eine gespeicherte Prozedur im Schema Sales mit dem Namen GetPickedByDate, die eine Statistik der Auftragspositionen (Sales.OrderLines) zurückgibt, die davon abhängt, wann bestimmte Aufträge in einem bestimmten Zeitintervall (OrderDate) als Kunden eingetroffen sind / enden können, mit den unten erläuterten Details!

Die gespeicherte Prozedur benötigt zwei Parameter:

  • @startDate datetime
    • Standardwert sollte Startdatum 01.01.2013 sein
  • @endDate datetime
    • Standardwert sollte Fertigstellungsdatum 01.06.2016 sein

Zum Beispiel:

  • Wenn die gespeicherte Prozedur mit den Standard-Parameterwerten ausgeführt wird, gibt sie die Statistik der Einkäufe zurück, die zwischen dem 01.01.2013 und dem 01.06.2016 (OrderDate) getätigt wurden, und zwar nach Kundenname (CustomerName) und nach dem Zeitpunkt, an dem die Auswahl abgeschlossen wurde (PickingCompletedWhen).

Geben Sie die gespeicherte Prozedur mit den folgenden Spalten zurück:

  • [Kundenname]: Spalte CustomerName aus der Tabelle Customers
  • [Pick Date]: das Datum des Abschlusses der Artikelauswahl (Spalte PickingCompletedWhen) aus der Tabelle OrderLines:
    • wenn das Datum nicht bekannt ist, wird das aktuelle Datum plus 3 Tage gedruckt
  • [Gesamtpreis]: Summe der Einzelposten (Menge * Stückpreis) aus der Tabelle OrderLines (2 Dezimalstellen)
  • [Anzahl der Käufe]: Anzahl der gezählten Positionen (OrderLine)
  • [Refreshed Date]: wenn das Datum der Kommissionierung des Sortierauftrags (PickingCompletedWhen) nicht bekannt ist, drucke 'Expected Pick Date', wenn bekannt, drucke 'Already picked'

Filterung einbeziehen, um nur diejenigen zurückzugeben, bei denen die BuyingGroup des Kunden nicht bekannt ist.

Filtern Sie weiter, um nur diejenigen zurückzugeben, deren Wert in der Spalte [Ovarall-Preis] über 20000 liegt.

Verwenden Sie die nachstehenden Tabellen:

  • Sales.OrderLines
  • Verkäufe.Aufträge
  • Vertrieb.Kunden

Schreiben Sie die gespeicherte Prozedur so, dass die Meldung "n rows affected" nach dem Lauf nicht auf der Registerkarte Messages erscheint.

Nennen Sie ein Beispiel für die Anwendung!

Beweise aus SQL-Skript, das den oben beschriebenen Vorgang ausführt
Name der Datei WINSQL-08.sql
USE WideWorldImporters;
GO
/*
SELECT * FROM Verkauf.Kunden
SELECT * FROM Verkäufe.Aufträge
SELECT * FROM Verkäufe.Auftragszeilen
*/
GO
CREATE OR ALTER PROCEDURE Sales.GetPickedByDate
    @startDate datetime = '2013-01-01',
    @endDate datetime = '2016-06-01'
AS
BEGIN
    NOCOUNT EINSCHALTEN;
    SELECT
        c.CustomerName AS [Kundenname],
        COALESCE(ol.PickingCompletedWhen, DATEADD(DAY, 3, GETDATE())) AS [Kommissionierdatum],
        SUM(ol.Quantity * ol.UnitPrice) AS [Gesamtpreis],
        COUNT(ol.OrderLineID) AS [Anzahl der Käufe],
        CASE
            WHEN ol.PickingCompletedWhen IS NULL THEN 'Erwartetes Kommissionierdatum'
            ELSE 'Bereits kommissioniert'
        END AS [Aktualisiertes Datum]
    FROM Sales.OrderLines ol
    JOIN Vertrieb.Aufträge o ON ol.AuftragID = o.AuftragID
    JOIN Vertrieb.Kunden c ON o.KundenID = c.KundenID
    WHERE c.BuyingGroupID IS NULL
        AND o.OrderDate >= @startDate
        AND o.OrderDate  20000
    ORDER BY c.CustomerName, ol.PickingCompletedWhen;
END;
GO
EXEC Sales.GetPickedByDate;
EXEC Sales.GetPickedByDate @startDate = '2014-01-01', @endDate = '2015-12-31';

DIAGNOSEABFRAGEN

Abfrage von Tabellen aus sys.tables

SELECT * FROM sys.tables;

Tabellen und ihre Schemata - Verbindung der Katalogansichten sys.tables und sys.schemas

SELECT t.name AS [tabellenname], t.type_desc, s.name AS [schema_name]
FROM sys.tables t INNER JOIN sys.schemas s ON
t.schema_id = s.schema_id;

in welcher Sitzung wir uns befinden

SELECT @@SPID;

Spaltendatentypinformationen für alle Benutzertabellen abrufen

SELECT c.object_id, OBJECT_NAME(c.object_id) AS [object_name],
s.name AS [schema], o.name AS [table_name], c.name AS [col_name],
type.name AS [col_type],
CASE c.is_nullable WHEN 1 THEN 'yes' ELSE 'no' END AS [nullable]
FROM sys.columns c INNER JOIN sys.objects o ON
c.object_id = o.object_id
INNER JOIN sys.tables t ON
t.object_id = o.object_id
INNER JOIN sys.types typ ON
c.user_type_id = typ.user_type_id
INNER JOIN sys.schemas s ON
s.schema_id = t.schema_id;
-- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15
	--wiederherstellungsmodelle für dbs
	SELECT name, recovery_model_desc FROM sys.databases;

	-- alle Dateien auf dem Server
	SELECT db.name AS DBName, type_desc AS FileType, Physical_Name AS Location, size * 8 as FileSizeKB
	FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id;

Zählindex pro Tabelle

SELECT S.name,COUNT(SYS.INDEXES.name) FROM SYS.INDEXES
INNER JOIN SYS.OBJECTS S
ON S.OBJEKT_ID=SYS.INDIZES.OBJEKT_ID
WHERE SYS.INDEXES.OBJECT_ID IN (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE name IN
    (SELECT name FROM SYS.TABLES WHERE NAME  'SYSDIAGRAMS'))
GROUP BY S.name;

Listenindex pro Tabelle

SELECT S.name,SYS.INDEXES.name FROM SYS.INDEXES
INNER JOIN SYS.OBJECTS S
ON S.OBJEKT_ID=SYS.INDIZES.OBJEKT_ID
WHERE SYS.INDEXES.OBJECT_ID IN (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE name IN
    (SELECT NAME FROM SYS.TABLES WHERE name  'SYSDIAGRAMS'));

INDEX-Fragmentierung

SELECT i. name, x.avg_fragmentation_in_percent, x.fragment_count
FROM sys.indexes i JOIN sys.dm_db_index_physical_stats(DB_ID('WideWor1dImporters' ) ,OBJECT_ID('INDEXTEST'), NULL,NULL,'DETAILED') x ON x.object_id = i.object_id
WHERE x.fragment_count > 1

--------------------------------------------------

SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Tabelle',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_Prozent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND
    indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.datenbank_id = DB_ID()

DEADLOCK?

SELECT SESSION_ID, BLOCKING_SESSION_ID
FROM SYS.DM_EXEC_REQUESTS
WO BLOCKING_SESSION_ID != 0

Anzahl der Beschränkungen (Prüfung+Standard) in der Datenbank

SELECT (SELECT COUNT(*) FROM sys.check_constraints)+(SELECT COUNT(*) FROM sys.default_constraints);

Deterministisch?

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.testlines'),'total_price','isDeterministic') AS Determ;

Select * FROM sys.computed_columns;

Suche nach Spaltennamen

SELECT *
FROM INFORMATION_SCHEMA.SPALTEN
WHERE COLUMN_NAME LIKE '%region%'

Suche nach Tabellennamen

SELECT c.object_id, OBJECT_NAME(c.object_id) AS [object_name], s.name AS [schema], o.name AS [table_name], c.name AS [col_name], typ.name AS [col_type]
, CASE c.is_nullable WHEN 1 THEN 'yes' ELSE 'no' END AS [nullable] FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.tables t ON t.object_id = o.object_id
INNER JOIN sys.types typ ON c.user_type_id = typ.user_type_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name LIKE '%Postal%'

Suche nach einer Zeichenkette in der Datenbank

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'Begrenzter Bestand

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @Tabellenname nvarchar(256), @Spaltenname nvarchar(128), @SuchStr2 nvarchar(110)
SET @Tabellenname = ''
SET @SuchStr2 = QUOTENAME('%' + @SuchStr + '%','''')

WHILE @Tabellenname IST NICHT NULL
BEGIN
    SET @SpaltenName = ''
    SET @Tabellenname =
    (
        SELECT MIN(QUOTENAME(TABELLEN_SCHEMA) + '.' + QUOTENAME(TABELLENNAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASISTABELLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            UND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@Tabellenname IST NICHT NULL) AND (@Spaltenname IST NICHT NULL)
    BEGIN
        SET @SpaltenName =
        (
            SELECT MIN(QUOTENNAME(SPALTEN_NAME))
            FROM INFORMATION_SCHEMA.SPALTEN
            WHERE TABLE_SCHEMA = PARSENAME(@Tabellenname, 2)
                AND TABLE_NAME = PARSENAME(@Tabellenname, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @Spaltenname IST NICHT NULL
        BEGIN
            INSERT INTO @Ergebnisse
            EXEC
            (
                'SELECT ''' + @Tabellenname + '.' + @Spaltenname + '''', LEFT(' +
@SpaltenName
+ ', 3630) FROM '
+
@Tabellenname
+ ' (NOLOCK) ' +
                ' WHERE '
+
@SpaltenName
+ ' LIKE '
+
@SuchStr2
                
            )
        END
    END
END

SELECT SpaltenName, SpaltenWert FROM @Ergebnisse

Sys.messages & Ändern der Sprache in der Sitzung

8134 Systemfehlermeldungen > diagn. Abfrage
SELECT * FROM sys.messages
WHERE message_id=8134;
/* Sprache in der Sitzung ändern */
SET LANGUAGE ungarisch;
PRINT 10/0;
SET LANGUAGE englisch;
PRINT 10/0;

VIEW-Diagnoseabfrage

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID('VarosokOrszagok2');
Datenbankprotokolle pro Datenbank-ID
SELECT b.name,a.* FROM sys.databases b
CROSS APPLY sys.dm_db_log_info(b.datenbank_id) a
WHERE b.name = 'WideWorldImporters';

alle TRIGGER auflisten

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID('VarosokOrszagok2');

gesperrte Objekte erkennen

SELECT ressource_type,request_status, request_mode, o.object_id, o.name, o.type_desc
FROM sys.dm_tran_locks l, sys.objects o
WHERE l.resource_associated_entity_id = o.object_id
    und resource_database_id = DB_ID();

Isolationsniveau erkennen

SELECT CASE transaction_isolation_level
    WHEN 0 THEN 'Nicht spezifiziert'
    WHEN 1 THEN 'ReadUncommitted'
    WHEN 2 THEN 'ReadCommitted'
    WHEN 3 THEN 'Wiederholbar
    WHEN 4 THEN 'Serialisierbar
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
-- Verwenden Sie Ihre Sitzungs-ID anstelle von 75
wo session_id = 75;
------------------------------------------------------------
SELECT CASE transaction_isolation_level
    WHEN 0 THEN 'Nicht spezifiziert'
    WHEN 1 THEN 'ReadUncommitted'
    WHEN 2 THEN 'ReadCommitted'
    WHEN 3 THEN 'Wiederholbar
    WHEN 4 THEN 'Serialisierbar
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID

SQL-Quiz

https://github.com/green-fox-academy/safely-syllabus/tree/master/materialreview/mssql

##### SQL-Fonds ######

SELECT IN SELECT, WHERE IN, WHERE NOT IN

SELECT TOP (1000) [SAMLAHEAD_ID]
      ,[VEVO_ID]
      ,[CELTE]
      ,[TELJESITES]
      ,[FIZHATARIDO]
      ,[NAVATKULDVE]
      ,[STATUS]
  FROM [CEGESADATOK].[samlazas].[samlahead].
  WHERE VEVO_ID Not IN (select vevo_id
						from samlazas.vevo)

UPDATE

VERWENDUNG [CEGESADATA]
GO

UPDATE [samlazas].[vevo]
   SET [STATUS] = 1
      ,[LASTUPDATE] = (GETDATE())
 WHERE STATUS=0
GO

INSERT INTO

VERWENDUNG [CEGESADATA]
GO

INSERT INTO [samlazas].[vevo]
           ([VEVONEV], [ADOSZAM], [CIM])
     VALUES
           ('öTÖDIK Nyrt','3499789012-4-04','4000 Kisvarda, Negyedik utca. 5'),
		   ('Hatodik Nyrt','3456789099-4-04','4000 Kisvarda, Negyedik utca. 6')
GO
/* MASIK */
USE [CEGESDATA]
GO

INSERT INTO [samlazas].[vevo]
           ([VEVONEV]
           ,[ADOSZAM]
           ,[CIM]
           ,[PHONE]
           ,[EMAIL]
           ,[STATUS])
     VALUES
           ('Hetedik Bt,
           '11223344-2-05',
           'Keine',
           '',
            null,
           0)
GO

DELETE

CEGESDATA BENUTZEN
Weiter zu

SELECT *
FROM szamlazas.szamlatetel
WHERE SZAMLAFEJ_ID Not IN (SELECT SZAMLAFEJ_ID
							FROM samlazas.samlahead)


SELECT HEAD_ID
FROM samlazas.samlahead

DELETE FROM samlazas.samlatetel
WHERE SZAMLAFEJ_ID Nicht IN (SELECT SZAMLAFEJ_ID
							FROM samlazas.samlahead)
GO

DISTINCT

SELECT DISTINCT v.*
FROM szamlazas.vevo v INNER JOIN szamlazas.szamlahead szf ON v.VEVO_ID=szf.VEVO_ID
WHERE v.STATUS=1 und szf.STATUS='normal'

KONVERTIEREN, AVG

SELECT *
  FROM [CEGESADATOK].[szamlazas].[ARU]

SELECT AVG(ASEGAR)
  FROM [CEGESADATOK].[samlazas].[ARU]


SELECT *
  FROM [CEGESADATOK].[samlazas].[ARU]
  wo EGYSEGAR < (SELECT AVG(EGYSEGAR) as EGYSEGAR
					FROM [CEGESADATOK].[samlazas].[ARU])

SELECT ARU_ID, ARUNEV, MENNYEGYS,CONVERT(int,EGYSEGAR) as EGYSEGAR, CONVERT(int,AFAKULCS) as AFAKULCS, STATUS
  FROM [CEGESADATOK].[szamlazas].[ARU]
  wo EGYSEGAR < (SELECT AVG(EGYSEGAR) as EGYSEGAR
					FROM [CEGESADATOK].[samlazas].[ARU])

FROMART, KONVERTIEREN

select getdate()
select FORMAT(getdate(), 'jjjj')
wähle FORMAT(getdate(), 'jjjj/MM')
wähle FORMAT(getdate(), 'jjjj/MM/dd')
select FORMAT(getdate()+1, 'jjjj/MM/tt')
wähle FORMAT(getdate()-2, 'jjjj/MM/tt')
select FORMAT(getdate(), 'jjjj/MM/tt HH:mm:ss.ms')

select '2022/3/15'
select CONVERT(datetime, '2022/3/25')

HABEN

/*Aufgabe:
Finde die Namen, Adressen, Steuernummern und die Anzahl der Käufe von Kunden, die mindestens zwei Käufe getätigt haben
FALSCH:
select v.VEVONEV
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
where (select COUNT(szf.VEVO_ID)
							aus szamlazas.szamlafej
							group by szf.VEVO_ID) > 1
GROUP by v.VEVONEV
*/

select COUNT(szf.VEVO_ID), szf.VEVO_ID
							aus szamlazas.szamlafej szf
							gruppieren nach szf.VEVO_ID

select v.VEVONEV,v.CIM, v.ADOSZAM,
	COUNT(szf.VEVO_ID) as VASARLASSZAM
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
GROUP by v.VEVONEV,v.CIM, v.ADOSZAM
HAVING COUNT(szf.VEVO_ID) >=2
ORDER BY VASARLASSZAM

/*Aufgabe:
Ermitteln des Namens, der Adresse, der Steuernummer und der Anzahl der Einkäufe, Bruttosumme*/
select v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM,
	CONVERT(int,SUM(t.[BRUTTO ERTEK])) as SUM
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join samlazas.tetelek t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
GROUP by v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM
ORDER BY SZUMMABRUTTO desc


select v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM,
	CONVERT(int,SUM(t.MENNYISEG*a.EGYSEGAR*(a.AFAKULCS/100+1))) as SUMABRUTTO
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join samlazas.szamlatetel t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
inner join samlazas.ARU a on t.ARU_ID=a.ARU_ID
WHERE v.CIM like '%Budapest%'
GROUP by v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM
HAVING CONVERT(int,SUM(t.MENNYISEG*a.EGYSEGAR*(a.AFAKULCS/100+1))) > 121000
ORDER BY SUMABRUTTO desc

KONSISTENT FIX

CEGESADATOK verwenden
gehen Sie zu

szamlazas.szamlafej aktualisieren
	set STATUS='Fehler'
where SZAMLAFEJ_ID not in (select SZAMLAFEJ_ID from szamlazas.szamlatetel)

weiter zu

SOK JOIN, SOK WHERE

USE CEGESDATA
GO

/* Wer hat A7 gekauft?*/
VEVONEV auswählen
Von samlazas.vevo
WHERE VEVO_ID IN (select VEVO_ID
					FROM samlazas.samlahead
					WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
											From samlazas.szamlatetel
											where ARU_ID IN (select ARU_ID
																von samlazas.ARU
																where ARUNEV='A7')))


select v.*
FROM szamlazas.vevo v
INNER JOIN samlazas.samlahead f ON v.VEVO_ID=f.VEVO_ID
	INNER JOIN samlazas.szamlatetel t ON f.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
		INNER JOIN samlazas.ARU a ON t.ARU_ID=a.ARU_ID
			WHERE a.ARUNEV='A7'
ORDER BY VEVO_ID

select *
From szamlazas.vevo v INNER JOIN szamlazas.szamlazfej sz ON v.VEVO_ID=sz.VEVO_ID
INNER JOIN samlazas.tetelek t ON sz.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
wo t.ARUNEV='A7'




select *
FROM samlazas.samlahead
WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
						From samlazas.szamlatetel
						where ARU_ID IN (select ARU_ID
											von samlazas.ARU
											where ARUNEV='A7'))

select *
Von szamlazas.szamlatetel
wo ARU_ID IN (select ARU_ID
					von szamlazas.ARU
					wo ARUNEV='A7')

/*
wähle *
From samlazas.tetelek t INNER JOIN samlazas.samlafej sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID
wo ARUNEV='A7'

select *
From samlazas.vevo v INNER JOIN (samlazas.tetelek t INNER JOIN samlazas.samlahead sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID) ON
where ARUNEV='A7'
*/

QUERY VIEW TO

USE CEGESDATA
GO

SELECT t.*, a.ARUNEV, a.MENNYEGYS, a.EGYSEGAR, a.EGYSEGAR*t.MENNYISEG as 'Netto ertek', a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100) as 'AFA ERTEK'
, (a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100))+(a.EGYSEGAR*t.MENNYISEG) als 'GROSS ERTEK'
FROM szamlazas.szamlatetel t INNER JOIN szamlazas.ARU a ON t.ARU_ID=a.ARU_ID
/*WHERE (t.ARU_ID=a.ARU_ID)*/

SELECT *
FROM samlazas.tetelek

VIEW ERSTELLEN

create view samlazas.bad_samla_head
als
wähle *
from samlazas.samlahead
where VEVO_ID not in (select vevo_id from szamlazas.vevo)

Schema erstellen

VERWENDUNG [CEGESADATA]
GO

/****** Objekt: schema [samlazas] Skript Datum: 2022. 10/25 0:11:57 ******/
CREATE SCHEMA [samlazas]
GO

Benutzer anlegen, Login, Berechtigungen, Rollen

USE [master]
GO
CREATE LOGIN [test1] WITH PASSWORD=N'Password123', DEFAULT_DATABASE=[CEGESADATOK], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [CEGESADATOK]
GO
CREATE USER [test1] FOR LOGIN [test1]
GO
USE [CEGESDATA]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [test1]
GO
USE [CEGESDATA]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test1]
GO
USE [CEGESADATA]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [test1]
GO
USE [CEGESDATA]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [test1]
GO
USE [CEGESDATA]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [test1]
GO

Tabelle löschen, Tabelle abschneiden

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT TOP (1000) [SAMLAHEAD_ID]
      ,[VEVO_ID]
      ,[KELTE]
      ,[TELJESITES]
      ,[FIZHATARIDO]
      ,[NAVATKULDVE]
      ,[STATUS]
  VON [CEGESADATOK].[samlazas].[samlahead].

  Tabelle szamlazas.szamlafej abschneiden

  bulk insert samlazas.samlahead
  FROM N'E:\szamlafej.table.csv'
  with (feldterminator=';',rowterminator='\n')

######3 Prüfung SQL #######

Tabellen erstellen (Tabelle erstellen)

CREATE TABLE Lehrlinge(
	id int,
	name varchar(50),
	Alter int,
	land varchar(50),
	kohorte_id int)

CREATE TABLE kohorten(
	id int,
	name varchar(50),
	start_at datum,
	typ varchar(50))

Tabellen importieren (Bulk Insert)

/****** Script für den Befehl SelectTopNRows aus SSMS ******/
  Bulk-Insert Lehrlinge
  FROM N'E:\apprentices.table.csv'
  WITH (fieldterminator=';',rowterminator='\n'/*, FIRSTROW=2 ha van fejlec*/)

  bulk insert kohorten
  FROM N'E:\cohorts.table.csv'
  WITH (fieldterminator=';',rowterminator='\n')

Auflistung des Namens (name) und des Anfangsdatums (started_at) aller Kohorten (Tabelle cohorts)
Dateiname: kohorten.sql

SELECT name, start_at
 FROM kohorten

Geben Sie den Namen und das Herkunftsland aller Schüler an (Tabelle für Auszubildende)
Dateiname: lehrlinge.sql

SELECT Name, Land
FROM Auszubildende

Geben Sie den Namen und das Alter der Schüler (Tabelle der Auszubildenden) an, die jünger als 21 Jahre sind.
Dateiname: lehrlinge-juenger-21.sql

SELECT Name, Alter
FROM Auszubildende
WHERE Alter < 21

Geben Sie die Kennung und den Namen der Auszubildenden (Tabelle) an, die nicht aus Ungarn (Land) stammen.
Dateiname: lehrlinge-nicht-aus-hu.sql

SELECT id, name
FROM Auszubildende
WHERE Land nicht wie 'Ungarn'

Geben Sie an, wie viele Schüler (Lehrlingstabellen) in der Datenbank 20 oder mehr und 30 oder weniger Jahre alt sind.
Dateiname: lehrlinge-zwischen-20-und-30.sql

SELECT count(id)
FROM Auszubildende
WHERE Alter >= 20 und Alter <= 30

Geben Sie an, wie viele Kohortentabellen in der Datenbank vom Typ "Teilzeit" sind (Typ)
Dateiname: kohorten-teilzeit.sql

SELECT count(id)
FROM kohorten
WHERE Typ wie 'Teilzeit'

Geben Sie den Namen des Auszubildenden (als "apprentice_name") und den Namen der Kohorte (als "cohort_name") für Studierende an, die ihr Studium in den Kohorten 2018 begonnen haben.
Dateiname: lehrlinge-in-2018.sql

SELECT a.name as auszubildende_name, c.name as kohorte_name
FROM auszubildende a INNER JOIN kohorten c ON a.kohorten_id=c.id
WHERE FORMAT(beginn_am, 'jjjj')=2018

Geben Sie die Anzahl der Klassen in der Datenbank an, für die es keine Schüler in der Datenbank gibt.
Dateiname: kohorten-ohne-auszubildende.sql

SELECT count(id)
FROM kohorten
WHERE id NOT IN (SELECT kohorten_id FROM auszubildende)

Führen Sie die Namen der Teilnehmer und das Datum des Beginns der Ausbildung in alphabetischer Reihenfolge auf
Dateiname: auszubildende-start-datum-geordnet.sql

SELECT a.name, c.started_at
FROM lehrlinge a INNER JOIN kohorten c ON a.kohorten_id=c.id
ORDER BY a.name

Auflistung der Namen der Klassen und der Anzahl der Auszubildenden (als numberOfApprentices) in der jeweiligen Klasse, in absteigender Reihenfolge der Anzahl der Auszubildenden. Einschließlich der Kohorte, die derzeit 0 Auszubildende hat.
Dateiname: kohorte-grössen.sql

SELECT c.name, count(a.id) as numberOfApprentices
FROM kohorten c LEFT JOIN auszubildende a ON a.kohorten_id=c.id
GROUP BY c.name
ORDER BY count(a.id) DESC

###### SQL-Administrator-Projektaufgaben ######

1) Dem Server wird eine Festplatte hinzugefügt, die noch nicht in Gebrauch ist
(20 GB) Fügen Sie S: als Laufwerk und Volume-Namen hinzu:
SQLDATA sollte sein.

2) Erstellen Sie auf dem Laufwerk S: ein Verzeichnis SQLDATA, und darin
zwei weitere Unterverzeichnisse namens DB und LOG.

3) Melden Sie sich am SQL-Server (GREENFOX-Instanz) an (Dieser Schritt
Sie müssen es nicht speichern! :-)

4) Stellen Sie die Datei C:\install\MSSQL\AdventureWorks2016.bak wieder her.
Datenbank auf die GREENFOX-Instanz des Servers, damit die Datenbank
Dateien befinden sich in S:\SQLDATA\DB, während die Protokolldateien in S:\SQLDATA\
LOG-Bibliothek.

 
USE [master]
RESTORE DATABASE [AdventureWorks2016] FROM DISK = N'C:\install\MSSQL\AdventureWorks2016.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'S:\SQLDATADB\AdventureWorks2016_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'S:\SQLDATA\LOG\AdventureWorks2016_Log.ldf', NOUNLOAD, STATS = 5

GO

5) Erstellen Sie ein authentifiziertes interface_svc MS SQL-Konto,
mit minimalen (öffentlichen) Rechten! Ihr Passwort sollte INTERface lauten.

USE [master]
GO
CREATE LOGIN [interface_svc] WITH PASSWORD=N'INTERFACE', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

6) Erstellen Sie eine Datenbank namens INTERFACE mit den folgenden Einstellungen:
- Name der Datenbank: INTERFACE
- Eigentümer: interface_svc
- Einziehungsmodell: FULL
- Ursprüngliche Datenbankdatei:
(a)Größe: 32 MByte
(b)Standort: S:\SQLDATA\DB
- Startup-Protokolldatei:
(a) Größe: 16 MByte
(b)Standort: S:\SQLDATA\LOG

DATENBANK ERSTELLEN [SCHNITTSTELLE]
 CONTAINMENT = KEINE
 ON PRIMARY
( NAME = N'INTERFACE', FILENAME = N'S:\SQLDATA\DB\INTERFACE.mdf' , SIZE = 32768KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'INTERFACE_log' , FILENAME = N'S:\SQLDATA\LOG\INTERFACE_log.ldf' , SIZE = 16384KB , FILEGROWTH = 65536KB )
GO

USE [INTERFACE]
GO
ALTER AUTHORIZATION ON DATABASE::[INTERFACE] TO [interface_svc]
/*
exec sp_changedbowner 'interface_svc'
GO
*/

ALTER DATABASE [INTERFACE] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [INTERFACE] SET ARITHABORT OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [INTERFACE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [INTERFACE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [INTERFACE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [INTERFACE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [INTERFACE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [INTERFACE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [INTERFACE] SET DISABLE_BROKER
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [INTERFACE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [INTERFACE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [INTERFACE] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [INTERFACE] SET READ_WRITE
GO
ALTER DATABASE [INTERFACE] SET RECOVERY FULL
GO
ALTER DATABASE [INTERFACE] SET MULTI_USER
GO
ALTER DATABASE [INTERFACE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [INTERFACE] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [INTERFACE] SET DELAYED_DURABILITY = DISABLED
GO
USE [INTERFACE]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Aus;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Aus;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
USE [INTERFACE]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [INTERFACE] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

7) Erteilen Sie dem interface_svc-Konto nur Abfrageberechtigung mit
AdventureWorks2016-Datenbank im Schema HumanResources
vAnsichtsfenster des Mitarbeiters (VIEW)!

verwenden [AdventureWorks2016]
GO
CREATE USER [interface_svc] FOR LOGIN [interface_svc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [interface_svc]
GO

8) Erstellen Sie ein AWDB-Schema in der Datenbank INTERFACE. Das Skript
"Erstellen bis" Format

VERWENDEN [SCHNITTSTELLE]
GO

/****** Objekt: schema [AWDB] Script Datum: 12/9/2022 8:21:47 AM ******/
SCHEMA ERSTELLEN [AWDB]
GO

9) Erstellen Sie im AWDB-Schema ein Synonym namens AW_Employee,
die sich im HumanResources-Schema der AdventureWorks2016-Datenbank befindet
zeigt auf das Ansichtsfenster vEmployee!

VERWENDEN [SCHNITTSTELLE]
GO

/****** Objekt: Synonym [AWDB].[AW_Employee] Script Date: 12/6/2022 1:59:44 PM ******/
CREATE SYNONYM [AWDB].[AW_Employee] FOR [AdventureWorks2016].[HumanResources].[vEmployee]
GO

10) Erstellen Sie eine Abfrage, die mit Hilfe der AWDB an die INTERFACE-Datenbank gesendet werden soll
alle Zeilen und Spalten des Synonyms AW_Employee im Schema
.................

VERWENDEN [SCHNITTSTELLE]
GO
SELECT *
FROM AWDB.AW_Employee

11) Erstellen Sie ein FULL-Backup der INTERFACE-Datenbank mit folgendem Inhalt
nach:
a) Name der Sicherungsdatei: S:\SQLdata\INTERFACE.bak
b) Speichern überschreibt den gleichnamigen Mediensatz!

BACKUP DATABASE [INTERFACE] TO DISK = N'S:\SQLdata\INTERFACE.bak' WITH NOFORMAT, INIT, NAME = N'S:\SQLdata\INTERFACE.bak', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

1) Ermitteln Sie die SQL-Server-Version und das SQL-Server-Release!

WÄHLEN SIE @@VERSION

2) Erstellen Sie eine einfache "Hello World!" Abfrage!

SELECT 'Hallo Welt!'

3) Erstellen Sie eine SQL-Abfrage, die die Quadratwurzel von 30 berechnet!

SELECT SQRT(30)

4) Erstellen Sie eine SQL-Abfrage, die das heutige Datum zurückgibt!

SELECT CAST(GETDATE() as date)

5) Erstellen Sie eine SQL-Abfrage, die berechnet, wie viele Tage vergangen sind.
Seit dem 15. März 1989!

SELECT DATEDIFF(day,'1989-03-15', GETDATE());

6) Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen
Die ersten 20 Elemente der Tabelle Person.Address in der AdventureWorks-Datenbank
(alle Felder)!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT TOP (20) *
  FROM [AdventureWorks2016].[Person].[Adresse]

7) Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen
Die ersten 20 Elemente der Tabelle Person.Person in der AdventureWorks-Datenbank
(alle Felder), aber das Feld PersonType ist 'EM'!

/****** Script für den Befehl SelectTopNRows aus SSMS ******/
SELECT TOP (20) *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

8) Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen
die ersten 20 Elemente der Tabelle Person.Person in der AdventureWorks-Datenbank, aber
so dass, wenn das Feld PersonType 'EM' ist und nur PersonType,
Die Felder FirstName, LastName sollten im Abfrageergebnis enthalten sein!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT TOP (20) PersonType, Vorname, Nachname
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

9) Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen
die ersten 20 Elemente der Tabelle Person.Person in der AdventureWorks-Datenbank, aber
so dass, wenn das Feld PersonType 'EM' ist und nur PersonType,
Die Felder Vorname, Nachname sollten im Abfrageergebnis enthalten sein, aber die
Vorname und Nachname sollten in einer Spalte mit einem einzigen Leerzeichen erscheinen
durch ein Zeichen getrennt!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT TOP (20) PersonType, Vorname + ' ' + Nachname
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM'

10)Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen
Datensätze in der Tabelle Person.Person der AdventureWorks-Datenbank
(alle Felder), wobei das Feld PersonType 'EM' und das Feld FirstName
sollten nur diejenigen angezeigt werden, die den Namen "John" tragen!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'

11)Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen
Datensätze in der Tabelle Person.Person der AdventureWorks-Datenbank
(alle Felder), wobei das Feld PersonType 'EM' und das Feld FirstName
sollten nur diejenigen mit dem Namen "John" angezeigt werden, bei denen die
Der Wert des Feldes LastName endet mit dem Zeichen 'e'!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'

12)Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen
Datensätze in der Tabelle Person.Person der AdventureWorks-Datenbank
(alle Felder), wobei das Feld PersonType 'EM' und das Feld FirstName
sollten nur diejenigen mit dem Namen "John" angezeigt werden, bei denen die
Die Werte im Feld Nachname enthalten entweder 'Chen' oder 'Kane'!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND (LastName='Chen' OR LastName='Kane')

13) Zähle, wie viele verschiedene Vornamen (FirstName) es in der
in der Tabelle Person.Person einer zuvor geladenen AdventureWorks-Datenbank!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT COUNT(DISTINCT Vorname)
  FROM [AdventureWorks2016].[Person].[Person]

14) Zählen Sie die Anzahl der Datensätze mit dem Vornamen "Ken" (FirstName) in der Datei
in der Tabelle Person.Person einer zuvor geladenen AdventureWorks-Datenbank!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT COUNT(Vorname)
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE Vorname='Ken'

15) Zählen Sie, wie viele Datensätze mit demselben Namen (Vorname und Nachname)
ist die zuvor geladene AdventureWorks-Datenbank Person.Person
auf Ihrer Tafel! Ordnen Sie die Ergebnisse so an, dass die meistgenutzten
Namenspaare am Anfang der Liste und nur die ersten 20 Ergebnisse
einbezogen werden!

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT TOP (20) Vorname, Nachname, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person]
  GROUP BY Vorname, Nachname
  ORDER BY COUNT(1) DESC

16) Zählen Sie, wie viele Datensätze mit demselben Namen (Vorname und Nachname)
ist die zuvor geladene AdventureWorks-Datenbank Person.Person
auf deinem Brett! Ordne das Ergebnis so an, dass nur 4 und mehr als 4
die im Namenspaar verwendet werden, sollten aufgeführt werden!

SELECT Vorname, Nachname, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person]
  GROUP BY Vorname, Nachname
  MIT COUNT(1) >= 4
  REIHENFOLGE NACH COUNT(1) DESC

17)Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen
AdventureWorks-Datenbank Person.Person und Person.EmailAddress
Tabellen mit den folgenden Feldern: Person.Person.FirstName,
Person.Person.LastName, Person.EmailAddress.EmailAddress, so dass die
um Tabellen zu verknüpfen, wird das Feld BusinessEntityID sowohl für
von der Tafel! Zeige nur die ersten 50 Zeilen des Ergebnisses an!

SELECT TOP (50) p.FirstName, p.LastName, e.EmailAddress
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

18) Erstellen Sie anhand der vorherigen Abfrage eine Lösung, bei der die E-Mail-Adresse
(EmailAddress) nur der Namensteil (vor dem @-Teil ist sichtbar)!

SELECT TOP (50) p.FirstName, p.LastName, LEFT(e.EmailAddress,CHARINDEX('@',e.EmailAddress)-1)
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

19)Oder eine Abfrage, bei der nur der Domänenteil (nach @)
sichtbar!

SELECT TOP (50) p.FirstName, p.LastName, RIGHT(e.EmailAddress,LEN(e.EmailAddress)- CHARINDEX('@',e.EmailAddress))
  FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID

20) (ZEIT AUFGABE) Erstellen Sie eine Abfrage, die die
Sales.SalesPerson aus dem Feld BusinessEntityID und ein "minsales"
ist eine berechnete Spalte genannt. Der Wert von minsales sollte, Zeile für Zeile, SalesYTD sein
Feld, wenn SalesYTD > 1000000, sonst NULL
Mehrwert schaffen.

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD > 1000000 THEN SalesYTD ELSE NULL END as minsales
  FROM [AdventureWorks2016].[Sales].[SalesPerson]

21) (SEHR WICHTIGE AUFGABE) Erstellen Sie eine Abfrage, die die
Sales.SalesPerson aus dem Feld BusinessEntityID und einer "salesinfo".
ist eine berechnete Spalte genannt. Der Wert von salesinfo sollte pro Zeile in SalesYTD gesetzt werden
Feld wenn SalesYTD >= 2000000, Wenn
SalesYTD<2000000 aber größer oder gleich 1000000, dann ist die
Wert sollte 1500000 sein, wenn weniger als 1000000 dann NULL. z.B.
Ausgänge :

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD >= 2000000 THEN SalesYTD ELSE CASE WHEN SalesYTD>=1000000 THEN 1500000 ELSE NULL END END als salesinfo
  FROM [AdventureWorks2016].[Sales].[SalesPerson]

Bewertung des Microsoft SQL Server (im Folgenden SQL) Wissensstandes

  • T-SQL-Code verwenden
  • Achten Sie darauf, die angegebenen SQL-Dateinamen zu verwenden
  • Ihre hochgeladene ZIP-Datei sollte keine Unterverzeichnisse enthalten, sondern nur die Dateien
  • Stellen Sie sicher, dass bei Datenbank-, Benutzer- und anderen Objektnamen zwischen Groß- und Kleinschreibung unterschieden wird.
  • Ändern Sie in Abfragen die Namen der Spalten, die nur angezeigt werden sollen, wenn die Aufgabe dies ausdrücklich erfordert
  • Die Abfragen, die im Fenster Spalten in der in der Übung angegebenen Reihenfolge erscheinen
  • Stellen Sie sicher, dass Sie immer die richtige Datenbank auswählen
  • ASCII- oder UTF-8-Zeichenkodierung (ohne BOM) verwenden
  • Die Standard-Startdatenbank für das MSSQL LOGIN, das die Aufträge im Docker-Container "ausführt", sind die ersten 10 Aufträge: Meister.
  • Im Docker-Container ist die Standard-Startdatenbank für das MSSQL LOGIN, das die Aufträge im Docker-Container "ausführt", von 11) bis 20) Aufträge: AbenteuerWerkeFür diese Aufgaben ist es ausreichend, wenn Ihre hochgeladene Datei nur die Abfrage enthält.
  • Die Auswertung von Sql-Erweiterungsdateien ist in erster Linie Autograder die anderen werden manuell bewertet.

Aufgaben:

  1. Dem Server wird ein Laufwerk hinzugefügt, das noch nicht in Gebrauch ist (20GBInstallieren Sie es, erstellen Sie eine 20 GB große Partition mit NTFS-Dateisystem und fügen Sie S: ist als Laufwerk gemountet, und der Volume-Name lautet: SQLDATA sein sollte.
Evidencia: Computerverwaltung => Speicher => Datenträgerverwaltung
Dateiname: WINSQL-01.jpg
  1. S: Antrieb zur Schaffung eines SQLDATA und zwei weitere Unterverzeichnisse innerhalb dieses Verzeichnisses: DB und LOG mit dem Namen.
Evidencia: Öffnen Sie den Datei-Explorer => S: => SQLDATA-Verzeichnis, wenn Sie die beiden angeforderten Unterverzeichnisse sehen.
Dateiname: WINSQL-02.jpg
  1. Melden Sie sich am SQL-Server an (GREENFOX Instanz) mit Microsoft SQL Server Management Studio!
Evidencia: GREENFOX-Instanz geöffnet in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-03.jpg
  1. Rufen Sie die C:\install\MSSQL\AdventureWorks2016.bak Datenbank auf die GREENFOX-Instanz des Servers AbenteuerWerke so dass sich die Datenbankdateien im Verzeichnis S:\SQLDATA\DB, während die Protokolldateien im Verzeichnis S:\SQLDATA\LOG zur Bibliothek.

Gradescopeban finden Sie das Backup hier: /AbenteuerWerke2016.bakdie Datenbankdateien: /SQLDATA/DB/AdventureWorks_Data.mdf, während die Protokolldateien im Verzeichnis /SQLDATA/LOG/AdventureWorks_Log.ldf zu Dateien

Evidencia: Kopieren oder speichern Sie ein Wiederherstellungsskript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-04.sql
USE [master]
RESTORE DATABASE [AdventureWorks] FROM DISK = N'/home/AdventureWorks2016.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'/SQLDATA/DB/AdventureWorks_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'/SQLDATA/LOG/AdventureWorks_Log.ldf', NOUNLOAD, STATS = 5

GO
  1. Erstellen einer isvc MS SQL Server authentifiziertes Konto mit minimalen (öffentlichen) Rechten! Das Passwort sollte lauten: interfAce21 Das Passwort sollte vom Benutzer bei der ersten Anmeldung nicht geändert werden müssen.
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-05.sql
USE [master]
GO
CREATE LOGIN [isvc] WITH PASSWORD=N'interfAce21', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
  1. Erstellen einer INTERFACE mit den folgenden Einstellungen:
    1. Name der Datenbank: INTERFACE
    2. Eigentümer: isvc
    3. Einziehungsmodell: FULL
    4. Ursprüngliche Datenbankdatei:
      1. Größe: 64 MByte
      2. Speicherort: S:\SQLDATA\DB (Gradescope auf /SQLDATA/DB)
    5. Startup-Protokolldatei:
      1. Größe: 24 MByte
      2. Speicherort: S:\SQLDATA\LOG (Gradescope auf /SQLDATA/LOG)
DATENBANK ERSTELLEN [SCHNITTSTELLE]
 CONTAINMENT = KEINE
 ON PRIMARY
( NAME = N'INTERFACE', FILENAME = N'/SQLDATA/DB/INTERFACE.mdf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'INTERFACE_log', FILENAME = N'/SQLDATA/LOG/INTERFACE_log.ldf' , SIZE = 24576KB , FILEGROWTH = 65536KB )
GO

USE [INTERFACE]
GO
ALTER AUTHORIZATION ON DATABASE::[INTERFACE] TO [isvc]

ALTER DATABASE [INTERFACE] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [INTERFACE] SET ARITHABORT OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [INTERFACE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [INTERFACE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [INTERFACE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [INTERFACE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [INTERFACE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [INTERFACE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [INTERFACE] SET DISABLE_BROKER
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [INTERFACE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [INTERFACE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [INTERFACE] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [INTERFACE] SET READ_WRITE
GO
ALTER DATABASE [INTERFACE] SET RECOVERY FULL
GO
ALTER DATABASE [INTERFACE] SET MULTI_USER
GO
ALTER DATABASE [INTERFACE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [INTERFACE] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [INTERFACE] SET DELAYED_DURABILITY = DISABLED
GO
USE [INTERFACE]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Aus;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Aus;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
USE [INTERFACE]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [INTERFACE] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
  1. Geben Sie nur Abfragerechte an isvc Konto für die AbenteuerWerke Datenbank HumanResources in der Regelung vMitarbeiter auf Ihrer Beobachtungsliste!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-07.sql
USE [AdventureWorks]
GO
CREATE USER [isvc] FOR LOGIN [isvc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [isvc]
GO
  1. INTERFACE Datenbank zur Erstellung einer AWDatenbank Schema!
Evidencia: Microsoft SQL Server Management Studio in der GREENFOX-Instanz in der Datenbank INTERFACE AWdatabase schema "Create To" script.
Dateiname: WINSQL-08.sql
VERWENDEN [SCHNITTSTELLE]
GO

/****** Objekt: schema [dbo] Script Datum: 2023 Jan 15 13:45:32 ******/
CREATE SCHEMA [AWdatabase]
GO
  1. Von AWDatenbank Schema zur Erstellung eines Synonym AW_Employee die die AbenteuerWerke Datenbank HumanResources in der Regelung vMitarbeiter zeigt auf das Ansichtsfenster!
Evidencia: Microsoft SQL Server Management Studio in GREENFOX Instanz der Datenbank INTERFACE AWdatabase schema AW_Employee gleichbedeutend mit dem Skript "Create To".
Dateiname: WINSQL-09.sql
VERWENDEN [SCHNITTSTELLE]
GO
CREATE SYNONYM [AWdatabase].[AW_Employee ] FOR [AdventureWorks].[HumanResources].[vEmployee]
GO
  1. Machen Sie die INTERFACE eine FULL-Sicherung der Datenbank, wie folgt:
  1. Name der Sicherungsdatei: S:\SQLDATA\INTERFACE.bak (In Gradescope /var/backups/INTERFACE.bak)
  2. Speichern überschreibt den gleichnamigen Mediensatz!
Evidencia: In Microsoft SQL Server Management Studio in der GREENFOX-Instanz, das INTERFACE-Datenbank-Backup-Skript.
Dateiname: WINSQL-10.sql
BACKUP DATABASE [INTERFACE] TO DISK = N'/var/backups/INTERFACE.bak' WITH NOFORMAT, INIT, NAME = N'INTERFACE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
  1. Erstellen Sie eine SQL-Abfrage, die die Quadratwurzel von 25 berechnet!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-11.sql
SELECT SQRT(25)
  1. Erstellen Sie eine SQL-Abfrage, die das heutige Datum in diesem Format zurückgibt: 2022-10-25
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-12.sql
SELECT CAST(GETDATE() as date)
  1. Erstellen Sie eine SQL-Abfrage, die berechnet, wie viele Tage seit dem 25. April 1994 vergangen sind!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-13.sql
SELECT DATEDIFF(Tag,'2022-04-25',GETDATE())
  1. Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen AbenteuerWerke Datenbank Person.Adresse Tabelle (alle Felder - ändern Sie nicht die Reihenfolge der Spalten)!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-14.sql
/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
USE [AdventureWorks]
GO
SELECT TOP (15) *
  FROM [AdventureWorks].[Person].[Adresse]
  1. Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen AbenteuerWerke Datenbank Person.Person von der Website von die ersten 5 Elemente(alle Felder - ändern Sie nicht die Reihenfolge der Spalten), sondern so, dass dort, wo die PersonType Feldwert 'EM‘!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-15.sql
USE [AdventureWorks]
GO
SELECT TOP (5) *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'
  1. Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen AbenteuerWerke Datenbank Person.Person von der Website von die ersten 30 Artikelsondern so, dass dort, wo die PersonType Feldwert 'EM' und nur die PersonType, Vorname, Nachname Felder im Abfrageergebnis!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-16.sql
USE [AdventureWorks]
GO
SELECT TOP (30) PersonType, Vorname,Nachname
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'
  1. Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen AbenteuerWerke Datenbank Person.Person von der Website von die ersten 20 Punktesondern so, dass dort, wo die PersonType Feldwert 'EM' und nur die PersonType, Vorname, Nachname Felder im Abfrageergebnis, aber die Vorname und Nachname sollten in einer Spalte stehen, getrennt durch ein einzelnes Leerzeichen, Vollständiger Name unter dem Namen!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-17.sql
USE [AdventureWorks]
GO
SELECT TOP (20) PersonType, Vorname+ ' ' + Nachname as FullName
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'
  1. Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen AbenteuerWerke Datenbank Person.Person der Tabelle (alle Spalten, alle Felder - ändern Sie nicht die Reihenfolge der Spalten), wobei PersonType Feldwert 'EM' und Vorname enthält nur das Feld 'John' sollte enthalten sein!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-18.sql
USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'
  1. Erstellen Sie eine SQL-Abfrage, die die zuvor geladenen AbenteuerWerke Datenbank Person.Person der Tabelle (alle Spalten, alle Felder - ändern Sie nicht die Reihenfolge der Spalten), wobei PersonType Feldwert 'EM' und Vorname enthält nur das Feld 'John', sollten nur die angezeigt werden, bei denen die Nachname Feldwert 'e'-Zeichen Ende!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-19.sql
USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'
  1. Zählen Sie, wie viele 'Ken' Vornamen (Vorname) ist der zuvor geladene Datensatz AbenteuerWerke Datenbank Person.Person auf Ihrem Brett!
Evidencia: Kopieren oder speichern Sie ein Skript in Microsoft SQL Server Management Studio.
Dateiname: WINSQL-20.sql
USE [AdventureWorks]
GO
SELECT COUNT(*)
  FROM [AdventureWorks].[Person].[Person]
  WHERE Vorname='Ken'