Relational Database and SQL

1
Relational Database and SQL

• More advanced SQL commands

2
The DUAL table

• DUAL is a virtual table
• Can be used to call system functions and evaluate
  expressions
• SELECT USER, SYSDATE, 543FROM DUALUSER
  SYSDATE 543OPSDANNY 25-OCT-02 23

3
Aliases

• Table aliases take the form FROM tablename
  tablealias
• Column aliases take the form SELECT 1,1rmag-0.5
  columnalias
• The aliases can be used as shorthand
• Aliases are sometimes mandatory as part of the OO
  syntax

4
Views

• A view is a virtual table
• It is defined as the result of a subquery
• CREATE VIEW sourcesASSELECT r.name, b.ra,
  b.dec, r.mag rmag, b.mag bmagFROM bsources b,
  rsources r
• View sources with columns name, ra, dec,
  rmag and bmag

5
Functions

• Arithmetical functionsSUM, AVG, MIN, MAX, COUNT,
  STDDEV, VARIANCE, WIDTH_BUCKET, ...
• Trigonometric functionsSIN, COS, TAN, ACOS,
  ASIN, ATAN, SINH, COSH,
• String functionsTRIM, SUBSTR, CONCAT, LOWER,
  UPPER, INITCAP, SOUNDEX, LENGTH,

6
Adding deleting columns

• ALTER TABLE sourcesADD ( meaning VARCHAR2(42) )
• ALTER TABLE sourcesDROP COLUMN meaning

7
Indexing for faster select

• Does not change the original data
• Search time scales linearly with the number of
  elements N
• In many cases search time can be made to scale
  logarithmically with N
• Sometimes search time is constant (independent
  of N)
• CREATE INDEX sources_bmag_idxON sources(bmag)

8
Grouping results

• SELECT SIGN(latitude), COUNT()FROM
  sourcesWHERE stargalaxy gt 0.9GROUP BY
  SIGN(latitude)
• HAVING clause works like WHERE but on the result
  of a query

9
Unions

• SELECT rmag FROM somesourcesUNIONSELECT rmag
  FROM othersources

10
Sequences

• CREATE SEQUENCE tripleBY 3 START WITH 0
• SELECT triple.nextval FROM DUAL0
• SELECT triple.nextval FROM DUAL3
• SELECT triple.currval FROM DUAL3

11
Transactions

• COMMIT ensures that only moves to a new state if
  all queries in a transaction succeed.
• ROLLBACK ensures that the database returns to the
  state it had before all queries in a transaction
  have been applied.
• SAVEPOINT marks a state to which the database can
  be rolled back.