LAB2

SQL uses the terms table, row, and column for relation, tuple, and attribute, respectively. In this tutorial we will use the terms interchangeably.

A table can have up to 254 columns which may have dierent or same data types and sets of values (domains), respectively. Possible domains are alphanumeric data (strings), numbers and date formats.

Oracle others the following basic data types:

char(n): Fixed-length character data (string), n characters long. The maximum size for n is 255 bytes (2000 in Oracle8). Note that a string of type char is always padded on right with blanks to full length of n. (+ can be memory consuming).

Example: char(40)

varchar2(n): Variable-length character string. The maximum size for n is 2000 (4000 in Oracle 8). Only the bytes used for a string require storage.

Example: varchar2(80)

number(o; d): Numeric data type for integers and reals. o = overall number of digits, d = number of digits to the right of the decimal point.

Maximum values:

o =38, d= 􀀀84 to +127. Examples: number(8), number(5,2)

Note that, e.g.,

number(5,2) cannot contain anything larger than 999.99 without resulting in an error. Data types derived from

number are int[eger], dec[imal], smallint and real.

date: Date data type for storing date and time.

The default format for a date is: DD-MMM-YY.

Examples: '13-OCT-94', '07-JAN-98'

SQL Constraints:

Constraints: Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be column level or table level.

Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL: 

NOT NULL Constraint: Ensures that a column cannot have NULL value. 

DEFAULT Constraint: Provides a default value for a column when none is specified. 

UNIQUE Constraint: Ensures that all values in a column are different. PRIMARY Key: Uniquely identified each rows/records in a database table.

FOREIGN Key: Uniquely identified a rows/records in any another database table.

CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

INDEX: Use to create and retrieve data from the database very quickly.