# KBaz 1

-- Zadanie 1

-- SELECT DISTINCT addres.[City]
-- FROM [SalesLT].[SalesOrderHeader] ship INNER JOIN [SalesLT].[Address] addres
-- ON ship.ShipToAddressID = addres.[AddressID]
-- WHERE ship.[Status] = 5
-- ORDER BY addres.[City]

-- SELECT addres.[City]
-- FROM [SalesLT].[SalesOrderHeader] ship INNER JOIN [SalesLT].[Address] addres
-- ON ship.ShipToAddressID = addres.[AddressID]
-- WHERE ship.[Status] = 5
-- GROUP BY addres.City
-- ORDER BY addres.[City]

-- Zadanie 2

-- SELECT m.Name, COUNT(m.Name) as Number FROM [SalesLT].[ProductModel] m
-- RIGHT JOIN [SalesLT].[Product] p
-- ON m.[ProductModelID] = p.[ProductModelID]
-- GROUP BY m.Name
-- HAVING COUNT(m.Name) > 1

-- Zadanie 3

-- SELECT addr.City, COUNT(DISTINCT c.[CustomerID]) as Customers, COUNT(DISTINCT c.SalesPerson) as SalesPerson
-- FROM [SalesLT].[Customer] c, [SalesLT].[CustomerAddress] cA, [SalesLT].[Address] addr
-- WHERE c.[CustomerID] = cA.[CustomerID] AND cA.[AddressID] = addr.[AddressID]
-- GROUP BY addr.City

-- Zadanie 4

-- WITH NOLEAF (ProductCategoryID,ParentProductCategoryID,Name) AS
-- (
-- SELECT ProductCategoryID,ParentProductCategoryID,Name FROM [SalesLT].[ProductCategory]
-- WHERE ParentProductCategoryID IS NULL
-- UNION ALL
-- SELECT cur.ProductCategoryID,cur.ParentProductCategoryID,cur.Name FROM [SalesLT].[ProductCategory] cur
-- INNER JOIN NOLEAF nol
-- ON nol.ParentProductCategoryID = cur.ProductCategoryID
-- )

-- SELECT mod.Name as Category, pro.Name as Product FROM NOLEAF mod
-- INNER JOIN [SalesLT].[Product] pro ON pro.[ProductCategoryID] = mod.ProductCategoryID

-- Zadanie 5

-- SELECT c.[FirstName], c.[LastName], ISNULL(SUM(sOD.[UnitPriceDiscount] * sOD.[OrderQty]),0) as Discount
-- FROM [SalesLT].[Customer] c
-- LEFT JOIN [SalesLT].[SalesOrderHeader] sH ON c.[CustomerID] = sH.[CustomerID]
-- LEFT JOIN [SalesLT].[SalesOrderDetail] sOD ON sH.[SalesOrderID] = sH.[SalesOrderID]
-- GROUP BY c.[CustomerID], c.[FirstName], c.[LastName]

-- Zadanie 6
--GETDATE()

-- DROP TABLE OrdersToProcess

-- CREATE TABLE OrdersToProcess
-- (
-- SalesOrderID INT PRIMARY KEY,
-- Delayed BIT
-- );
-- GO

-- MERGE OrdersToProcess AS TARGET
-- USING [SalesLT].[SalesOrderHeader] AS SOURCE
-- ON TARGET.SalesOrderID = SOURCE.SalesOrderID
-- WHEN MATCHED THEN UPDATE SET TARGET.Delayed=(CASE WHEN SOURCE.[DueDate] < CONVERT(DATETIME, '2009-09-25') THEN 1 ELSE 0 END)
-- WHEN NOT MATCHED THEN INSERT VALUES(SOURCE.SalesOrderID,(CASE WHEN SOURCE.[DueDate] < CONVERT(DATETIME, '2009-09-25') THEN 1 ELSE 0 END));

-- SELECT * FROM OrdersToProcess
-- WHERE SalesOrderID = 71774

-- SELECT * FROM [SalesLT].[SalesOrderHeader]
-- WHERE SalesOrderID = 71774

-- Zadanie 7

-- DROP TABLE IDEN_TABLE1
-- DROP TABLE IDEN_TABLE2

-- CREATE TABLE IDEN_TABLE1
-- (
-- ID INT IDENTITY(1000,10),
-- Name VARCHAR(100)
-- )

-- CREATE TABLE IDEN_TABLE2
-- (
-- ID INT IDENTITY(1000,10),
-- Name VARCHAR(100)
-- )

-- INSERT IDEN_TABLE1 VALUES ('MMM')
-- INSERT IDEN_TABLE1 VALUES ('DDD')

-- PRINT @@IDENTITY -- Zwraca ostatnią wstawioną wartość
-- PRINT IDENT_CURRENT('IDEN_TABLE1') -- Zwraca ostatnią wstawioną wartość w tabeli

-- INSERT IDEN_TABLE2 VALUES ('AAA')

-- PRINT @@IDENTITY -- Zwraca ostatnią wstawioną wartość
-- PRINT IDENT_CURRENT('IDEN_TABLE1') -- Zwraca ostatnią wstawioną wartość w tabeli
-- PRINT IDENT_CURRENT('IDEN_TABLE2') -- Zwraca ostatnią wstawioną wartość w tabeli

-- Zadanie 8
-- SELECT definition FROM sys.check_constraints
-- WHERE name LIKE 'CK_SalesOrderHeader_ShipDate'

