69_outputscript2.txt

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SELECT * FROM menuitem
--------------

+------------+------------------------+-------+--------------+--------+
| menuitemid | dishname               | price | caloriecount | veggie |
+------------+------------------------+-------+--------------+--------+
| MAAA       | Beef Lasagna           | 22.50 |          610 | N      |
| MBBB       | Spagetti and Meatballs | 21.50 |          490 | N      |
| MCCC       | Ratatouille            | 19.95 |          370 | Y      |
| MDDD       | Margherita Pizza       | 22.50 |          505 | Y      |
| MEEE       | Gnocchi Pomodoro       | 18.95 |          490 | Y      |
| MFFF       | Chicken Marsala        | 25.00 |          390 | N      |
| MGGG       | Chicken Parmesan       | 26.50 |          620 | N      |
| MHHH       | Pasta alla Brenda      | 16.80 |          500 | N      |
+------------+------------------------+-------+--------------+--------+
8 rows in set (0.00 sec)

--------------
UPDATE menuitem
SET dishname = 'Pasta alla Norma', veggie = 'Y'
WHERE dishname = 'Pasta alla Brenda'
--------------

Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--------------
UPDATE cusorder
JOIN driver ON cusorder.driverid = driver.driverid
SET cusorder.deliveryrating = 3
WHERE driver.firstname = 'Marge'
--------------

Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

--------------
SELECT * FROM menuitem
--------------

+------------+------------------------+-------+--------------+--------+
| menuitemid | dishname               | price | caloriecount | veggie |
+------------+------------------------+-------+--------------+--------+
| MAAA       | Beef Lasagna           | 22.50 |          610 | N      |
| MBBB       | Spagetti and Meatballs | 21.50 |          490 | N      |
| MCCC       | Ratatouille            | 19.95 |          370 | Y      |
| MDDD       | Margherita Pizza       | 22.50 |          505 | Y      |
| MEEE       | Gnocchi Pomodoro       | 18.95 |          490 | Y      |
| MFFF       | Chicken Marsala        | 25.00 |          390 | N      |
| MGGG       | Chicken Parmesan       | 26.50 |          620 | N      |
| MHHH       | Pasta alla Norma       | 16.80 |          500 | Y      |
+------------+------------------------+-------+--------------+--------+
8 rows in set (0.00 sec)

--------------
SELECT * FROM cusorder
--------------

+---------+-----------------+------------+------------+---------------+-------------+----------------+----------+-------+
| orderid | deladdress      | dateplaced | timeplaced | timedelivered | pickuporder | deliveryrating | driverid | cusid |
+---------+-----------------+------------+------------+---------------+-------------+----------------+----------+-------+
| C100    | 20 Main Street  | 2025-01-01 | 17:30:00   | 17:50:00      | N           |              3 | D222     | CRRR  |
| C111    | 24 Elm Street   | 2025-02-19 | 17:00:00   | 18:00:00      | N           |              3 | D222     | CE66  |
| C122    | 5 Oxford Street | 2024-11-21 | 18:15:00   | 18:59:00      | N           |              3 | D222     | CK78  |
| C133    | 29 Western Road | 2025-02-21 | 17:00:00   | 19:50:00      | N           |              1 | D333     | CK78  |
| C144    | 2 Ridout Street | 2025-01-01 | 17:30:00   | NULL          | Y           |           NULL | NULL     | CIT4  |
| C155    | 20 Main Street  | 2025-02-02 | 17:30:00   | 17:50:00      | N           |              5 | D333     | CRRR  |
+---------+-----------------+------------+------------+---------------+-------------+----------------+----------+-------+
6 rows in set (0.00 sec)

--------------
INSERT INTO customer (cusid, firstname, lastname, cellnum) 
VALUES ('C999', 'Walter', 'White', '5559998888')
--------------

Query OK, 1 row affected (0.03 sec)

--------------
INSERT INTO driver (driverid, firstname, lastname, cellnum) 
VALUES ('D999', 'Jesse', 'Pinkman', '5557776666')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
INSERT INTO menuitem (menuitemid, dishname, price, caloriecount, veggie) 
VALUES ('M999', 'Los Pollos Hermanos Chicken', 12.99, 850, 'N')
--------------

