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

 

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.

ComputerScienceExpert

(11)

Status NEW Posted 26 Apr 2017 04:04 AM My Price 8.00

-----------

Attachments

file 1493182940-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer