Chapter 2: Access and SQL
pages 18 - 33
Retrieving Data in SQL
- SELECT ... FROM ...;
- SELECT ... FROM ... WHERE ...;
Standard Logical Operators
Standard Boolean Operators
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
);