Simple Queries from one table
 

Simple Queries from one table




select [distinct] <column(s)>
from <table>
[ where <condition> ]
[ order by <column(s) [ascjdesc]> ];

A simple query from one table has the following structure:

  • the select list (which columns in our report)
  • the name of the table
  • the where clauses (which rows we want to see)
  • the order by clauses (how we want the rows arranged)

Instead of an attribute name, the select clause may also contain arithmetic expressions involving arithmetic operators etc.
select ENAME, DEPTNO, SAL * 1.55 from EMP;

For the diffrent data types supported in Oracle , several operators and functions are provided:

  • for numbers: abs, cos, sin, exp, log, power, mod, sqrt, +;
  • for strings: chr, concat(string1, string2), lower, upper, replace(string, search string, replacement string), translate, substr(string, m, n), length, to date, ...
  • for the date data type: add month, month between, next day, to char,...

Inserting the keyword distinct after the keyword select, however, forces the elimination of duplicates from the query result.It is also possible to specify a sorting order in which the result tuples of a query are displayed.For this the order by clause is used and which has one or more attributes listed in the select
clause as parameter. desc speci es a descending order and asc speci es an ascending order (this is also the default order). For example, the query

select ENAME, DEPTNO, HIREDATE from EMP;
from EMP
order by DEPTNO [asc], HIREDATE desc;
List the job title and the salary of those employees whose manager has the number 7698 or 7566 and who earn more than 1500:

select JOB, SAL
from EMP
where (MGR = 7698 or MGR = 7566) and SAL > 1500;

For all data types, the comparison operators =; != or <>; <; >; <=, => are allowed in the conditions of a where clause.
Further comparison operators are:

  • Set Conditions: <column> [not] in (<list of values>)

Example: select


 from DEPT where DEPTNO in (20,30);

  • Null value: <column> is [not] null, i.e., for a tuple to be selected there must (not) exist a de ned value for this column.

Example: select *  from EMP where MGR is not null;
Note: the operations = null and ! =null are not defined!

  • Domain conditions: <column> [not] between <lower bound> and <upper bound>

Example:  select EMPNO, ENAME, SAL from EMP
where SAL between 1500 and 2500;
select ENAME from EMP
where HIREDATE between '02-APR-81' and '08-SEP-81';

String Operations:
A powerful operator for pattern matching is the like operator. Together with this operator, two special characters are used: the percent sign % (also called wild card), and the underline_ , also called position marker.
SQL> select email
from users
where email like '%mit.edu';

The email like '%mit.edu' says "every row where the email column ends in 'mit.edu'". The percent sign is Oracle's wildcard character for "zero or more characters". Underscore is the wildcard for "exactly one character":

SQL> select email
from users
where email like '___@mit.edu';

Suppose that you were featured on Yahoo in September 1998 and want to see how many users signed up during that month:

SQL> select count(*)
from users
where registration_date >= '1998-09-01'
and registration_date < '1998-10-01';

  COUNT(*)
----------
       920

We've combined two restrictions in the WHERE clause with an AND. OR and NOT are also available within the WHERE clause. For example, the following query will tell us how many classified ads we have that either have no expiration date or whose expiration date is later than the current date/time.

SQL> select count(*)
from classified_ads
where expires >= sysdate
or expires is null;

Further string operations are:

  • upper(<string>) takes a string and converts any letters in it to uppercase, e.g., DNAME = upper(DNAME) (The name of a department must consist only of upper case letters.)
  • lower(<string>) converts any letter to lowercase,
  • initcap(<string>) converts the initial letter of every word in <string> to uppercase.
  • length(<string>) returns the length of the string.
  • substr(<string>, n [, m]) clips out a m character piece of <string>, starting at position n. If m is not speci ed, the end of the string is assumed.
  • substr('DATABASE SYSTEMS', 10, 7) returns the string 'SYSTEMS'.

Aggregate Functions:
Aggregate functions are statistical functions such as count, min, max etc. They are used to compute a single value from a set of attribute values of a column:

  • count Counting Rows

Example: How many tuples are stored in the relation EMP?
select count(*) from EMP;

Example: How many diffrent job titles are stored in the relation EMP?
select count(distinct JOB) from EMP;

  • max Maximum value for a column
  • min Minimum value for a column

Example: List the minimum and maximum salary.
select min(SAL), max(SAL) from EMP;

Example: Compute the di erence between the minimum and maximum salary.
select max(SAL) - min(SAL) from EMP;

  • sum Computes the sum of values (only applicable to the data type number)

Example: Sum of all salaries of employees working in the department 30.
select sum(SAL) from EMP
where DEPTNO = 30;

  • avg Computes average value for a column (only applicable to the data type number)

Note: avg, min and max ignore tuples that have a null value for the specified attribute, but count considers null values.

Subqueries

A query result can also be used in a condition of a where clause. In such a case the query is called a subquery and the complete select statement is called a nested query.You can query one table, restricting the rows returned based on information from another table. For example, to find users who have posted at least one classified ad:

