Chapter 6

Structured Query Language (SQL)

•Structured Query Language (SQL) is a query language that is
standardized by the American National Standards Institute (ANSI) for
most commercial relational database management systems (RDBMS).
•Ideally, a database language should allow a user to create the database
and relation structures; it should allow a user to perform basic data
management tasks, such as the insertion, modification and deletion of
data from the relations; and it should allow a user to perform both simple
and complex queries to transform the raw data into information.
•In addition, a database language must perform these tasks with minimal
user effort, and its command structure and syntax must be relatively easy
to learn.
• SQL is an example of a transform-oriented language, or a
language designed to use relations (tables) to transform inputs
into required outputs.
As a language SQL has two components:
 A Data Definition Language (DDL) for defining the database
structure, and
A Data Manipulation Language (DML) for retrieving and updating data.

1
 SQL contains only these definitional and manipulative commands; it does not
contain flow control commands. In other words, there are no IF..THEN..ELSE,
GO TO, DO ... WHILE or other commands to provide a flow of control.
1. Data Definition
 The SQL data definition language allows us to create or destroy database
objects (schemas, domains, tables, views and indexes).
The main SQL data definition language statements are:
CREATE SCHEMA
DROP SCHEMA
CREATE DOMAIN
ALTER DOMAIN
DROP DOMAIN
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE VIEW
DROP VIEW
 The SCHEMA is a collection of database objects that are in some way related
to one another. (All objects in a database are described in one schema or
another).
 The objects in a schema can be tables, views, domains, character sets,
assertions (rules), etc.
 At present CREATE and DROP SCHEMA are not yet widely implemented.
2
 In some implementations, the following
statement is used instead of SCHEMA.
CREATE DATABASE database_name
 So, this might be the first step before starting
to create any object.
Data definition for Domains
 CREATE DOMAIN domain_name [AS]
data_type
 DEFAULT default_value [CHECK
(search_condition)]
 A domain is given a name, domain_name, a
data type (CHAR, VARCHAR, INTEGER,
NUMERIC, FLOAT, DATE, etc.)
3
Examples:
 CREATE DOMAIN gender AS CHARACTER (1) CHECK
(VALUE IN (‘M’,’F’))
 Creates a domain called gender that consists of a single
character with either the value ‘M’ or ‘F’.When defining the Sex
column we can use this domain.
 DROP DOMAIN domain_name [RESTRICT|CASCADE]
 Removes domains defined in the system. In the CASCADE, any
