Re: suggestions for good graphical DB query builder
Date: Fri, 28 Sep 2007 12:23:43 -0700
Message-ID: <1191007413.718883_at_bubbleator.drizzle.com>
Martijn Tonies wrote:
>>>> Better to invest the money in good training than in a dumb-as-dirt >>>> GUI. >>> Although I do agree with you that an understanding of SQL helps >>> you to write proper queries, your opinion about tools producing >>> simplistic queries is a bit over the top, in my opinion. >>> >>> There are tools available that allow you to use derived tables, >>> sub-selects, multiple unions, complex multi joins etc etc. It's not >>> all "select * from ... " :-)
>
> For your convenience, I've tried your examples in our own Query Builder
> included in Database Workbench, which is based on a third party product.
>
>> Can you find INTERSECT and MINUS?
>
> Yes, no problem whatsoever.
>
>> Can you find WITH?
>
> Parsed and used correctly, did notice a minor GUI error, reported
> to the vendor.
>
>> Can you find the SAMPLE clause?
>
> Can you give me an example? :)
SELECT *
FROM t
SAMPLE(1);
>> Can you find regular expressions? >> Can you find the analytic functions? >> Can you find CONNECT BY PRIOR?
>
> I'm not sure what you mean by "find" here? You mean being able to
> select them from a list?
Can you write a query like any of these?
SELECT REGEXP_SUBSTR('Go to http://www.oracle.com/products and click on database', 'http://([[:alnum:]]+\.?){3,4}/?') RESULT FROM dual;
SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(PARTITION BY submit_date ORDER BY submit_date ROWS UNBOUNDED
PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;
SELECT "Name", SUM(salary) "Total_Salary"
FROM (
SELECT CONNECT_BY_ROOT last_name "Name", salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id)
GROUP BY "Name";
>> How about CUBE? ROLLUP? GROUPING SETS? GROUP_ID? >> Partition and subpartition selections? >> Database links?
>
> Not tested.
SELECT ch.channel_desc, calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc IN ('Direct Sales', 'Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_name LIKE 'U%'
GROUP BY CUBE(channel_desc, t.calendar_month_desc, co.country_name);
SELECT channel_id, promo_id, SUM(amount_sold) s_sales,
GROUPING(channel_id) AS GC,
GROUPING(promo_id) AS GP,
GROUPING_ID(channel_id, promo_id) AS GCP,
GROUPING_ID(promo_id, channel_id) AS GPC
FROM sales
WHERE promo_id > 496
GROUP BY CUBE(channel_id, promo_id);
SELECT channel_desc, calendar_month_desc, co.country_id,
TO_CHAR(SUM(amount_sold) , '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries co
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND sales.channel_id= channels.channel_id
AND customers.country_id = co.country_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_iso_code IN ('UK', 'US')
GROUP BY GROUPING SETS(
(channel_desc, calendar_month_desc, co.country_id), (channel_desc, co.country_id), (calendar_month_desc, co.country_id));
SELECT DECODE(GROUPING(department_name), '1', 'All Departments',
department_name) AS DEPARTMENT,
DECODE(GROUPING(job_id), '1', 'All Jobs', job_id) AS job,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id);
SELECT ch.channel_desc, t.calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND cu.country_id = co.country_id
AND ch.channel_desc IN ('Direct Sales','Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_name LIKE 'U%'
GROUP BY ROLLUP(ch.channel_desc, t.calendar_month_desc, co.country_name);
>> or this? >> SELECT COUNT(*) >> FROM all_objs >> WHERE data_object_id IS NOT NAN;
>
> Parser failed on NAN, this surprised me as well, reported to the vendor.
Excellent.
>> or this? >> SELECT * >> FROM persons p >> WHERE VALUE(p) IS OF TYPE (employee_t);
>
> Parser failed on OF TYPE, this surprised me as well, reported to the vendor.
Excellent. That you reported it not that it failed.
>> or this? >> SELECT COUNT(*) >> FROM customer_demo >> WHERE cust_address_ntab IS NOT EMPTY;
>
> Parser failed on EMPTY, not surprised anymore, reported to the vendor.
Not surprised either. Which was my original point.
[Quoted] Better tool than I would have thought: What is it.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Sep 28 2007 - 21:23:43 CEST