Introduction to SQL and Setting Up the Environment

1. Installing PostgreSQL

On Ubuntu:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

On MacOS:

brew install postgresql
brew services start postgresql

On Windows:

1. Download PostgreSQL from the official website.
2. Run the installer and follow the on-screen instructions.

2. Access PostgreSQL Database

sudo -i -u postgres
psql

3. Create a Database and a User in PostgreSQL

CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

4. Installing SQLite

On Ubuntu:

sudo apt update
sudo apt install sqlite3

On MacOS:

brew install sqlite

On Windows:

1. Download SQLite from the official website.
2. Extract the files and set up the environment variable.

5. Create and Access a SQLite Database

sqlite3 mydatabase.db

6. Basic SQL Commands (Applicable to PostgreSQL, SQL, and SQLite)

Create a Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY, 
    name VARCHAR(100), 
    email VARCHAR(100) UNIQUE
);

Insert Data

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

Select Data

SELECT * FROM users;

Update Data

UPDATE users SET email = 'alice_new@example.com' WHERE name = 'Alice';

Delete Data

DELETE FROM users WHERE name = 'Alice';

7. Installing SQL Clients

For PostgreSQL: pgAdmin

1. Download pgAdmin from the official website.
2. Install and configure to connect to your PostgreSQL server.

For SQLite: DB Browser for SQLite

1. Download DB Browser for SQLite from the official website.
2. Install and open your .db file for graphical management.

Conclusion

You have now set up the environment for both PostgreSQL and SQLite, and you are ready to start working with basic SQL commands in both databases.

Basic SQL Queries: SELECT, INSERT, UPDATE, DELETE

Below are practical examples of basic SQL queries in PostgreSQL, SQL, and SQLite.

1. SELECT

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

-- Select specific columns from the table 'employees'
SELECT first_name, last_name, salary FROM employees;

-- Select with a WHERE condition
SELECT * FROM employees WHERE department = 'Sales';

-- Select with ORDER BY clause
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;

2. INSERT

-- Insert a new record into the table 'employees'
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'Engineering', 75000);

-- Insert multiple records into the table 'employees'
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
('Jane', 'Smith', 'Marketing', 65000),
('Mark', 'Johnson', 'Sales', 50000);

3. UPDATE

-- Update a specific record in the table 'employees'
UPDATE employees
SET salary = 80000
WHERE first_name = 'John' AND last_name = 'Doe';

-- Update multiple records in the table 'employees'
UPDATE employees
SET department = 'Operations'
WHERE department = 'Engineering';

4. DELETE

-- Delete a specific record from the table 'employees'
DELETE FROM employees
WHERE first_name = 'Jane' AND last_name = 'Smith';

-- Delete all records where department is 'Sales'
DELETE FROM employees
WHERE department = 'Sales';

You can run these queries directly in your PostgreSQL, SQL, or SQLite environment to manipulate and query your database effectively.

Advanced SQL Queries: JOINs, Subqueries, and Aggregation

JOINs

-- INNER JOIN: Fetch all orders with customer information
SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

-- LEFT JOIN: Fetch all customers with their orders, if any
SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

-- RIGHT JOIN: Fetch all orders with customer information, including orders without customer details
SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

-- FULL OUTER JOIN: Fetch all customers and all orders, regardless of matching records
SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Subqueries

