Write the SQL queries

Question

HAPPY INSURANCE Database Tables

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

Question #1

Show the CREATE TABLE statements for HAPPY INSURANCE database for the table CLIENT (assume that non-primary key columns can be optional) with the DELETE SET-TO-NULL and UPDATE CASCADE option for its referential integrity constraint.

Question #2

Show the CREATE TABLE statements for HAPPY INSURANCE database for the table AGENT (assume that non-primary key columns can be optional) with the user defined constraint specifying that the agent rating must be between 50 and 150.

Answer 1

Presented in the opposite order, since problem 1 refers to a table

defined/constructed in problem 2

#2)

CREATE TABLE agent (

id VARCHAR(2) NOT NULL,

name VARCHAR(32) NOT NULL,

area TINYINT NULL,

rating TINYINT NULL CHECK (50 >= rating <= 150 ),

year_of_hire CHAR(4) NULL,

supervisor_id VARCHAR(2) NULL,

PRIMARY KEY(id),

FOREIGN KEY (supervisor_id) REFERENCES agent(id)

);

#1)

CREATE TABLE client (

id VARCHAR(4) NOT NULL,

name VARCHAR(32) NOT NULL,

agent_id VARCHAR(2) NULL,

spouse_name VARCHAR(32) NULL,

PRIMARY KEY(id),

FOREIGN KEY (agent_id) REFERENCES agent(id)

ON UPDATE CASCADE

ON DELETE SET NULL

);

NOTES: I applied my own naming conventions best practices,

but fields/data line up as described in the problem text.