Skip to content Skip to navigation

Connexions

You are here: Home » Content » Structured Query Language

Navigation

Lenses

What is a lens?

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

This content is ...

Affiliated with (What does "Affiliated with" mean?)

This content is either by members of the organizations listed or about topics related to the organizations listed. Click each link to see a list of all content affiliated with the organization.
  • VOCW

    This module is included inLens: Vietnam OpenCourseWare's Lens
    By: Vietnam OpenCourseWare

    Click the "VOCW" link to see all content affiliated with them.

Recently Viewed

This feature requires Javascript to be enabled.
 

Structured Query Language

Module by: Nguyen Kim Anh. E-mail the author

Summary: Formal language like Relational Algebra or Relational Calculus provide a concise notation for representing queries. However, commercial database systems require a more user-friendly language. In this lecture, we introduce the most popular commercial language for database system – the Structured Query Language or SQL.

The original version of this language is called SEQUEL (Structured English Query Language) was developed at IBM’s San Jose Research Lab in early 1970s. This language firstly implemented in a experimental relational database system – System R. The name of the language is changed to SQL in late 1970s.

In 1986, the American National Standard Institute (ANSI) and the International Standard Organization (ISO) published the first SQL standard, called SQL-86. An extended standard for SQL was published in 1989 so called SQL-89. In 1992, the revised standard called SQL-92 (or SQL2) was developed. This version is widely supported by most of the commercial relational DBMSs. The other version – SQL-99 has been proposed but not fully endorsed by the industry. Therefore, in this lecture, we will use the SQL -92 standard.

SQL language has several components:

  • Data Definition Language (DDL) : provides command for defining relation schemas, views, indices; modifying the schema ; specifying access rights to the relations and views; specifying integrity constraints etc.
  • Data Manipulation Language (DML) : SQL DML includes query language based on both relational algebra and tuple relational calculus. It includes also commands to manipulate the data in the database.
  • Embedded DML: This form is used within the programming language such as C, Java

In this lecture, we will focus on the SQL commands to creating , modifying database schemas and integrity constraints. Other parts will be discussed in the later lectures.

SQL Schema Definition

SQL uses the terms table, rows and columns instead of relation, tuples and attributes. The main command to introduce is the CREATE statement which can be used for databases, tables, views, indices, triggers etc. definition. In this session, we will discuss some of these definition in detail.

Data types in SQL

The following data types were the build-in types in SQL-92

  • char(n) : fixed-length character string – n is the number of characters
    • char(5): string which has exactly 5 characters such as ‘Hoa ‘
  • varchar(n) : variable-length character string with n is the maximum number of characters
    • varchar(5) : string has at most 5 characters such as ‘Hoa’
  • int ( or integer in the full form)
  • smallint: integer with 16 bits representation
  • numeric(p,d) : fixed –point number in which p is the total number of digits, d is the number of digit after the decimal point
    • numeric(5,3) : number with 5 digits in which there are 3 digits after the decimal point. This allows 14.135 to be stored exactly, but 141.35 and 1.4135 cannot be stored in a field of this type
  • real : floating point number
  • date: date type, containing year, month and day of the month
  • time: time of the day, containing hour, minute and second

Domain creation

SQL allows user to specify domains using the following clause:

create domain <domain-name> <data type>

For example: create domain name varchar(30).

This statement will create a domain called name of the type varchar(30). This domain then can be used to define the type of an attribute

Database definition

We can create a database using a simple clause as followed:

create database| schema DBName;

For example : create database COMPANY;

Relation (Table) definition

A relation can be defined using the create table clause:

create table <TableName> (

C1 D1 [constraints],

C2 D2 [constraints],

….

Cn Dn [constraints],

[table –level constraints 1],

…..

[table –level constraints k],

) ;

In this clause, each Ci is the name of a column and Di is the domain type (data type) of attribute represented by Ci. All constraints are optional.

A simple table definition:

create table EMPLOYEE (

SSN char(9),

Name varchar(30),

Bdate date,

Address varchar(100),

Salary smallint ,

DeptId smallint

);

Constraints in SQL

Constraints of a relation include detail about primary key, foreign key , default values and constraints on attribute values.

