To retrieve data from the database, use the SELECT statement. Once again, proper privileges are required and are maintained by the DBA. The SELECT statement has the following format:
SELECT column(s)
FROM tables(s)
WHERE conditions are met
GROUP BY selected columns
ORDER BY column(s);
Every SQL statement ends with a semicolon (;). When you are writing scripts (disk files) that will be executed, you can also use a slash (\) to terminate the SQL statement.
When SELECT column(s) is used, it is assumed that all of the columns fitting the WHERE clause will be retrieved. It is sometimes necessary to only retrieve columns that are distinct from one another. To do this, use the reserved word DISTINCT before the column descriptions. In the following example, a SELECT statement is used to retrieve all of the cities and states from the addresses table (defined previously).
SELECT city, state
FROM addresses;
When this code run, every city and state will be retrieved from the table. If 30 people lived in Rochester, NY, the data would be displayed 30 times. To see only one occurrence for each city and state use the DISTINCT qualifier, as shown in the following example:
SELECT DISTINCT city, state
FROM addresses;
This will cause only one row to be retrieved for entries with Rochester, NY.
The FROM clause is a listing of all tables needed for the query. You can use table aliases to help simplify queries, as shown in the following example:
SELECT adrs.city, adrs.state
FROM addresses adrs;
In this example, the alias adrs has been given to the table addresses. The alias will be used to differentiate columns with the same name from different tables.
The WHERE clause is used to list the criteria necessary to restrict the output from the query or to join tables in the FROM clause. See the following example.
SELECT DISTINCT city, state
FROM addresses
WHERE state in ('CA','NY','CT')
AND city is NOT NULL;
This example will retrieve cities and states that are in the states of California, New York, and Connecticut. The check for NOT NULL cities will not bring data back if the city field was not filled in.
The GROUP BY clause tells Oracle how to group the records together when certain functions are used.
SELECT dept_no, SUM(emp_salary)
FROM emp
GROUP BY dept_no;
The GROUP BY example will list all department numbers once with the summation of the employee salaries for that particular department.
SELECT column(s)
FROM tables(s)
WHERE conditions are met
GROUP BY selected columns
ORDER BY column(s);
Every SQL statement ends with a semicolon (;). When you are writing scripts (disk files) that will be executed, you can also use a slash (\) to terminate the SQL statement.
When SELECT column(s) is used, it is assumed that all of the columns fitting the WHERE clause will be retrieved. It is sometimes necessary to only retrieve columns that are distinct from one another. To do this, use the reserved word DISTINCT before the column descriptions. In the following example, a SELECT statement is used to retrieve all of the cities and states from the addresses table (defined previously).
SELECT city, state
FROM addresses;
When this code run, every city and state will be retrieved from the table. If 30 people lived in Rochester, NY, the data would be displayed 30 times. To see only one occurrence for each city and state use the DISTINCT qualifier, as shown in the following example:
SELECT DISTINCT city, state
FROM addresses;
This will cause only one row to be retrieved for entries with Rochester, NY.
The FROM clause is a listing of all tables needed for the query. You can use table aliases to help simplify queries, as shown in the following example:
SELECT adrs.city, adrs.state
FROM addresses adrs;
In this example, the alias adrs has been given to the table addresses. The alias will be used to differentiate columns with the same name from different tables.
The WHERE clause is used to list the criteria necessary to restrict the output from the query or to join tables in the FROM clause. See the following example.
SELECT DISTINCT city, state
FROM addresses
WHERE state in ('CA','NY','CT')
AND city is NOT NULL;
This example will retrieve cities and states that are in the states of California, New York, and Connecticut. The check for NOT NULL cities will not bring data back if the city field was not filled in.
The GROUP BY clause tells Oracle how to group the records together when certain functions are used.
SELECT dept_no, SUM(emp_salary)
FROM emp
GROUP BY dept_no;
The GROUP BY example will list all department numbers once with the summation of the employee salaries for that particular department.
