Please answer the full question only, not half of it. Observe the HAPPY INSURANC
ID: 3850950 • Letter: P
Question
Please answer the full question only, not half of it.
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.
Explanation / Answer
1. create TABLE CLIENT (ClientID varchar(4), ClientName varchar(20), ClientAgent varchar(2), ClientSpouseName varchar(20), PRIMARY KEY(ClientID), INDEX(ClientAgent));
2. create TABLE AGENT (AgentID varchar(2), AgentName varchar(20), AgentArea int(1), AgentRating int(3), AgentYearOfHire int(4), SupervisedBy varchar(2), PRIMARY KEY(AgentID), INDEX(AgentArea), FOREIGN KEY(AgentID) REFERENCES CLIENT(ClientAgent));
3. create table AREA (AreaID int(1), AreaName varchar(20), AreaHQ varchar(20), PRIMARY KEY(AreaID), FOREIGN KEY(AreaID) REFERENCES AGENT(AgentArea));
4.
INSERT INTO `client` (`ClientID`, `ClientName`, `ClientAgent`, `ClientSpouseName`) VALUES ('C111', 'Tom', 'A1', 'Jenny');
INSERT INTO `client` (`ClientID`, `ClientName`, `ClientAgent`, `ClientSpouseName`) VALUES ('C222', 'Karin', 'A1', 'Bill');
INSERT INTO `client` (`ClientID`, `ClientName`, `ClientAgent`, `ClientSpouseName`) VALUES ('C333', 'Cole', 'A2', 'Amy');
INSERT INTO `client` (`ClientID`, `ClientName`, `ClientAgent`, `ClientSpouseName`) VALUES ('C444', 'Dorothy', 'A2', '');
INSERT INTO `client` (`ClientID`, `ClientName`, `ClientAgent`, `ClientSpouseName`) VALUES ('C555', 'Andy', 'A3', 'Amy');
INSERT INTO `client` (`ClientID`, `ClientName`, `ClientAgent`, `ClientSpouseName`) VALUES ('C666', 'Tina', 'A3', 'Matt');
INSERT INTO `client` (`ClientID`, `ClientName`, `ClientAgent`, `ClientSpouseName`) VALUES ('C777', 'Christina', 'A4', 'Mike');
5.
INSERT INTO `agent`(`AgentID`, `AgentName`, `AgentArea`, `AgentRating`, `AgentYearOfHire`, `SupervisedBy`) VALUES ('A1','Kate',1,101,1990,'');
INSERT INTO `agent`(`AgentID`, `AgentName`, `AgentArea`, `AgentRating`, `AgentYearOfHire`, `SupervisedBy`) VALUES ('A2','Amy',2,92,2009,'A1');
INSERT INTO `agent`(`AgentID`, `AgentName`, `AgentArea`, `AgentRating`, `AgentYearOfHire`, `SupervisedBy`) VALUES ('A3','Luke',3,100,1992,'');
INSERT INTO `agent`(`AgentID`, `AgentName`, `AgentArea`, `AgentRating`, `AgentYearOfHire`, `SupervisedBy`) VALUES ('A4','James',3,90,2003,'A3');
6.
INSERT INTO `area`(`AreaID`, `AreaName`, `AreaHQ`) VALUES (1,'East','Boston');
INSERT INTO `area`(`AreaID`, `AreaName`, `AreaHQ`) VALUES (2,'West','San Fransisco');
INSERT INTO `area`(`AreaID`, `AreaName`, `AreaHQ`) VALUES (3,'Central','Chicago');
7. SELECT AgentID, AgentName from AGENT WHERE AgentYearofHire < 2000;
8. SELECT AVG(AgentRating) from AGENT;
9. SELECT AgentArea as AreaID, count(AgentArea) from AGENT GROUP BY AgentArea;
10. SELECT Client.ClientName from CLIENT, AGENT WHERE CLIENT.ClientAgent=AGENT.AgentID having MAX(AGENT.AgentRating);
11. SELECT AREA.AreaID, AREA.AreaName, AVG(AGENT.AgentRating) from AREA, AGENT WHERE AREA.AreaID=AGENT.AgentArea GROUP BY AREA.AreaID;
12. SELECT AREA.AreaID, AREA.AreaName, AVG(AGENT.AgentRating) from AREA, AGENT WHERE AREA.AreaID=AGENT.AgentArea and AGENT.AgentRating >100 GROUP BY AREA.AreaID;
13. SELECT CLIENT.ClientName, AGENT.AgentName from CLIENT, AGENT where CLIENT.ClientAgent=AGENT.AgentID AND AGENT.AgentName="Amy";
14. SELECT CLIENT.ClientName, AREA.AreaName from CLIENT, AGENT, AREA where CLIENT.ClientAGent=AGENT.AgentID AND AGENT.AgentArea=AREA.AreaID;
15. ALTER TABLE `client` ADD `ClientPhone` VARCHAR(10) NOT NULL AFTER `ClientSpouseName`;
Hope This helps. Please Vote UP my aanswer if you like it. Thanks...