Introduction to Structured Query Language SQL

1
Chapter 7

• Introduction to Structured Query Language (SQL)
• Database Systems Design, Implementation, and
  Management, Sixth Edition, Rob and Coronel

2
In this chapter, you will learn

• The basic commands and functions of SQL
• How to use SQL to query a database to extract
  useful information

3
Introduction to SQL

• SQL functions fit into two broad categories
• Data definition language
• SQL includes commands to create
• Database objects such as tables, indexes, and
  views
• Commands to define access rights to those
  database objects
• Data manipulation language
• Includes commands to insert, update, delete, and
  retrieve data within the database tables

4
Introduction to SQL (continued)

• SQL is relatively easy to learn
• Basic command set has a vocabulary of less than
  100 words
• Nonprocedural language
• American National Standards Institute (ANSI)
  prescribes a standard SQL
• Several SQL dialects exist

5
Data Manipulation Commands
6
Data Manipulation Commands

• Adding table rows
• Saving table changes
• Listing table rows
• Updating table rows
• Restoring table contents
• Deleting table rows
• Inserting table rows with a select subquery

7
Common SQL Data Manipulation Commands
8
A Data View and Entry Form
9
Listing Table Rows

• SELECT
• Used to list contents of table
• Syntax
• SELECT columnlistFROM tablename
• Columnlist represents one or more attributes,
  separated by commas
• Asterisk can be used as wildcard character to
  list all attributes

10
Selecting Rows with Conditional Restrictions

• Select partial table contents by placing
  restrictions on rows to be included in output
• Add conditional restrictions to the SELECT
  statement, using WHERE clause
• Syntax
• SELECT columnlistFROM tablelist WHERE
  conditionlist

11
Selected PRODUCT Table Attributes for VENDOR Code
21344
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE 21344
12
The Microsoft Access QBE and its SQL
13
Comparison Operators
14
Selected PRODUCT Table Attributes for VENDOR
Codes Other than 21344
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE ltgt 21344
15
Selected PRODUCT Table Attributes with a P_PRICE
Restriction
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM
PRODUCT WHERE P_PRICE lt 10
16
Selected PRODUCT Table Attributes Date
Restriction
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE,
P_INDATE FROM PRODUCT WHERE P_INDATE gt 20 Jan
2004
17
SELECT Statement with a Computed Column
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND
P_PRICE FROM PRODUCT
18
SELECT Statement with a Computed Column and an
Alias
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND
P_PRICE AS TOTVALUE FROM PRODUCT
19
Arithmetic Operators The Rule of Precedence

• Perform operations within parentheses
• Perform power operations
• Perform multiplications and divisions
• Perform additions and subtractions

20
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
21
Selected PRODUCT Table Attributes The Logical
AND
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE PRICE lt 50 AND P_INDATE gt 15 Jan
2004
22
Selected PRODUCT Table Attributes The Logical
AND and OR
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE (P_PRICE lt 50 AND P_INDATE gt 15
Jan 2004) OR V_CODE 24288
23
Selected PRODUCT Table Attributes Ordered by
(Ascending) P_PRICE
SELECT P_CODE, P_DESRIPT, P_INDATE, P_PRICE FROM
PRODUCT ORDER BY P_PRICE
24
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
• Data manipulation commands includes commands to
  insert, update, delete, and retrieve data within
  the database tables