Thread: Can anybody help me with SQL?
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
> 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.
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
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?
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) >
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.
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?
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.
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.
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?
Because no one's got around to it.
Seems like a good idea though.
regards, tom lane
> 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
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.
> > 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