SQL

ORDER BY Continent ASC, Subregion DESC
OFFSET 29 ROWS FETCH NEXT 50 ROWS ONLY;

SELECT TOP 10 PERCENT WITH TIES;

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

WHERE StockItemName LIKE '%ham[^m]%'
WHERE StockItemName LIKE 'a[l-m]%[l-p]'
WHERE CountryName LIKE 'H__[g,d]%'

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

SUM(Quantity) AS total_pack, AVG(UnitPrice) AS avg_unitprice, COUNT(OrderLineID) AS cnt_orderline

SELECT COUNT(ISNULL(PickingCompletedWhen,'1970-01-01'))
SELECT COUNT_BIG(*) FROM Sales.OrderLines;

COUNT(CASE WHEN PickingCompletedWhen IS NULL THEN 1 ELSE NULL END) AS unkown_values

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

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

SELECT CustomerId, OrderId, OrderDate
FROM Sales.Orders o1
WHERE OrderID=(SELECT MAX(OrderID)
FROM Sales.Orders o2
WHERE o1.CustomerId=o2.CustomerId)
Order by 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 CustomerID, CustomerName
FROM Sales.Customers c
WHERE PostalCityID = 33832
AND EXISTS
(SELECT * FROM Sales.Orders o
WHERE o.CustomerID=c.CustomerID);

SELECT CityID, CityName, (SELECT MIN(CityID) FROM Application.Cities c2 WHERE c2.CityID > c1.CityID) AS nextcityid
FROM Application.Cities 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 Application.Cities c1
WHERE StateProvinceID IN (SELECT StateProvinceID FROM Application.StateProvinces WHERE StateProvinceName='Colorado')
GROUP BY CityID, CityName, LatestRecordedPopulation;

SELECT order_count, month_number
FROM (
SELECT MONTH(OrderDate) AS month_number, COUNT(OrderDate) AS order_count
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
) AS derivedtable
WHERE order_count > 6000
ORDER BY month_number ASC;

SELECT order_count, month_number
FROM (
SELECT MONTH(OrderDate), COUNT(OrderDate)
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
) AS derivedtable (month_number,order_count)
WHERE order_count > 6000
ORDER BY month_number ASC;

SELECT CustomerID, ORderID, OrderDate
FROM Sales.Orders AS a -- a=schema_name.table_name
INNER JOIN
(
SELECT MAX(OrderDate) AS maxorderdate
FROM Sales.Orders AS b
) AS derivedtable ON a.orderDate=derivedtable.maxorderdate;

WITH cte_table AS (
SELECT MONTH(OrderDate) AS month_number, COUNT(OrderDate) AS order_count
FROM Sales.Orders
GROUP BY MONTH(OrderDate)
)
SELECT order_count, month_number FROM cte_table
WHERE order_count > 6000
ORDER BY month_number ASC;

SELECT CASE
WHEN 2013=YEAR(OrderDate) THEN '2013y'
WHEN 2014=FORMAT(OrderDate,'yyyy') THEN '2014y'
WHEN OrderDate like '2015%' THEN '2015y'
WHEN OrderDate >= '20160101' AND OrderDate < '20170101' THEN '2016y'
END AS years, COUNT(*)
FROM Sales.Orders
GROUP BY CASE
WHEN 2013=YEAR(OrderDate) THEN '2013y'
WHEN 2014=FORMAT(OrderDate,'yyyy') THEN '2014y'
WHEN OrderDate like '2015%' THEN '2015y'
WHEN OrderDate >= '20160101' AND OrderDate < '20170101' THEN '2016y'
END

SELECT years, COUNT(years)
FROM (
SELECT CASE
WHEN 2013=YEAR(OrderDate) THEN '2013y'
WHEN 2014=FORMAT(OrderDate,'yyyy') THEN '2014y'
WHEN OrderDate like '2015%' THEN '2015y'
WHEN OrderDate >= '20160101' AND OrderDate < '20170101' THEN '2016y'
END as years
FROM Sales.Orders
) AS whatever
GROUP BY years;

WITH whatever AS (
SELECT CASE
WHEN 2013=YEAR(OrderDate) THEN '2013y'
WHEN 2014=FORMAT(OrderDate,'yyyy') THEN '2014y'
WHEN OrderDate like '2015%' THEN '2015y'
WHEN OrderDate >= '20160101' AND OrderDate < '20170101' THEN '2016y'
END as years
FROM Sales.Orders)
SELECT years, COUNT(years)
FROM whatever
GROUP BY years;

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

SELECT beosztott.Nev, fonok.Nev, nagyfonok.Nev FROM dbo.emberek as Beosztott
INNER JOIN dbo.Emberek as fonok ON beosztott.FonokId = fonok.Id
LEFT JOIN dbo.Emberek as nagyfonok ON fonok.FonokId = nagyfonok.Id

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

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

DROP TABLE IF EXISTS HR.Employees
CREATE TABLE HR.Employees (
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
VALUES (1, '123456789');

SET IDENTITY_INSERT Hr.Employees ON;

INSERT INTO HR.Employees (id,emp_name)
VALUES(666,'firstname');

SET IDENTITY_INSERT Hr.Employees OFF;

INSERT INTO HR.Employees (emp_name)
OUTPUT inserted.emp_name INTO HR.Emplog(emp_name)
VALUES ('testname')

DELETE Hr.Employees
OUTPUT deleted.emp_name INTO HR.Emplog
WHERE id=666;

DELETE dbo.testlines
OUTPUT deleted.*
FROM dbo.testlines 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 IDENTITY(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 IDENTITY(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
, username 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 (emp_name,birth_date,mother_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
,last_name NVARCHAR(50) NOT NULL
,first_name NVARCHAR(50) NOT NULL
,phone_number VARCHAR(22)
,emailadress 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 MONTHS
,HISTORY_TABLE = dbo.persondate_history)
)
/* temporal table - töröltek*/
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,UnitPrice, Quantity
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 > no index ! best practice - nonclustered index         filtered testdb isHIgh education table-ben*/
/*talán masterwork - dátumnál nem jó*/
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;
/* Csak egy értéket tud tárolni */
/* Egyben az előzőek */
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 @myStockItemName=StockItemName, @myStockItemColorId=ColorID 
FROM Warehouse.StockItems
ORDER BY StockItemName;

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 @myStockItemName=StockItemName FROM Warehouse.StockItems
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)='teszt'
SELECT @mychar;

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

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

DECLARE @myvarchar VARCHAR(3)='teszt'
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)='teszt'
SELECT @mychar;

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

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

DECLARE @myvarchar NVARCHAR(3)='teszt'
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 @darabszam SMALLINT = 32768;
SELECT @darabszam;

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

DECLARE @ma DATETIME2 =GETUTCDATE();

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

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

DECLARE @ma DATETIME2 =GETUTCDATE();

SELECT @ma,FORMAT(@ma,'yyyy. MM. dd.')

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 @float,FORMAT(@float,'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('Ékezet')
SELECT LOWER('ÉKEzet')

SELECT REPLICATE('ho',3)
SELECT LEFT('HELLO WORLD', 5)

SELECT REVERSE('hello')

SELECT REPLACE('HELLO world', 'World', 'JOE')

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

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(yyyy, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(s, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

SELECT ValidFrom, '2022-01-01 00:00', DATEDIFF(s, ValidFrom,'2022-01-01 00:00')
FROM Warehouse.StockItems

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

SELECT ROUND(123.45,0)
SELECT ROUND(123.55,0)
SELECT ROUND(123.55,1)

CREATE TABLE #myTempTable
(
	Id uniqueidentifier primary key nonclustered,
	Name NVARCHAR(100) NOT NULL
);

CREATE TABLE ##globalTempTable
(
	Id uniqueidentifier primary key nonclustered,
	Name NVARCHAR(100) NOT NULL
);
DROP TABLE ##globalTempTable;

DECLARE @myTableVariable TABLE (
	Id uniqueidentifier primary key nonclustered,
	Name NVARCHAR(100) NOT NULL
);

-- közös tábla készítés
USE WideWorldImporters;
GO

SELECT COUNT(*) FROM Sales.Orders;
-- 0.39 cost
SELECT COUNT(*) FROM Sales.OrderLines;

SELECT TOP 1 * FROM Sales.OrderLines;

CREATE TABLE #Temp_OrderLines
(
	OrderLineID int PRIMARY KEY,
	StockItemID int NOT NULL, --/*FOREIGN KEY*/ References Warehouse.StockItems(StockItemID), --ezt ne csináljuk, mert nincs neve, meg kell találni a nevet is
	DESCRIPTION NVARCHAR(1000),
	Quantity int NOT NULL
);
-- 164 stock item id
INSERT INTO #Temp_OrderLines(OrderLineID,StockItemID,[DESCRIPTION],Quantity)
SELECT OrderLineID,StockItemID,[Description],Quantity
FROM Sales.OrderLines
WHERE StockItemID=164;

SELECT * FROM #Temp_OrderLines;
--0,013 cost
SELECT COUNT(*) FROM #Temp_OrderLines;

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

SELECT COUNT(*) FROM #Temp_OrderLines;

DECLARE @myTableVariable TABLE (
	Id uniqueidentifier primary key nonclustered,
	Name NVARCHAR(100) NOT NULL
);

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

SELECT * FROM @myTableVariable;

USE WideWorldImporters;

/*
    Declaring a table type
*/
DROP TYPE IF EXISTS mytable;
CREATE TYPE mytable AS table
(
    id int NOT NULL,
	Name NVARCHAR(100) NOT NULL
);

/*
    From now on, you don't have to declare your table variables
    with column info, just reference the type.
*/
DECLARE @t AS mytable;
INSERT INTO @t VALUES(1,'HELLO WORLD');

SELECT * FROM @t;


CREATE TABLE dbo.tesztecske AS mytable; -- nem

/*ez igen*/
SELECT * 
INTO dbo.tesztecske
FROM @t
WHERE 1 = 2; --oszlopokat megcsinálja, de sorokat nem


SELECT * FROM dbo.tesztecske;

DECLARE @year SMALLINT;
SET @year = 2022;

IF @year = YEAR(GETDATE())
BEGIN
PRINT 'Match! The current year is: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    PRINT 'THERE IS NO MATCH! THE Year variable:' + CAST(@year AS varchar(5));
    PRINT 'THERE IS NO MATCH! THE current year:' + 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 'Match! The current year is: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    PRINT 'THERE IS NO MATCH! THE Year variable:' + CAST(@year AS varchar(5));
    PRINT 'THERE IS NO MATCH! THE current year:' + 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 'Match! The current year is: ' + CAST(@year AS varchar(5));
END
ELSE
BEGIN
    IF @year IS NOT NULL
    BEGIN 
    PRINT 'THERE IS NO MATCH! THE Year variable:' + CAST(@year AS varchar(5));
    PRINT 'THERE IS NO MATCH! THE current year:' + CAST(YEAR(GETDATE()) AS varchar(5));
    END
    ELSE
    BEGIN
    PRINT '@year value is 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); -- február
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 ('Monday','Tuesday', 'Friday') OR CAST(GETDATE() AS date) = '2023-02-20'
BEGIN
PRINT 'THIS IS TRUE!'
END

/*testcase-2*/

IF EOMONTH('2023-03-20',-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,'2023-02-19') IN ('Monday','Tuesday', 'Friday') OR CAST(GETDATE() AS date) = '2023-02-20'
BEGIN
PRINT 'THIS IS TRUE!'
PRINT 'THIS IS STILL TRUE!'
END

/*test-case 3*/

IF EOMONTH('2023-03-20',-1)= CAST(DATEADD(day, -20,GETDATE()) AS date) OR DATENAME(dw,'2023-02-19') IN ('Monday','Tuesday', 'Friday') OR CAST(GETDATE() AS date) = '2023-01-28'
BEGIN
PRINT 'THIS IS TRUE!'
PRINT 'THIS IS STILL TRUE!'
END
ELSE
BEGIN
PRINT 'NOT TRUE'
END
DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

Use WideWorldImporters;
GO

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

IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb)
)
BEGIN
PRINT 'There are orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5))
END
ELSE
BEGIN
    PRINT 'There are not orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5))
END ;
/* Ugyanaz a feladat csak egy kis NULL-al fűszerezve*/
DECLARE @yeara SMALLINT=2015, @yearb SMALLINT;
 SELECT @yearb;

IF EXISTS (SELECT OrderID FROM SAles.Orders WHERE YEAR(OrderDate) IN (@yeara,@yearb)
)
BEGIN
PRINT 'There are orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5))
END
ELSE
BEGIN
    PRINT 'There are not orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5))
END ;

GO
/*NULL HANDLING*/

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

IF (@yeara IS NULL OR @yearb IS NULL)
BEGIN
PRINT 'UNKNOWN value(s) are detected';

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 'There are orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5));
END
ELSE
BEGIN
    PRINT 'There are not orders in ' + CAST(@yeara as VARCHAR(5))+ ' or in '+CAST(@yearb as VARCHAR(5));
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));