-- Subquery to fetch customers who have placed orders
SELECT customer_id, name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Subquery used in the SELECT statement to count orders per customer
SELECT customers.customer_id, customers.name, 
    (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

-- Subquery used in the FROM clause to find average order amount for customers who have placed an order
SELECT customers.customer_id, customers.name, subquery.avg_order_amount
FROM customers
JOIN (SELECT customer_id, AVG(amount) AS avg_order_amount 
      FROM orders 
      GROUP BY customer_id) AS subquery
ON customers.customer_id = subquery.customer_id;

Aggregation

-- Aggregation to count the number of orders per customer
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

-- Aggregation to find the total and average order amounts per customer
SELECT customer_id, SUM(amount) AS total_amount, AVG(amount) AS avg_amount
FROM orders
GROUP BY customer_id;

-- Aggregation with HAVING to filter customers with more than 5 orders
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

-- Aggregate function to find the highest spending customer
SELECT customers.customer_id, customers.name, SUM(orders.amount) AS total_spent
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, customers.name
ORDER BY total_spent DESC
LIMIT 1;

Combining JOINs, Subqueries, and Aggregation

-- Find top 3 customers by order amount including their names and total order amounts
SELECT customers.customer_id, customers.name, subquery.total_order_amount
FROM customers
JOIN (
    SELECT customer_id, SUM(amount) AS total_order_amount
    FROM orders
    GROUP BY customer_id
    ORDER BY total_order_amount DESC
    LIMIT 3
) AS subquery ON customers.customer_id = subquery.customer_id;

These SQL snippets can be used in PostgreSQL, SQL, and SQLite for practical implementation and can be executed directly to gain insights and manipulations as per the project requirements.

Understanding and Manipulating Database Schema

Viewing Schema Information

PostgreSQL and SQLite

To view the schema information in PostgreSQL or SQLite, use the following command:

-- PostgreSQL
SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_schema = 'public';

-- SQLite
PRAGMA table_info(table_name);

SQL (General)

To get a list of tables and their columns:

-- List all tables
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

-- List all columns for a specific table
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'your_table';

Creating Tables

PostgreSQL and SQL

To create a new table with primary and foreign keys:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

ALTER TABLE employees 
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) 
REFERENCES departments(department_id);

SQLite

SQLite does not support SERIAL. Use INTEGER PRIMARY KEY for autoincrement:

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INT,
    email TEXT UNIQUE
);

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY AUTOINCREMENT,
    department_name TEXT NOT NULL
);

PRAGMA foreign_keys = ON;
ALTER TABLE employees 
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) 
REFERENCES departments(department_id);

Modifying Tables

Adding Columns

-- PostgreSQL and SQL
ALTER TABLE employees ADD COLUMN phone_number VARCHAR(15);

-- SQLite
ALTER TABLE employees ADD COLUMN phone_number TEXT;

Modifying Columns

Change the data type or constraints:

-- PostgreSQL
ALTER TABLE employees 
ALTER COLUMN phone_number TYPE VARCHAR(20);

-- SQLite (SQLite does not support direct column type modification)
CREATE TABLE employees_new (
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INT,
    email TEXT UNIQUE,
    phone_number TEXT
);

INSERT INTO employees_new(employee_id, name, department_id, email)
SELECT employee_id, name, department_id, email 
FROM employees;

DROP TABLE employees;
ALTER TABLE employees_new RENAME TO employees;

Dropping Tables and Columns

Dropping Tables

-- General SQL (includes PostgreSQL and SQLite)
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

Dropping Columns

-- PostgreSQL
ALTER TABLE employees DROP COLUMN IF EXISTS phone_number;

-- SQLite (SQLite does not support dropping columns directly)
CREATE TABLE employees_new AS 
SELECT employee_id, name, department_id, email 
FROM employees;

DROP TABLE employees;
ALTER TABLE employees_new RENAME TO employees;

Renaming Tables and Columns

Renaming Tables

-- PostgreSQL and SQL
ALTER TABLE employees RENAME TO staff;

-- SQLite
ALTER TABLE employees RENAME TO staff;

Renaming Columns

-- PostgreSQL and SQL
ALTER TABLE employees RENAME COLUMN name TO employee_name;

-- SQLite (SQLite does not support renaming columns directly)
CREATE TABLE employees_new (
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_name TEXT NOT NULL,
    department_id INT,
    email TEXT UNIQUE
);

INSERT INTO employees_new(employee_id, employee_name, department_id, email)
SELECT employee_id, name, department_id, email 
FROM employees;

DROP TABLE employees;
ALTER TABLE employees_new RENAME TO employees;

These commands cover core operations you may need to perform while understanding and manipulating database schema in PostgreSQL, SQL, and SQLite.

Working with Indexes and Performance Optimization

Creating Indexes

PostgreSQL

