Structured Query Language (SQL)

- Part 1

Lecture 6

Carlos Coronel, Steven Morris, Peter Rob, Database Systems: Design,

Implementation, and Management, 10th Edition, Cengage Learning
Learning Outcomes
 In this chapter, students will learn:
 The basic commands and functions of SQL
 How to use SQL for data administration (to create tables)
 How to use SQL for data manipulation (to add, modify,
delete, and retrieve data)
 How to use SQL to query a database for useful
information

2
The Database Conceptual Model (Recap!)
Maps Conceptual Model to the DBMS
Introduction to SQL
 Structured Query Language (SQL):
 Non-procedural language (command what to do and not
how)
 SQL functions fit into two broad categories:
 Data definition language (DDL): create database, tables,
indexes, views, trigger, procedure
 Data manipulation language (DML): insert, update,
delete, retrieve data
 American National Standards Institute (ANSI) prescribes
a standard SQL

5
Creating the Database
 Syntax:
 CREATE DATABASE <database_name>

 Example:
 CREATE DATABASE dbMMU

6
Connecting to the Database
 Syntax:
 CONNECT TO <database_name>

 Example:
 CONNECT TO dbMMU

7
Creating Tables
 A database consists of tables (entities)
 Syntax:
 CREATE TABLE <table name>
(<attribute1 name> <attribute1 characteristics>,
<attribute2 name> <attribute2 characteristics>,
primary key <attribute name>,
foreign key <attribute name> );

Table name

CREATE TABLE Faculty

Data type
(
facID char(3),
facName char(50)
);
8 Attribute name
Data Types

Data Type Format

Numeric INTEGER
BIGINT
SMALLINT
DECIMAL(L,D)

Character CHAR(L)
VARCHAR(L)

Date DATE
9
Data Types
 Numeric
-9878149
 E.g., INTEGER
150581

 Integers are (whole) counting numbers

 CANNOT use to store decimal places
 Size: [-231, 231-1]

10
Data Types
 Numeric
123456987034
 E.g., BIGINT
-126992389155

 Up to nineteen digits long

 Size: [-263, 263-1]

11
Data Types
 Numeric
3456
 E.g., SMALLINT
-19531

 Like INTEGER, but limited to integer values up to five

digits
 Size: [-32768, 65535]

12
Data Types
 Numeric
1623.99 
 E.g., DECIMAL(5,2)
88.45

 Smaller lengths are acceptable but greater ones are not

13
Data Types
 Character
Alice
 E.g., CHAR(5)
Tim__

 Fixed-length character data for up to 255 characters.

 If you store strings that are not as long as the CHAR
parameter value, the remaining spaces are left unused

14
Data Types
 Character
George Rob
 E.g., VARCHAR(10)
Tim

 Variable-length character data

 VARCHAR will not leave unused spaces

15
Creating Table Structure: Example

16
 Creating Table Structure: Example

 CREATE TABLE VENDOR (

v_code varchar(5) PRIMARY KEY NOT NULL,
v_name varchar(20),
v_contact varchar(15),
v_areacode integer,
v_phone varchar(10),
v_state char(2), Entity integrity (recap!):
v_order char(1) ) - Primary key cannot
contain NULL value
 Creating Table Structure: Example

 CREATE TABLE PRODUCT (

p_code varchar(10) PRIMARY KEY NOT NULL,
p_descript varchar(50),
p_indate date, Referential integrity rules
p_onhand integer, – see next slide
p_min integer,
p_price decimal(5,2),
p_discount decimal (3,2),
v_code varchar(5),
FOREIGN KEY (v_code) REFERENCES VENDOR )
SQL Constraints
 NOT NULL constraint
 Ensures that column does not accept nulls
 UNIQUE constraint
 Ensures that all values in column are unique
 DEFAULT constraint
 Assigns value to attribute when a new row is added to table
 CHECK constraint
 Validates data when attribute value is entered

19
SQL Constraints
 Example:
 CREATE TABLE STUDENT (
STU_ID INTEGER PRIMARY KEY NOT NULL,
STU_Name VARCHAR(20) NOT NULL UNIQUE,
STU_Gender CHAR(1) CHECK (STU_Gender IN (‘F’, ‘M’)),
STU_Country CHAR (8) DEFAULT ‘MALAYSIA’
)

20
Data Manipulation Commands
 INSERT
 SELECT
 UPDATE
 DELETE

21
Adding Table Rows
 INSERT
 Used to enter data into table
 Syntax:
 INSERT INTO <columnname1,columnname2,…,columnnameN>
VALUES (value1, value2, … , valueN);

 Example:
 INSERT INTO VENDOR VALUES ('21226', 'Bryson Inc.',
'Smithson', 615, '223234', 'TN', 'Y');

 INSERT INTO PRODUCT VALUES (‘SW-20111', Hammer',

‘2001-01-01', 8, 10, 9.00, 1.00, '21226‘, 15, 1);

