This repository contains a series of SQL practice exercises focused on working with a restaurant's menu items and order details. It covers basic to advanced SQL topics, including:
1. Convert Dataset to SQL Database:
- Create SQL statements to define and populate tables for menu_details and order_details using the provided dataset.
2. Basic SELECT Queries:
- Retrieve all columns from the menu_items table.
select * from menu_items
- Display the first 5 rows from the order_details table.
select top 5 * from order_details
3. Filtering and Sorting:
- Select the item_name and price columns for items in the 'Main Course' category. - Sort the result by price in descending order.
select item_name,price from menu_items where category='Main Course' order by price desc
4. Aggregate Functions:
- Calculate the average price of menu items.
select AVG(price) as average_price from menu_items
- Find the total number of orders placed.
select count(order_details_id) as total_place from order_details
5. Joins:
- Retrieve the item_name, order_date, and order_time for all items in the order_details table, including their respective menu item details.
select m.item_name, o.order_date, o.order_time from menu_items m left join order_details o on m.menu_item_id = o.item_id
6. Subqueries:
- List the menu items (item_name) with a price greater than the average price of all menu items.
select item_name from menu_items where price > (select avg(price) from menu_items)
7. Date and Time Functions:
- Extract the month from the order_date and count the number of orders placed in each month.
select count(order_id) as count_order, month(order_date) as month_order from order_details group by month(order_date)
8. Group By and Having:
- Show the categories with the average price greater than $15.
select category from menu_items group by category having avg(price) > 15
- Include the count of items in each category.
select category, count(item_name) as count_item from menu_items group by category
9. Conditional Statements:
- Display the item_name and price, and indicate if the item is priced above $20 with a new column named 'Expensive'.
select item_name, price, case when price > 20 then 'yes' else 'no' end as expensive from menu_items
10. Data Modification - Update:
- Update the price of the menu item with item_id = 101 to $25.
update menu_items set menu_item_id = 101 where price = 25
11. Data Modification - Insert:
- Insert a new record into the menu_items table for a dessert item.
insert into menu_items (menu_item_id, item_name, category, price) values (133, 'Dessert', 'American', 20)
12. Data Modification - Delete:
- Delete all records from the order_details table where the order_id is less than 100.
delete from order_details where order_id < 100
13. Window Functions - Rank:
- Rank menu items based on their prices, displaying the item_name and its rank.
select item_name, price, rank() over(order by price desc) as price_rank from menu_items
14. Window Functions - Lag and Lead:
- Display the item_name and the price difference from the previous and next menu item.
select item_name, price, price - lag(price) over(order by price desc) as previous_price, lead(price) over(order by price desc) - price as next_price from menu_items
15. Common Table Expressions (CTE):
- Create a CTE that lists menu items with prices above $15.
with Expensive as (select item_name, price from menu_items where price > 15)
select * from Expensive
- Use the CTE to retrieve the count of such items.
with Expensive as (select item_name, price from menu_items where price > 15)
select count(*) as count_items from Expensive
16. Advanced Joins:
- Retrieve the order_id, item_name, and price for all orders with their respective menu item details.
select order_id, item_name, price from menu_items m left join order_details o on m.menu_item_id = o.item_id
- Include rows even if there is no matching menu item.
select order_id, item_name, price from order_details o left join menu_items m on m.menu_item_id = o.item_id
17. Unpivot Data:
- Unpivot the menu_items table to show a list of menu item properties (item_id, item_name, category, price).
select menu_item_id, property, property_value from (select menu_item_id, CAST(item_name AS VARCHAR(25)) AS item_name, CAST(category AS VARCHAR(25)) AS category, CAST(price AS VARCHAR(25)) AS price from menu_items) as menu unpivot (property_value for property in(item_name, category, price)) as unpvt
18. Dynamic SQL:
- Write a dynamic SQL query that allows users to filter menu items based on category and price range.
declare
@categoryfilter varchar(25) = 'American',
@minprice decimal(10,2) = 15.00,
@maxprice decimal(10,2) = 30.00,
@sql nvarchar(max);
set @sql = N'select menu_item_id, item_name, category, price from menu_items
where category = @categoryfilter and price between @minprice and @maxprice'
exec sp_executesql @sql, N'@categoryfilter varchar(25), @minprice decimal(10,2), @maxprice decimal(10,2)', @categoryfilter, @minprice, @maxprice;
19. Stored Procedure:
- Create a stored procedure that takes a menu category as input and returns the average price for that category.
create procedure avg_price_by_category
@categoryfilter varchar(25) = 'American'
as
begin
select avg(price) as avg_price from menu_items where category = @categoryfilter
end
20. Triggers:
- Design a trigger that updates a log table whenever a new order is inserted into the order_details table.
create table order_log (log_id int identity primary key, order_id int, log_date datetime, log_message varchar(100))
create trigger after_insert_order_details
on order_details
after insert
as
begin
declare @order_id int;
select @order_id = order_id from inserted;
insert into order_log (order_id, log_date, log_message)
values (@order_id, getdate(), 'New order inserted into order_details');
end
Advanced SQL (optional)
21. Recursive Common Table Expressions (CTE):
- Implement a recursive CTE to display the hierarchy of menu items with their subcategories.
with menu_cte as ( select category as parent_name, null as child_name, 0 as level from menu_items where category is not null group by category
union all
select mi.category as parent_name, mi.item_name as child_name, 1 as level from menu_items mi where mi.category is not null)
select case when level = 0 then parent_name else replicate(' ', level) + child_name end as item_hierarchy from menu_cte order by parent_name, level, child_name;
22. Temporal Tables:
- Design a temporal table structure to track changes in menu item prices over time.
ALTER TABLE menu_items ADD
valid_from DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(),
valid_to DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (valid_from, valid_to);
ALTER TABLE menu_items SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.menu_items_history))
23. Database Transactions:
- Write a series of SQL statements within a transaction that ensures atomicity for updating menu item prices and inserting a new order.
DECLARE @OrderId INT = (SELECT ISNULL(MAX(order_id), 0) + 1 FROM order_details);
DECLARE @OrderDetailId INT = (SELECT ISNULL(MAX(order_details_id), 0) + 1 FROM order_details);
DECLARE @OrderDate DATE = CAST(GETDATE() AS DATE);
DECLARE @OrderTime TIME = CAST(GETDATE() AS TIME);
DECLARE @MenuItemId INT = 25;
DECLARE @NewPrice DECIMAL(10,2) = 18.99;
BEGIN TRANSACTION
BEGIN TRY
UPDATE menu_items SET price = @NewPrice WHERE menu_item_id = @MenuItemId;
INSERT INTO order_details (order_details_id, order_id, order_date, order_time, item_id) VALUES (@OrderDetailId, @OrderId, @OrderDate, @OrderTime, @MenuItemId);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH
24. Database Security:
- Create a role in the database and assign permissions to the role to restrict access to sensitive tables.
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'SensitiveDataReader')
BEGIN CREATE ROLE SensitiveDataReader END
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'Laman')
BEGIN CREATE USER Laman WITHOUT LOGIN END
ALTER ROLE SensitiveDataReader ADD MEMBER Laman;
GRANT SELECT ON dbo.menu_items TO SensitiveDataReader;
GRANT SELECT ON dbo.order_details TO SensitiveDataReader;
25. Advanced Indexing:
- Analyze the menu_items table and suggest an appropriate index to improve the performance of queries involving category-based filtering.
CREATE NONCLUSTERED INDEX idx_menu_items_category ON menu_items (category) INCLUDE (menu_item_id, item_name, price)