GVeFVynb

1drop table if exists SCORE, BALLING, PLAYER, TEAM_MASTER;
2drop view if exists TEAM_DATA;
3
4create table TEAM_MASTER(
5	TEAM_ID int not null auto_increment,
6	TEAM_NAME varchar(100) not null,
7	primary key(TEAM_ID)
8);
9
10create table PLAYER(
11	TEAM_ID int not null,
12	PID int not null auto_increment,
13	PNAME varchar(100) not null,
14	PBIRTHDATE date not null,
15	primary key(PID),
16	foreign key(TEAM_ID) references TEAM_MASTER(TEAM_ID)
17);
18
19create table BALLING(
20	TEAM_ID int not null,
21	B_ID int not null auto_increment,
22	OVERS int,
23	MAIDEN int,
24	RUNS_GIVEN int not null,
25	WICKETS_TAKEN int,
26	foreign key(TEAM_ID) references TEAM_MASTER(TEAM_ID),
27	primary key(B_ID),
28	check (WICKETS_TAKEN <= 10)
29);
30
31create table SCORE(
32	TEAM_ID int not null,
33	PID int not null,
34	RUNS_MADE int not null,
35	OUT_TYPE enum('NOT OUT', 'LBW', 'CATCH OUT', 'BOWLED', 'STUMPING') not null,
36	B_ID int not null,
37	BTEAM_ID int,
38	foreign key(PID) references PLAYER(PID),
39	foreign key(B_ID) references BALLING(B_ID),
40	foreign key(TEAM_ID) references TEAM_MASTER(TEAM_ID)
41);
42
43insert into TEAM_MASTER (TEAM_NAME) values ('india');
44insert into TEAM_MASTER (TEAM_NAME) values ('england');
45insert into TEAM_MASTER (TEAM_NAME) values ('new zeland');	
46insert into TEAM_MASTER (TEAM_NAME) values ('sri lanka');
47insert into TEAM_MASTER (TEAM_NAME) values ('australia');
48
49insert into PLAYER (TEAM_ID, PNAME, PBIRTHDATE) values
50	(1, 'Virat Kohli', '1988-11-5'),
51	(1, 'MS Dhoni', '1981-7-7'),
52	(1, 'Virender Shewag', '1984-9-17'),
53	(2, 'Alastair Cook', '25-12-1984'),
54	(2, 'Andrew Flintoff', '1997-12-06'),
55	(2, 'Matt Prior', '1982-2-26'),
56	(3, 'Andre Adams', '1975-7-17'),
57	(3, 'Brendon McCullum', '1981-9-27'),
58	(3, 'Chris Harris', '1969-11-20'),
59	(4, 'Akalanka Ganegama', '1965-8-2'),
60	(4, 'Avishka Gunawardene', '1954-5-30'),
61	(4, 'Chamara Kapugedera', '1964-2-12'),
62	(5, 'Adam Gilchrist', '1971-11-14'),
63	(5, 'Andrew Symonds', '1975-9-9'),
64	(5, 'Brad Haddin', '1977-10-23');
65
66insert into BALLING (TEAM_ID, OVERS, MAIDEN, RUNS_GIVEN, WICKETS_TAKEN) values
67	(1, 34, 7, 78, 2),
68	(1, 34, 6, 328, 3),
69	(1, 24, 3, 438, 5),
70	(2, 84, 2, 568, 8),
71	(2, 74, 3, 438, 9),
72	(2, 33, 1, 678, 1),
73	(3, 74, 7, 756, 4),
74	(3, 94, 9, 748, 4),
75	(3, 14, 1, 758, 2),
76	(4, 44, 4, 768, 2),
77	(4, 54, 6, 778, 2),
78	(4, 74, 6, 738, 9),
79	(5, 34, 2, 738, 7),
80	(5, 37, 5, 748, 2),
81	(5, 39, 7, 438, 1);
82
83insert into SCORE (TEAM_ID, PID, B_ID, RUNS_MADE, OUT_TYPE) values
84	(1, 1, 1, 23, 'NOT OUT'),
85	(1, 2, 2, 53, 'CATCH OUT'),
86	(1, 3, 3, 63, 'STUMPING'),
87	(2, 4, 4, 73, 'CATCH OUT'),
88	(2, 5, 5, 83, 'LBW'),
89	(2, 6, 6, 13, 'NOT OUT'),
90	(3, 7, 7, 47, 'LBW'),
91	(3, 8, 8, 28, 'CATCH OUT'),
92	(3, 9, 9, 23, 'BOWLED'),
93	(4, 10, 10, 53, 'NOT OUT'),
94	(4, 11, 11, 53, 'BOWLED'),
95	(4, 12, 12, 63, 'CATCH OUT'),
96	(5, 13, 13, 43, 'NOT OUT'),
97	(5, 14, 14, 43, 'LBW'),
98	(5, 15, 15, 63, 'BOWLED');
99
100select * from PLAYER where PID IN (select B_ID from BALLING where WICKETS_TAKEN=(select max(WICKETS_TAKEN) from BALLING));
101select * from PLAYER where month(PBIRTHDATE)=10 and PID in (select PID from SCORE where RUNS_MADE>=25);
102select * from PLAYER where PID in (select B_ID from BALLING where MAIDEN<=3);
103select sum(RUNS_GIVEN) as TotalRun from BALLING;
104select count(OUT_TYPE) as 'CATCH OUT', TEAM_ID from SCORE where OUT_TYPE='CATCH OUT' group by TEAM_ID;
105select count(OUT_TYPE) as 'NOT OUT', TEAM_ID from SCORE where OUT_TYPE='NOT OUT' group by TEAM_ID;
106select count(OUT_TYPE) as 'STUMPING', TEAM_ID from SCORE where OUT_TYPE='STUMPING' group by TEAM_ID;
107select count(OUT_TYPE) as 'LBW', TEAM_ID from SCORE where OUT_TYPE='LBW' group by TEAM_ID;
108select count(OUT_TYPE) as 'BOWLED', TEAM_ID from SCORE where OUT_TYPE='BOWLED' group by TEAM_ID;
109select * from PLAYER where year(PBIRTHDATE)>=1984;
110create view TEAM_DATA as select a.TEAM_NAME, b.PNAME, a.TEAM_ID from TEAM_MASTER a, PLAYER b where a.TEAM_ID=b.TEAM_ID;
111select * from TEAM_DATA;