switch $($choice$)\{$ Question $2$

You are provided with a SQL file named data.sql : here is it $--$ 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)$;

INSERTthat contains SQL state$-$

ments to create and populate a Cars table.

Your Task:

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:

public class CarShopApp $\{$

public static void main$($String$[]$ args$)\{$

$//$ Establish connection to the database

$//$ Create a Statement object

$//$ Execute the SELECT $*$ FROM CARS query

$//$ Initialize Scanner for user input

Scanner scanner $=$ new Scanner$($

System.in$)$;

try $\{$

boolean running $=$ true;

while $($running$)\{$

$//$ Display menu

System.out.printIn$("$Menu: $")$;

System.out.printIn$("1.$ Check Stock"$)$;

System.out.printIn$("2.$ Exit"$)$;

System.out. print$("$Choose an option: $")$;

int choice $=$ scanner.nextInt$()$; 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 execute the query.

Implement the checkStockByYear function within the provided skeleton

code.

Ensure your code is properly formatted and includes necessary error

handling.

Test your program to verify that it works correctly.

Submission:

Submit the complete Java code including the implemented checkStockByYear

function and the completed main method.