Обсуждение: Optimizing

Поиск
Список
Период
Сортировка

Optimizing

От
"Jeff Sack"
Дата:

 

Hello all.  I have an academic project that I’m working on and, as I’m relatively new to optimization techniques and database design, I’m wondering if some of you can give me some pointers.  Below is the schema (to model baseball statistics), and I’m pretty much stuck with it at this point.  If anyone has any suggestions involving changing the schema, I’d appreciate hearing them just for future reference…

 

There are several interesting queries that I’ve been constructing just to get a feel for this schema, and given that some of these tables contain 100,000-200,000 tuples, some queries are taking a good 5-10 seconds to execute.  I’m just wondering if this is simply the fault of my schema or are these queries poorly constructed?  Here are some queries I’ve been trying:

 

Here’s a query for the top ten all time home run leaders:

select P.first_name, P.last_name, S.player_id, sum(B.hr) as hr, (sum(B.h)::float / sum(B.ab)::float) ab

from statistics S, batting_stats B, players P

where S.id=B.id and S.player_id=P.id and B.ab>0

group by P.first_name, P.last_name, S.player_id

order by hr desc limit 10;

 

Select the 10 highest batting averages of all time where the batter had 600 or more at bats.  Also gather the name, year, team, hits, at bats...

 

select (B.h::float / B.ab) as avg, B.h, B.ab, S.year, P.first_name, P.last_name, T.city, T.name

from batting_stats B, statistics S, players P, Teams T

where B.ab > 600 and S.id=B.id and S.player_id=P.id and S.team_id=T.id

order by avg desc limit 10;

 

To find the names of the single season home run leaders, along with the total number of home runs, the team name/city and the year:

 

select P.first_name, P.last_name, B.hr, T.name, T.city, S.year from statistics S, batting_stats B, players P, teams T

where (S.id=B.id) and

(S.player_id=P.id) and

(B.hr>30) and

(T.id=S.team_id) limit 10;

 

You get the idea.  These queries take a while.  Is this just the way it is or there things that can be done to optimize this? 

 

One separate issue (the reason why the above examples are all about batting statistics) I’m having is representing the innings pitched statistic.  The way it is often represented (and the way it is done in this schema) is something like this 123.0 means exactly 123 innings pitched, 123.1 means 123 1/3 innings, and 123.2 means 123 2/3 innings pitched.  I’m contemplating the best way to write a function that knows how to sum these values accurately.  Is this something that can be done with PL/PGSQL or should I go straight to something like PLPERL?  Alternatively, I could research a way to represent fractions in the DB and write a script to convert all values in this column.  Any advice here??

 

Thanks in advance for any thoughts, comments, suggestions…

 

-Jeff

 

 

create table leagues

(

            -- create an integer id field for easier and efficient FK referencing

            id                     serial,

            name               varchar(50),

            first_year         integer not null

                        check (first_year > 1850 and first_year < 2010),

            last_year         integer

                        check (first_year > 1850 and first_year < 2010),

            primary key(id),

            constraint chronological_order

                        check (last_year >= first_year)

);

 

 

create table teams

(

            id                     serial,

            name               varchar(50) not null,

            city                  varchar(50),

            first_year         integer not null

                        check (first_year > 1850 and first_year < 2010),

            last_year         integer

                        check (first_year > 1850 and first_year < 2010),

            alt_id               integer,

            league_id        integer not null,

            primary key(id),

            foreign key(alt_id) references teams(id),

            foreign key(league_id) references leagues(id)

                        on delete cascade,

            constraint chronological_order

                        check (last_year >= first_year)

);

 

create table players

(

            id                     serial,

            first_name       varchar(30),

            last_name       varchar(30) not null,

            bats                 char(1) check (bats in ('L','R','S')),

            throws             char(1) check (throws in ('L','R')),

            dob                  date,

            primary key(id)

);

 

create table statistics

(

            id                                 serial,

            year                             integer not null,

            g                                  integer,

            player_id                     integer not null,

            team_id                                   integer not null,

            foreign key(player_id) references players(id)

                        on delete cascade,

            foreign key(team_id) references teams(id)

                        on delete cascade,

            primary key(id)

);

 

create table managing_stats

(

            id                                 integer not null,

            w                                  integer,

            l                                   integer,

            primary key(id),

            foreign key(id) references statistics(id)

                        on delete cascade

);

 

create table fielding_stats

