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.