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, re****ted
> 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, re****ted 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, re****ted to the
vendor.
Excellent. That you re****ted 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, re****ted to the vendor.
Not surprised either. Which was my original point.
Better tool than I would have thought: What is it.
--
Daniel A. Morgan
University of Wa****ngton
damorgan@[EMAIL PROTECTED]
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