The following list contains possible constraints to specify when creating table.

  • NOT NULL : this constraints can be specified at attribute level. It means that the certain attribute cannot have NULL values.
  • DEFAULT <value> : this constraint define a default value for an attribute. If no default clause is specified, the default value for an attribute that do not have the NOT NULL constraint is NULL value
  • UNIQUE : this clause is used after define attribute name and its domain. This constraints make sure that the values of the specified attribute is unique in the whole table. It can be used for indicating a candidate key.
  • Constraints on Tuple using CHECK (<Predicate>): This constraints can be used as table level constraint to check the specified predicate whenever a tuple is inserted or modified.
  • PRIMARY KEY : indicate one or more attributes that make up a primary key of a relation. If the primary key has single attribute, this constraint can be used at attribute level (means follow the attribute definition directly). Otherwise, it is table level constraint.
  • FOREIGN KEY: this clause specified referential integrity.
    • If foreign key is a single attribute, can be used at either attribute level or table level. Otherwise, specify it as table constraint.
    • Example:

DeptId smallint references DEPARTMENT(DeptId) (attribute level)

Or

DeptId smallint references DEPARTMENT(attribute level)

Or

foreign key (DeptId) references DEPARTMENT(DeptId) (table level)

  • If a referential integrity constraint is violated when tuple are inserted or delete or when key value is modified, system can take the default action that is reject the operation or take other action if there is action specified in the FOREIGN KEY constraint. The options include SET DEFAULT, SET NULL and CASCADE. An option is specified following the clauses ON DELETE or ON UPDATE.

A constraint in the relation can have a name which may be given after the keyword CONSTRAINT. The names of all constraints within a schema must be unique.

create table EMPLOYEE (

SSN char(9) PRIMARY KEY ,

Name varchar(30) NOT NULL,

Bdate date,

Address varchar(100),

Salary smallint ,

DeptId smallint REFERENCES DEPARTMENT(DeptId) DEFAULT 1,

CONSTRAINT EmpSal CHECK (Salary >= 20000 and Salary <= 500000)

);

create table DEPARTMENT(

DeptId smallint,

DeptName varchar(30) NOT NULL,

Office varchar(50),

Mng-SSN char(9) NOT NULL DEFAULT ‘123123123’,

PRIMARY KEY(DeptId),

FOREIGN KEY (Mng-SSN) REFERENCES EMPLOYEE(SSN)

ON DELETE SET DEFAULT ON UPDATE CASCADE

);

Schema Change Statements in SQL

Schema change statements includes the command to alter the table such as dropping table, alter the structure of existing tables, attributes or constraints.

Drop Command

This command can be used to drop named database elements such as tables, constraits, indices and the database itself. Below are syntax for dropping various objects in the database.

Drop database <DatabaseName> ;

Drop table <TableName> ;

Example:

Drop database COMPANY;

Drop table EMPLOYEE;

Alter Command:

The Alter command is used to change the structure of the tables or other existing schema elements. For table, the possible alter actions include: adding or dropping a column, changing the column definition, adding or dropping table constraints.

Adding column

Alter Table <TableName> Add column <ColName> <Domain> [Constraint] ;

Example:

Alter Table EMPLOYEE Add column Sex char(1) DEFAULT ‘M’ ;

Dropping column

Alter Table <TableName> Drop column <ColName> [Cascade|Restrict] ;

Cascade or Restrict defines the behaviour for drop. If Cascade is chosen, all constraints and views that reference the column are dropped automatically along with the column. Otherwise, the command is proceed only if no constraints or views reference the column.

Example:

Alter Table EMPLOYEE Drop column Sex char(1) Cascade ;

Changing column definition

We can change the column’s domain by using the following clause

Alter Table <TableName> Change column <ColName> <New data type> ;

Example: To change the data type of attribute Name from varchar(30) to varchar(40) , we do

Alter Table EMPLOYEE Change column Name varchar(40) ;

We can also dropping or adding default clause

Alter Table <TableName> Alter column <ColName> [Drop Default | Set Default <Values>] ;

Example:

Alter Table EMPLOYEE Alter column DeptId Drop Default;

Alter Table EMPLOYEE Alter column DeptId Set Default 2;

Adding constraints

Alter Table <TableName> Drop Constraint <Constraint Name>;

Example:

Alter Table EMPLOYEE Drop Constraint EmpSal;

Dropping constraints

Alter Table <TableName> Add Constraint [<Constraint Name>] <Constraint Definition>;

In this clause, the Constraint Name is optional.

Example:

Alter Table EMPLOYEE Add Constraint New_EmpSal Check (Salary >= 25000) ;

Content actions

Download module as:

PDF | EPUB (?)

What is an EPUB file?

EPUB is an electronic book format that can be read on a variety of mobile devices.

Downloading to a reading device

For detailed instructions on how to download this content's EPUB to your specific device, click the "(?)" link.

| More downloads ...

Add module to:

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks