Thread: Can anybody help me with SQL?

Can anybody help me with SQL?

From
"Richard Lockwood"
Date:
Hi

I was wondering if anyone could help me with the following SQL questions:


1.Which car was rented for the longest single period? (regno)

SELECT regno, MAX(datein-dateout) "Longest Single Period" FROM rental group
by regno;

Although this works - it shows ALL the regno's longest periods - how would i
just display the regno?

2. Which car was rented for the longest total period? (regno)
3. Which customers (if any) were born on Monday? (surname)

For this question i thought that this would work:

SELECT * FROM cust WHERE TO_CHAR(cd_o_b,'DAY') = 'MONDAY'

But it never works - it says 'no rows selected'
although there are 2 cusotmers born on Monday
However if you put wednesday instead of monday or any other day it seems to
work correctly
Anyone know why this is?

4. Which customer averaged the most miles/day? (surname)(single rental)
5. Which customer averaged the least miles/day? (surname)(single rental)
6. Which customer had to wait for the previous renter to return the car?
(surname)
6. What % of rentals are for one day only?
7. Can cars be rented on a Sunday? (i.e. are there any?) (regno)
8. List the total amount of money paid to each garage during this period.
9. What is the average miles driven per day?

The rest of the questions just baffle me as i cannot work them out at all.

If you can give me any help at all it would be very much appreciated!

The data/tables are defined below:

SERVICE
regno
gid
sdate
smileage
scost
servrep

CAR
regno
model_id
curr_milge
next_serv
cyear
colour

MODEL
model_id
make
unit_price
mileage_da

REPLACE
regno
gid
pno

PART
partno
pdesc

RENTAL
hireno
regno
rentalno
dateout
datein
milesout
milesin
cost

CUST
custno
cname
cname_1
cadd_1
ccode
cd_o_b

GARAGE
gid
gname
gadd

Any help would be very much appreciated!

Thanks

Rickardo


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp


Re: Can anybody help me with SQL?

From
Stephan Szabo
Date:

> 1.Which car was rented for the longest single period? (regno)
>
> SELECT regno, MAX(datein-dateout) "Longest Single Period" FROM rental group
> by regno;
>
> Although this works - it shows ALL the regno's longest periods - how would i
> just display the regno?

There's probably a better way, but if I'm getting the question you want
answered right I think these will do it.

select regno, m from rental, (select max(datein-dateout) as m from rental)
foo where m=datein-dateout;

or

select regno, datein-dateout from rental where datein-dateout=(select
max(datein-dateout) from rental);

> 2. Which car was rented for the longest total period? (regno)
Maybe something like this?  Not entirely sure

select regno, t from (select regno, sum(datein-dateout) as t from rental
group by regno) r where t=(select max(total) from (select
sum(datein-dateout) as total from rental group by regno) foo);

> 3. Which customers (if any) were born on Monday? (surname)
>
> For this question i thought that this would work:
>
> SELECT * FROM cust WHERE TO_CHAR(cd_o_b,'DAY') = 'MONDAY'
>
> But it never works - it says 'no rows selected'
> although there are 2 cusotmers born on Monday
> However if you put wednesday instead of monday or any other day it seems to
> work correctly
> Anyone know why this is?

To_char fills out to a given length with spaces, I'd guess that the value
is actually something like 'MONDAY   '
I think you probably want the format FMDAY which should not have the
spaces.




Re: [NOVICE] Can anybody help me with SQL?

From
Andrew Bell
Date:
At 08:42 AM 12/10/2001 +0000, Richard Lockwood wrote:
>4. Which customer averaged the most miles/day? (surname)(single rental)
>5. Which customer averaged the least miles/day? (surname)(single rental)
>6. Which customer had to wait for the previous renter to return the car?
>(surname)
>6. What % of rentals are for one day only?
>7. Can cars be rented on a Sunday? (i.e. are there any?) (regno)
>8. List the total amount of money paid to each garage during this period.
>9. What is the average miles driven per day?

This sounds like a homework problem to me :)


-- Andrew Bell
acbell@iastate.edu



how does one replace a row in Postgresql? (ala MySQL REPLACE)

From
Terrence Brannon
Date:
A common task is to check to see if a row with a certain primary 
key exists before inserting it. If the row exists, then you 
simply update/overwrite its contents with the new row data. 
Otherwise you insert it.

MySQL has a REPLACE command which does this.

I didn't see one in the Postgresql manual. How do you do this in 
Postgresql?



Re: how does one replace a row in Postgresql? (ala MySQL REPLACE)

From
Philip Hallstrom
Date:
Try to UPDATE the row.  If that fails, then insert it.

There's no REPLACE in postgresql (unless someone added it and forgot to
tell me :)

On Tue, 11 Dec 2001, Terrence Brannon wrote:

> A common task is to check to see if a row with a certain primary
> key exists before inserting it. If the row exists, then you
> simply update/overwrite its contents with the new row data.
> Otherwise you insert it.
>
> MySQL has a REPLACE command which does this.
>
> I didn't see one in the Postgresql manual. How do you do this in
> Postgresql?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>



Re: how does one replace a row in Postgresql? (ala MySQL REPLACE)

From
Bruno Wolff III
Date:
On Tue, Dec 11, 2001 at 02:09:19PM -0800, Terrence Brannon <metaperl@mac.com> wrote:
> A common task is to check to see if a row with a certain primary 
> key exists before inserting it. If the row exists, then you 
> simply update/overwrite its contents with the new row data. 
> Otherwise you insert it.
> 
> MySQL has a REPLACE command which does this.
> 
> I didn't see one in the Postgresql manual. How do you do this in 
> Postgresql?

Another option that doesn't require testing of the response from one
sql statement before executing another is to delete rows with the
matching primary key. Then do an insert.


Numbering Rows (SEQUENCE, OID) questions

From
Terrence Brannon
Date:
The Momjian book is excellent (in spite of some of the bonehead 
reviews on amazon.com). I just finished the "Numbering Rows" 
section and have a few questions. None of these were in the FAQ, 
BTW.

1 - are OIDs ever re-used if rows are deleted?

2 - for both SEQUENCE and OID, when might we expect them to 
rollover? Ie, what is the largest integer we can use for either 
of these?

3 - What does one do once the rollover limit is reached if one 
wants to continue to add new row numbers?



Re: Numbering Rows (SEQUENCE, OID) questions

From
Stephan Szabo
Date:
On Sun, 16 Dec 2001, Terrence Brannon wrote:

> The Momjian book is excellent (in spite of some of the bonehead
> reviews on amazon.com). I just finished the "Numbering Rows"
> section and have a few questions. None of these were in the FAQ,
> BTW.
>
> 1 - are OIDs ever re-used if rows are deleted?

OIDs wraparound, but they don't just fill holes, so uniqueness
isn't guaranteed unless you have something like a unique index
on oid.

> 2 - for both SEQUENCE and OID, when might we expect them to
> rollover? Ie, what is the largest integer we can use for either
> of these?

oids are int4. In 7.1 and earlier sequences are int4, for 7.2 I
believe they're int8.

> 3 - What does one do once the rollover limit is reached if one
> wants to continue to add new row numbers?

If you're using oids, and you have a unique index on oid, you'll
get random failures insert if you happen to hit an already used oid.
If you don't have the unique index you'll get dups.

If you're using a sequence, you should be able to compress down
holes in the sequence and change rows that refer to rows of this one
and then point the sequence to give you the next available number.
A not terribly efficient way would be to make a new sequence, lock the
table you're compressing and any dependents, generate a table with the old
key value and a nextval from the sequence and then update both the
original table and dependents based on the mapping table and then set the
original table's sequence to get the following value and drop the new
sequence and table all in one transaction.



why doesn't \d table_name show the table inheritance graph?

From
Terrence Brannon
Date:
The Momjian books shows this:


 test=> CREATE TABLE parent_test (col1 INTEGER);
        CREATE
        test=> CREATE TABLE child_test (col2 INTEGER) INHERITS 
(parent_test);
        CREATE
        test=> \d parent_test
                 Table "parent_test"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col1      | integer | 
         
        test=> \d child_test
                 Table "child_test"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col1      | integer | 
         col2      | integer |   

