Big Java Lab 25

SQL

1.

Design a set of database tables for a pizza restaurant.

The restaurant stores the names, addresses, and phone numbers of its home delivery customers. All the current and previous orders for each customer are stored in the database. An order includes one or more pizzas. A pizza has size, crust type, one or more toppings.

The price of a pizza is the sum of the base price ($10 for a small pizza, $13 for a medium, and $16 for a large pizza), plus the sum of the topping prices. The crust type doesn't affect the price.

List the SQL code creating the tables.


2.

Write SQL statements to populate your tables with data.


3.

Give a SQL query that lists all pizza toppings for your pizza restaurant database.


4.

Give an SQL query to find if there is a customer named 'John Smith' in the database.

Give an SQL query to find the customer code (or id) for the customer named 'John Smith'.


5.

Give a SQL query that describes a particular order for a particular client, for example, order with order code '6' for client '1'.


6.

How can you calculate the total of an order?

JDBC

7.

Write a Java program to implement your pizza restaurant database.

The restaurant stores the names, addresses and phone numbers of its customers. All the current and previous orders for each customer are stored in the database. An order includes one or more pizzas. A pizza has size, crust type and one or more toppings.

The price of a pizza is the sum of the base price ($10 for a small pizza, $13 for a medium, and $16 for a large pizza), plus the sum of the topping prices. The crust type doesn't affect the price.

Using the tables you created and populated with data, write a PizzaOrderingSystem class with a method

public void acceptOrder()

that asks for the customer information and order, and stores the information in the database. You may additional helper classes.

What is the code of your PizzaOrderingSystem class?


8.

Did you use helper classes? If so, what is their code?


9.

What is the code of your tester program?


10.

To run your program you need a database properties file. What is the content of your database.properties file?


11.

You are the store manager and want to give a special prize to your best customer. Give an SQL query to find out what customer has the greatest number of orders in the system.