Newer
Older
labs / tiddlers / content / labs / lab05 / _Labs_05_Task 1_ The basic SELECT statement.md

Select

You can think of the SELECT statement as roughly equivalent to a “print” statement in other languages: it’s simply a way to output some information from the database. What you output could be as simple as a single character literal value, or complex collection of column values, calculations and summarised statistical data.

The basic form of the SELECT statement looks like this:

select [<list of expressions>]]
from <table expression>
[where <condition>]
[order by <list of columns>];

The SELECT clause is simply a comma-separated list of expressions, so you can write queries like the following:

select 'Hello, World!', 18 * 365, sysdate from Dual;

What does this query output?


Describe the structure and contents of the Dual table? Note:The Wikipedia page for the Dual table (https://en.wikipedia.org/wiki/DUAL_table) may be useful in answering these questions.

Why is this table needed? (Hint: It has to do with the syntax of the SELECT statement.)


From

The FROM clause specifies a table-valued expression, which tells the query where to get its data. In the simplest case, the FROM clause identifies a single table, but as you’ll see, it supports much more complex expressions.

select Site_ID, Latitude, Longitude
from Site;

select * will return all the columns that exist in the FROM expression.

Can you think of any reasons why it might not be a good idea to use select * in production code (i.e., deployed and running for real)?


Where

The WHERE clause is used to restrict data based on some condition. Only those rows returned by the FROM clause for which the condition evaluates to true will be output.

select *
from Site
where Altitude >= 100;

Sensible sorting of the output rows can mean the difference between a useful query result and an unintelligible one. Use the ORDER BY clause to specify a sort key. If you need to sort on multiple columns, specify them as a comma-separated list, with the most significant column(s) first. For descending order, specify DESC. For example:

select *
from Scientist
order by Surname, Other_Names;
select *
from Site
order by Altitude desc;

Almost all queries have a sensible row ordering, so you should get into the habit of supplying an ORDER BY clause for any queries that you are likely to use more than once.
Queries that might be commonly re-used can be stored as views, for example:

create view High_Altitude_Sites as
  select *
  from Site
  where Altitude >= 100
  order by Description;

Then all you have to do is:

select * from High_Altitude_Sites;