Query OK, 1 row affected (0.03 sec)

--------------
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')
--------------

ERROR 1406 (22001) at line 43: Data too long for column 'deladdress' at row 1
--------------
INSERT INTO overallorder (orderid, menuitemid, quantity) 
VALUES ('O999', 'M999', 3)
--------------

ERROR 1452 (23000) at line 47: Cannot add or update a child row: a foreign key constraint fails (`assign2db`.`overallorder`, CONSTRAINT `overallorder_ibfk_1` FOREIGN KEY (`orderid`) REFERENCES `cusorder` (`orderid`))
--------------
SELECT * FROM customer
--------------

+-------+-----------+-----------+------------+
| cusid | firstname | lastname  | cellnum    |
+-------+-----------+-----------+------------+
| C999  | Walter    | White     | 5559998888 |
| CE66  | Colleen   | Tyler     | 5197776666 |
| CGD5  | Sean      | Aziz      | 5196817509 |
| CIT4  | Scott     | Mortensen | 5196726721 |
| CK78  | Jon       | Joselyn   | 9051234444 |
| CRRR  | Sue       | Tanaka    | 5196817624 |
| CT67  | Gerry     | Webster   | 5488887524 |
+-------+-----------+-----------+------------+
7 rows in set (0.00 sec)

--------------
SELECT * FROM driver
--------------

+----------+-----------+----------+------------+
| driverid | firstname | lastname | cellnum    |
+----------+-----------+----------+------------+
| D111     | Homer     | Simpson  | 5191111111 |
| D222     | Marge     | Simpson  | 5192222222 |
| D333     | Bart      | Simpson  | 5193333333 |
| D444     | Lisa      | Simpson  | 9054444444 |
| D555     | Maggie    | Simpson  | 5488885555 |
| D666     | Ned       | Flanders | 5197776666 |
| D999     | Jesse     | Pinkman  | 5557776666 |
+----------+-----------+----------+------------+
7 rows in set (0.00 sec)

--------------
SELECT * FROM menuitem
--------------

+------------+-----------------------------+-------+--------------+--------+
| menuitemid | dishname                    | price | caloriecount | veggie |
+------------+-----------------------------+-------+--------------+--------+
| M999       | Los Pollos Hermanos Chicken | 12.99 |          850 | N      |
| MAAA       | Beef Lasagna                | 22.50 |          610 | N      |
| MBBB       | Spagetti and Meatballs      | 21.50 |          490 | N      |
| MCCC       | Ratatouille                 | 19.95 |          370 | Y      |
| MDDD       | Margherita Pizza            | 22.50 |          505 | Y      |
| MEEE       | Gnocchi Pomodoro            | 18.95 |          490 | Y      |
| MFFF       | Chicken Marsala             | 25.00 |          390 | N      |
| MGGG       | Chicken Parmesan            | 26.50 |          620 | N      |
| MHHH       | Pasta alla Norma            | 16.80 |          500 | Y      |
+------------+-----------------------------+-------+--------------+--------+
9 rows in set (0.00 sec)

--------------
SELECT * FROM cusorder
--------------

+---------+-----------------+------------+------------+---------------+-------------+----------------+----------+-------+
| orderid | deladdress      | dateplaced | timeplaced | timedelivered | pickuporder | deliveryrating | driverid | cusid |
+---------+-----------------+------------+------------+---------------+-------------+----------------+----------+-------+
| C100    | 20 Main Street  | 2025-01-01 | 17:30:00   | 17:50:00      | N           |              3 | D222     | CRRR  |
| C111    | 24 Elm Street   | 2025-02-19 | 17:00:00   | 18:00:00      | N           |              3 | D222     | CE66  |
| C122    | 5 Oxford Street | 2024-11-21 | 18:15:00   | 18:59:00      | N           |              3 | D222     | CK78  |
| C133    | 29 Western Road | 2025-02-21 | 17:00:00   | 19:50:00      | N           |              1 | D333     | CK78  |
| C144    | 2 Ridout Street | 2025-01-01 | 17:30:00   | NULL          | Y           |           NULL | NULL     | CIT4  |
| C155    | 20 Main Street  | 2025-02-02 | 17:30:00   | 17:50:00      | N           |              5 | D333     | CRRR  |
+---------+-----------------+------------+------------+---------------+-------------+----------------+----------+-------+
6 rows in set (0.00 sec)

