Load the createInsurance.txt script file to create and populate the tables necessary for this assignment. The insurance database includes the following tables: Employer, Member, Carrier, Plan and Agent.
Employers have employees (that are called members). The members subscribe (or elect) to participate in a health plan. These health plans are offered by Insurance Carriers and sold to Employers by Agents. The table relationships are defined for you and are pictured below. Review the table structures before you attempt to solve the assignment problems.
You will need to write the SQL to solve 14 query problems defined below. You will build a single script file named yourLastName_A8Script.txt that contains all your SQL and SQL/PL statement necessary to solve and format the 14 queries. Output must be directed to a single .txt file named yourLastName_A8Spool.txt. You will need to submit both files for grading.
Assignment Steps:
Step 1) Download and execute the createInsuranceScript.txt script file to build and populate the 5 tables of the database. The code is error free – and should execute without issue.
Step 2) Verify that your tables were created. You can visually inspect the table data 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 create the result sets needed to meet the assignment problem specification -- as defined in the accompanying document. Refer back to Assignment 6 – and use the same template to guide you in organizing your script file.
Step 4) Formatted output is required! You will need to write PL statements to create titles that bookend and title your result sets. Define the page size and line size of your output. Also, format the display of the columns in your result sets – all numbers should be formatted: currency to display as currency, other number to display with fixed number of decimal places. Your result set output should fit each row to one line of output without it wrapping to multiple lines.
Reduce the width of text columns by truncating the text output. Remember that COLUMN statements persist – and thus only need to be defined once for each column – not once for each query. Place all COLUMN statements together near the top of your script file make it easy to grade your SQL solutions. (Be sure to place a CLEAR COLUMNS statement at the top of your code).
Test your queries individually and then test your script file to ensure that it executes error free. Add the
Spool statements to generate and capture the spooled output and post both the script file and spool file
Project Deliverables:
1. (3 points) Script file, properly delineated and neatly organized with solutions to queries 1 – 14.
This file should only contain SQL code, comments and PL statements to format your output.
2. (24 points). Correct SQL statements for query problems 1 – 12.
3. (6 points). Correct SQL statements for query problems 13 and 14.
4. (3 points) Spool file with contents of echoed SQL and spooled query output.
5. (4 points) Formatted output in your spool file that displays string and numeric data meaningfully in the generated result sets.
#sqlassignmenthelp #oracleassignmenthelp #oraclequeryhelp #sqlqueryhelp #oracle #sql #plsql
Solutions: Database creation ,tables , insert data
commit;commit;Commit;Commit;commit;commit;commit;commit;PlanID integer references Plan,mbrDateEffective date,commit;commit;commit;