Home » SQL & PL/SQL » SQL & PL/SQL » Where can subqueries be used?
Where can subqueries be used? [message #405897] Sun, 31 May 2009 08:22 Go to next message
xyzt
Messages: 27
Registered: April 2008
Junior Member
Where can subqueries be used?
A-) The HAVING clause in the SELECT statement.
B-) The WHERE clause only in the SELECT statement.
C-) The WHERE clause in SELECT as well as all DML statements.
D-) The FROM clause in the SELECT statement.
E-) Field names in the SELECT statement.
F-) The GROUP BY clause in the SELECT statement.

(Choose all that apply.)

For me:
A is clear
C is clear
E is not clear

May someone give an example for the F option please?

Thanks in advance.
Re: Where can subqueries be used? [message #405898 is a reply to message #405897] Sun, 31 May 2009 08:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions001.htm#i1002626

Ross Leishman
Re: Where can subqueries be used? [message #405908 is a reply to message #405897] Sun, 31 May 2009 10:17 Go to previous messageGo to next message
xyzt
Messages: 27
Registered: April 2008
Junior Member
Thanks for your help but actually I couldn't find useful
information about my question(possibly my fault)

I found a similar question in
SQL Fundamentals I Exam Guide (Oracle Press)

The is like that:
Where can be the subqueries be used?
SELECT select_list
FROM table
WHERE condition
GROUP BY expression_1
HAVING expression_2
ORDER BY expression_3 ;

answer is: select_list,table,condition,expression_1,expression_2

But in the first question I've send (the question above that i get it from the Test King exam), it says "you can not use subqueries in the Group By clause in the select statement."

Which one is right?
Re: Where can subqueries be used? [message #405969 is a reply to message #405908] Mon, 01 June 2009 03:33 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member

SQL> SELECT   package_id, SUM (package_line_id)
  2      FROM (SELECT package_id, package_line_id, 1 dummy
  3              FROM kdlv_package_lines
  4             WHERE package_id BETWEEN 30006 AND 30028)
  5  GROUP BY (SELECT 'package_id'
  6              FROM DUAL)
  7  /
GROUP BY (SELECT 'package_id'
          *
ERROR at line 5:
ORA-22818: subquery expressions not allowed here



So you cant use subquery in group by clause
Re: Where can subqueries be used? [message #405991 is a reply to message #405908] Mon, 01 June 2009 07:33 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
xyzt wrote on Mon, 01 June 2009 01:17
Thanks for your help but actually I couldn't find useful
information about my question(possibly my fault)


Must have stopped reading a bit early. The syntax diagram shown listed all of the different types of expressions:
Oracle® Database SQL Reference 10g Release 2 (10.2)
{ simple_expression
| compound_expression
| case_expression
| cursor_expression
| datetime_expression
| function_expression
| interval_expression
| object_access_expression
| scalar_subquery_expression
| model_expression
| type_constructor_expression
| variable_expression
}


Then - most importantly - it goes on to say
Oracle® Database SQL Reference 10g Release 2 (10.2)
Oracle Database does not accept all forms of expressions in all parts of all SQL statements.

And then whets our collective appetites with:
Oracle® Database SQL Reference 10g Release 2 (10.2)
The sections that follow describe and provide examples of the various forms of expressions.


A quick flick through the pages that follow - which are inconveniently NOT hyperlinked - leads to the page on Scalar Sub-query expressions, which reads:

Oracle® Database SQL Reference 10g Release 2 (10.2)
You can use a scalar subquery expression in most syntax that calls for an expression (expr). However, scalar subqueries are not valid expressions in the following places:

  • As default values for columns

  • As hash expressions for clusters

  • In the RETURNING clause of DML statements

  • As the basis of a function-based index

  • In CHECK constraints

  • In WHEN conditions of CASE expressions

  • In GROUP BY and HAVING clauses

  • In START WITH and CONNECT BY clauses

  • In statements that are unrelated to queries, such as CREATE PROFILE



Ross Leishman
Previous Topic: insert into a table using view
Next Topic: Installing DBMS_XMLSCHEMA
Goto Forum:
  


Current Time: Sat Dec 10 10:33:13 CST 2016

Total time taken to generate the page: 0.09284 seconds