SQL kisokos

OFFSET – x. sortól y. sorig

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

WITH TIES – holtversenyben az utolsó helyen állóval

SELECT TOP 10 PERCENT WITH TIES;

COALESCE – 1. nem NULL érték

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

WHERE – karakterhelyettesítések

Wildcard search (%) – SQL Server | Microsoft Learn

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

WHERE IN – szerepel e egy listában

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

AGGREÁLT LEKÉRDEZÉSEK – AVG(),SUM(), MIN(), MAX(), COUNT(), COUNT_BIG()

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

AGGREGÁLÓK – nem kezelik a NULL-ot

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

subquery – self-contained/simple

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;

correlated subquery – last OrderID 

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;

retuns with list

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

EXISTS

SELECT DISTINCT CustomerID, CustomerName
FROM Sales.Customers c
WHERE PostalCityID = 33832
AND EXISTS
(SELECT * FROM Sales.Orders o
WHERE o.CustomerID=c.CustomerID);

running-total – gordulo lekerdezes

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;

DERIVED TABLE version

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;

CTE

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;

CASE – NORMAL – DERIVED TABLE – CTE 

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;

JOIN AND – Ez a where-ben and kapcsolattal lenne

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

Főnök főnöke JOIN

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

data length

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

UNIQUEIDENTIFIER

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

autocreate id

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

OWN TYPE

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

Avoid autoincrement & INSERT problem

SET IDENTITY_INSERT Hr.Employees ON;

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

SET IDENTITY_INSERT Hr.Employees OFF;

OUTPUT inserted, deleted

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 testlines where orderdate=’20130101′ > LOG 

DELETE dbo.testlines
OUTPUT deleted.*
FROM dbo.testlines tol
INNER JOIN dbo.testorders tor ON tor.OrderID=tol.OrderID
WHERE OrderDate='20130101'

PRIMARY KEYS, FOREIGN KEYS, UNIQUE KEY, CONSTRAINTS

PRIMARY KEY

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

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

FOREIGN KEY – 1 > N – PRIMARY KEY

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

cascade version

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

kapcsoló tábla – employees and educations – N > M

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

1 > 1

DROP TABLE IF EXISTS dbo.itusers;
CREATE TABLE dbo.itusers(
id int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_itusers_id PRIMARY KEY
, 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)
);

UNIQUE KEY

ALTER TABLE dbo.employees
ADD CONSTRAINT UK_employees_person UNIQUE (emp_name,birth_date,mother_name);

CHECK CONSTRAINT

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

működik e a check constraint

ALTER TABLE dbo.employees WITH CHECK
CHECK CONSTRAINT CK_employees_contacts;

DEFAULT CONSTRAIT

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

temporal table sql2017+ – advanced sql server 2019 cu18

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;

INDEX

INDEX típusok:
– in-memory index: oltp
– columnstore index
– rowstore: clustered 1 , nonclustered

rowstore index típusai:
– clustered index – telefonkönyv, teljes tábla
– non-clustered: heap – akkor heap ha nincs clustered index a táblán
címjegyzék, pl jogállamiság szó melyik oldalon szerepel 120,130. oldal

index node: index key, address
Leaf level: table
root > intermidiate > leaf node

nonclustered index. B-tree

leaf level: index key, clustered index

arra jó hogy index kulcsban jól keressünk, de nem ússzuk meg a clusterezést

CLUSTERED INDEX

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

nonclustered indexes akkor jók, ha a where-ben lévőre készítjük, olyan lekérdezés amelyet sokat használunk

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

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

index használatának kikényszerítése – ne használjuk

cost: 2.24

SELECT OrderLineID, StockItemID,UnitPrice, Quantity
FROM dbo.testlines WITH (INDEX (idx_testlines_stockitemid))
WHERE StockItemID=148;

leaf level szinten bekerül a UnitePrice, Quantity

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

Filtered index

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

Változók

Batch feldolgozás és változók (GO)
– két GO között ami van az egy batch
– változók csak egy batchban élnek
DECLARE @stockItemId INT;
SET @stockItemId = 2;

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

Szűrő feltételként, akár több lekérdezésben

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;

utolsó értéket veszi fel, ha egy selectben adjuk meg

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;

Ha nincs eredménye a lekérdezésnek, akkor a változó NULL értéket vesz fel

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;

ADATÍPUSOK

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

numeric vs float

float közelítő kerekít, nagy számoknál az integer része se biztos pontos, csak az első 7 precíz, x*2^y => 199*2*5 — fizika, statisztikai számítások nagy értékeknél, pl decimalnem elég
numeric/decimal,az amit kap, pénzügyi dolgoknál numeric pl. price is 1.99

money – ez decimal igazából, csak négy tört résszel,ami fix -eléírhatod a currency-t de nem tárolja

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

datetime vs datetime2

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

SELECT @ido_datetime, @ido_datetime2;

datetimeoffset

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

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

char vs varchar

DECLARE @mychar CHAR(3)='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

nchar vs nvarchar

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

binary

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

unicode karaktert teszik asciiba

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

túl nagy értékek

DECLARE @darabszam SMALLINT = 32768;
SELECT @darabszam;

 implicit konverzió

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

CAST vs CONVERT

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

DECLARE @ma DATETIME2 =GETUTCDATE();

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

TRY_CAST vs TRY_CONVERT

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

FORMAT

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

SZÖVEG műveletek: TRIM,LTRIM,RTRIM

len a végét nem nézi a szövegnek itt:

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

UPPER, LOWER

SELECT UPPER('Ékezet')
SELECT LOWER('ÉKEzet')

REPLICATE,LEFT, REVERSE, REPLACE

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

Dátum idő műveletek: DATEDIFF

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

Kerekítés

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

TEMP TABLE, TABLE VARIABLE

temp table

csak session-ben létezik, tempdb-ben tárolja, soknál túl nagy tempdb, táblák lekérdezésének tárolására, előnye: szűrt adatok vannak benne, gyorsabb lesz a lekérdezés, kevesebb kód, teljesítmény

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

Global temp talbe: session-ök között is megmarad, miután lefutott az összes query és sessionöknek vége akkor megszűnik

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

Table variable: csak a batch-ben létezik, nem ideális sok adathoz

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;

CONTROL FLOW ELEMENTS (IF, WHILE)

code branching

IF…ELSE
IF expressions

– three-valued logic: TRUE, FALSE, UNKNOWN (NULL)
simple if example

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

NULL handling nested if elements

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

Active DB, ez csak a sajátnál, más sessionnél még használhatják, kill kell

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

Complex if expression

last day of last month = today-20 || today is IN (monday, tuesday, friday) || today = 2023-02-20
true or false

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;

IF EXISTS

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;

LOOPS

syntax:

WHILE (expression)
BEGIN

END
(CONTINUE, BREAK, GO 1000)

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

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

loop with delaying

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

@@ROWCOUNT

utolsó statement-et számolja

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

ERRPOR HANDLING

Error types:
– datatype conversion error
– valuation error (értékelési hiba)
– syntax error > can not handle in t-sql
– logical error
– name resolution error > can not handle in t-sql
– constraint violation error
– error by trigger

quantity -1000

Miért írunk hibakezelést?
– nem akarom eltörni a kódot
– logolni szeretném a hibát
– emészhetőbb formában állítjuk elő a hibakódot

Hogyan?
– ne szálljon el a kód
– vagy szálljon el a kód, hogy ne legyen nagyobb baj

Hibakezelési módok:
– structured error handling with: TRY > CATCH
– @@ERROR beépített függvény, do not use

09:17:15Started executing query at Line 42
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Total execution time: 00:00:00.013

Msg 8134 > error code (id)
Level > Severity levels
State > status
Line > row where is the error

