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 need to do a query that will show me what items have on hand less than the reorder point.

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.

Problem #7: 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.

Now I want you to try creating a database table using Access:

Create a database using Access. 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 #8: Populate the table with 5records 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 #9: Do a query on the major field to get all CIS majors.

Problem #10: Do a query on the number of courses field to get all students who have taken more than 10 courses.