/* simple insert loop INSERT 1-1000 > dbo.t1 method 1*/

DECLARE @cnt int=1;

WHILE @cnt < 1001
BEGIN
 INSERT INTO dbo.t1 VALUES(@cnt);
 SET @cnt+= 1;
END

SELECT num FROM dbo.t1;
/*
 INSERT VALUES 1->1000 except 5 WITH Autoincrement column (method 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;  

/*
break loop at @cnt=5
*/
TRUNCATE TABLE dbo.t2;

SET IDENTITY_INSERT dbo.t2 ON;  

DECLARE @cnt int=0;
WHILE (@cnt < 1001)
BEGIN
    SET @cnt +=1;
    IF @cnt=5 
    BEGIN 
        PRINT 'loop will be terminated.';
        BREAK;
    END
    INSERT INTO dbo.t2 (num) VALUES (@cnt);
END
SELECT num FROM dbo.t2;

/*
INSERT VALUES 1-1000 -> dbo.t3 method 3
*/
INSERT INTO dbo.t3 DEFAULT VALUES;
GO 1000

SELECT * FROM dbo.t3;

DECLARE @cnt int=1;

WHILE @cnt <= 10
BEGIN

    PRINT 'The counter value is '+CAST(@cnt AS varchar(5));
    WAITFOR DELAY '00:00:01'
     SET @cnt+= 1;
   

END

/*EZ A JÓ*/

DECLARE @cnt int=1;

WHILE @cnt <= 10
BEGIN

    RAISERROR('The counter value is %d',0,1,@cnt) WITH NOWAIT;
    WAITFOR DELAY '00:00:01'
     SET @cnt+= 1;
END

DECLARE @rows int;
UPDATE #mytemp
SET Quantity=1000
WHERE Quantity>100;
SET @rows=@@ROWCOUNT;
If @rows > 0
BEGIN
    SELECT CAST(@rows as varchar(5)) + ' rows were updated'
END
ELSE
BEGIN
SELECT 'No rows were updated'
END

PRINT 10/0;

BEGIN TRY
    PRINT 10/0;
END TRY
BEGIN CATCH
    PRINT '0-val otszottál butus!';
END CATCH;

BEGIN TRY
    PRINT 10/0;
END TRY
BEGIN CATCH
    PRINT 'Error occurred!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

   BEGIN TRY
   EXEC dbo.badprocedure
   END TRY
   BEGIN CATCH

   END CATCH

BEGIN TRY
    DECLARE @unitprice INT=0;
    SELECT * FROM dbo.notavailable;

    PRINT 10/@unitprice;
END TRY
BEGIN CATCH
    PRINT 'Error occurred!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

GO
CREATE OR ALTER PROCEDURE dbo.badprocedure
AS
    SELECT * FROM dbo.notavailable;
GO
EXEC dbo.badprocedure;
BEGIN TRY
    DECLARE @unitprice int=0;
    EXEC dbo.badprocedure;
    PRINT 10/@unitprice;
END TRY
BEGIN CATCH
    PRINT 'Error occurred!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

BEGIN TRY
    DECLARE @unitprice INT=-20;
    IF @unitprice < 0
    BEGIN
    -- THROW valid range begrin from 50000, def. severitylevel 16
        THROW 55555, 'The @unitprice parameter value is less than zero!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Error occurred!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

BEGIN TRY
    DECLARE @unitprice INT=-20;
    IF @unitprice < 0
    BEGIN
    -- THROW valid range begrin from 50000, def. severitylevel 16
        THROW 55555, 'The @unitprice parameter value is less than zero!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Error occurred!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + 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;

    /* throwing an error with an argument if the value is not acceptable */
    IF @myvar < 0
        RAISERROR ('The @myvar parameter value is %d!', 16, 1, @myvar);

    PRINT 'This line does not execute!'
END TRY
BEGIN CATCH
    PRINT 'Error occurred!'
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;

BEGIN TRY
    DECLARE @unitprice INT=-20;
    IF @unitprice < 0
    BEGIN
    -- THROW valid range begrin from 50000, def. severitylevel 16
        THROW 55555, 'The @unitprice parameter value is less than zero!', 1;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Error occurred!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + 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 @unitprice INT=-20;
    IF @unitprice < 0
    BEGIN
    -- RAISERROR
        DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('%d unitprice value causes the error! Your error code: %x', 16, 1, @unitprice,@myerrorstring) WITH LOG;
    END
    PRINT 'SOR'
END TRY
BEGIN CATCH
    PRINT 'Error occurred!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + 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 IDENTITY(1,1) PRIMARY KEY
    ,soldier_name varchar(100) NOT NULL
    ,soldier_bdate date CHECK (soldier_bdate > '19780101')
)

BEGIN TRY
    INSERT INTO dbo.military (soldier_name,soldier_bdate)
    VALUES ('John Ramobo', '19770101')
    -- RAISERROR
END TRY
BEGIN CATCH
    IF ERROR_NUMBER()=547
    BEGIN
    DECLARE @myerrorcode UNIQUEIDENTIFIER=NEWID();
        DECLARE @myerrorstring varbinary(16)= CAST(@myerrorcode AS varbinary(16));
        RAISERROR ('Check constraint violation. Your error code: %x', 16, 1,@myerrorstring) WITH LOG;
    END
    PRINT 'Error occurred!';
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
    PRINT 'Error message: ' + ERROR_MESSAGE();
    PRINT 'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10));
    PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10));
    PRINT 'Error module: ' + COALESCE(ERROR_PROCEDURE(),'');
END CATCH;
/*barmilyen hiba*/
BEGIN TRY
    INSERT INTO dbo.military (soldier_name,soldier_bdate)
    VALUES ('John Ramobo', '19770101')
    -- RAISERROR
END TRY
BEGIN CATCH
    DECLARE @newerrormessage NVARCHAR(1000);
    SET @newerrormessage ='Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + CHAR(13) + 'Error message: ' + ERROR_MESSAGE()+ CHAR(13) +'Error state: ' + CAST(ERROR_STATE() AS nvarchar(10))+ CHAR(13) +'Error severity: ' + CAST(ERROR_SEVERITY() AS nvarchar(10))+ CHAR(13) +'Error line: ' + CAST(ERROR_LINE() AS nvarchar(10)) + CHAR(13) + 'Error module: ' + 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,soldier_name,soldier_bdate)
        VALUES (1,'John Ramobo', '19790101')
    END TRY
    BEGIN CATCH
        THROW 55555, 'The @unitprice parameter value is less than zero!', 2;
    END CATCH
END CATCH

USE WideWorldImporters
GO
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum 
FROM Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.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 Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
GO
SELECT * FROM  FirstView;

GO
CREATE VIEW SecondView
WITH SCHEMABINDING, ENCRYPTION
AS
SELECT c.CustomerName, o.OrderID, SUM(ol.Quantity) as QuantitySum 
FROM Sales.Customers as c
JOIN Sales.Orders as o ON o.CustomerID = c.CustomerID
JOIN Sales.OrderLines as ol ON ol.OrderID = o.OrderID
JOIN Warehouse.StockItems as si ON si.StockItemID = ol.StockItemID
GROUP BY c.CustomerName, o.OrderID;
GO

GO
ALTER VIEW dbo.VarosokOrszagok2
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);
-- nem determinisztikus
GO
ALTER VIEW dbo.NemDet
WITH SCHEMABINDING
AS
SELECT Nev, GetDate() AS RandomDate
FROM dbo.Varosok
GO
CREATE UNIQUE CLUSTERED INDEX ix_Nemdet ON dbo.NemDet(Nev);

-- JOIN, csak az inner-re lehet

DROP VIEW IF EXISTS dbo.TobbTabla;

GO
CREATE VIEW dbo.TobbTabla
WITH SCHEMABINDING
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 - Create, Read, Update, Delete

-- ORDER BY - nem lehet alapból
-- ORDER BY - TOP-pal
GO
CREATE VIEW dbo.ViewOrderBy
WITH SCHEMABINDING
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.VarosokOrszagok2
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),
	OrszagNev NVARCHAR(100),
)

INSERT INTO #Eredmeny1
EXEC dbo.TaroltEljarasVarosokOrszagok

