Observe the HAPPY INSURANCE DATABASE: CLIENT ClientID ClientName ClientAgent Cli
ID: 3850261 • Letter: O
Question
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 statements for the table AREA
Answer: CREATE TABLE AREA(AreaID int,AreaName varchar(255),AreaHQ varchar(255));
2) CREATE TABLE statements for the table AGENT
Answer: CREATE TABLE AGENT(AgentID varchar(2),AgentName varchar(20),AgentArea int,AgentRating int,AgentYearOfHire int,SupervisedBy varchar(2));
3) CREATE TABLE statements for the table CLIENT
Answer: CREATE TABLE CLIENT(ClientID varchar(3),ClientName varchar(30), ClientAgent varchar(2),ClientSpouseName varchar(30));
4) INSERT INTO statements for the table AREA
Answer:
Insert INTO AREA(AreaID,AreaName,AreaHQ) VALUES(1,"East","Boston");
Insert INTO AREA(AreaID,AreaName,AreaHQ) VALUES(2,"West","San Francisco");
Insert INTO AREA(AreaID,AreaName,AreaHQ) VALUES(3,"Central","Chicago");
5) INSERT INTO statements for the table AGENT.
Answer:
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,2010,"A3");
6) INSERT INTO statements for the table CLIENT.
Answer:
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");
7) list the agent ID and agent name for each agent hired before the year 2000
Answer:
select AgentID,AgentName from Agent where AgentYearOfHire<'2000';
8) display the average rating for all agents.
Answer:
Select avg(AgentRating) from Agent;
9) for each area display the area ID and the number of agents in the area.
Answer:
SELECT AgentArea, COUNT(*) FROM Agent Group By AgentArea;
10) display the name of each client of the agent with the highest agent rating in the company.
Answer:
SELECT ClientName FROM Client, Agent WHERE ClientAgent = AgentID AND AgentRating = (SELECT MAX(AgentRating) FROM agent);
11) for each area display the area ID, area name, and average rating for all agents in the area.
Answer:
SELECT AreaID, AreaName, AVG(AgentRating) FROM Area, Agent WHERE AreaID = AgentArea GROUP BY AreaID, AreaName;
12) 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.
Answer: SELECT AreaID, AreaName, AVG(AgentRating) FROM Area, Agent WHERE AreaID = AgentArea GROUP BY AreaID, AreaName HAVING MAX(AgentRating) > 100;
13) for each client of the agent named Amy, list the client's name and the name of the client's agent.
Answer: SELECT ClientName, AgentName FROM Client, Agent WHERE ClientAgent = AgentID and AgentName = 'Amy';
14) for each client list the client's name and the name of the area of his or her agent.
Answer: SELECT ClientName, AreaName FROM Client, Agent, Area WHERE ClientAgent = AgentID AND AreaID = AgentArea;
15) adds the column ClientPhone to the table CLIENT.
Answer: ALTER TABLE Client Add ClientPhone CHAR(12);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.