Transcript Chapter 3

Concepts of Database Management
Seventh Edition
Chapter 3
The Relational Model 2: SQL
Objectives
•
•
•
•
•
Introduce Structured Query Language (SQL)
Use simple and compound conditions in SQL
Use computed fields in SQL
Use built-in SQL functions
Use subqueries in SQL
2
Objectives (continued)
•
•
•
•
•
Group records in SQL
Join tables using SQL
Perform union operations in SQL
Use SQL to update database data
Use an SQL query to create a table in a database
3
Introduction
• SQL (Structured Query Language)
– Allows users to query a relational database
– Must enter commands to obtain the desired results
– Standard language for relational database
manipulation
4
Getting Started with SQL
• If you are completing the work in this chapter using
Microsoft Office Access 2007, Microsoft Office
Access 2010, or MySQL version 4.1 or higher, the
following sections contain specific information
about your DBMS
5
Getting Started with Microsoft Office
Access 2007 and 2010
• If you are using the Access 2007 or 2010 version of
the Premiere Products database provided with the
Data Files for this text:
– Tables in the database have already been created
– You will not need to execute the CREATE TABLE
commands to create the tables or the INSERT
commands to add records to the tables
6
Getting Started with Microsoft Office
Access 2007 and 2010 (continued)
• To execute SQL commands shown in the figures in
Access 2007 or Access 2010:
–
–
–
–
–
Open the Premiere Products database
Click the Create tab on the Ribbon
Click the Query Design button in the Other group
Click the Close button in the Show Table dialog box
Click the View button arrow in the Results group on
the Query Design Tools tab, then click SQL View
– The Query1 tab displays the query in SQL view,
ready for you to type your SQL commands
7
Getting Started with MySQL
• MySQL-Premiere script provided with the Data
Files for this text will:
– Activate the database
– Create the tables
– Insert the records
• To run a script in MySQL:
– Type the SOURCE command followed by the name
of the file
– Press the Enter key
8
Getting Started with MySQL
(continued)
• Before typing commands in MySQL, you must
activate the database by typing the USE command
followed by the name of the database
• The most recent command entered in MySQL is
stored in a special area of memory called the
statement history
9
Table Creation
• SQL CREATE TABLE command
– Creates a table by describing its layout
• Typical restrictions placed on table and column
names by DBMS
– Names cannot exceed 18 characters
– Names must start with a letter
– Names can contain only letters, numbers, and
underscores (_)
– Names cannot contain spaces
10
Table Creation (continued)
• INTEGER
– Number without a decimal point
• SMALLINT
– Uses less space than INTEGER
• DECIMAL(p,q)
– P number of digits; q number of decimal places
• CHAR(n)
– Character string n places long
• DATE
– Dates in DD-MON-YYYY or MM/DD/YYYY form
11
Simple Retrieval
• SELECT-FROM-WHERE: SQL retrieval command
– SELECT clause: lists fields to display
– FROM clause: lists table or tables that contain data
to display in query results
– WHERE clause (optional): lists any conditions to be
applied to the data to retrieve
• Simple condition: field name, a comparison
operator, and either another field name or a value
12
Simple Retrieval (continued)
• Retrieving all fields and all records
– Syntax: SELECT * FROM <table>
– Example 1: SELECT * FROM Customer
– Example 2: SELECT * FROM Rep
• Retrieving specific field(s)
• Syntax: SELECT <field1>, <field2>,..<fieldn> FROM
<table>
• Example 1: SELECT Customername, City, State FROM
Customer
• Example 2: SELECT Repnum, Lastname, Firstname,
Commission, Rate FROM Rep
13
Non-Graded Exercise
1. Show all the fields and records from Rep table
2. Show all fields and records Part table
3. Show the Last name and First name of all Sales
Rep from Rep table
4. Show the Part number, Description, On Hand
Quantity and Price from Part table
14
Simple Retrieval – Filtering Records
• Filtering records to retrieve using WHERE clause
– Syntax: SELECT * FROM <table>
– Example 1: (Filtering records with a Text/String field)
SELECT * FROM Customer WHERE City=‘Grove’
– Example 2: (Filtering records with a Numeric field)
SELECT * FROM Customer WHERE CreditLimit=7500
– Example 3: (Filtering records using specific fields)
SELECT Customername, City WHERE City = ‘Grove’
– Example 4: (Filtering records using not equal)
SELECT * FROM City WHERE City <> ‘Fillmore’
– Note : If it is a string or a text the value should have
a quote like in ‘Grove’ but if it is numeric do not put a
quote.
15
Simple Retrieval (continued)
FIGURE 3-6: SQL query with WHERE condition
16
Simple Retrieval (continued)
FIGURE 3-7: Query results
17
Relational Operators
Operator
Description
Example
>
Greater Than
Age>20
<
Less than
Age<21
=
Equal
City=‘Grove’
>=
Greater than or Equal
CreditLimit >= 10000
<=
Less than or Equal
Balance<=5000
<>
Not Equal
City <> ‘Grove’
Date Comparisons
Database
Example
MS Access
OrderDate = #10/21/2013#
MySQL
OrderDate = DATE(‘2013-10-21’)
18
Non-Graded Exercise
1. Show all Customers that comes from the City of
Altonville from Customer table
2. Show all products from Part table whose class is
AP
3. Show all Customers whose Balance is greater than
7000 from Customer table
4. Show all Customers whose Credit Limit is above or
equal to 7500 from Customer table
5. Show all Customers whose balance is less than
500 from Customer table
6. Show all orders from Orders table whose order is
beyond 10/20/2013
19
Compound Conditions
• Compound condition
– Connecting two or more simple conditions using one
or both of the following operators: AND and OR
– Preceding a single condition with the NOT operator
• Connecting simple conditions using AND operator
– All of the simple conditions must be true for the
compound condition to be true
• Connecting simple conditions using OR operator
– Any of the simple conditions must be true for the
compound condition to be true
20
AND – Truth Table
• In order for AND to be true all
conditions/expressions should be true
Condition 1
Condition 2
Result
False
False
False
False
True
False
True
False
False
True
True
True
21
AND – Truth Table (Example)
• Example : Age>20 AND State=‘Pohnpei’
Age
State
Result/Answer
20
Chuuk
False
21
Yap
False
19
Pohnpei
False
21
Pohnpei
True
22
OR – Truth Table
• In order for OR to be True at least one
conditions/expressions should be true
Condition 1
Condition 2
Result
False
False
False
False
True
True
True
False
True
True
True
True
23
OR – Truth Table (Example)
• Example : Age>20 OR State=‘Pohnpei’
Age
State
Result/Answer
20
Chuuk
False
21
Yap
True
19
Pohnpei
True
21
Pohnpei
True
24
Compound Conditions (continued)
FIGURE 3-15: Compound condition that uses the AND operator
FIGURE 3-16: Query results
25
Compound Conditions (continued)
FIGURE 3-17: Compound condition that uses the OR operator
FIGURE 3-18: Query results
26
Non-Graded Exercise
1. Show all products from Part table whose class is
AP and its price is above $400
2. Show all Customers that comes from either the City
of Fillmore or Grove from Customer table
3. Show all Customers whose Credit Limit is $7,500
but its Balance is above $5,000 from Customer
table
4. Show all products whose warehouse is from either
1 or 2
5. Show all products whose On hand quantity is
above 20 and coming from Warehouse no. 3
27
Compound Conditions (continued)
• Preceding a condition by NOT operator
– Reverses the truth or falsity of the original condition
• BETWEEN operator
– Value must be between the listed numbers
28
Compound Conditions (continued)
• Using NOT operator
– Example 1: SELECT * FROM Customer WHERE
NOT City=‘Grove’
– Example 2: SELECT * FROM Customer WHERE
NOT CreditLimit=10000
• Using BETWEEN operator
– Example 1: SELECT * FROM Customer WHERE
Balance BETWEEN 5000 AND 10000
– Example 2: SELECT * FROM Orders WHERE
Orderdate BETWEEN #10/20/2013# AND
#10/21/2013
29
Non-Graded Exercise
1. Show all products from Part whose Class is not
from AP
2. Show all products from Part whose Warehouse is
not from Warehouse number 3
3. Show all customers from Customer whose sales
representative is not Repnum no. 65
4. Show all products from Part whose Price is from
$300 to $600.
5. Show all products from Part whose On hand
quantity is from 20 to 50
30
Computed Fields
• Computed field or calculated field
– Field whose values you derive from existing fields
– Can involve:
•
•
•
•
Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
31
Computed Fields (continued)
• Numeric Computed Fields
– Example 1: Computing the Net by deducting Credit Limit from Balance
SELECT Customername, (Balance-CreditLimit) as
Net FROM Customer
– Example 2: Computing the Running Total by Multiplying On hand quantity
with Price
SELECT Partnum, Description, OnHand * Price as
RunningTotal FROM Part
• Non-numeric Computed Field
– Example 1: Concatenating or Joining Last name and First
name of the
Sales as Full Name
SELECT Repnum, Lastname + “, “ + Firstname as
Fullname FROM Rep
32
Computed Fields (continued)
FIGURE 3-25: SQL query with a computed field and condition
FIGURE 3-26: Query results
33
Non-Graded Exercise
1. Show customer name and a computed field name
Net using the formula CreditLimit – Balance from
Customer table.
2. Get the Running Total from each order by
multiplying Number Ordered and Quoted Price of
the Orderline table. Aside from the Running Total
show also the Order Number.
3. Get the Running Total per product from Part table
by multiplying Quantity on Hand by price. Show
also the Description of the product.
4. Make a concatenated field for Street, City, State
and Zip fields and named it Address, from
Customer table. Show also the Customer name
before the address.
34
Using Special Operators (LIKE and IN)
• Wildcards in Access SQL
– Asterisk (*): collection of characters
– Question mark (?): any individual character
• Wildcards in MySQL
– Percent sign (%): any collection of characters
– Underscore (_): any individual character
• To use a wildcard, include the LIKE operator in the
WHERE clause
• IN operator provides a concise way of phrasing
certain conditions
35
Using LIKE operator and wildcards
• Using asterisk for collection of characters pattern
– Example 1: Displays customer whose name starts with letter B
SELECT * FROM Customer WHERE customername LIKE ‘B*’
– Example 2: Displays customer whose name starts with the word Brook
SELECT * FROM Customer WHERE customername LIKE
‘Brook*’
• Using question mark for individual/single character
– Example 1: Display orders whose partnum starts with letter K followed by any 3
characters.
SELECT * FROM Orderline WHERE partnum LIKE ‘K???’
– Example 2: Display product whose class startswith letter A
followed by one character.
SELECT * FROM Part WHERE class LIKE ‘A?’
36
Using the IN operator
– Example 1: Displays customer whose credit limit is either $5,000 or $10,000 or
$15,000
SELECT * FROM Customer WHERE creditlimit
IN(5000,10000,15000)
– Example 2: Displays customer who comes from the City of Grove, Crystal, Fillmore
or Northfield
SELECT * FROM Customer WHERE city
IN(‘Grove’,’Crystal’,’Fillmore’,’Northfield’)
37
Sorting
• Sort data using the ORDER BY clause
• Sort key: field on which to sort data
• When sorting data on two fields:
– Major sort key (or primary sort key): more important
sort key
– Minor sort key (or secondary sort key): less
important sort key
38
Using the ORDER BY statement
– Example 1: Displays customer sorted by customer name
SELECT * FROM Customer ORDER BY customername
– Example 2: Displays sales rep sorted by Lastname then Firstname
SELECT * FROM Rep ORDER BY Lastname, Firstname
– Example 3: Displays sales rep sorted by Lastname in descending order
SELECT * FROM Rep ORDER BY Lastname DESC
– Example 4: Displays customer sorted by City whose Credit Limit is $10,000
SELECT * FROM Customer WHERE CreditLimit = 10000
ORDER BY city
39
Built-in Functions
• Built-in functions (aggregate functions) in SQL
– COUNT: calculates number of entries
– SUM or AVG: calculates sum or average of all
entries in a given column
– MAX or MIN: calculates largest or smallest values
respectively
40
Using Aggregate Functions (COUNT,
SUM, AVG, MAX, MIN)
– Example 1: Count all how many customers in Customer table
SELECT COUNT(*) as CustomerCount FROM Customer
– Example 2: Count how many customers from the City of Grove
SELECT COUNT(*) as CountGrove FROM Customer
WHERE city = ‘Grove’
– Example 3: Sum all Balance from all Customers
SELECT SUM(Balance) as TotalBalance FROM Customer
– Example 4: Sum all Balance from all Customers for those who a CreditLimit of
$5,000
SELECT SUM(Balance) as TotalBalance FROM Customer
WHERE CreditLimit=5000
41