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;

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;

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