This project is a comprehensive analysis of restaurant data using MySQL. It involves transforming a provided dataset into a relational database and executing various SQL queries to extract meaningful insights. The tasks cover a wide range of SQL functionalities, including basic SELECT queries, filtering, sorting, aggregate functions, joins, subqueries, date and time functions, data modification, window functions, common table expressions (CTE), and advanced joins.
Below are the tasks completed in this project:
-
Convert Dataset to SQL Database:
- Defined and populated tables for
menu_details
andorder_details
using the provided dataset.
- Defined and populated tables for
-
Basic SELECT Queries:
- Retrieved all columns from the
menu_items
table. - Displayed the first 5 rows from the
order_details
table.
- Retrieved all columns from the
-
Filtering and Sorting:
- Selected the
item_name
andprice
columns for items in the 'Main Course' category, sorted the result by price in descending order.
- Selected the
-
Aggregate Functions:
- Calculated the average price of menu items.
- Found the total number of orders placed.
-
Joins:
- Retrieved
item_name
,order_date
, andorder_time
for all items in theorder_details
table, including their respective menu item details.
- Retrieved
-
Subqueries:
- Listed the menu items (
item_name
) with a price greater than the average price of all menu items.
- Listed the menu items (
-
Date and Time Functions:
- Extracted the month from the
order_date
and counted the number of orders placed in each month.
- Extracted the month from the
-
Group By and Having:
- Showed the categories with the average price greater than $15, including the count of items in each category.
-
Conditional Statements:
- Displayed
item_name
andprice
, indicating if the item is priced above $20 with a new column named 'Expensive'.
- Displayed
-
Data Modification - Update:
- Updated the price of the menu item with
item_id = 101
to $25.
- Updated the price of the menu item with
-
Data Modification - Insert:
- Inserted a new record into the
menu_items
table for a dessert item.
- Inserted a new record into the
-
Data Modification - Delete:
- Deleted all records from the
order_details
table where theorder_id
is less than 100.
- Deleted all records from the
-
Window Functions - Rank:
- Ranked menu items based on their prices, displaying the
item_name
and its rank.
- Ranked menu items based on their prices, displaying the
-
Window Functions - Lag and Lead:
- Displayed the
item_name
and the price difference from the previous and next menu item.
- Displayed the
-
Common Table Expressions (CTE):
- Created a CTE that lists menu items with prices above $15, used the CTE to retrieve the count of such items.
-
Advanced Joins:
- Retrieved the
order_id
,item_name
, andprice
for all orders with their respective menu item details, including rows even if there is no matching menu item.
- Retrieved the