Introduction to Structured

Query Language (SQL)

Chapter 4
Why SQL?
• SQL is a database query language used for storing and managing data in
Relational DBMS. SQL was the first commercial language introduced for E.F
Codd's Relational model of database. Today almost all RDBMS (MySql,
Oracle, Infomix, Sybase, MS Access) use SQL as the standard database
query language. SQL is used to perform all types of data operations in
RDBMS.

• SQL Statements are understood by MS Access, MS SQL Server, DB2, Oracle,

Sybase, MySQL and others.

• SQL is a Sublanguage that:

• Uses high level, easy to understand statements
• Has a relational Database orientation
• Is portable across wide range of systems
Structures Query Language (SQL)
• Is a standard language for accessing and manipulating databases
• Graphical user interface allows point-and-click queries
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standards
What can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from database
• SQL can insert records in a database
• SQL can delete records from the database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views
SQL DML and DDL
• SQL can be divided into parts;

1. The Data Manipulation Language (DML)

2. The Data Definition Language (DDL)

The Query and Update commands from the DML part of SQL
• SELECT – extracts data from the database
• UPDATE – updates data from the database
• DELETE – deletes data from the database
• INSERT INTO – insert new data into a database
• The DDL part of SQL permits database tables to be created or deleted.
It also defines indexes (keys), specifies links between tables, and
imposes constraints between tables. The most importand 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
• DROP DATABASE - deletes a database
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX – deletes an index
Database tables
• A database most often contains one or more tables. Each table is
identified by a name (e.g. “Customer” or “Orders”). Tables contains
records (rows) with data.
• Below is an example of a table called “tblStudents”
Basic Data Types
1. INT --- Whole Numbers
2. DECIMAL (10,4) --- Decimal Number – Exact Value
3. VARCHAR(30) --- String of Text of length 1
4. BLOB --- Binary Large Object, Stores large
data
5. DATE --- ‘YYYY-MM-DD’
6. TIMESTAMP --- ‘YYYY-MM-DD HH:MM:SS’ – use for
recording time and date
SQL Queries/Command
• A query is a set of instructions given to the RDBMS (written in SQL)
that tells the RDBMS what information you want it to retrieve for you.

Example
SELECT tblstudent.Fname, tblstudent.Lname
FROM tblstudents
WHERE tblstudent.Address = Echague;
Basic SQL Commands
• CREATE DATABASE statement – is use to create a database

Syntax:
CREATE DATABASE database_name;
Example:
CREATE DATABASE dbEnrollmentSystem;
Basic SQL Commands
• CREATE TABLE statement – is use to create a tables in a database.

Syntax:
CREATE TABLE table_name
(
column_name1 datatype,
column_name2 datatype,
column_name3 datatype,
column_name4 datatype
);
Basic SQL Commands
Example:
CREATE TABLE tblStudents
(
IDNo int (6) primary key not null,
Lname varchar (30) default null,
Fname varchar (30) unique default null,
Mname varchar (30) default null,
Bdate date default null,
Address varchar (70) default null
);
Basic SQL Commands
Output:
SQL Constraints
• Constraints are use to limit the type of data that can go into a table.
• Constraints can be specified when table is created (with the CREATE TABLE
statement) or after the table is created (with the ALTER TABLE statement.)

We will only focus on the following constraints:

• NOT NULL - constraint that enforces a column to NOT accept Null values.
• UNIQUE – constraints that uniquely identifies each record in a database table
• PRIMARY KEY – uniquely identify each record in a database table.
• FOREIGN KEY – a key that points to a primary key in another table
• CHECK - constraint is used to limit the value range that can be placed in a column.
• DEFAULT – displays wanted default value if the input is null
MySQL Data Types
MySQL Data Types
MySQL Data Types
Basic SQL Commands
• DROP TABLE statement – use to delete the table

Syntax:
DROP TABLE tbl_name;
Example:
DROP TABLE tblStudents;
Basic SQL Commands
• ALTER TABLE statement – use to add a new column in a table

Syntax:
ALTER TABLE ADD age int(5);
Example:
ALTER TABLE tblstudents ADD age INT(5);
Basic SQL Commands
How to delete Column?

Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Example:
ALTER TABLE tblstudents DROP COLUMN age;
Inserting Data in the Database Table
• INSERT INTO statement can be also use in inserting data that lacks
some column content.

Syntax:
INSERT INTO tbl_name VALUES(‘value1’,’value2’,’value3’);
Example:
INSERT INTO tblStudent VALUES (10101, ‘Desiray’,’Nayga’);
Using Select statement to view Data in the
Database Table

Syntax:
SELECT * FROM table_name;
Example:
SELECT * FROM tblstudents;
Inserting Data in the Database Table
• INSERT INTO statement – use to insert a new row in a table

Syntax:
INSERT INTO tbl_name (column1,column2,column2)
VALUES(‘value1’,’value2’,’value3’);
Example:
INSERT INTO tblStudents VALUES (10101, ‘Desiray’,’Nayga’);
Basic SQL Commands
• SELECT statement – use to select data from the database

Syntax:
SELECT column_name FROM tbl_name;
OR
SELECT * FROM tbl_name;
Example:
SELECT IDNo, Lname, FName FROM tblStudent;
OR
SELECT * FROM tblStudents;

NOTE: SQL in not case sensitive SELECT is the same as select.

Basic SQL Commands
• SELECT DISTINCT statement – use to display different (distinct) values
in a table.