-- DROP TABLE IF EXISTS DATE_TABLE

-- CREATE TABLE DATE_TABLE
-- (
-- ID INT IDENTITY,
-- ShipDate DATETIME,
-- OrderDate DATETIME
-- )

-- ALTER TABLE DATE_TABLE
-- ADD CONSTRAINT SomeName CHECK (ShipDate>=OrderDate OR [ShipDate] IS NULL)

-- INSERT INTO DATE_TABLE VALUES ('2020-01-01','2020-01-01')
-- --INSERT INTO DATE_TABLE VALUES ('2000-01-01','2020-01-01')

-- alter table DATE_TABLE nocheck constraint SomeName
-- INSERT INTO DATE_TABLE VALUES ('2000-01-01','2020-01-01')
-- alter table DATE_TABLE check constraint SomeName
-- dbcc checkconstraints ('DATE_TABLE')

-- SELECT * FROM DATE_TABLE

-- Zadanie 9

-- ALTER TABLE [SalesLT].[Customer]
-- ADD CreditCardNumber CHAR

-- GO

-- UPDATE [SalesLT].[SalesOrderHeader] SET [CreditCardApprovalCode] = 000 WHERE SalesOrderID = 71774
-- UPDATE [SalesLT].[SalesOrderHeader] SET [CreditCardApprovalCode] = 000 WHERE SalesOrderID = 71776
-- UPDATE [SalesLT].[SalesOrderHeader] SET [CreditCardApprovalCode] = 000 WHERE SalesOrderID = 71780

-- UPDATE [SalesLT].[Customer]
-- SET CreditCardNumber = 'X'
-- WHERE CustomerID IN (SELECT CustomerID FROM [SalesLT].[SalesOrderHeader] WHERE CreditCardApprovalCode IS NOT NULL)

-- -- SELECT * FROM [SalesLT].[SalesOrderHeader]
-- -- ORDER BY SalesOrderID

-- SELECT CreditCardNumber,COUNT(CustomerID) FROM [SalesLT].[Customer]
-- GROUP BY CreditCardNumber

-- Zadanie 10

-- DROP TABLE IF EXISTS S1
-- DROP TABLE IF EXISTS M1
-- DROP TABLE IF EXISTS S2
-- DROP TABLE IF EXISTS M2

-- CREATE TABLE M1
-- (
-- K INT PRIMARY KEY,
-- V VARCHAR(20)
-- )

-- CREATE TABLE S1
-- (
-- K INT PRIMARY KEY,
-- MFK INT FOREIGN KEY REFERENCES M1(K),
-- V VARCHAR(20)
-- )

-- INSERT INTO M1 VALUES (0,'Ala');
-- INSERT INTO M1 VALUES (1,'ma');
-- INSERT INTO M1 VALUES (2,'kota');

-- INSERT INTO S1 VALUES (0,0,'Bob');
-- INSERT INTO S1 VALUES (1,1,'ma');
-- INSERT INTO S1 VALUES (2,2,'dużego');
-- GO
-- INSERT INTO S1 VALUES (3,3,'psa');
-- GO

-- CREATE TABLE M2
-- (
-- K1 INT,
-- K2 INT,
-- V VARCHAR(20),
-- CONSTRAINT DUOPRIM PRIMARY KEY(K1,K2)
-- )

-- CREATE TABLE S2
-- (
-- K INT PRIMARY KEY,
-- MFK1 INT,
-- MFK2 INT,
-- V VARCHAR(20),
-- CONSTRAINT DUOFOR FOREIGN KEY(MFK1,MFK2) REFERENCES M2(K1,K2)
-- )

-- INSERT INTO M2 VALUES (0,0,'Ala');
-- INSERT INTO M2 VALUES (0,1,'ma');
-- INSERT INTO M2 VALUES (1,0,'kota');

-- INSERT INTO S2 VALUES (0,0,0,'Bob');
-- INSERT INTO S2 VALUES (1,0,1,'ma');
-- INSERT INTO S2 VALUES (2,1,0,'dużego');
-- GO
-- INSERT INTO S2 VALUES (3,1,1,'psa');
-- GO

-----------------------------
-- DROP TABLE IF EXISTS S1
-- DROP TABLE IF EXISTS M1
-- DROP TABLE IF EXISTS S2
-- DROP TABLE IF EXISTS M2

-- CREATE TABLE M1
-- (
-- K INT PRIMARY KEY,
-- V VARCHAR(20)
-- )

-- CREATE TABLE S1
-- (
-- K INT PRIMARY KEY,
-- MFK INT FOREIGN KEY
-- REFERENCES M1(K)
-- -- ON UPDATE NO ACTION
-- -- ON DELETE NO ACTION,
-- -- ON UPDATE SET NULL
-- -- ON DELETE SET NULL,
-- ON UPDATE CASCADE
-- ON DELETE CASCADE,
-- V VARCHAR(20)
-- )

-- INSERT INTO M1 VALUES (0,'Ala');
-- INSERT INTO M1 VALUES (1,'ma');
-- INSERT INTO M1 VALUES (2,'kota');

-- INSERT INTO S1 VALUES (0,0,'Bob');
-- INSERT INTO S1 VALUES (1,1,'ma');
-- INSERT INTO S1 VALUES (2,2,'dużego');

-- SELECT * FROM S1;
-- UPDATE M1 SET K=4 WHERE K=0
-- DELETE M1 WHERE K=1
-- SELECT * FROM S1;