Access Assignment #1
First Problem: Create a database and a table that contains both text and numeric fields. Since you will be using this table to test queries, be sure to include appropriate data for a variety of tests. Take pictures of the design and the data and include them in your submitted assignment.
Second Problem: Do the following queries. Submit pictures of the design and copy and paste or take pictures of the results.
- Query on one field and also sort on that column/field
- Query on two fields in an AND relationship
- Query on two fields in an OR relationship
- Develop a query using the relationship condition1 and either condition2 or condition3
- Develop a query using the relationship condition1 and condition2 or just condition3
Download/print http://w3.one.net/~jhoffman/sqltut.htm - it is a good tutorial on SQL and you will find it very helpful in doing this assignment. Also, my notes on SQL are out on the Web (they were written for Ingres, not Access, so there may be some slight variations).
Third Problem: Do the following queries using SQL. Submit pictures of the SQL and paste or take pictures of the results.
- Do a conditional select on one table - show all of the fields of data from the record.
- Do a conditional select using AND on one table - show only certain fields of data (be sure to show the data involved in the select). One of the fields should be a character/text field and one of the fields should be numeric.
- Do a conditional select using OR on one table - show only certain fields of data (be sure to show the data involved in the select).
- Do a conditional select using AND and OR on one table - show only certain fields of data. Use the format -- AND (-- OR --). (Be sure to show the data involved in the select).
- Redo the conditional select above without the parenthesis - the format should be -- AND -- OR --.
- Do a conditional using the IN clause.
- Do a conditional using the BETWEEN clause.
- Do a conditional using the LIKE (Note: try the % and the * to represent any possible character).
- Do a conditional using the DISTINCT.