(

            id                                 integer not null,

            pos                              char(5),

            po                                integer,

            a                                  integer,

            e                                  integer,

            dp                                integer,

            primary key(id),

            foreign key(id) references statistics(id)

                        on delete cascade

);

 

create table batting_stats

(

            id                                 integer not null,

            ab                                integer,

            r                                   integer,

            h                                  integer,

            doubles                                   integer,

            triples                          integer,

            hr                                 integer,

            rbi                                integer,

            sb                                integer,

            cs                                integer,

            bb                                integer,

            so                                integer,

            sh                                integer,

            sf                                 integer,

            ibb                               integer,

            hbp                              integer,

            primary key(id),

            foreign key(id) references statistics(id)

                        on delete cascade

);

 

create table pitching_stats

(

            id                                 integer not null,

            w                                  integer,

            l                                   integer,

            gs                                integer,

            cg                                integer,

            sh                                integer,

            sv                                integer,

            ip                                 numeric(5,1),

            h                                  integer,

            er                                 integer,

            hr                                 integer,

            bb                                integer,

            so                                integer,

            primary key(id),

            foreign key(id) references statistics(id)

                        on delete cascade

);

 

 

 

 

Re: Optimizing

От
"Josh Berkus"
Дата:
Jeff,

> Hello all.  I have an academic project that I'm working on and, as
> I'm
> relatively new to optimization techniques and database design, I'm
> wondering if some of you can give me some pointers. 

See the PostgreSQL book review page:
http:\\techdocs.postgresql.org\bookreviews.php
There, you will find reviews of several good books on database design.

> There are several interesting queries that I've been constructing
> just
> to get a feel for this schema, and given that some of these tables
> contain 100,000-200,000 tuples, some queries are taking a good 5-10
> seconds to execute.  I'm just wondering if this is simply the fault
> of
> my schema or are these queries poorly constructed? 

That entirely depends.  If you're doing this on a 3-year-old laptop,
5-10 seconds is a *good* time.  But not on a Proliant 9000.

Here's the rules of indexing:
1. All JOIN columns should be indexed.
2. All criteria (WHERE) columns should be indexed, except        those that have a very limited range of values (e.g.
BOOLEAN).
3. All ORDER BY columns should be indexed, with the same exception.
4. Multi-column indexes are seldom useful for queries.
5. VACUUM should be performed regularly.
6. If SELECT is very fast and INSERT is very slow, try removing a few
indexes.

Also, you can muck around with runtime settings to you heart's content,
and moving the log files (pgsql/data/xlog) to a seperate drive improves
performance.  There are no quick answers once you've taken the basic
steps.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco

Re: Optimizing

