Home > Articles > Data > SQL

  • Print
  • + Share This
  • 💬 Discuss

Examples of Simple Queries

This section provides several examples of queries based on the concepts that have been discussed. The hour begins with the simplest query you can issue, and builds upon the initial query progressively. 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 a specified column. You can enter code on one line or use a carriage return as desired:

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

A simple query can be issued on a table to get a quick count of the number of records in the table or on the number of values for a column in the table. A count is accomplished by the function COUNT. Although functions are not discussed until later in this book, this function should be introduced here because it is often a part of one of the simplest queries that you can create.

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.

Selecting Data from Another User's Table

Permission must be granted to a user to access another user's table. If no permission has been granted, access is not allowed. You can select data from another user's table after access has been granted (the GRANT command is discussed in Hour 20, "Creating and Using Views and Synonyms") to select from another user's table. To access another user's table in a SELECT statement, you must precede the table name with the schema name or the username that owns (created) the table, as in the following example:

SELECT EMP_ID
FROM SCHEMA.EMPLOYEE_TBL;

NOTE

If a synonym exists in the database for the table to which you desire access, you do not have to specify the schema name for the table. Synonyms are alternate names for tables, which are discussed in Hour 21, "Working with the System Catalog."

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.

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus