Re: SQL QUERIES

> 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