Suppose that a car insurance company has built a database for the purpose of cal

Question

Suppose that a car insurance company has built a database for the purpose of calculating policy price incorporating various discounts.   It is made of the following tables:

CARS (IDENT, MAKE, MODEL, DISPLACEMENT, POWER, LUXURY)

CITIES (IDENT, POPULATION)

CONTRACTS (IDENT, CUSTOMER_NAME, CUSTOMER_BIRTHDATE, CUSTOMER_GENDER,CUSTOMER_CITY, CAR_IDENT, CONTRACT_DATE, CONTRACT_CLASS, MAX_REFUND, BASE_PRICE, FINAL_PRICE)

The CARS table contains information about brand, model, power and displacement, as well as a rather arbitrary attribute about being a luxury car or not.

The CITIES table will provide the known population for each served city.

The CONTRACTS table contains all information about each contract, including customer name, birth date and sex, as well as the contract start date, the contract class (an integer that decreases every year that a customer has no accidents, down to zero, and is raised when a customer has an accident), the city where the customer lives, the insured car and the maximum refund chosen by the customer.

Questions

Find all of the customers that lives in a small city. A small city is defined as a city with population of 500,000 or less. Generate a list of these customers, showing name and city of residence. (Note: you do this using Python program, not by hand). Report the python code you used.

Find all the luxury cars that part of any contracts with the company. Report the list such cars by IDENT, Make, and MODEL.

For any contract that cover that has a contract class of 10 or above, set the base price (in the contracts table) to 1000

Explanation / Answer

I am writting SQL queries for the question, you can embed them in the program.

select CUSTOMER_NAME, CUSTOMER_CITY from CUSTOMER, CITIES where CITIES.IDENT=CUSOTMER_CITY and CITIES.POPULATION<=500000;

select IDENT, MAKE, MODEL from CARS, CONTRACTS where CONTRACTS.CAR_IDENT=CARS.IDENT and (LUXURY="YES" or LUXURY="yes" or LUXURY="Yes");

update CONTRACTS set BASE_PRICE=1000 where CONTRACT_CLASS>=10;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.