The following tables are part of a schema of an auto insurance company. The insurance database keeps a record of cars and a record of claims for cars where policy holders could have initiated an auto insurance claim such as an auto accident. Issues for which a claim is initiated are called claim events. All claims have a claim event. A type of a claim event could be found in one or more claims. A car could have no or many claims. Every car has a make and a model. One car make could have one or more models. Write a query that shows the make, model, VIN number and year of manufacture only for cars that have an auto insurance claim of any event type. If the database tables were populated, your query would not list cars that have not had a claim. You do not need to create the database to answer this question but you might just for visualization. CREATE TABLE CAR_MAKE ( CAR_MAKE_ID INT NOT NULL PRIMARY KEY, CAR_MAKE VARCHAR(25)); CREATE TABLE MAKE_MODEL ( MAKE_MODEL_ID INT NOT NULL PRIMARY KEY, CAR_MODEL VARCHAR(25), CAR_MAKE_ID INT, CONSTRAINT CM_MM_FK FOREIGN KEY (CAR_MAKE_ID) REFERENCES CAR_MAKE (CAR_MAKE_ID)); CREATE TABLE AUTOMOBILE ( AUTOMOBILE_ID INT NOT NULL PRIMARY KEY, VIN VARCHAR(255), MAKE_MODEL_ID INT, YEAR_OF_MANUFACTURE VARCHAR(4), CONSTRAINT AU_MM_FK FOREIGN KEY (MAKE_MODEL_ID) REFERENCES MAKE_MODEL (MAKE_MODEL_ID)); CREATE TABLE CLAIM_EVENT ( CLAIM_EVENT_ID INT NOT NULL PRIMARY KEY, EVENT_TYPE VARCHAR(255)); CREATE TABLE CLAIMS ( CLAIM_ID INT NOT NULL PRIMARY KEY, CLAIM_NUMBER VARCHAR(25), AUTOMOBILE_ID INT, CLAIM_EVENT_ID INT, CLAIM_DATE DATE, CONSTRAINT CL_AU_FK FOREIGN KEY (AUTOMOBILE_ID) REFERENCES AUTOMOBILE (AUTOMOBILE_ID), CONSTRAINT CL_EV_FK FOREIGN KEY (CLAIM_EVENT_ID) REFERENCES CLAIM_EVENT (CLAIM_EVENT_ID));
The following tables are part of a schema of an auto insurance company. The insurance
Every car has a make and a model. One car make could have one or more models.
Write a query that shows the make, model, VIN number and year of manufacture only for cars that have an auto insurance claim of any event type. If the database tables were populated, your query would not list cars that have not had a claim.
You do not need to create the database to answer this question but you might just for visualization.
CREATE TABLE CAR_MAKE (
CAR_MAKE_ID INT NOT NULL PRIMARY KEY,
CAR_MAKE VARCHAR(25));
CREATE TABLE MAKE_MODEL (
MAKE_MODEL_ID INT NOT NULL PRIMARY KEY,
CAR_MODEL VARCHAR(25),
CAR_MAKE_ID INT,
CONSTRAINT CM_MM_FK FOREIGN KEY (CAR_MAKE_ID) REFERENCES CAR_MAKE (CAR_MAKE_ID));
CREATE TABLE AUTOMOBILE (
AUTOMOBILE_ID INT NOT NULL PRIMARY KEY,
VIN VARCHAR(255),
MAKE_MODEL_ID INT,
YEAR_OF_MANUFACTURE VARCHAR(4),
CONSTRAINT AU_MM_FK FOREIGN KEY (MAKE_MODEL_ID) REFERENCES MAKE_MODEL (MAKE_MODEL_ID));
CREATE TABLE CLAIM_EVENT (
CLAIM_EVENT_ID INT NOT NULL PRIMARY KEY,
EVENT_TYPE VARCHAR(255));
CREATE TABLE CLAIMS (
CLAIM_ID INT NOT NULL PRIMARY KEY,
CLAIM_NUMBER VARCHAR(25),
AUTOMOBILE_ID INT,
CLAIM_EVENT_ID INT,
CLAIM_DATE DATE,
CONSTRAINT CL_AU_FK FOREIGN KEY (AUTOMOBILE_ID) REFERENCES AUTOMOBILE (AUTOMOBILE_ID),
CONSTRAINT CL_EV_FK FOREIGN KEY (CLAIM_EVENT_ID) REFERENCES CLAIM_EVENT (CLAIM_EVENT_ID));
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
