Question

Question: Write one SQL statement for the following question:

Return number of players whose rating is over 1000.

Background information:

This is a chess tournament management database that stores information about chess players, tournaments, sections, registrations, and pairings.

• Each player has an ID, name, grade (0 to 12) and rating.
• Each tournament has a number of sections. Each player can register for a section of a tournament
• In each round of a tournament, players in the same section will be matched in pairs. Each pair of players will be assigned to a chess board to play. One plays white and the other plays black. The result could be white win (1:0), black win (0:1), or draw (0.5:0.5). Each player will play in each round (no knockout) and final score is sum of scores in each round. A player will play roughly equal number of games in white or black.

create table player(

pid int, -- player ID

pname varchar(50), -- name of player

grade int, --- grade of player, 0 to 12

rating int, -- rating of player, 0 to 3000

primary key (pid));

create table tournament(

tid int, -- tournament ID

tname varchar(50), --- name of tournament

tdate date, --- tournament date

primary key (tid));

create table section(

tid int, --- tournamnet id

sid int, -- section id

sname varchar(50), --- section name

maxgrade int, -- maximal grade allowed in section

mingrade int, --- minimal grade allowed in section

maxrating int, --- maximal rating allowed in section

minrating int, --- minimal rating allowed in section

primary key (tid, sid),

foreign key (tid) references tournament);

create table registration(

pid int, -- player id

tid int, -- tournament id

sid int, -- section id,

score number, -- score of player in the tournament, sum of score of each game, win 1, loss 0, draw 0.5,

---initial score is null

primary key (pid, tid),

foreign key (pid) references player,

foreign key (tid,sid) references section);

create table pairing(

bid int, --- board id, unique within a round in a tournament, two players will play on that board

tid int, --- tournament id

round int, -- round, 1, 2, 3, ...

wpid int, -- player ID who plays white

bpid int, -- player ID who plays black

wscore number, -- score for white player

bscore number, -- score for black player

primary key(bid,tid,round),

foreign key(tid) references tournament,

foreign key(wpid) references player,
foreign key(bpid) references player);

insert into player values(1,'Anna', 5, 1250);

insert into player values(2,'Dan', 6, 1320);

insert into player values(3,'Ella', 7, 1500);

insert into player values(4,'Nathan', 8, 1420);

insert into player values(5,'Rohan', 3, 920);

insert into player values(6,'Emily', 2, 620);

insert into player values(7,'Thomas', 3, 720);

insert into player values(8,'Andrew', 4, 820);

insert into tournament values(1,'Baltimore January Action',date '2017-1-7');

insert into tournament values(2,'MD Championship',date '2017-3-7');

insert into section values(1,1,'Junior Varsity', 12,0,1599,1200);

insert into section values(1,2,'Intermediate', 12,0,1199,600);

insert into section values(2,1,'Junior Varsity', 12,0,1599,1200);

insert into section values(2,2,'Intermediate', 12,0,1199,600);

insert into registration values(1,1,1,null);

insert into registration values(2,1,1,null);

insert into registration values(3,1,1,null);

insert into registration values(4,1,1,null);

insert into registration values(5,1,2,null);

insert into registration values(6,1,2,null);

insert into registration values(7,1,2,null);

insert into registration values(8,1,2,null);

insert into registration values(1,2,1,null);

insert into registration values(2,2,1,null);

insert into registration values(5,2,2,null);

insert into registration values(6,2,2,null);

--- tournament 1, round 1,

insert into pairing values(1,1,1,1,2,0,1);

insert into pairing values(2,1,1,3,4,0.5,0.5);

insert into pairing values(11,1,1,5,6,1,0);

insert into pairing values(12,1,1,7,8,0,1);

---- tournament 1, round 2

insert into pairing values(1,1,2,2,3,0,1);

insert into pairing values(2,1,2,4,1,1,0);

insert into pairing values(11,1,2,8,5,1,0);

insert into pairing values(12,1,2,6,7,0.5,0.5);

--- tournament 1, round 3

insert into pairing values(1,1,3,1,3,0.5,0.5);

insert into pairing values(2,1,3,4,2,1,0);

insert into pairing values(11,1,3,5,7,1,0);

insert into pairing values(12,1,3,8,6,0.5,0.5);

commit;

Answer 1

/*counting player name from Player table*/
/* Count is the function which helps to count the number of records.
AS is used to give Alias name */
SELECT COUNT(pname) As PlayerCount FROM player
/* Filtering data based on rating.
> symbol helps to get all players of rating 1000*/
WHERE rating > 1000;

Output:
PlayerCount
5