Extra Credit: Happy Insurance Database

HAPPY INSURANCE

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.  Add a 3-5 unique entries of your own

5.  Write and Show the INSERT INTO statements for the table AGENT. Add a 3-5 unique entries of your own

6.  Write and Show the INSERT INTO statements for the table CLIENT. Add a 3-5 unique entries of your own

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.

Submit your responses to these requests in text file.  Word Processing Files will not be accepted.

This is a 300 Point Extra Credit Assignment