SELECT * FROM #Eredmeny1
GO
CREATE PROCEDURE dbo.VarosKereses(@SearchName 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 @SearchName
END
GO

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

EXEC dbo.VarosKereses '"nincs ilyen OR 1=1 "'

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

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

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

SELECT * FROM dbo.Varosok WHERE Id =100;

DROP TYPE IF EXISTS mytable;
CREATE TYPE mytable AS table
(
    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);

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-t ad vissza
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2,@doubledValue
SELECT @doubledValue;
GO

-- Ez jó:
DECLARE @doubledValue int;
EXEC dbo.OutputTest 2, @doubledValue OUTPUT
SELECT @doubledValue;

-- Paraméterek sorrendjét nem lehet felcserélni
DECLARE @doubledValue int;
EXEC dbo.OutputTest @doubledValue OUTPUT,2;
SELECT @doubledValue;
GO

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

-- kivéve haa nevükkel van megadva
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;
--korábban már visszaadta a @varost, ezért ez már nem írja felül
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;
--korábban megadta, ezért ez már nem írja felül
EXEC @ret = dbo.ReturnTest -3, @varos OUTPUT
SELECT @ret,@varos;
GO

GO
CREATE OR ALTER PROCEDURE dbo.PlaceOrder (@PaymentDate datetime2)
AS
IF @PaymentDate > GETDATE()
THROW 50001, 'Cannot Place an order in future',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.Osztas 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.Osztas 10, @hanyados = @ertek OUTPUT;
SELECT @ret, @ertek;
GO
DECLARE @ertek INT,@ret INT;
EXEC @ret=dbo.Osztas 10,5, @ertek OUTPUT;
SELECT @ret, @ertek;

GO
CREATE OR ALTER PROCEDURE dbo.TaroltEljarasVarosokOrszagok
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 like '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 like @searchParam;
SET @affectedRows=@@ROWCOUNT
END
GO

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

USE WideWorldImporters;
Go
GO
CREATE OR ALTER FUNCTION Sales.function_date()
RETURNS datetime  /* specify the data type of the value you return */
AS
BEGIN
    /* return your value */
    RETURN CURRENT_TIMESTAMP;
END;
GO
-- call function
SELECT Sales.function_date();

GO
CREATE FUNCTION Sales.total_sum_orderline(
    @ordelineID int
)
RETURNS decimal(20,3) --WITH SCHEMABINDING  -  hozzákötjük a tábla szerkezetéhez,  valamikor segít a teljesítményben
AS
BEGIN
    RETURN (SELECT Quantity*UnitPrice FROM Sales.OrderLines Where OrderLineID = @ordelineID)
END;
GO
/*
    Using the Scalar UDF to return the taxed line total value
    in the SELECT clause.
*/

SELECT Sales.total_sum_orderline(2);
SELECT Quantity*UnitPrice FROM Sales.OrderLines Where OrderLineID = 2;
/* minden sorhoz a total- lassú lesz,soronként számol és a CPU magas lesz, nem szeretjük a skaláris függvényeket */
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  -- hozzákötjük a tábla szerkezetéhez,  valamikor segít a teljesítményben
AS
BEGIN
    RETURN (SELECT Quantity*UnitPrice FROM dbo.testlines Where OrderLineID = @ordelineID)
END;
GO
SELECT dbo.total_sum_orderline(2);
SELECT Quantity*UnitPrice FROM dbo.testlines Where OrderLineID = 2;
/* számított oszlop - itt elvesztjük az inliningot - táblán belül maradjunk, ha csinálunk ilyet
check constraintbe is elveszítjük */
ALTER TABLE dbo.testlines
ADD totalsum AS dbo.total_sum_orderline(OrderID);

SELECT OrderLineID, totalsum FROM dbo.testlines
WHERE OrderLineID<200;
/*view-n - itt használjuk inkább */
GO
CREATE OR ALTER VIEW dbo.testview
WITH SCHEMABINDING
AS
SELECT OrderLIneID AS 'lineID'
, dbo.total_sum_orderline(OrderLineID)  as 'totalsum'
FROM dbo.testlines
GO
SELECT * FROM dbo.testview
WHERE LineID < 200;
/* if the view with schemabiding contains scalar function, then scalar function must be also schemabiding!! */
/* check constraint - inlining is not working */
ALTER TABLE dbo.testlines
ADD CONSTRAINT CK_test_1 CHECK (
(OrderLineID < 200 AND dbo.total_sum_orderline(OrderLineID) IS NOT NULL) OR OrderLineID >= 200);
UPDATE dbo.testlines
SET OrderLineID=1600
WHERE OrderLineID < 200;
--------------------------------datediff---------------------------------------------------------------------
DROP TABLE IF EXISTS dbo.testorders;
SELECT *
INTO dbo.testorders
FROM WideWorldImporters.Sales.Orders;
/* scalar function datediff orderdate between, return max 500 */
/* 2 return */
GO
CREATE OR ALTER FUNCTION dbo.datediffer(@orderid int)
RETURNS SMALLINT
WITH INLINE=ON -- csak inlineolhatot hoz létre
AS
BEGIN
    DECLARE @days SMALLINT;
    --RETURN (SELECT @days= DATEDIFF(dd, OrderDate, GETDATE()) FROM dbo.testorders WHERE OrderID = @orderid )
    SELECT @days= DATEDIFF(dd, OrderDate, GETDATE()) FROM dbo.testorders WHERE OrderID = @orderid
    IF @days > 500
    RETURN 500;

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testorders
WHERE OrderID < 200;
/*meg mindig nem, GETDATE*/
GO
CREATE OR ALTER FUNCTION dbo.datediffer(@orderid int)
RETURNS SMALLINT
WITH INLINE=ON -- csak inlineolhatot hoz létre
AS
BEGIN
    DECLARE @days SMALLINT;
    --RETURN (SELECT @days= DATEDIFF(dd, OrderDate, GETDATE()) FROM dbo.testorders WHERE OrderID = @orderid )
    SELECT @days= DATEDIFF(dd, OrderDate, GETDATE()) FROM dbo.testorders WHERE OrderID = @orderid
    IF @days > 500
    BEGIN
        SET @days=500;
    END

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID)
FROM dbo.testorders
WHERE OrderID < 200;
/*JÓ - inputdate*/
GO
CREATE OR ALTER FUNCTION dbo.datediffer(@orderid int, @inputdate DATE)
RETURNS SMALLINT
WITH INLINE=ON -- csak inlineolhatot hoz létre
AS
BEGIN
    DECLARE @days SMALLINT;
    --RETURN (SELECT @days= DATEDIFF(dd, OrderDate, GETDATE()) FROM dbo.testorders WHERE OrderID = @orderid )
    SELECT @days = DATEDIFF(dd, OrderDate, @inputdate) FROM dbo.testorders WHERE OrderID = @orderid
    IF @days > 500
    BEGIN
        SET @days=500;
    END

    RETURN @days;
END
GO
SELECT OrderID, OrderDate, dbo.datediffer(OrderID, GETDATE())
FROM dbo.testorders
WHERE OrderID < 200;
GO
CREATE OR ALTER FUNCTION dbo.datediffer2(@orderdate date,@inputdate date)
RETURNS smallint
WITH INLINE=ON
AS
BEGIN
	DECLARE @days int;
	SET @days = DATEDIFF(dd, @orderdate,@inputdate);
	IF @days > 500
		BEGIN
			SET @days = 500;
		END
	RETURN @days
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
/*
    Creating an inline TVF to get ColorId and ColorName
*/
CREATE FUNCTION Warehouse.GetColor(@colorid int)
RETURNS TABLE
AS
RETURN 
    (SELECT ColorID, ColorName 
     FROM Warehouse.Colors 
     WHERE ColorID = @colorid);
GO
/*
    Using the inline TVF in a simple SELECT query in the FROM clause.
*/
SELECT ColorId, ColorName 
FROM WareHouse.GetColor(36);

SELECT ColorId, ColorName 
FROM WareHouse.GetColor(10);

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

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

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

/* subquery verion: 0.1452 */
SELECT	o.OrderID
				,o.StockItemID
				,o.Quantity
				,(	SELECT MAX(Quantity)
					FROM Sales.OrderLines
					WHERE StockItemID = 180
				 ) AS Max_StockItem_Qty
				,(SELECT maxq.qty - Quantity
						FROM (	SELECT MAX(Quantity) AS qty
										FROM Sales.OrderLines
										WHERE StockItemID = 180
								 ) AS maxq
				) AS Max_Diff_Qty
FROM Sales.OrderLines AS o
WHERE StockItemID = 180;
/*	CROSS APPLY - table operator version - CROSS APPLY cost 0.102256 */
SELECT OrderId,StockItemID,Quantity,Max_StockItem_Qty,Max_StockItem_Qty-Quantity AS Max_Diff_Qty
FROM Sales.OrderLines
	CROSS APPLY (SELECT MAX(Quantity) 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()
);
-- login name
SELECT SUSER_NAME();
-- server name \ (instance)
SELECT @@SERVERNAME;
-- sesrver version
SELECT @@VERSION;
-- service name (instance name without server)
SELECT @@SERVICENAME;
/*trigger*/
GO
CREATE OR ALTER TRIGGER tr_logtouser
ON ALL SERVER FOR LOGON
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;
-- drop logon trigger
DROP TRIGGER tr_logtouser ON ALL SERVER;

DROP TABLE IF EXISTS dbo.Invoices;
CREATE TABLE dbo.Invoices (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    total INT NOT NULL
);
INSERT INTO dbo.Invoices
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 'you can not change or delete any tables!'
ROLLBACK;
END
PRINT 'hello';
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_invoices  (
    id INT, --PRIMARY KEY,
    customer_id INT NOT NULL,
    total INT NOT NULL,
    delete_time DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP
);
/* AFTER TRIGGER - create trigger to move deleted invoices to strono_table
SQL SERVER trigger works per statement!!!!!
- Avoid variables ! */
GO
CREATE TRIGGER dbo.TR_stroni_invoice ON dbo.Invoices AFTER DELETE
AS
SET NOCOUNT ON;
BEGIN
    INSERT INTO dbo.storno_invoices (id, customer_id,total)
    SELECT * FROM deleted;
END
GO
SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.storno_invoices;

DELETE FROM dbo.Invoices
WHERE total > 1000;

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.storno_invoices;

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

UPDATE dbo.Invoices
SET total=1000
WHERE id=3;

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

DROP TABLE IF EXISTS dbo.smalltotalinvoices
  CREATE TABLE dbo.smalltotalinvoices (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    total INT NOT NULL
);
SELECT * FROM dbo.Invoices;
GO 
CREATE OR ALTER TRIGGER dbo.tr_smalltotal
ON dbo.Invoices 
INSTEAD OF INSERT
AS
SET NOCOUNT ON;
BEGIN
 INSERT INTO dbo.Invoices
 SELECT * FROM inserted
 WHERE total >=1100;

 INSERT INTO dbo.smalltotalinvoices
 SELECT * FROM inserted
 WHERE total < 1100;
END
GO
 INSERT INTO dbo.Invoices
 VALUES(3,200,50000);

  INSERT INTO dbo.Invoices
 VALUES(5,200,900);

SELECT * FROM dbo.Invoices;
SELECT * FROM dbo.smalltotalinvoices;

bulk insert dbo.Invoices
  FROM N'C:\Users\mrhen\Documents\READER\MatReview\WEEK-4\w1d1_smalltotal.csv'
  WITH (fieldterminator=';',rowterminator='0x0A'/* nem '\n' mert Zolitól volt,  FIRSTROW=2 ha van fejlec*/)
