Guideline: Please read the following bullets before you start.

•There are 2 problems, 100 points. Make sure you answer all questions.

•There is no limit on how much time you spend on the exam except that you must submit your answer by  Eastern daylight saving time. No late submission will be accepted. You can submit your answer up to three times but only the last attempt before the deadline will be graded.

•You can submit your answer in text file (preferred), PDF, or word format. Please execute the statements on next page to create the tables used in the exam. 

•You should work on the exam by your own (you may look up textbook, slides, etc.), and should not ask anyone else for help. If you get caught on any such conduct, you will be given F in this class and are likely to be dismissed from the program. 

•You may ask clarification questions with the instructor. You should ask these questions via email and should not post on discussion board. Basically there should be no discussion on the exam on discussion board.

•If a clarification question is common to others, the instructor will post the answer as an announcement. So please check course announcement regularly.

•For SQL and PL/SQL programs, just submit the statements themselves. There is no need to print out results or show screen shots. Remember your statements need to work regardless of what rows are in the data tables. 

Database used in the EXAM: 

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. [80 points, 10 points for each task]

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: [20 points] 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).

END OF EXAM