Welcome to the "Burger Restaurant Database" project! This GitHub repository focuses on creating a comprehensive database for a burger restaurant using SQL, along with an Entity-Relationship (ER) diagram generated using dbdiagram.io and several SQL queries to analyze sales, customer spending, employee performance, and ingredient usage.

1. receipt Table: Stores information about customer orders, including order date, menu items, quantity, and payment details.

2. customer Table: Contains customer details such as name, address, email, and phone number.

3. menu_detail Table: Holds details about menu items, including menu name and price.

4. employee Table: Stores information about employees, including name and contact details.

5. payment Table: Contains payment types accepted by the restaurant.

6. ingredient Table: Lists various burger ingredients.

7. food_recipe Table: Establishes relationships between menu items and ingredients.

The ER diagram visually represents the relationships between different tables, providing a clear overview of the database structure.

1. Top-Selling Menu:

   • Identifies the most popular menu items based on total sales quantity.

   SELECT
      md.menu_name,
      SUM(r.quantity) AS total_sales
   FROM receipt r 
   JOIN menu_detail md ON r.menu_id = md.menu_id
   GROUP BY 1
   ORDER BY 2 DESC;

   

2. Sales by Week:

   • Analyzes sales trends on a weekly basis, calculating the total sales amount.

   SELECT 
      strftime('%W', order_date) AS week_number,
      SUM((r.quantity * md.price)) AS total_sales
   FROM receipt r
   JOIN menu_detail md ON r.menu_id = md.menu_id
   GROUP BY 1;

   

3. Top Spending Customer:

   • Identifies customers who spent the most by calculating their total spending.

   SELECT
      r.customer_id,
      c.firstname || ' ' || c.lastname AS full_name,
      SUM((r.quantity *  md.price)) AS total_spending
   FROM receipt r
   JOIN menu_detail md ON r.menu_id = md.menu_id
   JOIN customer c ON r.customer_id = c.customer_id
   GROUP BY 1
   ORDER BY 3 DESC;

   

4. Average Order Amount:

   • Calculates the average order quantity.

   SELECT
      AVG(quantity) AS avg_order_size
   FROM receipt;

   

5. Employee Handling Most Orders:

   • Determines which employee processed the highest number of orders.

   SELECT
      r.employee_id,
      e.firstname || ' ' || e.lastname AS full_name,
      COUNT(r.employee_id) AS no_order
   FROM receipt r
   JOIN employee e ON r.employee_id = e.employee_id
   GROUP BY 1
   ORDER BY 3 DESC;

   

6. Ingredient Usage Analysis:

   • Analyzes ingredient usage, highlighting the most frequently used ingredients.

   SELECT
      i.ing_id,
      i.ing_name,
      (SELECT SUM(r.quantity) FROM receipt r WHERE r.menu_id IN (SELECT fr.menu_id FROM food_recipe fr WHERE fr.ing_id = i.ing_id)) AS total_quantity_used
   FROM ingredient i
   ORDER BY total_quantity_used DESC;