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)

• The latest SQL standard was in 1999

– This standard includes some object-relational support as well as purely relational

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

SQL Data Definition

• SQL uses the terms table, row, and column instead of relation, tuple, and attribute

• The data definition commands are those that affect the metadata

– i.e. they affect the structure of the tables

• The main DDL commands are:

– Create

– Alter

– Drop

• These can be used with various constructs, including tables, domains, views assertions, and triggers

PostgreSQL—how to get started

• Log on to esus

• To create a new database with the name mydb type:
  % createdb mydb

• You have to give your database a different name from everyone else's.

• So if you type % createdb mydb and it says mydb already exists, you'll just have to try another name.

• Despite the name clash, the permissions in the system mean that you can't access other peoples' databases.

• To be able to use SQL to actually do something with this database, type:
   % psql mydb

– This starts the interactive command line SQL environment, with mydb as the active database.

• Everyone should have an account on PostgreSQL. If not, email Mark Fessler. fessler@cs.montana.edu

PostgreSQL Documentation --This site has a nice SQL tutorial

An online 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 numeric(6,2)
);

System Catalog

• When you create a table, PostgreSQL stores the definition in the system catalog

• This metadata can be accessed just like you access the data itself (with select statements)

– Or, when using psql you can use the \d command

– This will give you a table showing you all the tables you have created.

– To see details of a specific table type \d tablename

• You can get a list of all the PostgreSQL commands by typing \?

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 may be either exact or approximate

– Exact

• smallint – two bytes to store the integer ( Also called int2)

• integer – 4 bytes (Also be called int, int4)

• bigint – 8 bytes (Also called int8)

• numeric(p,s) (Also be called decimal (p,s))

– p is the total number of digits, s is the number of fractional digits

– Approximate

• Real – 4 bytes (Also called float, float4)

• Double precision – 8 bytes (Also called float8)

Character types

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

• may be padded with spaces if less than size characters

• If size is omitted, it is assumed to be one

– varchar(size) - variable-length character data at most size characters

– text – a text column can store strings of any length

• Operators

– Concatenation operator ||

Date/Time types

– date –default format: YYYY-MM-DD

• Any date or time literal input needs to be enclosed in single quotes, like text strings

– time – default format: HH:MM:SS

– There are also timestamp and interval data types you can use

• 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 integer(4) primary key,
name varchar(20),
price number(6,2) default 9.99
);

Constraints

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

• There are several types of constraints that can be specified as part of creating tables

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

– Key and referential integrity restraints are another

– Not null can be specified as a constraint

– The check constraint is used to specify application specific constraints

• 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 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

– A comma always signals the end of an attribute, and any information about the attribute

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 integer(4) not null,
name varchar(20),
price number(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>] type_of_constraint;

• The type_of_constraint could be:

– [not] null

– check(<condition>)

– unique (for a candidate key)

– primary key

– references <table_name>[(col_name)][on delete cascade] -- (for a foreign key)

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 number (2) primary key,
deptname varchar(9),
loc varchar(10) );

• This example uses column constraint.

Example of a table constraint

• Part of the table definition, so comes after the comma of the last attribute

• Can impose rules on any column in the table

create table dept
(deptno int unique,
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
(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 number(4),
dno numeric(2,0) 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 number (4);
ename varchar(10),
mgr number(4),
deptno number(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

• The default is to reject the operation

• cascade (on delete or on update)

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

– Automatically deletes/updates dependent rows from the child table to maintain referential integrity.

• set null or set default (on delete or on update)

• Look at overhead of figure 8.2 for examples

Example

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

Changing the metadata

• Tables, attributes or constraints can be changed using the drop or alter command

• drop

– Can only be used on named constructs

– Two drop options

• Cascade or restrict

• Example: drop table department cascade;

– This will drop automatically all constrains and views that reference the table

• Example: drop table department restrict;

– The table is dropped only if it is not referenced in any constrains in any other table

• alter

– Can change the number/type of attributes, or the constraints

Alter table example

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