Errors [message #1906] |
Mon, 10 June 2002 03:16 |
Hannah
Messages: 2 Registered: June 2002
|
Junior Member |
|
|
Cheeky I know but I have a question for a piece of home work I am doing and I don't know the answer, can anyone help?
Review this SQL statement:
Select ename, emp_number, salary
From employee
Where dept_number=(Select dept_number
From department
Where location In (‘Chicargo’, ‘Atlanta’))
Why may this statement return an error?
1.A multiple-row subquery returns one row
2.A multiple-column subquery returns one column
3.A single-row subquery returns more than one row
4.A multiple-row query uses a single-row subquery
5.A single-row query uses a multiple-row subquery that returns only one row.
I am thinking number 5 am I right?
Also how many types of SQL statements are there?
Hannah
|
|
|
Re: Errors [message #1907 is a reply to message #1906] |
Mon, 10 June 2002 04:39 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Here you go:
1 Select ename, empno, sal
2 From emp
3 Where deptno=(Select deptno
4 From dept
5 Where loc In ('CHICAGO', 'DALLAS'
6 )
7* )
SQL> /
Where deptno=(Select deptno
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
If you come to think about it, it is logical: You attempt to do an equation of a single value (deptno) with a multi-row subquery. Because of the 'IN' in the subquery, you're very likely to select more than one row in this subquery, hence the error.
As of your second question:
4 DML statements (INSERT, UPDATE, DELETE and SELECT) In oracle 9i a fifth exists, namely MERGE)
As for DDL statements there are a lot of them (CREATE, ALTER, DROP.....)
MHE
|
|
|