Extra Credit: Happy Insurance Database

HAPPY INSURANCE

Observe the HAPPY INSURANCE DATABASE:

The information below illustrates and describes tables in a database named Happy Insurance.  The illustration also includes a representation of the data instances in the tables. You can use this as a partial representation of the Data and Metadata for the tables in 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 responding to the following requests and questions.

The cardinality between the Tables

There is a 1_M Relationship between the Agent Table and the Client Table.  

There is a 1_M Relationship between the Area Table and the Agent Table

1.   Write and Show the CREATE TABLE statements for the table AREA.  The Create Table statement must have the appropriate Primary Key and Foreign Key Constraint declarations based on the cardinality listed above.

2. Write and Show the CREATE TABLE statements for the table AGENT.  The Create Table statement must have the appropriate Primary Key and Foreign Key Constraint declarations based on the cardinality listed above.

3. Write and Show the CREATE TABLE statements for the table CLIENT.  The Create Table statement must have the appropriate Primary Key and Foreign Key Constraint declarations based on the cardinality listed above.

The CREATE TABLE Statements must have the appropriate Primary Key and Foreign Key Constraints based on the attribute names, data, and descriptions in the information above. This is a relational database.  The relationships between the table must be established in the CREATE Table Statements

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

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

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

The INSERT INTO Statements must have data that has the appropriate Primary Key and Foreign Key Constraints links described and listed in the CREATE TABLE Statements. It is recommended to use the sample listed above.  Make sure that you have supplied sufficient data to match the information requests listed below.  Each information request must produce a ResultSet that contains at least one Row/Tuple containing information. 

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 in Area 3.

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 the HAPPY INSURANCE database that will for each area display the area ID, area name, and average rating for all agents in all the areas.

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, the agent'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 an SQL text file.  Word Processing Files will not be accepted.

This is a 300 Point Extra Credit Assignment