mysql> show tables;
+------------------+
| Tables_in_kab935 |
+------------------+
| Loggers          |
+------------------+
1 row in set (0.00 sec)

mysql> drop table Loggers;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE Loggers (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     first_name VARCHAR(50) NOT NULL,
    ->     last_name VARCHAR(50) NOT NULL,
    ->     username VARCHAR(50) UNIQUE NOT NULL,
    ->     password VARCHAR(255) NOT NULL,
    ->     dob DATE NOT NULL,
    ->     avatar_url VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE Logins (
    ->     login_id INT AUTO_INCREMENT PRIMARY KEY,
    ->     logger_id INT NOT NULL,
    ->     login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ->     FOREIGN KEY (logger_id) REFERENCES Loggers(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Loggers (first_name, last_name, username, password, dob, avatar_url) 
    -> VALUES 
    -> ('Jane', 'Doe', 'jd999', 'abc12345', '1990-08-15', 'abc.jpeg'),
    -> ('John', 'Doe', 'jd101', '123abcd', '1992-09-01', 'picture.jpeg');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO Logins (logger_id, login_time, ip_address)
    -> VALUES
    -> (1, '2022-05-05 14:00:00', '192.168.100.100'),
    -> (2, '2022-02-04 12:30:00', '191.198.111.101'),
    -> (3, '2022-06-10 8:30:00', '192.168.100.100'),
    -> (4, '2022-06-11 10:15:02', '192.168.100.100'),
    -> (5, '2022-08-15 9:15:31', '191.198.111.101'),
    -> (6, '2022-08-16 21:00:00', '191.198.111.101'),
    -> (7, '2022-08-16 21:01:00', '191.198.111.101');
ERROR 1054 (42S22): Unknown column 'ip_address' in 'field list'
mysql> ALTER TABLE Logins
    -> ADD COLUMN ip_address VARCHAR(50) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> -- Insert login data into the Logins table
mysql> INSERT INTO Logins (logger_id, login_time, ip_address) 
    -> VALUES
    -> (1, '2022-05-05 14:00:00', '192.168.100.100'),  -- Login 1 for Jane Doe
    -> (2, '2022-02-04 12:30:00', '191.198.111.101'),  -- Login 2 for John Doe
    -> (1, '2022-06-10 08:30:00', '192.168.100.100'),  -- Login 3 for Jane Doe
    -> (1, '2022-06-11 10:15:02', '192.168.100.100'),  -- Login 4 for Jane Doe
    -> (2, '2022-08-15 09:15:31', '191.198.111.101'),  -- Login 5 for John Doe
    -> (2, '2022-08-16 21:00:00', '191.198.111.101'),  -- Login 6 for John Doe
    -> (1, '2022-08-16 21:01:00', '191.198.111.101');  -- Login 7 for Jane Doe
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> UPDATE Loggers
    -> SET username = 'jd@uregina.ca'
    -> WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT id AS user_id, first_name, last_name
    -> FROM Loggers
    -> WHERE username = 'jd@uregina.ca' AND password = 'abc12345';
+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
|       1 | Jane       | Doe       |
+---------+------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT
    -> 1.id AS user_id,
    -> 2.first_name,
    -> grth;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS user_id,
2.first_name,
grth' at line 2
mysql> -- Display user's details and the number of times they have logged in
mysql> SELECT 
    ->     l.id AS user_id, 
    ->     l.first_name, 
    ->     l.last_name, 
    ->     COUNT(lo.login_id) AS login_count
    -> FROM Loggers l
    -> LEFT JOIN Logins lo ON l.id = lo.logger_id
    -> WHERE l.username = 'jd@uregina.ca' AND l.password = 'abc12345'
    -> GROUP BY l.id;
+---------+------------+-----------+-------------+
| user_id | first_name | last_name | login_count |
+---------+------------+-----------+-------------+
|       1 | Jane       | Doe       |           4 |
+---------+------------+-----------+-------------+
1 row in set (0.01 sec)

mysql> -- Display the last three logins in descending order
mysql> SELECT login_time, ip_address
    -> FROM Logins
    -> WHERE logger_id = 1
    -> ORDER BY login_time DESC
    -> LIMIT 3;
+---------------------+-----------------+
| login_time          | ip_address      |
+---------------------+-----------------+
| 2022-08-16 21:01:00 | 191.198.111.101 |
| 2022-06-11 10:15:02 | 192.168.100.100 |
| 2022-06-10 08:30:00 | 192.168.100.100 |
+---------------------+-----------------+
3 rows in set (0.00 sec)