Observe the HAPPY INSURANCE DATABASE:

CLIENT

ClientID ClientName ClientAgent ClientSpouseName

C111 Tom A1 Jenny

C222 Karin A1 Bill

C333 Cole A2 Amy

C444 Dorothy A2

C555 Andy A3 Amy

C666 Tina A3 Matt

C777 Christina A4 Mike

AGENT

AgentID AgentName AgentArea AgentRating AgentYearOfHire SupervisedBy

A1 Kate 1 101 1990

A2 Amy 2 92 2009 A1

A3 Luke 3 100 1992

A4 James 3 90 2010 A3

AREA

AreaID AreaName AreaHQ

1 East Boston

2 West San Francisco

3 Central Chicago

This database will be used for the following questions citing tables from the HAPPY INSURANCE database. Examine the result sets listed above. Use the Column Titles and Data as a guide to respond to the following requests and questions.

1. Write and Show the CREATE TABLE statements for the table AREA

2. Write and Show the CREATE TABLE statements for the table AGENT

3. Write and Show the CREATE TABLE statements for the table CLIENT

4. Write and Show the INSERT INTO statements for the table AREA

5. Write and Show the INSERT INTO statements for the table AGENT.

6. Write and Show the INSERT INTO statements for the table CLIENT.

7. Write an SQL query for the HAPPY INSURANCE database that will list the agent ID and agent name for each agent hired before the year 2000.

8. Write an SQL query for the HAPPY INSURANCE database that will display the average rating for all agents.

9. Write an SQL query for the HAPPY INSURANCE database that will for each area display the area ID and the number of agents in the area.

10. Write an SQL query for the HAPPY INSURANCE database that will display the name of each client of the agent with the highest agent rating in the company.

11. Write an SQL query for HAPPY INSURANCE database that will for each area display the area ID, area name, and average rating for all agents in the area.

12. Write an SQL query for the HAPPY INSURANCE database that will, for each area where the highest rated agent has a rating higher than 100, display the area ID, area name, and average rating for all agents in the area.

13. Write an SQL query for the HAPPY INSURANCE database that will, for each client of the agent named Amy, list the client's name and the name of the client's agent.

14. Write an SQL query for the HAPPY INSURANCE database that will for each client list the client's name and the name of the area of his or her agent.

15. Write an SQL statement for the HAPPY INSURANCE database that adds the column ClientPhone to the table CLIENT.