Tutorial 02: SQL DDL commands
Structured Query Language (SQL):
SQL stands for Structured Query Language and it is an ANSI (American National
Standards Institute) standard computer language for accessing and manipulating
database systems. It is used for managing data in relational database management
system, which stores data in the form of tables, and relationship between data is stored
in the form of tables. SQL statements are used to retrieve and update data in a database.
SQL works with database programs like DB2, MySQL, PostgreSQL, Oracle, SQLite,
SQL Server, Sybase, MS Access and much more. There are many different versions of
the SQL language, but to be in compliance with the ANSI standard, they support the
major keyword such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others.
The following picture shows the communicating with an RDBMS using SQL.
SQL Commands:
The standard SQL commands to interact with relational databases are CREATE,
SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be
classified into the following groups based on their nature.
Data Definition Language (DDL):
No. Command & Description
1 CREATE: Creates a new table, a view of a table, or other object in the
database.
2 ALTER: Modifies an existing database object, such as a table.
3 DROP: Deletes an entire table, a view of a table or other objects in the
database.
Data Manipulation Language (DML):
No. Command & Description
1 INSERT: Creates a record.
2 UPDATE: Modifies records.
3 DELETE: Deletes records.
4 SELECT: Retrieves certain records from one or more tables.
Data Control Language (DCL):
No. Command & Description
1 COMMIT: Takes back privileges granted from user.
2 ROLLBACK: This command restores the database to last commited state.
3 SAVEPOINT: used to temporarily save a transaction so that you can
rollback to that point whenever required.
4 GRANT: Gives a privilege to user.
5 REVOKE: Takes back privileges granted from user.
Mohammed Mahmoud
1
Create database:
To create a new database use CREATE DATABASE command, open SQL Shell and
type the following command to create a new database. Use gisdatabase as database
name.
postgres=# create database gisdatabase;
CREATE DATABASE
When you have multiple databases in your SQL Schema, then before starting your
operation, you would need to select a database where all the operations would be
performed. The \c or \connect statement is used to select any existing database in the
SQL schema.
postgres=# \connect gisdatabase
WARNING: Console code page (850) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
You are now connected to database "gisdatabase" as user "postgres".
Data Definition Language (DDL):
1. Create Table:
The CREATE TABLE statement is used to create a new table in a database.
gisdatabase=# create table departments (deptid integer, deptname
varchar(50), address varchar(100));
CREATE TABLE
The above command will create a table with the following architecture
(schema).
Deptid (Integer) Deptname (varchar(50)) Address (varchar(100))
2. Alter Table:
The ALTER TABLE statement is used to add, delete, or modify columns in an existing
table. The ALTER TABLE statement is also used to add and drop various constraints
on an existing table. To add a column in a table, use the following syntax:
gisdatabase=# alter table departments add phone varchar(10);
ALTER TABLE
gisdatabase=#
The above statement will add additional column to the departments table.
Mohammed Mahmoud
2
Deptid (Integer) Deptname (varchar(50)) Address (varchar(100)) Phone (varchar(10))
To delete a column in a table, use the following syntax (notice that some database
systems don't allow deleting a column):
gisdatabase=# alter table departments drop column address;
ALTER TABLE
gisdatabase=#
The above statement will remove the address column from the departments table.
Deptid (Integer) Deptname (varchar(50)) Phone (varchar(10))
To change the data type or column size of a column in a table, use the following syntax:
gisdatabase=# alter table departments alter column deptname type
varchar(100);
ALTER TABLE
gisdatabase=#
The above command will modify the deptname column size from 50 to 100.
Deptid (Integer) Deptname (varchar(100)) Phone (varchar(10))
The following command will modify the name of deptname column to dname.
gisdatabase=# alter table departments rename column deptname to dname;
ALTER TABLE
gisdatabase=#
Deptid (Integer) Dname (varchar(100)) Phone (varchar(10))
To change table name use the following command to rename departments table to
dept:
gisdatabase=# alter table departments rename to dept;
ALTER TABLE
gisdatabase=#
Mohammed Mahmoud
3
3. Drop table:
The DROP TABLE statement is used to drop an existing table in a database. The
following SQL statement drops the existing table:
gisdatabase=# drop table departments;
DROP TABLE
gisdatabase=#
4. Constraints:
SQL constraints are used to specify rules for data in a table. Constraints 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 table. If there is any violation between the constraint and the data
action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to a
column, and table level constraints apply to the whole table. The following constraints
are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value.
UNIQUE - Ensures that all values in a column are different.
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table.
FOREIGN KEY - Uniquely identifies a row/record in another table.
CHECK - Ensures that all values in a column satisfies a specific condition.
DEFAULT - Sets a default value for a column when no value is specified.
INDEX - Used to create and retrieve data from the database very quickly.
Constraints can be specified when the table is created with the CREATE TABLE
statement, or after the table is created with the ALTER TABLE statement.
CREATE TABLE [TABLE NAME] (
[COLUMN NAME] DATA TYPE CONSTRAINT,
[COLUMN NAME] DATA TYPE CONSTRAINT,
.
.
.
[COLUMN NAME] DATA TYPE CONSTRAINT
);
Primary Key:
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys
must contain UNIQUE values, and cannot contain NULL values. A table can have only
one primary key, which may consist of single or multiple fields.
Mohammed Mahmoud
4
Not Null:
By default, a column can hold NULL values. The NOT NULL constraint enforces a
column to NOT accept NULL values. This enforces a field to always contain a value,
which means that you cannot insert a new record, or update a record without adding a
value to this field.
The following statement create a new table for BRANCHES the branchid column is
a PRIMARY KEY column and the branchname is NOT NULL column.
CREATE TABLE branches (
branchid serial PRIMARY KEY,
branchname VARCHAR(100) NOT NULL,
phone varchar(10)
);
Unique:
The UNIQUE constraint ensures that all values in a column are different. Both the
UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a
column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE
constraint. However, you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY
constraint on multiple columns, use the following SQL syntax create customers table
and add PRIMARY KEY constraints for custID with pk name and set UNIQUE
constraint for the Phone with uni name:
CREATE TABLE customers (
CustID SERIAL, CustName VARCHAR(100), Phone VARCHAR(15),
CONSTRAINT pk PRIMARY KEY (CustomerID),
CONSTRAINT uni UNIQUE (Phone)
);
Check:
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for
this column. If you define a CHECK constraint on a table it can limit the values in
certain columns based on values in other columns in the row.
The following SQL creates a CHECK constraint on the "Age" column when the
"Persons" table is created. The CHECK constraint ensures that you can not have any
person below 18 years:
Mohammed Mahmoud
5
CREATE TABLE Persons(
ID SERIAL,
PNAME VARCHAR(50),
AGE INTEGER CHECK(AGE >= 18)
);
Foreign Key:
A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a
field (or collection of fields) in one table that refers to the PRIMARY KEY in another
table. The table containing the foreign key is called the child table, and the table
containing the candidate key is called the referenced or parent table. Suppose we have
the following tables:
Customers:
CustID CustName Phone Address
1 M Mahmoud +249-123222555 Khartoum
2 Adam Ibrahim +249-123565897 Omdurman
3 Karam Ibrahim +249-112126845 Bahri
Orders:
OrderID Order CustID
1 Orange 1
2 Orange 3
3 Apple 2
4 Mango 3
Notice that the "CustID" column in the "Orders" table points to the "CustID" column
in the "Customers" table.
The "CustID" column in the "Customers" table is the PRIMARY KEY in the
"Customers" table.
The "CustID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links
between tables. The FOREIGN KEY constraint also prevents invalid data from being
inserted into the foreign key column, because it has to be one of the values contained
in the table it points to.
The following SQL creates a Customers Table and add PRIMARY KEY constraint on
the "CustID" column:
CREATE TABLE customers (
CustID SERIAL PRIMARY KEY,
Mohammed Mahmoud
6
CustName VARCHAR(100),
Phone Varchar(15),
CustAddress Varchar(255)
);
The following SQL creates a FOREIGN KEY on the "CustID" column when the
"Orders" table is created:
CREATE TABLE orders (
OrderID SERIAL PRIMARY KEY,
Order VARCHAR(100),
CustID INTEGER,
CONSTRAINT custid_kf FOREIGN KEY (CustID) REFERENCES customers(CustID)
);
Mohammed Mahmoud
7
