Transcript csis115Lecture4

CSIS 115
Database Design and
Applications for Business
Dr. Meg Fryling
“Dr. Meg”
Fall 2012
@SienaDrMeg
#csis115
Agenda
• Questions?
• Assignments
• Review
– “Fun” with iClicker
• Chapter 2 - SQL Basics
– Continued
Homework
• Project Part I
– Was due at the start of class today
• Finish Chapter 3 (100-105 only)
• Finish Chapter 5
• Reminder: Quiz 1 will be Mon, 9/24
– Closed book but you may have a 8.5x11
handwritten “cheat sheet”. Must use your own
and turn in with quiz.
1-3
Let’s do some review from last
week
Get those clickers ready!
1-4
The industry standard
supported by all major DBMS
that allows data to be selected,
added, updated, and deleted
from a relational database is…
A) Sequential Query Language (SQL)
B) Structured Question Language (SQL)
C) Structured Query Language (SQL)
D) Relational Question Language (RQL)
E) Relational Query Language (RQL)
1-5
Which elements are required in
an SQL statement
a)
b)
c)
d)
e)
SELECT
FROM
WHERE
SELECT and FROM
SELECT, FROM, and WHERE
1-6
In addition to being a data
sublanguage, SQL is also a
programming language, like
Java or C#
• A) True
• B) False
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
1-7
The SQL keyword SELECT is
used to specify the __________
to be listed in the query results.
•
•
•
•
•
A) Columns
B) Rows
C) Records
D) Tuples
E) None of the above
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
1-8
A column is also referred to as a
field or an attribute
• A) True
• B) False
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
1-9
Add ORDER BY
Update the query below so it
sorts the records by
OrderNumber and then by Price
SELECT *
FROM
ORDER BY
ORDER_ITEM
OrderNumber;
1-11
Sorting the Results – ORDER BY
SELECT *
FROM
ORDER BY
ORDER_ITEM
OrderNumber, Price;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-12
Sort Order Default
Which order are the results sorting in?
a)Ascending
b)Descending
1-13
Sort Order:
Ascending and Descending
SELECT
*
FROM
ORDER_ITEM
ORDER BY Price DESC, OrderNumber ASC;
NOTE: The default sort order is ASC – does not have to be specified.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-14
SQL for Data Retrieval:
Logical Operators
• Multiple matching criteria (conditions) may
be specified using…
– AND
• Representing an intersection of the data sets
– OR
• Representing a union of the data sets
3-15
Write a Query
• Selects all columns from the SKU_DATA
table
• Only include rows for the Water Sports
department where the buyer is Nancy
Meyers
1-16
WHERE Clause Options - AND
SELECT
FROM
WHERE
AND
*
SKU_DATA
Department = 'Water Sports'
Buyer = 'Nancy Meyers';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-17
Write a Query
• Selects all columns from the SKU_DATA
table
• Only include rows that are associated with
the Camping or the Climbing department
1-18
WHERE Clause Options - OR
SELECT
FROM
WHERE
OR
*
SKU_DATA
Department = 'Camping'
Department = 'Climbing';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-19
How many rows will this
query return?
SELECT
FROM
WHERE
AND
*
SKU_DATA
Department = 'Water Sports'
Buyer = 'Nancy Meyers';
Just look at data in SKU_DATA table first to answer this question?
2-20
How many rows will this
query return?
SELECT
FROM
WHERE
OR
*
SKU_DATA
Department = 'Camping'
Department = 'Climbing';
Just look at data in SKU_DATA table first to answer this question?
2-21
Write a Query
• That will return all columns from the
ORDER_ITEM table
• Only include records with an extended
price greater than or equal to 100 and less
than or equal to 200
• Only include records for SKU 101100 or
SKU 101200
• Hint: You will need to use both AND and
OR in the WHERE clause
1-22
Using both OR and AND
SELECT
FROM
WHERE
AND
AND
OR
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200
SKU = 101100
SKU = 101200;
• Does this return what you would expect?
1-23
Remember operator
precedence?
• 3 + 4 × 5 = 23
– vs
• (3 + 4) × 5 = 35
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
1-24
Excel OR() and AND()
Functions
• Recall CSIS114 BI-OLAP Lab
• =IF(OR(A3<62,C3="snow",C3="rain",B3>7
0%,AND(D3="Monday",OR(E4="Average",
E4="Bad"))),"no","yes")
1-25
Using both OR and AND
SELECT
FROM
WHERE
AND
AND
OR
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200
SKU = 101100
SKU = 101200;
• How do you think you can “fix” this query?
1-26
Using both OR and AND
SELECT
FROM
WHERE
AND
AND
OR
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200
(SKU = 101100
SKU = 101200);
1-27
Gotcha
• When using both OR & AND clauses be
sure to use () to clarify what criteria
belongs together
• If not you may get unexpected results!
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
1-28
SQL for Data Retrieval:
Inclusion/Exclusion
• SQL provides a IN/NOT IN statements that
allows a user to specify all valid values on one
line! SWEET
SELECT
FROM
WHERE
FieldName
TABLE
FieldName IN (X,Y,Z);
You can use this instead of _______________
3-29
SQL for Data Retrieval:
The Logical NOT Operator
• Any criteria statement may be
preceded by a NOT operator which
is to say that all information will be
shown except that information
matching the specified criteria
SELECT
FROM
WHERE
FieldName
TABLE
FieldName NOT IN (X, Y, Z);
3-30
WHERE Clause Options - IN
Update query below so it uses IN clause to return
same results
SELECT
FROM
WHERE
AND
AND
OR
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200
(SKU = 101100
SKU = 101200);
2-31
WHERE Clause Options - IN
SELECT
FROM
WHERE
AND
AND
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200
SKU IN(101100, 101200);
Wow, that sure looks cleaner!
What if we wanted to return all SKU
records that were NOT 101100 or
101200 but had an extended price
between 100 and 200?
2-32
WHERE Clause Options – NOT IN
SELECT
FROM
WHERE
AND
AND
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200
SKU NOT IN(101100, 101200);
2-33
SQL for Data Retrieval:
Finding Data in a Range of Values
• SQL provides a BETWEEN statement that
allows a user to specify a minimum and
maximum value on one line! SUPER SWEET
– Note: Will include the minimum and maximum value
in the results
SELECT
FROM
WHERE
FieldName
TABLE
FieldName BETWEEN X AND Y;
3-34
SQL for Data Retrieval:
Finding Data in a Range of Values
Update query below so it uses BETWEEN clause
to return same results
SELECT
FROM
WHERE
AND
AND
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200
SKU IN(101100, 101200);
3-35
WHERE Clause Options –
BETWEEN
SELECT
FROM
WHERE
AND
*
ORDER_ITEM
ExtendedPrice BETWEEN 100 AND 200
SKU IN(101100, 101200);
Huh, that’s awesome!
2-36
SQL Basics
In-Class Activity
Part III
1-37