use master;
GO

IF DB_ID('zapocet_eshop') is not null
	DROP DATABASE zapocet_eshop;
GO

CREATE DATABASE zapocet_eshop;
GO

USE zapocet_eshop;
GO

CREATE TABLE Zakaznik
( 
    idZ		INT  NOT NULL, -- cislo zakaznika
	meno		VARCHAR(10),
	priezvisko	VARCHAR(15),
	pohlavie	CHAR(1),
	datum_narodenia DATE,
	vyska DEC(4,1) CHECK (vyska BETWEEN 30.0 AND 250.0), 
	vaha DECIMAL(4,1) -- To iste ako DEC(4,1)
);
GO

CREATE TABLE Zakaznik2
( 
    idZ		INT  NOT NULL IDENTITY PRIMARY KEY, -- cislo zakaznika
	meno		VARCHAR(10),
	priezvisko	VARCHAR(15),
	pohlavie	CHAR(1),
	datum_narodenia DATE,
	vyska DEC(4,1) CHECK (vyska BETWEEN 30.0 AND 250.0), 
	vaha DECIMAL(4,1) -- To iste ako DEC(4,1)
);
GO

CREATE TABLE Tovar
( 
  idT		INT  NOT NULL, -- cislo tovaru
  nazov	VARCHAR(15),
  cena	DEC(10,2),
  pocet INT NOT NULL DEFAULT 100 CHECK (pocet >=0)
);

CREATE TABLE Nakup
( 
  idN		INT  NOT NULL, -- cislo nakupu
  idZ		INT, -- cislo zakaznika
  idT		INT, -- cislo tovaru
  den		DATE,
  pocet INT NOT NULL DEFAULT 1 CHECK (pocet > 0)
);

CREATE TABLE Logistika(
	od VARCHAR(3),
	do VARCHAR(3)
);


INSERT Zakaznik VALUES( 1, 'Jan',       'Hrasko',	'm', '1997.7.12', 180.0, 80.0);
INSERT Zakaznik VALUES( 2, 'Ruzena',	'Sipka',	'z', '2004.2.1', 160.0, 60.0);
INSERT Zakaznik VALUES( 3, 'Aladar',	'Baba',		'm', '2000.1.22', 175.5, 75);
INSERT Zakaznik VALUES( 4, 'Ferdinand', 'Mravec',	'm', '2004.3.3', 155.0, 99);
INSERT Zakaznik VALUES( 5, 'Jan',	    'Polienko',	'm', '2002.4.14', 199.5, NULL);
INSERT Zakaznik VALUES( 6, 'Juraj',	    'Trulo',	'm', '1999.7.16', 172.5, 57.5);
INSERT Zakaznik VALUES( 7, 'Jana',	    'Botkova',	'z', '1997.9.5', 182.5, 89.5);
INSERT Zakaznik VALUES( 8, 'Dana',	    'Botkova',	'z', '1997.9.5', 167.0, 88);
INSERT Zakaznik VALUES( 9, 'Jan',	    'Hlupy',	'm',  NULL, 179.5, 78.5);
INSERT Zakaznik VALUES(10, 'Aladar',	'Miazga',	'm', '1997.12.22', 193.0, 110.5);
INSERT Zakaznik VALUES(11, 'Mikulas',   'Mysiak',	'm', '2003.6.6', 156.5, 45.5);
INSERT Zakaznik VALUES(12, 'Donald',    'Kacer',	'm', '2002.10.7', 167.0, 55.0);
INSERT Zakaznik VALUES(13, 'Pepek',	    'Namornik',	'm', '2001.9.23', NULL, NULL);

INSERT Tovar VALUES	( 1, 'Fotoaparat',  170.53, 100 );
INSERT Tovar VALUES	( 2, 'Televizor',   241.50, 100 );
INSERT Tovar VALUES	( 3, 'Telefon',	    197.00, 100 );
INSERT Tovar VALUES	( 4, 'Notebook',    530.98, 100 );
INSERT Tovar VALUES	( 5, 'Tablet',	    122.71, 100 );
INSERT Tovar VALUES	( 6, 'Pracka',	    285.10, 100 );
INSERT Tovar VALUES	( 7, 'Chladnicka',  296.00, 100 );
INSERT Tovar VALUES	( 8, 'Vysavac',	    61.85, 100 );
INSERT Tovar VALUES	( 9, 'Bicykel',	    268.99, 100 );
INSERT Tovar VALUES	(10, 'Gril',	   69.90, 100 );

INSERT Nakup VALUES	(1, 1, 2, '2022.5.5', 1 );
INSERT Nakup VALUES	(2, 2, 3, '2022.5.5', 1 );
INSERT Nakup VALUES	(3, 6, 3, '2022.5.5', 1);
INSERT Nakup VALUES	(4, 4, 1, '2022.6.5', 1);
INSERT Nakup VALUES	(5, 5, 4, '2022.6.5', 1 );
INSERT Nakup VALUES	(6, 7, 1, '2022.6.5', 1 );
INSERT Nakup VALUES	(7, 6, 1, '2022.6.5', 1 );
INSERT Nakup VALUES	(8, 8, 3, '2022.7.5', 1 );
INSERT Nakup VALUES	(9, 2, 1, '2022.7.5', 1 );
INSERT Nakup VALUES	(10,3, 3, '2022.7.5', 1 );
INSERT Nakup VALUES	(11,6, 2, '2022.8.5', 1 );
INSERT Nakup VALUES	(12,7, 2, '2022.8.5', 1 );
INSERT Nakup VALUES	(13,6, 4, '2022.8.5', 1 );
INSERT Nakup VALUES	(14,2, 1, '2022.9.5', 1 );
INSERT Nakup VALUES	(15,3, 1, '2022.9.5', 1 );
INSERT Nakup VALUES	(16,8, 1, '2022.9.5', 1 );
INSERT Nakup VALUES	(17,9, 5, '2022.9.5', 1 );
INSERT Nakup VALUES	(18,7, 1, '2022.10.5', 1 );
INSERT Nakup VALUES	(19,8, 4, '2022.10.5', 1 );
INSERT Nakup VALUES	(20,10,5, '2022.10.5', 1 );
INSERT Nakup VALUES	(21,1, 1, '2022.11.5', 1 );
INSERT Nakup VALUES	(22,6, 5, '2022.11.5', 5 );

INSERT INTO Logistika VALUES
	('TV','MI'),
	('KE','TV'),
	('KE','VT'),
	('KE','GL'),
	('VT','HE'),
	('VT','BJ'),
	('BJ','PO'),
	('GL','PO'),
	('GL','SN'),
	('SN','PO'),
	('PP','LE'),
	('LE','PO');


SELECT * FROM Zakaznik;
SELECT * FROM Zakaznik2;
SELECT * FROM Tovar;
SELECT * FROM Nakup;
SELECT * FROM Logistika;

-- SELECT name FROM sys.key_constraints;
-- ALTER TABLE Zakaznik DROP CONSTRAINT PK_Zakaznik__DC50