but I think it would be very important, to know inheritance as to 
know indexes.



temp tables versus the global sql area

From
Terrence Brannon
Date:
In the Momjian book it is stated:

Temporary tables are ideal for holding intermediate data used by 
the current SQL session. For example, suppose you need to do many 
SELECTs on the result of a complex query. An efficient strategy 
is to execute the complex query once, then store the result in a 
temporary table.


...However, my question is doesn't postgresql calculate the cost 
of complex queries and store there result in some sort of global 
sql area sorted by LRU or cost so that you can simply write 
complex queries and take care of automatic caching?



Re: why doesn't \d table_name show the table inheritance graph?

From
Tom Lane
Date:
Because no one's got around to it.

Seems like a good idea though.
        regards, tom lane


Re: temp tables versus the global sql area

From
Bruce Momjian
Date:
> In the Momjian book it is stated:
> 
> Temporary tables are ideal for holding intermediate data used by 
> the current SQL session. For example, suppose you need to do many 
> SELECTs on the result of a complex query. An efficient strategy 
> is to execute the complex query once, then store the result in a 
> temporary table.
> 
> 
> ...However, my question is doesn't postgresql calculate the cost 
> of complex queries and store there result in some sort of global 
> sql area sorted by LRU or cost so that you can simply write 
> complex queries and take care of automatic caching?

Nope.  Sorry.  Those caches would have to be invalidated if someone made
a change to those tables.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Numbering Rows (SEQUENCE, OID) questions

From
Terrence Brannon
Date:
On Sunday, December 16, 2001, at 11:42 AM, Stephan Szabo wrote:

> On Sun, 16 Dec 2001, Terrence Brannon wrote:
>
>> The Momjian book is excellent (in spite of some of the bonehead
>> reviews on amazon.com). I just finished the "Numbering Rows"
>> section and have a few questions. None of these were in the FAQ,
>> BTW.
>>
>> 1 - are OIDs ever re-used if rows are deleted?
>
> OIDs wraparound, but they don't just fill holes, so uniqueness
> isn't guaranteed unless you have something like a unique index
> on oid.

Where do you get this information? If I am reading the Momjian 
book correctly, it disagrees with you:

Every row in POSTGRESQL is assigned a unique, normally invisible 
number called an object identification number (OID). When the 
software is initialized with initdb , 12.1 a counter is created 
and set to approximately seventeen-thousand. 12.2 The counter is 
used to uniquely number every row. Although databases may be 
created and destroyed, the counter continues to increase. It is 
used by all databases, so identification numbers are always 
unique. No two rows in any table or in any database will ever 
have the same object ID.



Re: Numbering Rows (SEQUENCE, OID) questions

From
Bruce Momjian
Date:
> 
> On Sunday, December 16, 2001, at 11:42 AM, Stephan Szabo wrote:
> 
> > On Sun, 16 Dec 2001, Terrence Brannon wrote:
> >
> >> The Momjian book is excellent (in spite of some of the bonehead
> >> reviews on amazon.com). I just finished the "Numbering Rows"
> >> section and have a few questions. None of these were in the FAQ,
> >> BTW.
> >>
> >> 1 - are OIDs ever re-used if rows are deleted?
> >
> > OIDs wraparound, but they don't just fill holes, so uniqueness
> > isn't guaranteed unless you have something like a unique index
> > on oid.
> 
> Where do you get this information? If I am reading the Momjian 
> book correctly, it disagrees with you:
> 
> Every row in POSTGRESQL is assigned a unique, normally invisible 
> number called an object identification number (OID). When the 
> software is initialized with initdb?, 12.1 a counter is created 
> and set to approximately seventeen-thousand. 12.2 The counter is 
> used to uniquely number every row. Although databases may be 
> created and destroyed, the counter continues to increase. It is 
> used by all databases, so identification numbers are always 
> unique. No two rows in any table or in any database will ever 
> have the same object ID.

The book asssume you are not going to roll over the counter.  Very large
installations have been concerned about such rollover after inserting >4
billion rows.  We have not gotten any actual report of it happening, but
it could theoretically happen.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026