Setting Up Your Environment

PostgreSQL

1. Install PostgreSQL

• Ubuntu / Debian:

  sudo apt update
  sudo apt install postgresql postgresql-contrib

• MacOS:

  brew update
  brew install postgresql

• Windows: Download and run the installer from PostgreSQL official site.

2. Start PostgreSQL Service

• Ubuntu / Debian / MacOS:

  sudo service postgresql start

• Windows: Start PostgreSQL service through Services app or by running:

  pg_ctl -D "C:\Program Files\PostgreSQL\\data" start

3. Access PostgreSQL

sudo -u postgres psql

SQLite

1. Install SQLite

• Ubuntu / Debian:

  sudo apt update
  sudo apt install sqlite3

• MacOS:

  brew update
  brew install sqlite

• Windows: Download the precompiled binaries from SQLite official site and add the directory to your PATH.

2. Access SQLite

sqlite3 database_name.db

General SQL Usage

1. Create a Database (PostgreSQL and SQLite)

• PostgreSQL:

  CREATE DATABASE my_database;

• SQLite: Automatically creates the database when accessed:

  sqlite3 my_database.db

2. Create a Table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

3. Insert Data

INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');

4. Query Data

SELECT * FROM users;

Final Steps

For both PostgreSQL and SQLite, ensure that your environment variables and paths are correctly configured to access the executables from the command line or your preferred IDE. This setup will allow you to execute and test SQL commands in a local development environment successfully.

Basic SQL Commands: SELECT, INSERT, UPDATE, DELETE

SELECT

-- Selects all columns from the 'employees' table
SELECT * FROM employees;

-- Selects specific columns from the 'employees' table
SELECT id, name, position FROM employees;

-- Selects specific columns with a condition from the 'employees' table
SELECT id, name, position FROM employees WHERE department = 'HR';

-- Orders the results by 'name' in ascending order
SELECT id, name, position FROM employees ORDER BY name ASC;

-- Limits the result to 10 rows
SELECT id, name, position FROM employees ORDER BY name ASC LIMIT 10;

INSERT

-- Inserts a new row into the 'employees' table
INSERT INTO employees (name, position, department, salary) VALUES 
('John Doe', 'Manager', 'HR', 75000);

-- Inserts multiple rows into the 'employees' table
INSERT INTO employees (name, position, department, salary) VALUES 
('Jane Smith', 'Developer', 'IT', 68000), 
('Emily Johnson', 'Analyst', 'Finance', 62000);

UPDATE

-- Updates the 'salary' of an employee with a specific 'id'
UPDATE employees SET salary = 80000 WHERE id = 1;

-- Updates the 'department' of all employees with the title 'Manager'
UPDATE employees SET department = 'Operations' WHERE position = 'Manager';

DELETE

-- Deletes a row from the 'employees' table with a specific 'id'
DELETE FROM employees WHERE id = 1;

-- Deletes all rows from the 'employees' table where 'department' is 'Temporary'
DELETE FROM employees WHERE department = 'Temporary';

Advanced SQL Queries: JOINs, Subqueries, and Aggregations

JOINs

INNER JOIN

-- Retrieve customers and their corresponding orders
SELECT customers.customer_id, customers.name, orders.order_id, orders.amount
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

LEFT JOIN

-- Retrieve all customers and their orders, including those without any orders
SELECT customers.customer_id, customers.name, orders.order_id, orders.amount
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN

-- Retrieve all orders and their corresponding customers, including orders without an assigned customer
SELECT customers.customer_id, customers.name, orders.order_id, orders.amount
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

FULL OUTER JOIN

-- Combine the results of both LEFT and RIGHT joins
SELECT customers.customer_id, customers.name, orders.order_id, orders.amount
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Subqueries

Simple Subquery

-- Retrieve customers who have placed an order
SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

Correlated Subquery

-- Retrieve customers along with the total amount of their orders
SELECT customer_id, name, 
  (SELECT SUM(amount) FROM orders WHERE orders.customer_id = customers.customer_id) AS total_orders
FROM customers;

Aggregations

COUNT

-- Count the number of orders placed by each customer
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id;

SUM

-- Calculate the total amount of all orders
SELECT SUM(amount) AS total_sales
FROM orders;

AVG

-- Find the average order amount
SELECT AVG(amount) AS average_order
FROM orders;

MAX

