Notes

Outline

Relational Databases and SQL The relational model and the most common SQL commands

Entities and Relations Entities are combinations of values Table consists of columns and rows A row is an entity, a.k.a. tuple A column is an attribute of an entity Relations can exist between columns of tables

Structured Query Language    Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database

Structured Query Language Create and remove tables Insert rows into tables Change rows in tables Delete rows from tables Search in tables and combinations thereof

Data types VARCHAR2(size) NUMBER(p,s) CHAR(size) BLOB FLOAT(b) INTEGER

VARCHAR2(size)    Variable-length character string having maximum length size bytes. Maximum size is 4000 bytes, and minimum is 1 byte.    You must specify size for VARCHAR2.

NUMBER(p,s)    Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.    NUMBER(6) 123456    NUMBER(6,2) 1234,56    NUMBER(6,-2) 1200

CHAR(size)    Fixed-length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.

BLOB       A binary large object. Maximum size is 4 gigabytes.

FLOAT(b) & INTEGER    FLOAT(b) specifies a floating-point number with binary precision b. The precision b can range from 1 to 126.     INTEGER :== NUMBER(38)

The NULL value NULL marks an attribute undefined NULL is a valid value of ANY data type Unspecified attributes get the NULL value NULL is not equal to any value but NULL is equivalent to NULL The function NVL(x,y) returns y if x equivalent NULL, else it returns x

Tables CREATE TABLE sources ( name VARCHAR2(10), ra FLOAT, dec FLOAT, bmag NUMBER(5, 2), rmag NUMBER(5, 2) ); DROP TABLE sources;

Adding rows to a table INSERT INTO sources ( name, ra, dec, bmag, rmag ) VALUES ( 'NGC7072', 21.45694, -43.37306, 14.31, 13.33 );

Adding rows to a table INSERT INTO sources VALUES ( 'NGC7072', 21.45694, -43.37306, 14.31, 13.33 ); INSERT INTO sources (name, rmag) VALUES ('NGC891', 16.45);

Simple selection (1) SELECT * FROM sources WHERE name = 'NGC7072'; result: NAME               RA        DEC     BMAG     RMAG ---------- ---------- ---------- -------- -------- NGC7072      21.45694  -43.37306    14.31    13.33

Simple selection (2) SELECT name, ra, dec FROM sources WHERE bmag > 13; result: NAME               RA        DEC ---------- ---------- ---------- NGC7072      21.45694  -43.37306

Simple selection (3) SELECT name, bmag-rmag FROM sources WHERE bmag > 13 AND rmag < 19; result: NAME        BMAG-RMAG ---------- ---------- NGC7072           .98

Simple selection (4) SELECT DISTINCT SUBSTR(name, 1, 3) FROM sources; result: SUB --- NGC

Simple selection (5) SELECT x.rmag, x.bmag FROM sources x WHERE x.rmag < x.bmag; result:       RMAG       BMAG ---------- ----------      13.33      14.31

Deleting rows from a table DELETE FROM sources WHERE name like 'NGC70__'; DELETE FROM sources WHERE name like 'NGC70%'; TRUNCATE TABLE sources; efficiently deletes ALL rows

Changing values in rows UPDATE sources SET rmag = rmag / 10, bmag = bmag * 10 WHERE name IS NOT NULL;

Joining tables SELECT b.name, b.ra, b.dec, b.mag bmag, r.mag rmag FROM bsources b, rsources r WHERE b.name = r.name;     result: NAME               RA        DEC       BMAG       RMAG ---------- ---------- ---------- ---------- ---------- NGC1234         12.34      45.67       17.3       19.3 NGC4321         23.45      56.78       18.2       17.2