69_script2.sql

-- Use the correct database
USE assign2db;


-- -----------------------------
-- Part 1 SQL Updates

-- Show all data in menuitem table before update
SELECT * FROM menuitem;

-- Update Pasta alla Brenda to Pasta alla Norma and set it as vegetarian
UPDATE menuitem
SET dishname = 'Pasta alla Norma', veggie = 'Y'
WHERE dishname = 'Pasta alla Brenda';

-- Update orders delivered by driver with first name 'Marge' to have a delivery rating of 3
UPDATE cusorder
JOIN driver ON cusorder.driverid = driver.driverid
SET cusorder.deliveryrating = 3
WHERE driver.firstname = 'Marge';

-- Show all data in menuitem and cusorder table after update
SELECT * FROM menuitem;
SELECT * FROM cusorder;


-- -----------------------------
-- Part 2 SQL Inserts

-- Insert favorite TV character into customer table
INSERT INTO customer (cusid, firstname, lastname, cellnum) 
VALUES ('C999', 'Walter', 'White', '5559998888');

-- Insert another TV character as a driver
INSERT INTO driver (driverid, firstname, lastname, cellnum) 
VALUES ('D999', 'Jesse', 'Pinkman', '5557776666');

-- Insert favorite food into menuitem table
INSERT INTO menuitem (menuitemid, dishname, price, caloriecount, veggie) 
VALUES ('M999', 'Los Pollos Hermanos Chicken', 12.99, 850, 'N');

-- Insert a new order into cusorder table
INSERT INTO cusorder (orderid, deladdress, dateplaced, timeplaced, timedelivered, pickuporder, deliveryrating, driverid, cusid) 
VALUES ('O999', '308 Negra Arroyo Lane', '2025-05-12', '18:00:00', '19:00:00', 'N', 5, 'D999', 'C999');

-- Link the order with the menu item in overallorder
INSERT INTO overallorder (orderid, menuitemid, quantity) 
VALUES ('O999', 'M999', 3);

-- Show data to verify inserts
SELECT * FROM customer;
SELECT * FROM driver;
SELECT * FROM menuitem;
SELECT * FROM cusorder;
SELECT * FROM overallorder;


-- -----------------------------
-- Part 3 SQL Queries

-- Query 1: Show the last names of all the drivers
SELECT lastname FROM driver;

-- Query 2: Show the last names of all the drivers with no repeats
SELECT DISTINCT lastname FROM driver;

-- Query 3: Show all the data in the menuitem table, ordered by calorie count (ascending)
SELECT * FROM menuitem ORDER BY caloriecount ASC;

-- Query 4: Show vegetarian menu items (dish name, calorie count, price), ordered by price (ascending)
SELECT dishname, caloriecount, price FROM menuitem WHERE veggie = 'Y' ORDER BY price ASC;

-- Query 5: List orderid, delivery address, and delivery rating for orders delivered by a driver with last name 'Simpson'
SELECT cusorder.orderid, deladdress, deliveryrating FROM cusorder
JOIN driver ON cusorder.driverid = driver.driverid
WHERE driver.lastname = 'Simpson';

-- Query 6: List first and last names of all drivers who haven't delivered any orders
SELECT firstname, lastname FROM driver
WHERE driverid NOT IN (SELECT DISTINCT driverid FROM cusorder WHERE driverid IS NOT NULL);

-- Query 7: List customer name, orderid, order date, and driver name (or null if no driver assigned)
SELECT customer.firstname, customer.lastname, cusorder.orderid, cusorder.dateplaced, driver.firstname AS driver_fname, driver.lastname AS driver_lname
FROM cusorder
LEFT JOIN customer ON cusorder.cusid = customer.cusid
LEFT JOIN driver ON cusorder.driverid = driver.driverid;

-- Query 8: List orderid, order date, menu item name, price, and quantity, ordered by orderid
SELECT cusorder.orderid, cusorder.dateplaced, menuitem.dishname, menuitem.price, overallorder.quantity
FROM overallorder
JOIN menuitem ON overallorder.menuitemid = menuitem.menuitemid
JOIN cusorder ON overallorder.orderid = cusorder.orderid
ORDER BY cusorder.orderid;

-- Query 9: Count of deliveries per driver, including new drivers with zero deliveries, ordered by most deliveries
SELECT driver.firstname, driver.lastname, COUNT(cusorder.orderid) AS deliveries
FROM driver
LEFT JOIN cusorder ON driver.driverid = cusorder.driverid
GROUP BY driver.driverid
ORDER BY deliveries DESC;

