Basic Query Language Operations
• Aggregates: Max, Min, Sum, Avg, Count
– Totals and SubTotals
• Calculated fields
• Selection operation retrieves records that
satisfy user’s criteria.
• >, >=, <, <=, =, <>
• Range: BETWEEN 1/1/03 AND 12/31/03
– ? – match any one character
– * - Match any number of characters
• Projection operation defines a vertical
subset of a table and retrieves only the
• Example: Customer table: CID, Cname, City, Rating
– Create a query to show CID, Cname, Rating
– Create a query to show CID, Cname, City
• The two tables must have common
– 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.
SID Sname FID
S1 Peter F1
S2 Paul F2
S3 Smith F1
Faculty Join Student =
Student File: StudentCourse File:
SID Sname FID SID CID
S1 Peter F1 S1 ISYS263
S2 Paul F2 S1 Acct101
S3 Smith F1 S3 ISYS363
CID Cname Units
ISYS263 IS Intro 3
ISYS363 MIS Intro 3
Acct101 accounting 3
Fin350 Finance Intro 3
(Student Join StudentCourse) Join Course
• Max, Min, Sum, Count, Avg
• 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
– How many employees in each race group?
Sname Major GPA SID Balance
Student Has Account
FID Fname CID Cname
• Q1: Display students’ ID, name and account
balance who owe university more than
• Q2: Display student’s ID, name and total
• 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.
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
Order Processing Database
– Find customers live in San Francisco.
– Produce a customer report that shows CID, Cname,
– Number of customers in each city
• City, NumbeOfCustomers
– Produce a report that shows the number of orders for
• CID, Cname, TotalNumberOfOrders
– Total sales by product
– Total amount for each order
– MIS report
– Criteria applied to subtotal
• Rename a field:
• Define a calculated field:
– Age:Year(Now()) – Year(DOB)
– IIF function
• One field sorting
• Two fields sorting
• Update query:
– Query tools/Design/Query type/Update
• Delete query
• Parameter query
• CrossTab query
– Crosstab row