Relational Databases and SQL

1
Relational Databases and SQL

• The relational model and the most common SQL
  commands

2
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

3
Structured Query Language

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

4
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

5
Data types

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

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

7
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

8
CHAR(size)

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

9
BLOB

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

10
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)

11
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 valuebutNULL is
  equivalent to NULL
• The function NVL(x,y) returns y if x equivalent
  NULL, else it returns x

12
Tables

• CREATE TABLE sources( name VARCHAR2(10), ra F
  LOAT, dec FLOAT, bmag NUMBER(5,
  2), rmag NUMBER(5, 2))
• DROP TABLE sources

13
Adding rows to a table

• INSERT INTO sources
• ( name,
• ra,
• dec,
• bmag,
• rmag
• )
• VALUES
• ( 'NGC7072', 21.45694, -43.37306, 14.31, 13.33 )

14
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)

15
Simple selection (1)

• SELECT FROM sources
• WHERE name 'NGC7072'
• result
• NAME RA DEC BMAG
  RMAG
• ---------- ---------- ---------- --------
  --------
• NGC7072 21.45694 -43.37306 14.31
  13.33

16
Simple selection (2)

• SELECT name, ra, dec FROM sources
• WHERE bmag gt 13
• result
• NAME RA DEC
• ---------- ---------- ----------
• NGC7072 21.45694 -43.37306

17
Simple selection (3)

• SELECT name, bmag-rmag
• FROM sources
• WHERE bmag gt 13 AND rmag lt 19
• result
• NAME BMAG-RMAG
• ---------- ----------
• NGC7072 .98

18
Simple selection (4)

• SELECT DISTINCT SUBSTR(name, 1, 3)
• FROM sources
• result
• SUB
• ---
• NGC

19
Simple selection (5)

• SELECT x.rmag, x.bmag
• FROM sources x
• WHERE x.rmag lt x.bmag
• result
• RMAG BMAG
• ---------- ----------
• 13.33 14.31

20
Deleting rows from a table

• DELETE FROM sourcesWHERE name like 'NGC70__'
• DELETE FROM sourcesWHERE name like 'NGC70'
• TRUNCATE TABLE sourcesefficiently deletes ALL
  rows

21
Changing values in rows

• UPDATE sources
• SET rmag rmag / 10, bmag bmag 10
• WHERE name IS NOT NULL

22
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