Introduction to Data Manipulation Language
The SQL data manipulation language (DML) is used to query and modify
database data. Here we will describe how to use the SELECT, INSERT,
UPDATE, and DELETE SQL DML command statements, defined below.
• SELECT – to query data in the database
• INSERT – to insert data into a table
• UPDATE – to update data in a table
• DELETE – to delete data from a table
In the SQL DML statement, each clause in a statement should begin on a new
line. If a clause has several parts, they should appear on separate lines and
be indented under the start of the clause to show the relationship.
SELECT Statement: A Basic Form and the WHERE Clause
The SQL language has one basic statement for retrieving information from a
database: the SELECT statement. With this statement, it is possible to query
information from one or more tables of a database. The simplest form of the
SELECT statement contains a SELECT list with a FROM clause. A SELECT
statement can retrieve either certain columns or rows from a table.
Example: We can use the SELECT statement to generate an employee
Fname, Lname and Salary from the Employees table as follows:
SELECT Fname, Lname, Salary FROM Employee
This action will display the employee name and salary number from the
Employees table.
Example: Get full details of all departments:
SELECT * FROM department;
The asterisk symbol (*), which specifies all columns of the named tables in
the FROM clause.
WHERE Clause
1
The simplest form of the SELECT statement, described in the previous
Page
section, is not very useful for queries. The SELECT statement allows the user
Introduction to DML Dr. A Maatuk
to extract data from tables, based on specific criteria. The WHERE clause
specifies a Boolean expression (an expression that returns a value of TRUE or
FALSE) that is tested for each row to be returned (potentially). If the
expression is true, then the row is returned; if it is false, it is discarded.
Example: Get the names and numbers of the department managed by
manager no 33445555.
SELECT dname, dnumber FROM department WHERE mgrssn = 333445555;
In addition to the equal sign, the WHERE clause can contain other
comparison operators, including the following:
<> (or !=) not equal
< less than
> greater than
>= greater than or equal
<= less than or equal
!> not greater than
!< not less than
Example: Get the first and last names of all employees who get $30000 or
more. This shows the use of a comparison operator in the WHERE clause.
SELECT fname, lname FROM Employee WHERE salary >= 30000;
Boolean Operators
WHERE clause conditions can either be simple or contain multiple conditions.
Multiple conditions can be built using the Boolean operators AND, OR, and
NOT. If two conditions are connected by the AND operator, rows are
retrieved for which both conditions are true. If two conditions are connected
by the OR operator, all rows of a table are retrieved in which either the first
or the second condition (or both) is true, as shown in the nest Example.
Example: Get the employee and project numbers for all employees those
work for either project 1 or project 2 (or both):
2
SELECT essn, pno FROM works_on WHERE pno = 1 OR pno = 2;
Page
Introduction to DML Dr. A Maatuk
The result of the above query contains some duplicate values of the essn
column. If this redundant information is to be eliminated, the DISTINCT
option should be used, as shown here:
SELECT DISTINCT essn FROM works_on WHERE pno = 1 OR pno = 2;
The third Boolean operator, NOT, changes the logical value of the
corresponding condition.
Example: Get the employee numbers and first names of all employees who
do not belong to the department 4:
SELECT ssn, fname FROM Employee WHERE NOT dno = 4;
IN and BETWEEN Operators
An IN operator allows the specification of two or more expressions to be used
for a query search. The result of the condition returns true if the value of the
corresponding column equals one of the expressions specified by the IN
predicate.
Example: Get all number and names of employees whose employee-number
equals 123456789, 333445555 or 987654321:
SELECT ssn, fname, lname FROM employee WHERE ssn IN (123456789,
333445555, 987654321);
The IN operator can be used together with the Boolean operator NOT. In this
case, the query retrieves rows that do not include any of the listed values in
the corresponding columns.
Example: Get all information for every employee whose supervisor number
is neither 123456789 nor 333445555:
SELECT * FROM employee WHERE superssn NOT IN (123456789,
333445555);
The BETWEEN operator specifies a range, which determines the lower and
3
upper bounds of qualifying values.
Page
Introduction to DML Dr. A Maatuk
Example: Get last names and salaries for all employees with a budget
between $20,000 and $30,000.
SELECT lname, salary FROM Employee WHERE salary BETWEEN 20000 AND
30000;
Example: Get the last names of all employees with a salary less than
$20000 and greater than $30000
SELECT lname FROM Employee WHERE salary NOT BETWEEN 20000 AND
30000;
Queries Involving NULL Values
To retrieve the rows with NULL values in the column, SQL includes the
operator IS NULL.
Example: Get project numbers that have unknown locations.
SELECT pnumber FROM Project WHERE plocation IS NULL
Because all comparisons with NULL values return false, the example below
shows syntactically correct but logically incorrect, usage of NULL.
Example:
SELECT pno, hours FROM works_on WHERE hours IS NOT NULL
LIKE Operator
LIKE is an operator that is used for pattern matching; that is, it compares
column values with a specified pattern. The data type of the column can be
any character or date.
Two of the pattern are:
% (percent sign) specifies any sequence of zero or more characters
_ (underscore) specifies any single character
4
Page
Introduction to DML Dr. A Maatuk
Example: Get the first and last names and numbers of all employees whose
first name contains the letter 'a' as the second character:
SELECT fname, lname, ssn FROM employee WHERE fname LIKE '_a%'
Example: Get details of all employees whose first name does not end with
the character 'n'.
SELECT ssn, fname, lname FROM employee WHERE fname NOT LIKE '%n';
SELECT Statement: Other Clauses and Functions
The following examples describe the clauses used in a query as aggregate
functions and set operators.
SQL Functions
The SQL functions can be either aggregate functions or scalar functions.
Aggregate Functions
AVG calculates the arithmetic average of the data values contained
within a column. The column must contain numeric values.
MAX and MIN calculate the maximum and minimum data value of the
column, respectively. The column can contain numeric, string, and
date/time values.
SUM calculates the total of all data values in a column. The column
must contain numeric values.
COUNT calculates the number of (non-null) data values in a column.
The only aggregate function not being applied to columns is COUNT(*).
This function returns the number of rows (whether or not particular
columns have NULL values).
All aggregate functions operate on a single argument, which can be either a
column or an expression. The only exception is the second form of the
COUNT function: COUNT(*). The result of each aggregate function is a
constant value, which is displayed in a separate column of the result.
5
Example: Get the lowest employee salary:
Page
Introduction to DML Dr. A Maatuk
SELECT MIN(salary) FROM Employee
Example: Get the mgrstartdate that was entered last in the department
table:
SELECT MAX(mgrstartdate) FROM Department
SUM Aggregate Function
The aggregate function SUM calculates the sum of the values in a column.
Example : Calculate the sum of hours on project 2:
SELECT SUM(hours) FROM Works_on WHERE pno='2'
AVG Aggregate Function
The aggregate function AVG calculates the average of the values in the
column.
Example: Calculate the average of all salaries with an amount greater than
$1000:
SELECT AVG(salary) FROM Employee WHERE salary > 1000;
COUNT Aggregate Functions
When the DISTINCT keyword is used, all duplicate values are eliminated
before COUNT is applied. This form of COUNT does not count NULL values for
the column.
Example: Count all different department controls each project:
SELECT dnum, COUNT(pnumber) FROM Project GROUP BY dnum;
Example : Get the number of employees in all projects:
6
SELECT pno, COUNT(*) FROM works_on GROUP BY pno;
Page
Introduction to DML Dr. A Maatuk
Scalar Functions
SQL provides several scalar functions that are used in the construction of
scalar expressions like numeric functions, date functions and String
functions.
Examples:
ABS(n) returns the absolute value (i.e., negative values are returned as
positive) of the numeric expression n.
Example: SELECT ABS(-5.767) = 5.767,
SELECT ABS(6.384) = 6.384
POWER(x,y) calculates the value xy.
Example: SELECT POWER(3.12,5) = 295.65
SELECT POWER(81,0.5)
SQRT(n) calculates the square root of n.
Example: SELECT SQRT(9) = 3
GETDATE() returns the current system date and time.
Example: SELECT GETDATE() = 2008-01-01 13:03:31.390
LEFT(z, length) returns the first length characters from the string z.
Example: select left(fname,3) from Employee
RIGHT(z, length) returns the last length characters from the string z.
LEN(z) returns the number of characters, instead of the number of
bytes, of the specified string expression, excluding trailing blanks.
LOWER(z1) converts all uppercase letters of the string z1 to lowercase
letters. Lowercase letters and numbers, and other characters, do not
change.
Example: SELECT LOWER('BiG') = 'big'
GROUP BY Clause
7
Page
Introduction to DML Dr. A Maatuk
The GROUP BY clause defines one or more columns as a group such that all
rows within any group have the same values for those columns.
Example : Get the number of employees for each department.
SELECT count(*) FROM Employee GROUP BY dno
Example : Get the number of hours spent by employees grouped by project
numbers.
SELECT sum(hours), pno FROM works_on GROUP BY pno;
Example : Calculate the average of employee salaries for each department.
SELECT dno, AVG(salary) FROM Employee GROUP BY dno
HAVING Clause
The HAVING clause defines the condition that is applied to groups of rows.
Hence, this clause has the same meaning to groups of rows that the WHERE
clause has to the content of the corresponding table.
Example: Get project numbers for all projects employing fewer than three
persons. This example shows the use of the HAVING clause with the
aggregate function COUNT(*).
SELECT pno, count(*) FROM Works_on GROUP BY pno HAVING COUNT(*) < 3;
In the example, the system uses the GROUP BY clause to group all rows
according to existing values in the pno column. After that, it counts the
number of rows in each group and selects those groups with three or fewer
rows. The HAVING clause can also be used without aggregate functions.
ORDER BY Clause
The ORDER BY clause defines the particular order of the rows in the result of
a query.
8
Example: Get department numbers and employee last names for
Page
employees in department 5, in ascending order of last names:
Introduction to DML Dr. A Maatuk
SELECT dno, lname FROM Employee WHERE dno = 5 ORDER BY lname;
With ASC:
SELECT * FROM Employee ORDER BY ssn ASC
And with DESC:
SELECT * FROM Department ORDER BY dname DESC
Modification of Contents of Tables
In addition to the SELECT statement, there are three other DML statements:
INSERT, UPDATE, and DELETE, which operate on tables. This section
discusses these statements.
INSERT Statement
The INSERT statement inserts rows (or parts of them) into a table. Every
inserted value must have a data type that is compatible with the data type of
the corresponding column of the table. To ensure compatibility, all character-
based values and temporal data must be enclosed in apostrophes, while all
numeric values need no such enclosing.
Example: Load data into the Department table:
insert into Department values ('Headquater' , 1,888665555,'06/19/1981');
insert into Department values ('Administration', 4,987654321,'01/01/1985');
insert into Department values ('Research' , 5,333445555,'05/22/1988');
Inserting a Single Row
9
Page
Introduction to DML Dr. A Maatuk
In the INSERT statement, the explicit specification of the column list is
optional. This means that omitting the list of columns is equivalent to
specifying a list of all columns in the table. This action shows the use of the
INSERT statement to load a small amount of data into a database.
The column names can be stated before inserting their values:
insert into Department (dname, dnumber, mgrssn, mgrstartdate) values
('Headquater', 1,888665555,'06/19/1981');
The insertion of values into some (but not all) of a table’s columns usually
require the explicit specification of the corresponding columns. The omitted
columns must either be nullable or have a DEFAULT value.
Example:
INSERT INTO Employee (ssn, fname, lname) VALUES (15201, 'Dave', 'Davis');
Their other values e.g., dno and salary columns are the only nullable
columns in the EMPLOYEE table because all other columns in the EMPLOYEE
table were declared with the NOT NULL clause in the CREATE TABLE
statement.
The order of column names in the VALUE clause of the INSERT statement can
be different from the original order of those columns, which is determined in
the CREATE TABLE statement. In this case, it is necessary to list the columns
in the new order.
UPDATE Statement
The UPDATE statement modifies the values of table rows. This statement has
the general rows in the table are modified by the WHERE clause. For each
row to be modified, the UPDATE statement changes the values of the
columns in the SET clause, assigning a constant (or generally an expression)
to the associated column. If the WHERE clause is omitted, the UPDATE
statement modifies all rows of the table.
10
Example: Change all the manager number of departments to be
123456789:
Page
Introduction to DML Dr. A Maatuk
UPDATE Department set mgrssn = 123456789
Example: Set the hours of employee number 123456789, who works on
project p2, to be 10 hours:
UPDATE Works_on SET hours = 10 WHERE essn = 123456789 AND pno =
'1';
The UPDATE statement in the example modifies exactly one row of the
Works_on table, because the combination of the columns essn and pno
builds the primary key of that table and is therefore unique. This example
modifies the hours of the employee, which was previously unknown, set to
NULL or error.
Example: Change the salary of all employees. The new rate of change is the
addition of 0.25 of the old salary.
UPDATE Employee SET salary = salary *1.25;
In the example, all rows of the EMPLOYEE table will be modified because of
the omitted WHERE clause.
DELETE Statement
The DELETE statement deletes rows from a table.
DELETE FROM table_name [WHERE predicate];
All rows that satisfy the condition in the WHERE clause will be deleted.
Explicitly naming columns within the DELETE statement is not necessary (or
allowed), because the DELETE statement operates on rows and not on
columns.
Example: Delete all Departments:
11
DELETE FROM Department;
Page
Introduction to DML Dr. A Maatuk
Example: Delete all Departments that have no managers:
DELETE FROM Department WHERE mgr_ssn IS NULL;
The WHERE clause in the DELETE statement can contain an inner query.
Example: Delete data of Employees who earn more than 50000.
DELETE FROM Employee WHERE salary > 50000
Example: Delete all data of the DEPENDENT table.
DELETE FROM DEPENDENT
Join Operator (Retrieving data from more than one table)
The previous use of the SELECT statement is to query rows from one table of
a database. The SQL provides the join operator, which retrieves data from
more than one table. This operator is probably the most important operator
for relational database systems because it allows data to be spread over
many tables and thus achieves a vital property of database systems.
Natural (Inner) Join
Natural (or inner) join connects two tables on a column with the same data
type. Only the rows where the column values match are returned;
unmatched rows are discarded.
Example: Get full details of each employee; that is, besides the employee’s
number, first and last names, and corresponding department number, also
get the name of his or her department and its location, with duplicate
columns displayed.
12
SELECT * FROM Employee INNER JOIN Department ON dno = dnumber;
Page
Introduction to DML Dr. A Maatuk
SELECT employee.*, department.* FROM employee INNER JOIN department
ON employee.dno = department.dnumber;
The SELECT list in the Example includes all columns of the EMPLOYEE and
DEPARTMENT tables. The FROM clause in the SELECT statement specifies the
tables that are joined as well as the explicit name of the join form (INNER
JOIN). The ON clause is also part of the FROM clause; as it specifies the join
columns from both tables. The condition EMPLOYEE.DNO =
DEPARTMENT.DNUMBER specifies a join condition, and both columns are said
to be join columns.
The equivalent solution is as follows: “Old-style” join syntax:
SELECT ssn, dno, dname FROM Employee, Department WHERE dno=
dnumber
Example : Get numbers of employees and names of projects for employees
who work on the project no 30.
SELECT essn, pname FROM WORKS_ON JOIN PROJECT
ON PROJECT.PNUMBER = WORKS_ON.PNO WHERE pno = 30;
Joins Three Tables.
Example: Get the first and last names of employees whose department is
located in 'Houston':
SELECT fname, lname FROM Employee JOIN Department
ON Employee.dno = Department.dnumber JOIN Dept_locations
ON Department.dnumber = Dept_locations.dnumber
AND Dept_locations.dlocation = 'Houston';
OR
SELECT fname, lname FROM Employee e JOIN Department d
ON e.dno = d.dnumber JOIN Dept_locations l
ON d.dnumber = l.dnumber
AND l.dlocation = 'Houston';
13
Outer Join
Page
Introduction to DML Dr. A Maatuk
In the previous examples of natural join, the result set included only rows
from one table that have corresponding rows in the other table. Sometimes it
is necessary to retrieve, in addition to the matching rows, the unmatched
rows from one or both of the tables. Such an operation is called an outer join.
Next Examples show the difference between a natural join and the
corresponding outer join.
Left Outer Join
The LEFT OUTER JOIN specifies that all left outer rows to be returned. All
rows from the left table that did not meet the condition specified are
included in the results set, and output columns from the other table are set
to NULL.
Example: Get details for all employees plus the names of their departments:
SELECT fname, dnumber, dname FROM EMPLOYEE LEFT OUTER JOIN
DEPARTMENT
ON DNO = DNUMBER
Right Outer Join
The RIGHT OUTER JOIN includes all rows from the right table that did not
meet the condition specified. Output columns that correspond to the other
table are set to NULL.
Example : Get full details of all departments, as well as all living places of
their employees, for all cities that are either the locations of departments or
the living and working places of an employee.
SELECT employee.*, department.* FROM employee RIGHT OUTER JOIN
department
ON dno = dnumber;
Full Outer Join
There is also the FULL OUTER JOIN, which is defined as the union of the left
and right outer joins. A full outer join specifies that if a row from either table
does not match the selection criteria, the row is included in the result set,
14
and its output columns that correspond to the other table are set to NULL. In
other words, all rows from both tables are represented in the result set. If
Page
there is no corresponding row in one of the tables, its columns are returned
Introduction to DML Dr. A Maatuk
with NULL values. This operation is specified using the FULL OUTER JOIN
operator.
Example: Get the first name and department number and department
names for all Employees using FULL OUTER JOIN.
SELECT fname, dnumber, dname FROM EMPLOYEE FULL OUTER JOIN
DEPARTMENT
ON DNO = DNUMBER
Example: Get the first name and department number and department
names for all Employees using full outer join where department number is 1
or 2 or 5 ordered by department number.
SELECT fname, dnumber, dname FROM Employee FULL OUTER JOIN
Department ON dno = dnumber WHERE dno IN (1, 2, 5) ORDER BY dno
Sub-Queries
The SQL language offers the ability to compare column values with the result
of another SELECT statement. Such construct, where one or more SELECT
statements are nested in the WHERE clause of another SELECT statement, is
called a sub-query. The first SELECT statement of a sub-query is called the
outer query - in contrast to the inner query, which denotes the SELECT
statement(s) used in a comparison. The inner query will be evaluated first,
and the outer query receives the values of the inner query.
Example: Get the first and last names of employees who work in the
'Research' department:
SELECT fname, lname FROM Employee
WHERE dno = (SELECT dnumber FROM Department WHERE dname =
'Research');
The inner query is logically evaluated first. That query returns the number of
the research department. Thus, after the evaluation of the inner query, the
sub-query can be represented with the following equivalent query:
15
SELECT fname, lname FROM employee WHERE dno = 5;
Page
Introduction to DML Dr. A Maatuk
Example: Get full details of all employees whose department is located in
Houston:
SELECT * FROM Employee WHERE dno IN
(SELECT dnumber FROM Dept_locations WHERE dlocation = 'Houston');
Example:
Return first name and salaries of employees who get a salary bigger than the
salary of the employee of number 123456789.
How query works:
The inner query determines the salary of employee 123456789, and the
outter query uses this result to return the salaries of employees who get who
get a salary bigger than the salary of the employee of number 123456789.
SELECT fname, salary FROM EMPLOYEE WHERE salary >
(SELECT salary FROM EMPLOYEE WHERE ssn=123456789)
Example: Get the first name and salaries of all employees who are working
in the same department that the employee of number 123456789 works in
and that they have a less salary of this employee.
select fname, salary from Employee
where dno = (select dno from Employee where ssn = 987654321)
and salary < (select salary from Employee where ssn =
987654321);
Example: Find the last names of Employees who work in Project '1'
select lname from employee where ssn in (select essn from works_on where
pno = '1')
The inner query returns the employee numbers who work in the project '1',
and the outer query returns the last names based on the employee numbers
set that are returned from the inner query. We used the IN operator to return
16
the employee numbers set.
Page
Question: What the following query returns?
Introduction to DML Dr. A Maatuk
select sname from student where stud_no
in (select stud_no from enroll where cour_no
in ( select cour_no from course where credit = 4 ))
17
Page
Introduction to DML Dr. A Maatuk