SQL: basics and recent advances


Wednesday 23 February

11:30 12:25 Lecture 2 SQL: basics and recent advances Miguel Anjo

At the end of this lecture it is expected that the participants have heard about the main features available for interacting with a database. The base of the session is to look in detail at all the possibilities of database queries, with particular attention to advanced SELECT forms. Most of the session will be based on SQL92 standard and a small part on Oracle features.

1. DML basics: insert/update/delete

2. SELECT basics

  • '||', column pseudonyms, NVL

  • union, union all, intersect, minus

  • restricting: where, in, like, distinct, and/or, not, is [not] null, any, all

  • sorting: order by, asc/desc

  • aggregation: count, sum, max, avg, group by, having

  • joins: equijoins, outerjoins

  • charater manipulation functions: contat, length, lower, upper, ltrim, substr, ...

  • numeric functions: abs, ceil, floor, mod, power, round, sign, sqrt, trunc, ...

  • date functions: to_date, last_day, next_day, NLS_DATE_FORMAT, round, sysdate, trunc

  • convertion functions: to_char, to_date, to_number

  • other functions: decode, greatest, least, nvl, uid, user, vsize

3. Advanced SELECT

  • self joins

  • subqueries, inline views, rownum

  • correlated subqueries

4. Indexes b-tree

5. Transactions

6. Multi-dimensional aggregation