Create a basic table

Create a customer table (via DB Browser)

CREATE TABLE "customer" (
    "id"    INTEGER NOT NULL,
    "firstName"    TEXT NOT NULL,
    "lastName"    TEXT NOT NULL,
    "birthDate"    date NOT NULL,
    "phone"    TEXT NOT NULL,
    "address"    TEXT NOT NULL,
    "city"    TEXT NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);

Add some records (via DB Browser)

Let's insert some records so that we have duplicates in the record for the city and for the name.

Test basic SELECT query

SELECT    1 + 1;
SELECT * FROM customer
SELECT * FROM customer ORDER BY firstName DESC
SELECT * FROM customer ORDER BY firstName DESC LIMIT 1
SELECT firstName, lastName FROM customer ORDER BY firstName DESC

Apply limitation

SELECT * FROM customer WHERE id = 1
SELECT * FROM customer WHERE firstName = "Stepan"
SELECT count() FROM customer WHERE firstName = "Stepan"
SELECT count() FROM customer WHERE firstName = "Stepan" OR firstName = "Petr"
SELECT * FROM customer WHERE id BETWEEN 1 AND 5
SELECT * FROM customer WHERE firstName LIKE "Step"
SELECT * FROM customer WHERE firstName LIKE "Step%"
SELECT * FROM customer WHERE firstName LIKE "%tep%"
SELECT * FROM customer WHERE firstName LIKE "%p%"
SELECT DISTINCT firstName FROM customer //two same names in records

Update name and delete duplicate records

UPDATE customer SET firstName = "Ludek" WHERE id = 1
DELETE FROM customer WHERE firstName = "Ludek"

Relations

CREATE TABLE "order" (
    "id"    INTEGER NOT NULL,
    "customer_id"    INTEGER NOT NULL,
    "date"    date NOT NULL,
    "comment"    TEXT,
    FOREIGN KEY("customer_id") REFERENCES "customer"("id") ON UPDATE CASCADE,
    PRIMARY KEY("id" AUTOINCREMENT)
);

INSERT INTO "order"
("customer_id", "date", "comment")
VALUES (1, '2024-05-12', 'Please ship as soon as possible!');

SELECT *
FROM customer AS c
INNER JOIN "order" AS o on c.id = o.customer_id;

SELECT *
FROM customer AS c
LEFT JOIN "order" AS o on c.id = o.customer_id;

VS

SELECT *
FROM "order" AS o
INNER JOIN customer AS c on c.id = o.customer_id;

SELECT *
FROM "order" AS o
LEFT JOIN customer AS c on c.id = o.customer_id;

Compare the joining of two tables with another type of joining.

Add product to the order many to many

CREATE TABLE "product" (
    "id"    INTEGER NOT NULL,
    "name"    TEXT NOT NULL,
    "stock"    INTEGER NOT NULL,
    "price"    decimal(4, 2) NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE TABLE "order_product" (
    "id"    INTEGER NOT NULL,
    "order_id"    INTEGER NOT NULL,
    "product_id"    INTEGER NOT NULL,
    "quantity"    INTEGER NOT NULL,
    FOREIGN KEY("product_id") REFERENCES "product"("id") ON UPDATE CASCADE,
    FOREIGN KEY("order_id") REFERENCES "order"("id") ON UPDATE CASCADE,
    PRIMARY KEY("id" AUTOINCREMENT)
);

Joins

SELECT *
FROM "order" AS o
LEFT JOIN order_product AS op on o.id = op.order_id
LEFT JOIN product AS p on op.product_id = p.id
LEFT JOIN customer AS c on c.id = o.customer_id

If we have more customers than orders.

SELECT *
FROM "customer" AS c
LEFT JOIN "order" AS o on c.id = o.customer_id
LEFT JOIN order_product AS op on o.id = op.order_id
LEFT JOIN product AS p on op.product_id = p.id

From a different side 

SELECT *
FROM "product" AS p
INNER JOIN order_product AS op on p.id = op.product_id
INNER JOIN "order" AS o on o.id = op.order_id
INNER JOIN customer AS c on o.customer_id = c.id

Aggregations

SELECT city, count(*) FROM customer
GROUP BY city

SELECT order_id, p.name, p.price, c.firstName, c.lastName
FROM "order" AS o
LEFT JOIN order_product AS op on o.id = op.order_id
LEFT JOIN product AS p on op.product_id = p.id
LEFT JOIN customer AS c on c.id = o.customer_id

SELECT order_id, c.firstName, c.lastName, sum(price)
FROM "order" AS o
LEFT JOIN order_product AS op on o.id = op.order_id
LEFT JOIN product AS p on op.product_id = p.id
LEFT JOIN customer AS c on c.id = o.customer_id
GROUP BY order_id

Additional knowledge about databases

What is a database?

• A database is an organized electronic collection of data.
• It is designed to store, manage, and facilitate operations on data like adding, modifying, deleting, or retrieving.
• Databases can store various types of data - text, numbers, images, etc.
• Data in a database is managed via a Database Management System (DBMS).
• Databases can be classified based on their data model - such as relational databases that use tables, and NoSQL databases that use various data models.
• SQL (Structured Query Language) is a common language used to manage data in a relational database.

Key features compare to Excel

1. Data Capacity: Excel has a limit on how much data it can handle. Databases, on the other hand, can handle vast amounts of data, into the terabytes or even petabytes.
2. Data Integrity: Databases have built-in measures to ensure data remains consistent and accurate, which is crucial for businesses. Excel does not have these safeguards.
3. Concurrent Users: Multiple users can access and work on a database simultaneously. With Excel, only one person can edit the spreadsheet at a time.
4. Data Relationships: Databases can establish relationships between different data sets. This is not possible with Excel.
5. Security: Databases have robust security features to protect sensitive data. Excel has limited security features.
6. Automation: Databases can automate repetitive tasks, reducing the risk of human error. Excel requires manual operation.

SQL vs NoSQL

1. SQL Databases: These are also known as Relational Databases. They use a structured format, storing data in tables with rows and columns. Each row represents a data record and each column represents a data field. This structure makes it easy to establish relationships between different data types. SQL (Structured Query Language) is used to manage and query the data. Examples of SQL databases include MySQL, Oracle, and SQL Server.
2. NoSQL Databases: These are designed to handle unstructured data. They do not rely on tables for storing data. Instead, they use a variety of data models, including document, key-value, wide-column, and graph formats. This flexible structure allows them to handle large volumes of diverse data and scale horizontally. They are particularly useful for dealing with real-time data and applications that require multi-directional data connections. Examples of NoSQL databases include MongoDB, Cassandra, and Redis.
   
   In summary, SQL databases are ideal when you need a structured, organized, and relationship-driven approach to data storage, while NoSQL databases are a better choice for flexible, scalable, and diverse data storage needs.

Basic about SQL query language

1. Data Definition Language (DDL): These commands are used to define or alter the structure of the database. The basic DDL commands are CREATE, ALTER, and DROP.
2. Data Manipulation Language (DML): These commands are used for managing data within the schema objects. The basic DML commands are INSERT, UPDATE, and DELETE.
3. Data Query Language (DQL): This is used to fetch the data from the database. The basic DQL command is SELECT.
4. Data Control Language (DCL): These commands are used to control the access to data stored in a database. The basic DCL commands are GRANT and REVOKE.
5. Transaction Control Language (TCL): These commands are used to manage transactions within a database. The basic TCL commands are COMMIT, ROLLBACK, and SAVEPOINT.