--------------
SELECT * FROM overallorder
--------------

+---------+------------+----------+
| orderid | menuitemid | quantity |
+---------+------------+----------+
| C100    | MBBB       |        4 |
| C100    | MCCC       |        2 |
| C111    | MAAA       |        3 |
| C111    | MEEE       |        5 |
| C122    | MCCC       |        1 |
| C133    | MAAA       |        2 |
| C133    | MBBB       |        1 |
| C133    | MEEE       |        2 |
| C133    | MHHH       |        1 |
| C144    | MHHH       |        3 |
| C155    | MCCC       |        4 |
| C155    | MEEE       |        3 |
| C155    | MHHH       |        2 |
+---------+------------+----------+
13 rows in set (0.00 sec)

--------------
SELECT lastname FROM driver
--------------

+----------+
| lastname |
+----------+
| Simpson  |
| Simpson  |
| Simpson  |
| Simpson  |
| Simpson  |
| Flanders |
| Pinkman  |
+----------+
7 rows in set (0.00 sec)

--------------
SELECT DISTINCT lastname FROM driver
--------------

+----------+
| lastname |
+----------+
| Simpson  |
| Flanders |
| Pinkman  |
+----------+
3 rows in set (0.00 sec)

--------------
SELECT * FROM menuitem ORDER BY caloriecount ASC
--------------

+------------+-----------------------------+-------+--------------+--------+
| menuitemid | dishname                    | price | caloriecount | veggie |
+------------+-----------------------------+-------+--------------+--------+
| MCCC       | Ratatouille                 | 19.95 |          370 | Y      |
| MFFF       | Chicken Marsala             | 25.00 |          390 | N      |
| MBBB       | Spagetti and Meatballs      | 21.50 |          490 | N      |
| MEEE       | Gnocchi Pomodoro            | 18.95 |          490 | Y      |
| MHHH       | Pasta alla Norma            | 16.80 |          500 | Y      |
| MDDD       | Margherita Pizza            | 22.50 |          505 | Y      |
| MAAA       | Beef Lasagna                | 22.50 |          610 | N      |
| MGGG       | Chicken Parmesan            | 26.50 |          620 | N      |
| M999       | Los Pollos Hermanos Chicken | 12.99 |          850 | N      |
+------------+-----------------------------+-------+--------------+--------+
9 rows in set (0.00 sec)

--------------
SELECT dishname, caloriecount, price FROM menuitem WHERE veggie = 'Y' ORDER BY price ASC
--------------

+------------------+--------------+-------+
| dishname         | caloriecount | price |
+------------------+--------------+-------+
| Pasta alla Norma |          500 | 16.80 |
| Gnocchi Pomodoro |          490 | 18.95 |
| Ratatouille      |          370 | 19.95 |
| Margherita Pizza |          505 | 22.50 |
+------------------+--------------+-------+
4 rows in set (0.00 sec)

--------------
SELECT cusorder.orderid, deladdress, deliveryrating FROM cusorder
JOIN driver ON cusorder.driverid = driver.driverid
WHERE driver.lastname = 'Simpson'
--------------

+---------+-----------------+----------------+
| orderid | deladdress      | deliveryrating |
+---------+-----------------+----------------+
| C100    | 20 Main Street  |              3 |
| C111    | 24 Elm Street   |              3 |
| C122    | 5 Oxford Street |              3 |
| C133    | 29 Western Road |              1 |
| C155    | 20 Main Street  |              5 |
+---------+-----------------+----------------+
5 rows in set (0.00 sec)

--------------
SELECT firstname, lastname FROM driver
WHERE driverid NOT IN (SELECT DISTINCT driverid FROM cusorder WHERE driverid IS NOT NULL)
--------------

+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Homer     | Simpson  |
| Lisa      | Simpson  |
| Maggie    | Simpson  |
| Ned       | Flanders |
| Jesse     | Pinkman  |
+-----------+----------+
5 rows in set (0.00 sec)

