Обсуждение: SQL QUERIES

Hi can somebody help me with some queries for my problem database.


I want to report:

1.  Which device/software that (practically) never shows up some problems.

2. A top 10 of the most occuring problems

3. The need of more time that a employee needs to solve a problem over the
average solving time.


I am just beginning to study sql so my knowledge is very low. :(

Can some body help me with these queries?


Greets,

Menno Pieper

> I want to report:
> 1.  Which device/software that (practically) never shows up some problems.

select item from items_tbl
 where item not in (select distinct item from problem_tbl);

(there may be more efficient ways to form this query)

> 2. A top 10 of the most occuring problems

select problem, count(problem) as num from problem_tbl
 group by problem order by num desc limit 10;

> 3. The need of more time that a employee needs to solve a problem over the
> average solving time.

It is expensive to calculate an average from scratch each time you want
to compare a row with it. So I would suggest having a table which holds
the expected times for each problem (or problem type), which you can
then adjust as needed.

begin;
delete from timing_tbl;
select problem, avg(solution_time) as solution_time
 into timing_tbl from problem_tbl
 group by problem;
end;

select employee as stupid from problem_tbl p, timing_tbl t
 where (t.problem = p.problem)
  and (p.solution_time > 1.5*t.solution_time);


hth

                    - Thomas

I have a tabl called 'unit' with a field 'room' of type character
varying(20). I want to change the type to character varying(40).
How do I do this without affecting any data in the table.

Thanksâ

--
David A Dickson
david.dickson@mail.mcgill.ca

You can either:

a)  create table unittemp as select field1, field2, cast(room as
varchar(20)), ..., fieldn from unit;
    drop table unit;
    alter table unittemp rename to unit;

b)  pg_dump -t unit [dbname] > unit.pgsql
    edit unit.pgsql and change varchar(20) to varchar(40) and add a line to
beginning of file "drop table unit"
    pgsql [dbname] < unit.pgsql

Frank

At 12:03 PM 2/4/02 -0500, David A Dickson wrote:
>I have a tabl called 'unit' with a field 'room' of type character
>varying(20). I want to change the type to character varying(40).
>How do I do this without affecting any data in the table.
>
>Thanksâ
>
>--
>David A Dickson
>david.dickson@mail.mcgill.ca
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

>You can either:
>
>a)  create table unittemp as select field1, field2, cast(room as
>varchar(20)), ..., fieldn from unit;
>    drop table unit;
>    alter table unittemp rename to unit;
>
>b)  pg_dump -t unit [dbname] > unit.pgsql
>    edit unit.pgsql and change varchar(20) to varchar(40) and add a line to
>beginning of file "drop table unit"
>    pgsql [dbname] < unit.pgsql
>
>Frank
>
>At 12:03 PM 2/4/02 -0500, David A Dickson wrote:
> >I have a tabl called 'unit' with a field 'room' of type character
> >varying(20). I want to change the type to character varying(40).
> >How do I do this without affecting any data in the table.

Has anyone discovered any workaround to the lack of a full ALTER
TABLE that preserves the OID for each row? I am finding that for
insurance I have to assign each table an SERIAL of my own to create
an autoincremented ID that I can control - because using OID as a key
requires me to update the key throughout the database if I have to
drop the table.

Elaine Lindelef

You could add "-o" option to pg_dump in option (b) to preserve oid's.

At 11:08 AM 2/4/02 -0800, Elaine Lindelef wrote:
>>You can either:
>>
>>a)  create table unittemp as select field1, field2, cast(room as
>>varchar(20)), ..., fieldn from unit;
>>    drop table unit;
>>    alter table unittemp rename to unit;
>>
>>b)  pg_dump -t unit [dbname] > unit.pgsql
>>    edit unit.pgsql and change varchar(20) to varchar(40) and add a line to
>>beginning of file "drop table unit"
>>    pgsql [dbname] < unit.pgsql
>>
>>Frank
>>
>>At 12:03 PM 2/4/02 -0500, David A Dickson wrote:
>> >I have a tabl called 'unit' with a field 'room' of type character
>> >varying(20). I want to change the type to character varying(40).
>> >How do I do this without affecting any data in the table.
>
>Has anyone discovered any workaround to the lack of a full ALTER
>TABLE that preserves the OID for each row? I am finding that for
>insurance I have to assign each table an SERIAL of my own to create
>an autoincremented ID that I can control - because using OID as a key
>requires me to update the key throughout the database if I have to
>drop the table.
>
>Elaine Lindelef

On Mon, Feb 04, 2002 at 11:08:57AM -0800, Elaine Lindelef wrote:
> Has anyone discovered any workaround to the lack of a full ALTER
> TABLE that preserves the OID for each row? I am finding that for
> insurance I have to assign each table an SERIAL of my own to create
> an autoincremented ID that I can control - because using OID as a key
> requires me to update the key throughout the database if I have to
> drop the table.

Probably a good idea anyway since OIDs are not guarenteed to be unique. At
least by using a SERIAL you will notice when the it wraps around. I think in
7.2 the OID column will be optional.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.