TRUNCATE TABLE dbo.invoices;
TRUNCATE TABLE dbo.smalltotalinvoices;
/* DE FIRE_TRIGGERS */
bulk insert dbo.Invoices
  FROM N'C:\Users\mrhen\Documents\READER\MatReview\WEEK-4\w1d1_smalltotal.csv'
  WITH (fieldterminator=';',rowterminator='0x0A', FIRE_TRIGGERS/* nem '\n' mert Zolitól volt,  FIRSTROW=2 ha van fejlec*/)

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(Quantity) AS sum_stock_quantities
FROM Sales.OrderLines
GROUP BY StockItemID;
GO
SELECT SUM(quantity) AS sum_all_quantitis
FROM Sales.OrderLines;
/* FELADAT
orderlineid stockitemid sum_stock sum_all
1           1               20000   9million
2           1               20000   
3             2             1000
*/
WITH myCTE AS (
    SELECT StockItemId, SUM(Quantity) AS sum_stock_quantities
    FROM Sales.OrderLines
    GROUP BY StockItemID
),
myCTE2 AS (
    SELECT SUM(quantity) AS sum_all_quantitis
    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 with window function */
SELECT SUM(Quantity) OVER ()
FROM Sales.OrderLines;
/*replacing cte1 with new function*/
SELECT SUM(Quantity) OVER (PARTITION BY StockItemID)
FROM Sales.OrderLines;
/* Window function version */
SELECT sol.StockItemID,
OrderLineID, 
SUM(Quantity) OVER (PARTITION BY StockItemID) AS sum_stock_quantitis,
SUM(Quantity) OVER () AS sum_all_quantities
, FORMAT(SUM(Quantity) OVER (PARTITION BY StockItemID)/(SUM(Quantity) OVER ()*1.0),'P2') AS pct_all_quantity
FROM Sales.OrderLines sol
ORDER BY sol.[StockItemID] ASC, OrderLineID ASC;

SELECT sol.StockItemID, 
SUM(Quantity) /*OVER (PARTITION BY StockItemID)*/ AS sum_stock_quantitis,
SUM(SUM(Quantity)) OVER () AS sum_all_quantities
, FORMAT(SUM(Quantity) /*OVER (PARTITION BY StockItemID)*//(SUM(SUM(Quantity)) 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 -- holtversenynél kihagyja a és a következő rank a sor
    , DENSE_RANK() OVER(ORDER BY OrderDate) AS dense_rnk -- holtversenynél jön a következő rank
    , NTILE (20) OVER(ORDER BY OrderDate) AS n_tile -- 20 részre osztja, visszakapott halmazt
FROM Sales.Orders;

SELECT OrderId, CUstomerid,OrderDate,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS ROW_NUMBER
    , Rank() 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 -- 20 részre osztja, visszakapott halmazt
FROM Sales.Orders;

SELECT OrderId, 
StockItemID,
Quantity,
UnitPrice, 
(UnitPrice*Quantity) AS OrderLine_SUM,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS orderline_number,
SUM(UnitPrice*Quantity) OVER (PARTITION BY OrderID Order BY OrderLineID) as running_total
 FROM Sales.OrderLines;

 SELECT OrderId, 
StockItemID,
Quantity,
UnitPrice, 
OrderLineID,
(UnitPrice*Quantity) AS OrderLine_SUM,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS orderline_number,
SUM(UnitPrice*Quantity) OVER (PARTITION BY OrderID Order BY OrderLineID DESC) as running_total
 FROM Sales.OrderLines;

SELECT CityID, CityName, LatestRecordedPopulation AS population,
       (SELECT SUM(LatestRecordedPopulation) 
        FROM Application.Cities c2 
        WHERE c2.StateProvinceID = c1.StateProvinceID AND c2.CityID <= c1.CityID) AS run_total
FROM Application.Cities c1
WHERE StateProvinceID = 6
ORDER BY CityID;
/* windows function version */
 SELECT CityID, CityName, LatestRecordedPopulation,
 SUM(LatestRecordedPopulation) OVER(ORDER BY CityID) as run_total
FROM Application.Cities
WHERE StateProvinceID = 6

/* next cityID */
SELECT CityID, CityName, (SELECT MIN(CityID) FROM Application.Cities c2 WHERE c2.CityID > c1.CityID) AS nextcityid
FROM Application.Cities c1
ORDER by CityID ASC;
/* windows function order */
SELECT CityID, CityName, LEAD(CityID) OVER(ORDER BY CityID) AS nextcityid
FROM Application.Cities
ORDER by CityID ASC;

SELECT CityID, CityName, LAG(CityID) OVER(ORDER BY CityID) AS nextcityid
FROM Application.Cities
ORDER by CityID ASC;

ALTER DATABASE WideWorldImporters
SET Compatibility_Level = 150;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET Legacy_Cardinality_Estimation = OFF

--------------
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION' ;
-----------
SELECT *
FROM sys.databases
WHERE name = 'WideWorldImporters';

/* EXERCISE 1
Design a small subset of an imaginary Books database.

In the Books database, you want to store data about the following at least:
    - books
    - writers
    - the relationship between books and writers as needed

Each writer can have multiple books, but a book can only have one writer.

The following data must be stored at minimum:
    - Title of the book
    - Length of the book in pages
    - Publication date of the book
    - Writer of the book
    - The name of the genre (only one per book: Sci-fi, Action, Horror etc.)

Create the relevant tables with the required data columns and the appropriate data types.

Create the following constraints at minimum:
    - PRIMARY KEY
    - FOREIGN KEY

Make sure there is no books with the same title.
Specify additional constraints if necessary.

Script out the tables.

how many tables are needed ? books,authors, genre
table relationship ? 1->N (books,authors), 1->N (books,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
    ,author_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 
    ,author_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 books.authors
VALUES ('Robert Ludlum'),('Jack Higgins');

INSERT INTO books.genres 
VALUES ('spy thriller'),('action');

INSERT INTO books.books 
VALUES ('Chancellor Manuscript',700,'19760101',1,1);

INSERT INTO books.books 
VALUES ('Angel of Death',300,'19940302',2,2);

SELECT * FROM books.authors ba
INNER JOIN books.books bb ON ba.id=bb.author_id
INNER JOIN books.genres bg ON bg.id=bb.genre_id;

*/

/* EXERCISE 2
-- how many tables should be used ? 1

-- More "result set" should be combined ? (subquery/set operator/CTE/etc...) ANTI SEMI JOIN (WHERE NOT EXISTS/EXCEPT)

-- which aggregation should be used ? NOPE

-- which join should be used ? NOPE

-- which column should be used ? rename ? NOPE

-- where should be used the filter(s) ? / WHERE/HAVING, SELECT/CASE/ WHERE

-- "ORDER BY" should be used ? NOPE

Return the stock item names and their recommended retail prices
where the unit price is greater than 20 and lower than 100,
but exclude "Limited Stock" and empty tags.

Use the appropriate SET operator.

Use the following table:
    - Warehouse.StockItems

    
The report should display the following columns:
    -	StockItemName
    -	RecommendedRetailPrice

*/

/* version 1 */

SELECT StockItemName,RecommendedRetailPrice FROM Warehouse.StockItems
WHERE UnitPrice > 20 AND UnitPrice < 100
EXCEPT
SELECT StockItemName,RecommendedRetailPrice FROM Warehouse.StockItems
WHERE Tags IN ('["Limited Stock"]','[]')

/* version 2 */

SELECT StockItemName,RecommendedRetailPrice FROM Warehouse.StockItems
WHERE UnitPrice > 20 AND UnitPrice < 100 AND Tags IS NOT NULL AND Tags != '["Limited Stock"]' AND Tags != '[]'

/* version 3 */

SELECT StockItemName, RecommendedRetailPrice
FROM Warehouse.StockItems s1
WHERE UnitPrice > 20 AND UnitPrice < 100
EXCEPT
SELECT StockItemName, RecommendedRetailPrice
FROM Warehouse.StockItems
WHERE Tags = '[]' OR Tags LIKE '%Limited Stock%'

/* version 4 */

SELECT StockItemName,RecommendedRetailPrice FROM Warehouse.StockItems wso
WHERE UnitPrice > 20 AND UnitPrice < 100 AND NOT EXISTS (
    SELECT StockItemID FROM Warehouse.StockItems wsi
    WHERE Tags IN ('["Limited Stock"]','[]') AND
    wso.StockItemID=wsi.StockItemID
);
/* version 5 */
SELECT StockItemName, RecommendedRetailPrice
FROM Warehouse.StockItems s1
WHERE UnitPrice > 20 AND UnitPrice < 100
AND NOT EXISTS (SELECT StockItemID
                FROM Warehouse.StockItems s2 WHERE s1.StockItemID=s2.StockItemID AND (Tags = '[]' OR Tags LIKE '%Limited Stock%'));


/* EXERCISE 3
-- how many tables should be used ?  2

-- More "result set" should be combined ? (subquery/set operator/CTE/etc...)  ?

-- which aggregation should be used ? NOPE

-- which join should be used ? CROSS

-- which column should be used ? rename ? 

-- where should be used the filter(s) ? / WHERE/HAVING, SELECT/CASE/  WHERE USB*

-- "ORDER BY" should be used ? Stockitemname DESC

In the future we would like to sell all USB drives in all color.

Create a report of all Colors combined with all USB. Every USB's name starts with USB...

Order the report by the Name of Stock Items in descending order.

Use the following tables:
    - Warehouse.Colors
    - Warehouse.StockItems

The report should display the following columns:
    [StockItemName]: StockItemName from the Warehouse.StockItems table
    [ColorName] : ColorName from the Warehouse.Colors table

*/

SELECT StockItemName,Colorname FROM Warehouse.StockItems
CROSS JOIN Warehouse.Colors
WHERE StockItemName LIKE 'USB%'
ORDER BY Stockitemname DESC;

/* EXERCISE 4
-- how many tables should be used ?  2

-- More "result set" should be combined ? (subquery/set operator/CTE/etc...)  ?

-- which aggregation should be used ? MIN

-- which join should be used ? INNER

-- which column should be used ? rename ?  [First order date]

-- where should be used the filter(s) ? / WHERE/HAVING, SELECT/CASE/   SELECT-CASE

-- "ORDER BY" should be used ? Orderdate DESC

Create a report where we can see each customers first order date. 
If it was in 2016 use the 'Month: 1, Day: 13' format.
If it was before 2016 use the 'Date: 2013-03-04' format.

Order the report by the dates in descending order.

Use the following tables:
    - Sales.Customers
    - Sales.Orders

The report should display the following columns:
    [CustomerName]: CustomerName from the Sales.Customers table
    [First order date] : Oldest orderdate by customer from the Sales.Orders table in the specified format

*/

/* version 1 */

SELECT CustomerName,
    CASE  
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Month: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Day: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN 'Date : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [First order date]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY sc.CustomerID,CustomerName
ORDER BY [First order date] DESC;

/* version 2 */

SELECT CustomerName,
    CASE  
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Month: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Day: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN 'Date : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [First order date]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY CustomerName
ORDER BY [First order date] DESC;


/* version 3 */

SELECT CustomerName,
    CASE  
        WHEN 2016 = YEAR(MIN(OrderDate)) THEN 'Month: '+CAST(MONTH(MIN(OrderDate)) AS varchar(2))+', Day: '+CAST(DAY(MIN(OrderDate)) AS varchar(2))
        WHEN 2016 > YEAR(MIN(OrderDate)) THEN 'Date : '+CAST(MIN(OrderDate) AS varchar(10))
    END AS [First order date]
FROM Sales.Customers sc
INNER JOIN Sales.Orders so ON sc.CustomerID=so.CustomerID
GROUP BY CustomerName
ORDER BY MIN(OrderDate) DESC;

/* version 4 */


WITH FirstOrder AS (
    SELECT CustomerID, MIN(OrderDate) AS MinOrderDate
    FROM Sales.Orders
    GROUP BY CustomerID
)
SELECT CustomerName
, (CASE
WHEN fo.MinOrderDate >= '20160101' AND fo.MinOrderDate < '20170101' THEN 'Month: ' + CAST(MONTH(fo.MinOrderDate) AS varchar(2)) + ', Day: ' + CAST(DAY(fo.MinOrderDate) AS varchar(2))
WHEN fo.MinOrderDate <'20160101' THEN 'DATE: ' + CAST(fo.MinOrderDate AS varchar)
END) AS 'First order date'
FROM Sales.Customers c
INNER JOIN FirstOrder fo ON c.CustomerID=fo.CustomerID 
ORDER BY [First Order Date] DESC;

/* version 5 */

SELECT CustomerName,
    CASE
    WHEN (OrderDate BETWEEN '2016-01-01' AND '2016-12-31') THEN
        'Month: ' + CAST(MONTH(OrderDate) AS varchar(2)) +
        ', Day: ' + CAST(DAY(OrderDate) AS varchar(2))
    ELSE
        'Date: ' + CAST(OrderDate AS varchar(10))
    END AS 'First order date'
FROM (
    SELECT CustomerID, MIN(OrderDate) AS 'OrderDate'
    FROM Sales.Orders
    GROUP BY CustomerID) AS first_orders
INNER JOIN Sales.Customers c
    ON first_orders.CustomerID = c.CustomerID
ORDER BY
    OrderDate DESC
;

/* EXERCISE 5
-- how many tables should be used ?   2

-- More "result set" should be combined ? (subquery/set operator/CTE/etc...)  ? NOPE

-- which aggregation should be used ? COUNT

-- which join should be used ?  INNER

-- which column should be used ? rename ? YES

-- where should be used the filter(s) ? / WHERE/HAVING, SELECT/CASE/   CASE

-- "ORDER BY" should be used ? NOPE

Write a view in the Sales schema named PickupReport.

It should use schemabinding.

Use it to return the picked up and not picked up orders by PostalCityID 
arrange by the not taken deliveries descending.

Use the following tables:
    - Sales.Orders
    - Sales.Customers

    
The report should display the following columns:
    [Postal ID]: PostalCityID column from Sales.Customers
    [Picked up] : number of orders already picked up
	[Not picked up]: number of orders not yet picked up  
*/
GO
CREATE OR ALTER VIEW Sales.PickupReport
WITH SCHEMABINDING
AS 
SELECT PostalCityID,
    COUNT(CASE 
        WHEN PickingCompletedWhen IS NOT NULL THEN 1
    END) AS [Picked up]
   ,COUNT(CASE 
        WHEN PickingCompletedWhen IS NULL THEN 0
    END) AS [Not picked up]
FROM Sales.Customers sc 
INNER JOIN Sales.Orders so 
ON sc.CustomerID=so.CustomerID
GROUP BY PostalCityID;
GO

/* version 2 */
CREATE OR ALTER VIEW Sales.PickupReport
WITH SCHEMABINDING
AS 
SELECT c.PostalCityID AS [Postal ID]
		, COUNT(o.PickingCompletedWhen) AS [Picked up]
		, COUNT(*)-COUNT(o.PickingCompletedWhen) AS [Not picked up]
FROM Sales.Customers AS c
INNER JOIN  Sales.Orders AS o
ON c.CustomerID=o.CustomerID
GROUP BY c.PostalCityID;
GO
/*  SHOWCASE */

SELECT * FROM Sales.PickupReport
ORDER BY [Not picked up] DESC;

/* EXERCISE 6
-- how many tables should be used ?  2

-- More "result set" should be combined ? (subquery/set operator/CTE/etc...)  ? ???

-- which aggregation should be used ? COUNT/SUM

-- which join should be used ?  LEFT

-- which column should be used ? rename ? YES

-- where should be used the filter(s) ? / WHERE/HAVING, SELECT/CASE/  FORMAT 

-- "ORDER BY" should be used ? NOPE 

Write a view in the Sales schema named SupplierReport.

It should use schemabinding.

Show suppliers and the ratio of their products to total products sold. 
If a supplier has no item, it should show 0.00

Use the following tables:
    - Purchasing.Suppliers
    - Warehouse.StockItems

    
The report should display the following columns:
    [Supplier]: The name of the supplier from Purchasing.Suppliers
    [Total percentage] : Two digit percentage of the items sold by the supplier 
        compared to all the items sold by all the suppliers


*/
GO
CREATE OR ALTER VIEW Sales.SupplierReport
WITH SCHEMABINDING
AS 
   SELECT 
     SupplierName AS [Supplier]
    ,FORMAT(COUNT(ws.StockItemID)/(SUM(COUNT(ws.StockItemID)) OVER() *1.0),'P2') AS  [Total percentage]
    FROM Purchasing.Suppliers ps
    LEFT JOIN Warehouse.StockItems ws
    ON ps.SupplierID=ws.SupplierID
    GROUP BY SupplierName
GO

SELECT 
     SupplierName
    ,FORMAT(COUNT(ws.StockItemID)/(maxstockitem*1.0),'P2') AS  [Total percentage]
FROM Purchasing.Suppliers ps
    CROSS APPLY (SELECT COUNT(*) AS maxstockitem FROM Warehouse.StockItems) AS temptable
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName,maxstockitem

 -- version 2 

SELECT 
     SupplierName
    ,FORMAT(COUNT(ws.StockItemID)/((SELECT COUNT(StockItemID) FROM Warehouse.StockItems)*1.0),'P2') AS  [Total percentage]
FROM Purchasing.Suppliers ps
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName

-- version 3 
SELECT 
     SupplierName
    ,COUNT(ws.StockItemID)
    ,FORMAT(COUNT(ws.StockItemID)/(SUM(COUNT(ws.StockItemID)) OVER() *1.0),'P2') AS  [Total percentage]
FROM Purchasing.Suppliers ps
LEFT JOIN Warehouse.StockItems ws
ON ps.SupplierID=ws.SupplierID
GROUP BY SupplierName

/* EXERCISE 7
-- how many tables should be used ?  2

-- More "result set" should be combined ? (subquery/set operator/CTE/etc...)  ? 

-- which aggregation should be used ? 

-- which join should be used ?  

-- which column should be used ? rename ?

-- where should be used the filter(s) ? / WHERE/HAVING, SELECT/CASE/  

-- "ORDER BY" should be used ? 

Create an inline table-valued function in the Warehouse schema named StockUsbReport 
which returns all the USB related records.

It should use one parameter:
    - @stockitemname of nvarchar(10)

Return the stock item, it's retail price and an overall stock price 
based on the unit price and it's quantity based on the parameter value.

Use the following tables:
    - Warehouse.StockItemHoldings
    - Warehouse.StockItems

The report should display the following columns:
    [Stock Item Name] : StockItemName column from Warehouse.StockItems
    [Retail Price]: RecommendedRetailPrice column from Warehouse.StockItems
    [Current Stock Value]: Multiply the unit price with the quantity of the items
        The values should look like the following: $1,234,567

Showcase how you use the table-valued function!

*/
GO
CREATE OR ALTER FUNCTION Warehouse.StockUsbReport( @stockitemname nvarchar(10))
RETURNS TABLE
AS 
RETURN (
    SELECT 
         StockItemName AS [Stock Item Name]
        ,RecommendedRetailPrice AS [Retail Price]
        ,FORMAT((QuantityOnHand*UnitPrice),'C0','en-US') AS [Current Stock Value]
    FROM Warehouse.StockItemHoldings wsh
    INNER JOIN Warehouse.StockItems wsi
    ON wsh.StockItemID=wsi.StockItemID
    WHERE StockItemName LIKE '%'+@stockitemname+'%'
    )
GO

/* SHOWCASE */

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

----
SELECT 
     StockItemName
    ,RecommendedRetailPrice
    ,FORMAT((QuantityOnHand*UnitPrice),'C0','en-US') FROM Warehouse.StockItemHoldings wsh
INNER JOIN Warehouse.StockItems wsi
ON wsh.StockItemID=wsi.StockItemID
WHERE StockItemName LIKE '%'+'USB'+'%'

/* version 2 */

SELECT 
     StockItemName
    ,RecommendedRetailPrice
    ,FORMAT((QuantityOnHand*UnitPrice),'$###,###,###') FROM Warehouse.StockItemHoldings wsh
INNER JOIN Warehouse.StockItems wsi
ON wsh.StockItemID=wsi.StockItemID



/* EXERCISE 8 */
----------------------------------------------------------------------------------------------
 DROP TABLE IF EXISTS Application.Fruits;
    GO
    CREATE TABLE Application.Fruits
    (
        FruitId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
        FruitName nvarchar(50) NOT NULL
    );

    INSERT INTO Application.Fruits (FruitName) 
        VALUES ('banana'),('banana'),('apple'),('peach'),('peach'),('peach');
/*
Create a stored procedure in the Application schema named DeleteFruit.

The stored procedure deletes records from the Application.Fruits table.

The stored procedure should accept the mandatory columns as parameters:
    - @FruitName nvarchar(50)

The stored procedure should output on success:
	-@FruitsDeleted int

Use error handling and error handling functions to display the error number,
the error message and the Error severity!
Also print out a message 'Delete fruit failed!'

If the process went without an error, but the deleted rows were 0, 
raise an error with the following message:
'No such fruit found' and with severity level 16.

Showcase how you use the stored procedure!
Don't show the number of affected rows in the T-SQL query as informational message.
*/
GO
CREATE OR ALTER PROCEDURE Application.DeleteFruit (
     @FruitName NVARCHAR(50)
    ,@FruitsDeleted INT OUTPUT
    )
AS 
    SET NOCOUNT ON;
    BEGIN TRY
        DELETE Application.Fruits
        WHERE FruitName=@FruitName;
        SET @FruitsDeleted= @@ROWCOUNT;
        IF @FruitsDeleted = 0 
            BEGIN 
                RAISERROR('No such fruit found',16,7);
            END
    END TRY
    BEGIN CATCH
        PRINT 'Delete fruit failed!';
        PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10));
        PRINT 'Error message: ' + ERROR_MESSAGE();
        PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10));
    END CATCH
