Transcript CSE 2111 Lecture-Querying a Database

Computer Science & Engineering 2111
Querying a Database
CSE 2111 Lecture- Querying a Database
1
What is a Database Query?
• A request for information from a database
• To extract information from the Database you must use a Query
which is a “question” or “request”
– Criterion
• An expression that tells the DBMS which records to retrieve
• Make up of conditions
– Can be one conditions or many
• When you run the query a dynaset, or subset of the database is
displayed.
• You can make changes to this dynaset and the changes will be
reflected in your database, because the dynaset is just a view of
your database.
CSE 2111 Lecture- Querying a Database
2
Query By Example (QBE) Grid
Dynaset created when Query is run
CSE 2111 Lecture- Querying a Database
3
Structured Query Language (SQL)
• SQL is the language you use to talk to the database
• MS Access 2010 supplies a graphical user interface (GUI)
called the Query By Example Grid or (QBE) grid
• MS Access 2010 creates the SQL for you.
CSE 2111 Lecture- Querying a Database
4
SQL Created from QBE Grid
CSE 2111 Lecture- Querying a Database
5
Write a query to list the First Name and Last Name
and state for all clients who live in Ohio.
Query Name:
Ohio Residents
Tables Required:
Client
Foreign Keys:
None
Join Type:
None
Field
FirstName LastName
State
Table
Client
Client
Client
x
x
x
Sort
Show
Criteria
OR
OR
“oh”
When typing in non-numeric criteria, always
surround the criteria with quotes.
CSE 2111 Lecture-Basic Criteria in Queries
6
The data table
The resulting dynaset
CSE 2111 Lecture-Basic Criteria in Queries
7
Write a query to list the ClientID for all
payments of $100.
Query Name:
PaymentOf100
Tables Required:
Payments
Foreign Keys:
None
Join Type:
None
Field
ClientID
Amount
Table
Payments
Payments
Sort
Show
Criteria
OR
OR
x
100
When typing in numeric criteria, DO NOT
surround the criteria with quotes.
CSE 2111 Lecture-Basic Criteria in Queries
8
The data table
The resulting dynaset
CSE 2111 Lecture-Basic Criteria in Queries
9
Write a query to list the Client ID, payment, and
payment date for all payments made on March 8, 2008.
Query Name:
March8
Tables Required:
Payments
Foreign Keys:
None
Join Type:
None
Field
ClientID
Amount
PaymentDate
Table
Payments
Payments
Payments
Sort
Show
Criteria
OR
OR
x
x
x
#3/8/2008#
When typing in date criteria, always surround
the criteria with #.
CSE 2111 Lecture-Basic Criteria in Queries
10
The data table
The resulting dynaset
CSE 2111 Lecture-Basic Criteria in Queries
11
Write a query to list the ClientID and
payment for all payments of $100 or more.
Query Name:
Payment>=100
Tables Required:
Payments
Foreign Keys:
None
Join Type:
None
Field
ClientID
Amount
Table
Payments
Payments
Sort
Show
Criteria
OR
OR
x
x
>=100
When typing in numeric criteria, DO NOT
surround the criteria with quotes.
CSE 2111 Lecture-Basic Criteria in Queries
12
The data table
The resulting dynaset
CSE 2111 Lecture-Basic Criteria in Queries
13
Wild cards in Criteria
An asterisk * replaces any number of characters
• Used with the keyword, Like
– Like “C*” – in Product Name field will select c, Cookie, cake.
– Like “*cookie*” - in Product Name field will select all records that
include the word cookie in the Product Name field
A ? replaces a single character
– Like “B?” – in the Category field will select all records that
consists of two characters and start with the letter “B”
CSE 2111 Lecture-Wild Cards and Key
Words in Queries
14
Write a query to list the First Name and Last Name
for all clients who live within the 614 area code.
Query Name:
Phone#614
Tables Required:
Client
Foreign Keys:
None
Join Type:
None
Field
FirstName LastName
HomePhone
Table
Client
Client
Client
x
x
Sort
Show
Criteria
Like “614*”
OR
OR
CSE 2111 Lecture-Wild Cards and Key
Words in Queries
15
The data table
The resulting dynaset
CSE 2111 Lecture-Wild Cards and Key
Words in Queries
16
Write a query to list the First Name and
Last Name of all clients who DO NOT live
within the 614 area code.
Query Name:
NotPhone#614
Tables Required:
Client
Foreign Keys:
None
Join Type:
None
Field
FirstName LastName
HomePhone
Table
Client
Client
Client
x
x
Sort
Show
Criteria
Not Like “614*”
OR
OR
CSE 2111 Lecture-Wild Cards and Key
Words in Queries
17
The data table
The resulting dynaset
CSE 2111 Lecture-Wild Cards and Key
Words in Queries
18
Write a query to list the Client ID of all clients
whose Client ID consists of 5 characters
beginning with J75 and ending with 0.
Query Name:
J75?0
Tables Required:
Client
Foreign Keys:
None
Join Type:
None
Field
ClientID
Table
Clients
Sort
Show
Criteria
X
Like “j75?0”
OR
OR
CSE 2111 Lecture-Wild Cards and Key
Words in Queries
19
The data table
The resulting dynaset
CSE 2111 Lecture-Wild Cards and Key
Words in Queries
20
Query Name: InnerJoinExample
Tables Required:
Client/Payments
Foreign Keys: ClientID
Join Type:
Inner
Field
ClientID
Table
Client
LastName
Amount
Client
Payments
Sort
Show
x
x
x
Criteria
OR
OR
CSE 2111 Lecture-Inner Joins in Queries
21
The data tables
The resulting dynaset
Dynaset only includes records that have
matching keys on both tables. This is
called an inner join which is the default
join type in Access
CSE 2111 Lecture-Inner Joins in Queries
22
Multiple conditions in a Query
Boolean Operators - AND, OR
For conditions in multiple fields the placement of your arguments
determines the Boolean relationship between those arguments
•
•
If a criteria is on the same line it is automatically considered an AND
If a criteria is on a separate line it is automatically considered an OR
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
23
Write a query to list the First Name, Last Name, and amount
for all clients who paid $250 or more or made payments of
less than $75.
Query
Name:
Example1
Tables
Required:
Client/Payments
Query
Name:
Example2
Tables
Required:
Client/Payments
Foreign
Keys:
ClientID
Join Type:
Inner
Foreign
Keys:
ClientID
Join Type:
Inner
Field
FirstName
LastName
Amount
Field
FirstName
LastName
Amount
Table
Client
Client
Payments
Table
Client
Client
Payments
x
x
Sort
Show
Sort
x
x
x
Show
Criteria
>= 250
Criteria
OR
< 75
OR
OR
x
>= 250 or < 75
OR
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
24
The data tables
The resulting dynaset
25
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
Write a query to list the First
Name, Last Name, amount
and payment date for all
clients who paid more than
$100 on or after 3/3/2008.
Query Name:
AndExample
Tables Required:
Client/Payments
Foreign Keys:
ClientID
Join Type:
Inner
Field
FirstName LastName
Amount
PaymentDate
Table
Client
Client
Payments
Payments
x
x
Sort
Show
Criteria
x
> 100
x
>= #3/3/2008#
OR
OR
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
26
The data tables
The resulting dynaset
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
27
Write a query to list the First Name
and Last Name of all clients who
made payments between 1/1/2008
and 3/8/2008.
Query Name:
BetweenExample
Tables Required:
Client/Payments
Foreign Keys:
ClientID
Join Type:
Inner
Field
FirstName
LastName
PaymentDate
Table
Client
Client
Payments
Sort
Show
Criteria
x
x
x
Between #1/1/2008# And #3/8/2008#
OR
OR
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
28
The data tables
The resulting dynaset
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
29
Write a query to list the First Name,
Last Name, amount and payment
date for all clients who paid more
than $100 on or after 3/3/2008 or
made a payment of $100.
Query Name:
CompoundExample
Tables Required:
Client/Payments
Foreign Keys:
ClientID
Join Type:
Inner
Field
FirstName LastName
Amount
PaymentDate
Table
Client
Client
Payments
Payments
x
x
Sort
Show
x
Criteria
> 100
OR
100
x
>= #3/3/2008#
OR
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
30
The data tables
The resulting dynaset
CSE 2111 Lecture-Multiple and Compound
Criteria in Queries
31