Hello everyone,

Can someone please tell me if this is correct somewhat or which statements have mistakes in it.

Select * from player
where rating > 1000;
group by pname;

Select pname, rating
from registration
INNER JOIN Section
where player.pid = registration.pid and registration.tid = section.tid

Select sname , avg(rating)
From registration, section
where registration.pid = player.pid and tname = 'Baltimore January Action';

 

Select sname
From section,tournament
group by sname
where section.tid = tournament.tid and tname = 'Baltimore January Action'
having avg(rating)>= 1200;

Select pname
From registration,
JOIN player 
ON registration.pid = player.pid
where registration.tid = tournament.tid and tname = 'Baltimore January action';

 

 

IS 620 Assignment 2
Due 2/20
Suppose you have created tables in assignment 1. Please write an anonymous PL/SQL
program for each the following problems. You can use the attached SQL statements
to create the tables.
Problem 1. Please write ONE SQL statement for each of the following tasks using tables
created in Problem 1. [100 points, 20 points each]
Task 1: Return names of all elementary school players (i.e., grade 1 to 5) with rating over
1000.
Task 2: Return the name and rating of players who have registered for the Intermediate
section of Baltimore January Action.
Task 3: return the section name and average rating of players registered for each section
in 'Baltimore January Action'.
Task 4: Return the names of sections in 'Baltimore January Action' that have average
rating of at least 1200
Task 5: Return names of players who have played against Anna in 'Baltimore January
Action'
Sample code to create the tables.
drop table player;
drop table tournament;
drop table section;
drop table registration;
drop table pairing;
create table player(
pid int,
pname varchar(50),
grade int,
rating int,
primary key (pid));
create table tournament(
tid int, tname varchar(50),
tdate date,
primary key (tid));
create table section(
tid int, --- tournamnet id
sid int, -- section id
sname varchar(50), --- section name
maxgrade int, -- maximal grade
mingrade int, --- minimal grade
maxrating int, --- maximal rating
minrating int, --- minimal rating
primary key (tid, sid),
foreign key (tid) references tournament);
create table registration(
pid int,
tid int,
sid int,
score number,
primary key (pid, tid),
foreign key (pid) references player,
foreign key (tid,sid) references section);
create table pairing(
bid int, --- board id,
tid int, --- tournament
round int, -- round, 1, 2, 3, ...
wpid int, -- player who plays white
bpid int, -- player 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 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);
--- 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);
commit;

• 1493711450-assignment2 6.docx

ComputerScienceExpert

(11)

Status NEW Posted 02 May 2017 07:05 AM My Price 11.00

-----------

Attachments

file 1493711453-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer