June 1, 2014 at 8:30 pm
I need to create a view called High_Score of Teams that scored more than
40 points (in home and away games), grouped by country.
something along these lines, I am guessing.
create view High_Score as
select tname from teams, results
where homescore AND awayscore > 40
group by country
I know this is fair way off, but its all i can think up from what Ive been provided with.
----------
These are the 3 tables used in the DB
create table teams (
tnum int not null,
tname varchar(30),
country char(2),
coach varchar(100),
points int,
captain_id int,
primary key (tnum)
)
create table players (
pnum int not null,
pname varchar(100),
team_id int,
primary key (pnum),
foreign key (team_id) references teams
)
create table results (
roundnumber int,
hometeam int not null,
awayteam int not null,
gamedate varchar(100),
homescore int,
awayscore int
primary key (roundnumber, hometeam, awayteam),
foreign key (hometeam) references teams,
foreign key (awayteam) references teams
--- The result will display this.
select * from High_Score
team country
BrumbiesAU
ChiefsNZ
CrusadersNZ
HurricanesNZ
SharksSA
June 2, 2014 at 12:44 am
tnhope (6/1/2014)
I need to create a view called High_Score of Teams that scored more than40 points (in home and away games), grouped by country.
something along these lines, I am guessing.
create view High_Score as
select tname from teams, results
where homescore AND awayscore > 40
group by country
Don't you think in above query, you'll need a join condition ?
Please provide sample records for all tables and more importantly your exact requirement. 😉
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 2, 2014 at 1:24 am
These are the teams that are view should generate.
insert into teams values (2, 'Brumbies', 'AU', 'Jake White', 0, 67
insert into teams values (5, 'Chiefs', 'NZ', 'Dave Rennie', 0, 176)
insert into teams values (6, 'Crusaders', 'NZ', 'Todd Blackadder', 0, 208)
insert into teams values (9, 'Hurricanes', 'NZ', 'Mark Hammett', 0, 307)
insert into teams values (13, 'Sharks', 'SA', 'John Plumtree', 0, 437)
based on these result inputs
Brumbies
insert into results values (11,2,7,'27-Apr',41,7)
Chiefs--
insert into results values (2,8,5,'22-Feb',27,41)
insert into results values (3,5,4,'2-Mar',45,3)
Crusaders--
insert into results values (5,6,3,'16-Mar',41,19)
insert into results values (6,6,10,'23-Mar',55,20)
Hurricanes--
insert into results values (7,9,10,'30-Mar',46,30)
insert into results values (8,9,15,'6-Apr',41,29)
Sharks--
insert into results values (6,13,11,'23-Mar',64,7)
All these teams scored over 40 points in these games, i need these teams this displayed using a view.
Yes a join will be needed. I've just over thought the situation.
create view High_Score as
select tname, country
from teams t, results r
where r.homescore = r.awayscore
and ????
I'm stumped, I'm not even sure if the joins correct.
this is literally everything I have been supplied with.
June 2, 2014 at 2:30 am
Does this do what you need...
SELECT
tname,
country,
(SUM(b.homescore) + SUM(c.awayscore)) as High_Score
FROM teams a
INNER JOIN results b ON
a.tnum = b.hometeam
INNER JOIN results c ON
a.tnum = c.awayteam
GROUP BY tname,country
HAVING (SUM(b.homescore) + SUM(c.awayscore)) > 40
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply