SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Processing Cost - How to catch a soccer team with the highest combined score?

Filippe Soares RozaJun 21 2013 — edited Jul 2 2013

Hi to all.

It's a pleasure to receive your help.

I am using Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta and I am working with the two follows tables.

I want to get a soccer team with the highest combined score averages and participations considering all possible formations and my budget, but I'm not finding a way to get a cost effective processing.

Suggestions?

create table ESQUEMA
(
  ESQUEMA   INTEGER,
  ZAGUEIROS INTEGER,
  LATERAIS  INTEGER,
  MEIAS     INTEGER,
  ATACANTES INTEGER,
  P1        CHAR(3),
  P2        CHAR(3),
  P3        CHAR(3),
  P4        CHAR(3),
  P5        CHAR(3),
  P6        CHAR(3),
  P7        CHAR(3),
  P8        CHAR(3),
  P9        CHAR(3),
  P10       CHAR(3)
)
;

esquema table - sample data

ESQUEMA

ZAGUEIROS

LATERAIS

MEIAS

ATACANTES

P10

442

ZAG

LAT

MEI

ATA

433

ZAG

LAT

MEI

ATA

532

ZAG

LAT

MEI

ATA

352

ZAG

MEI

ATA

343

ZAG

MEI

ATA

451

ZAG

LAT

MEI

ATA

541

ZAG

LAT

MEI

ATA

create table JOGADORES
(
  ID      INTEGER not null,
  CLUBE   VARCHAR2(100),
  NOME    VARCHAR2(100),
  POSICAO CHAR(3),
  PRECO   NUMBER(4,2),
  MEDIA   NUMBER(4,2),
  JOGOS   INTEGER
)
;

jogadores table - sample data

	ID	CLUBE	NOME	POSICAO	PRECO	MEDIA	JOGOS
80	403	Fluminense	Abel Braga	TEC	17,51	5,36	4
70	393	Coritiba	Alex	MEI	16,98	5,08	5
42	365	Náutico	Auremir	LAT	7,73	5,48	4
18	341	Grêmio	Barcos	ATA	18,96	3,67	4
102	425	Grêmio	Bressan	ZAG	10,85	5,90	4
31	354	Crisciúma	Bruno	GOL	10,66	3,20	5
105	428	Cruzeiro	Bruno Rodrigo	ZAG	15,47	5,28	5
82	405	Vitória	Caio Júnior	TEC	11,40	4,45	5
41	364	Fluminense	Carlinhos	LAT	12,40	6,93	3
61	384	Vasco	Carlos Alberto	MEI	15,01	6,75	2
13	336	Ponte Preta	Chiquinho	ATA	9,97	4,64	5
72	395	Ponte Preta	Cicinho	MEI	11,42	4,72	5
92	415	Santos	Claudinei Oliveira	TEC	11,92	3,17	3
104	427	Ponte Preta	Cléber	ZAG	14,61	5,78	5
68	391	Portuguesa	Correa	MEI	8,44	5,60	4
93	416	Bahia	Cristóvão Borges	TEC	8,27	2,92	5
96	419	Atlético-MG	Cuca	TEC	12,62	2,32	4
29	352	Corinthians	Cássio	GOL	12,51	3,74	5

I want a result like this query could bring, if it had a low cost. Should I make any changes in tables or attempting to use PL / SQL?

select *

from jogadores j1,

jogadores j2,

jogadores j3,

jogadores j4,

jogadores j5,

jogadores j6,

jogadores j7,

jogadores j8,

jogadores j9,

jogadores j10,

jogadores j11,

jogadores j12,

esquema e

where j1.posicao = e.p1

and j2.posicao = e.p2

and j3.posicao = e.p3

and j4.posicao = e.p4

and j5.posicao = e.p5

and j6.posicao = e.p6

and j7.posicao = e.p7

and j8.posicao = e.p8

and j9.posicao = e.p9

and j10.posicao = e.p10

and j11.posicao = 'TEC'

and j12.posicao = 'GOL'

and j1.id NOT IN (j2.id,

j3.id,

j4.id,

j5.id,

j6.id,

j7.id,

j8.id,

j9.id,

j10.id,

j11.id,

j12.id)

and j2.id NOT IN (j3.id,

j4.id,

j5.id,

j6.id,

j7.id,

j8.id,

j9.id,

j10.id,

j11.id,

j12.id)

and j3.id NOT IN

(j4.id, j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)

and j4.id NOT IN

(j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)

and j5.id NOT IN (j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)

and j6.id NOT IN (j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)

and j7.id NOT IN (j8.id, j9.id, j10.id, j11.id, j12.id)

and j8.id NOT IN (j9.id, j10.id, j11.id, j12.id)

and j9.id NOT IN (j10.id, j11.id, j12.id)

and j10.id NOT IN (j11.id, j12.id)

and j11.id <> j12.id

and (e.esquema,

(j1.media + j2.media + j3.media + j4.media + j5.media + j6.media +

j7.media + j8.media + j9.media + j10.media + j11.media + j12.media +

j1.jogos + j2.jogos + j3.jogos + j4.jogos + j5.jogos + j6.jogos +

j7.jogos + j8.jogos + j9.jogos + j10.jogos + j11.jogos + j12.jogos)) in

(select e.esquema,

nvl(max(j1.media + j2.media + j3.media + j4.media + j5.media +

j6.media + j7.media + j8.media + j9.media + j10.media +

j11.media + j12.media + j1.jogos + j2.jogos +

j3.jogos + j4.jogos + j5.jogos + j6.jogos + j7.jogos +

j8.jogos + j9.jogos + j10.jogos + j11.jogos +

j12.jogos),

from jogadores j1,

jogadores j2,

jogadores j3,

jogadores j4,

jogadores j5,

jogadores j6,

jogadores j7,

jogadores j8,

jogadores j9,

jogadores j10,

jogadores j11,

jogadores j12,

esquema e

where j1.posicao = e.p1

and j2.posicao = e.p2

and j3.posicao = e.p3

and j4.posicao = e.p4

and j5.posicao = e.p5

and j6.posicao = e.p6

and j7.posicao = e.p7

and j8.posicao = e.p8

and j9.posicao = e.p9

and j10.posicao = e.p10

and j11.posicao = 'TEC'

and j12.posicao = 'GOL'

and j1.id NOT IN (j2.id,

j3.id,

j4.id,

j5.id,

j6.id,

j7.id,

j8.id,

j9.id,

j10.id,

j11.id,

j12.id)

and j2.id NOT IN (j3.id,

j4.id,

j5.id,

j6.id,

j7.id,

j8.id,

j9.id,

j10.id,

j11.id,

j12.id)

and j3.id NOT IN (j4.id,

j5.id,

j6.id,

j7.id,

j8.id,

j9.id,

j10.id,

j11.id,

j12.id)

and j4.id NOT IN

(j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)

and j5.id NOT IN

(j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)

and j6.id NOT IN (j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)

and j7.id NOT IN (j8.id, j9.id, j10.id, j11.id, j12.id)

and j8.id NOT IN (j9.id, j10.id, j11.id, j12.id)

and j9.id NOT IN (j10.id, j11.id, j12.id)

and j10.id NOT IN (j11.id, j12.id)

and j11.id <> j12.id

and (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco +

j6.preco + j7.preco + j8.preco + j9.preco + j10.preco +

j11.preco + j12.preco) <= &patrimonio

group by e.esquema)

and (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco + j6.preco +

j7.preco + j8.preco + j9.preco + j10.preco + j11.preco + j12.preco) <=

&patrimonio;

Thank in advance,

Filippe

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 30 2013

Added on Jun 21 2013

#pl-sql, #query, #sql

14 comments

2,509 views