Relational Databases and SQL The relational model and

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 (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 • • • VARCHAR 2(size) NUMBER(p, s) CHAR(size) BLOB FLOAT(b) INTEGER

VARCHAR 2(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 VARCHAR 2.

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) NUMBER(6, 2) NUMBER(6, -2) 123456 1234, 56 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 VARCHAR 2(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 ( 'NGC 7072', 21. 45694, -43. 37306, 14. 31, 13. 33 );

Adding rows to a table • INSERT INTO sources VALUES ( 'NGC 7072', 21. 45694, -43. 37306, 14. 31, 13. 33 ); • INSERT INTO sources (name, rmag) VALUES ('NGC 891', 16. 45);

Simple selection (1) SELECT * FROM sources WHERE name = 'NGC 7072'; result: NAME RA DEC BMAG RMAG ---------- -------NGC 7072 21. 45694 -43. 37306 14. 31 13. 33

Simple selection (2) SELECT name, ra, dec FROM sources WHERE bmag > 13; result: NAME RA DEC ----------NGC 7072 21. 45694 -43. 37306

Simple selection (3) SELECT name, bmag-rmag FROM sources WHERE bmag > 13 AND rmag < 19; result: NAME BMAG-RMAG -----NGC 7072. 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 'NGC 70__'; • DELETE FROM sources WHERE name like 'NGC 70%'; • 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 ---------- -----NGC 1234 12. 34 45. 67 17. 3 19. 3 NGC 4321 23. 45 56. 78 18. 2 17. 2