Chapter 2: Access and SQL

Retrieving Data in SQL

• SELECT ... FROM ...;
• SELECT ... FROM ... WHERE ...;

Standard Logical Operators

• =
• >
• <
• >=
• <=
• <>

Standard Boolean Operators

• and
• or
• not

Other Useful Operators

• is null
• is not null
• ... like ...
• between ... and ... (good for text, numeric and date types)
• in (... , etc.)
• order by ... {asc | desc}
• group by ...
• group by ... having ...

Aggregate Functions

• avg()
• count()
• max()
• min()
• sum()

Wildcard *

Multiple Database Queries (Joins)

• SELECT last_name, first_name, grade FROM students, grades WHERE students.student_id = grades.student_id

Exercises

• Be prepared to demo the above features of the SELECT statement to John or Jack in the context of the sales.mdb database.
• Modified Project 2-2 from the textbook. Prepare a text document that contains the SQL query and its results for each of these 6 questions: (1) Total sales for the company. (2) Region with the highest sales - find the region and the amount. (3) Region with the lowest sales - find the region and the amount. (4) Salesperson with the highest sales - find the person and the amount. (5) Salesperson with the lowest sales - find the person and the amount. (6) Average sales - define this and provide relevant data.

Sample Query

SELECT MAX(quarterly_totals) AS max_quarter
FROM  
  (
  SELECT e.region, SUM(s.quarterly_sales) AS quarterly_totals
  FROM employees e, sales s
  WHERE e.employee_no = s.emp_no
  GROUP BY e.region
  ORDER BY SUM(s.quarterly_sales) DESC
  );