INTRODUCTION TO

STRUCTURED QUERY
LANGUAGE (SQL)
Nor Intan Shafini Nasaruddin
College of Computing, Informatics and Mathematics
UiTM Malacca, Jasin Campus
In this chapter, you will learn:

The basic commands and functions of SQL

How to use SQL for data administration (to create

tables, indexes, and views)

How to use SQL for data manipulation (to add, modify,

delete, and retrieve data)

How to use SQL to query a database to extract useful

information
 DML
Data Manipulation Language
 Saving Table Changes
◦ Changes made to table contents are not physically saved on disk until
◦ Database is closed
◦ Program is closed
◦ COMMIT command is used
◦ Syntax
◦ COMMIT [WORK]
◦ Will permanently save any changes made to any table in the database
 Inserting new row

◦ Syntax INSERT INTO EMP

◦ INSERT INTO tablename VALUES (‘101’, ‘News’, ‘John’, ‘G’, ’08-Nov-98’, ‘502’);
VALUES (value1, value2,
value2,…)
INSERT INTO EMP (EmpNo, EmpFName, EmpLName)

OR VALUES (‘102’, ‘Senior’, ‘David’);

◦ INSERT INTO tablename

(column1, column2,
column3, ….)VALUES
(value1, value2, value2,…)
 Updating Table Rows
◦ UPDATE UPDATE EMP
SET EMP_PCT = 3.85
◦ Modify data in a table WHERE EMP_NUM = '103';
◦ Syntax
UPDATE EMP
◦ UPDATE tablename
SET EMP_PCT = 5.00, EMP_INITIAL = ‘G’
SET columnname =
WHERE EMP_NUM = ‘101’;
expression [, columname =
expression]
UPDATE PRODUCT
[WHERE conditionlist];
SET P_SALECODE = ‘1’
◦ If more than one attribute is to WHERE P_CODE = ‘2232/QWE’
be updated in the row, OR P_CODE = ‘2232/QTY’;
separate corrections with
commas
 Deleting Table Rows
◦ DELETE DELETE FROM EMP
WHERE EMP_LNAME = 'Smithfield'
◦ Deletes a table row AND EMP_FNAME = 'William'
◦ Syntax AND EMP_HIREDATE = '22-June-02'
AND JOB_CODE = '500';
◦ DELETE FROM tablename
[WHERE conditionlist ];
◦ WHERE condition is optional
◦ If WHERE condition is not
specified, all rows from the
specified table will be
deleted
 Listing Table Rows
◦ SELECT SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT;
◦ Used to list contents of table
◦ Syntax OR
◦ SELECT columnlist
FROM tablename SELECT *
◦ Columnlist represents one or FROM PRODUCT;

more attributes, separated by

commas
◦ Asterisk can be used as wildcard
character to list all attributes
 Listing Table Rows
◦ SELECT SELECT DISTINCT V_CODE
◦ The SELECT DISTINCT statement is FROM PRODUCT;
used to return only distinct
(different) values.
◦ Syntax
◦ SELECT DISTINCT column1,
column2, …
FROM tablename
◦ Inside a table, a column often
contains many duplicate values;
and sometimes you only want to list
the different (distinct) values.
 Selecting Rows with Conditional Restrictions

◦ Select partial table contents by SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE

placing restrictions on rows to FROM PRODUCT
be included in output WHERE V_CODE = 21344;
◦ Add conditional restrictions
to the SELECT statement,
using WHERE clause
◦ Syntax
◦ SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;
Comparison Operators
 Special Operators
 BETWEEN
Used to check whether attribute value is within a range
 IS NULL
Used to check whether attribute value is null
 LIKE
Used to check whether attribute value matches a given string pattern
 IN
Used to check whether attribute value matches any value within a
value list
 EXISTS
Used to check if a subquery returns any rows
 Arithmetic Operators:
The Rule of Precedence

Perform operations Perform power

within parentheses operations

Perform multiplications Perform additions and

and divisions subtractions
 AND, OR, and NOT operators
 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
are 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.
◦ Syntax
◦ SELECT columnlist
FROM tablelist
WHERE condition1 AND condition1 ;
 The Logical OR

SELECT P_DESCRIPT, P_INDATE, P_PRICE,

V_CODE
FROM PRODUCT
WHERE V_CODE = 21344
OR V_CODE = 24288;
 The Logical AND

SELECT P_DESCRIPT, P_INDATE, P_PRICE,

V_CODE
FROM PRODUCT
WHERE PRICE < 50
AND P_INDATE > ’15 – Jan – 2004’;
 The Logical AND and OR

SELECT P_DESCRIPT, P_INDATE,

P_PRICE, V_CODE
FROM PRODUCT
WHERE (P_PRICE < 50 AND
P_INDATE > ’15 – Jan – 2004’)
OR V_CODE = 24288;
 SELECT Statement with a Computed Column
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND * P_PRICE
FROM PRODUCT;
 SELECT Statement with a Computed Column
and an Alias
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND * P_PRICE AS TOTVALUE FROM PRODUCT;
Summary
◦ SQL commands can be divided into two overall categories:
◦ Data definition language commands
◦ Data manipulation language commands
◦ Basic data definition commands allow you to create tables,
indexes, and views
◦ Many SQL constraints can be used with columns
◦ Aggregate functions
◦ Special functions that perform arithmetic computations over a
set of rows
Summary
◦ ORDER BY clause
◦ Used to sort output of a SELECT statement
◦ Can sort by one or more columns and use either an ascending
or descending order
◦ Join output of multiple tables with SELECT statement
◦ Natural join uses join condition to match only rows with equal
values in specified columns
◦ Right outer join and left outer join used to select rows that have
no matching values in other related table
REFERENCES
◦ Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel
◦ [Link]
◦ [Link]