example division by zero

PRINT 10/0;

TRY…CATCH

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

error handling built-in fuctions

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

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;

PRINT ‘Error module: ‘ + COALESCE(ERROR_PROCEDURE(),”);

   BEGIN TRY
   EXEC dbo.badprocedure
   END TRY
   BEGIN CATCH

   END CATCH

name resulution error

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;

name resolution error handling by module

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;

negative unit price error by handling THROW

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;

THROW in CATCH – eltöröm a kódot, de előtte kiiratom a hiba okát

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;

RAISE ERROR – in catch nem töri el a kódot

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;

RAISERROR can integrate to event viewer > windows log > applications
generate unique error code

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;

error handling with INSERT

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;

nested try-catch block

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

VIEWS

– hivatkozás, értékeket újra számolja, ha számított, kivéve ha indexed views

— Felhasználók elől rejtek el egy view-t, egyes oszlopokat nem teszek bele a viewba és ahhoz adok hozzáférést
— gyakorlatban egy külön schema-ba tesszük és arra adunk hozzáférést

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;

WITH SCHEMABINDING, ENCRYPTION

SCHEMABINDING: nem tudjuk úgy módosítani, hogy használhatatlan legyen pl oszlop törlés
ENCRYPTION: nem tudjuk megnézni a kódját (create to)

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

ALTER VIEW

— view módosítása drop nélkül  –  DROP VIEW IF EXISTS dbo.VarosokOrszagok2;
–hasznalja a VarosokOrszagok2Alapjan még, ott még van SCHEMABIDING

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

INDEXED VIEW

persisted?

– determinisztikusnak kell lennie a view-nak
– schemabiding kell, a felhasználói functionöknek is
– csak táblákat használhat

milyen esetekben? milyen előnyökkel jár? indexed view
– disken van ezért gyorsabb

probléma: gyorsabb lekérdezni, ha bonyolult join van
ha módosítasz, akkor a view-t is módosítani kell

ha egy táblában többet írunk vagy ugyanannyit írunk, mint amennyit olvasunk, akkor nem alkalmazzuk

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

Tárolt Eljárások

– Bemenetek
– Kimenetek
– Többször meglehet hívni

Tárolt eljárások előnyei:

– paraméterátadás
– tárolt eljárásra létrehoz egy > végrehajtási terv – teljesítmény – parameter sniffing – másik paraméterrel lehet nagyon lassú > WITH RECPOMPILE (minden meghíváshoz új execution plan)
— meg tudod oldani, hogy külön eset legyen
-https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

– minden paraméternél kell a típus

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

Mi az az SQL Injection?

— Mit ír be a felhasználó? Ez oké?
–„V” > OK
— „V’ ; DROP TABLE dbo.students; SELECT * FROMdbo.Teachers WHERE valami LIKE ‘” > NAGYON NEM OKE
— WHERE studentId = 123 AND v.Nev LIKE ‘”‘ ” vagy WHERE v.Nev LIKE ‘”nincs ilyen ‘ OR 1=1″ > NAGYON NEM OK
–/**/OR//**/

— Mi a megoldás?

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;

You’ll need to specify the stored procedure table parameter
as READONLY

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

Hogyan kaphatunk visszaértéket?
OUTPUT – paraméter! több kimeneti értéket is ki tudunk küldeni a tárol eljárásunkból

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;

Visszatérési érték – RETURN

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

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

SELECT * FROM dbo.Varosok

Első RETURN-ig fut

– korábban megadta, ezért ez már nem írja felül

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

HIBAKEZELÉS

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;

DEFAULT érték

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;

SET NOCOUNT ON

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

Hány soron volt módosítás?

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;

UDF:

– always retrun
– not state, gives data type
– input paramater
– stored procedured can not be called

Types:
– system functions
– Table-valued: view-nál nem tudom paraméterezni
– Scalar-values: can be in select
– Aggregate < külső nyelveket pl .Net and C# függvényeket be lehet hozni
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-aggregate-transact-sql?view=sql-server-ver15

TRY-CATCH – NEM MŰKÖDIK!!!!!!!!!!!!!!!!
de store procedure-ból hívom és ott van akkor igen

DDL, insert,update, delete nem működik
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();

total_sum_orderline

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;

UDF Scalar inlining

– nem hajtódik végre az UDF, szétszedi táblaműveletekre > kevesebb CPU — ADVANCED FEATURE
2019-től, ezért pakoltuk át
Meg kell felelni:

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

– pl. egy return lehet csak
– for example Scalar UDF inlining not working with computed column!

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;

DEFAULT érték scalar UDF-nél

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

Table-valued funcions:

– return always with table
– no JOIN

Types:
– inline table-valued functions iTVF > 1 SELECT STATEMENT;
– multi statement – MSTVF(borzalmas) – ne használd, rossz optimalizáció

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

DEFAULT érték TVF UDF-nél

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

APPLY – CROSS APPLY like inner join

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

APPLY – OUTER APPLY like LEFT OR RIGHT OUTER join

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

nem függvényes rész
táblaoperátor:
cross aply instead of subquery (return )
bizonyos subquery

cross apply instead of subquery (subquery returns 1 scalar value)
for example
https://github.com/green-fox-academy/teaching-materials/blob/master/workshop/sql-server-subqueries-aggregations/correlated-quantity/correlated-quantity.md
Modify the below query per the following:
– add a new column that shows the maximum quantity for the StockItemID
– add yet another column that shows the difference between the maximum
quantity for the StockItemID and each order quantity
| OrderID | StockItemID | Quantity | Max_StockItem_Qty | Max_Diff_Qty |

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

TRIGGERS

– can I run trigger? > NO
– an event activate the trigger ! „FIRE_TRIGGER”
– can I  give parameter to the trigger? -> NO
– IF … ELSE structure  usable
– try …catch structured error handling > YES
– create simple trigger
Trigger groups:
– DML triggers: INSERT, UPDATE, DELETE > MASTERWORK
– DDL trigger: ALTER,CREATE,DROP
— logon trigger

LOGON triggers: DANGEROUS! bejelentkezést elbaszhatja

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;

DDL triggers

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;

DML triggers:

– AFTER trigger
– INSTEAD OF trigger

AFTER DELETE

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;

create trigger to deny: modify invoice

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;

where are the DML triggers!

!!!!! TRUNCATE can not activate with triggers !!!!!!!!!!

INSTEAD OF triggers? > DML triggers

pl. szamlaknal a nagyok máshova menjenek ritka

create a trigger
where total >= 1100 > dbo.Invoces

where total < 1100 > dbo.smalltotalinvoices

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 ignoralja a triggert alapból

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;

Window function

– Window aggregate functions (COUNT, SUM, MIN, MAX)
– Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
– Offset functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE)
– Statistical functions (PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, CUME_DIST)

OVER() == Window function / SELECT /

sor halmazokat, adott sorhalmaz az ablak,

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;

windows function by group by

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;

ranking functions

syntax requirement: ORDER BY inside OVER clause

ROW_NUMBER, RANK, DENSE_RANK, NTILE

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;

ranking by orderid per customerid

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;

webshop rendelés milyen tételekből áll – végösszeg, orderline_number

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;

running total

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

offset function
(LAG, LEAD, FIRST_VALUE, LAST_VALUE)

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

Cardinality Estimation

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

Első próbavizsga

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

Első vizsga

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

Próbavizsga

hsg-reader-sqlserver-final-trial-exam

