Exam Topic: Retrieving Data Using the SQL SELECT Statement – List the capabilities of SQL SELECT statements

By far, the most important skill for anyone to possess when working with a database is the skill to extract from the database the data you want in the format you desire. The SELECT statement, therefore, is the most important statement you will ever learn or ever use.

The Basic SELECT

SELECT statements require two clauses:

• SELECT – a list of columns or values to be returned by the query, or * (asterisk) for all columns
• FROM – the source of the data, such as a table, multiple tables or dual

SELECT statements have two optional clauses:

• WHERE – conditions that must be met to return a row
• ORDER BY – a list of fields by which the data will be sorted

Examples:

Return all rows from the customer table.

SQL> SELECT * FROM customer;

Return the current system time and date. Dual is a dummy table that can be used in the FROM clause when you want to select from a function and return one row.

SQL> SELECT sysdate FROM dual;

SELECT Capabilities

Normally, queries involve returning more focused data than all the columns in all the rows of one table. It can involve just a couple columns of just a couple of rows and may involve more than one table. For that, a SELECT statement has the following capabilities:

• Projection – a subset of columns specified in the SELECT clause
• Selection – a subset of rows using the WHERE clause
• Joining – a combination of tables or views in the FROM clause or the WHERE clause

Projection

Projection refers to a subset of columns. Projections are made by specifying the columns to be returned by a SELECT query.

SQL> SELECT first_name, last_name FROM customer;

Selection

Selection refers to a subset of rows. Selections are made my providing conditions in the WHERE clause that amount to true/false statements. For every row where all conditions are true, that row is returned by the query.

SQL> SELECT first_name, last_name 
1  FROM customer 
2  WHERE first_name = 'RANDY';

Only rows where the first_name is Randy will be returned.

NOTE: Oracle is case sensitive by default. “YNGIWULF” and “Yngiwulf” are not the same in Oracle. For this reason, data is often stored in all uppercase in an Oracle database. If you have mixed cases, then you have to convert one side or the other to make the cases match. UPPER and LOWER allow you to do that.

For more than one condition, use AND and OR to string them together. If you use OR, those conditions should be enclosed in parentheses or else strangeness will ensue.

SQL> SELECT first_name, last_name 
1  FROM customer 
2  WHERE first_name = 'RANDY' and state = 'VA';

Show me the first and last name of all the RANDYs where the state code is VA.

SQL> SELECT first_name, last_name 
1  FROM customer 
2  WHERE first_name = 'RANDY' and (state='VA' or country = 'MEX');

Show me the first and last name of all the RANDYs where the state code is VA or the country code is MEX. Note the parentheses. All the RANDYs. Either VA or MEX.

Joining

Joining refers specifying a combination of tables or views. So far, we’ve been using the customer table as an example, which presumably holds basic customer information. If we want to find out the orders connected to a name, we would have to join the customer table with the orders table.

Joining tables is done by matching columns that are in both tables. Normally, these columns will be a primary key in one table and a foreign key in the other table. In the examples given below, both tables have a customer_id. In the customer table, the customer_id is the primary key. The customer_id is in the orders table so that way we have a way of identifying who’s order it is. We’re not going to put the customer’s full information with the order every time (see Third Normal Form). So, we make the customer_id a foreign key in the orders table that references the customer_id in the customer table.

There are two ways to join tables.

In the FROM clause using  JOIN... ON.

SQL> SELECT first_name, order_id
1  FROM customer JOIN orders 
2  ON customer.customer_id = orders.customer_id;

In the WHERE clause using conditions. Notice that the table names are separated by a comma.

SQL> SELECT first_name, order_id
1  FROM customer, orders
2  WHERE customer.customer_id = orders.customer_id;

ORDER BY

The ORDER BY clause lets you specify the sorting of the data returned. Alphanumeric data can be sorted from A to Z or Z to A. Numeric data can be sorted by greatest to smallest or smallest to greatest. In fact, data can be sorted in all sorts of ways with a little creativity.

A basic example where a field is specified.

SQL> SELECT first_name, last_name, order_id
1  FROM customer, orders
2  WHERE customer.customer_id = orders.customer_id
3  ORDER BY last_name;

The default sort order is ascending. Ascending can be specified by adding ASC. Descending would be specified by adding DESC.

SQL> SELECT first_name, last_name, order_id
1  FROM customer, orders
2  WHERE customer.customer_id = orders.customer_id
3  ORDER BY last_name ASC, first_name DESC;

If you use an alias for a column, you can use that alias in the ORDER BY clause.

SQL> SELECT first_name || ' ' || last_name AS full_name, order_id
1  FROM customer, orders
2  WHERE customer.customer_id = orders.customer_id
3  ORDER BY full_name;

You can also reference the column by it’s order in the SELECT list:

SQL> SELECT first_name, last_name, order_id
1  FROM customer, orders
2  WHERE customer.customer_id = orders.customer_id
3  ORDER BY 2 ASC, 1 DESC;

Conclusion

The SELECT statement is the basic building block of databases. There are many more options and techniques that can be used in SELECT statements. This article covers only the basics of what will likely be the statement you use most in your work with databases.