CREATE TABLE table_name (col_1_name col_1_type, column_2_name column_2_type…);
e.g.
CREATE TABLE items (id INTEGER, name TEXT);

add PRIMARY KEY and AUTOINCREMENT keywords to the id field while creating the table, like so:

CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

To automatically increment ids on each insertion, no need to define id when inserting items.
Primary key means the column is for unique ids

INSERT INTO table_name (column_1_name, column_2_name) VALUES (value_1, value_2);
e.g.
INSERT INTO items (id, name) VALUES (2, "Book");

items table:
id | name
2 | Book

UPDATE table_name SET column_n_name = value_1 WHERE column_n_name = value_2;
e.g.
UPDATE items SET name = "Novel" WHERE id = 2;

items table
id | name
2 | Novel

SELECT column_n_name, column_n_name… FROM table_name;
e.g.
SELECT id, name FROM items;

This will return all id and name values from all rows in the items table:

a ‘*’ wildcard also represents all columns:

SELECT * FROM items;
returns all of the rows:

Use the WHERE statement to narrow the returned values

SELECT * FROM items WHERE name = "Novel";
returns all data from row(s) where the name col value is is “Novel”

ORDER BY will sort the results by the column you choose (default ASC).
To reverse the order add DESC after the column name (ascending or descending).

SELECT * FROM items WHERE name = "Novel" >ORDER BY id DESC;
You can also use logical operators like AND and OR to connect more statements.

SELECT * FROM items WHERE name = “Novel” AND id = 2;

DELETE FROM table_name WHERE column_n_name = value;
e.g.
DELETE FROM items WHERE id = 2;

DROP TABLE table_name;
e.g.
DROP TABLE items;

You can also create a condition to only delete the table if it exists.
DROP TABLE IF EXISTS items;

————————–

FOREIGN KEYS
We can define relationships between tables.
An items table may have multiple items that have the same category .

items table
id | name | category
1 | Apple | Fruit
2 | Hammer | Tools
3 | Wrench | Tools

Instead of storing the same category string multiple times,
we can create another table which will store all of the categories.

categories table
id | category
1 | Fruit
2 | Tools

Now we can just create a reference column inside of the items table, instead of storing strings, we can now store id ‘s from the categories table.

items table
id | name | category_id
1 | Apple | 1
2 | Hammer | 2
3 | Wrench | 2

We can connect this category_id column with the id column from the categories database
with the help of FOREIGN KEYS.

FOREIGN KEY will define the connection.

Here is the SQL for creating these tables:

CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
category_id INTEGER,
FOREIGN KEY(category_id) REFERENCES categories(id)
);

Joins (Inner, Left, Right and Full Joins)
A JOIN clause combines rows from two or more tables based on related columns.

INNER JOIN selects records that have matching values in multiple tables.

Review: This SELECT will print out the rows from the items table created earlier

SELECT id, name, category_id FROM items;

items table
id | name | category_id
1 | Apple | 1
2 | Hammer | 2
3 | Wrench | 2

But we want to see the category name not the category id.
INNER JOIN can join two tables together using the ON keyword with a foreign key
for example:

SELECT *
FROM items
INNER JOIN categories ON items.category_id = categories.id;

… will join the tables together on the appropriate columns.

items table joined with categories
id | name | category_id | id | name
1 | Apple | 1 | 1 | Fruit
2 | Hammer | 2 | 2 | Tools
3 | Wrench | 2 | 2 | Tools

Instead of printing all of the columns, you can pick which ones to print.
Columns with the same name in both tables have to be prefixed with the table name.
Since this is the case, it’s better to prefix all of the columns when doing INNER JOIN , just to be sure.

SELECT items.id, items.name, categories.name
FROM items
INNER JOIN categories ON items.category_id = categories.id;

items table joined with categories
items.id | items.name | categories.name
1 | Apple | Fruit
2 | Hammer | Tools
3 | Wrench | Tools

Now we are listing the category name next to items –
without repeating category name strings in the items table.

Since joins can involve many tables, queries can get long.
You can use AS to create aliases for each table and use those aliases throughout the query.

SELECT i.id, i.name, c.name
FROM items AS i
INNER JOIN categories AS c ON i.category_id = c.id;

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
For the rows for which there is no matching row on the right side, the result-set will contain null.

SELECT *
FROM Orders
LEFT JOIN Customers
ON items.category_id = categories.category_id
;

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
For the rows for which there is no matching row on the left side, the result-set will contain null.

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.

—————————

COUNT
Calculates the number of rows returned by a query.

SELECT COUNT(*) FROM items WHERE name = "Apple"

This will return 1

LIKE
The LIKE keyword can be used with WHERE to find substrings in some column.
For example, if you want all of the rows which have the name that starts with App.

SELECT id, name FROM items WHERE name LIKE 'App%';

items table
id | name
1 | Apple

* see
https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
https://www.w3schools.com/sql/sql_join.asp
https://en.wikipedia.org/wiki/Join_(SQL)
https://www.tutorialspoint.com/sql/sql-using-joins.htm