Fontos

  • Bármilyen online forrást használhatsz, de kérlek dolgozz egyedül!
  • A repot forkold le, utána a leforkolt repót klónozd a saját gépedre
  • Használj UTF8 WITHOUT BOM karakterkódolást
  • Ne csak copy-paste-eld a válaszokat és megoldásokat, inkább használd a saját szavaidat
  • Nincs autograderes kiértékelés.
  • A 4. feladathoz mongoDB szükséges a NoSQL szakanyag megismerése után oldható meg
  • A 4. feladathoz telepíthesz mongoDB-t a rendszeredre

Feladat #1:

Egy vezető gyorsétteremlánc megkér, hogy csinálj adatbázist az ő termékeivel forgalmazó kiszállító cégeknek.

Ebben a „Homedelivery” adatbázis egy részét kell most megtervezned.

Az alábbi adatokat kell kezelni:

  • Store (boltok adatai)
  • Menu (a boltokban tárolt aktuális termékek katalógusa)
  • A táblák között lévõ kapcsolatok

Az alábbi adatokat kell kezelni:

  • Bolt azonosítója (kapjanak egyedi azonosítót)
  • Jelenleg bent lévő aktív katalógus azonosítója
  • Mikor kapott utoljára sikeres katalógust a bolt
  • Mikor készült utoljára sikeres katalógus
  • Kiszállító cég fix listából
  • Nyitvatartás
  • A bolt 7/24 nyitva tart

Egy bolthoz több katalógus is tartozhat, viszont egy katalógust (azonosító alapján) csak egy bolt fog tudni használni. Egy bolt egyelőre csak egy kiszállítóval tud dolgozni.

Hozd létre a megfelelõ adatbázis táblákat, a megfelelõ típusú oszlopokkal!

Az alábbi megszorításokat (constraints) használd minimum:

  • PRIMARY KEY
  • FOREIGN KEY

Definiálj további megszorításokat, indexeket is ha szükséges!

Figyelj a normalizálásra!

Küldd vissza a tábla script-eket!

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

Feladat #2:

Használd a WideWorldImporters példa adatbázist!

Írj egy egyszerű tárolt eljárást az Application.Countries táblára, amely visszaadja a kontinens nevét és a hozzátartozó összegzett népességszámot.

A tárolt eljárás az Application sémában készüljön GetContinentRanking néven.

A tárolt eljárás egy paramétert fogad: – @rank tinyint: alapértelmezett értéke 1

Az egész szám típusú paraméter értéke megmondja, hogy hányadik kontinenst kell visszaadnia a tárolt eljárásnak a népességszám szerinti ranglista alapján.

Például:

  • Ha a paraméter értéke 3, akkor a harmadik legnépesebb kontinens nevét és népességszámát kell visszaadni.
  • Ha a paraméter értéke 5, akkor az ötödik legnépesebb kontinens nevét és népességszámát kell visszaadni.
  • Ha a paraméter értéke 1, akkor a legnépesebb kontinens nevét és népességszámát kell visszaadni.

Az alábbi táblákat használd:

  • Application.Countries

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!

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;

Feladat #3:

Használd a WideWorldImporters példa adatbázist!

Írj egy T-SQL script-et ami az alábbiakat tartalmazza:

  1. Létrehoz egy „SampleLogin” nevű SQL login-t egy jelszóval és a WideWorldImporters adatbázist jelöli meg mint alapértelmezett adatbázis.
  2. A WideWorldImporters adatbázisban létrehozni egy „SampleUser” felhasználót az előbbi login-hoz.
  3. A felhasználót hozzáadja az „External Sales” adatbázis szerephez.
  4. Ennek a szerepnek a SELECT jogot hozzáadni a Application.People táblához.
  5. Teszteli az adatolvasást az „SampleUser” felhasználó megszemélyesítésével és egy SELECT lekérdezéssel a Application.People táblából.
  6. Visszaállítja a végrehajtás kontextusát az elõzõ felhasználóra.
  7. Lekérni az aktuális felhasználó nevét.

Mindegyik feladat egy-egy T-SQL utasítást jelent.

Másold fel az alábbiakat github-ra:

  • a script fájlt
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();

Feladat #5:

Reprodukálj egy deadlock-ot az alábbi script segítségével!

Nyiss hozzá 3 új lekérdezés ablakot!

Futtasd le a következő szkripteket sorban a megjelölt ablakban:

  1. ablak:
DROP TABLE IF EXISTS Application.Deadlock;
GO
CREATE TABLE Application.Deadlock
(
    DeadlockId int NOT NULL PRIMARY KEY
);
BEGIN TRANSACTION
INSERT INTO Application.Deadlock (DeadlockId) VALUES (1);
  1. ablak:
BEGIN TRANSACTION
INSERT INTO Application.Deadlock (DeadlockId) VALUES (2);
  1. ablak:
SELECT DeadlockId FROM Application.Deadlock WHERE DeadlockId = 2;
  1. ablak:
BEGIN TRANSACTION
INSERT INTO Application.Deadlock (DeadlockId) VALUES (3);
  1. ablak:
SELECT DeadlockId FROM Application.Deadlock WHERE DeadlockId = 3;
  1. ablak:
SELECT DeadlockId FROM Application.Deadlock WHERE DeadlockId = 1;

A 3 ablak valamelyike visszatér egy piros deadlock hibaüzenettel.

A ROLLBACK TRAN paranccsal leállíthatók az esetlegesen futva maradt utasítások.

  1. Gyűjtsd be a deadlock graph xml-t az általad reprodukált deadlock-ra!

  2. Mentsd le a deadlock graph xml-t .XDL fájl formátumba!

  3. Jelenítsd meg a deadlock graph-ot grafikusan (általad választott eszközzel) és készíts róla egy screenshot-ot!

  4. Konfiguráld be az adatbázist úgy, hogy konkrétan ez a deadlock ne ismétlődjön meg!

Másold fel az alábbiakat github-ra és számozd a fájlokat a lentieknek megfelelően::

  • 1: az .XDL fájlt
  • 2: a screenshot kép fájlt
  • 3: a T-SQL utasítást
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;

Feladat #6:

Készítsd el az SQLTestDB táblát az alábbi szkripttel!

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

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

USE [SQLTestDB]
GO

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

SELECT * FROM SQLTest
GO

A SQLTestDB adatbázis „point-in-time recovery”-t igényel az üzlet kívánsága szerint.

Írj egy T-SQL script-et ami az alábbiakat tartalmazza:

  1. Bekonfigurálja az adatbázis recovery model-t az üzleti igénynek megfelelően.

  2. Backup előtt teljes adatbázis integritás ellenőrzést végez a SQLTestDB adatbázison úgy, hogy nonclustered indexek ellenőrzését kihagyja.

  3. Full backup-ot készít fix elérési útvonalon SQLTestDB_Full.bak fájlnévvel.

  4. Differential backup-ot készít tömörítéssel fix elérési útvonalon SQLTestDB_Diff.bak fájlnévvel.

  5. Log backup-ot készít tömörítéssel és checksum-mal fix elérési útvonalon SQLTestDB_Log.trn fájlnévvel.

  6. Ellenőriz egy backup-ot, teljes-e illetve olvasható-e a backup set (de nem állítja vissza).

Mindegyik feladat egy-egy T-SQL utasítást jelent. Az utasításokban csak a kért opciók/beállítások szerepeljenek!

  1. Ütemezd be a fenti backup utasításokat SQL Agent job-okkal (használhatod az Ola Hallengren job-okat is) és készíts egy megfelelő mentési ütemezést (schedule) az alábbiak figyelembevételével:
    • az adatbázis mérete több száz GB lehet
    • optimalizálni kell a visszaállítást egy katasztrófa esetén
    • maximum 5 percnyi adat veszhet el egy katasztrófa esetén
    • az ütemezéseket úgy kell elnevezni, hogy egyértelműen utaljon a mentés típusára

