Examples of Simple Queries

The tutorial egins with the simplest query you can issue, You use the EMPLOYEE_TBL table.

Selecting all records from a table and displaying all columns:

SELECT * FROM EMPLOYEE_TBL;

Selecting all records from a table and displaying a specified column. 

SELECT EMP_ID FROM EMPLOYEE_TBL;

Selecting all records from a table and displaying multiple columns separated by commas:

SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL;

Displaying data for a given condition:

SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '333333333';

Displaying data for a given condition and sorting the output:

SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY EMP_ID;

Displaying data for a given condition and sorting the output on multiple columns, one column sorted in reverse order:

SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY EMP_ID, LAST_NAME DESC;

Displaying data for a given condition and sorting the output using an integer in the place of the spelled-out column name:

SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY 1;

Displaying data for a given condition and sorting the output by multiple columns using integers, the order of the columns in the sort is different than their corresponding order after the SELECT keyword:

SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY 2, 1;

When selecting all rows of data from a large table, the results could render a substantial amount of data returned.

Counting the Records in a Table

In simple query can  we used count function for find the number of records in the table. A count is accomplished by the function COUNT.

The syntax of the COUNT function is as follows:

SELECT COUNT(*)
FROM TABLE_NAME;

The COUNT function is used with parentheses, which are used to enclose the target column to count or the asterisk to count all rows of data in the table.

Counting the number of records in the PRODUCTS_TBL table:

SELECT COUNT(*) FROM PRODUCTS_TBL; 

COUNT(*)
----------
    9


1 row selected.

Counting the number of values for PROD_ID in the PRODUCTS_TBL table:

SELECT COUNT(PROD_ID) FROM PRODUCTS_TBL;

COUNT(PROD_ID)
---------------
       9


1 row selected.

Interesting note: Counting the number of values for a column is the same as counting the number of records in a table, if the column being counted is NOT NULL (a required column). However, COUNT(*) is typically used for counting the number of rows for a table.

Column Aliases

Column aliases are used to rename a table's columns for the purpose of a particular query. The PRODUCTS_TBL illustrates the use of column aliases.

SELECT COLUMN_NAME ALIAS_NAME
FROM TABLE_NAME;

The following example displays the product description twice, giving the second column an alias named PRODUCT. Notice the column headers in the output.

select prod_desc,
    prod_desc product
from products_tbl;

PROD_DESC                 PRODUCT
------------------------- ------------------------
WITCHES COSTUME           WITCHES COSTUME
PLASTIC PUMPKIN 18 INCH   PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH      FALSE PARAFFIN TEETH
LIGHTED LANTERNS          LIGHTED LANTERNS
ASSORTED COSTUMES         ASSORTED COSTUMES
CANDY CORN                CANDY CORN
PUMPKIN CANDY             PUMPKIN CANDY
PLASTIC SPIDERS           PLASTIC SPIDERS
ASSORTED MASKS            ASSORTED MASKS
KEY CHAIN                 KEY CHAIN
OAK BOOKSHELF             OAK BOOKSHELF


11 rows selected.

Column aliases can be used to customize names for column headers, and can also be used to reference a column with a shorter name in some SQL implementations.

NOTE

When a column is renamed in a SELECT statement, the name is not a permanent change. The change is only for that particular SELECT statement.