Subqueries

Explain subqueries with an example.

A subquery is a query within a query. The inner query is processed first. They are most commonly used to return data from multiple tables when the exact value is not known.

Example:
To display salaries of all employees in company test.
SELECT salary, emp_name FROM employee WHERE emp_name IN (SELECT emp_name FROM company WHERE company_name = 'test')

A multiple-row subquery with an example

A multiple row subquery returns one or more rows to the outer SQL query.

To display salaries of all employees with names like e in company.
SELECT salary, emp_name FROM employee WHERE emp_name IN (SELECT emp_name FROM company WHERE company_name LIKE ‘%e%’)

Single-row subqueries with an example

A single row subquery returns a maximum of one row to outer SQL query.

To display salary of an employee with id 4 in company test
SELECT salary FROM employee WHERE emp_name IN (SELECT emp_name FROM company WHERE emp_id= ‘4’)
SQL*Plus
SQL*Plus - What is SQL*Plus? Explain its features, Explain how to use the SQL*Plus utility, What are the basic SQL*Plus commands?, What is AFIEDT.BUF?, Explain how to enable the SQL*Plus HELP facility...
Oracle database tables
Oracle database tables - What is a Database Table?, How many types of tables supported by Oracle? Explain them, Can you explain how to create a aew table in your schema?, Explain with an example how to create a new table by selecting rows from another table, Depict a sample script for renaming an existing table...
Oracle constraints FAQs
Oracle constraints - Oracle constraints - overview. What are the types of constraints avaialable in oracle, Explain Oracle Check constraint, Explain Not Null constraint, primary key constraint, oracle foreign key constraint, unique Constraint...
Post your comment
Discussion Board
indexes and subqueries
this is a not good ans to subquries and indexes plz.......... give me the best answer .............. to subqueries and indexes
ashish khanduri 11-11-2012

You may like