GO

/* version 2 */

GO
CREATE OR ALTER PROCEDURE Application.DeleteFruit (
    @FruitName nvarchar(50),
    @FruitsDeleted int OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        DELETE FROM Application.Fruits WHERE FruitName = @FruitName;
        SET @FruitsDeleted = @@ROWCOUNT;
        IF @FruitsDeleted = 0
        BEGIN
            RAISERROR('No such fruit found', 16, 1);
        END
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorNumber int = ERROR_NUMBER();
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        RAISERROR('Delete fruit failed! Error number: %d, Error message: %s, Error severity: %d', 16, 1, @ErrorNumber, @ErrorMessage, @ErrorSeverity);
        ROLLBACK TRANSACTION;
    END CATCH
END;
GO


/* SHOWCASE */

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

/*
Exercise #1:
Design a small subset of an imaginary Restaurant database.

In the Restaurant database, you want to store data about the following at least:

Franchise
Store
The relationship between franchises and stores
Multiple stores can belong to a franchise.A store can have only one type of franchise.

The following data must be stored at minimum:

Business name (plan with storing non-English characters too)
Type of the franchise (plan with storing brief descriptions here - e.g. Product, Manufacturing)
Store address
Store opening date
If a store is 24/7 or not (true or false)
Create the relevant tables with the required data columns and the appropriate data types.

Create the following constraints at minimum:

PRIMARY KEY
FOREIGN KEY
Specify additional constraints if necessary.

Script out the tables.
*/
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,
    BusinessName 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)
);

