Transcript MCLEOD

PROJECT 11
DATABASE QUERIES—
CLASS PROJECTS
DATABASE
Management Information Systems, 9th edition,
By Raymond McLeod, Jr. and George P. Schell
© 2004, Prentice Hall, Inc.
1
Learning Objectives
• Learn to create queries using one or multiple
tables.
• Understand how to limit query results with single
and multiple constraints.
• Understand how a query can request constraint
values from the query user.
• Learn to use queries that look for partial values in
fields.
• Learn to make computations within queries on
both numeric and text fields.
2
Introduction
• This is a companion project to Project 10
• It covers the same learning objectives but
uses a different database example
3
EXAMPLE
1. This example will generate a number of queries
from the ClassProjects database.
2. It is important for decision makers to be able to
generate their own queries because:
3. The decision maker who can create a query gains
immediate access to the power of a database.
4. The decision maker may not know exactly which
records in the database are needed until a query is
generated, its results are seen, and more queries are
created in an iterative process until the results
desired are ultimately generated.
5. An intuitive understanding of the database cannot
occur unless the decision maker gains a hands-on
understanding of the data values and relationships 4
in the database.
ClassProjects Database
• The ClassProjects database consists of three tables
that relate to each other by common values
• The tables and their data fields are shown in
Figure P11.1
• The COURSE table contains three fields; Code,
Description, and Abbreviation. The key fields of
the tables are shown in bold font
• It takes the combination of values from the Code
and Number fields to make a key (i.e., a unique
value) for the PROJECT table
• The Abbreviation field is the only field needed to
be the key of the DEPARTMENT table
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
ASSIGNMENT
1. Create a query using the PROJECT table that shows the code and
number of projects that are worth more than 40 points. Project codes
should be the combination of the Code and Number fields such that
code and number values “FIN305” and “1” become “FIN305, Project
1.” Sort the results by the number of points that the project is worth.
Hint: Figure P.11.25 shows how concatenate fields.
2. Create a query that shows the project code and number of projects
whose points are greater than or equal to 30 and less than or equal to
40. Show only the project code and number, not the field or fields you
use to constrain the results.
3. Create a query that shows the project code and number of projects
whose points are less than 30 or greater than 40. Show only the project
code and number fields in the query results.
4. Create a query that displays all of the MIS classes. These classes will
begin the Code field with “MIS” and you should use an inexact
constraint match. Use the table that contains all the course codes for
this query. Sort in descending order.
38
ASSIGNMENT(cont.)
5. Create a query that finds all senior level classes in the COURSE table based upon
values in the Code field. A senior level class will always have the fourth character
of the Code field be the value “4.”
6. Make a parameter query that asks the user to provide a Code value in the COURSE
table and limit the query results to the Description field value for that course.
7. What courses from the International Business department (the Abbreviation field
value of “INT”) do not have any projects? Only display the course code for
courses that have no projects.
8. For each department, count the number of courses offered by the department. Show
the department name and count of courses. For this query you need two tables.
9. Sum the number of project points for each department (not for each individual
course). The department name and the summed value of points should be
displayed. Sort in descending order by the number of points.
10. Which project is due first from the PROJECT table? Show the code, number, and
title.
39
END OF PROJECT 11
40