Syntax:
SELECT DISTINCT column1, column2 FROM tbl_name;

Example:
SELECT DISTINCT IDNo, Lname, FName FROM tblStudents

NOTE: SQL is not case sensitive SELECT is the same as select.

Basic SQL Commands
• SELECT DISTINCT statement
Determine the output of SELECT DISTINCT statement;
SELECT DISTINCT City, Country FROM tblcustomer;
Basic SQL Commands
• WHERE clause – use to extract only the records that fulfill a specified
criterion.

Syntax:
SELECT columnName FROM tbl_name WHERE columnName operator
value;

Example:
SELECT Fname, LName FROM tblStudents WHERE IDNo = 10101;
OR
SELECT * FROM tblStudents WHERE Address = ‘Echague’;
Note: You can use single quote on textfield and none for numeric values.
Operators Allowed in WHERE clause
Operators
• AND operator – displays a record if both the first and second condition is
true.

Syntax:
SELECT columnName, columnName FROM tbl_name WHERE
columnName operator value AND columnName operator value;

Example:
SELECT FName, LName FROM tblStudents WHERE Address = Echague
AND IDNo =10101;
Operators
• OR operator – displays a record if either the first condition or the second
condition is true.

Syntax:
SELECT columnName FROM tbl_name WHERE columnName operator
value OR columnName operator value;

Example:
SELECT Fname, LName FROM tblStudents WHERE Address = ‘Echague’
OR IDNo=10102 ;
 Operators
• ORDER BY keyword – used to sort the result-set by specified column.
Sorts record by ascending order by default.

Syntax:
SELECT columnName FROM tbl_name ORDER BY columnName;
Example:
SELECT Fname, LName FROM tblStudents ORDER BY LName
DESC;

Note: DESC for Descending ASC for ascending

 Basic SQL Commands
• UPDATE statement – used to update existing records from the table.

Syntax:
UPDATE tbl_name SET columnName = value, columnName = value2
WHERE columnName =value;
Example:
UPDATE tblStudents SET Address = ‘Alicia’ WHERE IDNo =10102;
OR
UPDATE tblStudents SET Address = ‘Alicia’ WHERE IDNo = 10102 AND
Fname=‘Desiray’;

Note: UPDATE statement can be use to fill-up Null data or change the current value of a column.
 Basic SQL Commands
• DELETE statement – used to delete rows on the table.

Syntax:
DELETE FROM tbl_name WHERE columnName =value;
Example:
DELETE FROM tblStudents WHERE IDNo =10102;
OR
DELETE FROM tblStudents WHERE FName=‘Desiray’ AND LName
=‘Nayga’;

Note: The WHERE clause specifies the record or records that should be deleted. If WHERE clause is omitted then all
records will be deleted.
 SQL Advance Commands
• LIKE operator – is used to search for a specified pattern in a column.

Syntax:
SELECT columnName FROM tbl_name WHERE columnName LIKE
pattern;
Example:
SELECT * FROM tblStudents WHERE Address LIKE ‘a%’;
The example above illustrates that we want to select the tblStudents having an Address that starts with “a” from
the table
SQL Advance Commands
SELECT * FROM tblStudents WHERE Address LIKE ‘s%’;
The example above illustrates that we want to select the tblStudents having an Address that starts with “a” from
the table. The “%” sign can be use to define wildcards (missing letter in the pattern) both before and after the
pattern.
IDNo LName FName MName BDate Address
10101 Castillo Wendell Mercado 1989-04-26 Echague
10102 Nayga Desiray Domael 1990-08-08 Echague
10103 Medrano Jun Michael Nayga 1988-06-12 Alicia

RESULT

IDNo LName FName MName BDate Address

10103 Medrano Jun Michael Nayga 1988-06-12 Alicia

Note: If we want to search for an Address that ends with an “e” then we’ll just have to change our pattern from
‘a%’ to ‘%e’.
SQL Advance Commands
SELECT * FROM tblStudents WHERE Address NOT LIKE ‘%ag%’;
If we are going to search for a particular address that does not have “ag” in the Pattern we’ll just have to add NOT
keyword in the statement.

IDNo LName FName MName BDate Address

10101 Castillo Wendell Mercado 1989-04-26 Echague
10102 Nayga Desiray Domael 1990-08-08 Echague
10103 Medrano Jun Michael Nayga 1988-06-12 Alicia
RESULT

IDNo LName FName MName BDate Address

10103 Medrano Jun Michael Nayga 1988-06-12 Alicia
SQL Advance Commands
• BETWEEN operator – selects a range of data between two values. The
values can be number, text or dates.
Syntax:
SELECT columnName FROM tbl_name WHERE columnName
BETWEEN value1 AND value2;
Example:
SELECT * FROM tblStudents WHERE LName BETWEEN ‘A’ AND ‘M’;
SQL Advance Commands
Example:
SELECT * FROM tblStudents WHERE LName BETWEEN ‘A’ AND ‘M’;
IDNo LName FName MName BDate Address
10101 Castillo Wendell Mercado 1989-04-26 Echague
10102 Nayga Desiray Domael 1990-08-08 Echague
10103 Medrano Jun Michael Nayga 1988-06-12 Alicia

RESULT

IDNo LName FName MName BDate Address

10101 Castillo Wendell Mercado 1989-04-26 Echague
10103 Medrano Jun Michael Nayga 1988-06-12 Alicia
Note: The BETWEEN operator is treated differently in different database.
To display the records outside the range use NOT BETWEEN command.