-- Create an index on a column
CREATE INDEX idx_employee_name ON employees(name);

-- Create a unique index
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- Create a composite index
CREATE INDEX idx_employee_name_dept ON employees(name, department_id);

SQLite

-- Create an index on a column
CREATE INDEX idx_employee_name ON employees(name);

-- Create a unique index
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- Create a composite index
CREATE INDEX idx_employee_name_dept ON employees(name, department_id);

Analyzing Query Performance

PostgreSQL

-- Execute the query with EXPLAIN to analyze performance
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE name = 'John Doe';

SQLite

-- Execute the query with EXPLAIN to analyze performance
EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE name = 'John Doe';

Dropping Indexes

PostgreSQL

-- Drop an index
DROP INDEX idx_employee_name;

SQLite

-- Drop an index
DROP INDEX idx_employee_name;

Practical Examples

Speed Up SELECT Queries

PostgreSQL

-- Creating an index on a frequently searched column
CREATE INDEX idx_customer_last_name ON customers(last_name);

-- Optimized query
SELECT * FROM customers WHERE last_name = 'Smith';

SQLite

-- Creating an index on a frequently searched column
CREATE INDEX idx_customer_last_name ON customers(last_name);

-- Optimized query
SELECT * FROM customers WHERE last_name = 'Smith';

Ensure Data Uniqueness

PostgreSQL

-- Create a unique index to ensure email uniqueness
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Attempt to insert duplicate email (will fail)
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'alice@example.com'); -- This will generate an error

SQLite

-- Create a unique index to ensure email uniqueness
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Attempt to insert duplicate email (will fail)
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'alice@example.com'); -- This will generate an error

Routine Maintenance

PostgreSQL

-- Rebuild an index
REINDEX INDEX idx_employee_name;

-- Analyze the table to refresh statistics
ANALYZE employees;

SQLite

-- Rebuild all indexes on the database
REINDEX;

-- Vacuum the database to optimize performance
VACUUM;

Conditional Indexing

PostgreSQL

-- Create an index with a condition
CREATE INDEX idx_active_customers ON customers(email) WHERE active = TRUE;

SQLite

-- Create a partial index
CREATE INDEX idx_active_customers ON customers(email) WHERE active = 1;

Database Security and User Management

PostgreSQL

Create a New Role (User)

CREATE ROLE user_role WITH LOGIN PASSWORD 'secure_password';

Grant Privileges to the Role

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user_role;

Create a New User and Assign to Role

CREATE USER new_user WITH PASSWORD 'password';
GRANT user_role TO new_user;

Revoke Privileges from the Role

REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM user_role;

Manage Access Control

ALTER TABLE sensitive_table OWNER TO admin_user;
REVOKE ALL ON sensitive_table FROM public;
GRANT SELECT ON sensitive_table TO authorized_user;

Ensure Secure Connections

Edit pg_hba.conf:

host    all             all             0.0.0.0/0               md5

Reload Configuration:

SELECT pg_reload_conf();

SQLite

Create a New User (SQLite does not support user management directly)

-- SQLite uses the operating system's file permissions to secure the database.
-- Ensure the database file permissions are set correctly.

User Authentication via Application Logic

-- Implement user authentication and authorization in the application layer using stored credentials.

SQL (Generic)

Create a New Role

CREATE ROLE user_role;

Grant Privileges

GRANT SELECT, INSERT ON table_name TO user_role;

Create a User

CREATE USER new_user WITH PASSWORD 'password';
GRANT user_role TO new_user;

Revoke Privileges

REVOKE INSERT ON table_name FROM user_role;

Set Up Access Control

ALTER TABLE sensitive_table OWNER TO admin;
REVOKE ALL ON sensitive_table FROM public;
GRANT SELECT ON sensitive_table TO authorized_user;

Conclusion

The above SQL scripts offer practical implementations for database security and user management, applicable to PostgreSQL, SQLite, and SQL in general. Each set of commands should help manage users, roles, and permissions efficiently.

Practical Implementation: Real-World Applications Using PostgreSQL, SQL, and SQLite

