Database Assignment

You will be using the following tables about chess tournaments in the exam. You can copy and paste to execute the following statements to set up the database. Please read the comments that explain meaning of columns.

drop table pairing;
drop table registration;
drop table section;
drop table player;
drop table tournament;

-- this table stores player information, each player has a numerical rating
create table player(
pid int, -- player id
pname varchar(50), -- player name
grade int, -- player grade, 1 means first grade, elementary school: 1 to 5
rating int, -- player's rating, a number representing player's strength
primary key (pid));

-- this table stores information about tournament
create table tournament(
tid int, -- tournament id
tname varchar(50), -- tournament name
tdate date,-- tournament date (assuming one day tournament)
primary key (tid));

-- this table stores information about a section in a tournament
-- each tournament has a few sections (often based on player's ratings or grade level)
-- each player only plays in one section
create table section(
tid int, --- tournamnet id
sid int, -- section id
sname varchar(50), --- section name
maxgrade int, -- maximal grade for the section
mingrade int, --- minimal grade
maxrating int, --- maximal rating for the section
minrating int, --- minimal rating
primary key (tid, sid),
foreign key (tid) references tournament);

-- this table stores registration information.
-- each player can register for a section in a tournament
create table registration(
pid int, -- player id
tid int, -- tournament id
sid int, -- section id
score number, -- score in the tournament,
-- initial score = 0, final score equals sum of score of this player in all rounds
primary key (pid, tid),
foreign key (pid) references player,
foreign key (tid,sid) references section);

-- this table stores pairing (game) information
-- a pairing means a game between two players in the same section
-- each section in a tournament typically has a few rounds,
-- each round players are divided into pairs and each pair plays a game on a chess board.
-- each tournament has a number of chess boards, each with a board id
-- during each round a board is assigned to only one pair of players
-- in the next round though the board will be reassigned.
-- one player plays white (move first), the other plays black.
-- winner gets 1.0 point, loser gets 0 point, if it is a draw each gets 0.5
-- typically each player plays in every round (no knockout),
-- you can find rules for swiss tournament at https://www.thespruce.com/the-swiss-system-611537

create table pairing(
bid int, --- board id, it is unique within a round, but can be reused across rounds
tid int, --- tournament id
round int, -- round id, 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);

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,600,1199);

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

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

insert into registration values(5,1,2,0);
insert into registration values(6,1,2,0);
insert into registration values(7,1,2,0);
insert into registration values(8,1,2,0);

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

insert into registration values(5,2,2,0);
insert into registration values(6,2,2,0);

--- 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;

Problem 1: Please write SQL statements to implement the following tasks.

You can ONLY use conditions listed in each task. You cannot add or change conditions by manually looking up data. E.g., if we look for a player named Anna, you cannot manually look up her pid.

Task 1: Return Player Anna's grade and rating.

Task 2: Return names of all elementary school players with rating over 1000.

Task 3: Return number of players registered for Baltimore January Action

Task 4: Return the name and rating of players who have registered for the Intermediate section of Baltimore January Action.

Task 5: return the tournament name and number of players registered for each tournament.

Task 6: return the name of tournament with at least 5 registered players

Task 7: return the pairing information for the first round of Baltimore January Action.

Please include board ID, white player's name, white player's rating, black player's name, black player's rating

Task 8: return the section name and average rating of players registered for each section in 'Baltimore January Action'

Problem 2: Please write a PL/SQL program to compute the sum of 13, 23, 33, ..., 103. Here i3 means cube of i (e.g., 23 = 8).