SQL

Modern Database Management

12th Edition
Jeffrey A. Hoffer, V. Ramesh,
Heikki Topi
Relational Model:

Consists of:
• Data structure
• Tables (relations), rows, columns
• Data integrity
• Mechanisms for implementing business rules that maintain
integrity of manipulated data
• Data manipulation
• Powerful SQL operations for retrieving and modifying data
SQL - Structured Query Language
The standard for relational database management
systems (RDBMS)
History of SQL
• 1970 – E. F. Codd develops relational database concept

• 1974-1979 – System R with Sequel (later SQL) created at IBM Research Lab

• 1979 – Oracle markets first relational DB with SQL

• Current – SQL is supported by most major database vendors but is still not
completely portable among different database systems without
adjustments.
SQL Standard:
Basic Purpose:
• Specify syntax/semantics for data definition and manipulation
• Define data structures and basic operations
• Enable portability of database definition and application modules

Benefits:
• Reduced training costs
• Productivity
• Application portability
• Application longevity
• Reduced dependence on a single vendor
• Cross-system communication
SQL ENVIRONMENT
 An instance of SQL DBMS

 Users and programs that use the DBMS to

access the database

 Each database is contained in a catalog,

which describes any object

 A schema is a collection of related objects

(tables, indexes, … )

 Most companies keep at least two versions

of the database: development and
production

 A testing version may also be kept

Catalog:
A set of schemas that constitute the description of a database

Schema:
The structure that contains descriptions of objects created by a user (base tables, views, constraints)
SQL ENVIRONMENT

DDL - Data Definition Language

DML - Data Manipulation Language

DCL - Data Control Language

 DDL, DML, DCL are used in different stages of the database

development process
Data Manipulation Language
SELECT Statement
 Is the heart of DML

 Is used to fetch data from tables

 Used for queries on single or multiple tables

 Simplest SELECT statement:

SELECT List the columns (and expressions) to be returned from the query
FROM Indicate the table(s) or view(s) from which data will be obtained ;
SELECT Statement

 The SELECT statement is used on relations

The result of any SELECT statement is also a relation!

Example: Using table CUSTOMERS report the names of all customers:

SELECT Customer_First_Name, Customer_Last_Name Returns:

FROM CUSTOMERS ;
SELECT Statement
Clauses of the SELECT Statement:
SELECT
List the columns (and expressions) to be returned from the query
FROM
Indicate the table(s) or view(s) from which data will be obtained
WHERE
Indicate the conditions under which a row will be included in the result
GROUP BY
Indicate categorization of results
HAVING
Indicate the conditions under which a category (group) will be included
ORDER BY
Sorts the result according to specified criteria
Examples:
• Using table Product_T report the names and prices of all the products:

ProductId ProductDescription ProductStandardPrice ProductDescription ProductStandardPrice

1 End Table 175 End Table 175

2 Computer Desc 250 Computer Desc 250
3 Coffee Table 270 Coffee Table 270
4 Dining Table 500 Dining Table 500
Examples:
• Using table Product_T report the names and prices of all the products
with standard price less than $275:

ProductId ProductDescription ProductStandardPrice ProductDescription ProductStandardPrice

1 End Table 175 End Table 175

2 Computer Desc 250 Computer Desc 250
3 Coffee Table 270 Coffee Table 270
4 Dining Table 500
Comparison operators

• Many operators are available, depending on the DBMS

More examples

SELECT OrderID, OrderDate

FROM Order_T
WHERE OrderDate < ’24-Oct-2010’;

SELECT ProductDescription, ProductFinish

FROM Product_T
WHERE ProductFinish !=‘Cherry’;
 Quiz

• Find the Phone numbers of customers who live in OH

• Find the Customers whose First name is alphabetically after ‘Derek’

SELECT Example Using Alias
 Alias is an alternative column or table name

SELECT CUSTOMERNAME, CUSTOMERADDRESS

FROM CUSTOMERS
WHERE CUSTOMERNAME = ‘Home Furnishings’;

CUSTOMERNAME CUSTOMERADDRESS

Home Furnishings 1900 Allard ave

SELECT CUST.CUSTOMERNAME AS NAME, CUST.CUSTOMERADDRESS

FROM CUSTOMERS CUST
WHERE NAME = ‘Home Furnishings’;
NAME CUSTOMERADDRESS

Home Furnishings 1900 Allard ave

Using expressions
• You can use mathematical operations to do calculations:
• Assuming 80% discount, report the final price for the products with
standard price less than $275

SELECT ProductDescription, ProductStandardPrice,

ProductStandardPrice*0.8 AS NewPrice
FROM Product_T
WHERE ProductStandardPrice <275;

ProductDescription ProductStandardPrice NewPrice

End Table 175 140

Computer Desc 250 200

Coffee Table 270 216