-- Query 10: Cost of items in orders sent to '20 Main Street'
SELECT overallorder.orderid, menuitem.dishname, overallorder.quantity, menuitem.price, 
       CONCAT('$', FORMAT(menuitem.price * overallorder.quantity, 2)) AS 'Total Cost For This Item'
FROM overallorder
JOIN menuitem ON overallorder.menuitemid = menuitem.menuitemid
JOIN cusorder ON overallorder.orderid = cusorder.orderid
WHERE cusorder.deladdress = '20 Main Street'
ORDER BY overallorder.orderid;

-- Query 11: Customers who placed all-vegetarian orders
SELECT DISTINCT customer.firstname, customer.lastname, cusorder.orderid, menuitem.dishname
FROM customer
JOIN cusorder ON customer.cusid = cusorder.cusid
JOIN overallorder ON cusorder.orderid = overallorder.orderid
JOIN menuitem ON overallorder.menuitemid = menuitem.menuitemid
WHERE cusorder.orderid NOT IN (
    SELECT overallorder.orderid FROM overallorder
    JOIN menuitem ON overallorder.menuitemid = menuitem.menuitemid
    WHERE menuitem.veggie = 'N');

-- Query 12: Customer who spent the most money on one order
SELECT customer.firstname, customer.lastname, 
       CONCAT('$', FORMAT(SUM(menuitem.price * overallorder.quantity), 2)) AS 'Total Spent'
FROM customer
JOIN cusorder ON customer.cusid = cusorder.cusid
JOIN overallorder ON cusorder.orderid = overallorder.orderid
JOIN menuitem ON overallorder.menuitemid = menuitem.menuitemid
GROUP BY cusorder.orderid
ORDER BY SUM(menuitem.price * overallorder.quantity) DESC
LIMIT 1;

-- Query 13: Drivers who have never delivered an order containing 'Beef Lasagna'
SELECT DISTINCT driver.firstname, driver.lastname FROM driver
WHERE driver.driverid NOT IN (
    SELECT DISTINCT cusorder.driverid FROM cusorder
    JOIN overallorder ON cusorder.orderid = overallorder.orderid
    JOIN menuitem ON overallorder.menuitemid = menuitem.menuitemid
    WHERE menuitem.dishname = 'Beef Lasagna');

-- Query 14: Most popular dishes (ordered at least 6 times)
SELECT menuitem.dishname, menuitem.menuitemid, COUNT(overallorder.orderid) AS order_count, SUM(overallorder.quantity) AS total_quantity
FROM overallorder
JOIN menuitem ON overallorder.menuitemid = menuitem.menuitemid
GROUP BY menuitem.menuitemid
HAVING total_quantity >= 6
ORDER BY total_quantity DESC;

-- Query 15: Custom Query - List all customers who have placed more than 3 orders
SELECT customer.firstname, customer.lastname, COUNT(cusorder.orderid) AS total_orders
FROM customer
JOIN cusorder ON customer.cusid = cusorder.cusid
GROUP BY customer.cusid
HAVING total_orders > 3
ORDER BY total_orders DESC;



-- -----------------------------
-- Part 4 SQL Views/Deletes

-- Create a view showing driver details, order ID, time placed, and time delivered
CREATE VIEW driver_orders AS
SELECT driver.driverid, driver.firstname, driver.lastname, cusorder.deliveryrating, cusorder.orderid, cusorder.timeplaced, cusorder.timedelivered
FROM driver
JOIN cusorder ON driver.driverid = cusorder.driverid;

-- Prove the view works by selecting orders delivered within 1 hour
SELECT * FROM driver_orders
WHERE TIMESTAMPDIFF(MINUTE, timeplaced, timedelivered) <= 60;

-- Show all rows and columns in the drivers table
SELECT * FROM driver;

-- Delete the driver with driverid 'D666'
DELETE FROM driver WHERE driverid = 'D666';

-- Prove the driver was deleted
SELECT * FROM driver;

-- Count the number of drivers before next delete
SELECT COUNT(*) AS driver_count FROM driver;

-- Delete the driver with driverid 'D333'
DELETE FROM driver WHERE driverid = 'D333';

-- Count the number of drivers again to see if it worked
SELECT COUNT(*) AS driver_count FROM driver;

-- Explanation: A driver can only be deleted if they have no associated orders due to foreign key constraints. If deletion fails, it means the driver is referenced in cusorder.