Készíts screenshot-okat a mentési ütemezésekről SQL Server Management Studio-ban, az ütemezések „Job Schedule Properties” dialógus ablakairól.

Másold fel az alábbiakat github-ra:

  • a script fájlt
  • a screenshot fájlokat
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

Feladat #7:

Az SQLRestoreDB nevű adatbázist vissza kell állítani backup-ból, a legutolsó lehetséges restore pontig. Az adatbázis visszaállítására azért van szükség, mert véletlenül törlésre került.

Írj restore utasításokat, amelyek egy restore láncban visszaállítják az adatbázist és így a tábla teljes tartalmát.

Az elérhető mentések az alábbiak:

  • SQLRestoreDB_Full.bak: Full backup
  • SQLRestoreDB_Log_2.trn: Log backup
  • SQLRestoreDB_Diff_3.bak: Differential backup
  • SQLRestoreDB_Log_4.trn: Log backup

A backup fájlok nevei mutatják a backup típusát és keletkezésük sorrendjét is.

Az adatbázis nem az alapértelmezett meghajtón helyezkedett el, így lehet szükség mozgatásra is. Írj egy restore utasítást, ami kilistázza milyen fájlok találhatóak a full backup fájlban.

Az adatok törlése az „SQLRestoreDB_Log_4.trn” log backup után történt!

A legoptimálisabb restore láncot alkalmazd!

Miután visszaállítottad az adatbázist, futtasd az alábbi lekérdezést és mentsd le az eredményt .csv formátumba.

USE SQLRestoreDB;
GO
SELECT 
    GETDATE() AS [examstimestamp], 
    @@SERVERNAME AS [myservername],
    * 
FROM dbo.SQLRestoreDB;
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;

Feladat #8:

Készítsd el a következő táblát:

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

Adj hozzá adatot a következő INSERT INTO paranccsal:

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

Készíts egy tárolt eljárást az Application sémába DeleteLogData néven.

Az eljárás töröl egy rekordot az Application.LogAudit táblából az átadott LogAuditId paraméter alapján.

A tárolt eljárás a következő paramétert kéne használja:

  • @logauditid int

A tárolt eljárás a következő OUTPUT paramétert kéne használja:

  • @deletedlogdata nvarchar(50)

Használj hibakezelést az ehhez tartozó beépített hiba szám és hiba üzenet funkciókkal.

Ha a törlés sikeres az eljárás térjen vissza a törölt LogData értékkel az OUTPUT paraméterben.

Ha hiba történne, kezelje azt:

  • nyomtassa: ‘Delete of log audit failed!’
  • nyomtassa: ‘Error number: ‘ and the error number
  • nyomtassa: ‘Error message: ‘ and the error message
  • térjen vissza -1 értékkel

Mutass példát az elkészült eljárás felhasználására!

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

HSG Reader Final Normal Exam

Általános

  • Gradescope link az Írásbeli Teszt részhez
  • A teszt időablaka: 9:00-10:00 . 40 kérdés megválaszolására 60 perc áll rendelkezésre.
  • Az alább elhelyezkedő feladatok megoldásait Gradescope-ra a Projektfeladat részhez kell feltölteni
  • A fájlokat a feladatokban megadott módon kell elnevezni
  • A megoldásokat .zip tömörítésként lehet együttesen Grade-scopra feltölteni.
  • Csak a Gradescope tartalom szerint történik a kiértékelés!
  • Ügyelj rá, hogy a feltöltött fájljaid kódolása UTF-8 WITHOUT BOM (signature) legyen.
  • Ügyelj rá, hogy a beadott SQL fájljaid SSMS/Azure Data Studióból „futtathatóak” legyenek

Kezdeti lépések

  • Fork-old ezt a mappát a saját github useredhez
  • Klónozd le a saját neved alól a fork-olt reposytori-t gépedre, ide dolgozz
  • Commit-elj rendszeresen és használj érthető commit megjegyzéseket
  • Minden megoldásodat fel kell ide, github-ra is tölteni

Mit találsz a kapott Azure környezetben a gépen?

  • A WideWorldImporters adatbázist működéskész állapotban.
  • Egy ExamDB nevű adatbázist működés kész állapotban. (Lentebb a Mentések linken találtok egy ExamDB_Full.bak backup fájlt is, ha saját környezetben dolgoznátok.)
  • Továbbá egy DBAHealth nevű adatbázist, amire fel vannak rakva az sp_Blitz féle scriptek és az Ola Hallengren féle scriptek.
  • Ezen kívül az alapértelmezett backup mappájában egy RestoreDB adatbázis mentései helyezkednek el. (Ezeket is megtaláljátok legalul a Mentések linken pluszban.)

Mit lehet használni?

  • Bármilyen online forrás használható, de dolgozz egyénileg
  • NE csak másold a megoldásokat, használd a saját tudásod, szavaid
  • NE Push-olj a GitHub-ra míg a mentor ki nem jelenti, hogy lehet
  • Ne feledd feltölteni a megoldásaidat ide is időben: Gradescope

Feladatok

Backup fájlok

Deadlock szkript

Feladat #1:

Egy iskola adatbázis fejlesztésében veszel részt. Azt kérték, hogy az adatbázis a tanárokat, a diákokat, a tantárgyakat és a hozzájuk kapcsolódó adatokat tárolja.

Az alábbi adatokat kell kezelni:

  • Tanárok adatai
  • Tantárgyak listája
  • Diákok adatai
  • A táblák között lévő kapcsolatok

Az alábbi adatokat kell kezelni:

  • Tanárok/diákok/tantárgyak azonosítója (kapjanak egyedi azonosítót)
  • Tanárok/diákok neve, email címe, születési dátum
  • Tantárgyak neve
  • Megjegyzés a diákokhoz
  • Tanárok telefonszáma
  • Diákok jelentkezésének éve (mióta jár az iskolába) / csak az évszámot kell tárolni /
  • Diákok jelenlegi osztálya

Egy diákhoz több tantárgy is tartozhat, ahogyan egy tantárgyat több diák is tanulhat, viszont jelen esetben egy tanár csak egy tantárgyat oktat.

Hozd létre a megfelelő adatbázis táblákat, a megfelelő típusú oszlopokkal!

Az alábbi megszorításokat (constraints) használd minimum:

  • PRIMARY KEY
  • FOREIGN KEY

Definiálj további megszorításokat is ha szükséges!

Figyelj a normalizálásra!

Evidencia SQL szkript, amely elvégzi a fent leírt műveletet
File neve WINSQL-01.sql
DROP DATABASE IF EXISTS SchoolDB;
CREATE DATABASE SchoolDB;
GO
USE SchoolDB;
GO
CREATE TABLE dbo.Subjects (
 ID INT IDENTITY(1,1),
 Name NVARCHAR(150) NOT NULL);

CREATE TABLE dbo.Students (
 ID INT 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

Feladat #2:

Használd a WideWorldImporters példa adatbázist!

Írj egy lekérdezést ami visszaadja az „Abel” keresztnévvel kezdődő vásárló ID-ját, nevét és CreditLimit értékét, ahol a vásárló nem adott le rendelést 2016.05.01 és 2016.05.15 között (A 05.01-ei és a 05.15-ei rendelések se szerepeljenek !).

Figyelj arra, hogy a dátum keresési feltétel lehetőleg SARG-able legyen !

Az alábbi táblákat használd:

  • Sales.Customers
  • Sales.Orders

A lekérdezés az alábbi oszlopokkal térjen vissza:

  • [Customer ID]: a vásárló ID-a
  • [Customer Name]: a vásárló neve
  • [Credit Limit]: a vásárló kredit limite
Evidencia SQL szkript, amely elvégzi a fent leírt műveletet
File neve WINSQL-02.sql
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'
    );

