Madda Walabu University
College of computing
Department of Information Science
Fundamental of Database
Chapter Seven:
SQL(Structured query language)
Discussion outline
Basic concept of SQL
Data Definition Language
Data Manipulation Language
SQL
• SQL stands for Structured Query Language.
• SQL is a programming language for Relational
Databases. It is designed over relational algebra
and tuple relational calculus. SQL comes as a
package with all major distributions of RDBMS.
SQL, is a standardized computer language that was
originally developed by IBM for querying, altering
and defining relational databases, using declarative
statements.
SQL is not case sensitive.
What can SQL do?
SQL can execute queries against a database.
SQL can retrieve data from a database.
SQL can insert records in a database.
SQL can update records in a database.
SQL can delete records from a database.
SQL can create new databases.
SQL can create new tables in a database.
SQL can create stored procedures in a database.
SQL cont’d…
SQL can create views in a database
SQL can set permissions on tables, procedures,
and views
• SQL commands can be divided into parts based
on their nature: The Data Manipulation
Language (DML) , the Data Definition Language
(DDL) and the Data Control Language (DCL).
Cont’d…
• The query and update commands form the DML
part of SQL:
• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
SQL Data Types for Various DBs
Microsoft Access Data Types
Text, Memo, Integer, Single, Double,
Currency, AutoNumber, Date/Time, Yes/No,
Ole Object, Hyperlink, Byte
SQL sever 2005 some data type
• Text, varchar, int, float, date, char, bit, string,
double, Size limitation
• In both case, limitation of text/number/both give
consideration though Access and SQL have their
limitation.
SQL Constraints
Constraints are used to limit the type of data that
can go into a table.
constraints can be specified when a table is
created(with create table statement) or after the
table is created (with ALTER TABLE statement).
Here are the most important constraints:
PRIMARY KEY
NOT NULL
UNIQUE
FOREIGN KEY
CHECK
DEFAULT
IDENTITY
Cont’d…
PRIMARY KEY
Primary key is the key which uniquely identify each
tuple or record in a database table.
CREATE TABLE Customer(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,);
In the above example the CustomerId identify each
record in Customer table.
Cont’d…
FOREIGN KEY
A foreign key in one table points to a primary key
in another table.
Foreign key prevent action that would destroy
the link b/n the table
and also prevent invalid data from being inserted
into foreign key column
Cont’d…
CREATE TABLE SCHOOL (
SchoolId int IDENTITY(1,1) PRIMARY KEY,
SchoolName varchar(50) NOT NULL UNIQUE,
Description varchar(1000) NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,
PostCode varchar(50) NULL,
PostAddress varchar(50) NULL, )
CREATE TABLE CLASS (
ClassId int IDENTITY(1,1) PRIMARY KEY,
SchoolId int NOT NULL FOREIGN KEY REFERENCES SCHOOL (SchoolId),
ClassName varchar(50) NOT NULL UNIQUE,
Description varchar(1000) NULL, )
Cont’d…
Not Null/Required column
It enforces the column not to accept Null value.
CREATE TABLE [CUSTOMER]
(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,
)
Cont’d…
Check
Used to limit the value range that can be placed in a
column.
Example:
CREATE TABLE [CUSTOMER]
(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE
CHECK(CustomerNumber>0),
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL, )
DDL(Data Definition Language)
The DDL part of SQL permits database tables to be created or
deleted. It also defines indexes (keys), specify links between
tables, and impose constraints between tables. The most
important DDL statements in SQL are:
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
Cont’d…
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
TRUNCATE - remove all records from a table, including all
spaces
DDL SQL commands
Create database
Database is a collection of an organized
information. To create this database we use:
Command- create database
Syntax – create database database_name;
Example to create database customer:
create database customer;
DDL Cont’d…
Create table
A database most often contains one or more tables.
Each table is identified by a name (e.g. "Customers"
or "Orders"). Tables contain records (rows) with
data.
Command: create table
Syntax:
• create table table_name(
column_name1 data_type(size) key constraint,
column_name2 data_type(size),
DDL Cont’d…
Example: create a database EmployeDatabase
and create Employee table in the database with
(EID, Fname Lname, Age, salary) information.
Answer
create database EmployeeDatabase;
create table Employee
(
EID varchar(10) primary key, Result
Fname varchar(20) not null,
Lname varchar(20) not null,
Age int not null,
Salary float(10) not null
);
DDL Cont’d…
Alter Table
• The ALTER TABLE statement is used to add,
delete, or modify columns in an existing table.
Example :
• To add a column in the table the following syntax
is used:
Alter table table_name
Add column_name data type
DDL cont’d…
• To delete column in the table the following syntax is
used:
Alter table table_name
Drop column column _name
• To change the data type of the column in table the
following syntax is used:
Alter table table_name
Alter column column _name data type
DML(Data Manipulation Language)
Insert into statement
Syntax: insert into table_name values(‘vcol1’,
‘vcol2’,…); or
Insert into table_name(col1,col2, …) values(‘Vcol1’,
‘vcol2’, …);
Lets insert the data into the above created
table:
insert into Employee
values('2001c','Estephen','Jerard','26','67493.000');
insert into Employee
values('2002c','Evan','Christin','28','6500.000');
DML Cont’d..
Result (Employee table)
SQL Select statement
• Below is an example of a table called “Employee“
We will work with select statement on this table.
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
• The table above contains four records (one for
each Employee) and five columns (EID, Fname,
Lname, Age, and Salary).
Select statement
The SELECT statement is used to select data
from a database.
The result is stored in a result table, called the
result-set.
SQL SELECT Syntax
SELECT column_name(s)
FROM table_name and
SELECT * FROM table_name
Note: SQL is not case sensitive. SELECT is the
same as select.
Select Statement cont’d…
Example1 Select *
From the above example if we want to display or
select all records in the table we use the following
commands
select * from Employee;
Result EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
NB. The asterisk (*) is a quick way of selecting all
columns!
Select Statement cont’d…
• Now we want to select the content of the
columns named “Fname" and “Lname" from the
table above.
We use the following SELECT statement:
• SELECT Fname,Lname FROM Employee
Result will look like this:
Fname Lname
Estephen Jerard
Evan Christin
Lencho Biyansa
Dibabe Hunduma
Select Statement cont’d…
The SQL SELECT DISTINCT Statement
• In a table, some of the columns may contain
duplicate values. This is not a problem, however,
sometimes you will want to list only the different
(distinct) values in a table.
• The DISTINCT keyword can be used to return
only distinct (different) values.
Now we want to select only the distinct values
from the column named “Age" from the table
above.
Cont’d…
We use the following SELECT statement:
SELECT DISTINCT Age FROM Employee;
The result-set will look like this:
Age
26
28
24
Cont’d…
SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those
records that fulfill a specified criterion or condition.
SQL WHERE Syntax
SELECT *FROM table_name
WHERE column_name operator value;
or
SELECT column_name(s)
FROM table_name
WHERE column_name operator value;
Operators Allowed in the WHERE Clause
• With the WHERE clause, the following operators
can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN
If you know the exact value you want to
return for at least one of the columns
Cont’d…
• WHERE Clause Example
The “Employee" table
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
Now we want to select only the Employee whose
salary is “10000" from the table above.
We use the following SELECT statement:
• SELECT * FROM Employee
WHERE Salary=‘10000‘;
Cont’d…
The result-set will look like this:
EID Fname Lname Age Salary
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
• NB. SQL uses single quotes around text values
(most database systems will also accept double
quotes).
• Although, numeric values should not be enclosed
in quotes.
Cont’d…
Example of correct and incorrect where clause
This is correct:
SELECT * FROM Employee WHERE Fname=‘Dibabe'
This is wrong:
SELECT * FROM Employee WHERE Fname=Dibabe
This is correct:
SELECT * FROM Employee WHERE Age=28
This is wrong:
SELECT * FROM Employee WHERE Age=‘28’
Cont’d…
SQL AND & OR Operators
The AND & OR Operators
• The AND operator displays a record if both the
first condition and the second condition is true.
• The OR operator displays a record if either the
first condition or the second condition is true.
Cont’d…
Example of AND Operator
The “Employee" table:
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
• Now we want to select only the persons with the
Fname equal to “Lencho" AND the Lname equal to
“Biyansa":
• We use the following SELECT statement:
Cont’d…
• SELECT * FROM Employee
WHERE Fname=‘Lencho‘ AND
Lname=‘Biyansa‘;
The
EIDresult set look like
Fname this:
Lname Age Salary
2003c Lencho Biyansa 26 10000
Cont’d…
OR Operator Example
• Now we want to select only the persons with the
Lname equal to “Hunduma" OR the Fname equal
to “Christin":
• We use the following SQL statement:
SELECT * FROM Employee
WHERE Fname=‘Christin‘ OR Lname=‘Hunduma‘;
TheEID
result setFname
look likeLname
this: Age Salary
2002c Evan Christin 28 6500
2004c Dibabe Hundum 24 10000
a
Cont’d…
Combining AND & OR
• You can also combine AND and OR (use
parenthesis to form complex expressions).
• Now we want to select only the persons with the
Fname equal to “Estephen " AND the Lname
equal to “Biyansa" OR to “Jerard":
Cont’d…
We use the following SELECT statement:
SELECT * FROM Employee WHERE
Fname=‘Estephen'
AND (Lname=‘Biyansa' OR Lname=‘Jerard')
• The result-set will look like this:
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
Cont’d…
SQL Like operator
The Like operator is used to search for specified
pattern in the column. We can combine like with
not key word.
Syntax:
Select column_name from table_name
Where column_name like pattern;
Cont’d…
Example : from the above table if we want to select the
Fname wich start with “D” letter we use the following
statement:
Select * from Employee
Where Fname like ‘D%’ ;
EID Fname Lname Age Salary
2004c Dibabe Hunduma 24 10000
• Note! The % sign is used to define the
wildcards(missing letters in a pattern ) both before
and after
Cont’d…
SQL In Operator
The In operator allows you to specify multiple
values in where clause.
Syntax:
Select * from table_name
Where column_name in(value1,value2,…);
Example:
Select * from Employee
Where Fname in(‘Estephen’, ’Lench’);
The result set look like this:
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2003c Lencho Biyansa 26 10000
Cont’d…
SQL Between Operator
The between operator selects a ranges of data
between values.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
Cont’d…
SQL ORDER BY Keyword
• The ORDER BY keyword is used to sort the result-set
by a specified column.
• The ORDER BY keyword sort the records in ascending
order by default.
• If you want to sort the records in a descending order,
you can use the DESC keyword.
SQL ORDER BY Syntax
SELECT * FROM table_name
ORDER BY column_name(s) ASC|DESC
Cont’d…
ORDER BY Example
• The “Employee" table
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
• Now we want to select all the persons from the
table above, however, we want to sort the persons
by their Fname.
We use the following SELECT statement:
SELECT * FROM Employee
ORDER BY Fname
Cont’d…
The result is:
EID Fname Lname Age Salary
2004c Dibabe Hunduma 24 10000
2001c Estephen Jerard 26 67493
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
Cont’d…
SQL UPDATE Statement
• The UPDATE statement is used to update records
in a table.
• The UPDATE statement is used to update existing
records in a table.
• SQL UPDATE Syntax
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
Cont’d…
• Note: Notice the WHERE clause in the UPDATE
syntax. The WHERE clause specifies which
record or records that should be updated. If you
omit the WHERE clause, all records will be
updated!
Cont’d…
Consider The Employee table below:
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
Now we want to update the Employee“Evan, Christin" in the
“Employee" table.
• We use the following SQL statement:
Cont’d…
UPDATE Employee
SET Fname=‘John', Lname=‘Smith'
WHERE EID=‘2002c‘;
The result set will look like this:
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2002c John Smith 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
Cont’d…
SQL DELETE Statement
• The DELETE statement is used to delete records in a
table.
• The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax
DELETE FROM table_name
WHERE some_column=some_value
Note: Notice the WHERE clause in the DELETE syntax.
The WHERE clause specifies which record or records that
should be deleted. If you omit the WHERE clause, all
records will be deleted!
Cont’d…
Consider The Employee table below:
EID Fname Lname Age Salary
2001c Estephen Jerard 26 67493
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
Now we want to delete the person “Estephen" in
the “Employee" table.
We use the following SQL statement:
Cont’d…
DELETE FROM Employee
WHERE EID=‘2001c‘;
The result set will look like this:
EID Fname Lname Age Salary
2002c Evan Christin 28 6500
2003c Lencho Biyansa 26 10000
2004c Dibabe Hunduma 24 10000
Cont’d…
Delete All Rows
• 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:
DELETE FROM table_name
or
DELETE * FROM table_name
Note: Be very careful when deleting records. You
cannot undo this statement!
The End
Any ?
