--Zad. 1
--W bazie danych należy utworzyć schemat, którego nazwa będzie odpowiadać nazwisku wykonują-cego ćwiczenie
--(zapisać zapytanie tworzące ten schemat).
USE AdventureWorks2014
GO
CREATE SCHEMA Mandziejewska AUTHORIZATION dbo
GO

--Zad. 2
--W nowo utworzonym schemacie utworzyć tabele (zapisać skrypt create table), opisane w następujących podpunktach:
--•	DIM_CUSTOMER (CustomerID, FirstName, LastName, TerritoryName, CounrtyRegionCode, Group) – tabele źródłowe:
--·	Sales.SalesTerritory
--·	Sales.Customer
--·	Person.Person

 	USE AdventureWorks2014
 	GO
 	CREATE TABLE Mandziejewska.DIM_CUSTOMER(
 	CustomerID int NOT NULL,
 	FirstName nvarchar(50)  NULL,
 	LastName nvarchar(50)  NULL,
 	TerritoryName nvarchar(50)  NULL,
 	CountryRegionCode nvarchar(3) NOT NULL,
 	[Group] nvarchar(50) NOT NULL);
 	GO

--•	DIM_PRODUCT (ProductID, Name, ListPrice, Color, Rating, SubCategoryName, Catego-ryName) – tabele źródłowe:
--·	Production.Product
--·	Production.ProductReview
--·	Production.ProductSubcategory
--·	Production.ProductCategory

	USE AdventureWorks2014
	GO
	CREATE TABLE Mandziejewska.DIM_PRODUCT(
	ProductID int NOT NULL,
	[Name] nvarchar(50) NOT NULL,
	ListPrice money NOT NULL,
	Color nvarchar(15) NULL,
	Rating float NULL,
	SubCategoryName nvarchar(50) NULL,
	CategoryName nvarchar(50) NULL);
	GO

--•	DIM_SALESPERSON (SalesPersonID, FirstName, LastName, Title, Gender, CountryRe-gionCode, Group, Age,
--Seniority) – tabele źródłowe:
--·	HumanResources.Employee
--·	Person.Person
--·	Sales.SalesTerritory
--·	Sales.SalesPerson
--Uwaga: Wiek sprzedawcy i staż pracy należy wyliczyć wykorzystując pole BirthDate i HireDate.

USE AdventureWorks2014
GO
CREATE TABLE Mandziejewska.DIM_SALESPERSON(
SalesPersonID int NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
Title nvarchar(8) NULL,
Gender nchar(1) NOT NULL,
CountryRegionCode nvarchar(3) NULL,
[Group] nvarchar(50) NULL,
Age int NOT NULL,
Seniority int NOT NULL
);
GO

--•	FACT_SALES (ProductID, CustomerID, SalesPersonID, OrderDate, ShipDate, OrderQty, UnitPrice,
--UnitPriceDiscount, LineTotal) – tabele źródłowe:
--·	Sales.SalesOrderDetail
--·	Sales.SalesOrderHeader
--Uwaga: Kolumny OrderDate oraz ShipDate przechowują dane typu całkowitego,
--gdzie cztery pierwsze cyfry oznaczają rok, dwie następne miesiąc, a dwie ostatnie dzień.
--Do pobrania po-szczególnych części daty użyć funkcji datepart.

USE AdventureWorks2014
GO
CREATE TABLE Mandziejewska.FACT_SALES(
ProductID int NOT NULL,
CustomerID int NOT NULL,
SalesPersonID int NULL,
OrderDate int NOT NULL,
ShipDate int NULL,
OrderQty smallint NOT NULL,
UnitPrice money NOT NULL,
UnitPriceDiscount money NOT NULL,
LineTotal numeric(38,6) NOT NULL
);
GO

--Zad. 3. Wypełnić nowoutworzone tabele danymi znajdującymi się w tabelach źródłowych.
--Do wy-pełnienia użyć instrukcji INSERT INTO.
--Uwaga 1. Do tabeli DIM_PRODUCT należy także skopiować produkty, które nie mają przypi-sanej
--podkategorii.
--Uwaga 2. Do tabeli FACT_SALES należy skopiować transakcje, które nie mają sprzedawcy.

USE AdventureWorks2014
GO
INSERT INTO Mandziejewska.DIM_CUSTOMER
SELECT
C.CustomerID,
P.FirstName,
P.LastName,
T.[Name],
T.CountryRegionCode,
T.[Group]
FROM Sales.SalesTerritory T
RIGHT JOIN Sales.Customer C ON T.TerritoryID=C.TerritoryID
LEFT JOIN Person.Person P ON P.BusinessEntityID=C.CustomerID;
GO