table column that is based on the domain is automatically
changed to the underlying data type.
 RESTRICT means don`t remove domain if it is used in any
column definition.
DROP DOMAIN gender
 Creating Database
Syntax
CREATE DATABASE <database_name>
<database_name> is the name of the new database.
Example:
CREATE DATABASE test
4
Table Creation and Modification
The CREATE TABLE command in the SQL statement is used to specify a
new relation in a database by giving it a name and listing its attributes.
syntax
CREATE TABLE table_name
(column_name data type| [NOT NULL|NULL]]
[DEFAULT default_value][CHECK (search_condition)] ... {for each column
and then after all columns defined}
[PRIMARY KEY (column(s))][UNIQUE (column(s))]
[FOREIGN KEY (column(s)) REFERENCES target_table_name
[list_of_candidate_key_columns]
[ON UPDATE referential_integrity_action]
[ON DELETE referential_integrity_action]]
- <column_name> is the name of the column.
- <data_type> is the SQL supported data types: CHAR(n),VARCHAR(n),
INT,
SMALLINT, DECIMAL(i,j), DATE, TIME (DATETIME), …
- {column_constraint} is optional constraints on the column such as NULL,
NOT NULL,
PRIMARY KEY, FOREIGN KEY, UNIQUE, DEFAULT, …

5
Examples
1. Create a table with fields name. age and sex.
CREATE TABLE newtable (name char(30) NOT NULL, age
integer NOT NULL CHECK age>0, sex char(1))
2. Create a table with fields: name, age, sex and city; and make the
default value of city ‘Addis Ababa’ (expecting many records will
hold this value) and a rule for male members to be of age>30.
CREATE TABLE temp (name char(30) NOT NULL, age integer
NOT NULL CHECK age>0, sex char(1) CHECK VALUE IN
(‘M’,’F’) PRIMARY KEY (name) CHECK sex=‘M’AND age>30)
3. The following table defines a foreign key rule for the employee
table whose target is the department table.
CREATE TABLE department (depid char(5) NOT NULL,
depname char(40), depid char(5), budget float(14,2) PRIMARY
KEY (depid) UNIQUE (depname))
CREATE TABLE employee (empid char(5) NOT NULL,
empname char(40), depid char(5), salary float(10,2) PRIMARY
KEY (empid) FOREIGN KEY (depid) REFERENCES department
ON UPDATE CASCADE ON DELETE CASCADE)

6
Changing a Table definition (ALTER TABLE)
 The ALTER TABLE command in SQL is used to change the structure/definition
of an existing table.
 It is used mainly to:
Add a new column to a table
Drop a column from a table
Add a new table constraint
Drop a table constraint
Set a default for a column
Drop the default for a column
 The basic format of the statement is:
ALTER TABLE table_name ADD|ALTER column_name data_type [NOT
NULL][DEFAULT dvalue] [CHECK (condition)]]
[ALTER column_name data_type]
[DROP column_name [RESTRICT|CASCADE]]
[ADD [CONSTRAINT [constraint_name]] table_constraint_definition]
[DROP CONSTRAINT constraint_name [RESTRICT|CASCADE]]
[ALTER [COLUMN] SET DEFAULT default_value]
[ALTER [COLUMN] DROP DEFAULT]
 Where the parameters are as defined in the CREATE TABLE. The
table_constraint_defintion is one of the clauses:
PRIMARY KEY, UNIQUE, FOREIGN KEY OR CHECK.
7
Examples
1. Change the field width of the empid column inside the
employee table.
ALTER TABLE employee ALTER COLUMN empid
character (20)
2. Add a new default value of .AA. for the empid field inside
the employee table.
ALTER TABLE employee ALTER COLUMN empid SET
DEFAULT .AA.
3. Tell the system that empid is the primary key inside the
employee table.
ALTER TABLE employee ADD PRIMARY KEY (empid)
Removing Tables (DROP TABLE)
 To remove a table we use the DROP TABLE command
as follows:
DROP TABLE table_name

8
2. Data Manipulation Language
 The Data Manipulation Language (DML) is part of the
SQL syntax for executing queries to insert, retrieve,
update, and delete records. The statements are;
- INSERT INTO - inserts new data into a database table.
- SELECT - extracts data from a database table.
- UPDATE - updates data in a database table.
- DELETE - deletes data from a database table.
SELECT statement
 The purpose of the SELECT statement is to retrieve and
display data from one or more database tables.
 It is the most frequently used SQL command. The
general form of the SELECT statement is:
 SELECT [DISTINCT|ALL]
{*|[Column_expression [AS new_name]] }
FROM table_expression [WHERE condition]
[GROUP BY column_list] [HAVING condition]
[ORDER BY column_list]
9
 Column_expression represents a column or an expression.
The sequence of processing in a SELECT statement is:
 FROM Specifies the table or tables used.
 WHERE Filters the rows subject to some
condition.
 GROUP BY Form groups of rows the same
column value.
 HAVING Filters the groups subject to some
condition.
 SELECT Specifies which columns are to appear
in the output.
 ORDER BY Specifies the order of the output.
 The above order of the clauses in the processing of
SELECT cannot be changed. The only two mandatory
clauses are the first two: SELECT and FROM and the
remainder are optional. The result of a query on a
table is another table.
 The following examples show the different usage of
this statement. 10
Consider the following table for the examples.

Employee
Department
EmpId Empname Depid Salary

A8 Abebe Kebede 1 1050.00 Dep Department Dept_

B4 Girma Belew 1 500.45 id tel
1 Finance 1
A5 Ahmed 2 675.99
Mohammed 2 Planning 2
B6 Zahara Hagos 3 710.00
3 Administration 3
C4 Tarik Sisay 2 835.00
C5 Tadesse Belay 1 980.00

I. Column Selection
1. Retrieve all rows and all columns
SELECT empid, empname, depid, salary
FROM employee

11
•Since many SQL retrievals require all columns of a
table, there is a quick way of expressing all columns.,
using an asterisk (*) in place of the column names.
SELECT * FROM EMPLOYEE
The result table for the above two SELECT
statements is:
EmpId Empname Depid Salary

A8 Abebe Kebede 1 1050.00

B4 Girma Belew 1 500.45
A5 Ahmed Mohammed 2 675.99
B6 Zahara Hagos 3 710.00
C4 Tarik Sisay 2 835.00
C5 Tadesse Belay 1 980.00

12
2. Retrieve specific columns, in all rows.
SELECT empname, salary FROM
employee
 This shows all employees with their name
and salary.
Empname Salary
Abebe Kebede 1050.00
Girma Belew 500.45
Ahmed Mohammed 675.99
Zahara Hagos 710.00
Tarik Sisay 835.00
Tadesse Belay 980.00
13
3. Using distinct (includes all those values that
are unique in a particular column (by
eliminating duplicate values))
 SELECT DISTINCT depid FROM employee
Depid
1
2
3

4. Using a calculated field or an expression

 SELECT empname,’Yearly salary =‘AS title,
salary * 12 as yearly FROM employee
 This command displays the following table.
Note the title and Yearly column names in the
following table heading.
14
 Empname
Title Salary
Abebe Kebede Yearly salary= 12600.00
Girma Belew Yearly salary= 6005.40

Ahmed Mohammed Yearly salary= 8111.88

Zahara Hagos Yearly salary= 8520.00

Tarik Sisay Yearly salary= 10020.00

Tadesse Belay Yearly salary= 11760.00

Row selection
• The above examples show the use of the SELECT statement to
retrieve all rows from a table.
• However, we often need to restrict the rows that are retrieved.
• This can be achieved with the WHERE clause, which consists of the
keyword WHERE followed by a search condition that specifies the
rows to be retrieved.
• The five basic search conditions are as follows: 15
Comparison Compare the value of one or more expression to the
value of another expression using the following
operators. (<, >, =, <=, >=, and, <>) and the logical
operators (NOT, AND and OR).
Range Test whether the value of an expression falls within a specified
range (BETWEEN low, high).
Set Test whether the value of an expression equals one of a set of
membership values. (Using IN).
Pattern Test whether a string matches a specified a pattern (name LIKE
match .abebe. ).
Null Tests whether a column has a null (unknown) value.

List all employees with a salary greater than 800

SELECT * FROM employee WHERE salary > 800

EmpID Empname Depid Salary

A8 Abebe Kebede 1 1050.00
C4 Tarik Sisay 2 835.00
C5 Tadesse Belay 1 980.00
16
2. List all employees working in the planning department
(depid=2) and with salary greater than 800
SELECT * FROM employee WHERE depid=2 and salary
> 800
3. Range search condition (BETWEEN/NOT BETWEEN)
SELECT * FROM employee WHERE salary BETWEEN
500 AND 1000
4. Set membership search condition (IN/NOT IN)
SELECT * FROM employee WHERE name IN
abebe‘,‘ABEBE‘,‘Abebe‘)
5. Pattern match search condition (LIKE/ NOT LIKE)
SQL has two special pattern matching symbols:
 % Percent character represents any sequence of zero
or more characters
 _ Underscore character represents any single
character.

17
 All other characters in the pattern represent
themselves.
 For example, address LIKE ‘H%’ the first character
must start with H, but the rest of the string can be
anything.
 Address LIKE ‘H_’there must be exactly four
characters in the string, the first of which must be
an H.
 address LIKE ‘%e’ any sequence of characters, of
length at least 1, with the last character an e.
 address LIKE ‘%ADDIS ABABA%’ a sequence
characters of any length containing ADDIS ABABA.
 address NOT LIKE ‘H%’ the name cannot start
with an H.
SELECT * FROM employee WHERE name LIKE
‘Abebe’
6. Null search condition (IS NULL/IS NOT NULL)
SELECT * FROM employee WHERE empid IS NULL 18
Sorting results (ORDER BY clause)
 In general, the rows of an SQL query result table are not arranged in
any particular order.
 However, we can sort the results of a query using the ORDER BY
clause in the SELECT statement.
 This clause consists of a list of column identifiers that the result is to
be sorted on, separated by commas.
1. List all employee ordered by empname column
SELECT * FROM employee ORDER BY empname
2. List all employee ordered by empname column (in reverse order)
SELECT * FROM employee ORDER BY empname DESC
3. List all employees by empname and those with similar names by
department Id.
SELECT * FROM employee ORDER BY empname ASC, depid ASC
 ASC stands for ascending sort order and DESC stands for reverse
sort order.
Using the SQL Aggregate functions
 The ISO standard defines five aggregate functions:
 COUNT number of values in a specified column
 SUM sum of values in a specified column.
 AVG average of the values in a specified column.
19
 MIN the smallest value in a specified column
 MAX the largest value in a specified column
 where COUNT(*) is a special use of COUNT and it counts all records
(rows) in a table.
 It is important to note that an aggregate function can be used only in the
SELECT list and in the HAVING clause.
 It is incorrect to use it elsewhere. If the SELECT list includes an aggregate
function and no GROUP BY clause is being used to group data together then
no item in the SELECT list can include any reference to a column unless that
column is an argument to an aggregate function.
 For example, the following query is illegal:
SELECT empid, COUNT(salary) FROM employee is illegal.
 because the SELECT list contains both a column name (empid) and a
separate aggregate function (COUNT), without a GROUP BY clause being
used.
 But, the following are correct.
SELECT COUNT (*) AS count FROM employee WHERE depid=2
Count
2
Or
SELECT COUNT (DISTINCT depid) AS count FROM employee Count
3

20
 Count the number of employees and Sum their salaries for those
employees of salary < 600
 SELECT COUNT(empid) AS count, SUM(salary) AS SUM FROM
employee WHERE salary<600
Grouping Results (GROUP BY Clause)
 The above summary queries are similar to the totals at the bottom of
a report.
 A query that uses the GROUP BY is called a grouped query, because
it groups the data from SELECT table(s) and produces a single
summary row for each group.
 The ISO standard requires that the SELECT clause and the GROUP
BY clause be closely integrated.
 When GROUP BY is used, each item in the SELECT line must be
single-valued per group. Further, the SELECT clause may
only contain: column names, aggregate functions, constants and an
expression involving combinations of the above.
 As an example, let us find the number of employees working in each
department and sum of their salaries.
SELECT depid, COUNT (depid) as depid, SUM(salary) as sum FROM
employee GROUP BY depid

21
 The HAVING clause is designed for use with the GROUP BY clause
to restrict the (unwanted) groups that appear in the final result table.
 The ISO standard requires that column names used in the HAVING
clause must also appear in the GROUP BY list or be contained within
an aggregate function.
 In practice, the search condition in the HAVING clause always
includes at least one aggregate function, otherwise the search
condition could be moved to the WHERE clause and applied to
individual rows (Remember that aggregate functions can not be used
with the WHERE clause).
 The HAVING clause is not a necessary part of SQL as any query
expressed using the HAVING clause can always be rewritten without
the HAVING clause.
 As an example, let us display, for each department with more than
one employee in it, find the number of workers and sum of their
salaries. Basically, this question is similar to the example given above.
 What we need to include is a condition that selects those
departments that have only one employee working. So, the above
statement can be rewritten and result displayed as follows:
SELECT depid, COUNT (depid) as depid, SUM(salary) as sum FROM
employee GROUP BY depid
depid count Sum
HAVING COUNT(depid)>1
1 3 2530.45
2 2 1510.99 22
The INSERT INTO Statement
 The INSERT INTO statement is used to insert new
rows into a table.
Syntax
INSERT INTO table_name VALUES (value1,
value2,....)
 You can also specify the columns for which you
want to insert data:
INSERT INTO table_name (column1,
column2,...)VALUES (value1, value2,....)
The Update Statement
 The UPDATE statement is used to modify the data
in a table.
Syntax
UPDATE table_name SET column_name =
new_value WHERE column_name = some_value
23
The DELETE Statement
 The DELETE statement is used to delete rows in a
table.
Syntax
DELETE FROM table_name WHERE column_name =
some_value
Joins and Keys
 Sometimes we have to select data from two or more
tables to make our result complete.
 We have to perform a join.
 Tables in a database can be related to each other with
keys.
 A primary key is a column with a unique value for each
row.
 Each primary key value must be unique within the table.
 The purpose is to bind data together, across tables,
without repeating all of the data in every table.
24
Using Joins
 we can select data from two tables with the JOIN
keyword, like this:
Example INNER JOIN
Syntax
SELECT field1, field2, field3 FROM first_table INNER
JOIN second_table ON first_table.keyfield =
second_table.foreign_keyfield
Example LEFT JOIN
Syntax
SELECT field1, field2, field3 FROM first_table LEFT JOIN
second_table ON first_table.keyfield
=second_table.foreign_keyfield
Example RIGHT JOIN
Syntax
SELECT field1, field2, field3 FROM first_table RIGHT
JOIN second_table ON first_table.keyfield =
second_table.foreign_keyfield
25
Subqueries
 Some SQL statements can have a complete SELECT statement
embedded within them.
 An outer SQL statement can have another SQL statement that is
part of the first SQL statement.
 The results of this inner SELECT or (subselect) statement are used in
the outer statement to help determine the contents of the final result. A
subselect can be used in the WHERE and HAVING clauses of an outer
SELECT statement, where it is called a subquery or nested query.
Subselects may also appear in INSERT, UPDATE and DELETE
statements.
 Following are examples that describe each subquery type.
The general syntax of such nesting looks like:
SQL statement ... WHERE [value operator |keyword1]|[keyword2]
SELECT Column(s) FROM tablename, etc.
 Where SQL statement can be a proper SELECT or INSERT or
another statement; operator can be any relational operator (<,
>, =, etc.) ; keyword1 can be ALL, ANY or SOME and
keyword2 can be EXISTS|NOT EXISTS or IN or NOT IN
 The second SELECT statement is what is referred to as the subquery.

26
Using a subquery with relational operator
1. List the workers who work in the .Finance. department
SELECT empid, empname, salary FROM employee
WHERE depid = (SELECT depid FROM department
WHERE depname=.Finance.)
2. List all workers whose salary is greater than the
average salary
SELECT empid, empname, salary FROM employee
WHERE salary > (SELECT avg(salary) FROM
employee)
3. List all workers working in the .Finance. department
SELECT empid, empname, salary FROM employee
WHERE depid IN (SELECT depid FROM department
WHERE depname=.Finance. )
 The IN operator checks if depid is found inside the
entire resulting table.

27
2. List a worker whose salary is larger than the salary of at least one worker at
the .Finance. department (depid=.1.).
SELECT * FROM employee WHERE salary > SOME (SELECT salary FROM
employee WHERE depid=.1.)
List workers whose salary is larger than the salary of every worker at the .Finance.
department (depid=.1.).
SELECT * FROM employee WHERE salary > ALL (SELECT salary FROM
employee WHERE depid=.1.)
3. List all departments with no employee assigned in them
SELECT * FROM department WHERE NOT EXISTS (SELECT * FROM
employee WHERE department.depid=employee.depid)
 You can see the opposite by dropping the NOT operator of EXISTS.
 The following rules apply to subqueries:
1. The ORDER BY clause can not be used in a subquery (although it may be
used in the outermost SELECT statement)
2. The subquery SELECT must consist of a single column or expression except
for subqueries that use EXISTS
3. By default, column names in a subquery refer to the table in the subquery. It is
possible to refer to a table in FROM clause in an outer query by qualifying
the column name.
4. When a subquery is one of the two operands involved in a comparison, the
subquery must appear on the right hand side of the comparison. For
example, the following is illegal.
SELECT empid, empname, salary FROM employee WHERE (SELECT
avg(salary) FROM employee) > salary is illegal 28
Multitable Queries
 All the examples considered so far are based on a single table. To
combine columns from several tables we need to use the join
operation.
 If we need to obtain information from more than one table,
the choice is between using a subquery and using a join.
 If the final result table is to contain columns from different
tables, then we must use join.
 When joining tables we need to specify a column(s) in which the
join is to be based. However, we can join tables with no common
columns and in such cases the join type is known as cross join.
 In this case all possible pairs of rows from the two tables will
be the product of the join. Commonly, such join is not important.
The other join type is the equi-join or inner join or natural join,
in this
 case, all rows from both tables that have a matching value in the
common columns (in both tables) will be selected. The
 remaining join type is the outer join in which case the rows that
don.t have matching values in the common fields are
 included in the result table. For the unmatched rows each column will
be filled with NULLs. To illustrate the join types
 consider the following tables.

29
Case 1: Cross join of table1 and table2 gives the
following result table.
Table 1 Table 2
Result(cross join)
A B table 1 B table2 C
A1 B1 B1 C1
A1 B1 B2 C2
A1 B1 B3 C3
A2 B1 B1 C1
A2 B1 B2 C2
A2 B1 B3 C3
A3 B2 B1 C1
A3 B2 B2 C2
a3 B2 b3 c3
SELECT * FROM table1 CROSS JOIN table2
This join creates a table that holds 3 (rows from table1) * 3 (rows from
table2) = 9 rows. For large systems such join
may take long time and also consume a lot of space.
30