-- Find the highest order amount
SELECT MAX(amount) AS highest_order
FROM orders;

MIN

-- Find the smallest order amount
SELECT MIN(amount) AS smallest_order
FROM orders;

GROUP BY and HAVING

-- Find customers with more than 5 orders and the total amount of these orders
SELECT customer_id, COUNT(order_id) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

These SQL commands utilize various types of JOINs, subqueries, and aggregation functions, providing practical examples that can be used directly in PostgreSQL, SQL, and SQLite environments.

Database Schema Design: Tables, Keys, and Indexes

Table Creation

-- Users table to store user information
CREATE TABLE Users (
    UserID SERIAL PRIMARY KEY,  -- Auto-incrementing primary key
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table to store product information
CREATE TABLE Products (
    ProductID SERIAL PRIMARY KEY, -- Auto-incrementing primary key
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    Stock INT DEFAULT 0
);

-- Orders table to store order information
CREATE TABLE Orders (
    OrderID SERIAL PRIMARY KEY, -- Auto-incrementing primary key
    UserID INT NOT NULL,
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (UserID) REFERENCES Users(UserID) -- Foreign key
);

-- OrderDetails table to store order details
CREATE TABLE OrderDetails (
    OrderDetailID SERIAL PRIMARY KEY, -- Auto-incrementing primary key
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID), -- Foreign key
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID) -- Foreign key
);

Indexes

-- Index on Username to improve search performance
CREATE INDEX idx_users_username ON Users (Username);

-- Index on Email to ensure quick lookups
CREATE INDEX idx_users_email ON Users (Email);

-- Composite index on OrderID and ProductID to optimize joins and searches
CREATE INDEX idx_orderdetails_order_product ON OrderDetails (OrderID, ProductID);

Keys

• Primary Keys

-- UserID is the primary key for Users table
ALTER TABLE Users
ADD CONSTRAINT PK_Users PRIMARY KEY (UserID);

-- ProductID is the primary key for Products table
ALTER TABLE Products
ADD CONSTRAINT PK_Products PRIMARY KEY (ProductID);

-- OrderID is the primary key for Orders table
ALTER TABLE Orders
ADD CONSTRAINT PK_Orders PRIMARY KEY (OrderID);

-- OrderDetailID is the primary key for OrderDetails table
ALTER TABLE OrderDetails
ADD CONSTRAINT PK_OrderDetails PRIMARY KEY (OrderDetailID);

• Foreign Keys

-- Foreign key for OrderDetails on OrderID
ALTER TABLE OrderDetails
ADD CONSTRAINT FK_OrderDetails_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);

-- Foreign key for OrderDetails on ProductID
ALTER TABLE OrderDetails
ADD CONSTRAINT FK_OrderDetails_ProductID FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

-- Foreign key for Orders on UserID
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_UserID FOREIGN KEY (UserID) REFERENCES Users (UserID);

Applying the Schema

Run these SQL commands in your PostgreSQL, SQL, or SQLite database environment to create the schema, including tables, keys, and indexes.

Working with PostgreSQL: Specific Features and Extensions

1. Working with JSON data

Creating a table that includes JSON/JSONB columns:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    order_details JSONB
);

Inserting data into the JSON/JSONB column:

INSERT INTO orders (customer_name, order_details) 
VALUES ('John Doe', '{"product_id": 1, "quantity": 3, "price": 29.99}');

Querying JSON/JSONB Columns:

SELECT customer_name, order_details->>'product_id' 
FROM orders 
WHERE order_details->>'product_id' = '1';

2. Using Full-Text Search

Creating a table with a tsvector column for full-text indexing:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    tsv_content TSVECTOR
);

Populating the tsvector column and creating an index:

UPDATE documents SET tsv_content = to_tsvector('english', content);

CREATE INDEX idx_fulltext ON documents USING GIN(tsv_content);

Performing Full-Text Search:

SELECT * FROM documents
WHERE tsv_content @@ to_tsquery('english', 'search_term');

3. Working with Arrays

Creating a table with an array column:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    hobbies TEXT[]
);

Inserting data into the array column:

INSERT INTO users (username, hobbies) 
VALUES ('Alice', ARRAY['reading', 'hiking', 'coding']);

Querying Array Columns:

SELECT username 
FROM users 
WHERE 'hiking' = ANY(hobbies);

4. Time Travel with temporal_tables Extension

Enable temporal_tables extension:

Provide SQL statement assuming temporal_tables is pre-installed.

CREATE EXTENSION IF NOT EXISTS temporal_tables;

Creating a history-enabled table

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50),
    salary NUMERIC
);

Enable history tracking:

SELECT create_history_table('employees');

Access data's past states:

SELECT * 
FROM employees_history 
WHERE emp_id = 1 AND period @> now() - interval '1 month';

5. PostGIS Extension

Enable PostGIS extension:

CREATE EXTENSION IF NOT EXISTS postgis;

Creating a table with a geographic column:

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates GEOGRAPHY(POINT, 4326)
);

Inserting data with geographic coordinates:

INSERT INTO locations (name, coordinates) 
VALUES ('Sample Location', ST_GeogFromText('SRID=4326;POINT(-118.4079 33.9434)'));

Querying Spatial Data:

SELECT name 
FROM locations 
WHERE ST_DWithin(coordinates, ST_GeogFromText('SRID=4326;POINT(-118.4079 33.9434)'), 1000);

6. HStore Extension

Enable HStore extension:

CREATE EXTENSION IF NOT EXISTS hstore;

Creating a table with HStore column:

CREATE TABLE product_info (
    product_id SERIAL PRIMARY KEY,
    attributes HSTORE
);

Inserting data into HStore column:

INSERT INTO product_info (attributes) 
VALUES (hstore('color', 'red', 'size', 'L'));

Querying HStore column:

SELECT product_id 
FROM product_info 
WHERE attributes -> 'color' = 'red';

---

Ensure your PostgreSQL instance has the necessary extensions installed and permissions set.

Section 6: SQLite Peculiarities and Optimization Techniques

Using Transactions

BEGIN TRANSACTION;

-- Your bulk insert, update or delete operations here

COMMIT;

Indexing for Performance

-- Creating an index to speed up queries on the 'name' column
CREATE INDEX idx_name ON your_table(name);

-- Compound index on multiple columns
CREATE INDEX idx_name_age ON your_table(name, age);

Analyzing Performance with EXPLAIN

EXPLAIN QUERY PLAN SELECT * FROM your_table WHERE name = 'John';

Utilizing PRAGMA Statements

-- Optimize the database performance
PRAGMA synchronous = NORMAL;
PRAGMA journal_mode = WAL; 
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -2000;  -- 2000 pages in cache
PRAGMA foreign_keys = ON;  -- Ensure foreign keys are enforced

Efficient Bulk Insertions

BEGIN TRANSACTION;
INSERT INTO your_table (col1, col2) VALUES 
('value1a', 'value2a'),
('value1b', 'value2b'),
('value1c', 'value2c');
COMMIT;

Vacuuming the Database

-- Reclaim space and optimize performance
VACUUM;

Using Parameterized Queries for Safety and Performance

INSERT INTO your_table (name, age) VALUES (?, ?);

-- Use your preferred language-specific method to bind parameters, for example:
-- In Python (for illustration purposes, not to be coded here):
# cursor.execute("INSERT INTO your_table (name, age) VALUES (?, ?)", (name, age))

Optimizing with WITHOUT ROWID

-- Using WITHOUT ROWID for tables that don't require a rowid
CREATE TABLE your_table (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
) WITHOUT ROWID;

Dealing with Text Affinity

-- Ensure columns have the correct type affinity
CREATE TABLE your_table (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL, 
    age INTEGER NOT NULL
);

-- Insert strictly as TEXT to avoid unwanted type changes
INSERT INTO your_table (name, age) VALUES ('John', 30);

Using Partial Indexes

-- Create indexes on specific subsets of data
CREATE INDEX idx_active_users ON users(is_active) WHERE is_active = 1;

Applying Common Table Expressions (CTEs)

-- Using CTEs for better readability and performance
WITH cte AS (
    SELECT id, name FROM your_table WHERE age > 30
)
SELECT * FROM cte WHERE name LIKE 'J%';

Analyzing and Optimizing Queries

-- Using ANALYZE to gather statistics for the query optimizer
ANALYZE;

-- Example query plan analysis
EXPLAIN QUERY PLAN SELECT * FROM your_table WHERE age = 25;

---

These techniques can be directly applied to an SQLite database to improve performance and handle peculiarities specific to SQLite.