|
|
|
More advanced SQL commands |
|
|
|
|
DUAL is a virtual table |
|
Can be used to call system functions and
evaluate expressions |
|
SELECT USER, SYSDATE, 5*4+3
FROM DUAL;
USER
SYSDATE 5*4+3
OPS$DANNY 25-OCT-02 23 |
|
|
|
|
Table aliases take the form
FROM tablename tablealias |
|
Column aliases take the form
SELECT 1,1*rmag-0.5 columnalias |
|
The aliases can be used as shorthand |
|
Aliases are sometimes mandatory as part of the
OO syntax |
|
|
|
|
A view is a virtual table |
|
It is defined as the result of a subquery |
|
CREATE VIEW sources
AS
SELECT r.name, b.ra, b.dec,
r.mag rmag, b.mag bmag
FROM bsources b, rsources r; |
|
View ‘sources’ with columns ‘name’, ‘ra’, ‘dec’,
‘rmag’ and ‘bmag’ |
|
|
|
|
Arithmetical functions
SUM, AVG, MIN, MAX, COUNT, STDDEV, VARIANCE, WIDTH_BUCKET, ... |
|
Trigonometric functions
SIN, COS, TAN, ACOS, ASIN, ATAN, SINH, COSH, … |
|
String functions
TRIM, SUBSTR, CONCAT, LOWER, UPPER, INITCAP, SOUNDEX, LENGTH, … |
|
|
|
|
ALTER TABLE sources
ADD ( meaning VARCHAR2(42) ); |
|
|
|
ALTER TABLE sources
DROP COLUMN meaning; |
|
|
|
|
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_idx
ON sources(bmag); |
|
|
|
|
SELECT SIGN(latitude), COUNT(*)
FROM sources
WHERE stargalaxy > 0.9
GROUP BY SIGN(latitude); |
|
HAVING clause works like WHERE but on the result
of a query |
|
|
|
|
SELECT rmag FROM somesources
UNION
SELECT rmag FROM othersources; |
|
|
|
|
CREATE SEQUENCE
triple
BY 3 START WITH 0; |
|
SELECT triple.nextval FROM DUAL;
0 |
|
SELECT triple.nextval FROM DUAL;
3 |
|
SELECT triple.currval FROM DUAL;
3 |
|
|
|
|
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. |
|
Notes