select user_id, email
from users
where 0 < (select count(*)
           from classified_ads
           where classified_ads.user_id = users.user_id);

   USER_ID EMAIL
---------- -----------------------------------
     42485 twm@meteor.com
     42489 trunghau@ecst.csuchico.edu
     42389 ricardo.carvajal@kbs.msu.edu
     42393 gon2foto@gte.net
     42399 rob@hawaii.rr.com
     42453 stefan9@ix.netcom.com
     42346 silverman@pon.net
     42153 gallen@wesleyan.edu
...
Conceptually, for each row in the users table Oracle is running the subquery against classified_ads to see how many ads are associated with that particular user ID. Keep in mind that this is only conceptually; the Oracle SQL parser may elect to execute this query in a more efficient manner.
Another way to describe the same result set is using EXISTS:
 

select user_id, email
from users
where exists (select 1
              from classified_ads
              where classified_ads.user_id = users.user_id);

This may be more efficient for Oracle to execute since it hasn't been instructed to actually count the number of classified ads for each user, but only to check and see if any are present. Think of EXISTS as a Boolean function that takes a SQL query as its only parameter returns TRUE if the query returns any rows at all, regardless of the contents of those rows (this is why we can use the constant 1 as the select list for the subquery).

A respective condition in the where clause then can have one of the following forms:

1. Set-valued subqueries

<expression> [not] in (<subquery>)
<expression> <comparison operator> [any|all] (<subquery>)

An <expression> can either be a column or a computed value.
2. Test for (non)existence

[not] exists (<subquery>)

In a where clause conditions using subqueries can be combined arbitrarily by using the logical connectives and and or.
Example: List the name and salary of employees of the department 20 who are leading a project that started before December 31, 1990:

select ENAME, SAL from EMP
where EMPNO in
(select PMGR from PROJECT
where PSTART < '31-DEC-90')
and DEPTNO =20;

Explanation: The subquery retrieves the set of those employees who manage a project that started before December 31, 1990. If the employee working in department 20 is contained in this set (in operator), this tuple belongs to the query result set.

Example: List all employees who are working in a department located in BOSTON:

select * from EMP
where DEPTNO in
(select DEPTNO from DEPT
where LOC = 'BOSTON');