Feladat #3:

Használd az ExamDB példa adatbázist!

Előzetesen futtasd le az alábbi script-et:

USE ExamDB;
GO
DROP SCHEMA IF EXISTS Exam;
GO
CREATE SCHEMA Exam;
GO
DROP PROCEDURE IF EXISTS Exam.Proc1
GO
CREATE PROCEDURE Exam.Proc1
AS
SELECT 1;
 

Írj egy T-SQL script-et ami az alábbiakat tartalmazza:

  1. Létrehoz egy „NewExamLogin” nevű SQL login-t egy jelszóval és az ExamDB adatbázist jelöli meg mint alapértelmezett adatbázis.
  2. Az ExamDB adatbázisban létrehoz egy „NewExamUser” felhasználót az előbbi login-hoz.
  3. Létrehoz egy új adatbázis szerepet (role) az ExamDB adatbázisban, „Exam Admins” néven.
  4. Létrehoz egy másik új adatbázis szerepet (role) az ExamDB adatbázisban, „Exam Contributors” néven.
  5. A „NewExamUser” felhasználót hozzáadja az „Exam Admins” adatbázis szerephez.
  6. Az Exam sémára EXECUTE jogot ad az „Exam Admins” adatbázis szerepnek.
  7. Az Exam sémán explicit letiltja az EXECUTE jogot az „Exam Contributors” szerepnek.
  8. Teszteli az Exam.Proc1 tárolt eljárás futtatását a „NewExamUser” felhasználó megszemélyesítésével.
  9. Visszaállítja a végrehajtás kontextusát az előző felhasználóra.
  10. Lekérdezi a jelenlegi felhasználó nevét és a login nevét.

Mindegyik feladat egy-egy T-SQL utasítást jelent.

Evidencia SQL szkript, amely elvégzi a fent leírt műveletet
File neve WINSQL-03.sql
-- 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];

Feladat #4:

Írj egy diagnosztikai lekérdezést (system catalog view-k segítségével), ami visszaadja a szerveren lévő összes adatbázishoz (user és system) tartozó fájlok paramétereit, mint pl.: fájl típusa (ROWS vagy LOG), a fájl fizikai elérési útvonala, a fájl mérete, és hogy százalékos a növekmény vagy fix érték.

A lekérdezés az alábbiakat adja vissza:

  • database_id: az adatbázis ID-ja
  • name: az adatbázis neve
  • recovery_model_desc: az adatbázis recovery model neve (pl.: SIMPLE, FULL)
  • file_id: a fájl ID-ja az adott adatbázison belül pl.: 1, 2 stb.
  • type_desc: a fájl típusa pl.: ROWS, LOG stb.
  • name: a fájl logikai neve
  • physical_name: a fájl fizikai elérési útvonala és neve
  • file_size_MB: alias és számolt érték, a fájl mérete (size) Megabyte-ban számolva
  • is_percent_growth: a növekmény százalékos vagy fix
Evidencia SQL szkript, amely elvégzi a fent leírt műveletet
File neve WINSQL-04.sql
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;

Feladat #5:

A feladathoz szükséged lesz az ExamDB adatbázisra. Az adatbázis legyen elérhető (ONLINE) állapotban.

Írj egy karbantartó script-et T-SQL utasításokkal amelyek az alábbiakat végzik el:

  1. Szerverszinten beállítja, hogy alapértelmezés szerint tömörített mentések készüljenek!
  2. Az ExamDB adatbázisra (csak arra!) kikapcsolja a párhuzamosítást! (ne készüljenek párhuzamos lekérdezési tervek erre az adatbázisra)
  3. Visszaellenőrzi (lekérdezi) a tömörített mentésekkel kapcsolatos szerverszintű beállítást!
  4. Visszaellenőrzi (lekérdezi) az ExamDB adatbázisra hogy ki van-e kapcsolva a párhuzamosítás!
  5. 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!
Evidencia SQL szkript, amely elvégzi a fent leírt műveletet
File neve WINSQL-05.sql
-- 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
);

Feladat #6:

A RestoreDB nevű adatbázist vissza kell állítani backup-ból egy adott restore pontig (point-in-time recovery).

A visszaállítás előtt, írj egy utasítást ami megmutatja milyen fájlok (data és log) vannak a RestoreDB_Full.bak backup set-ben!

Utána írj restore utasításokat, amelyek egy restore láncban visszaállítják az adatbázist.

Az elérhető mentések az alábbiak (csak ezeket a mentéseket lehet használni):

  • RestoreDB_Full.bak: Full backup
  • RestoreDB_Log_2.trn: Log backup
  • RestoreDB_Log_3.trn: Log backup
  • RestoreDB_Log_5.trn: Log backup
  • RestoreDB_Log_6.trn: Log backup

A backup fájlok nevei mutatják a backup típusát és keletkezésük sorrendjét is.

A „RestoreDB_Log_5.trn” log backup-pal bezáróan kell visszaállítani az adatbázist!

Miután visszaállítottad az adatbázist, futtasd az alábbi lekérdezést és mentsd le az eredményt .csv formátumba.

USE RestoreDB;
GO
SELECT 
    GETDATE() AS [examstimestamp], 
    @@SERVERNAME AS [myservername],
    * 
FROM dbo.RestoreTable;
 
Evidencia SQL szkript, amely elvégzi a fent leírt műveletet
File neve WINSQL-06.sql
Evidencia A lementett eredményt .csv formátumban
File neve WINSQL-06.csv
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;

Feladat #7:

A feladat végrehajtása előtt futtasd az alábbi script-et:

USE [master];
GO
ALTER DATABASE [ExamDB] SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
 

Használd a deadlock script-et: deadlock_script_exam.sql a 3. lépésig! A 3. lépésben a SELECT lekérdezés várakozni fog (még nincs deadlock!).

  • Írj egy lekérdezést egy új lekérdezési ablakban, ami megmutatja, hogy a várakozó SELECT lekérdezés (illetve a session amiben fut), éppen milyen lock-okat tart fenn illetve milyen lock-okat igényel! Csak arra a session-re add vissza a lock adatokat, ami éppen várakozik!

  • A lekérdezés végrehajtása után, mit látsz, milyen típusú lock-ot igényelt a SELECT lekérdezés, amire éppen várakozik? A lekérdezés mellé kommentben írd a választ.

  • Módosítsd a szerver-konfigurációt a következőképpen: A kiterjesztett (advanced) beállítási lehetőségeknek is elérhetőnek kell lennie, és ez a változtatás a szkript lefutása után azonnal lépjen is érvénybe.

Evidencia SQL szkript, amely elvégzi a fent leírt műveletet és tartalmazza kommentben a válaszokat
File neve WINSQL-07.sql
/*
SELECT tl.request_session_id, tl.resource_type, tl.request_mode, tl.request_status, tl.resource_database_id, tl.resource_associated_entity_id, tl.resource_description
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_requests er ON tl.request_session_id = er.session_id
WHERE er.wait_type LIKE 'LCK%'
  AND tl.request_status = 'WAIT';
GO
*/
USE DBAHEALTH;
GO
dbo.sp_BlitzLock  -- S - 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

Feladat #8

Használd a WideWorldImporters példa adatbázist!

Írj egy tárolt eljárást (stored procedure) Sales sémába, GetPickedByDate névvel, ami rendelési sortétel (Sales.OrderLines) statisztikát ad vissza attól függően, hogy egyes rendelések mikor értek / mikor érhetnek véget vásárlókként egy adott idő intervallumban (OrderDate) az alább kifejtett részletekkel!

A tárolt eljárás két paramétert fogad:

  • @startDate datetime
    • alapértelmezett értéke legyen 2013.01.01. kezdeti dátum
  • @endDate datetime
    • alapértelmezett értéke legyen 2016.06.01. befejezési dátum

