- Logical operators
'and',
'or' and
'not' can
be used to further qualify the select
action:
- select
*
from project, department
where project.dept_id
= department.dept_id
and project_id < 3;
- Further qualifying the select action: ordering results:
- SQL query results are not guaranteed to be in any order
(!!remember the definition of a
relation!!).
If ordering is desired, use the
order by
clause.
- select
*
from project
order by project_id;
- Ordering is ascending by default; specify descending order
if desired:
- select
*
from project
order by project_id desc;
- You may nest ordering;
e.g., project two columns and sort the returned
records on the second column within the sorted records of the
first column:
- select
dept_id, maxhours
from project
order by dept_id asc,
maxhours desc;
- Aggregating records using built-in functions:
- select min(maxhours),
max(maxhours), avg(maxhours), sum(maxhours)
from project;
-
select count(*)
from project
where dept_id = 1;
- Problem: What sort of a
virtual table does this query return?
- Note that aggregation functions cannot be combined with unaggregated
data:
- invalid:
select name,
max(project_id)
from project;
- However, aggregations can be used on groups of records:
- select
sum(maxhours), count(*)
from project
group by project_id;
- Groupings can be further qualified using the
having subclause.
- Some notes on 'purity' of dialects:
- How to select the current system date/time?
- Join action requires that we query at least one table.
- Which one?
- What happens when we try just any table?
- Microsoft Access:
select date() from
project;
- Oracle: select
sysdate from project;
-
Problem:
what do you expect to see? (remember: join-->select-->project)
-
Problem:
how would you solve this?
- Problem: What about doing some arithmetic?
- To comply with standard SQL syntax (requires
from
clause), Oracle provides the
dual dummy table:
- select sysdate
from dual;
- select 8+5
from dual;
- Note that the result of
sysdate or
8+5, although
but a single number or string, still represents a table; although
a table with but one column and one record; a so-called
scalar table.
Remember!! All queries return a selection of records (select
action) and columns (project
action) from a virtual table created by combining one or more existing
tables (join action).