Week 1: The Database User View – i Lab

Week 1: The Database User View – i Lab
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.
–iqra javaid

Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.

[order_calculator]