Question $2$

You are provided with a SQL file named data.sql that contains SQL statements to create and populate a Cars table. Here is the data.sql code below: $--$ Connect to or create the CarDB database

CONNECT $\text{'}$jdbc:derby:CarDB;create$=$true';

$--$ Drop the table if it already exists to avoid conflicts

DROP TABLE Cars;

$--$ Create the Cars table

CREATE TABLE Cars $($

id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,

make VARCHAR$(50),$

model VARCHAR$(50),$

manufacture$\_$year INT,

price DECIMAL$(10,2)$

$)$;

$--$ Insert $30$ sample records into the Cars table

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Toyota$\text{'},$ 'Corolla', $2020,20000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Ford$\text{'},$ 'Mustang', $2018,30000\mathrm{.}50)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Honda$\text{'},$ 'Civic', $2019,22000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Chevrolet$\text{'},$ 'Camaro', $2021,35000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$BMW$\text{'},\text{'}3$ Series', $2017,28000\mathrm{.}75)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Audi$\text{'},\text{'}$A$4\text{'},2016,27000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Mercedes$-$Benz', $\text{'}$C$-$Class', $2019,40000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Tesla$\text{'},$ 'Model $3\text{'},2021,50000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Hyundai$\text{'},$ 'Elantra', $2018,19000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Kia$\text{'},$ 'Optima', $2020,21000\mathrm{.}50)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Volkswagen$\text{'},$ 'Jetta', $2021,23000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Nissan$\text{'},$ 'Altima', $2019,24000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Mazda$\text{'},$ 'Mazda$3\text{'},2017,18000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Subaru$\text{'},$ 'Impreza', $2020,26000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Jeep$\text{'},$ 'Wrangler', $2018,32000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Dodge$\text{'},$ 'Charger', $2019,29000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Volvo$\text{'},\text{'}$S$60\text{'},2017,27000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Porsche$\text{'},$ 'Cayenne', $2021,80000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Ferrari$\text{'},\text{'}488$ Spider', $2020,280000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Lamborghini$\text{'},$ 'Huracan', $2021,300000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Bentley$\text{'},$ 'Continental GT$\text{'},2020,220000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Rolls$-$Royce', 'Phantom', $2021,450000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Ford$\text{'},$ 'Explorer', $2019,35000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Chevrolet$\text{'},$ 'Tahoe', $2020,60000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$BMW$\text{'},\text{'}$X$5\text{'},2021,65000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Mercedes$-$Benz', 'GLE', $2020,70000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Toyota$\text{'},$ 'RAV$4\text{'},2021,30000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Honda$\text{'},\text{'}$CR$-$V$\text{'},2020,32000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Audi$\text{'},\text{'}$Q$7\text{'},2021,75000\mathrm{.}00)$;

INSERT INTO Cars $($make$,$ model, manufacture$\_$year, price$)$

VALUES $(\text{'}$Lexus$\text{'},\text{'}$RX$\text{'},2020,55000\mathrm{.}00)$;

$--$ Commit the transaction

COMMIT;

$--$ Verify the content of the Cars table $($optional$)$

SELECT $*$ FROM Cars;

Your Task:

Create a new database called CarShop.

Import the data.sql file into your CarShop database to create and popu$-$

late the Cars table.

You are required to write a Java program that connects to the CarShop database

and presents a menu to the user. The program should use a SELECT $*$ FROM

CARS query to retrieve all records from the Cars table and perform tasks by

processing the ResultSet.

The following code is provided:

The function should prompt the user to enter a manufacture year.

The function should output the number of cars available for that year.

You are only allowed to use the SELECT $*$ FROM CARS query; you may

not use any other SQL statements to accomplish the task.

All logic should be implemented by working with the ResultSet obtained

from the SELECT $*$ query.

Instructions:

Complete the missing code in the main method to establish the database

connection and exec