· 5 years ago · Mar 29, 2020, 07:24 PM
1DROP TABLE IF EXISTS team_players;
2DROP TABLE IF EXISTS player;
3DROP TABLE IF EXISTS club;
4DROP TABLE IF EXISTS league;
5DROP TABLE IF EXISTS user_team;
6DROP TABLE IF EXISTS user;
7
8CREATE TABLE `league`
9(
10 `league_id` int
11(11) NOT NULL,
12 `league_name` varchar
13(45) DEFAULT NULL,
14 `country_name` varchar
15(45) DEFAULT NULL,
16 `no_of_clubs` int
17(11) DEFAULT NULL,
18 PRIMARY KEY
19(`league_id`)
20) ENGINE=InnoDB;
21
22
23CREATE TABLE `user`
24(
25 `id` int
26(11) NOT NULL,
27 `full_name` varchar
28(45) NOT NULL,
29 `email` varchar
30(45) NOT NULL,
31 `password` varchar
32(45) NOT NULL,
33 PRIMARY KEY
34(`id`)
35) ENGINE=InnoDB;
36
37
38
39CREATE TABLE `club`
40(
41 `club_id` int
42(11) NOT NULL,
43 `club_name` varchar
44(45) DEFAULT NULL,
45 `club_league_id` int
46(11) NOT NULL,
47 PRIMARY KEY
48(`club_id`),
49 KEY `club_league_id_idx`
50(`club_league_id`),
51 CONSTRAINT `club_league_id` FOREIGN KEY
52(`club_league_id`) REFERENCES `league`
53(`league_id`)
54) ENGINE=InnoDB ;
55
56
57CREATE TABLE `player`
58(
59 `player_id` int
60(11) NOT NULL,
61 `player_club_id` int
62(11) DEFAULT NULL,
63 `player_name` varchar
64(45) DEFAULT NULL,
65 `player_rating` int
66(11) DEFAULT NULL,
67 `player_position` varchar
68(45) DEFAULT NULL,
69 PRIMARY KEY
70(`player_id`),
71 KEY `player_club_id_idx`
72(`player_club_id`),
73 CONSTRAINT `player_club_id` FOREIGN KEY
74(`player_club_id`) REFERENCES `club`
75(`club_id`)
76) ENGINE=InnoDB ;
77
78
79CREATE TABLE `user_team`
80(
81 `user_team_id` int
82(11) NOT NULL,
83 `user_id` int
84(11) DEFAULT NULL,
85 PRIMARY KEY
86(`user_team_id`),
87 KEY `user_id_idx`
88(`user_id`),
89 CONSTRAINT `user_id` FOREIGN KEY
90(`user_id`) REFERENCES `user`
91(`id`)
92) ENGINE=InnoDB;
93
94
95CREATE TABLE `team_players`
96(
97 `team_player_id` int
98(11) NOT NULL,
99 `user_team_id` int
100(11) DEFAULT NULL,
101 `player_id` int
102(11) DEFAULT NULL,
103 PRIMARY KEY
104(`team_player_id`),
105 KEY `team_player_id_idx`
106(`user_team_id`),
107 KEY `player_id_idx`
108(`player_id`),
109 CONSTRAINT `player_id` FOREIGN KEY
110(`player_id`) REFERENCES `player`
111(`player_id`),
112 CONSTRAINT `team_player_id` FOREIGN KEY
113(`user_team_id`) REFERENCES `user_team`
114(`user_team_id`)
115) ENGINE=InnoDB;
116
117INSERT INTO `user`
118 (`id`, `full_name`, `email`, `password`) VALUES
119(1,'Sushant Baskota','sbaskota@go.olemiss.edu','thikxahai'),
120(2,'Ayush','aupadhyay@go.olemiss.edu','thikxaina'),
121(3, 'Thomas Shelby', 'tommy@shelbyltd.com', 'peakyblinders');
122
123INSERT INTO `league` (`league_id`,`league_name`, `country_name`, `no_of_clubs`) VALUES
124(1,'Barclays','England',20),
125(2,'Laliga','Spain',20),
126(3,'Serie A','Italy',20),
127(4,'Bundesliga','Germany',18),
128(5,'Ligue 1','France',18);
129
130INSERT INTO `club` (`club_id`,`club_name`, `club_league_id`) VALUES
131(1,'Manchester City',1),
132(2,'Liverpool',1),
133(3,'Arsenal',1),
134(4,'Barcelona',2),
135(5,'Real Madrid',2),
136(6,'PSG',5),
137(7,'Dortmund',4),
138(8,'Monaco',5),
139(9,'Juventus',3),
140(10,'Atletico Madrid',2),
141(11,'Bayern Munich',4);
142
143INSERT INTO `player` (`player_id`,`player_club_id`, `player_name`, `player_rating`, `player_position`) VALUES
144(1,4,'Lionel Messi',5,'RW'),
145(2,4,'Suarez',4,'ST'),
146(3,4,'Frenkie Dejong',5,'CM'),
147(4,1,'Sergio Aguero',4,'ST'),
148(5,6,'Neymar Jr.',5,'LW'),
149(6,9,'Cristiano Ronaldo',5,'CF'),
150(7,6,'Angel Di Maria',4,'RW'),
151(8,1,'Mahrez',4,'RW'),
152(9,9,'Paulo Dybala', 5, 'RM');
153
154INSERT INTO `user_team` (`user_team_id`,`user_id`) VALUES
155(1,1),
156(2,2),
157(3,3);
158
159INSERT INTO `team_players` (`team_player_id`,`user_team_id`, `player_id`) VALUES
160(1,1,1),
161(2,1,2),
162(3,1,3),
163(4,1,4),
164(5,2,4),
165(6,2,2),
166(7,2,1),
167(8,2,4),
168(9,3,9),
169(10,3,8),
170(11,3,7),
171(12,3,6),
172(13,3,5);
173
174SELECT full_name , group_concat(player_name) AS NumberOfPlayers
175FROM user natural join user_team natural join team_players natural join player
176where user.id = user_team.user_id and user_team.user_team_id= team_players.user_team_id
177Group by user.id;
178
179SELECT league_name, COUNT(distinct player_id)
180from player natural join club natural join league
181where player_club_id = club_id and club_league_id = league_id
182group by league_name;