/*
Exercise #2
Create a report of all the suppliers, their number of transactions and comments.

Return all suppliers regardless how many transactions they have. (zero or more)

Group the results by the supplier name and their comments.

Order the report by the Number of transactions in descending order.

Use the following tables:

Purchasing.Suppliers
Purchasing.SupplierTransactions
The report should display the following columns:

[Supplier name]: SupplierName from the Purchasing.Suppliers table
[Number of transactions]: the number of transactions each supplier has
Not all suppliers have successful transactions. If a supplier has no successful transaction, display 0 (zero)
[Notes]: supplier internal comments / InternalComments/
Not all suppliers have comments. If a supplier has no comment, display 'N/A'
*/

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

SELECT * FROM Purchasing.Suppliers
SELECT * FROM Purchasing.SupplierTransactions;

SELECT 
    s.SupplierName AS [Supplier name], st.SupplierTransactionID
FROM 
    Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID


SELECT 
    s.SupplierName AS [Supplier name], COUNT(st.SupplierTransactionID)
FROM 
    Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY 
    s.SupplierName

SELECT s.SupplierName AS [Supplier name],
    ISNULL(COUNT(st.FinalizationDate), 0) AS [Number of transactions],
    ISNULL(s.InternalComments, 'N/A') AS [Notes]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [Number of transactions] DESC;

SELECT s.SupplierName AS [Supplier name],
    COUNT(st.SupplierTransactionID) AS [Number of transactions],
    ISNULL(s.InternalComments, 'N/A') AS [Notes]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [Number of transactions] DESC;
-----

USE WideWorldImporters;
GO
SELECT s.SupplierName AS [Supplier name],
    ISNULL(COUNT(st.SupplierTransactionID), 0) AS [Number of transactions],
    ISNULL(s.InternalComments, 'N/A') AS [Notes]
FROM Purchasing.Suppliers s
    LEFT JOIN Purchasing.SupplierTransactions st ON s.SupplierID = st.SupplierID
GROUP BY s.SupplierName, s.InternalComments
ORDER BY [Number of transactions] DESC;


/*
Exercise #3
Create a report of supplier references / SupplierReference /, their expected overall outer prices (unit prices multiplied by the ordered outers /OrderedOuters*ExpectedUnitPricePerOuter/ ) 
and the total number of ordered outers / OrderedOuters / by each "supplier reference".

Return only those suppliers where the expected delivery date is greater than 2014-03-31.

Group the results by the supplier references.

Order the report by [Supplier reference] in ascending order.

Return the price using any type of US currency formatting.

Use the following tables:

Purchasing.PurchaseOrderLines
Purchasing.PurchaseOrders
The report should display the following columns:

[Supplier reference] -> from Purchasing.PurchaseOrders table
[Expected overall outer price] -> the expected price by outers
[Ordered outers] -> the total number of ordered outers
*/

SELECT * FROM Purchasing.PurchaseOrderLines
SELECT * FROM Purchasing.PurchaseOrders;

USE WideWorldImporters;
GO
SELECT 
    po.SupplierReference AS [Supplier reference],
    FORMAT(SUM(pol.OrderedOuters * pol.ExpectedUnitPricePerOuter), 'C') AS [Expected overall outer price],
    SUM(pol.OrderedOuters) AS [Ordered outers]
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 
    [Supplier reference] ASC;


/*
Exercise #4
Create a report of each customer's latest order and show when it was compared to a point in time.

First declare a variable that holds a date value and set the current date to it.

Then declare an other variable that holds a string and set the value to '%Toys%' .

Your report should not contain any Customer name with the created string variable's value in it.

Use the following tables:

Sales.Orders
Sales.Customers
Order the report in [Customer Name] descending order.

The report should display the following columns:

[Customer Name]: CustomerName column from the Sales.Customers table
[Days passed] : The difference in days between the created date variable and the latest Orderdate from the Sales.Orders
*/
USE WideWorldImporters;
GO

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

SELECT 
    c.CustomerName AS [Customer Name],
    DATEDIFF(DAY, o.OrderDate, @currentdate) AS [Days passed]
FROM 
    Sales.Orders o
    INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE 
    c.CustomerName NOT LIKE @excludeString
    AND o.OrderDate = (
        SELECT MAX(OrderDate) 
        FROM Sales.Orders 
        WHERE CustomerID = o.CustomerID
    )
ORDER BY 
    [Customer Name] DESC;

/*
Exercise #5:
Create a scalar user-defined function with schemabinding in the Sales schema named SmallestOrderQuantity.

Return the smallest Quantity that belongs to an Order. The OrderID is passed as a parameter.

The function should return with an Int type value (Quantity).

Use the following tables:

Sales.OrderLines
The function should accept one parameter:

@orderid int
Showcase how you use the scalar user-defined function in a query!
*/
SELECT TOP 1 Quantity
    FROM Sales.OrderLines
    WHERE OrderID = 1
    ORDER BY QUANTITY DESC;

SELECT MIN(Quantity)
    FROM Sales.OrderLines
    WHERE OrderID = 1

USE WideWorldImporters;
GO
CREATE OR ALTER FUNCTION Sales.SmallestOrderQuantity(@orderid int)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @quantity int;
    SELECT @quantity = MIN(Quantity)
    FROM Sales.OrderLines
    WHERE OrderID = @orderid;
    RETURN @quantity;
END;
GO


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

/*
Exercise #6:
Create a view with schemabinding in the Sales schema named CustomerYearlyOrder.

Return the Customers and their associated yearly order numbers along with the order success.

An order is successful when it's picking completed.

Group them by the customer name and the year of the order.

Use the following tables:

Sales.Customers
Sales.Orders
The report should display the following columns:

[Customer Name] : Customer Name from Sales.Customers
[Order Year]: The year of the order's date from Sales.Orders
[Orders]: The number of the orders from Sales.Orders
[Picking Completed]: The number of the orders picked up from Sales.Orders / PickingCompletedWhen /
[Difference]: The difference between the Orders and the Picking Completed columns
Showcase how you use the view in a query!
*/
GO
CREATE OR ALTER VIEW Sales.CustomerYearlyOrder
WITH SCHEMABINDING
AS
SELECT c.CustomerName AS [Customer Name],
       YEAR(o.OrderDate) AS [Order Year],
       COUNT(*) AS [Orders],
       COUNT(o.PickingCompletedWhen) AS [Picking Completed],
       COUNT(*) - COUNT(*)  AS [Difference]
FROM Sales.Customers c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, YEAR(o.OrderDate);
GO
SELECT *
FROM Sales.CustomerYearlyOrder


USE WideWorldImporters;
GO
CREATE OR ALTER VIEW Sales.CustomerYearlyOrder
WITH SCHEMABINDING
AS
SELECT c.CustomerName AS [Customer Name],
       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 [Picking Completed],
       COUNT(o.OrderID) - SUM(CASE WHEN o.PickingCompletedWhen IS NOT NULL THEN 1 ELSE 0 END) AS [Difference]
FROM Sales.Customers c
JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, YEAR(o.OrderDate);
GO
--SHOWCASE
SELECT *
FROM Sales.CustomerYearlyOrder;

/*
Exercise #7:
Create an inline table-valued function in the Application schema named CityReport.

It should use two parameters:

@salesterritory of nvarchar(50), with a default value of 'New England'
@bigcitylimit of decimal(18,2), with a default value of 100000.00
Return the Sales Territories and the Cities that belong to them along with the city populations, but only for those Cities where the city populations are known (not missing)!

Use the following tables:

Application.StateProvinces
Application.Cities
The report should display the following columns:

[Sales Territory]: SalesTerritory column from Application.StateProvinces
[State] : StateProvinceName column from Application.StateProvinces
[City]: CityName column from Application.Cities
[City Population]: LatestRecordedPopulation column from Application.Cities
[Big City Percentage]: City LatestRecordedPopulation percentage of @bigcitylimit parameter value Display the percentage value with a precision of 2 digits
Showcase how you use the table-valued function!
*/
SELECT * FROM Application.StateProvinces
SELECT * FROM Application.Cities

SELECT sp.SalesTerritory AS [Sales Territory],
        sp.StateProvinceName AS [State],
        c.CityName AS [City],
        c.LatestRecordedPopulation AS [City Population],
        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 decimal(18,2) = 100000.00)
RETURNS TABLE
AS
RETURN
(
    SELECT sp.SalesTerritory AS [Sales Territory],
        sp.StateProvinceName AS [State],
        c.CityName AS [City],
        c.LatestRecordedPopulation AS [City Population],
        CAST((c.LatestRecordedPopulation / @bigcitylimit * 100) AS decimal(18,2)) AS [Big City Percentage]
    FROM Application.StateProvinces sp
    INNER JOIN Application.Cities 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);


/*
Exercise #8:
Create the following table:

DROP TABLE IF EXISTS Application.LogAudit;
GO
CREATE TABLE Application.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);
Insert records into this table with INSERT statements:

INSERT INTO Application.LogAudit (LogData) VALUES ('This is a log data!');
Create a stored procedure in the Application schema named DeleteLogData.

The stored procedure deletes a record from the Application.LogAudit table based on LogAuditId that is passed as a parameter.

The stored procedure should accept the mandatory columns as parameters:

@logauditid int
It should also accept an OUTPUT parameter:

@deletedlogdata nvarchar(50)
Use error handling and error handling functions to display the error number and the error message!

If the delete is successful, return the deleted LogData value in the OUTPUT parameter!

If an error occurs or the deleted rows were 0, handle the error:

print: 'Delete of log audit failed!'
print: 'Error number: ' and the error number
print: 'Error message: ' and the error message
return -1 as a return value
Don't show the number of affected rows in the T-SQL query as informational message.

Showcase how you use the stored procedure!
*/

DELETE FROM Application.LogAudit 
        WHERE LogAuditId = 10;

SELECT * FROM Application.LogAudit

 SELECT LogData 
        FROM Application.LogAudit 
        WHERE LogAuditId = 3


DECLARE @deletedlogdata nvarchar(50);
SELECT @deletedlogdata = LogData 
        FROM Application.LogAudit 
        WHERE LogAuditId = 2
SELECT @deletedlogdata

USE WideWorldImporters;
GO

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

INSERT INTO Application.LogAudit (LogData) VALUES ('This is a log data!');


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

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

        DELETE FROM Application.LogAudit 
        WHERE LogAuditId = @logauditid;
        IF @@ROWCOUNT = 0
        BEGIN
            --THROW 55555, 'ROWCOUNT=0', 1;
            RAISERROR ('ROWCOUNT=0', 16, 1);
        END
    END TRY
    BEGIN CATCH
        PRINT 'Delete of log audit failed!';
        PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
        PRINT 'Error message: ' + ERROR_MESSAGE();
        SET @deletedlogdata = NULL;
        RETURN -1;
    END CATCH
END;
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

CREATE DATABASE Homedelivery;
GO
-- boltok, katalógusok, szállítók
-- bolt->katalógus 1:N, bolt-> szállítók 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)
	,opening_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
	,catalog_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 (last_success_transport >catalog_success_created )
);

/*
INSERT INTO dbo.transporters
VALUES ('corleone family'),('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.catalogs
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);
*/

