Setting Up Your SQL Environment

Install PostgreSQL

Linux

sudo apt update
sudo apt install postgresql postgresql-contrib

macOS

brew install postgresql
brew services start postgresql

Windows

1. Download installer from https://www.postgresql.org/download/windows/
2. Run installer and follow instructions.

Install SQLite

Linux

sudo apt update
sudo apt install sqlite3

macOS

brew install sqlite

Windows

1. Download the precompiled binaries from https://www.sqlite.org/download.html
2. Extract and add the directory to your system's PATH.

Running SQL Queries

PostgreSQL

1. Start PostgreSQL service.

   Linux

   sudo service postgresql start

   macOS

   pg_ctl -D /usr/local/var/postgres start

   Windows

   net start postgresql-x64-13

2. Access PostgreSQL shell.

   psql -U postgres

   Enter your password when prompted.

3. Create a database.

   CREATE DATABASE mydatabase;

4. Connect to the database.

   \c mydatabase

5. Run a test query.

   SELECT version();

SQLite

1. Open SQLite shell.

   sqlite3 mydatabase.db

2. Create a table.

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

3. Insert a test record.

   INSERT INTO users (name) VALUES ('John Doe');

4. Run a test query.

   SELECT * FROM users;

Final Steps

• Confirm running queries return expected results.
• Your SQL environment is now set up for PostgreSQL and SQLite.

Creating Your First Database

PostgreSQL Implementation

Step 1: Connect to PostgreSQL

psql -U your_username -h your_host -d your_initial_db

Step 2: Create a New Database

CREATE DATABASE my_first_db;

Step 3: Connect to the New Database

\c my_first_db;

Step 4: Create a Table

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 5: Insert Sample Data

INSERT INTO users (username, email) VALUES 
    ('johndoe', 'johndoe@example.com'),
    ('janedoe', 'janedoe@example.com');

Step 6: Select Data

SELECT * FROM users;

SQLite Implementation

Step 1: Open SQLite Command Line

sqlite3 my_first_db.sqlite

Step 2: Create a New Table

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Step 3: Insert Sample Data

INSERT INTO users (username, email) VALUES 
    ('johndoe', 'johndoe@example.com'),
    ('janedoe', 'janedoe@example.com');

Step 4: Select Data

SELECT * FROM users;

Adding Tables to Your Database

PostgreSQL Implementation

-- Connect to your PostgreSQL database
\c your_database_name;

-- Create a new table named 'employees'
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

-- Create another table named 'departments'
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- Add a foreign key reference between 'employees' and 'departments'
ALTER TABLE employees
ADD COLUMN department_id INTEGER REFERENCES departments(id);

SQLite Implementation

-- Open your SQLite database
.open your_database_name.db

-- Create a new table named 'employees'
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT UNIQUE,
    hire_date TEXT
);

-- Create another table named 'departments'
CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    department_name TEXT NOT NULL
);

-- Add a foreign key reference between 'employees' and 'departments'
ALTER TABLE employees
ADD COLUMN department_id INTEGER,
ADD FOREIGN KEY (department_id) REFERENCES departments(id);

Running the Initial SQL Query

For both PostgreSQL and SQLite, you can perform a basic query to ensure your tables are set up correctly:

-- Retrieve all records from 'employees'
SELECT * FROM employees;

-- Retrieve all records from 'departments'
SELECT * FROM departments;

Make sure to run these SQL commands within an appropriate SQL client or a tool that can handle SQL queries, aligned with the database you're using.

Inserting Data into Tables

This section focuses on implementing SQL commands to add data into existing tables in both PostgreSQL and SQLite.

PostgreSQL

-- Insert a single row into the 'users' table
INSERT INTO users (id, name, age, email)
VALUES (1, 'John Doe', 30, 'john.doe@example.com');

-- Insert multiple rows into the 'orders' table
INSERT INTO orders (order_id, user_id, order_total, order_date)
VALUES (101, 1, 250.00, '2023-10-01'),
       (102, 2, 150.00, '2023-10-02');

SQLite

-- Insert a single row into the 'customers' table
INSERT INTO customers (id, name, age, email)
VALUES (1, 'Jane Smith', 28, 'jane.smith@example.com');

-- Insert multiple rows into the 'purchases' table
INSERT INTO purchases (purchase_id, customer_id, amount, purchase_date)
VALUES (201, 1, 125.50, '2023-10-01'),
       (202, 2, 175.75, '2023-10-02');

Notes

• Ensure the columns specified in INSERT INTO table_name (columns...) match the table schema.
• Values should correspond to the respective columns in the VALUES clause.
• Dates follow the format 'YYYY-MM-DD'.

Section 5: Querying Data from Tables

5.1 Query Sample for PostgreSQL

