Row Name | Data Type | Properties |
---|---|---|
itemname | TEXT | PRIMARY KEY NOT NULL |
class | TEXT | NOT NULL |
price | REAL | NOT NULL |
quantity | INTEGER | NOT NULL |
-
add an item
INSERT INTO inventory (itemname, class, price, quantity) VALUES ('sample-name', 'sample-class', 69, 499);
-
retrieve all items
SELECT * FROM inventory;
-
delete an item
DELETE FROM inventory WHERE itemname = 'sample-itemname',
-
edit an item
UPDATE inventory SET itemname = 'new-itemname', class = 'new-class', price = 999, quantity = 777 WHERE itemname = 'old-itemname'
-
add quantity count
UPDATE inventory SET quantity = quantity + 7 WHERE itemname = 'sample-itemname'
-
sub quantity count
UPDATE inventory SET quantity = quantity - 2 WHERE itemname = 'sample-itemname'
Row Name | Data Type | Properties |
---|---|---|
buydate | DATE | NOT NULL |
itemname | TEXT | NOT NULL |
class | TEXT | NOT NULL |
price | REAL | NOT NULL |
quantity | INTEGER | NOT NULL |
-
get current datetime.
DATETIME() OR DATETIME('now')
-
subtract (-) or add (+) from a date, you can also use the
'now'
as a date.DATETIME('2022-05-13T03:40:35.303Z', '+3 day', '-1 year', '-2 months', '+1 hour', '-2 minute', '+1 second');
-
get all transactions between two dates
SELECT * FROM transactions WHERE DATETIME(buydate) BETWEEN DATETIME(startdate) AND DATETIME(enddate)
Note that the
DATETIME
function was just used here because we need the same date types for the conditions and column values. You can use any sqlite3 date function modifier you want. If you can guarantee that the values are all just the same date type, then there is no need to use any date function modifier. -
record a transaction ; example:
INSERT INTO transactions (buydate, itemname, class, price, quantity) VALUES ('2021-02-05','regular jeans', 'clothes', 35, 7);