USE WideWorldImporters;
GO
CREATE OR ALTER PROCEDURE Application.GetContinentRanking 
(@rank TINYINT=1)
AS
	SET NOCOUNT ON;
	SELECT @rank AS ranking
		,Continent AS [continent name]
		,SUM(LatestRecordedPopulation) population 
	FROM Application.Countries
	GROUP BY Continent
	ORDER BY SUM(LatestRecordedPopulation) DESC
	OFFSET (@rank-1) ROWS FETCH NEXT 1 ROWS ONLY;
GO

/*
A tárolt eljárás egy paramétert fogad: - @rank tinyint: alapértelmezett értéke 1
A tárolt eljárás az alábbi oszlopokkal térjen vissza:
    [ranking]: a ranglista helyezés értéke
    [continent name]: Continent oszlop az Application.Countries táblában
    [population]: összegzett LatestRecordedPopulation oszlop az Application.Countries táblában
*/

/*
Mutasd be, hogyan használod a tárolt eljárást!
*/

EXEC Application.GetContinentRanking DEFAULT;

EXEC Application.GetContinentRanking 3;

-- window function version
GO
CREATE OR ALTER PROC Application.GetContinentRanking
    @rank TINYINT = 1
AS
BEGIN
    SELECT ranking, continent_name, population
    FROM (
        SELECT
            RANK() OVER (ORDER BY SUM(LatestRecordedPopulation) DESC) ranking,
            Continent AS 'continent_name',
            SUM(LatestRecordedPopulation) AS 'population'
        FROM Application.Countries
        GROUP BY Continent) AS continent_populations
    WHERE ranking = @rank
END;
GO

EXEC Application.GetContinentRanking DEFAULT;

EXEC Application.GetContinentRanking 3;

USE  WideWorldImporters;
GO
CREATE LOGIN [SampleLogin] WITH PASSWORD=N'jelszó', 
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 [External Sales];
GO
EXECUTE AS USER='SampleUser';
GO
SELECT TOP 5 * FROM Application.People;
--SELECT USER_NAME();
GO
REVERT
GO
SELECT USER_NAME();

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. ----------- baloldali session:
BEGIN TRAN t1;
UPDATE dbo.ExamLefty SET Numbers = Numbers + 1;
--- 4. ----------- baloldali session:
SELECT * FROM dbo.ExamRighty;

ROLLBACK TRAN

------------------------------------ JOBB
--- 2. -- jobb oldali session:
BEGIN TRAN t2; 
UPDATE dbo.ExamRighty SET Numbers = Numbers + 1;
--- 3. -- jobb oldali session:
SELECT * FROM dbo.ExamLefty;

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:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Diff.bak' 
WITH  DIFFERENTIAL, COMPRESSION;
GO
BACKUP LOG [SQLTestDB] 
TO  DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Log.trn' 
WITH COMPRESSION, CHECKSUM;
GO

RESTORE VERIFYONLY 
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Log.trn';
GO
RESTORE VERIFYONLY 
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Diff.bak';
GO
RESTORE VERIFYONLY 
FROM DISK = N'S:\instance\MSSQL15.GFOX\MSSQL\Backup\SQLTestDB_Full.bak';
GO
-- ole 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
@Databases = ''SQLTestDB'',
@Directory = ''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, --sunday
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=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 @schedule_id
GO

-- DIFF BACKUP JOB

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
@Databases = ''SQLTestDB'',
@Directory = ''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_relative_interval=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 @schedule_id
GO

-- LOG BACKUP JOB

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
@Databases = ''SQLTestDB'',
@Directory = ''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, -- minutes
		@freq_subday_interval=5, -- five ...
		@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 @schedule_id
GO

USE master;
GO
-- restore list
RESTORE FILELISTONLY FROM  DISK = N'Y:\x\SQLRestoreDB_Full.bak';
GO
/*
RESTORE HEADERONLY FROM  DISK = N'Y:\x\SQLRestoreDB_Full.bak';
GO
*/
-- restore full backupv
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 backup
RESTORE DATABASE [SQLRestoreDB]  FROM  DISK = N'Y:\x\SQLRestoreDB_Diff_3.bak' WITH NORECOVERY;
-- log backup
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;

USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Application.LogAudit;
GO
CREATE TABLE Application.LogAudit
(
    LogAuditId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    LogData nvarchar(50) NOT NULL
);
INSERT INTO Application.LogAudit (LogData) VALUES ('This is a log data!');
GO
SELECT * FROM Application.LogAudit;
GO
CREATE OR ALTER PROC Application.DeleteLogData (
	 @logauditid int
	,@deletedlogdata nvarchar(50) OUTPUT
)
AS
	SET NOCOUNT ON;
	BEGIN TRY
		DECLARE @Mytemptable TABLE (
			logdatas NVARCHAR(50)
		);
		DELETE Application.LogAudit
		OUTPUT deleted.LogData INTO @Mytemptable
		WHERE LogAuditId=@logauditid;

		IF @@ROWCOUNT <> 1
			BEGIN	
				RAISERROR ('Delete of log audit failed!',16,1);
			END;

		SELECT @deletedlogdata = logdatas FROM @Mytemptable;
	END TRY
	BEGIN CATCH
		PRINT  'Delete of log audit failed!';
		PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS nvarchar(10));
		PRINT 'Error message: ' + ERROR_MESSAGE();
		RETURN -1;
	END CATCH
GO

-- showcase

DECLARE @outputvar NVARCHAR(50),@returnstatus INT;
EXEC @returnstatus=Application.DeleteLogData 1, @deletedlogdata = @outputvar OUTPUT;
SELECT @outputvar AS [Deleted log value],@returnstatus AS [return status];
GO
-- showcase error
DECLARE @outputvar NVARCHAR(50),@returnstatus INT;
EXEC @returnstatus=Application.DeleteLogData 1, @deletedlogdata = @outputvar OUTPUT;
SELECT @outputvar AS [Deleted log value],@returnstatus AS [return status];
GO

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 IDENTITY(1,1),
 Name NVARCHAR(500) NOT NULL, 
 Email VARCHAR(255) NOT NULL, 
 Birthdate DATE NOT NULL, 
 YearOfEnrolment SMALLINT NOT NULL, 
 Class NVARCHAR(60) NOT NULL, 
 Comment NVARCHAR(5000));

CREATE TABLE dbo.Teachers (
 ID INT IDENTITY(1,1),
 Name NVARCHAR(500) NOT NULL, 
 Email VARCHAR(255) NOT NULL, 
 PhoneNumber VARCHAR(15) NOT NULL, 
Birthdate 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_Students_Subjects PRIMARY KEY (ID),
CONSTRAINT FK_Conn_Students_Subjects_SubID FOREIGN KEY (SubID) REFERENCES dbo.Subjects(ID),
CONSTRAINT FK_Conn_Students_Subjects_StudID FOREIGN KEY (StudID) REFERENCES dbo.Students(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 (PhoneNumber);
GO

USE WideWorldImporters;
GO
SELECT 
    c.CustomerID AS [Customer ID],
    c.CustomerName AS [Customer Name],
    c.CreditLimit AS [Credit Limit]
FROM 
    Sales.Customers c
WHERE 
    c.CustomerName LIKE 'Abel%'
    AND NOT EXISTS (
        SELECT 1
        FROM Sales.Orders o
        WHERE 
            o.CustomerID = c.CustomerID
            AND o.OrderDate > '2016-04-30'
            AND o.OrderDate < '2016-05-16'
    );

-- Létrehoz egy "NewExamLogin" nevű SQL login-t egy jelszóval és az ExamDB adatbázist jelöli meg mint alapértelmezett adatbázis
CREATE LOGIN NewExamLogin WITH PASSWORD = 'SomeVeryStrongPassword123!', DEFAULT_DATABASE = [ExamDB];

-- Az ExamDB adatbázisban létrehoz egy "NewExamUser" felhasználót az előbbi login-hoz
USE ExamDB;
CREATE USER NewExamUser FOR LOGIN NewExamLogin;

-- Létrehoz egy új adatbázis szerepet (role) az ExamDB adatbázisban, "Exam Admins" néven
CREATE ROLE [Exam Admins];

-- Létrehoz egy másik új adatbázis szerepet (role) az ExamDB adatbázisban, "Exam Contributors" néven
CREATE ROLE [Exam Contributors];

-- A "NewExamUser" felhasználót hozzáadja az "Exam Admins" adatbázis szerephez
ALTER ROLE [Exam Admins] ADD MEMBER NewExamUser;

-- Az Exam sémára EXECUTE jogot ad az "Exam Admins" adatbázis szerepnek
GRANT EXECUTE ON SCHEMA::Exam TO [Exam Admins];

-- Az Exam sémán explicit letiltja az EXECUTE jogot az "Exam Contributors" szerepnek
DENY EXECUTE ON SCHEMA::Exam TO [Exam Contributors];

-- Teszteli az Exam.Proc1 tárolt eljárás futtatását a "NewExamUser" felhasználó megszemélyesítésével
EXECUTE AS USER = 'NewExamUser';
EXEC Exam.Proc1;
REVERT;

-- Visszaállítja a végrehajtás kontextusát az előző felhasználóra
-- (A REVERT utasítás a korábbi lépésben már visszaállította a kontextust)

-- Lekérdezi a jelenlegi felhasználó nevét és a login nevét
SELECT CURRENT_USER AS [Current User], SUSER_NAME() AS [Login Name];

SELECT 
    DB.database_id,
    DB.name AS database_name,
    DB.recovery_model_desc,
    FM.file_id,
    FM.type_desc,
    FM.name AS file_name,
    FM.physical_name,
    (FM.size * 8.0 / 1024) AS file_size_MB,
    FM.is_percent_growth
FROM 
    sys.databases AS DB
JOIN 
    sys.master_files AS FM
ON 
    DB.database_id = FM.database_id;

-- Szerverszinten beállítja, hogy alapértelmezés szerint tömörített mentések készüljenek
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;
-- Az ExamDB adatbázisra (csak arra!) kikapcsolja a párhuzamosítást
USE [ExamDB]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
-- Visszaellenőrzi (lekérdezi) a tömörített mentésekkel kapcsolatos szerverszintű beállítást
EXEC sp_configure 'backup compression default';
-- Visszaellenőrzi (lekérdezi) az ExamDB adatbázisra hogy ki van-e kapcsolva a párhuzamosítás
SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';
-- Bekapcsolja az ExamDB adatbázison a Query Store-t, írható és olvasható üzemmódban, maximum 500MB-os mérettel, és úgy hogy a Query Store minden lekérdezést elkapjon
USE [master]
GO
ALTER DATABASE ExamDB
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 500,
    QUERY_CAPTURE_MODE = ALL
);

USE master;
GO
-- restore list
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:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\RestoreDB_Full.bak';
GO
*/
-- restore full backupv
RESTORE DATABASE [RestoreDB] 
FROM  DISK = N'C:\Program Files\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; -- én direkt mozgatom, hogy magamnak könnyebben ellenőrizhessem a mappát c-n létrehoztam
/*-- 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 backup
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;

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

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

ALTER DATABASE [ExamDB] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

USE WideWorldImporters;
GO
/*
SELECT * FROM Sales.Customers
SELECT * FROM Sales.Orders
SELECT * FROM Sales.OrderLines
*/
GO
CREATE OR ALTER PROCEDURE Sales.GetPickedByDate
    @startDate datetime = '2013-01-01',
    @endDate datetime = '2016-06-01'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        c.CustomerName AS [Customer Name],
        COALESCE(ol.PickingCompletedWhen, DATEADD(DAY, 3, GETDATE())) AS [Pick Date],
        SUM(ol.Quantity * ol.UnitPrice) AS [Overall Price],
        COUNT(ol.OrderLineID) AS [Number of Purchase],
        CASE 
            WHEN ol.PickingCompletedWhen IS NULL THEN 'Expected Pick Date'
            ELSE 'Already picked'
        END AS [Refreshed Date]
    FROM Sales.OrderLines ol
    JOIN Sales.Orders o ON ol.OrderID = o.OrderID
    JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
    WHERE c.BuyingGroupID IS NULL
        AND o.OrderDate >= @startDate
        AND o.OrderDate <= @endDate
    GROUP BY c.CustomerName, ol.PickingCompletedWhen
    HAVING SUM(ol.Quantity * ol.UnitPrice) > 20000
    ORDER BY c.CustomerName, ol.PickingCompletedWhen;
END;
GO
EXEC Sales.GetPickedByDate;
EXEC Sales.GetPickedByDate @startDate = '2014-01-01', @endDate = '2015-12-31';

SELECT * FROM sys.tables;

SELECT t.name AS [table_name], 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],
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;

-- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15
	--recovery models for dbs
	SELECT name, recovery_model_desc FROM sys.databases;

	-- all files in 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;

SELECT S.name,COUNT(SYS.INDEXES.name) FROM SYS.INDEXES
INNER JOIN SYS.OBJECTS S
ON S.OBJECT_ID=SYS.INDEXES.OBJECT_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.OBJECT_ID=SYS.INDEXES.OBJECT_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 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 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    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.database_id = DB_ID()

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

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 = 'Limited Stock'

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

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + 
@ColumnName 
+ ', 3630) FROM ' 
+ 
@TableName 
+ ' (NOLOCK) ' +
                ' WHERE ' 
+ 
@ColumnName 
+ ' LIKE '
+ 
@SearchStr2
                
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

8134 system error messages-ben > diagn. query
SELECT * FROM sys.messages
WHERE message_id=8134;
/* modify language in session */
SET LANGUAGE hungarian;
PRINT 10/0;
SET LANGUAGE english;
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.database_id) a
WHERE b.name = 'WideWorldImporters';

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

SELECT resource_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
    and resource_database_id = DB_ID();

SELECT CASE transaction_isolation_level 
    WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
-- use your session ID instead of 75
where session_id = 75;
------------------------------------------------------------
SELECT CASE transaction_isolation_level 
    WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

SELECT TOP (1000) [SZAMLAFEJ_ID]
      ,[VEVO_ID]
      ,[KELTE]
      ,[TELJESITES]
      ,[FIZHATARIDO]
      ,[NAVATKULDVE]
      ,[STATUS]
  FROM [CEGESADATOK].[szamlazas].[szamlafej]
  WHERE VEVO_ID Not IN (select vevo_id
						from szamlazas.vevo)

USE [CEGESADATOK]
GO

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

USE [CEGESADATOK]
GO

INSERT INTO [szamlazas].[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 [CEGESADATOK]
GO

INSERT INTO [szamlazas].[vevo]
           ([VEVONEV]
           ,[ADOSZAM]
           ,[CIM]
           ,[TELEFON]
           ,[EMAIL]
           ,[STATUS])
     VALUES
           ('Hetedik Bt.',
           '11223344-2-05',
           'Seholgy sincs',
           '',
            null,
           0)
GO

USE CEGESADATOK
Go

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


SELECT SZAMLAFEJ_ID
FROM szamlazas.szamlafej

DELETE FROM szamlazas.szamlatetel
WHERE SZAMLAFEJ_ID Not IN (SELECT SZAMLAFEJ_ID
							FROM szamlazas.szamlafej)
GO

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

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

SELECT AVG(EGYSEGAR)
  FROM [CEGESADATOK].[szamlazas].[ARU]


SELECT *
  FROM [CEGESADATOK].[szamlazas].[ARU]
  where EGYSEGAR < (SELECT AVG(EGYSEGAR) as egysegar
					FROM [CEGESADATOK].[szamlazas].[ARU])

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

select getdate()
select FORMAT(getdate(), 'yyyy')
select FORMAT(getdate(), 'yyyy/MM')
select FORMAT(getdate(), 'yyyy/MM/dd')
select FORMAT(getdate()+1, 'yyyy/MM/dd')
select FORMAT(getdate()-2, 'yyyy/MM/dd')
select FORMAT(getdate(), 'yyyy/MM/dd HH:mm:ss.ms')

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

/*Feladat:
Irassuk ki azoknak a vevőknek a nevet,címét, adószámát ésa vásárlások számát, akik legalább kétszer vásároltak
ROSSZ:
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)
							from szamlazas.szamlafej
							group by szf.VEVO_ID) > 1
GROUP by v.VEVONEV
*/

select COUNT(szf.VEVO_ID), szf.VEVO_ID
							from szamlazas.szamlafej szf
							group by 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

/*Feladat:
Irassuk ki azoknak a vevőknek a nevet,címét, adószámát ésa vásárlások számát, bruttó szummáját*/
select v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM, 
	CONVERT(int,SUM(t.[BRUTTO ERTEK])) as SZUMMABRUTTO
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join szamlazas.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 SZUMMABRUTTO
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join szamlazas.szamlatetel t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
inner join szamlazas.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 SZUMMABRUTTO desc

use CEGESADATOK
go

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

go

USE CEGESADATOK
GO

/* Kik akik vettek A7-et?*/
select VEVONEV
From szamlazas.vevo
WHERE VEVO_ID IN (select VEVO_ID 
					FROM szamlazas.szamlafej
					WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
											From szamlazas.szamlatetel 
											where ARU_ID IN (select ARU_ID 
																From szamlazas.ARU
																where ARUNEV='A7')))


select v.*
FROM szamlazas.vevo v
INNER JOIN szamlazas.szamlafej f ON v.VEVO_ID=f.VEVO_ID
	INNER JOIN szamlazas.szamlatetel t ON f.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
		INNER JOIN szamlazas.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.szamlafej sz ON v.VEVO_ID=sz.VEVO_ID
INNER JOIN szamlazas.tetelek t ON sz.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
where t.ARUNEV='A7'




select * 
FROM szamlazas.szamlafej
WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
						From szamlazas.szamlatetel 
						where ARU_ID IN (select ARU_ID 
											From szamlazas.ARU
											where ARUNEV='A7'))

select * 
From szamlazas.szamlatetel 
where ARU_ID IN (select ARU_ID 
					From szamlazas.ARU
					where ARUNEV='A7')

/*
select * 
From szamlazas.tetelek t INNER JOIN szamlazas.szamlafej sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID
where ARUNEV='A7'

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

USE CEGESADATOK
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) as 'BRUTTO 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 szamlazas.tetelek

create view szamlazas.rossz_szamlafej
as
select *
from szamlazas.szamlafej
where VEVO_ID not in (select vevo_id from szamlazas.vevo)

USE [CEGESADATOK]
GO

/****** Object:  Schema [szamlazas]    Script Date: 2022. 10. 25. 0:11:57 ******/
CREATE SCHEMA [szamlazas]
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 [CEGESADATOK]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [test1]
GO

CREATE TABLE apprentices(
	id int,
	name varchar(50),
	age int,
	country varchar(50),
	cohort_id int)

CREATE TABLE cohorts(
	id int,
	name varchar(50),
	started_at date,
	type varchar(50))

/****** Script for SelectTopNRows command from SSMS  ******/
  bulk insert apprentices
  FROM N'E:\apprentices.table.csv'
  WITH (fieldterminator=';',rowterminator='\n'/*,  FIRSTROW=2 ha van fejlec*/)

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

SELECT name, started_at
 FROM cohorts

SELECT name, country
FROM apprentices

SELECT name, age
FROM apprentices
WHERE age < 21

SELECT id, name
FROM apprentices
WHERE country not like 'Hungary'

SELECT count(id)
FROM apprentices
WHERE age >= 20 and age <= 30

SELECT count(id)
FROM cohorts
WHERE type like 'part-time'

SELECT a.name as apprentice_name, c.name as cohort_name
FROM apprentices a INNER JOIN cohorts c ON a.cohort_id=c.id
WHERE FORMAT(started_at, 'yyyy')=2018

SELECT count(id)
FROM cohorts
WHERE id NOT IN (SELECT cohort_id FROM apprentices)

SELECT a.name, c.started_at
FROM apprentices a INNER JOIN cohorts c ON a.cohort_id=c.id
ORDER BY a.name

SELECT c.name, count(a.id) as numberOfApprentices
FROM cohorts c LEFT JOIN apprentices a ON a.cohort_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:\SQLDATA\DB\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

CREATE DATABASE [INTERFACE]
 CONTAINMENT = NONE
 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 = Off;
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 = Off;
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 [AdventureWorks2016]
GO
CREATE USER [interface_svc] FOR LOGIN [interface_svc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [interface_svc]
GO

USE [INTERFACE]
GO

/****** Object:  Schema [AWDB]    Script Date: 12/9/2022 8:21:47 AM ******/
CREATE SCHEMA [AWDB]
GO

USE [INTERFACE]
GO

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

USE [INTERFACE]
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

SELECT @@VERSION

SELECT 'Hello World!'

SELECT SQRT(30)

SELECT CAST(GETDATE() as date)

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

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (20) *
  FROM [AdventureWorks2016].[Person].[Address]

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (20) *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (20) PersonType, FirstName, LastName
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (20) PersonType, FirstName + ' ' + LastName
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM'

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND (LastName='Chen' OR LastName='Kane')

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT COUNT(DISTINCT FirstName)
  FROM [AdventureWorks2016].[Person].[Person]

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT COUNT(FirstName)
  FROM [AdventureWorks2016].[Person].[Person]
  WHERE FirstName='Ken'

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (20) FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person]
  GROUP BY FirstName, LastName
  ORDER BY COUNT(1) DESC

SELECT FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person]
  GROUP BY FirstName, LastName
  HAVING COUNT(1) >= 4
  ORDER BY 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

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD > 1000000 THEN SalesYTD ELSE NULL END as minsales
  FROM [AdventureWorks2016].[Sales].[SalesPerson]

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD >= 2000000 THEN SalesYTD ELSE CASE WHEN SalesYTD>=1000000 THEN 1500000 ELSE NULL END END as 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

CREATE DATABASE [INTERFACE]
 CONTAINMENT = NONE
 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 = Off;
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 = Off;
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

USE [INTERFACE]
GO

/****** Object:  Schema [dbo]    Script Date: 2023. 01. 15. 13:45:32 ******/
CREATE SCHEMA [AWdatabase]
GO

USE [INTERFACE]
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(day,'2022-04-25',GETDATE())

/****** Script for SelectTopNRows command from SSMS  ******/
USE [AdventureWorks]
GO
SELECT TOP (15) *
  FROM [AdventureWorks].[Person].[Address]

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

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

USE [AdventureWorks]
GO
SELECT TOP (20) PersonType, FirstName+ ' ' + LastName 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 FirstName='Ken'