-- Select all records from a table named 'employees'
SELECT * FROM employees;

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

-- Filter records from 'employees' where department is 'HR'
SELECT * FROM employees WHERE department = 'HR';

-- Retrieve employees hired after a specific date
SELECT * FROM employees WHERE hire_date > '2022-01-01';

-- Count total number of employees
SELECT COUNT(*) FROM employees;

-- Retrieve employees with salary greater than a specific amount
SELECT * FROM employees WHERE salary > 50000;

-- Sorting employees by last name in ascending order
SELECT * FROM employees ORDER BY last_name ASC;

-- Retrieving the top 5 highest-paid employees
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

-- Aggregating data: count employees by department
SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department;

5.2 Query Sample for SQLite

-- Select all records from a table named 'products'
SELECT * FROM products;

-- Select specific columns from 'products' table
SELECT product_name, price, category FROM products;

-- Filter records from 'products' where category is 'Electronics'
SELECT * FROM products WHERE category = 'Electronics';

-- Retrieve products added after a specific date
SELECT * FROM products WHERE added_date > '2022-01-01';

-- Count total number of products
SELECT COUNT(*) FROM products;

-- Retrieve products with price greater than a specific amount
SELECT * FROM products WHERE price > 100;

-- Sorting products by price in descending order
SELECT * FROM products ORDER BY price DESC;

-- Retrieving the top 5 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 5;

-- Aggregating data: count products by category
SELECT category, COUNT(*) AS num_products FROM products GROUP BY category;

Notes

• Replace table names and columns as per your specific database schema.
• Queries are designed for standard compliance in both PostgreSQL and SQLite. For any specific non-standard feature, refer to relevant documentation.
• Always ensure your SQL commands are tested in a development environment before applying them to production databases.

-- PostgreSQL Implementation

-- Assume a table called 'employees' with columns id, name, department, and salary

-- SELECT Query to fetch all data from the employees table
SELECT * FROM employees;

-- INSERT Query to add data to the employees table
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'John Doe', 'Engineering', 70000);

-- UPDATE Query to update data in the employees table
UPDATE employees
SET salary = 75000
WHERE id = 1;

-- DELETE Query to remove data from the employees table
DELETE FROM employees
WHERE id = 1;

-- Testing Queries with Assertions
-- You can use a testing framework or create a simple script to validate queries.
-- Pseudo-Code Example for Basic Script Testing

-- 1. Check if data insertion works
INSERT INTO employees (id, name, department, salary) VALUES (2, 'Jane Smith', 'Marketing', 65000);
-- Assert that new row exists: SELECT COUNT(*) FROM employees WHERE id = 2 AND name = 'Jane Smith' AND department = 'Marketing' AND salary = 65000;

-- 2. Check if data retrieval works
-- Assert that retrieved data matches expected data: SELECT name, department, salary FROM employees WHERE id = 2;

-- 3. Check if data update works
UPDATE employees SET salary = 68000 WHERE id = 2;
-- Assert updated value: SELECT salary FROM employees WHERE id = 2;

-- 4. Check if data deletion works
DELETE FROM employees WHERE id = 2;
-- Assert row deletion: SELECT COUNT(*) FROM employees WHERE id = 2;

-- SQLite Implementation

-- Assume a table called "products" with columns id, name, category, and price

-- SELECT Query to fetch all data from the products table
SELECT * FROM products;

-- INSERT Query to add data to the products table
INSERT INTO products (id, name, category, price)
VALUES (1, 'Laptop', 'Electronics', 999.99);

-- UPDATE Query to update data in the products table
UPDATE products
SET price = 949.99
WHERE id = 1;

-- DELETE Query to remove data from the products table
DELETE FROM products
WHERE id = 1;

-- Testing Queries with Assertions
-- Pseudo-Code Example for Basic Script Testing

-- 1. Check if data insertion works
INSERT INTO products (id, name, category, price) VALUES (2, 'Smartphone', 'Electronics', 699.99);
-- Assert that new row exists: SELECT COUNT(*) FROM products WHERE id = 2 AND name = 'Smartphone' AND category = 'Electronics' AND price = 699.99;

-- 2. Check if data retrieval works
-- Assert that retrieved data matches expected data: SELECT name, category, price FROM products WHERE id = 2;

-- 3. Check if data update works
UPDATE products SET price = 649.99 WHERE id = 2;
-- Assert updated value: SELECT price FROM products WHERE id = 2;

-- 4. Check if data deletion works
DELETE FROM products WHERE id = 2;
-- Assert row deletion: SELECT COUNT(*) FROM products WHERE id = 2;

These SQL queries and corresponding pseudocode provide a practical approach for executing and testing SQL queries both in PostgreSQL and SQLite environments. Ensure assertions are handled appropriately in your development or testing framework to validate each action.