ICSI410- Assignment 1 Solved

Download the Access file Asg1.accdb from Blackboard, and then rename the file name from

“Asg1” to your last and first names separated by an underscore. Don’t change the extension “accdb”. For example, mine would be borys_ted.accdb. Insert one row of data into each of the seven tables. You can choose whatever data values you choose with one exception: the one row in the Employee table must contain your first and last name. Then, if you wish, you may add any additional rows in any of the tables at any time to help you debug your query syntax. Next, create 10 separate Access SQL queries, named “Query1” through “Query10”. Each query will contain a SQL statement that is your solution for the translation of a “plain English” request. Each query is worth one point. The 10 “plain English” queries are:

1.                  Display all the columns in Invoice for those rows with an iprice value greater than $10,500.

2.                  Display all the columns in Invoice and sort the output first by ascending ieid and then further sub-sorted by descending iprice.

3.                  Display only the cfirst, cmi, and clast columns in Customer.

4.                  Join Employee and Invoice and display all columns.

5.                  Join Customer, Relationship, and Dealership and display all columns.

6.                  Join Dealership and Employee and display all columns when ehire is greater than 12/31/2017.

7.                  Display all the columns in Vehicle for those rows that have one or more associated rows in Invoice.

8.                  Display all the columns in Employee for those rows that don’t have any associated rows in Invoice.

9.                  Count the number of rows in Vehicle.

10.              Display ieid and the count of the number of rows for each ieid value. Use just the Invoice table.

(You are counting the number of invoices for each employee.)

You are responsible for creating adequate test data to prove to yourself that your queries are working correctly. Your SQL statements must be logically correct and not simply return the “correct” results based on your choice of data. Each query is a single SQL statement (but may include subselects). Do not allow the possibility of duplicate rows in your answer. Use DISTINCT when necessary, but do not specify DISTINCT when it’s not needed. All joins must be done by matching the appropriate columns in the WHERE clause. In other words, you are not to use “INNER, LEFT, or RIGHT JOIN” and “ON” syntax to perform the join operation; you will get no credit for doing this. One-quarter point (0.25) will be deducted for each of the following:

•          Extra / missing distinct / distinctrow

•          Extra / missing simple condition needed to join two tables

•          Extra / missing column in the output

•          Each table that has no rows in it

•          Using a table name prefix for a column that doesn’t need it

•          Any additional error

If a SQL statement has one or more syntax errors, it automatically earns a score of zero. If the execution of a SQL statement generates the “Enter Parameter Value” dialog box, it automatically earns a score of zero. There may be multiple, equally correct solutions for some or all of these problems.