Task 13 - Java JDBC and Basic SQL Practice Exercise

Download SQL script file. Connect MySQL Workbench with MySQL Database server. In Workbench, choose menu: File > Open SQL Script option and select the downloaded file. Run the script to create a database of two tables i.e. products and product_categories. You can open the script in Notepad++ to anaylyze, it contains SQL statements to create database, tables and few insert statement to add some seed data. Write Java application that shall provide following features:
  1. Show all categories names with products count of that category e.g. Mobiles - 2
  2. Show list of categories (id and name only) to user to choose a category id by user input. Show product id, name, description, minimum stock level and cost of all products that belongs to the given category id.
  3. Show average cost of products the store sell.
  4. Show products order by category.
  5. Show products where minimum stock level is between 50 and 300 (inclusive).
  6. Let user search the products by entering a string and show all products whose name contain that string (hint: use 'like' clause in select query).
  7. Show total number of products the database contain. 
  8. Get product model from user input and show its cost and minimum order quanity. 
  9. Let user add a new product under given category id. Get all attributes values from user, use LocalDate class to store Date.
  10. Let user delete the product by product id.
  11. Let user update the model and cost by product id.