The Databases Course

This document contains all the SQL that this course requires you to know. This means that the syntax of the commands given here is (very) partial. You are welcome to explore further using Oracle’s ‘help [command]’ command.

select [all | distinct ] expression [alias] {, expression [alias], … }

from tablename [corr_name] {, tablename [corr_name], … }

where search_condition

group by column, column, …

having search_condition

Subselect

[union | union all | intersect | minus

Subselect

…]

[ order by result_column [asc | desc] {, result_column [asc | desc], … } ]

1. The Cartesian product of all tables in the from clause is formed.
2. From this, rows not satisfying the where condition are eliminated.
3. The remaining rows are grouped in accordance with the group by clause.
4. Groups not satisfying the having clause are then eliminated.
5. The expressions of the select clause target list are evaluated.
6. If the keyword distinct is present, duplicate rows are now eliminated.
7. The union/intersect/minus is taken after each subselect is evaluated.
8. Finally, the set of all selected rows is sorted if an order by is present.

A search condition is: predicate | not(predicate) | (pred1 and pred2) | (pred1 or pred2).

A predicate can be:

1. Comparison predicate: expr1 [ =, <>, >, <, >=, <= ] (expr2 | Subselect)
   p.price > 5
2. Between predicate: expr1 [not] between expr2 and expr3
   c.discount between 10.0 and 14.0
3. Quantified predicate: expr [=,<>,>,<,>=,<=][any | all] (Subselect)
   c.price >=all (select price from parts)
4. In predicate: expr [not] in (Subselect)
   city in (‘New York’, ‘Atlanta’)
5. Exists predicate: [not] exists (Subselect)
   exists (select * from parts where p.weight > 100)
6. Is null predicate: column_name is [not] null
   c.address is not null
7. Like predicate: column_name is [not] like ‘pattern’
   cname is like ‘A%’

1. create table tablename (field1 datatype [default expr], field2 datatype [default expr], …, primary key(field1, field2, …) ) as Subselect;
   create table parts (p# integer, pname varchar(30), price numeric(8,2), department smallint default 1, primary key(p#));
   create table my_parts as select * from parts;
2. drop table tablename
   drop table parts;
3. alter table tablename [ add field datatype | drop field | modify field datatype], …
   alter table parts add validity date, drop pname;

Smallint – 16 bits integer

Integer – 32 bits integer

Char(n) – fixed length character string

Varchar(n) – variable length character string

Numeric(precision, scale) – a real number

Float(precision, scale) – a real number

Date, Time – date or time types

Boolean – a true/false variable

Bytes(n) – Anything (user defined)

1. insert into table_name (field1, field2, …) values (value1, value2, …)
   insert into parts (p#, weight, pname) values (11, 0.250, ‘Pacifier’);
2. insert into table_name (Select)
   insert into projects select * from old_proj where old_proj.duration > 365;
3. delete from table_name where search_condition
   delete from suppliers where city = ‘Rome’;
4. update table_name set (field1) = value1{, (field2) = value2, …} where condition
   update parts set (price)=0.0, (dept)='SendBack' where city='London';

A view is a virtual table: It is actually a name for a query. You can use views in select, insert, update or delete statements instead of table_names.

1. create [or replace] [force | noforce] view view_name as (Select)
   create view partnames as select pname from parts;
2. drop view view_name
   drop view partnames;