--------------
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
--------------

+-----------+-----------+---------+------------+--------------+--------------+
| firstname | lastname  | orderid | dateplaced | driver_fname | driver_lname |
+-----------+-----------+---------+------------+--------------+--------------+
| Sue       | Tanaka    | C100    | 2025-01-01 | Marge        | Simpson      |
| Colleen   | Tyler     | C111    | 2025-02-19 | Marge        | Simpson      |
| Jon       | Joselyn   | C122    | 2024-11-21 | Marge        | Simpson      |
| Jon       | Joselyn   | C133    | 2025-02-21 | Bart         | Simpson      |
| Scott     | Mortensen | C144    | 2025-01-01 | NULL         | NULL         |
| Sue       | Tanaka    | C155    | 2025-02-02 | Bart         | Simpson      |
+-----------+-----------+---------+------------+--------------+--------------+
6 rows in set (0.00 sec)

--------------
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
--------------

+---------+------------+------------------------+-------+----------+
| orderid | dateplaced | dishname               | price | quantity |
+---------+------------+------------------------+-------+----------+
| C100    | 2025-01-01 | Spagetti and Meatballs | 21.50 |        4 |
| C100    | 2025-01-01 | Ratatouille            | 19.95 |        2 |
| C111    | 2025-02-19 | Beef Lasagna           | 22.50 |        3 |
| C111    | 2025-02-19 | Gnocchi Pomodoro       | 18.95 |        5 |
| C122    | 2024-11-21 | Ratatouille            | 19.95 |        1 |
| C133    | 2025-02-21 | Beef Lasagna           | 22.50 |        2 |
| C133    | 2025-02-21 | Spagetti and Meatballs | 21.50 |        1 |
| C133    | 2025-02-21 | Gnocchi Pomodoro       | 18.95 |        2 |
| C133    | 2025-02-21 | Pasta alla Norma       | 16.80 |        1 |
| C144    | 2025-01-01 | Pasta alla Norma       | 16.80 |        3 |
| C155    | 2025-02-02 | Ratatouille            | 19.95 |        4 |
| C155    | 2025-02-02 | Gnocchi Pomodoro       | 18.95 |        3 |
| C155    | 2025-02-02 | Pasta alla Norma       | 16.80 |        2 |
+---------+------------+------------------------+-------+----------+
13 rows in set (0.00 sec)

--------------
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
--------------

+-----------+----------+------------+
| firstname | lastname | deliveries |
+-----------+----------+------------+
| Marge     | Simpson  |          3 |
| Bart      | Simpson  |          2 |
| Homer     | Simpson  |          0 |
| Lisa      | Simpson  |          0 |
| Maggie    | Simpson  |          0 |
| Ned       | Flanders |          0 |
| Jesse     | Pinkman  |          0 |
+-----------+----------+------------+
7 rows in set (0.00 sec)

--------------
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
--------------

+---------+------------------------+----------+-------+--------------------------+
| orderid | dishname               | quantity | price | Total Cost For This Item |
+---------+------------------------+----------+-------+--------------------------+
| C100    | Spagetti and Meatballs |        4 | 21.50 | $86.00                   |
| C100    | Ratatouille            |        2 | 19.95 | $39.90                   |
| C155    | Ratatouille            |        4 | 19.95 | $79.80                   |
| C155    | Gnocchi Pomodoro       |        3 | 18.95 | $56.85                   |
| C155    | Pasta alla Norma       |        2 | 16.80 | $33.60                   |
+---------+------------------------+----------+-------+--------------------------+
5 rows in set (0.00 sec)

--------------
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')
--------------

+-----------+-----------+---------+------------------+
| firstname | lastname  | orderid | dishname         |
+-----------+-----------+---------+------------------+
| Scott     | Mortensen | C144    | Pasta alla Norma |
| Jon       | Joselyn   | C122    | Ratatouille      |
| Sue       | Tanaka    | C155    | Ratatouille      |
| Sue       | Tanaka    | C155    | Gnocchi Pomodoro |
| Sue       | Tanaka    | C155    | Pasta alla Norma |
+-----------+-----------+---------+------------------+
5 rows in set (0.00 sec)