22
 Adding Table Rows (cont’d.)
 When entering values, notice that:
 Character and date values are entered between apostrophes (')
 Numerical entries are not enclosed in apostrophes
 Attribute entries are separated by commas (,)
 A value is required for each column

INSERT INTO VENDOR VALUES

('21226', 'Bryson Inc.', 'Smithson', 615, '223234', 'TN', 'Y')

V_Code V_CONTACT V_PHONE

V_NAME V_AREACODE V_STATE

23 V_Order
Listing Table Rows
 SELECT
 Used to list contents of table
 Syntax:
 SELECT <columnlist> FROM <tablename>;
 Columnlist represents one or more attributes, separated by
commas
 E.g., SELECT p_code, p_descript, p_indate FROM product;
 E.g., SELECT * FROM product;

Use * to list
all attributes
24
Updating Table Rows
 UPDATE
 Modify data in a table

 Syntax:
 UPDATE tablename
SET <columnname = expression> [, columnname = expression]
[WHERE conditionlist];

 Example:
 UPDATE product
SET p_indate = ‘1996-12-11’
WHERE p_code = ‘12345’
Updating Table Rows
 If more than one attribute is to be updated in row, separate
corrections with commas
 Example:
UPDATE product
SET p_indate = ‘2001-11-01’, p_price = 15.99, p_min = 10

WHERE P_CODE = ‘12345’

Deleting Table Rows
 DELETE
 Deletes a table row

 Syntax:
 DELETE FROM tablename
[WHERE conditionlist ];

 Example:
 DELETE FROM product WHERE p_code = ‘12345’
Deleting Table Rows
 WHERE condition is optional
 If WHERE condition is not specified, all rows from
specified table will be deleted
 Example:
 DELETE FROM product
SELECT Queries
 Fine-tune SELECT command by adding restrictions to
search criteria using:
 Conditional restrictions (=, <=, >=, etc.)
 Arithmetic operators (+, -, /, *)
 Logical operators (AND, OR, NOT)
 Special operators (BETWEEN, EXISTS, etc.)

29
Selecting Rows with Conditional
Restrictions
 Select partial table contents by placing restrictions on
rows to be included in output
 Add conditional restrictions to SELECT statement, using
WHERE clause

 Syntax:
SELECT columnlist
FROM tablelist
[ WHERE conditionlist ]

30
Selected PRODUCT Table Attributes for
VENDOR Code 21344
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344

31
Comparison Operators

32
Selected PRODUCT Table Attributes for
VENDOR Codes Other than 21344
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344

33
Selected PRODUCT Table Attributes
with a P_PRICE Restriction
SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_PRICE <= 10

34
Selected PRODUCT Table Attributes:
The ASCII Code Effect
SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE < ‘1558-QW1’

35
Selected PRODUCT Table Attributes: Date
Restriction
SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE
FROM PRODUCT
WHERE P_INDATE >= ‘2004-01-20’

36
SELECT Statement with a Computed
Column
SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH * P_PRICE
FROM PRODUCT;

37
SELECT Statement with a Computed
Column and an Alias
SELECT P_DESCRIPT, P_QOH, P_PRICE,
P_QOH * P_PRICE AS TOT VALUE
FROM PRODUCT;

38
Arithmetic Operators: The Rule of
Precedence
 Perform operations within parentheses
 Perform power operations
 Perform multiplications and divisions
 Perform additions and subtractions

39
Logical Operators: AND, OR, and NOT
 Searching data involves multiple conditions

 Logical operators: AND, OR, and NOT

 Can be combined
 Parentheses enforce precedence order
 Conditions in parentheses are always executed first

 NOT negates result of conditional expression

40
Selected PRODUCT Table Attributes:
The Logical OR
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344 OR V_CODE = 24288

41
Selected PRODUCT Table Attributes:
The Logical AND
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE < 50 AND P_INDATE > ‘2004-01-15’

42
Selected PRODUCT Table Attributes:
The Logical AND and OR
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE (P_PRICE < 50 AND P_DATE > ‘2004-01-15’)
OR V_CODE = 24288

43
Selected PRODUCT Table Attributes:
The Logical AND and OR
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE NOT V_CODE=21344

44
Special Operators
 BETWEEN
 IS NULL
 LIKE
 IN
 EXISTS

45
Special Operators
 BETWEEN is used to define range limits

 Examples:
 SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 50.00 AND 100.00

 SELECT *
FROM PRODUCT
WHERE P_PRICE >= 50.00 AND P_PRICE <= 100.00

46
Special Operators
 IS NULL is used to check whether an attribute value is
null.

 Examples:
 SELECT P_CODE, P_DESCRIPT,V_CODE
FROM PRODUCT
WHERE V_CODE IS NULL

 SELECT P_CODE, P_DESCRIPT, P_INDATE

FROM PRODUCT
WHERE P_INDATE IS NULL

47
Special Operators
 LIKE is used to check for similar character strings.

 Examples:
SELECT V_NAME, V_CONTACT,V_AREACODE,V_PHONE
FROM VENDOR
WHERE V_NAME LIKE ‘Smith’

SELECT V_NAME, V_CONTACT,V_AREACODE,V_PHONE

FROM VENDOR
WHERE V_NAME LIKE ‘SMITH’

48
Special Operators
 SQL has two special pattern matching symbols:
 % sequence of zero or more characters
 _ (underscore): any single character

 LIKE ‘Smith%’ means a sequence of characters of any length

containing ‘Smith’.

 Examples:
SELECT V_NAME, V_CONTACT,V_AREACODE,V_PHONE
FROM VENDOR
WHERE V_NAME LIKE ‘Smith%’
49
Special Operators
 IN is used to check whether an attribute value matches a
value contained within a (sub)set of listed values.
SELECT *
FROM PRODUCT
WHERE V_CODE IN (21344, 24288)

50
Special Operators
 EXISTS is used to check whether an attribute has value.
SELECT V_CODE
FROM VENDOR
WHERE EXISTS
( SELECT V_CODE
FROM PRODUCT
WHERE VENDOR.V_CODE = PRODUCT.V_CODE
AND P_PRICE >= 50.00 )

51