while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))

begin


	declare @sql nvarchar(2000)


	SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME


	+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')


	FROM information_schema.table_constraints


	WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'


	exec (@sql)


end

IF OBJECT_ID('dbo.Subjectparticipants') IS NOT NULL
DROP TABLE dbo.Subjectparticipants


IF OBJECT_ID('dbo.StudentsGroup') IS NOT NULL
DROP TABLE dbo.StudentsGroup



IF OBJECT_ID('dbo.Students') IS NOT NULL
DROP TABLE dbo.Students

IF OBJECT_ID('dbo.Subjects') IS NOT NULL
DROP TABLE dbo.Subjects







create table Students (
StudentID int not null primary key identity(1,1),
EnrollmentID char(7) not null,
Firstname varchar(20) not null,
Lastname varchar(30) not null,
Birthdate date not null,
Sex varchar(1) default 'M' check(Sex = 'M' or Sex = 'K')
)

ALTER TABLE dbo.Students WITH CHECK ADD CONSTRAINT [Difference] CHECK (DATEDIFF(year,GetDate(),Students.Birthdate) <= 40);




create table Subjects (
SubjectID int primary key not null,
SubjectName varchar(30) Unique not null
)




create table Subjectparticipants (
SubjectparticipantID int not null primary key,
Participationbeginning date null,
Participationend date null,
StudentID_FK int not null,
SubjectID_FK int not null,
foreign key (StudentID_FK) references Students(StudentID) ON DELETE CASCADE,
foreign key (SubjectID_FK) references Subjects(SubjectID) ON DELETE CASCADE
)
ALTER TABLE dbo.SUBJECTPARTICIPANTS WITH CHECK ADD CONSTRAINT [ParticipationEnd] CHECK (([ParticipationEnd]>[ParticipationBeginning]));



create table StudentsGroup (
GroupID int not null primary key,
SubjectID_FK int not null,
Foremanname varchar(50),
Weekday varchar(20) not null,
BeginingTime time not null,
foreign key (SubjectID_FK) references Subjects(SubjectID) ON DELETE CASCADE
)

IF OBJECT_ID('dbo.GroupParticipants') IS NOT NULL
DROP TABLE dbo.GroupParticipants

create table GroupParticipants(
	GroupParticipantID int not null primary key,
	ParticipationBegining date,
	ParticipationEnd date,
	GroupID_FK int not null,
	StudentID_FK int not null,
	foreign key (StudentID_FK) references Students(StudentID) ON DELETE CASCADE,
	foreign key (GroupID_FK) references StudentsGroup(GroupID)ON DELETE CASCADE
)


INSERT Subjects values ('1', 'matematyka'), ('2', 'biologia'), ('3', 'chemia'), ('4', 'historia')



INSERT dbo.Students ( EnrollmentID, Firstname, Lastname, Birthdate, Sex)
values
	('1', 'Katarzyna', 'Kozera', '1998-05-09', 'K'),
	('2', 'Anna', 'Mielczarek', '2001-07-03', 'K'),
	('3', 'Jan', 'Jasiewicz', '1998-04-05', 'M'),
	('4', 'Piotr', 'Wiecha', '1998-05-01', 'M'),
	('5', 'Jakub', 'Grzywna', '1998-01-02', 'M'),
	('6', 'Leszek', 'Sarepski', '1997-11-07', 'M'),
	('7', 'Kamil', 'Laski', '1997-11-07', 'M'),
	('8', 'Mateusz', 'Siekiera', '1997-11-07', 'M'),
	('9', 'Karol', 'Kunicki', '1997-11-07', 'M'),
	('0', 'Igor', 'Czub', '1997-11-07', 'M'),
	('abc', 'Igor', 'Czub', '1997-11-07', 'M');


select * from Students


/*
CREATE VIEW dbo.studentgroupparticipation
AS
Select Student.Lastname, Student.Firstname, Subject.SubjectName, Subjectparticipants.Participationbeginning, Subjectparticipants.Participationend
from Student, Subject, Subjectparticipants
*/





INSERT SUBJECTPARTICIPANTS(SubjectParticipantID, ParticipationBeginning, ParticipationEnd, StudentID_FK, SubjectID_FK)
VALUES (1, '2019-04-02', '2019-07-12', 1, 1),
	   (2, '2019-04-03', '2019-07-13', 1, 3),
	   (3, '2019-04-04', '2019-07-14', 1, 2),
	   (4, '2019-04-05', '2019-07-15', 1, 4),
	   (5, '2015-04-06', '2017-07-10', 2, 1),
	   (6, '2016-04-06', '2016-07-10', 2, 4),
	   (7, '2015-04-06', '2015-07-10', 2, 1),
	   (8, '2017-09-06', '2018-02-10', 3, 1),
	   (9, '2017-09-06', '2018-02-10', 3, 2),
	   (10, '2014-04-06', '2015-07-10', 4, 1),
	   (11, '2015-04-06', '2015-07-10', 4, 2),
	   (12, '2016-04-06', '2017-07-10', 4, 3),
	   (13, '2015-04-06', '2015-07-10', 4, 4);


INSERT StudentsGroup(
	GroupID, SubjectID_FK, Foremanname, Weekday, BeginingTime)
values
	(0, 1, 'Jakub', 'Poniedzialek', '13:30'),
	(1, 2, 'Kamil', 'Sroda', '11:00'),
	(2, 3, 'Jakub', 'Piatek', '14:00'),
	(3, 1, 'Mateusz', 'Czwartek', '11:00'),
	(4, 2, 'Jan', 'Sroda', '10:00'),
	(5, 4, 'Karol', 'Wtorek', '08:00'),
	(6, 2, 'Jakub', 'Sroda', '15:00'),
	(7, 1, 'Katarzyna', 'Czwartek', '17:00'),
	(8, 2, 'Anna', 'Poniedzialek', '12:00'),
	(9, 4, 'Piotr', 'Sobota', '11:00');

INSERT GroupParticipants (GroupParticipantID, ParticipationBegining, ParticipationEnd, GroupID_FK, StudentID_FK)
VALUES
	(0, '2019-02-09', '2019-06-03', 0, 5),
	(1, '2019-03-29', '2019-06-16', 1, 7),
	(2, '2019-02-19', '2019-07-05', 2, 5),
	(3, '2019-02-15', '2019-06-18', 3, 8),
	(4, '2019-05-07', '2019-08-19', 4, 3),
	(5, '2019-04-01', '2019-07-02', 5, 9),
	(6, '2019-02-05', '2019-09-08', 6, 5),
	(7, '2019-03-09', '2019-08-03', 7, 1),
	(8, '2019-05-18', '2019-07-12', 8, 2),
	(9, '2019-04-11', '2019-09-04', 9, 4);


Update StudentsGroup
	Set Weekday = 'Poniedzialek'
	where Weekday = 'Czwartek'
	and SubjectID_FK = 1;

	Delete from Students
	where lastname like 'S%';

Delete from StudentsGroup
	where Weekday = 'Sobota'
	and SubjectID_FK = 4;



Alter Table GroupParticipants
	Add Status varchar(20)
	constraint Status check(Status in ('A','S','C'))
	constraint xx default 'A'
	with Values;

Alter Table StudentsGroup
	add size smallint
	constraint size check(size between 10 and 30)
	constraint x default 20
	with values;



select * from StudentsGroup