The subquery retrieves only one value (the number of the department located in Boston). Thus it is possible to use \=" instead of in. As long as the result of a subquery is not known in advance, i.e., whether it is a single value or a set, it is advisable to use the in operator. A subquery may use again a subquery in its where clause. Thus conditions can be nested arbitrarily. An important class of subqueries are those that refer to its surrounding (sub)query and the tables listed in the from clause, respectively. Such type of queries is called correlated subqueries.
(IMP)Example: List all those employees who are working in the same department as their manager (note that components in [ ]are optional:

select * from EMP E1
where DEPTNO in
(select DEPTNO from EMP [E]
where [E.]EMPNO = E1.MGR);

Explanation: The subquery in this example is related to its surrounding query since it refers to the column E1.MGR. A tuple is selected from the table EMP (E1) for the query result if the value for the column DEPTNO occurs in the set of values select in the subquery. One can think of theevaluation of this query as follows: For each tuple in the table E1, the subquery is evaluated individually. If the condition where DEPTNO in : : : evaluates to true, this tuple is selected.
Note that an alias for the table EMP in the subquery is not necessary since columns without a preceding alias listed there always refer to the innermost query and tables.

Conditions of the form <expression> <comparison operator> [any|all] <subquery> are used to compare a given <expression> with each value selected by <subquery>.
 For the clause any, the condition evaluates to true if there exists at least on row selected by the subquery for which the comparison holds. If the subquery yields an empty result set, the condition is not satis ed.
 For the clause all, in contrast, the condition evaluates to true if for all rows selected by the subquery the comparison holds. In this case the condition evaluates to true if the subquery does not yield any row or value.
(IMP)Example: Retrieve all employees who are working in department 10 and who earn at least as much as any (i.e., at least one) employee working in department 30:

select * from EMP
where SAL >= any
(select SAL from EMP
where DEPTNO = 30)
and DEPTNO = 10;

Note: Also in this subquery no aliases are necessary since the columns refer to the innermost from clause.
Example: List all employees who are not working in department 30 and who earn more than all employees working in department 30:

select * from EMP
where SAL > all
(select SAL from EMP
where DEPTNO = 30)
and DEPTNO <> 30;

For all and any, the following equivalences hold:
in , = any
not in , <> all or != all

JOIN

A major feature of relational databases, however, is to combine (join) tuples stored in di erent tables in order to display more meaningful and complete information. In SQL the select statement is used for this kind of queries joining relations:

select [distinct] [<alias ak >.]<column i>, : : : , [<alias al >.]<column j>
from <table 1> [<alias a1 >], : : : , <table n> [<alias an >]
[where <condition>]

The speci cation of table aliases in the from clause is necessary to refer to columns that have the same name in di erent tables. For example, the column DEPTNO occurs in both EMP and DEPT. If we want to refer to either of these columns in the where or select clause, a table alias has to be speci ed and put in the front of thecolumn name. Instead of a table alias also the complete relation name can be put in front of the column such as DEPT.DEPTNO, but this
sometimes can lead to rather lengthy query formulations.

A professional SQL programmer would be unlikely to query for users who'd posted classified ads in the preceding manner. The SQL programmer knows that, inevitably, the publisher will want information from the classified ad table along with the information from the users table. For example, we might want to see the users and, for each user, the sequence of ad postings:

select users.user_id, users.email, classified_ads.posted
from users, classified_ads
where users.user_id = classified_ads.user_id
order by users.email, posted;

   USER_ID EMAIL          POSTED
---------- ----------------------------------- ----------
     39406 102140.1200@compuserve.com        1998-09-30
     39406 102140.1200@compuserve.com        1998-10-08
     39406 102140.1200@compuserve.com        1998-10-08
     39842 102144.2651@compuserve.com        1998-07-02
     39842 102144.2651@compuserve.com        1998-07-06
     39842 102144.2651@compuserve.com        1998-12-13
...

Because of the JOIN restriction, where users.user_id = classified_ads.user_id, we only see those users who have posted at least one classified ad, i.e., for whom a matching row may be found in the classified_ads table. This has the same effect as the subquery above.
The order by users.email, posted is key to making sure that the rows are lumped together by user and then printed in order of ascending posting time.

Comparisons in the where clause are used to combine rows from the tables listed in the from clause.
Example: In the table EMP only the numbers of the departments are stored, not their name. For each salesman, we now want to retrieve the name as well as the number and the name of the department where he is working:

select ENAME, E.DEPTNO, DNAME
from EMP E, DEPT D
where E.DEPTNO = D.DEPTNO
and JOB = 'SALESMAN';

Explanation: E and D are table aliases for EMP and DEPT, respectively. The computation of the query result occurs in the following manner (without optimization):
1. Each row from the table EMP is combined with each row from the table DEPT (this operation is called Cartesian product). If EMP contains m rows and DEPT contains n rows, wethus get n * m rows.
2. From these rows those that have the same department number are selected (where E.DEPTNO = D.DEPTNO).
3. From this result nally all rows are selected for which the condition JOB = 'SALESMAN' holds.
In this example the joining condition for the two tables is based on the equality operator \=". The columns compared by this operator are called join columns and the join operation is called an equijoin.
Any number of tables can be combined in a select statement.
Example: For each project, retrieve its name, the name of its manager, and the name of the department where the manager is working:

select ENAME, DNAME, PNAME
from EMP E, DEPT D, PROJECT P
where E.EMPNO = P.MGR
and D.DEPTNO = E.DEPTNO;

It is even possible to join a table with itself:
(IMP)Example: List the names of all employees together with the name of their manager:

select E1.ENAME, E2.ENAME
from EMP E1, EMP E2
where E1.MGR = E2.EMPNO;

Explanation: The join columns are MGR for the table E1 and EMPNO for the table E2. The equijoin comparison is E1.MGR = E2.EMPNO.

OUTER JOIN

Suppose that we want an alphabetical list of all of our users, with classified ad posting dates for those users who have posted classifieds. We can't do a simple JOIN because that will exclude users who haven't posted any ads. What we need is an OUTER JOIN, where Oracle will "stick in NULLs" if it can't find a corresponding row in the classified_ads table.

select users.user_id, users.email, classified_ads.posted
from users, classified_ads
where users.user_id = classified_ads.user_id(+)
order by users.email, posted;

...
   USER_ID EMAIL          POSTED
---------- ----------------------------------- ----------
     52790 dbrager@mindspring.com
     37461 dbraun@scdt.intel.com
     52791 dbrenner@flash.net
     47177 dbronz@free.polbox.pl
     37296 dbrouse@enter.net
     47178 dbrown@cyberhighway.net
     36985 dbrown@uniden.com         1998-03-05
     36985 dbrown@uniden.com         1998-03-10
     34283 dbs117@amaze.net
     52792 dbsikorski@yahoo.com
...

The plus sign after classified_ads.user_id is our instruction to Oracle to "add NULL rows if you can't meet this JOIN constraint".

Extending a simple query into a JOIN

Suppose that you have a query from one table returning almost everything that you need, except for one column that's in another table. Here's a way to develop the JOIN without risking breaking your application:

  • add the new table to your FROM clause
  • add a WHERE constraint to prevent Oracle from building a Cartesian product
  • hunt for ambiguous column names in the SELECT list and other portions of the query; prefix these with table names if necessary
  • test that you've not broken anything in your zeal to add additional info
  • add a new column to the SELECT list

Students build a conference room reservation system. They generally define two tables: rooms and reservations. The top level page is supposed to show a user what reservations he or she is current holding:

select room_id, start_time, end_time
from reservations
where user_id = 37;

This produces an unacceptable page because the rooms are referred to by an ID number rather than by name. The name information is in the rooms table, so we'll have to turn this into a JOIN.

select reservations.room_id, start_time, end_time, rooms.room_name
from reservations, rooms
where user_id = 37
and reservations.room_id = rooms.room_id;