USE AdventureWorks2014
GO
INSERT INTO Mandziejewska.DIM_PRODUCT
SELECT
distinct pp.ProductID,
pp.Name,
pp.ListPrice,
pp.Color,
AVG(pr.Rating) OVER (Partition by pr.ProductID),
ps.Name,
pc.Name
FROM [Production].[ProductReview] pr RIGHT JOIN [Production].[Product] pp ON pp.ProductID = pr.ProductID
LEFT JOIN [Production].[ProductSubcategory] ps ON pp.ProductSubcategoryID = ps.ProductSubcategoryID
LEFT JOIN [Production].[ProductCategory] pc ON pc.ProductCategoryID = ps.ProductCategoryID

--•	DIM_SALESPERSON (SalesPersonID, FirstName, LastName, Title, Gender, CountryRe-gionCode, Group, Age,
--Seniority) – tabele źródłowe:
--·	HumanResources.Employee
--·	Person.Person
--·	Sales.SalesTerritory
--·	Sales.SalesPerson
--Uwaga: Wiek sprzedawcy i staż pracy należy wyliczyć wykorzystując pole BirthDate i HireDate.

USE AdventureWorks2014
GO
INSERT INTO Mandziejewska.DIM_SALESPERSON
SELECT
S.BusinessEntityID,
P.FirstName,
P.LastName,
P.Title,
H.Gender,
T.CountryRegionCode,
T.[Group],
DATEDIFF(YY,H.BirthDate,GETDATE()),
DATEDIFF(YY,H.HireDate, GETDATE())
FROM Sales.SalesPerson S JOIN Person.Person P ON S.BusinessEntityID=P.BusinessEntityID
JOIN HumanResources.Employee H ON H.BusinessEntityID=P.BusinessEntityID
LEFT JOIN Sales.SalesTerritory T ON T.TerritoryID=S.TerritoryID;


USE AdventureWorks2014
GO
INSERT INTO Mandziejewska.FACT_SALES
SELECT
D.ProductID,
H.CustomerID,
H.SalesPersonID,
CAST(CONCAT
		(datepart(YY, H.OrderDate),
		CASE WHEN datepart(M, H.OrderDate) < 10 THEN '0' ELSE '' END,
		datepart(M, H.OrderDate),
		CASE WHEN datepart(D, H.OrderDate) < 10 THEN '0' ELSE '' END,
		 datepart(D, H.OrderDate)
		)
		AS int),

	CAST(CONCAT
		(datepart(YY, H.ShipDate),
		CASE WHEN datepart(M, H.ShipDate) < 10 THEN '0' ELSE '' END,
		datepart(M, H.ShipDate),
		CASE WHEN datepart(D, H.ShipDate) < 10 THEN '0' ELSE '' END,
		datepart(D, H.ShipDate)
		)
		AS int),
	D.OrderQty,
	D.UnitPrice,
	D.UnitPriceDiscount,
	D.LineTotal
FROM Sales.SalesOrderDetail D JOIN Sales.SalesOrderHeader H ON D.SalesOrderID = H.SalesOrderID;
GO

--Zad. 4. Dodać integralność referencyjną i klucze główne do tabel już zdefiniowanych.


USE AdventureWorks2014
GO
ALTER TABLE Mandziejewska.DIM_CUSTOMER
ADD CONSTRAINT pk_dim_customer PRIMARY KEY(CustomerID);
GO

ALTER TABLE Mandziejewska.DIM_PRODUCT
ADD CONSTRAINT pk_dim_product PRIMARY KEY(ProductID);
GO

ALTER TABLE Mandziejewska.DIM_SALESPERSON
ADD CONSTRAINT pk_dim_salesperson PRIMARY KEY (SalesPersonID);

ALTER TABLE Mandziejewska.FACT_SALES
ADD FactSalesID int NOT NULL IDENTITY(1,1) PRIMARY KEY;
GO


ALTER TABLE Mandziejewska.FACT_SALES
ADD CONSTRAINT fk_fact_product FOREIGN KEY (ProductID) REFERENCES Mandziejewska.DIM_Product(ProductID),
CONSTRAINT fk_fact_customer FOREIGN KEY (CustomerID) REFERENCES Mandziejewska.DIM_CUSTOMER(CustomerID),
CONSTRAINT fk_fact_salesperson FOREIGN KEY (SalesPersonID) REFERENCES Mandziejewska.DIM_SALESPERSON(SalesPersonID);
GO

