BA372 - SQL Queries

BA372

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 table(s) is/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 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). This results in a third virtual table.

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 selection of 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) join 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	dept_boss
1	accounting	Joe
2	payroll	Mary
3	marketing	Jane

project * department virtual table
project table				department table
project_id	project_name	dept_id	maxhours	dept_id	dept_name	dept_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.
Notice how queries involving joins can get 'big,' as they require the creation of Cartesian products ==> in assignment 2 we'll work with a join of two large tables which will result in one, very large virtual table ==> must to some special things to make it work in reasonable time.
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 join clause to express the 'select' action.

select project_name, dept_name
from project inner join department on 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 multiple tables (joins) using subqueries:

Specify a set of values with a where clause and a subselect (nested query):

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

Problem: What does this return?

Notice how this subselect alleviates the need for a multitable join.

Eliminating duplicates:

Find all bosses whose departments have projects:

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

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;

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?

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