Database query examples
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:
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."