---
# System prepended metadata

title: KBaz 1

---

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