--------------
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
--------------

+-----------+----------+-------------+
| firstname | lastname | Total Spent |
+-----------+----------+-------------+
| Sue       | Tanaka   | $170.25     |
+-----------+----------+-------------+
1 row in set (0.00 sec)

--------------
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')
--------------

+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Homer     | Simpson  |
| Lisa      | Simpson  |
| Maggie    | Simpson  |
| Ned       | Flanders |
| Jesse     | Pinkman  |
+-----------+----------+
5 rows in set (0.00 sec)

--------------
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
--------------

+------------------+------------+-------------+----------------+
| dishname         | menuitemid | order_count | total_quantity |
+------------------+------------+-------------+----------------+
| Gnocchi Pomodoro | MEEE       |           3 |             10 |
| Ratatouille      | MCCC       |           3 |              7 |
| Pasta alla Norma | MHHH       |           3 |              6 |
+------------------+------------+-------------+----------------+
3 rows in set (0.00 sec)

--------------
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
--------------

Empty set (0.00 sec)

--------------
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
--------------

Query OK, 0 rows affected (0.03 sec)

--------------
SELECT * FROM driver_orders
WHERE TIMESTAMPDIFF(MINUTE, timeplaced, timedelivered) <= 60
--------------

+----------+-----------+----------+----------------+---------+------------+---------------+
| driverid | firstname | lastname | deliveryrating | orderid | timeplaced | timedelivered |
+----------+-----------+----------+----------------+---------+------------+---------------+
| D222     | Marge     | Simpson  |              3 | C100    | 17:30:00   | 17:50:00      |
| D222     | Marge     | Simpson  |              3 | C111    | 17:00:00   | 18:00:00      |
| D222     | Marge     | Simpson  |              3 | C122    | 18:15:00   | 18:59:00      |
| D333     | Bart      | Simpson  |              5 | C155    | 17:30:00   | 17:50:00      |
+----------+-----------+----------+----------------+---------+------------+---------------+
4 rows in set (0.00 sec)

--------------
SELECT * FROM driver
--------------

+----------+-----------+----------+------------+
| driverid | firstname | lastname | cellnum    |
+----------+-----------+----------+------------+
| D111     | Homer     | Simpson  | 5191111111 |
| D222     | Marge     | Simpson  | 5192222222 |
| D333     | Bart      | Simpson  | 5193333333 |
| D444     | Lisa      | Simpson  | 9054444444 |
| D555     | Maggie    | Simpson  | 5488885555 |
| D666     | Ned       | Flanders | 5197776666 |
| D999     | Jesse     | Pinkman  | 5557776666 |
+----------+-----------+----------+------------+
7 rows in set (0.00 sec)

--------------
DELETE FROM driver WHERE driverid = 'D666'
--------------

Query OK, 1 row affected (0.02 sec)

--------------
SELECT * FROM driver
--------------

+----------+-----------+----------+------------+
| driverid | firstname | lastname | cellnum    |
+----------+-----------+----------+------------+
| D111     | Homer     | Simpson  | 5191111111 |
| D222     | Marge     | Simpson  | 5192222222 |
| D333     | Bart      | Simpson  | 5193333333 |
| D444     | Lisa      | Simpson  | 9054444444 |
| D555     | Maggie    | Simpson  | 5488885555 |
| D999     | Jesse     | Pinkman  | 5557776666 |
+----------+-----------+----------+------------+
6 rows in set (0.00 sec)

--------------
SELECT COUNT(*) AS driver_count FROM driver
--------------

+--------------+
| driver_count |
+--------------+
|            6 |
+--------------+
1 row in set (0.01 sec)

--------------
DELETE FROM driver WHERE driverid = 'D333'
--------------

ERROR 1451 (23000) at line 185: Cannot delete or update a parent row: a foreign key constraint fails (`assign2db`.`cusorder`, CONSTRAINT `cusorder_ibfk_2` FOREIGN KEY (`driverid`) REFERENCES `driver` (`driverid`))
--------------
SELECT COUNT(*) AS driver_count FROM driver
--------------

+--------------+
| driver_count |
+--------------+
|            6 |
+--------------+
1 row in set (0.02 sec)

Bye