******************************************************************************************************************************************
//1. Login Form
//• Given the username and password, retrieve the matching user ID and avatar image/graphic URL
//• If the username and password pair do not match any records in the database, return an empty result set
******************************************************************************************************************************************

mysql> SELECT u_id, imgURL FROM users WHERE email='sample2@apple.com' and pswd='passw2';
+------+----------------------+
| u_id | imgURL               |
+------+----------------------+
|    2 | https://sample2.test |
+------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT u_id, imgURL FROM users WHERE email='sample1@google.com' and pswd='passw1';
+------+----------------------+
| u_id | imgURL               |
+------+----------------------+
|    1 | https://sample1.test |
+------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT u_id, imgURL FROM users WHERE email='fakeid' and pswd='fakepassword';
Empty set (0.00 sec)

//----------------------------------------------------------------------------------------------------------------------------------------

******************************************************************************************************************************************
//2. Grocery List Page
//• Retrieve up to 10 of the groceries, ordered by the number available grocery unit count (grocery with zero available item first) and then by the posting date/time (most recent first); this should include the title and description of the grocery, the date/time it was posted, the avatar and name of the author. The query should only retrieve groceries posted for logged in user’s family
******************************************************************************************************************************************

mysql> 
mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (3, 2, 2, 'Doughnut', 'creamy doughnuts', NOW(), 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (4, 3, 3, 'Peas', 'Sweet peas', NOW(), 10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (5, 1, 1, 'Milk', 'Chocolate mil', NOW(), 9);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (6, 2, 2, 'Zuchini', 'Veggies', NOW(), 7);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (7, 3, 3, 'Corn', 'Creamy mushrooms', NOW(), 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (8, 1, 1, 'Chips', 'Onion chips', NOW(), 19);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (9, 3, 3, 'Onions', 'Red Onions', NOW(), 3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (10, 2, 2, 'Pepsi', 'Cold drink', NOW(), 6);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO glist (g_id, u_id, f_id, title, descript, dt, qty) VALUES (11, 3, 3, 'Coke', 'R doughnuts', NOW(), 3);
Query OK, 1 row affected (0.00 sec)
//----------------------------------------------------------------------------------------------------------------------------

mysql> SELECT glist.title, glist.descript, glist.dt, users.imgURL, users.uname FROM glist, users 
    -> WHERE (glist.u_id = users.u_id) and (glist.u_id=1)
    -> ORDER BY glist.qty ASC, glist.dt DESC
    -> LIMIT 10;
+--------+----------------+---------------------+----------------------+-------+
| title  | descript       | dt                  | imgURL               | uname |
+--------+----------------+---------------------+----------------------+-------+
| Potato | yummy potatoes | 2021-08-04 19:13:36 | https://sample1.test | Dora  |
| Tomato | tangy tomatoes | 2021-08-04 19:13:36 | https://sample1.test | Dora  |
| Milk   | Chocolate mil  | 2021-08-04 19:54:48 | https://sample1.test | Dora  |
| Chips  | Onion chips    | 2021-08-04 19:54:48 | https://sample1.test | Dora  |
+--------+----------------+---------------------+----------------------+-------+
4 rows in set (0.03 sec)

//----------------------------------------------------------------------------------------------------------------------------------------

******************************************************************************************************************************************
//3. Family Page
//• Retrieve all families created by other users to show in the dropdown list.
******************************************************************************************************************************************

mysql> SELECT DISTINCT fname FROM family ;
+-------+
| fname |
+-------+
| Singh |
| Gnome |
| Xyz   |
+-------+
3 rows in set (0.01 sec)

//----------------------------------------------------------------------------------------------------------------------------------------