Using Functions
ProductDescription ProductStandardPrice
• Use of functions:
End Table 175
o MIN, MAX, COUNT, SUM, ROUND
Computer Desc 250
o LOWER, UPPER, INITCAP, CONCAT
Coffee Table 270
o NEXT_DAY, ADD_MONTHS, MONTHS_BETWEEN
Dining Table 500

SELECT MAX(ProductStandardPrice) answer

FROM Product_T;
500

SELECT sum(ProductStandardPrice) answer

FROM Product_T
WHERE ProductStandardPrice <270 425
Using WILDCARDS
ProductDescription ProductStandardPrice
• Count the rows of a table
End Table 175
Computer Desc 250
Coffee Table 270
Dining Table 500

SELECT count(*) count

FROM Product_T;
4

SELECT count(*) count

FROM Product_T
WHERE ProductStandardPrice <275 3
Using WILDCARDS
• Select values using LIKE
ProductDescription ProductStandardPrice
SELECT ProductDescription, ProductStandardPrice
FROM Product_T End Table 175
WHERE ProductDescription LIKE ‘%Table’ Coffee Table 270
Dining Table 500

ProductDescription ProductStandardPrice
SELECT ProductDescription, ProductStandardPrice End Table 175
FROM Product_T Computer Desc 250
WHERE ProductDescription LIKE ‘%e%’ Coffee Table 270
Dining Table 500
Using Functions
ProductDescription ProductStandardPrice
• Use of functions:
End Table 175
Computer Desc 250
Coffee Table 270
Dining Table 500

SELECT AVG(ProductStandardPrice) answer

FROM Product_T;
299

ProductStandardPrice

175
SELECT ProductStandardPrice 250
FROM Product_T;
270
500
Using Functions
ProductDescription ProductStandardPrice
• Use of functions:
End Table 175
Computer Desc 250
Coffee Table 270
Dining Table 500

SELECT ProductStandardPrice - AVG(ProductStandardPrice)

FROM Product_T; ERROR !!!

Difference
SELECT ProductStandardPrice – PriceAvg AS Difference
-124
FROM Product_T, (SELECT AVG(ProductStandardPrice) AS PriceAvg
-49
FROM Product_T);
-29
201
 Quiz

• What is the average replacement cost?

• What would the new rental_rate be for each movie, if you increase the current one by 50%?

• Which movie titles start with ‘AIR’ ?

• Which movie titles include the string ‘AIR’ ?

Using NULL
ProductID ProductDescription ProductStandardPrice
A null value means that a column is
missing a value; the value is not 1 End Table 175
zero or blank or any special code.
2 Computer Desc 250
3 270
There simply is no value.
4 Dining Table 500

• Select the products for which we do not have a Description:

SELECT ProductId, ProductDescription

FROM Product_T
WHERE ProductDescription IS NULL

ProductID ProductDescription

3
Using NULL
ProductID ProductDescription ProductStandardPrice
A null value means that a column is
missing a value; the value is not 1 End Table 175
zero or blank or any special code.
2 Computer Desc 250
3 270
There simply is no value.
4 Dining Table 500

• Select the products for which we do not have a price:

SELECT ProductId, ProductDescription

FROM Product_T
WHERE ProductStandardprice IS NULL

ProductID ProductDescription
Using NULL
ProductID ProductDescription ProductStandardPrice
A null value means that a column is
missing a value; the value is not 1 End Table 175
zero or blank or any special code.
2 Computer Desc 250
3 270
There simply is no value.
4 Dining Table 500

• Select the products for which we do have a Description:

SELECT ProductId, ProductDescription
FROM Product_T
WHERE Productdescription IS NOT NULL

ProductID ProductDescription

1 End Table
2 Computer Desc
4 Dining Table
Using Function and NULL
ProductID ProductDescription ProductStandardPrice

A null value is a value that 1 End Table 175

does not exist!
2 Computer Desc 250
3 270
4 Dining Table 500

SELECT count(*) SELECT count(ProductDescription)

FROM Product_T; FROM Product_T;

answer answer

4 3
SELECT–Boolean Operators

AND, OR, and NOT Operators for customizing conditions

in WHERE clause

Be careful: By default, processing order of Boolean

operators is NOT, then AND, then OR
28
Boolean query without use of parentheses

AND is processed first:

Tables with price >300
then OR
Desks regardless the price

29
SELECT–Boolean Operators
With parentheses…these override the normal
precedence of Boolean operators

With parentheses, you can override normal precedence rules. In

this case parentheses make the OR take place before the AND.

30
Boolean query with use of parentheses

OR is processed first:
Tables or Desks
then AND
with price >300

31
 Quiz

• Which movie titles have ( replacement_cost = 22.99 or replacement_cost = 21.99)

and rating = PG ?

• Which movie titles have replacement_cost = 22.99

or (replacement_cost = 21.99 and rating = PG )?