sql queries

INFS 3770 – Assignment #8 Advanced SQL using Oracle – 40 points

Load and execute the createInsuranceScript.txt script file (in SQL Developer) 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 provided below. Review the table structures as you solve the following problems.

Include a listing for each problem attempted, even if it produces an error – as I will give partial credit for any reasonable attempt. Problems: 1. Insert a row for yourself into the Member Table. Use your real name and assign yourself MemberNo = 88, Employer ID = 2, PlanID = 7, phone number = '9158888888', and state = 'OH'. Make up the data for the rest of the fields. Write a query to display the count of members before the update, insert the row, then write a query to display only the inserted row's MemberNo, first and last name. Finally, re-run the query to display the count of members after the update. Be sure to Commit the insert.

2. Create a query to list all members with an area code = 915. (Matching the first 3 characters of the Phone Number). List the data as follows: Member Name (formatted as Last Name, First), Area Code (in its own column), and Phone Number. The Area code must be displayed as ‘(999)’ and format the phone number to be displayed with the dash inserted in the 4th position ‘###-####’. (Use the substr() function and concatenation operands.) Order the result set by Name.

3. Create a query to update the AgentTitle (in the Agent table) and replace all rows with the agent title of "Salesman" and replace it with "Sales Rep". List the data of the Agent table to display the updated data.

4. The legal department needs to send a blast e-mail to all Employers, Members and Carriers who reside or do business in the state of Ohio. Create a UNION query that will build a result set with a complete list of Ohio contacts. Include the contact name, e-mail addresses, and project a new column that identifies the row’s table of origin: ‘Member’, ‘Employer’ or ‘Carrier’. For example – project an 'M' for members, 'E' for employees and a 'C' for carriers. Order the 'Unioned' data by contact last name.

5. The marketing department is considering where to place a customer service center. It makes sense to locate it in a state in which we have both Carriers and Employers. Create a query using the INTERSECT set operator that will provide that list of states. Order by State.

6. Create the same result set identified above (#5) – but instead of using the INTERSECT set operator – code a nested query with the IN keyword. Order by State.

7. Which Carriers currently do NOT have a Plan? (Use the MINUS set operator in your solution.) Display the CarrierID and the carName.

8. Which Carriers currently have a Plan? List the CarrierID, carName, and carWebSite. (Create a solution using a nested Select statement with an IN clause). Order by carName.

9. List ALL Plans and a count of Members that have subscribed to each plan. (use either the right or left join). List the PlanID, plnDescription, plnCost and the count of Members. Order by PlanID.

10. List ALL Plans and a count of the Members who subscribe to those plans (if there are no subscribers – the count should = 0). Also include the count of ALL members who do not subscribe to a plan. (You must use a FULL OUTER Join for this query.). Order by PlanID.

11. Select and list all Plans (PlanID, plnDescription, and plnCost) that have a plncost greater than the average plncost of all plans. Also display the average plncost of all plans as a column in the result set. Order the result set by PlanId.

12. List all Agents (AgentID, AgentFname, and AgentLname), the count of how many employers they service and the count of how many employees work for those employers. Order by AgentID.

13. Each month, our company bills the employers for their insurance coverage. Create a View (named: yourLastNameView13) that will produce a listing to generate monthly bills. Include in the view: the Employer Name, the employer contact name (first and last), the plan ID and description, the count of employees in that plan, the plan cost and the billing amount (count * plan cost). Order the view by Employer Name and PlanID. Then, write the SQL statement required to display the result set generated by the view created for the problem. Your SQL should SELECT FROM the VIEW not the underlying SQL code.

14. Create a View (named: yourLastNameView14) that will list Carriers and each of their Plans along with a column that displays the number of members in that Plan, the total number of members serviced by the Carrier (for all plans) and the percent of the Carrier’s Members that are in that Plan. Order by Carrier and Plan Description. Then, write the SQL statement required to display the result set generated by the view created for the problem. Your SQL should SELECT FROM the VIEW not the underlying SQL code.