Például:

  • Ha az alapértelmezett paraméter értékekkel futtatjuk, a tárolt eljárás vissza adja a 2013.01.01. – 2016.06.01. (OrderDate) között történt vásárárlások statisztikáit vásárló nevenként (CustomerName) és a választás befejezési ideje (PickingCompletedWhen) szerint.

A tárolt eljárás az alábbi oszlopokkal térjen vissza:

  • [Customer Name]: CustomerName oszlop a Customers táblából
  • [Pick Date]: A tétel választás befejezésének időpontja (PickingCompletedWhen) oszlop az OrderLines táblából:
    • ha a dátum nem ismert, akkor írja ki a pillanatnyi dátum 3 nappal megnövelt értékét
  • [Overall Price]: összegzett sortételek (Quantity * UnitPrice) az OrderLines táblából (2 tizedesjegy)
  • [Number of Purchase]: a megszámlált sortételek (OrderLine) száma
  • [Refreshed Date]: ha a sortétel választás befejezésének időpontja (PickingCompletedWhen) nem ismert, írja ki, hogy ‘Expected Pick Date’, ha ismert, írja ki, hogy ‘Already picked’

Tégy bele szűrést, hogy csak azt kapjuk vissza, ahol a vásárló (Customer) vásárlói csoportja (BuyingGroup) nem ismert.

Tégy további szűrést, hogy csak 20000 feletti [Ovarall Price] oszlop értékkel rendelkezőeket kapjuk vissza.

Az alábbi táblákat használd:

  • Sales.OrderLines
  • Sales.Orders
  • Sales.Customers

A tárolt eljárást úgy írd meg, hogy a futás után ne jelenjen meg az „n rows affected” üzenet a Messages fülön!

Mutass példát a használatra!

Evidencia SQL szkript, amely elvégzi a fent leírt műveletet
File neve WINSQL-08.sql
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';

DIAGNOSZTIKAI LEKÉRDEZÉSEK

Táblák lekérdezése a sys.tables-ből

SELECT * FROM sys.tables;

Táblák és sémáik – joining the sys.tables and the sys.schemas catalog views

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;

melyik session-ben vagyunk

SELECT @@SPID;

get column data type info for all user tables

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;

count index per table

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;

list index per table

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

INDEX töredezettsége

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

DEADLOCK?

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

count constraints (check+default) in db

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

Determinisztikus?

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

Select * FROM sys.computed_columns;

Keresés  oszlop neve alapján

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

Keresés tábla neve alapján

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

String keresése az adatbázisban

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

Sys.messages & Modifying language in session

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;

VIEW diagnosztikai lekérés

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID('VarosokOrszagok2');
database logs per database id
SELECT b.name,a.* FROM sys.databases b
CROSS APPLY sys.dm_db_log_info(b.database_id) a
WHERE b.name = 'WideWorldImporters';

minden TRIGGER listázása

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

detect locked objects

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

detect isolation level

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

SQL Kisokos

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

##### SQL alapok #####

SELECT IN SELECT, WHERE IN, WHERE NOT IN

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)

UPDATE

USE [CEGESADATOK]
GO

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

INSERT INTO

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

DELETE

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

DISTINCT

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'

CONVERT, AVG

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

FROMART, CONVERT

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

HAVING

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

CONSISTENT FIX

use CEGESADATOK
go

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

go

SOK JOIN, SOK WHERE

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

LEKÉRDEZÉS VIEW-HOZ

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

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

Create schema (séma)

USE [CEGESADATOK]
GO

/****** Object:  Schema [szamlazas]    Script Date: 2022. 10. 25. 0:11:57 ******/
CREATE SCHEMA [szamlazas]
GO

Create user, login, permissions, roles

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

Tábla törlése, truncate table

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [SZAMLAFEJ_ID]
      ,[VEVO_ID]
      ,[KELTE]
      ,[TELJESITES]
      ,[FIZHATARIDO]
      ,[NAVATKULDVE]
      ,[STATUS]
  FROM [CEGESADATOK].[szamlazas].[szamlafej]

  truncate table szamlazas.szamlafej

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

###### 3. vizsga SQL  #####

Táblák létrehozása (create table)

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

Táblák importálása (bulk insert)

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

Listázd a nevét (name) és kezdési dátumát (started_at) az összes a évfolyamnak (cohorts tábla)
Filename: cohorts.sql

SELECT name, started_at
 FROM cohorts

Listázd a nevét (name) és származási országát (country) az összes a hallgatónak (apprentices tábla)
Filename: apprentices.sql

SELECT name, country
FROM apprentices

Listázd a nevét (name) és korát (age) azoknak hallgatónak (apprentices tábla), akik fiatalabbak, mint 21
Filename: apprentices-younger-21.sql

SELECT name, age
FROM apprentices
WHERE age < 21

Listázd az id-ját és nevét (name) azoknak hallgatónak (apprentices tábla), akik nem Magyarországról származnak (country)
Filename: apprentices-not-from-hu.sql

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

Írasd ki hány darab olyan hallgató(apprentices tábla) található az adatbázisban, akik legalább 20 és legfeljebb 30 évesek
Filename: apprentices-between-20-and-30.sql

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

Írasd ki hány darab olyan évfolyam(cohorts tábla) található az adatbázisban, amelyek „part-time” típusúak (type)
Filename: cohorts-part-time.sql

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

Listázd a hallgatók nevét (as apprentice_name) és évfolyam nevét (as cohort_name) azoknak a hallgatóknak, akik 2018-as évfolyamokban kezdték (started_at) a tanulmányaikat.
Filename: apprentices-in-2018.sql

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

Írasd ki hány darab olyan évfolyam található az adatbázisban, amelyekhez nincs még hallgató felvéve
Filename: cohorts-without-apprentice.sql

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

Listázd a hallgatók nevét és a képzés kezdődátumát a hallgató neve alapján rendezve ABC sorrendben
Filename: apprentices-start-date-ordered.sql

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

Listázd az évfolyamok nevét és a hallgatók számát (as numberOfApprentices) az adott évfolyamban, a hallgatók számával csökkenő sorrendben. Az az évfolyam is legyen benne, ahol jelenleg 0 hallgató van.
Filename: cohort-sizes.sql

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

###### SQL admin projektfeladatok #####

1) A szerverhez hozzá van adva egy disk, ami még nincs használatban
(20GB). Add hozzá a géphez S: meghajtóként, a kötet neve pedig:
SQLDATA legyen.

2) Az S: meghajtón hozzál létre egy SQLDATA könyvtárat, majd abban
további két alkönyvtárat: DB és LOG névvel.

3) Jelentkezz be az SQL szerverbe (GREENFOX instance)! (Ezt a lépést
nem kötelező elmentened! :-)

4) Töltsd vissza a C:\install\MSSQL\AdventureWorks2016.bak
adatbázist a szerver GREENFOX instance-ába úgy, hogy az adatbázis
állományok az S:\SQLDATA\DB, míg a log állományok az S:\SQLDATA\
LOG könyvtárba kerüljenek.

 
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

5) Hozz létre egy interface_svc MS SQL autentikációjú account-ot,
minimális (public) jogokkal! A jelszava legyen: INTERface.

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

6) Hozz létre egy INTERFACE nevű adatbázist az alábbi beállításokkal:
• Adatbázis neve: INTERFACE
• Tulajdonos: interface_svc
• Recovery model: FULL
• Induló adatbázis file:
(a)méret: 32 MByte
(b)helye: S:\SQLDATA\DB
• Induló log file:
(a) méret: 16 MByte
(b)helye: S:\SQLDATA\LOG

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

