Transcript Introduction to Oracle - UWEC Computer Science Department

Introduction to Databases Queries
CS 146
Sample
Database:
CANDY_CUSTOMER
CUST_ID
CUST_NAME
CUST_TYPE
CUST_ADDR
CUST_ZIP
CUST_PHONE USERNAME PASSWORD
1 Jones, Joe
P
1234 Main St.
91212 434-1231
jonesj
2 Armstrong,Inc.
R
231 Globe Blvd.
91212 434-7664
armstrong
3 Sw edish Burgers
R
1889 20th N.E.
91213 434-9090
sw edburg
4 Pickled Pickles
R
194 CityView
91289 324-8909
pickpick
5 The Candy Kid
W
2121 Main St.
91212 563-4545
kidcandy
6 Waterman, Al
P
23 Yankee Blvd.
91234
w ateral
7 Bobby Bon Bons
R
12 Nichi Cres.
91212 434-9045
bobbybon
8 Crow sh, Elias
P
7 77th Ave.
91211 434-0007
crow el
9 Montag, Susie
P
981 Montview
91213 456-2091
montags
10 Columberg Sw eets W
239 East Falls
91209 874-9092
columsw e
1234
3333
2353
5333
2351
8900
3011
1033
9633
8399
CANDY_PURCHASE
CANDY_CUST_TYPE
PURCH_ID
CUST_TYPE_ID
CUST_TYPE_DESC
PROD_ID
CUST_ID
PURCH_DATE DELIVERY_DATE POUNDS
STATUS
1
1
5
28-Oct-04
28-Oct-04
3.5 PAID
P
Private
2
2
6
28-Oct-04
30-Oct-04
15 PAID
R
Retail
3
1
9
28-Oct-04
28-Oct-04
2 PAID
W
Wholesale
3
3
9
28-Oct-04
28-Oct-04
3.7 PAID
4
3
2
28-Oct-04
5
1
7
29-Oct-04
29-Oct-04
3.7 NOT PAID
5
2
7
29-Oct-04
29-Oct-04
1.2 NOT PAID
5
3
7
29-Oct-04
29-Oct-04
4.4 NOT PAID
6
2
7
29-Oct-04
7
2
10
29-Oct-04
14 NOT PAID
4.8 NOT PAID
CANDY_PRODUCT
PROD_ID
PROD_DESC
PROD_COSTPROD_PRICE
1 Celestial Cashew Crunch
$
7.45
$
10.00
2 Unbrittle Peanut Paradise
$
5.75
$
9.00
3 Mystery Melange
$
7.75
$
10.50
4 Millionaire’s Macadamia Mix
$
12.50
$
16.00
7
5
10
29-Oct-04
5 Nuts Not Nachos
$
6.25
$
9.50
8
1
4
29-Oct-04
8
5
4
29-Oct-04
9
5
4
29-Oct-04
3.7 PAID
3 PAID
29-Oct-04
1 PAID
7.6 PAID
29-Oct-04
3.5 NOT PAID
Basic Database Vocabulary
PROD_ID
Field



PROD_DESC
PROD_COSTPROD_PRICE
1 Celestial Cashew Crunch
$
7.45
$
10.00
2 Unbrittle Peanut Paradise
$
5.75
$
9.00
3 Mystery Melange
$
7.75
$
10.50
4 Millionaire’s Macadamia Mix
$
12.50
$
16.00
5 Nuts Not Nachos
$
6.25
$
9.50
Field: column of similar data values
Record: row of related fields
Table: set of related rows
Record
Sidenote: Database History


All pre-1960’s systems used file-based data
First database: Apollo project



Goal: to not store duplicate data in multiple locations
Used a hierarchical structure
Created relationships using pointers

Pointer: hardware address
Example Hierarchical Database
UniversityStudent
Student
ID
Student
LastName
Student
FirstName
Student
MI
5000
Nelson
Amber
S
5001
Hernandez
Joseph
P
5002
Myers
Stephen
R
Pointers* to Course Data
UniversityCourse
CourseID
Course
Name
Course
Title
100
MIS 290
Intro. to Database
Applications
101
MIS 304
Fundamentals of
Business
Programming
102
MIS 310
Systems Analysis
& Design
*Pointer – physical location (as a number) to the start of the referenced data
Problems with Hierarchical Databases
 Relationships are all one-way; to go the other
way, you must create a new set of pointers
 Pointers are hardware/hard drive-specific
 VERY hard to move to new hardware
 Applications must be custom-written
 Usually in COBOL
Relational Databases

Circa 1972



Store data items only once



E.J. Codd
“Normalizing” relations
With the exception that foreign keys can be duplicated
Stores data in a tabular format
Creates relationships through sharing key fields
Key Fields

Primary key: uniquely identifies a record
UniversityInstructor
Primary keys
InstructorID
Instructor
LastName
Instructor
FirstName
1
Black
Greg
2
McIntyre
Karen
3
Sarin
Naj
Class Discussion


What is the primary key of each table in the CANDY
database?
How can you tell if a field is a primary key?
Special Types of Primary
Keys

Composite PK: made by combining 2 or more fields to
create a unique identifier
o
Consider the CANDY_PURCHASE table…
PURCH_ID

