-- Composite key SQL
Create Table classroom
(
block CHAR (2);
	Check (block IN ('WE', 'PM')),
rLevel INT,
rNumber INT,
rType CHAR(20),
rSize INT,
Constraint roomPK
Primary Key (block, rlevel, rNumber)
)

-- Insert values
Insert Into classroom Values
('WE', 2, 1, 'Lab' 50);
Insert Into classroom Values
('WE', 2, 2, 'Lab' 50);
-- Rejected values
Insert Into classroom Values
('WE', 2, NULL, 'Lab' 50);

--  Example of foreign key
Create Table team
(
name Char(20),
category Char (2) NOT NULL,
gPlayed INT NOT NULL DEFAULT 0 CHECK (gPlayed > 0),
point INT NOT NULL CHECK (point > 0),
Constraint ??? Primary Key (name)
);

Create Table teammember
(
ID Char (8),
name Varchar (30) NOT NULL,
email Varchar (30) NOT NULL,
phone Char(10),
gt Char(20) NOT NULL,
Constraint memberpk Primary Key (ID),
Constraint memberfk Foreign Key (gt) References team(name)
);

insert into team values ('team 1', 'c1', 0, 0);
insert into team values ('team 2', 'c1', 0, 0);
insert into teammember values
('m1', 'xx', 'xx', 'xx', 'team 1');
('m1', 'xx', 'xx', 'xx', 'team 2');

DELETE FROM team where name = 'team 1';

SELECT *
From team, teammember
where team.name = gt
order by team.name;