2XtUNtEV

1DROP DATABASE IF EXISTS Football;
2CREATE DATABASE Football;
3USE Football;
4
5CREATE TABLE teams (
6	id INT NOT NULL Identity,
7	teamName VARCHAR (50) NOT NULL,
8	country VARCHAR (50) NOT NULL,
9	PRIMARY KEY (Id)
10	);
11
12INSERT INTO teams (teamName,country) VALUES ('Barcelona', 'Spanien');
13INSERT INTO teams (teamName,country) VALUES ('Manchester United','England');
14
15-- testing table and database
16SELECT * FROM teams;
17
18
19CREATE TABLE players (
20	id INT NOT NULL Identity,
21	playerName VARCHAR (50) NOT NULL,
22	teamid INT NOT NULL,
23	PRIMARY KEY (id),
24	FOREIGN KEY (teamid) REFERENCES teams (id),
25	);
26
27INSERT INTO players (playerName,teamid) VALUES ('Leo Messi', '1');
28INSERT INTO players (playerName,teamid) VALUES ('Victor Lindelöf', '2');
29INSERT INTO players (playerName,teamid) VALUES ('Frankie de Jong', '1');
30INSERT INTO players (playerName,teamid) VALUES ('Edinson Cavani', '2');
31
32-- testing table and database
33SELECT * FROM players;
34
35
36
37-- which team and country does each of the player belongs?
38SELECT playerName, teamName, country
39FROM players
40LEFT JOIN teams 
41ON players.teamid = teams.id;
42