Guideline: 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

Guideline: 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. 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 . 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 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. 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: 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. 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] 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

Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadline.


Click Here to Make an Order Click Here to Hire a Writer