Transcript Querying Database - San Francisco State University

Querying Database
ISYS 363
Basic Query Language Operations
•
•
•
•
Selection
Projection
Join
Aggregates: Max, Min, Sum, Avg, Count
– Totals and SubTotals
– GroupBy
• Calculated fields
Selection
• Selection operation retrieves records that
satisfy user’s criteria.
Criteria
• >, >=, <, <=, =, <>
• Range: BETWEEN 1/1/03 AND 12/31/03
• Wildcard:
– ? – match any one character
• “K?NG”
– * - Match any number of characters
• “C*”
Projection
• Projection operation defines a vertical
subset of a table and retrieves only the
specified fields.
• Example: Customer table: CID, Cname, City, Rating
– Create a query to show CID, Cname, Rating
– Create a query to show CID, Cname, City
Join
• The two tables must have common
attributes:
– Key and foreign key match.
• Combines two tables to form a new table
where records of the two tables are
combined if the common attributes have
the same value.
Join Example
Faculty File:
FID
F1
F2
F5
Student File:
SID
S1
S2
S3
Fname
Chao
Smith
Boxer
Sname
Peter
Paul
Smith
Faculty Join Student =
FID
F1
F2
F1
Join Example
Student File:
SID
S1
S2
S3
Sname
Peter
Paul
Smith
StudentCourse File:
SID
CID
S1
ISYS263
S1
Acct101
S3
ISYS363
S2
ISYS263
S2
Fin350
S2
Acct101
FID
F1
F2
F1
Course File:
CID
Cname
ISYS263 IS Intro
ISYS363 MIS Intro
Acct101 accounting
Fin350 Finance Intro
Units
3
3
3
3
(Student Join StudentCourse) Join Course
Aggregate Functions
• Max, Min, Sum, Count, Avg
• QueryTools/Totals
• Ex. Employee: EmpID,Ename, Sex, Race,
Birthdate, Hiredate, Salary
– How many employees in this company?
– What is the overall average salary?
Aggregates by Group
– Compute the average salary by race.
– Compute the average salary by sex.
– Compute the average salary by Race and
Sex.
– How many employees in each race group?
University Database
ERD Notations
SID
Sname
GPA
Major
1
Student
M
M
Advise
Has
Enroll
SID
1
Balance
Account
M
Grade
1
Faculty
FID
Fname
Course
Phone
CID
Units
Cname
Query Examples
• Q1: Display students’ ID, name and account
balance who owe university more than
$2000.
• Q2: Display student’s ID, name and total
units.
• Q3: Find students taking at least 9 units and
display their ID, Name and total units.
• Q4: Display CID, Cname, SID, Sname
• Q5: Display CID, Cname, number of
students in each course.
• Q6: Display faculty’s name and phone if the
faculty advises at least three students.
Order Processing Database
Query Examples
– Find customers live in San Francisco.
– Produce a customer report that shows CID, Cname,
and Rating.
– Number of customers in each city
• City, NumbeOfCustomers
– Produce a report that shows the number of orders for
each customer:
• CID, Cname, TotalNumberOfOrders
– Total sales by product
– Total amount for each order
– MIS report
– Criteria applied to subtotal
Calculated Fields
• Rename a field:
– NewName:OldName
• Define a calculated field:
– Tax:salary*.15
– Age:Year(Now()) – Year(DOB)
– IIF function
Sorting
• One field sorting
• Two fields sorting
Other Queries
• Update query:
– Query tools/Design/Query type/Update
• Delete query
• Parameter query
• CrossTab query
– Crosstab row
Top n query: Example Top 3 GPA
1. Sort GPA in descending order
2. From Query/Design view, click Property Sheet and select Top Value