| 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. |