BA372 - SQL Queries

BA 372 - SQL Queries

DB (select) queries:

Select all records and all columns from the table foo:

select * from foo;

Select fields A and B from table foo, but only from those rows where field C > 100:

select A, B from foo where C > 100;

Requesting information from a database using a select statement always!! results in the generation of a (virtual) table, the contents of which can be listed (selected & projected).

Query combines three relational algebraic actions:

Join action: specifies which (existing) tables are involved in the query. At least one table must be involved. The join results in a virtual table.

Select action: specifies which rows from the joined (virtual) table must be retrieved. This results in another virtual table.

Project action: specifies which columns from the newly created virtual table must be retained (or even created).

e.g.;

project
project_id*	project_name	dept_id	maxhours
1	first_project	1	10
2	second_project	2	20
3	third_project	1	30

To see the project_id and maxhours of all rows of this table:

join contains only one table project:

virtual table

project_id* project_name dept_id maxhours

1
first_project
1
10

2
second_project
2
20

3
third_project
1
30

select involves all rows:

virtual table

project_id* project_name dept_id maxhours

1
first_project
1
10

2
second_project
2
20

3
third_project
1
30

project involves only the project_id and maxhours fields:

virtual table

project_id
maxhours

1
10

2
20

3
30

In SQL:

select project_id, maxhours
from project;

Notice the name confusion caused by 'select.'

Show all columns for those projects with a project_id > 2:

select *
from project
where project_id > 2000;

Thus:

Project action ==> select clause (* means all columns).

Select action ==> where clause (no where clause implies all rows).

Join action ==> from clause (at least one table must be listed).

Joining multiple tables in a single query:

select *
from project, department;

Shows all columns for all rows (no where clause) of the (virtual) table that represents the 'combination' of the tables project and department.

Problem: what would this 'combination' be?

The virtual table contains the Cartesian product of the tables specified in the join; i.e., if project contains 10 rows and department contains 30 rows, the join results in 10 * 30 = 300 rows where each row is a unique combination of a row in project with a row in department.

department
dept_id	dept_name	boss
1	accounting	Joe
2	payroll	Mary
3	marketing	Jane

project x department virtual table
project_id	project_name	dept_id	maxhours	dept_id	dept_name	boss
1	first_project	1	10	1	accounting	Joe
1	first_project	1	10	2	payroll	Mary
1	first_project	1	10	3	marketing	Jane
2	second_project	2	20	1	accounting	Joe
2	second_project	2	20	2	payroll	Mary
2	second_project	2	20	3	marketing	Jane
3	third_project	1	30	1	accounting	Joe
3	third_project	1	30	2	payroll	Mary
3	third_project	1	30	3	marketing	Jane

select * from project, department --> returns Cartesian product.

select project_name, dept_name from project, department --> also returns Cartesian product but only projects the project_name and dept_name.

However, in most cases we are not interested in all possible combinations, but only in those for which there are matching values on the foreign key; e.g., projects and departments that have the same dept_id.

Use the where clause to express the 'select' action.

select project_name, dept_name
from project, department
where project.dept_id = department.dept_id;

Virtual table
project_name	dept_name
first_project	accounting
second_project	payroll
third_project	accounting

Note the <table>.<column> notation.

Note how the final virtual (result) table is a selected and projected version of the joined (Cartesian product) table.

Querying multible tables using subqueries:

Specify a set of values with a where clause and a subselect:

select project_name
from project
where dept_id in (select dept_id from department);

Eliminating duplicates:

Find all bosses whose departments have projects:

select boss
from project, department
where project.dept_id = department.dept_id;
- --> Joe, Joe, Mary
select distinct(boss)
from project, department
where project.dept_id = department.dept_id;
- --> Joe, Mary.
Notice how the distinct function eliminates the second hit for Joe.

Adding where conditions:

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;

Qualifying the project 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., request two columns and sort the returned records on the second field within the sorted records of the first field:

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;

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?

Problem: how would you solve this?

What about doing some arithmetic?;

select 8 + 5 from project;

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).