EjxJAN92

1--system database
2use master 
3go
4
5--create database
6drop database if exists Players
7go
8create database Players
9go
10use Players
11go
12
13--Create users and categories
14create table Reserves(	
15	ReservePlayerID INT NOT NULL PRIMARY KEY IDENTITY (1,1),
16	ReserveReason VARCHAR (50) NULL,
17)
18
19create table FirstTeam(
20	PlayerID INT NOT NULL PRIMARY KEY IDENTITY (1,1),
21	PlayerName VARCHAR (50) NULL,
22	Age INT NULL,
23	IsInjured BIT NULL,
24	ReservePlayerID INT NULL FOREIGN KEY REFERENCES Reserves(ReservePlayerID)
25)
26INSERT INTO Reserves VALUES ('Dropped')
27INSERT INTO Reserves VALUES ('Return To Fitness')
28INSERT INTO Reserves VALUES ('Youth')
29
30INSERT INTO FirstTeam VALUES ('Hector Bellerin', '24', 'false',1)
31INSERT INTO FirstTeam VALUES ('Gabriel Martinelli', '18', 'false',3)
32INSERT INTO FirstTeam VALUES ('Kieran Tierney', '21', 'true',2)
33
34select * from FirstTeam
35SELECT * FROM Reserves
36
37--SQL JOIN SIMILAR TO LINQ
38SELECT * FROM FirstTeam
39INNER JOIN Reserves
40ON FirstTeam.ReservesID = Reserves.ReservesID
41
42SELECT FirstTeamId, FirstTeamName, isValid, ReservesName from FirstTeam
43INNER JOIN Reserves
44ON FirstTeam.ReservesID = Reserves.ReservesID