Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Select Statement – Part XII

Posted by Ravi Varma Thumati on October 15, 2009

The SELECT statement allows you to retrieve records from one or more tables in your database.

The syntax for the SELECT statement is:

SELECT columns
FROM tables
WHERE predicates;

Example #1

Let’s take a look at how to select all fields from a table.

SELECT * FROM suppliers WHERE city = ‘Newark’;

In our example, we’ve used * to signify that we wish to view all fields from the suppliers table where the supplier resides in Newark.

Example #2

You can also choose to select individual fields as opposed to all fields in the table.

For example:

SELECT name, city, state
FROM suppliers
WHERE supplier_id > 1000;

This select statement would return all name, city, and state values from the suppliers table where the supplier_id value is greater than 1000.

Example #3

You can also use the select statement to retrieve fields from multiple tables.

SELECT orders.order_id, suppliers.name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

The result set would display the order_id and suppier name fields where the supplier_id value existed in both the suppliers and orders table.

DISTINCT Clause

The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.

The syntax for the DISTINCT clause is:

SELECT DISTINCT columns
FROM tables
WHERE predicates;

Example #1

Let’s take a look at a very simple example.

SELECT DISTINCT city FROM suppliers;

This SQL statement would return all unique cities from the suppliers table.

Example #2

The DISTINCT clause can be used with more than one field.

For example:

SELECT DISTINCT city, state FROM suppliers;

This select statement would return each unique city and state combination. In this case, the distinct applies to each field listed after the DISTINCT keyword.

Subqueries

What is a subquery?

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

WHERE clause

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

select * from all_tables tabs

where tabs.table_name in                          (select cols.table_name

from all_tab_columns cols

where cols.column_name = ‘SUPPLIER_ID’);

Limitations:

Oracle allows up to 255 levels of subqueries in the WHERE clause.

FROM clause

A subquery can also be found in the FROM clause. These are called inline views.

For example:

select suppliers.name, subquery1.total_amt
from suppliers,
(select supplier_id, Sum(orders.amount) as total_amt
from orders
group by supplier_id) subquery1,
where subquery1.supplier_id = suppliers.supplier_id;

In this example, we’ve created a subquery in the FROM clause as follows:

(select supplier_id, Sum(orders.amount) as total_amt
from orders
group by supplier_id) subquery1

This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

Limitations:

Oracle allows an unlimited number of subqueries in the FROM clause.

SELECT clause

A subquery can also be found in the SELECT clause.

For example:

select tbls.owner, tbls.table_name,
(select count(column_name) as total_columns
from all_tab_columns cols
where cols.owner = tbls.owner
and cols.table_name = tbls.table_name) subquery2
from all_tables tbls;

In this example, we’ve created a subquery in the SELECT clause as follows:

(select count(column_name) as total_columns
from all_tab_columns cols
where cols.owner = tbls.owner
and cols.table_name = tbls.table_name) subquery2

The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.

The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: