Tutorial 03: SQL DML commands
Data Manipulation Language (DML):
Data Manipulation Language (DML) is a family of commands allow users
to manipulate data in a database, this manipulation include inserting data
into database tables, deleting data from existing tables and modifying
existing data. There are three DML statements INSERT, UPDATE and
DELETE.
Suppose you have the following table, which include data about customers.
CustomerID CustomerName Address City PostalCode Country
gisdatabase=# CREATE TABLE customers (CustomerID INTEGER, CustomerName
VARCHAR(100), Adress VARCHAR(50), City VARCHAR(50), PostalCode
VARCHAR(5), Country VARCHAR(50));
CREATE TABLE
gisdatabase=#
Use the \d command to show table description:
gisdatabase=# \d customers
Table "public.customers"
Column | Type | Collation | Nullable | Default
--------------+------------------------+-----------+----------+---------
customerid | integer | | |
customername | character varying(100) | | |
adress | character varying(50) | | |
city | character varying(50) | | |
postalcode | character varying(5) | | |
country | character varying(50) | | |
1. Insert Into:
The INSERT INTO statement is used to insert new records in a table. It is
possible to write the INSERT INTO statement in two ways. The first way
specifies both the column names and the values to be inserted:
gisdatabase=# INSERT INTO customers (customerid, customername, adress,
city, postalcode, country) VALUES (1, 'Mohamed ALi', 'ALSAFIA BLOCK
57', 'BAHRI', '11232', 'SUDAN');
INSERT 0 1
gisdatabase=#
Mohammed Mahmoud
1
The above statements will insert data for customer number one into
customers table:
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALSAFIA BLOCK 57 BAHRI 11232 SUDAN
It is also possible to only insert data in specific columns. The following SQL statement
will insert a new record, but only insert data in the"CustomerId", "CustomerName",
"City", and "Country" columns.
gisdatabase=# INSERT INTO customers (customerid, customername, city,
country) VALUES (2, 'Salah Elhaj', 'KHARTOUM', 'SUDAN');
INSERT 0 1
gisdatabase=#
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALSAFIA BLOCK 57 BAHRI 11232 SUDAN
2 Salah Elhaj KHARTOUM SUDAN
If you are adding values for all the columns of the table, you do not need
to specify the column names in the SQL query. However, make sure the
order of the values is in the same order as the columns in the table. The
INSERT INTO syntax would be as follows:
gisdatabase=# INSERT INTO customers VALUES (3, 'Saeed Suliman',
'ALRIYADH BLOCK 24', 'KHARTOUM', '05023', 'SUDAN');
INSERT 0 1
gisdatabase=#
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALSAFIA BLOCK 57 BAHRI 11232 SUDAN
2 Salah Elhaj KHARTOUM SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05023 SUDAN
Classwork: Write SQL Statement to insert the following data into
customers table:
Customerid Customername Address City PostalCode Country
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
Mohammed Mahmoud
2
2. Update Set:
The UPDATE statement is used to modify the existing records in a table.
Below is a customers table from gisdatabase.
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALSAFIA BLOCK 57 BAHRI 11232 SUDAN
2 Salah Elhaj KHARTOUM SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05023 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
The following SQL statement updates the first customer (CustomerID = 1)
with a new country, new address and a new city.
gisdatabase=# UPDATE customers SET country='EGYPT', city='CAIRO',
adress='ALMOHANDISEEN BLOCK 1' WHERE customerid=1;
UPDATE 1
gisdatabase=#
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
2 Salah Elhaj KHARTOUM SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05023 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
It is the WHERE clause that determines how many records that will be updated. The
following SQL statement will update the PostalCode to "05011" for all records where
city is "KHARTOUM".
gisdatabase=# UPDATE customers SET postalcode='05011' WHERE
city='KHARTOUM';
UPDATE 2
gisdatabase=#
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
Mohammed Mahmoud
3
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
3. Delete From:
The DELETE statement is used to delete existing records in a table. Below
is a customers table from gisdatabase.
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
The following SQL statement deletes the customer “Salah Elhaj” from
customers table:
gisdatabase=# DELETE FROM customers WHERE customername='Salah Elhaj';
DELETE 1
gisdatabase=#
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
It is possible to delete all rows in a table without deleting the table. This
means that the table structure, attributes, and indexes will be intact, The
following SQL statement deletes all rows in the "Customers" table, without
deleting the table:
gisdatabase=# DELETE FROM customers;
Mohammed Mahmoud
4
DELETE 4
gisdatabase=#
4. Truncate:
The truncate statement is used to delete all records in a table. the following
statement will delete all records from customers table, without deleting the
table:
gisdatabase=# TRUNCATE table customers;
TRUNCATE TABLE
gisdatabase=#
5. Data Retrieve Command (SELECT):
The SELECT statement is used to select data from a database. The data
returned is stored in a result table, called the result-set. The following code
is a syntax of Select statement:
SELECT COLUMN1, COLUMN2, ... FROM TABLENAME ;
Here, column1, column2, ... are the field names of the table you want to
select data from. If you want to select all the fields available in the table,
use the following syntax:
SELECT * FROM TABLENAME ;
Suppose we have the following customers table:
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
The following statement select customerName and City from customers
table:
SELECT customerName, City FROM customers ;
Mohammed Mahmoud
5
CustomerName City
Mohamed Ali CAIRO
Salah Elhaj KHARTOUM
Saeed Suliman KHARTOUM
Yousif Elbagir OMDORMAN
Mahasin Mohamed BAHRI
The following statement SELECT all columns from customers table:
SELECT * FROM customers ;
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
The SELECT DISTINCT statement is used to return only distinct
(different) values. Inside a table, a column often contains many duplicate
values; and sometimes you only want to list the different (distinct) values.
SELECT DISTINCT City FROM customers ;
City
CAIRO
KHARTOUM
OMDORMAN
BAHRI
The WHERE clause is used to filter records. The WHERE clause is used
to extract only those records that fulfill a specified condition.
SELECT column1, column2, ... FROM tablename WHERE condition;
The following SQL statement selects all the customers from the country
"Sudan", in the "Customers" table:
SELECT * FROM Customers WHERE Country='Sudan';
CustomerID CustomerName Address City PostalCode Country
Mohammed Mahmoud
6
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
SQL requires single quotes around text values (most database systems will
also allow double quotes). However, numeric fields should not be enclosed
in quotes:
SELECT * FROM Customers WHERE customerID=1;
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
The following operators can be used in the WHERE clause:
Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator
may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than
one condition:
The AND operator displays a record if all the conditions separated by
AND is TRUE.
The OR operator displays a record if any of the conditions separated by
OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
Mohammed Mahmoud
7
SELECT * FROM table_name WHERE condition1 AND condition2;
The following SQL statement selects all fields from "Customers" where
country is "Sudan" AND city is "Khartoum":
SELECT * FROM customers WHERE country='Sudan' AND city='Khartoum';
CustomerID CustomerName Address City PostalCode Country
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
The following SQL statement selects all fields from "Customers" where
city is "Cairo" OR "Khartoum":
SELECT * FROM customers WHERE city='Cairo' OR city='Khartoum';
CustomerID CustomerName Address City PostalCode Country
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
The ORDER BY keyword is used to sort the result-set in ascending or
descending order. The ORDER BY keyword sorts the records in ascending
order by default. To sort the records in descending order, use the DESC
keyword.
SELECT column1, column2, ... FROM tableName ORDER BY customerName
ASC|DESC ;
The following SQL statement selects all customers from the "Customers"
table, sorted DESCENDING by the "Country" column:
SELECT * FROM Customers ORDER BY Country DESC;
CustomerID CustomerName Address City PostalCode Country
2 Salah Elhaj KHARTOUM 05011 SUDAN
3 Saeed Suliman ALRIYADH BLOCK 24 KHARTOUM 05011 SUDAN
4 Yousif Elbagir ALMOHANDISEEN OMDORMAN 43521 SUDAN
BLOCK 6
5 Mahasin KAFORY BLOCK 8 BAHRI 11521 SUDAN
Mohamed
Mohammed Mahmoud
8
1 Mohamed Ali ALMOHANDISEEN CAIRO 11232 EGYPT
BLOCK 1
Mohammed Mahmoud
9
