SQL developer

INFS 3770 – Assignment #6 Basic Queries & Script Files – Using Oracle

30 Points Possible

For this assignment, you will download and execute a script file to create and insert data into the NHL tables data similar to what you did is the previous assignment. You will need to write the SQL to solve 12 query problems defined in the Assignment 6 - Query Defs and Solution Images.pdf file. You will build a single script file named yourLastName_A6Script.txt that contains all your SQL and SQL/PL statement necessary to solve and format the 12 queries. Output should be directed to a single .txt file named yourLastName_A6Spool.txt. You will need to submit both files for grading. Assignment Steps: Step 1) Download and execute the nhl player, team and position sql script files to build and populate the 3 tables of the NHL database. The code is error free – and should execute without issue. The Player table carries foreign keys to both the Team and Position table – so note that the order of execution of the script files DOES matter. Step 2) Verify that your tables were created. You can visually inspect the table list using SQL Developer's user interface – by clicking on the Tables under the connection – and drilling down to view the table list – and then selecting each table and viewing the Data tab in the worksheet area. **No output from this section needs to be included in your spooled file. Step 3) Write the SQL code for the queries needed to answer the questions and/or perform the operations as outlined in the accompanying document. Use the Assignment6_template.txt SQL file provided to help you better format your output. Formatted output is required! This means that you should create PL statements to format the display of the columns in your result sets. Remember that COLUMN statements persist, meaning you could define the formats at the top of your script – and not need to re-define them for each individual query. (Just don't CLEAR COLUMNS). Review the template provided to assist you with this requirement. Test your script file to ensure that it executes. Capture the spooled output and post both the script file and spool file to Blackboard. Project Deliverables:

1. (24 points) Script file with correct solutions to queries 1 – 12 with comments to identify query problem# and delimiters to separate one query from the next. This file should only contain SQL code, comments and PL statements to format your output.

2. (2 points) Spool file with contents of echoed SQL and spooled query output. 3. (4 Points) Properly named, properly submitted – well documented, well formatted and

easy to grade solution.