There are 2 parts to this week’s assignment. (The detailed instructions are attached) Part A you will use a Web site to build and execute SQL commands. The Web site provides descriptions and examples of the SQL commands. You will create a table, enter data into that table, and use Select commands to create queries. I suggest you use Notepad to develop the SQL commands AND then a Word document for the submission. For Part 1 - you need to submit ONE Word document, which includes the SQL commands, and display captures of the Web site’s feedback. Hints and Common errors when using the SQL Web site can be found in the Terms and Concepts Forum. Part B – use MS Access to create the same tables, content and QueriesPart 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, Jones) (50112, 4711 Beacon St, Boston, MA, 60644, 700.50, 1300, ID103, Jones) (60213, 925 Myrtle Rd, Sarasota, FL, 33385, 450.50, 1100, ID103, Doe) 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. INFO321 Week 3 Assignment Page 2 of 2 D) List the PropertyNum, Street, City, WeeklyRental, AgentNum, and Tenant fields for all properties with a weekly rental less than 700 E) List the PropertyNum, Street, City, WeeklyRental, MonthlyRental, and Tenant fields for all properties assigned to agent ID102. 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, lnclude 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).