MIS 582 iLab 1 SQL Queries Using Access

BUY MIS 582 iLab 1 SQL Queries Using Access

iLab 1: SQL Queries Using Access

NOTE: Access 2010 is required for this project. Please ensure you can run Access

i L A B O V E R V I E W

Scenario and Summary

This week, you will learn to create and run SQL SELECT queries in the Access database. You will need to

download the Lab_1_SQL.accdb database from Doc Sharing and be able to run Access 2010, either through Citrix or installed on your workstation or laptop.

Deliverables

Rename the Lab_1_SQL.accdb database using your first initial, last name, and assignment number as the file name (e.g., JSmith1.accdb). Create and save your SQL queries in your database. When you are done, submit

i L A B S T E P S

STEP 1

Download the Lab_1_SQL.accdb database from Doc Sharing.

Rename the Lab_1_SQL.accdb database with the following naming convention: Lab1_FirstinitialLastname as the file name (e.g., Lab1_JSmith.accdb).

STEP 2

Using the data in the Student table in the database, create a SQL query to satisfy each of the tasks below.

Save each SQL query using the name associated with the task in the table below.

Name Task

Query1 Write a SQL statement to display Students' First and Last Name.

Query2

Write a SQL statement to display the Major of students with no duplications.

Do not display student names.

Query3

Write a SQL statement to display the First and Last Name of students who live

in the Zip code 88888.

Query4

Write a SQL statement to display the First and Last Name of students who live

in the Zip code 88888 and have the major of Biology.

Query5

Write a SQL statement to display the First and Last Name of students who live

in the Zip code 88888 or 88808. Do not use IN.

Query6

Write a SQL statement to display the First and Last Name of students who

have the major of Biology or Math. Use the SQL command IN.

Query7

Write a SQL statement to display the First and Last Name of students who

have the Status greater than 1 and less than 10. Use the SQL command

BETWEEN.

Query8

Write a SQL statement to display the First and Last Name of students who

have a last name that starts with an S.

Query9

Write a SQL statement to display the First and Last Name of students having

an a in the second position in their first names.

Query10

Write a SQL expression to display the Status and a sum of the Status of each

Status value as SumOfStatus. Group by Status and display the results in

descending order of SumOfStatus.

STEP 3

Upload the following files to the Week 1: Course Project Dropbox.

Lab1_FirstInitialLastName.accdb

Submit your assignment to the Dropbox located on the silver tab at the top

of this page.

(See Syllabus "Due Dates for Assignments & Exams" for due dates.)

Rubrics

Queries that are correct will be awarded the number of points shown below:

6 points: Query 1

7 points: Query 2 - Query 5

8 points: Query 6 - Query 9

9 points: Query 10

The following rubrics will be used for incorrect queries:

0 points: Query was not turned in with the assignment.

-5 points: Query would not run.

-4 points: Query runs but is incorrect because query required a Where clause to meet requirements which was not included.

-3 points: Query runs but is incorrect because Where clause contained errors, gives popup for user input, or only meets partial requirements.