CIT12
- Problems:
I
have a file that contains information about the books that I have on software
and programming.
The
layout of the file is:
ISBN # of Book
Title of Book
Primary Author of Book
Topic
Year Published
Here
are a couple of records that I have on the file:
ISBN
# |
Title |
Primary
Author |
Topic |
Year
Published |
0-619-03440-8 |
CGI/Perl |
Zak,
Diane |
CGI/Perl |
2002 |
0-13-100277-5 |
Oracle SQL |
Rischert, Alice |
Oracle |
2003 |
0-619-03520-X |
Cascading
Style Sheets |
Sklar,
Joel |
CSS |
2001 |
1-5927-3000-0 |
Developing
Online Games |
Mulligan,
Jessica |
Games |
2003 |
0-201-74205-5 |
Visual
Basic.Net for Students |
Bell,
Douglas |
VB.NET |
2003 |
0-619-06475-7 |
Oracle
9i: SQL |
Morris-Murphy,
Lannes |
Oracle |
2003 |
I
am going to create this file in Access, so we can test it to see what data can
be gotten from the file. You should
view the Power Point Presentation dealing with this assignment and look at the
Access database called asgn1.mdb.
Problem
#1: I need to do a query to list of all
books with their title, primary author and ISBN#. Does this file have the data/information available that would
allow me to do this query?
Problem
#2: I need to do a query to list all
books that were published in 2003. Does
this file have the data/information available that would allow me to do this
query?
Problem
#3: I need to do a query to list all
books published by Course Technology.
Does this file have the data/information available that would allow me to
do this query?
Problem
#4: I need to do a query to list all books with their title, edition, and topic
where the topic is CGI/Perl. Does this
file have the data/information available that would allow me to do this query?
For
the next few problems, I want you to tell me what fields need to be on the
table/file that you create if you want to be able to do a query and retrieve
the specific information requested.
Problem
#5: In my inventory system, I will have a table about products. I know that I need to do a query that will
show me what items have on hand less than the reorder point. Clearly I need to
be able to identify the product as well to do this query. What fields should be on the table to enable
me to do the query?
Problem
#6: In my inventory system, I need to
do a query that will show me all the items where the price is more than 50
dollars. What field or fields should I
put on the table to be able to do this query?
Problem
#7: In my inventory system, I need to
do a query that will show me all items where the price minus the cost is
greater than 10 dollars. What field or
fields should I put on the table to be able to do this query.
Problem
#8: In my car insurance system, I want
to give all people with a rating less than 4 a discount off their premium of 25
dollars. When I show the results of the
query I want to identify the customer, the car and the value of the car. What field or fields should I put on my insurance
car table to be able to do this query?
Problem
#9: If you wanted to create a table/file that contained records about your
relatives to create a family tree, what fields would you want to put in the
table/file? Think about the questions
you would want to ask like where was the relative born? Make a list of the fields you would include
in the table.
Access:
Create
a database using Access. When you
create this table, I want you to have no embedded spaces in the field name, I
want you to identify the student identification number as the primary key and I
want you to set up a reasonable type and length for each field. Create a table
of student information with the following information on the table:
Student
identification number
Student
name
Student
street address
Student
city
Student state
Student
zip
Student
major
Student
option
Number
of courses the student has taken
Problem
#10: Populate the table with 5 records and information that will allow you to
do queries and retrieve multiple records.
Look at the queries I am going to ask you to do before entering the data
Problem
#11: Do a query on the major field to get all CIS majors.
Problem #12: Do a query on the number of courses field to get all students who
have taken more than 10 courses.