7) Adj csak lekérdezési jogot az interface_svc account számára az
AdventureWorks2016 adatbázis HumanResources sémában levő
vEmployee nézettáblájára (VIEW)!

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

8) INTERFACE adatbázisban hozz létre egy AWDB sémát! A szkript
„Create to » formátumú legyen

USE [INTERFACE]
GO

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

9) Az AWDB sémában hozz létre egy szinonimát AW_Employee néven,
ami az AdventureWorks2016 adatbázis HumanResources sémában
levő vEmployee nézettáblára mutat!

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

10) Készíts lekérdezést, ami az INTERFACE adatbázisnak az AWDB
sémájának az AW_Employee szinonimájának minden sorát és oszlopát
kilistázza!

USE [INTERFACE]
GO
SELECT * 
FROM AWDB.AW_Employee

11) Készíts az INTERFACE adatbázisról egy FULL mentést, az alábbiak
szerint:
a) Backup file neve: S:\SQLdata\INTERFACE.bak
b) Mentés felülírja az azonos nevű media set-et!

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

1) Kérdezd le az SQL szerver verzióját és az SQL szerver kiadását!

SELECT @@VERSION

2) Készíts egyszerű „Hello World!” lekérdezést!

SELECT 'Hello World!'

3) Készíts olyan SQL lekérdezést, ami kiszámítja 30 négyzetgyökét!

SELECT SQRT(30)

4) Készíts olyan SQL lekérdezést, ami visszaadja a mai dátumot!

SELECT CAST(GETDATE() as date)

5) Készíts olyan SQL lekérdezést, ami kiszámítja, hogy hány nap telt el
1989. március 15-e óta!

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

6) Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Address táblájának első 20 elemét
(minden mezőjét)!

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

7) Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának első 20 elemét
(minden mezőjét), de úgy, hogy ahol a PersonType mező értéke ‘EM’!

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

8) Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának első 20 elemét, de
úgy, hogy ahol a PersonType mező értéke ‘EM’ és csak a PersonType,
FirstName, LastName mezők legyenek a lekérdezés eredményében!

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

9) Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának első 20 elemét, de
úgy, hogy ahol a PersonType mező értéke ‘EM’ és csak a PersonType,
FirstName, LastName mezők legyenek a lekérdezés eredményében, de a
FirstName és LastName egy oszlopban jelenjen meg egy darab szóköz
karakterrel elválasztva!

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

10)Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának azon rekordjait
(minden mezőjét), ahol a PersonType mező értéke ‘EM’ és a FirstName
mezőben csak a ‘John’ nevűek közül azok legyenek megjelenítve!

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

11)Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának azon rekordjait
(minden mezőjét), ahol a PersonType mező értéke ‘EM’ és a FirstName
mezőben csak a ‘John’ nevűek közül azok legyenek megjelenítve, ahol a
LastName mező értéke ‘e’-karakterrel végződik!

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

12)Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának azon rekordjait
(minden mezőjét), ahol a PersonType mező értéke ‘EM’ és a FirstName
mezőben csak a ‘John’ nevűek közül azok legyenek megjelenítve, ahol a
LastName mező értéke vagy ‘Chen’ vagy ‘Kane’ neveket tartalmazzák!

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

13) Számold meg, hogy hány különböző keresztnév (FirstName) van a
korábban betöltött AdventureWorks adatbázis Person.Person táblájában!

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

14) Számold meg, hogy hány ‘Ken’ keresztnévű (FirstName) rekord van a
korábban betöltött AdventureWorks adatbázis Person.Person táblájában!

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

15) Számold meg, hogy hány azonos nevű (FirstName és LastName) rekord
van a korábban betöltött AdventureWorks adatbázis Person.Person
táblájában! Rendezd az eredményt úgy, hogy a legtöbbet használt
névpáros a lista elején szerepeljen és csak a legelső 20 eredmény
szerepeljen!

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

16) Számold meg, hogy hány azonos nevű (FirstName és LastName) rekord
van a korábban betöltött AdventureWorks adatbázis Person.Person
táblájában! Rendezd az eredményt úgy, hogy csak a 4 és a 4-nél többször
használt névpáros eredménye szerepeljen!

SELECT FirstName, LastName, COUNT(1)
  FROM [AdventureWorks2016].[Person].[Person]
  GROUP BY FirstName, LastName
  HAVING COUNT(1) >= 4
  ORDER BY COUNT(1) DESC

17)Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person és Person.EmailAddress
tábláinak a következő mezőit: Person.Person.FirstName,
Person.Person.LastName, Person.EmailAddress.EmailAddress úgy hogy a
táblák összekapcsolásához a BusinessEntityID mezőt használjuk mindkét
táblából! Az eredmény első 50 sora jelenjen csak meg!

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

18)Az előző lekérdezésből készíts olyan megoldást is, ahol aze-mail címből
(EmailAddress) csak a név rész (@ előtti rész látható)!

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

19)Illetve egy olyan lekérdezést is, ahol csak a domain rész (@ utáni rész)
látható!

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

20) (IZMOS FELADAT) Készíts egy lekérdezést ami visszaadja a
Sales.SalesPerson táblából a BusinessEntityID mezőt és egy „minsales”
nevű számított oszlopot. A minsales értéke legyen soronként a SalesYTD
mező értéke, amennyiben a SalesYTD > 1000000, egyéb esetben NULL
értéket adjon.

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

21) (NAGYON IZMOS FELADAT) Készíts egy lekérdezést ami visszaadja a
Sales.SalesPerson táblából a BusinessEntityID mezőt és egy „salesinfo”
nevű számított oszlopot. A salesinfo értéke legyen soronként a SalesYTD
mező értéke, amennyiben a SalesYTD >= 2000000, Amennyiben
SalesYTD<2000000 de nagyobb vagy egyenlő mint 1000000, akkor az
érték legyen 1500000, Ha 1000000-nál is kisebb akkor pedig NULL. pl.
kimenet :

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

Microsoft SQL szerver (továbbiakban SQL) tudásszint felmérő

  • Használj T-SQL kódot
  • Mindenképp a megadott SQL fájlneveket használd
  • A feltöltött ZIP fájlodban ne legyenek alkönyvtárak, csak a fájlokat tartalmazza
  • Ügyelj rá, hogy az adatbázis-, felhasználó-, és egyéb objektumelnevezések nagybetűérzékenyek
  • A lekérdezésekben a megjelenítendő oszlopok nevén csak akkor változtass, ha a feladat külön előírja
  • A lekérdezésekben a megjelenítendő oszlopok a feladatban megadott sorrendben jelenjenek meg
  • Figyelj oda, hogy mindig a megfelelő adatbázis legyen kiválasztva
  • Használj ASCII, vagy UTF-8 (without BOM) karakterkódolást
  • A Docker konténerben a feladatokat „futtató” MSSQL LOGIN alapértelmezett kiinduló adatbázisa az első 10 feladatban: master.
  • A Docker konténerben a feladatokat „futtató” MSSQL LOGIN alapértelmezett kiinduló adatbázisa 11)-től 20)-es feladattal bezárólag: AdventureWorks.Ezekben a feladatokban elegendő ha a feltöltött fájlod csak a lekérdezést tartalmazza.
  • Az sql kiterjesztésú fájlok kiértékelése elsődlegesen autograder révén történik, a többit manuális pontozással értékeljük.

Feladatok:

  1. A szerverhez hozzá van adva egy diszk, ami még nincs használatban (20GB). Helyezd üzembe, hozzál létre egy 20 GByte-os partíciót NTFS file rendszerrel és a géphez S: meghajtóként mountold fel, a kötet neve pedig: SQLDATA legyen.
