Scribd
Upload
36 views30 pages

CSIS 115 Database Design and Applications For Business: Dr. Meg Fryling "Dr. Meg" Fall 2012

csis115Lecture3

Uploaded by

ود نصر
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
36 views30 pages

CSIS 115 Database Design and Applications For Business: Dr. Meg Fryling "Dr. Meg" Fall 2012

csis115Lecture3

Uploaded by

ود نصر
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 30

CSIS 115

Database Design and


Applications for Business

Dr. Meg Fryling


“Dr. Meg” @SienaDrMeg
#csis115
Fall 2012
Agenda
• Questions?
• Announcement: 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.
• Homework
• Review with iClickers
• Chapter 2 (SQL)
– Continued
Homework
• Homework 1
– Was due today by beginning of class!
• Project – Part I
– Due Monday (9/17) by beginning of class
• Continue Reading Chapters 5 and 3 (100-105 only)

1-3
Misc.
• Lecture slides are available in Piazza under
‘Course Page > Resources > Lecture Notes’
• Review lecture slides in ‘Normal’ view instead of
‘Slide Show’ view
• Questions posted in Piazza are eligible for
participation credit!
• You may change your notification frequency to
daily digest (vs. every 4 hours)
– Click on your name (top right) then Account Settings
Let’s do some review from last
week
Get those clickers ready!

1-5
Microsoft Access uses which
type of database model?
a) Relational
b) Hierarchical
c) Object-Oriented
d) Object-Relational
e) XML

1-6
When was this DB model
invented?
a) 1960s
b) 1970s
c) 1980s
d) 1990s
e) 2000+

1-7
Who invented it?
a) E. F. Fryling
b) E. F. Moore
c) E. F. Ratliff
d) E. F. Smith
e) E. F. Codd

1-8
Microsoft Access includes…

A) a DBMS
B) an application generator
C) a Web server
D) A and B
E) A, B and C

1-10
A database stores…

A) data
B) relationships
C) Metadata
D) A and B
E) A, B and C

1-11
SQL Basics

In-Class Activity
Finish Part I Only
Open “SQL Basics.docx”

1-15
The SQL SELECT Statement
• The fundamental framework for SQL query
states is the SQL SELECT statement.
– SELECT {ColumnName(s)}
– FROM {TableName(s)}
• All SQL statements end with a semi-colon
(;).

KROENKE AND AUER - DATABASE PROCESSING, 11th 2-16


Edition © 2010 Pearson Prentice Hall
Create a New Query
• Create > Query Design
• Close the ‘Show Tables’ screen
• Switch to SQL view
– upper left-hand corner
• Type the following into the SQL window (Access
will default to SELECT;):
SELECTDepartment, Buyer
FROM SKU_DATA;
• Click RUN
1-17
Specific Columns on One Table
SELECT Department, Buyer
FROM SKU_DATA;

KROENKE AND AUER - DATABASE PROCESSING, 11th 2-18


Edition © 2010 Pearson Prentice Hall
Modify Query…

…so it return the results listing


buyer first and then department?

1-19
Specifying Column Order
SELECT Buyer, Department
FROM SKU_DATA;

What do you notice about the data returned that’s kind of weird?

KROENKE AND AUER - DATABASE PROCESSING, 11th 2-20


Edition © 2010 Pearson Prentice Hall
DISTINCT Keyword
• Used to eliminate duplicate rows

Select DISTINCT Field1, Field2, Field3


FROM TABLE;

1-21
Modify Query…

…so duplicate rows don’t display

1-22
Modify Query…

…so it returns all columns (fields)

1-23
Selecting All Columns:
The Asterisk (*) Keyword
SELECT *
FROM SKU_DATA;

KROENKE AND AUER - DATABASE PROCESSING, 11th 2-24


Edition © 2010 Pearson Prentice Hall
SQL Basics

In-Class Activity
Part II

1-25
The WHERE Clause
• A query Must have SELECT and FROM
clauses
– Not a super interesting query though
• We can add a WHERE clause to restrict
the rows (records/tuples) that are returned
– Add conditions

1-26
The WHERE Clause
• SQL SELECT statement.
– SELECT {ColumnName(s)}
– FROM {TableName(s)}
– WHERE {Conditions};

1-27
SQL for Data Retrieval:
Comparison Criteria
• The WHERE clause match criteria may
include
– Equals “=“
– Not Equals “<>”
– Greater than “>”
– Less than “<“
– Greater than or Equal to “>=“
– Less than or Equal to “<=“

3-28
Modify Query…

…so it only returns records for the


Water Sports department?

1-29
Specific Rows from One Table
SELECT *
FROM SKU_DATA
WHERE Department = 'Water Sports';
NOTE: SQL wants a plain ASCII single quote: ' NOT ‘ !

KROENKE AND AUER - DATABASE PROCESSING, 11th 2-30


Edition © 2010 Pearson Prentice Hall
Write a New Query
• Selects SKU_Description and Buyer (in
that order) from the SKU_DATA table
• Only include rows for the Climbing
department

1-31
Specific Columns and Rows from
One Table
SELECT SKU_Description, Buyer
FROM SKU_DATA
WHERE Department = 'Climbing';

KROENKE AND AUER - DATABASE PROCESSING, 11th 2-32


Edition © 2010 Pearson Prentice Hall
ORDER BY
• We can control the order of the columns
(fields) in the SELECT but what if we
want to control the order of the records
(sorting)
• SQL SELECT statement.
SELECT {ColumnName(s)}
FROM {TableName(s)}
WHERE {Conditions}
ORDER BY {ColumnName(s)};
1-33
Add ORDER BY
Update the query below so it
sorts the records by
OrderNumber

SELECT *
FROM ORDER_ITEM;

1-34

You might also like