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