Evidencia: Computer Management => Storage => Disk Management
File neve: WINSQL-01.jpg
  1. S: meghajtón hozzál létre egy SQLDATA könyvtárat, majd abban további két alkönyvtárat: DB és LOG névvel.
Evidencia: File Explorer => S: => SQLDATA könyvtárat megnyitva, amikor látszik a kért két alkönyvtár is.
File neve: WINSQL-02.jpg
  1. Jelentkezz be az SQL szerverbe (GREENFOX instance) a Microsoft SQL Server Management Studio-val!
Evidencia: Microsoft SQL Server Management Studio-ban GREENFOX instance megnyitva.
File neve: WINSQL-03.jpg
  1. Töltsd vissza a C:\install\MSSQL\AdventureWorks2016.bak adatbázist a szerver GREENFOX példányába AdventureWorks néven úgy, hogy az adatbázis állományok az S:\SQLDATA\DB, míg a log állományok az S:\SQLDATA\LOG könyvtárba kerüljenek.

Gradescopeban itt találod a backupot: /AdventureWorks2016.bak, az adatbázis állományok: /SQLDATA/DB/AdventureWorks_Data.mdf, míg a log állományok az /SQLDATA/LOG/AdventureWorks_Log.ldf fájlokba kerüljenek

Evidencia: Microsoft SQL Server Management Studio-ban restore típusú szkript kimásolása vagy mentése.
File neve: WINSQL-04.sql
USE [master]
RESTORE DATABASE [AdventureWorks] FROM  DISK = N'/home/AdventureWorks2016.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2016_Data' TO N'/SQLDATA/DB/AdventureWorks_Data.mdf',  MOVE N'AdventureWorks2016_Log' TO N'/SQLDATA/LOG/AdventureWorks_Log.ldf',  NOUNLOAD,  STATS = 5

GO
  1. Hozz létre egy isvc MS SQL Server autentikációjú account-ot, minimális (public) jogokkal! A jelszó legyen: interfAce21 A jelszót ne kelljen megváltoztatnia a felhasználónak első belépéskor.
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-05.sql
USE [master]
GO
CREATE LOGIN [isvc] WITH PASSWORD=N'interfAce21', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
  1. Hozz létre egy INTERFACE nevű adatbázist az alábbi beállításokkal:
    1. Adatbázis neve: INTERFACE
    2. Tulajdonos: isvc
    3. Recovery model: FULL
    4. Induló adatbázis file:
      1. méret: 64 MByte
      2. helye: S:\SQLDATA\DB (Gradescope-on /SQLDATA/DB)
    5. Induló log file:
      1. méret: 24 MByte
      2. helye: S:\SQLDATA\LOG (Gradescope-on /SQLDATA/LOG)
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
  1. Adj csak lekérdezési jogot az isvc account számára az AdventureWorks adatbázis HumanResources sémában levő vEmployee nézettáblájára!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-07.sql
USE [AdventureWorks]
GO
CREATE USER [isvc] FOR LOGIN [isvc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [isvc]
GO
  1. INTERFACE adatbázisban hozz létre egy AWdatabase sémát!
Evidencia: Microsoft SQL Server Management Studio-ban GREENFOX instance-ban az INTERFACE adatbázis AWdatabase séma „Create To” script.
File neve: WINSQL-08.sql
USE [INTERFACE]
GO

/****** Object:  Schema [dbo]    Script Date: 2023. 01. 15. 13:45:32 ******/
CREATE SCHEMA [AWdatabase]
GO
  1. Az AWdatabase sémában hozz létre egy szinonímát AW_Employee néven, ami az AdventureWorks adatbázis HumanResources sémában levő vEmployee nézettáblára mutat!
Evidencia: Microsoft SQL Server Management Studio-ban GREENFOX instance-ban az INTERFACE adatbázis AWdatabase séma AW_Employee szinoníma „Create To” script.
File neve: WINSQL-09.sql
USE [INTERFACE]
GO
CREATE SYNONYM [AWdatabase].[AW_Employee ] FOR [AdventureWorks].[HumanResources].[vEmployee]
GO
  1. Készíts az INTERFACE adatbázisról egy FULL mentést, az alábbiak szerint:
  1. Backup file neve: S:\SQLDATA\INTERFACE.bak (Gradescope-ban /var/backups/INTERFACE.bak)
  2. Mentés felülírja az azonos nevű media set-et!
Evidencia: Microsoft SQL Server Management Studio-ban GREENFOX instance-ban az INTERFACE adatbázis backup script.
File neve: WINSQL-10.sql
BACKUP DATABASE [INTERFACE] TO  DISK = N'/var/backups/INTERFACE.bak' WITH NOFORMAT, INIT,  NAME = N'INTERFACE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
  1. Készíts olyan SQL lekérdezést, ami kiszámítja 25 négyzetgyökét!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-11.sql
SELECT SQRT(25)
  1. Készíts olyan SQL lekérdezést, ami visszaadja a mai dátumot ebben a formátumban: 2022-10-25
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-12.sql
SELECT CAST(GETDATE() as date)
  1. Készíts olyan SQL lekérdezést, ami kiszámítja, hogy hány nap telt el 1994. április 25-e óta!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-13.sql
SELECT DATEDIFF(day,'2022-04-25',GETDATE())
  1. Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Address táblájának első 15 elemét (minden mezőjét – az oszlopok sorrendjén ne változtass)!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-14.sql
/****** Script for SelectTopNRows command from SSMS  ******/
USE [AdventureWorks]
GO
SELECT TOP (15) *
  FROM [AdventureWorks].[Person].[Address]
  1. Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának első 5 elemét (minden mezőjét – az oszlopok sorrendjén ne változtass -), de úgy, hogy ahol a PersonType mező értéke ‘EM‘!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-15.sql
USE [AdventureWorks]
GO
SELECT TOP (5) *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'
  1. Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának első 30 elemét, de úgy, hogy ahol a PersonType mező értéke ‘EM‘ és csak a PersonType, FirstName, LastName mezők legyenek a lekérdezés eredményében!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-16.sql
USE [AdventureWorks]
GO
SELECT TOP (30) PersonType, FirstName,LastName
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'
  1. Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának első 20 elemét, de úgy, hogy ahol a PersonType mező értéke ‘EM‘ és csak a PersonType, FirstName, LastName mezők legyenek a lekérdezés eredményében, de a FirstName és LastName egy oszlopban jelenjen meg egy darab szóköz karakterrel elválasztva, FullName néven!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-17.sql
USE [AdventureWorks]
GO
SELECT TOP (20) PersonType, FirstName+ ' ' + LastName as FullName
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM'
  1. Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának azon rekordjait (minden oszlopát, minden mezőt – az oszlopok sorrendjén ne változtass -), ahol a PersonType mező értéke ‘EM‘ és a FirstName mezőben csak a ‘John‘ nevűek szerepeljenek!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-18.sql
USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John'
  1. Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának azon rekordjait (minden oszlopát, minden mezőt – az oszlopok sorrendjén ne változtass -), ahol a PersonType mező értéke ‘EM‘ és a FirstName mezőben csak a ‘John‘ nevűek közül is azok legyenek csak megjelenítve, ahol a LastName mező értéke ‘e‘-karakterrel végződik!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-19.sql
USE [AdventureWorks]
GO
SELECT *
  FROM [AdventureWorks].[Person].[Person]
  WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'
  1. Számold meg, hogy hány ‘Ken‘ keresztnevű (FirstName) rekord van a korábban betöltött AdventureWorks adatbázis Person.Person táblájában!
Evidencia: Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése.
File neve: WINSQL-20.sql
USE [AdventureWorks]
GO
SELECT COUNT(*)
  FROM [AdventureWorks].[Person].[Person]
  WHERE FirstName='Ken'