PROD_ID
CUST_ID
PURCH_DATE DELIVERY_DATE POUNDS
1
1
5
28-Oct-04
28-Oct-04
3.5
2
2
6
28-Oct-04
30-Oct-04
15
3
1
9
28-Oct-04
28-Oct-04
2
3
3
9
28-Oct-04
28-Oct-04
3.7
4
3
2
28-Oct-04
2-Nov-04
3.7
5
1
7
29-Oct-04
29-Oct-04
3.7
5
2
7
29-Oct-04
29-Oct-04
1.2
5
3
7
29-Oct-04
29-Oct-04
4.4
8
1
4
29-Oct-04
29-Oct-04
1
8
5
4
29-Oct-04
30-Oct-04
7.6
9
5
3
29-Oct-04
29-Oct-04
3.5
6
2
6
29-Oct-04by the
30-Oct-04
Surrogate
PK:
ID generated
DBMS solely3 as a unique
7
2
10
29-Oct-04
31-Oct-04
14
identifier
7
5
10
29-Oct-04
2-Nov-04
4.8
Key Fields (continued)

Foreign key


Field that is a primary key in another table
Serves to create a relationship
UniversityInstructor
Primary keys
InstructorID
Instructor
LastName
Instructor
FirstName
1
Black
Greg
2
McIntyre
Karen
3
Sarin
Naj
Foreign keys
UniversityStudent
StudentID Student
LastName
Student
FirstName
StudentMI
AdvisorID
5000
Nelson
Amber
S
1
5001
Hernandez
Joseph
P
1
5002
Myers
Stephen
R
3
Class Discussion



What are the foreign keys in the CANDY database?
Does a table HAVE to have foreign keys?
How can you tell if a field is a foreign key?
Alternative to Foreign Keys:


Repeat data values for every record
Problems:


Space
Data becomes inconsistent over time
UniversityStudent
StudentID Student
LastName
Student
FirstName
StudentMI
AdvisorLast
Name
AdvisorFirst
Name
5000
Nelson
Amber
S
Black
Greg
5001
Hernandez
Joseph
P
Black
Greg
5002
Myers
Stephen
R
Sarin
Naj
Rules for Relational Database
Tables (non-negotiable)


Every record has to have a non-NULL and unique PK
value
Every FK value must be defined as a PK in its parent
table
Structure of a Database
Client Workstations
Database Server
DB
Data
DBMS
Database Structure
 A database consists of multiple user accounts
 Your area in the database is called your user schema
 Identified by your username and password
 Each user schema contains database objects that you
create




Tables
Views
Stored programs
Etc.
Query Browser


Example: MySQL SQL query utility
Logging on:




Server Host: stef.cs.uwec.edu
Username: CS320_Student
Password: C4361
Database: CS320_Student
Database Queries
 Query: command to perform an
operation on a database object
 Create
 Insert
 Modify
 View
 Delete
 Structured Query Language (SQL)
 Standard query language for relational
databases
MySQL Query Browser
Type query:
Click Execute:
Query Conventions

Not case-sensitive


Convention: reserved words in all-caps, user-supplied values
(table names, field names, etc.) in lower-case letters
Queries can span multiple lines

Semi-colon marks the end of a line
Retrieving Data From a Single Table

Syntax:
SELECT column1, column2, …
FROM schema.tablename
WHERE search_condition
SELECT candycust_id, candycust_name
FROM candy_customer
WHERE cust_id = 1
Retrieving all Fields or Records

To retrieve all fields in the table: use the "*"
wildcard character
SELECT *
FROM tablename
WHERE search_condition

To retrieve all records in a table: omit the
search condition
How many fields and how many records will
the following query retrieve?
SELECT
A.
B.
C.
D.
*
FROM candy_purchase;
7 fields and 14 records
14 fields and 7 records
7 fields and 9 records
None of the above
Search Conditions

General format:
FieldName Operator TargetValue


Operators: =, <, >, <=, >=, <> or !=
Examples:



PROD_ID = 1
POUNDS > 5
STATUS != 'PAID'
Search Conditions (continued)


Number: just type the number
Text string:



Case-sensitive
Enclose in single quotes
Date:

Enter as a text string in 'yyyy-mm-dd' format:
WHERE purch_date = '2004-10-24'
Which records will the following query
retrieve?
SELECT *
FROM candy_purchase
WHERE pounds >= 5
A.
B.
C.
D.
Purch_id values 2, 3, 5, 7, 8
Purch_id values 2, 7, 8, 9
Purch_id values 2, 7, 8
None of the above
Which records will the following query
retrieve?
SELECT *
FROM candy_purchase
WHERE status = 'Paid'
A.
B.
C.
D.
E.
Purch_id values 1, 2, 3, 4, 6, 8
Purch_id values 5, 7, 9
All purch_id records will be returned
No purch_id records will be returned
An error will occur
Searching for NULL Values

NULL: undefined

Search conditions for NULL and non-NULL values:
WHERE column_name IS NULL
WHERE column_name IS NOT NULL
Combining Multiple Search Conditions
 AND: query only retrieves records for
which both conditions are true
WHERE Condition1 AND Condition2
 OR: query retrieves records for which
either condition is true
WHERE Condition1 OR Condition2
Using AND and OR in Search
Conditions

Every expression must be well-formed:
Do this:
WHERE purch_date > '2004-10-28'
AND purch_date < '2004-11-1'


Not this:
WHERE purch_date > '2004-10-28'
AND < '2004-11-1'
Which records will the following query
retrieve?
SELECT *
FROM candy_purchase
WHERE delivery_date IS NULL
AND status = 'PAID'
A.
B.
C.
D.
Purch_id values 4, 6, 8
Purch_id values 1, 2, 3, 4, 5, 9, 12, 13
Purch_id values 1, 2, 3, 4, 5, 9, 10, 11, 12, 13
None of the above
Which records will the following query
retrieve?
SELECT *
FROM candy_purchase
WHERE delivery_date = NOT NULL
AND status = 'PAID'
A.
B.
C.
D.
Purch_id values 1, 2, 3, 4, 6, 7, 8, 12
Purch_id values 1, 2, 3, 4, 12
Purch_id values 1, 2, 3, 4, 5, 9, 12, 13
None of the above