--Zad. 5
--Przygotować instrukcję INSERT INTO, która sprawdzi poprawność integralności referencyjnej
--oraz klucze główne.

--klucze główne
INSERT INTO Mandziejewska.DIM_CUSTOMER VALUES(1,'Karol','Kowalski','New York','US','South America');
--Violation of PRIMARY KEY constraint 'pk_dim_customer'. Cannot insert duplicate key in object 'Mandziejewska.DIM_CUSTOMER'. The duplicate key value is (1).

INSERT INTO Mandziejewska.DIM_PRODUCT VALUES(1,'bike','100','pink', 5, NULL, 'Bikes');
--Violation of PRIMARY KEY constraint 'pk_dim_product'. Cannot insert duplicate key in object 'Mandziejewska.DIM_PRODUCT'. The duplicate key value is (1).
INSERT INTO Mandziejewska.DIM_SALESPERSON VALUES(274,'Dawid','Nowak', 'Mr.','M',NULL,NULL,34,5);
--Violation of PRIMARY KEY constraint 'pk_dim_salesperson'. Cannot insert duplicate key in object 'Mandziejewska.DIM_SALESPERSON'. The duplicate key value is (274).
SET IDENTITY_INSERT Mandziejewska.FACT_SALES ON;
INSERT INTO Mandziejewska.FACT_SALES(ProductID,CustomerID,SalesPersonID,OrderDate,ShipDate,OrderQty,UnitPrice,UnitPriceDiscount,LineTotal,FactSalesID) VALUES(10,220,125,20150503,NULL,1,50,0,30,4);
--Violation of PRIMARY KEY constraint 'PK__FACT_SAL__AA8B53276D5C82BB'. Cannot insert duplicate key in object 'Mandziejewska.FACT_SALES'. The duplicate key value is (4).

-- integralność referencyjna

SET IDENTITY_INSERT Mandziejewska.FACT_SALES OFF;

INSERT INTO Mandziejewska.FACT_SALES VALUES(100000,333,NULL,20150503,NULL,1,20,0,10);
--The INSERT statement conflicted with the FOREIGN KEY constraint "fk_fact_product". The conflict occurred in database "AdventureWorks2014", table "Mandziejewska.DIM_PRODUCT", column 'ProductID'.

INSERT INTO Mandziejewska.FACT_SALES VALUES(1,50119,NULL,20150503,NULL,1,20,0,10);
--The INSERT statement conflicted with the FOREIGN KEY constraint "fk_fact_customer". The conflict occurred in database "AdventureWorks2014", table "Mandziejewska.DIM_CUSTOMER", column 'CustomerID'.

INSERT INTO Mandziejewska.FACT_SALES VALUES(1,1,291,20150503,NULL,1,20,0,0);
--The INSERT statement conflicted with the FOREIGN KEY constraint "fk_fact_salesperson". The conflict occurred in database "AdventureWorks2014", table "Mandziejewska.DIM_SALESPERSON", column 'SalesPersonID'.


--Zad 6
--Przygotować instrukcję usuwającą każdą z tabel utworzonych w trakcie dotychczasowej pracy.
--Uwaga: Instrukcja powinna być wykonana tylko pod warunkiem istnienia usuwanej tabeli. Należy sprawdzić, czy dana tabela istnieje,
--używając instrukcji IF oraz informacji zawartych w widoku systemowym INFORMATION_SCHEMA.TABLES.

USE AdventureWorks2014
GO
IF(SELECT COUNT (*) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Mandziejewska' AND TABLE_NAME='FACT_SALES')>0
DROP TABLE Mandziejewska.FACT_SALES;
GO

IF(SELECT COUNT (*) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Mandziejewska' AND TABLE_NAME='DIM_CUSTOMER')>0
DROP TABLE Mandziejewska.DIM_CUSTOMER;
GO
IF(SELECT COUNT (*) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Mandziejewska' AND TABLE_NAME='DIM_PRODUCT')>0
DROP TABLE Mandziejewska.DIM_PRODUCT;
GO

IF(SELECT COUNT (*) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Mandziejewska' AND TABLE_NAME='DIM_SALESPERSON')>0
DROP TABLE Mandziejewska.DIM_SALESPERSON;
GO