Please I need help with writing SQL queries

1. Create a query to list all members with an area code = 915. (Matching the 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.

2. Create a query to update all members with an area code = 915. The new area code should be 419. Rerun the query created in #2 above to display the updated data.

3. 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 a column that identifies the rows table of origin: Member, Employer or Carrier. For example insert an 'M' for members, 'E' for employees and a 'C' for carriers. Order the Union-ed data by contact name.

4. 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 an INTERSECT query that will provide that list of states. Order by State.

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

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

7. Which Plans do not have any Member subscriptions? List the PlanID, the plnDescription and plnCost. (Hint: if you use the nested Select statement with a NOT IN clause you must exclude the NULL records from the nested Select. You may also want to try left or right join.) Order by PlanID.

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

9. List ALL Carriers and the count of Plans they have. (use either the right or left join). List the CarrierID, CarName, and the count of Plans. Order by carName.

10. 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.

11. 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.

12. 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.

13. List all Agents (AgentID, AgentFname, and AgentLname) along with the count of how many employers they service and the count of how many employees work for those employers.

14. Each month, our company bills the employers for their insurance coverage. Create a View (named: yourLastNameView14) that will produce a listing that can be used 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.

15. Create a View (named: yourLastNameView16) 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 Carriers 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.

CarrieriD carName carAddress carCity car Code carzip carphone car Website carContactFirstName carContactLastName carEmail Plans PlanID plnDescription plnCost CarrierID Employers EmployerID emplName emplAddress emplCity emplState emplzip empl Phone emplContactFirstName emplContactLastName emplContactEmail Agent ID Members MemberNo mbrFirstName mbr LastName mbrStreet mbrCity mbr State mbrzip mbrPh oneNo PlanID mbrEmail mbrDateEffective employerID Agents Agent ID AgentFName Agent Name Agent Phone AgentsupertD Agent Title CarrieriD carName carAddress carCity car Code carzip carphone car Website carContactFirstName carContactLastName carEmail Plans PlanID plnDescription plnCost CarrierID Employers EmployerID emplName emplAddress emplCity emplState emplzip empl Phone emplContactFirstName emplContactLastName emplContactEmail Agent ID Members MemberNo mbrFirstName mbr LastName mbrStreet mbrCity mbr State mbrzip mbrPh oneNo PlanID mbrEmail mbrDateEffective employerID Agents Agent ID AgentFName Agent Name Agent Phone AgentsupertD Agent Title