От
"Steve Brett"
Дата:
the main way i optimise small queries is to image a path with the tables joining the path (this has it's root in relational algebra and relational calculus) and then build the query in my head imagining how i can reduce the amount of 'traffic' flowing up the tree to the selects. or draw the query as a tree (as ingres does as i seem to remember - great idea for postgres ??????) like : select X,Y,Z | | | /| t1.id=t2.id / | / | / | table table 2 obviously you'd have more joins and more tables but the aim is the restrict the amount of data travelling up the tree so subsequent joins contain less tuples. if you need them i can dig out some references from books that i have read (and forgotten !!!) so let me know if i'm only making sense to myself. Steve ""Jeff Sack"" wrote in message news:003301c168a0$034d53e0$019ca8c0@jeff... > This is a multi-part message in MIME format. > > ------=_NextPart_000_0034_01C1686D.B8B2E3E0 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > > Hello all. I have an academic project that I'm working on and, as I'm > relatively new to optimization techniques and database design, I'm > wondering if some of you can give me some pointers. Below is the schema > (to model baseball statistics), and I'm pretty much stuck with it at > this point. If anyone has any suggestions involving changing the > schema, I'd appreciate hearing them just for future reference. > > There are several interesting queries that I've been constructing just > to get a feel for this schema, and given that some of these tables > contain 100,000-200,000 tuples, some queries are taking a good 5-10 > seconds to execute. I'm just wondering if this is simply the fault of > my schema or are these queries poorly constructed? Here are some > queries I've been trying: > > Here's a query for the top ten all time home run leaders: > select P.first_name, P.last_name, S.player_id, sum(B.hr) as hr, > (sum(B.h)::float / sum(B.ab)::float) ab > from statistics S, batting_stats B, players P > where S.id=B.id and S.player_id=P.id and B.ab>0 > group by P.first_name, P.last_name, S.player_id > order by hr desc limit 10; > > Select the 10 highest batting averages of all time where the batter had > 600 or more at bats. Also gather the name, year, team, hits, at bats... > > select (B.h::float / B.ab) as avg, B.h, B.ab, S.year, P.first_name, > P.last_name, T.city, T.name > from batting_stats B, statistics S, players P, Teams T > where B.ab > 600 and S.id=B.id and S.player_id=P.id and S.team_id=T.id > order by avg desc limit 10; > > To find the names of the single season home run leaders, along with the > total number of home runs, the team name/city and the year: > > select P.first_name, P.last_name, B.hr, T.name, T.city, S.year from > statistics S, batting_stats B, players P, teams T > where (S.id=B.id) and > (S.player_id=P.id) and > (B.hr>30) and > (T.id=S.team_id) limit 10; > > You get the idea. These queries take a while. Is this just the way it > is or there things that can be done to optimize this? > > One separate issue (the reason why the above examples are all about > batting statistics) I'm having is representing the innings pitched > statistic. The way it is often represented (and the way it is done in > this schema) is something like this 123.0 means exactly 123 innings > pitched, 123.1 means 123 1/3 innings, and 123.2 means 123 2/3 innings > pitched. I'm contemplating the best way to write a function that knows > how to sum these values accurately. Is this something that can be done > with PL/PGSQL or should I go straight to something like PLPERL? > Alternatively, I could research a way to represent fractions in the DB > and write a script to convert all values in this column. Any advice > here?? > > Thanks in advance for any thoughts, comments, suggestions. > > -Jeff > > > create table leagues > ( > -- create an integer id field for easier and efficient FK > referencing > id serial, > name varchar(50), > first_year integer not null > check (first_year > 1850 and first_year < 2010), > last_year integer > check (first_year > 1850 and first_year < 2010), > primary key(id), > constraint chronological_order > check (last_year >= first_year) > ); > > > create table teams > ( > id serial, > name varchar(50) not null, > city varchar(50), > first_year integer not null > check (first_year > 1850 and first_year < 2010), > last_year integer > check (first_year > 1850 and first_year < 2010), > alt_id integer, > league_id integer not null, > primary key(id), > foreign key(alt_id) references teams(id), > foreign key(league_id) references leagues(id) > on delete cascade, > constraint chronological_order > check (last_year >= first_year) > ); > > create table players > ( > id serial, > first_name varchar(30), > last_name varchar(30) not null, > bats char(1) check (bats in ('L','R','S')), > throws char(1) check (throws in ('L','R')), > dob date, > primary key(id) > ); > > create table statistics > ( > id serial, > year integer not null, > g integer, > player_id integer not null, > team_id integer not null, > foreign key(player_id) references players(id) > on delete cascade, > foreign key(team_id) references teams(id) > on delete cascade, > primary key(id) > ); > > create table managing_stats > ( > id integer not null, > w integer, > l integer, > primary key(id), > foreign key(id) references statistics(id) > on delete cascade > ); > > create table fielding_stats > ( > id integer not null, > pos char(5), > po integer, > a integer, > e integer, > dp integer, > primary key(id), > foreign key(id) references statistics(id) > on delete cascade > ); > > create table batting_stats > ( > id integer not null, > ab integer, > r integer, > h integer, > doubles integer, > triples integer, > hr integer, > rbi integer, > sb integer, > cs integer, > bb integer, > so integer, > sh integer, > sf integer, > ibb integer, > hbp integer, > primary key(id), > foreign key(id) references statistics(id) > on delete cascade > ); > > create table pitching_stats > ( > id integer not null, > w integer, > l integer, > gs integer, > cg integer, > sh integer, > sv integer, > ip numeric(5,1), > h integer, > er integer, > hr integer, > bb integer, > so integer, > primary key(id), > foreign key(id) references statistics(id) > on delete cascade > ); > > > > > > ------=_NextPart_000_0034_01C1686D.B8B2E3E0 > Content-Type: text/html; > charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > hemas-microsoft-com:office:word" xmlns=3D"http://www.w3.org/TR/REC-html40"> > > > > > > > > > > > > > > > > > > >
> >

0.0pt; > font-family:Arial'> 

> >

0.0pt; > font-family:Arial'>Hello all.  >I > have an academic project that I’m working on and, as I’m relati= > vely > new to optimization techniques and database design, I’m wondering if = > some > of you can give me some pointers.  span>Below > is the schema (to model baseball statistics), and I’m pretty much stu= > ck > with it at this point.  If any= > one > has any suggestions involving changing the schema, I’d appreciate hea= > ring > them just for future reference…

> >

0.0pt; > font-family:Arial'> 

> >

0.0pt; > font-family:Arial'>There are several interesting queries that I’ve be= > en > constructing just to get a feel for this schema, and given that some of the= > se > tables contain 100,000-200,000 tuples, some que= > ries > are taking a good 5-10 seconds to execute.= >   > I’m just wondering if this is simply the fault of my schema or= > are > these queries poorly constructed? style=3D'mso-spacerun:yes'> Here are some queries I’ve be= > en > trying:

> >

0.0pt; > font-family:Arial'> 

> >

0.0pt; > font-family:Arial'>Here’s a query for the top ten all time home run > leaders:

> >

style=3D'font-size:10.0pt;font-family:System;mso-bidi-font-family:Arial'>se= > lect size=3D2 face=3DSystem> so-bidi-font-family: > Arial'> P.first_name, P.la= > st_name, > S.player_id, sum(B.hr n>) as > hr, (sum(B.h)::float / sum( >B.ab)::float) > ab

> >

style=3D'font-size:10.0pt;font-family:System;mso-bidi-font-family:Arial'>fr= > om size=3D2 face=3DSystem> so-bidi-font-family: > Arial'> statistics S, batting_stats B, players = > P

> >

style=3D'font-size:10.0pt;font-family:System;mso-bidi-font-family:Arial'>wh= > ere size=3D2 face=3DSystem> so-bidi-font-family: > Arial'> S.id=3DB.id= > and class=3DSpellE>S.player_id=3DP.id and pan > class=3DSpellE>B.ab>0

> >

style=3D'font-size:10.0pt;font-family:System;mso-bidi-font-family:Arial'>gr= > oup size=3D2 face=3DSystem> so-bidi-font-family: > Arial'> by P.first_name, P= > .last_name, > S.player_id

> >

style=3D'font-size:10.0pt;font-family:System;mso-bidi-font-family:Arial'>or= > der size=3D2 face=3DSystem> so-bidi-font-family: > Arial'> by hr desc limit 10;<= > /font>

> >

10.0pt; > font-family:System;mso-bidi-font-family:Arial'>  nt>

> >

e'> size=3D2 face=3DArial>Se= > lect the > 10 highest batting averages of all time where the batter had 600 or more at > bats.  Also gather the name, y= > ear, > team, hits, at bats...

> >

e'> size=3D2 face=3D"Courier New"> Courier New"'> 

> >

e'> class=3DGramE> font-family: > System;mso-bidi-font-family:"Courier New"'>select size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'> (B.h::float / llE>B.ab) > as avg, B.h, class=3DSpellE>B.ab, S.year, =3DSpellE>P.first_name, > P.last_name, T.city= > , class=3DSpellE>T.name

> >

e'> class=3DGramE> font-family: > System;mso-bidi-font-family:"Courier New"'>from size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'> batting_stats B, statistics S, > players P, Teams T

> >

e'> class=3DGramE> font-family: > System;mso-bidi-font-family:"Courier New"'>where size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'> B.ab > 600 and =3DSpellE>S.id=3D class=3DSpellE>B.id and S.player_id=3D pan > class=3DSpellE>P.id and S.team_id=3D class=3DSpellE>T.id

> >

e'> class=3DGramE> font-family: > System;mso-bidi-font-family:"Courier New"'>order size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'> by avg des= > c > limit 10;

> >

10.0pt; > font-family:System;mso-bidi-font-family:Arial'>  nt>

> >

e'> size=3D2 face=3DArial>To= > find the > names of the single season home run leaders, along with the total number of > home runs, the team name/city and the year:

> >

e'> size=3D2 face=3D"Courier New"> Courier New"'> 

> >

e'> class=3DGramE> font-family: > System;mso-bidi-font-family:"Courier New"'>select size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'> P.first_name, llE>P.last_name, > B.hr, T.name, class=3DSpellE>T.city, S.year from stati= > stics S, > batting_stats B, players P, teams T = >

> >

e'> class=3DGramE> font-family: > System;mso-bidi-font-family:"Courier New"'>where size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'> (S.id=3DB.= > id) > and

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'>(S.player_id=3D llE>P.id) > and

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'>(B.hr>30) and n>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'>(T.id=3DS.t= > eam_id) > limit 10;

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > "Courier New"'> 

> >

e'> size=3D2 face=3DArial>Yo= > u get the > idea.  These queries take a wh= > ile. style=3D'mso-spacerun:yes'>  Is this just the way it is or there > things that can be done to optimize this?&= > nbsp; >

> >

e'> size=3D2 face=3DArial> :p> 

> >

e'> size=3D2 face=3DArial>On= > e separate > issue (the reason why the above examples are all about batting statistics) = > I’m > having is representing the innings pitched statistic. style=3D'mso-spacerun:yes'> The way it is often represented (an= > d the > way it is done in this schema) is something like this 123.0 means exactly 1= > 23 > innings pitched, 123.1 means 123 1/3 innings, and 123.2 means 123 2/3 innin= > gs > pitched.  I’m contemplat= > ing > the best way to write a function that knows how to sum these values > accurately.  Is this something= > that > can be done with PL/PGSQL or should I go straight to something like PLPERL?= > style=3D'mso-spacerun:yes'>  Alternatively, I could research a = > way to > represent fractions in the DB and write a script to convert all values in t= > his > column.  A= > ny > advice here??

> >

e'> size=3D2 face=3DArial> :p> 

> >

e'> size=3D2 face=3DArial>Th= > anks in > advance for any thoughts, comments, suggestions… ont>

> >

e'> size=3D2 face=3DArial> :p> 

> >

e'> size=3D2 face=3DArial>-J= > eff

> >
ottom-alt: > solid windowtext .75pt;padding:0in 0in 1.0pt 0in'> > >

wtext .75pt; > padding:0in;mso-padding-alt:0in 0in 1.0pt 0in'> > style=3D'font-size:10.0pt;font-family:System;mso-bidi-font-family:Arial'> :p> 

> >
> >

ze:10.0pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'> = > ;

> >

e'> class=3DGramE> pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'>create an> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> table leagues

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>(

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          -- > create an integer id field for easier and efficient FK referencing p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>id   &n= > bsp;            = > ;     serial,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>name   = >             <= > span > class=3DSpellE>varchar(50),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>first_year so-tab-count: > 1'>         integer not null= >

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>check (first_year > 185= > 0 and class=3DSpellE>first_year < 2010), p> > >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>last_year o-tab-count: > 1'>         integer p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>check (first_year > 185= > 0 and class=3DSpellE>first_year < 2010), p> > >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>primary key(id),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>constraint chronological_order pan>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>check (last_year >=3D <= > span > class=3DSpellE>first_year)

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>);

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> class=3DGramE> pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'>create an> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> table teams

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>(

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>id   &n= > bsp;            = > ;     serial,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>name   = >             <= > span > class=3DSpellE>varchar(50) not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>city   = >             &nb= > sp;  class=3DSpellE>varchar(50),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>first_year so-tab-count: > 1'>         integer not null= >

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>check (first_year > 185= > 0 and class=3DSpellE>first_year < 2010), p> > >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>last_year o-tab-count: > 1'>         integer p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>check (first_year > 185= > 0 and class=3DSpellE>first_year < 2010), p> > >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>alt_id ab-count: > 2'>            = >    integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>league_id o-tab-count: > 1'>        integer not null,= >

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>primary key(id),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>foreign key(alt_id) refere= > nces > teams(id),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>foreign key(league_id) ref= > erences > leagues(id)

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>on delete cascade,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>constraint chronological_order pan>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>check (last_year >=3D <= > span > class=3DSpellE>first_year)

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>);

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> class=3DGramE> pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'>create an> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> table players

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>(

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>id   &n= > bsp;            = > ;     serial,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>first_name so-tab-count: > 1'>       varchar= > (30),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>last_name o-tab-count: > 1'>       varchar= > (30) > not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>bats   = >             &nb= > sp; char(1) > check (bats in ('L','R','S')),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>throws  &nbs= > p;          char(1) > check (throws in ('L','R')),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>dob count: > 2'>            = >       date,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>primary key(id)

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>);

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> class=3DGramE> pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'>create an> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> table statistics

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>(

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>id   &n= > bsp;            = > ;            &n= > bsp;    serial,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>year   = >             &nb= > sp;            = > integer > not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>g   &nb= > sp;            = >             &nb= > sp;     integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>player_id o-tab-count: > 2'>            = >          integer > not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>team_id tab-count: > 3'>            = >             &nb= > sp;          integer > not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>foreign key(player_id) ref= > erences > players(id)

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>on delete cascade,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>foreign key(team_id) refer= > ences > teams(id)

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>on delete cascade,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>primary key(id)

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>);

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> class=3DGramE> pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'>create an> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> table managing_stats<= > o:p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>(

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>id   &n= > bsp;            = > ;            &n= > bsp;    integer > not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>w   &nb= > sp;            = >             &nb= > sp;     integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>l   &nb= > sp;            = >             &nb= > sp;      integer,<= > /b>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>primary key(id),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>foreign key(id) references statistics(id) span>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>on delete cascade

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>);

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> class=3DGramE> pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'>create an> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> table fielding_stats<= > o:p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>(

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>id   &n= > bsp;            = > ;            &n= > bsp;    integer > not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>pos   &= > nbsp;           &nbs= > p;            &= > nbsp; char(5),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>po ount:3'>           &= > nbsp;           &nbs= > p;        integer, an>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>a   &nb= > sp;            = >             &nb= > sp;     integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>e   &nb= > sp;            = >             &nb= > sp;     integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>dp ount:3'>           &= > nbsp;           &nbs= > p;        integer, an>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>primary key(id),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>foreign key(id) references statistics(id) span>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>on delete cascade

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>);

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> class=3DGramE> pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'>create an> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> table batting_stats :p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>(

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>id   &n= > bsp;            = > ;            &n= > bsp;    integer > not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>ab ount:3'>           &= > nbsp;           &nbs= > p;        integer, an>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>r   &nb= > sp;            = >             &nb= > sp;      integer,<= > /b>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>h   &nb= > sp;            = >             &nb= > sp;     integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>doubles  &nb= > sp;            = >             &nb= > sp;       integer, font>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>triples  &nb= > sp;            = >            integer= > ,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>hr   &n= > bsp;            = > ;            &n= > bsp;    integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>rbi count: > 3'>            = >             &nb= > sp;       integer, font>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>sb ount:3'>           &= > nbsp;           &nbs= > p;        integer, an>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>cs ount:3'>           &= > nbsp;           &nbs= > p;        integer, an>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>bb   &n= > bsp;            = > ;            &n= > bsp;   integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>so   &n= > bsp;            = > ;            &n= > bsp;   integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>sh ount:3'>           &= > nbsp;           &nbs= > p;        integer, an>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>sf ount:3'>           &= > nbsp;           &nbs= > p;         integer, p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>ibb count: > 3'>            = >             &nb= > sp;      integer,<= > /b>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>hbp count: > 3'>            = >             &nb= > sp;     integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>primary key(id),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>foreign key(id) references statistics(id) span>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>on delete cascade

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>);

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> class=3DGramE> pt; > font-family:System;mso-bidi-font-family:System;font-weight:bold'>create an> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> table pitching_stats<= > o:p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>(

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>id   &n= > bsp;            = > ;            &n= > bsp;    integer > not null,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>w   &nb= > sp;            = >             &nb= > sp;     integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>l   &nb= > sp;            = >             &nb= > sp;      integer,<= > /b>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>gs ount:3'>           &= > nbsp;           &nbs= > p;        integer, an>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>cg   &n= > bsp;            = > ;            &n= > bsp;   integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>sh ount:1'>        style=3D'mso-tab-count:2'>        &= > nbsp;           &nbs= > p;   integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>sv ount:3'>           &= > nbsp;           &nbs= > p;        integer, an>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>ip ount:3'>           &= > nbsp;           &nbs= > p;         numeric(5,1), >

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>h   &nb= > sp;            = >             &nb= > sp;     integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DSpellE>er ount:3'>           &= > nbsp;           &nbs= > p;         integer, p>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>hr   &n= > bsp;            = > ;            &n= > bsp;    integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>bb   &n= > bsp;            = > ;            &n= > bsp;   integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>so   &n= > bsp;            = > ;            &n= > bsp;   integer,

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>primary key(id),

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >          class=3DGramE>foreign key(id) references statistics(id) span>

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>   = >             &nb= > sp;        class=3DGramE>on delete cascade

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'>);

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

e'> size=3D2 face=3DSystem> so-bidi-font-family: > System;font-weight:bold'> 

> >

0.0pt; > font-family:Arial'> 

> >
> > > > > > ------=_NextPart_000_0034_01C1686D.B8B2E3E0-- >

Сайт использует файлы cookie для корректной работы и повышения удобства. Нажимая кнопку «Принять» или продолжая пользоваться сайтом, вы соглашаетесь на их использование в соответствии с Политикой в отношении обработки cookie ООО «ППГ», в том числе на передачу данных из файлов cookie сторонним статистическим и рекламным службам. Вы можете управлять настройками cookie через параметры вашего браузера