Please see attachment for assignment 

INFO321Week 3 Assignment

Page 1 of 2

Part A SQL Commands (70%)

Read all of the Part 1 instructions before beginning – you need to perform several SQL

commands and capture several display images in this exercise – ensure you capture the entire display

and paste it into a Word document, NOT just the SQL Web site “window” – contact the instructor if

you do not understand these instructions or need guidance on how to capture the entire screen.

Typically, all you need to do is to press the PRTSC key to capture the entire screen, then use Paste to

paste the captured screen into a Word document. Failure to include entire screen displays will result in

a minimum 10-point deduction.

Go to http://sqlcourse.com/create.html , use the links on the left side of the Web site to review Table

Basics, Creating Tables, Inserting into a table, and Drop a table, for the command syntax used by the

Web site. You should also review the Select command instructions (which includes the “Where”

clause) at http://www.sqlcourse2.com/select2.html .

Then use the Standalone SQL interpreter (the link is on the left site of the Web site page) to enter and

submit each SQL command. Use the Interpreter on the sqlcourse.com page – the SQL interpreter on

the sqlcourse2.com page does not seem to work! The site executes one SQL command at a time.

Create a table with the following columns – we are tracking properties that have been rented:

PropertyNum 5 maximum numeric digits (no decimal points)

Street up to 15 characters

City up to 12 characters

State 2 characters

Zip 5 characters

WeeklyRental 6 maximum numeric digits (2 decimal digits)

MonthlyRental 6 maximum numeric digits (2 decimal digits)

AgentNum 5 characters

Tenant up to 14 characters.

The table name should be your last name, plus your student number or another “unique”

number.

Insert the following four records into the table: (ensure the format agrees with the field type)

(40011, 8215 Main St, Denver, CO, 33331, 650.75, 1200, ID102, Johnson)

(50112, 4711 Beacon St, Boston, MA, 60644, 800.50, 1300, ID103, Brown)

(60213, 925 Myrtle Rd, Sarasota, FL, 33385, 450.50, 1100, ID103, Davis)

include another record with different content – you determine the content, EXCEPT it must use ID103

for the AgentNum, and your last name for the Tenant.

NOTE: The SQL INSERT Command syntax on the Web site differs from the text. The SQL Interpreter

requires identification of the columns in the Insert Command to properly execute. Review this

webpage for more specifics of the online SQL Interpreter INSERT Command.

http://sqlcourse.com/insert.html .

Create and Submit SQL commands to accomplish the following:

A) Submit the SQL Create command used to create the table (you also were asked to capture and

include the feedback from the Create table command).

B) Use the Insert command to load the above records (one record at a time is recommended).

C) Use the Select * command to list all fields for all records in the table.

http://sqlcourse.com/create.html

http://www.sqlcourse2.com/select2.html

http://sqlcourse.com/insert.html

INFO321 Week 3 Assignment

Page 2 of 2

D) List the PropertyNum, Street, City, WeeklyRental, MonthlyRental, AgentNum, and Tenant fields for

all properties assigned to agent ID102.

E) List the PropertyNum, Street, City, WeeklyRental, AgentNum, and Tenant fields for all properties

with a weekly rental less than 700

Submit the assignment in a word document under the week’s Assignment. Name your document

Last_Name_SQLWeb (i.e. Smith_SQLWeb).

The submission must include screen captures for:

1) The “create table” feedback display, this will confirm the Table Name and Field Names (A above)

2) The display after submitting one of the Insert commands in B above

3) The display after submitting C above

4) The display after submitting D above

5) The display after submitting E above, also

(For each command, include the SQL statements as text – above each screen capture).

I suggest you use Notepad to create the SQL commands for use with the SQL Interpreter – then

copy the commands into the Word document and include the screen captures for submission.

MS Word may insert control characters that are not visible – they can cause errors.

The SQL interpreter Web site does not provide much error feedback.

The most common error is “Invalid character used in command” – this generally means you have an

incorrect character in the statement; in many cases, you have used an incorrect single quote ‘(typing

a single quote in Notepad seems to work, or copying a single quote from an example on the Web site

works). If the command errors, you may have a syntax error. Also note that table and field names

are case sensitive on this site and should not include spaces.

If you run into trouble, start over by using the Drop Table command to delete your table, and begin

again with the Create Table command.

Part B Access Database (30%)

Create the same table and Queries using MS Access – ensure you create a “new” Access database for

this exercise.

A Create the table (same fields as in Part A)

B Insert the same data as in Part A

C Create and Save the Query created for Item C above with the name Item C

D Create and Save the Query created for Item D above with the name Item D

E Create and Save the Query created for Item E above with the name Item E

Hint – notice that there is no primary key identified – in other assignments we have primary keys –

but for this exercise we do not need to specify a primary key

Name the Access database Last Name_Assignment3 (i.e. Smith_ Assignment3).

Ensure you attach and submit BOTH files