General concepts

TermMeaning
TableRelation
Schema
Data dictionaryspecial set of relational tables that store information about all tables
Metadatadefinition of a table: info about table name, owner, details about columns and physical storage on disk;
obtained by using the DESCRIBE command;
by default is stored in uppercase in data dictionary
Datacontents of a table
Rowrecord; tuple; single record
Cardinality of the tuplesthe number of rows in the table
Primary keyattribute unique for each row; only one in a table; does NOT accept any duplicate value or NULL
Unique keyattribute unique for each row; can be more than one per table; accepts only ONE NULL value
Foreign keyattribute that form relationship between entities; are based on unique keys in a related entity,
but those unique keys do not have to be the primary key; they just have to be unique;
can be nullable (!)
Lookupfinding by foreign key
Lookup entitymaster entity; participates in a master-detail relationship;
for ex. in a Car-Color relationship – the master entity is Color and the detail entity is Car (one Color to many Cars)
Result seta set of rows returned by a SELECT statement
DUALa special table that belongs to SYS schema; every other schema has access to it
Projectionthe ability to restrict columns that participate in a SELECT statement
Selectionthe ability to restrict rows returned by a SELECT statement
Joininginteraction of table with each other in a query
Logical modelentities
Relational modeltables, foreign keys, types
Physical modelrelational DB
“Blind” queryusing * (asterisk) in a query; because you don’t know how many columns will be fetched (projected);
may cause performance issues in case of huge tables
Aliasalternative name for referencing a column or expression;
they are case sensitive – case preservation is only possible in case the alias is double quoted;
can be created either by:
– leaving a space after column name
– using keyword AS

Normal forms:

  • 1NF – eliminating unnecessary repeating groups of data
  • 2NF – removes attributes from entity (1NF) that are not dependent on the primary key
  • 3NF – removes any interdependent attributes from a 2NF entity

Data types

NUMBER(X,Y)

NUMBER(X,Y) means that totally this column will have at most X digits but:

  • fractional part will be rounded to at most Y digits
  • whole part will be of at most X-Y digits

For ex.: NUMBER(5,2)

  • 535,24 – valid
  • 4344,1 – invalid
  • 542,12122 – will be rounded to 542,12
  • 23,1 – valid

Data retrieval using the SQL SELECT statement

Capabilities of the SELECT statement

SELECT statement never alters information stored in DB; it provides read-only method for extracting info.

Relational database tables are built using relational theory. Relational theory uses relational algebra formal language.

According to relational theory SELECT statement uses three concepts:

  1. Projection – restriction of columns to be returned
  2. Selection – limiting the result set in accordance to some criteria
  3. Joining – interaction of tables with each other in a query.

The special keywords and reserved words case does not matter but they cannot be used as column or other database objects names.

The two mandatory clauses of a SELECT statement are:

  1. SELECT
  2. FROM

The * (asterisk) usage in a query is referred to as blind query, because the exact columns to be fetched are not specified.

Aliases

When using SELECT you may use an alias – an alternative name for referencing a column or an expression.

Aliases are used for:

  • displaying output in a user-friendly manner
  • serving a shorthand when referencing columns or expressions to reduce typing.

Multi word aliases must be enclosed in double quotes or you’ll get ORA-00923 errors. When declaring an alias AS keyword may be used for better readability.

Aliases are enclosed in double quotes whereas string literals are enclosed in single quotes.

SQL expressions and operators

Columns and expressions are selectable.

Expressions are made up of an operation being performed on one or more column values (remember Excel formulas).

Operators by data type of columns:

  • Arithmetic operations – for numeric columns – (+ – * /)
  • Concatenation operator – for character or string columns
  • Addition or subtraction – for date and timestamp

Arithmetic operations on date columns return days number.

Basic SELECT statements notes

Letters case matters only when dealing with literals and aliases; SQL reserved words don’t have to be written in upper case.

When writing long expressions it is better to spread them on multiple lines – for debugging purposes, as debugger will point out the line number on failure.

SQL*Plus always requires statement terminator and usually a semicolon is used.

A / (forward slash) at the end of a file containing SQL statements indicates that the statements need to be executed.

Characters concatenation is done by || operator (in MySQL you would have to use CONCAT() function.

DUAL

There is a special table called DUAL that is used for literal expressions to be calculated – these are returned as a single row from this table.
DUAL has a single column (called DUMMY of character type) and a single row.

Ex:

For literals single quotes are used like:

Escaping single quotes

Simplest way of escaping a quote in a literal is by writing 2 single quotes (1st is for escaping the 2nd)

For escaping an alternative quote operator (q) may be used. It uses delimiters, these can be braces pairs like: [], {}, () and also any single or multi byte character. This operator is used as follows:

q’delimiter some text delimiter

SQL statements should be terminated with a semicolon. As an alternative, a new line can be added after a statement and a forward slash can be used to execute the statement (forward slash is a command – execute).

Null

Null refers to absence of data. Nullable is a term describing a columns may have null values.

  • Any arithmetic calculation with a NULL returns a NULL
  • Character concatenation operators ignore NULL

Any column that is a primary key is not nullable. But the foreign key can be nullable and this is a risk.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.