Queries: Selecting Data from a Table

An SQL query requests data from the database and receives the results. This process, also known as data retrieval, is expressed using the select statement.

A query has three main parts: the select clause, the from clause, and the where clause.

The query process, also known as data retrieval, is used for selections, which retrieve a subset of the rows in one or more tables, or for projections, which retrieve a subset of the columns in one or more tables.

A simple example of a select statement is:

select select_list 
from table_list 
where search_conditions 

The select clause specifies the columns you want to retrieve. The from clause specifies the tables to search. The where clause specifies which rows in the tables you want to see. For example, the following select statement finds, in the pubs2 database, the first and the last names of writers living in Oakland from the authors table:

select au_fname, au_lname 
from authors 
where city = "Oakland"

Results of this query appear in columnar format:

au_fname        au_lname 
--------------  ----------- 
Marjorie        Green 
Dick            Straight 
Dirk            Stringer 
Stearns         MacFeather 
Livia           Karsen
 
(5 rows affected)

• select Syntax
  A simple select statement contains only the select clause; the from clause is almost always included, but is necessary only in select statements that retrieve data from tables.
• Choose Columns Using the select Clause
  The items in the select clause make up the select list. When the select list consists of a column name, a group of columns, or the wildcard character (*), the data is retrieved in the order in which it is stored in the table (create table order).
• select for update
  select for update exclusively locks rows in datarows-locked tables for subsequent updates within the same transaction, and for updatable cursors.
• Eliminate Duplicate Query Results with Distinct
  The distinct keyword eliminates duplicate rows from the default results of a select statement.
• Specify Tables with the from Clause
  The from clause is required in every select statement that retrieves data from tables or views. Use it to list all the tables and views containing columns included in the select list and in the where clause.
• Select Rows Using the where Clause
  The where clause in a select statement specifies the search conditions that determine which rows are retrieved.
• Multiple select Items in a Nested exists Query
  You can use multiple select items in nested queries.
• Use a Column Alias in Nested select Statements
  You can use an column alias in the select list of nested select statements.