Unit 7: Practical Projects and Real-World Applications

Project 1: E-Commerce Database

E-Commerce Schema

-- Create tables
CREATE TABLE Customers (
    CustomerID SERIAL PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE Products (
    ProductID SERIAL PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

CREATE TABLE Orders (
    OrderID SERIAL PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderDetails (
    OrderDetailID SERIAL PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Sample Data Insertion

-- Insert sample data into Customers
INSERT INTO Customers (FirstName, LastName, Email) VALUES 
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com');

-- Insert sample data into Products
INSERT INTO Products (ProductName, Price) VALUES
('Laptop', 799.99),
('Smartphone', 499.99),
('Tablet', 299.99);

-- Insert sample data into Orders
INSERT INTO Orders (CustomerID, OrderDate) VALUES
(1, '2023-10-01'),
(2, '2023-10-02');

-- Insert sample data into OrderDetails
INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES
(1, 1, 1),
(1, 2, 2),
(2, 3, 1);

Practical Queries

-- Query 1: Get order details for a specific customer
SELECT 
    Orders.OrderID, 
    OrderDate, 
    Products.ProductName, 
    Quantity, 
    (Quantity * Products.Price) AS TotalPrice
FROM 
    Orders
    JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
    JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE 
    CustomerID = 1;

-- Query 2: Calculate total sales per product
SELECT
    Products.ProductName,
    SUM(OrderDetails.Quantity) AS TotalQuantitySold,
    SUM(OrderDetails.Quantity * Products.Price) AS TotalSales
FROM
    OrderDetails
    JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY
    Products.ProductName;

-- Query 3: List all customers who have placed orders
SELECT DISTINCT
    Customers.FirstName,
    Customers.LastName,
    Customers.Email
FROM
    Customers
    JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Project 2: Library Management System

Library Schema

-- Create tables
CREATE TABLE Members (
    MemberID SERIAL PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE Books (
    BookID SERIAL PRIMARY KEY,
    Title VARCHAR(100),
    Author VARCHAR(100),
    ISBN VARCHAR(13) UNIQUE,
    CopiesAvailable INT
);

CREATE TABLE Loans (
    LoanID SERIAL PRIMARY KEY,
    MemberID INT,
    BookID INT,
    LoanDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

Sample Data Insertion

-- Insert sample data into Members
INSERT INTO Members (FirstName, LastName, Email) VALUES 
('Alice', 'Wong', 'alice.wong@example.com'),
('Bob', 'Johnson', 'bob.johnson@example.com');

-- Insert sample data into Books
INSERT INTO Books (Title, Author, ISBN, CopiesAvailable) VALUES
('1984', 'George Orwell', '9780451524935', 5),
('To Kill a Mockingbird', 'Harper Lee', '9780060935467', 3);

-- Insert sample data into Loans
INSERT INTO Loans (MemberID, BookID, LoanDate, ReturnDate) VALUES
(1, 1, '2023-09-25', '2023-10-02'),
(2, 2, '2023-09-28', '2023-10-05');

Practical Queries

-- Query 1: Find all books loaned by a specific member
SELECT 
    Loans.LoanID, 
    Loans.LoanDate, 
    Loans.ReturnDate, 
    Books.Title, 
    Books.Author
FROM 
    Loans
    JOIN Books ON Loans.BookID = Books.BookID
WHERE 
    MemberID = 1;

-- Query 2: List all books that are currently available
SELECT 
    Title, 
    Author 
FROM 
    Books 
WHERE 
    CopiesAvailable > 0;

-- Query 3: Calculate total number of books loaned out per member
SELECT 
    Members.FirstName, 
    Members.LastName, 
    COUNT(Loans.LoanID) AS TotalLoans
FROM 
    Members
    JOIN Loans ON Members.MemberID = Loans.MemberID
GROUP BY 
    Members.FirstName, 
    Members.LastName;

Summary

These SQL projects provide real-world application examples using PostgreSQL, SQL, and SQLite. You can adapt this schema and queries to fit specific practical needs in e-commerce and library management systems.