====== SQL examples ======

Just useful code snippets, examples not functional code.


===== Table columns =====

<code sql>
MariaDB [accesscontrol]> select * from keycards;
+--------+------------+---------------------+---------------------+----------+
| cardID | cardNumber | Created             | Deleted             | issuedTo |
+--------+------------+---------------------+---------------------+----------+
|      1 | 1234       | 2024-05-07 12:28:20 | 0000-00-00 00:00:00 |        3 |
|      2 | 2345       | 2024-05-07 12:28:32 | 0000-00-00 00:00:00 |        3 |
|      3 | 3456       | 2024-05-07 12:28:45 | 0000-00-00 00:00:00 |        1 |
|      4 | 4567       | 2024-05-07 12:28:54 | 0000-00-00 00:00:00 |        2 |
+--------+------------+---------------------+---------------------+----------+


> select * from users;
+--------+-----------+-----------+----------+-----+---------------------+---------------------+
| userID | username  | firstname | surname  | pin | created             | deleted             |
+--------+-----------+-----------+----------+-----+---------------------+---------------------+
|      1 | as        | A*        | S*       |   0 | 2024-05-07 11:29:25 | 0000-00-00 00:00:00 |
|      2 | os        | O*        | S*       |   0 | 2024-05-07 11:30:30 | 0000-00-00 00:00:00 |
|      3 | hs        | H*        | S*       |   0 | 2024-05-07 11:31:02 | 0000-00-00 00:00:00 |
+--------+-----------+-----------+----------+-----+---------------------+---------------------+

> SELECT * FROM access;
+----------+--------+-------------+--------+
| accessID | doorID | accesslevel | cardID |
+----------+--------+-------------+--------+
|        1 |      1 | allowed     |      1 |
|        2 |      1 | denied      |      2 |
|        3 |      1 | denied      |      3 |
|        4 |      2 | denied      |      1 |
|        5 |      2 | allowed     |      2 |
|        6 |      2 | allowed     |      3 |
+----------+--------+-------------+--------+

</code>


===== Useful selects =====

<code sql>

> select username from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'hs';
+----------+
| username |
+----------+
| hs       |
| hs       |
+----------+

> select username, keycards.cardNumber from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'os';
+----------+------------+
| username | cardNumber |
+----------+------------+
| os       | 4567       |
+----------+------------+
1 row in set (0.000 sec)

> select username, keycards.cardNumber from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'hs';
+----------+------------+
| username | cardNumber |
+----------+------------+
| hs       | 1234       |
| hs       | 2345       |
+----------+------------+
2 rows in set (0.000 sec)



> SELECT * FROM access JOIN keycards ON access.userID = keycards.userID \G;
*************************** 1. row ***************************
   accessID: 1
     userID: 1
accesslevel: {
        "description": "access",
        "front_door": "allowed",
        "cellar_door": "denied"
}
     cardID: 3
 cardNumber: 3456
    Created: 2024-05-07 12:28:45
    Deleted: 0000-00-00 00:00:00
     userID: 1
*************************** 2. row ***************************
   accessID: 2
     userID: 2
accesslevel: {
        "description": "access",
        "front_door": "denied",
        "cellar_door": "denied"
}
     cardID: 4
 cardNumber: 4567
    Created: 2024-05-07 12:28:54
    Deleted: 0000-00-00 00:00:00
     userID: 2
2 rows in set (0.079 sec)

</code>


With WHERE to select just one card

<code>
SELECT `accesslevel` 
FROM `access` 
JOIN `keycards` ON `access`.`cardID` = `keycards`.`cardID` 
WHERE `keycards`.`cardID` = 2 ;


{
1 : "denied",
2 : "denied",
3 : "allowed"
}
</code>


<code sql>
> select keycards.cardNumber, users.username
    -> from keycards
    -> inner join users on keycards.userID = users.userID;
+------------+-----------+
| cardNumber | username  |
+------------+-----------+
| 1234       | hds       |
| 2345       | hds       |
| 3456       | as        |
| 4567       | oas       |
+------------+-----------+
</code>