The SQL Language

An Overview of SQL

• Over the last several years, SQL has become the standard computer database language.

• Over 100 database management products now support SQL, running on systems from PC's to mainframes.

Relational Algebra and SQL

• Importance of the relational algebra

– It is a procedural language, how the request is to be carried out is specified.

– It helps you understand the types of requests that may be specified

– It is used in query processing and optimization

• Limitations of the algebra

– Must specify how to execute the query operation.

– This makes it more difficult to use.

SQL

• The name

– It is derived from Structured Query Language

– Originally it was called Sequel, (for Structured English QUEry Language)

• SQL2 was standardized in 1992

• SQL is a declarative language (not procedural)

– What is to be retrieved, not how to retrieve it

– Much more user friendly

– It is based on the relational algebra (along with relational calculus)

Components of SQL:

– Data Definition Language (DDL)

• Creating, modifying, deleting relatins

– Interactive Data Manipulation Language (DML)

• Data retrieval and updates

– Embedded DML

– Access control

– Data sharing

– Data integrity

Data Definition

• SQL use table, row, and column instead of relation, tuple, and attribute

• The data definition commands are those that affect the metadata

• Create

• Alter

• Drop

PostgreSQL

• Documentation

• PostSQL tutorial

• How to get started

• SQL Tutorial

Create Table

• The create table command creates a table, then specifies the

– Attributes,

– The defaults (if any) and

– The constraints

• Attributes consist of:

– Name

– Data type and domain

– Optional default value for attributes

– Any attribute constraints

• Constraints can either be constraints on the entire table, or a constraint on an column

Create table example

create table products
(
productNum integer(4) primary key,
name varchar(20),
price decimal(6,2)
);

create table syntax

create table <tablename>
(<column_name> < datatype > [default<n>] [constraint],
<column_name > < datatype > [default<n>] [constraint],
table constraints);

• Syntax

– parenthesis after tablename

– the default is optional: default <expr>

– column constraints are optional

– commas separate column definitions

– semicolon at end of statement

– whitespace doesn’t matter

Data types

• Numeric types

– int – same as number, but no decimal digits; default 40

– numeric(n) – Integer and real values up to n spaces; default 40

– numeric(n,d) - real values up to n spaces with d digits after the decimal

• A PostgreSQL extension does not require n and/or d. It default to any n & d

• Character types

– char(size) - fixed length character data of length size

– varchar(size) - variable-length character data with size the maximum length

Data types (cont)

• Date/Time types

– date – date field; default format DD-MON-YY

– Any date literal input needs to be enclosed in single quotes, like text strings

• Other

– long & long raw- variable-length character data; up to 2 GB. (can be used to store graphics, sound, documents, or arrays of binary data)

– PostgreSQL has a lot of additional data types, some of them nonstandard. I advise sticking to the standard.

Default values

• When a new tuple is created and no values are specified for some of the attributes, the value of the attribute will be the default

• If no default value is declared, the null value is the default.

Example
create table products
(
productNum int primary key,
name varchar(20),
price numeric(6,2) default 9.99
);

Constraints

• Data types are a way to limit the kind of data that can be stored in a table

• For many applications, more control on what is allowed is needed

• Constrains give as much control over the data in the tables as you wish.

• If a user attempts to store data in a column that would violates a constraint, an error is raised

• There are two syntactic forms of constraints:

– 1) column_constraint—refer only to a column

– 2) table_constraint—refer to the whole table

Check constraint

• A check constraint is the most generic constraint type.

• It allows you to specify that the value in a certain columns satisfy an arbitrary expression.

create table products
(
productNum integer(4) not null,
name varchar(20),
price numeric(6,2) check (price >0)
);

• Constraint definitions come after the data type if they are a column constraint

• Default values and constraints can be listed in any order.

– Do NOT use a comma between them

Naming constraints

• Naming constraints clarifies error messages and allows you to refer to the constraint when you need to change it.

• To specify a named constraint, use the key word constraint followed by an identifier followed by the constraint definition.

• create table products
(
productNum int not null,
name varchar(20),
price numeric(6,2)
constraint pos_price check (price >0)
);

• A check constraint is satisfied if the check expression evaluates to true or the null value.

– If this is not what you want, use the not-null constraint.

Column constraint syntax

• [constraint <constraint_name>] name_of_constraint;

• The name_of_constraint could be:

– [not] null

– check(<condition>)

– unique (for a candidate key)

– primary key

– references <table_name>[(col_name)][on delete cascade]

Entity Constraint

• The Key constraint says a key is unique & minimal

• The Entity constraint says the PK is not null

• In SQL, declaring an attribute to be a PK, assures both of these.
create table dept
( deptno int primary key,
deptname varchar(9),
loc varchar(10) );

• This example uses column constraint.

Example of a table constraint

• Part of the table definition

• Can impose rules on any column in the table

create table dept
(deptno int,
deptname varchar(9),
loc varchar(10),
constraint pk_dept primary key (deptno) );

• This example names the constraint.

Composite primary keys

• Composite primary keys must use the table_constraint syntax

• create table dept_location
(deptname varchar(9),
loc varchar(10),
constraint pk_dept primary key (deptno, deptname) );

Not null constraint

• Not null constraints

– Specifies a column cannot contain nulls

– Must use column_constraint syntax

• Can be used for other columns than the PK

• create table products (
product_no integer not null,
name text not null,
price numeric not null check (price > 0) );

• Here, price has two constraints

Referential Integrity Constraints

• Terminology

– The referenced table is often called the parent table

– The table with the foreign key, referencing the PK of the parent table is called the child table

• A foreign key constraint specifies the value of the foreign key must appear as a value in the parent table

– OR the foreign key may be null

• Before you define a foreign key constraint in the child table,

– the referenced PK constraint on the parent table must already be defined.

• Foreign keys may be defined using either syntax (column or table_constraint)

Example

• create table emp
(empno int,
ename varchar(10),
mgr int,
dno int references dept(dnumber)
);

• Putting the PK of the table referenced is optional

Naming the constraint

• Naming constraints clarifies error messages and allows you to refer to the constraint when you need to change it.

• create table emp
(empno numeric (4);
ename varchar(10),
mgr numeric(4),
deptno numeric(2) constraint fk_deptno references dept (deptno)
);

• Use the keyword constraint when naming constraints

Referential constraints violations

• You can specify what to do if an update tries to remove a tuple with a foreign key referencing it

• on delete cascade

– allows deletion in the parent table of key values referenced by the child;

– BUT automatically deletes dependent rows from the child table to maintain referential integrity.

• if this option is omitted, deletions in the parent table of referenced keys are forbidden.

Example

• create table emp
(empno numeric (4);
ename varchar(10),
mgr numeric(4),
deptno int constraint fk_deptno references dept(deptno) on delete cascade
);

As a common practice

• The primary key, candidate key and foreign key constraints are all expressed as table constraints

rather than column constraints

• When they are table constraints, the keywords primary key and foreign key are used

– They are not necessary when they are expressed as column constraints

Creating and altering tables

• The constraints can be specified when the table is created

• OR, they can be added they can be added later using the alter table command.

• Hint: You will have trouble entering the data into the Company database if you specify all the foreign keys when you create the tables.

– In this case, you will want to alter the table to add the foreign key constraints.

Alter table example

• alter table emp
add constraint fk_deptno foreign key(deptno)references dept(deptno) ;