SQL

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

WÄHLEN SIE DIE ERSTEN 10 PROZENT MIT KRAWATTEN;

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

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

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

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

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

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;

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;

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;

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

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;

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;

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;

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 < '20170101' THEN '2016y'
END as Jahre
FROM Umsatz.Aufträge)
SELECT jahre, COUNT(jahre)
FROM was auch immer
GROUP BY jahre;

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

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

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

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

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

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');

SET IDENTITY_INSERT Hr.Employees ON;

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

SET IDENTITY_INSERT Hr.Employees OFF;

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 dbo.testlines
OUTPUT gelöscht.*
FROM dbo.testlinien tol
INNER JOIN dbo.testorders tor ON tor.OrderID=tol.OrderID
WHERE OrderDate='20130101'

/* 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)
);

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

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

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)
);

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)
);

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

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

ALTER TABLE dbo.employees WITH CHECK
CHECK CONSTRAINT CK_employees_contacts;

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

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;

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

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

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

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

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

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';

DECLARE @stockItemId INT;
SET @stockItemId = 2;

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

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;

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;

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;

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';

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

SELECT @ido_datetime, @ido_datetime2;

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;

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

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);

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

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

DECLARE @chunk SMALLINT = 32768;
SELECT @darabszam;

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

DECLARE @ma DATETIME2 =GETUTCDATE();

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

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

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')

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 '));

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

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')

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')

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

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

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

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

-- Herstellung 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 Vertrieb.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 auf den Typ verweisen.
*/
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;

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

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

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

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;

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;

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;

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

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

PRINT 10/0;

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

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;

   BEGIN TRY
   EXEC dbo.badprocedure
   END TRY
   BEGIN CATCH

   END CATCH

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;

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;

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;

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;

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;

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;

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;

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

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;

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

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

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

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%'

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;

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

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;

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

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

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;

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;

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

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;

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();

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;

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 es 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.testorders
WHERE OrderID  500
    BEGIN
        SET @days=500;
    END

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testorders
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;

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);

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);

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);

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

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

/* 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;

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;

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;

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;

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;

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 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;

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;

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;

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;

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;

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;

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

/* 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;

SELECT * FROM sys.tables;

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;

SELECT @@SPID;

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;

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;

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'));

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

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

Select * FROM sys.computed_columns;

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

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%'

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

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;

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

SELECT b.name,a.* FROM sys.databases b
CROSS APPLY sys.dm_db_log_info(b.datenbank_id) a
WHERE b.name = 'WideWorldImporters';

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

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();

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
where session_id = 75;

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)

VERWENDUNG [CEGESADATA]
GO

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

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

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

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'

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])

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')

/*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

CEGESADATOK verwenden
gehen Sie zu

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

weiter zu

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'
*/

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

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

VERWENDUNG [CEGESADATA]
GO

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

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

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))

/****** 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')

SELECT name, start_at
 FROM kohorten

SELECT Name, Land
FROM Auszubildende

SELECT Name, Alter
FROM Auszubildende
WHERE Alter < 21

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

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

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

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

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

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

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

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

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

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

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

VERWENDEN [SCHNITTSTELLE]
GO

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

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

VERWENDEN [SCHNITTSTELLE]
GO
SELECT *
FROM AWDB.AW_Employee

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

WÄHLEN SIE @@VERSION

SELECT 'Hallo Welt!'

SELECT SQRT(30)

SELECT CAST(GETDATE() as date)

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

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

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

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

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

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

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

/****** 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')

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

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

/****** 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

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

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

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

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

/****** 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]

/****** 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]

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

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

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

USE [AdventureWorks]
GO
CREATE USER [isvc] FOR LOGIN [isvc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [isvc]
GO

VERWENDEN [SCHNITTSTELLE]
GO

/****** Objekt: schema [dbo] Script Datum: 2023 Jan 15 13:45:32 ******/
CREATE SCHEMA [AWdatabase]
GO

VERWENDEN [SCHNITTSTELLE]
GO
CREATE SYNONYM [AWdatabase].[AW_Employee ] FOR [AdventureWorks].[HumanResources].[vEmployee]
GO

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

SELECT SQRT(25)

SELECT CAST(GETDATE() as date)

SELECT DATEDIFF(Tag,'2022-04-25',GETDATE())

/****** Skript für den Befehl SelectTopNRows aus SSMS ******/
USE [AdventureWorks]
GO
SELECT TOP (15) *
  FROM [AdventureWorks].[Person].[Adresse]

USE [AdventureWorks]
GO
SELECT TOP (5) *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'

USE [AdventureWorks]
GO
SELECT TOP (30) PersonType, Vorname,Nachname
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'

USE [AdventureWorks]
GO
SELECT TOP (20) PersonType, Vorname+ ' ' + Nachname as FullName
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'

USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'

USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'

USE [AdventureWorks]
GO
SELECT COUNT(*)
  FROM [AdventureWorks].[Person].[Person]
  WHERE Vorname='Ken'