Setting Up Your Database Environment

Step 1: Install PostgreSQL

Installation on Ubuntu

sudo apt update
sudo apt install postgresql postgresql-contrib

Installation on macOS

brew update
brew install postgresql
brew services start postgresql

Step 2: Verify PostgreSQL Installation

psql --version

Step 3: Install SQLite

Installation on Ubuntu

sudo apt update
sudo apt install sqlite3

Installation on macOS

brew install sqlite

Step 4: Verify SQLite Installation

sqlite3 --version

Step 5: Set up PostgreSQL Database

# Switch to the postgres user
sudo -i -u postgres

# Enter the PostgreSQL command line
psql

# Create a new database
CREATE DATABASE mydatabase;

# Create a new user with a password
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

# Grant privileges to the user on the database
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

# Exit the PostgreSQL command line
\q

# Exit from postgres user
exit

Step 6: Connect to PostgreSQL Database

psql -U myuser -d mydatabase -h 127.0.0.1 -W

Step 7: Set up SQLite Database

# Create a new SQLite database
sqlite3 mydatabase.db

# Create a new table in the SQLite database
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

# Exit the SQLite command line
.exit

Step 8: Confirm Database Connection for SQLite

# Connect to the SQLite database
sqlite3 mydatabase.db

# Verify the table creation
.tables

# Exit the SQLite command line
.exit

By following these steps, you will have a PostgreSQL and SQLite environment set up for your SQL projects.

Basic SELECT Statements in PostgreSQL, SQL, and SQLite

PostgreSQL

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

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

-- Select employees whose age is greater than 30
SELECT * FROM employees WHERE age > 30;

-- Select distinct job titles from table 'employees'
SELECT DISTINCT job_title FROM employees;

-- Select count of employees in each department
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- Select employee details, ordered by 'last_name'
SELECT * FROM employees ORDER BY last_name ASC;

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

SQL (General SQL Syntax)

-- Select all columns from table 'customers'
SELECT * FROM customers;

-- Select specific columns 'name' and 'email' from table 'customers'
SELECT name, email FROM customers;

-- Select customers from 'USA'
SELECT * FROM customers WHERE country = 'USA';

-- Select distinct customer roles from table 'customers'
SELECT DISTINCT role FROM customers;

-- Select count of customers by country
SELECT country, COUNT(*) FROM customers GROUP BY country;

-- Select customer details, ordered by 'name'
SELECT * FROM customers ORDER BY name ASC;

-- Select top 10 newest customers
SELECT * FROM customers ORDER BY signup_date DESC FETCH FIRST 10 ROWS ONLY;

SQLite

-- Select all columns from table 'products'
SELECT * FROM products;

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

-- Select products with price greater than 50
SELECT * FROM products WHERE price > 50;

-- Select distinct product categories from table 'products'
SELECT DISTINCT category FROM products;

-- Select count of products by category
SELECT category, COUNT(*) FROM products GROUP BY category;

-- Select product details, ordered by 'product_name'
SELECT * FROM products ORDER BY product_name ASC;

-- Select top 3 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 3;

Feel free to apply the examples as needed, adjusting table and column names according to your specific database schema.

Step 3: Filtering Data with WHERE Clauses

PostgreSQL

-- Select customers from a specific city
SELECT *
FROM customers
WHERE city = 'New York';

-- Retrieve orders with an amount greater than 100
SELECT *
FROM orders
WHERE amount > 100;

-- Find employees hired after January 1, 2020
SELECT *
FROM employees
WHERE hire_date > '2020-01-01';

SQL (Generic)

-- Select products from a certain category
SELECT *
FROM products
WHERE category = 'Electronics';

-- Get transactions on a specific date
SELECT *
FROM transactions
WHERE transaction_date = '2023-01-01';

-- Filter users by a certain age threshold
SELECT *
FROM users
WHERE age > 18;

SQLite

-- Select books published after 2010
SELECT *
FROM books
WHERE published_year > 2010;

-- Retrieve movies with a rating above 8
SELECT *
FROM movies
WHERE rating > 8;

-- Find events scheduled on a weekend
SELECT *
FROM events
WHERE strftime('%w', event_date) IN ('0', '6');

This approach leverages SQL's WHERE clause to filter and retrieve specific records from a database effectively. Use these snippets in your PostgreSQL, SQL, or SQLite environments as part of your data selection process.

-- Sort customer data by last name in ascending order
SELECT * FROM customers
ORDER BY last_name ASC;

-- Sort order data by order date in descending order
SELECT * FROM orders
ORDER BY order_date DESC;

-- Sort product data by price in ascending order, and in case of equal prices, by name in ascending order
SELECT * FROM products
ORDER BY price ASC, name ASC;

-- Sort employee data first by department_id in ascending order, then by salary in descending order
SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;

-- Sort sales data by sale_amount in descending order, and in case of ties, by sale_date in ascending order
SELECT * FROM sales
ORDER BY sale_amount DESC, sale_date ASC;

This SQL code snippet can be used directly in PostgreSQL, SQL, and SQLite databases to sort data efficiently according to specific columns as detailed.

Using Aggregate Functions and GROUP BY in SQL

-- Create a sample table
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(50),
    quantity INT,
    price DECIMAL(10, 2),
    sale_date DATE
);

-- Insert sample data
INSERT INTO sales (product_name, quantity, price, sale_date) VALUES 
('product_A', 10, 20.00, '2023-01-01'),
('product_B', 5, 15.00, '2023-01-01'),
('product_A', 8, 20.00, '2023-01-02'),
('product_B', 7, 15.00, '2023-01-02'),
('product_C', 12, 30.00, '2023-01-03');

-- Aggregate function examples with GROUP BY

-- 1. Total quantity sold per product
SELECT product_name, SUM(quantity) AS total_quantity_sold
FROM sales
GROUP BY product_name;

-- 2. Average price per product
SELECT product_name, AVG(price) AS avg_price
FROM sales
GROUP BY product_name;

-- 3. Total revenue per product
SELECT product_name, SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY product_name;

-- 4. Total number of sales per day
SELECT sale_date, COUNT(*) AS number_of_sales
FROM sales
GROUP BY sale_date;

-- 5. Maximum quantity sold in a single transaction per product
SELECT product_name, MAX(quantity) AS max_quantity_sold
FROM sales
GROUP BY product_name;

-- 6. Minimum price per product
SELECT product_name, MIN(price) AS min_price
FROM sales
GROUP BY product_name;

This practical example helps in understanding SQL aggregation and grouping functions within PostgreSQL, SQL, and SQLite.

Joining Tables in SQL (Applicable to PostgreSQL, SQL, SQLite)

Step 1: INNER JOIN

SELECT 
    a.column1,
    a.column2,
    b.column3
FROM 
    table1 AS a
INNER JOIN 
    table2 AS b
ON 
    a.common_column = b.common_column;

Step 2: LEFT JOIN

SELECT 
    a.column1,
    a.column2,
    b.column3
FROM 
    table1 AS a
LEFT JOIN 
    table2 AS b
ON 
    a.common_column = b.common_column;

Step 3: RIGHT JOIN (Not available in SQLite)

SELECT 
    a.column1,
    a.column2,
    b.column3
FROM 
    table1 AS a
RIGHT JOIN 
    table2 AS b
ON 
    a.common_column = b.common_column;

Step 4: FULL OUTER JOIN (Not available in SQLite)

SELECT 
    a.column1,
    a.column2,
    b.column3
FROM 
    table1 AS a
FULL OUTER JOIN 
    table2 AS b
ON 
    a.common_column = b.common_column;

Step 5: CROSS JOIN

SELECT 
    a.column1,
    b.column3
FROM 
    table1 AS a
CROSS JOIN 
    table2 AS b;

Step 6: USING Clause (If both tables have a column with the same name)

SELECT 
    a.column1,
    b.column3
FROM 
    table1 AS a
INNER JOIN 
    table2 AS b
USING (common_column);

Step 7: NATURAL JOIN (Joins using all columns with the same name)

SELECT 
    a.column1,
    b.column3
FROM 
    table1 AS a
NATURAL JOIN 
    table2 AS b;

Step 8: Subquery with JOIN

SELECT 
    a.column1,
    (SELECT b.column3 FROM table2 b WHERE a.common_column = b.common_column) AS column3
FROM 
    table1 AS a;

---

This structure provides concise and practical SQL commands for joining tables across PostgreSQL, standard SQL, and SQLite databases.

Advanced Data Selection Techniques

Step 7: Effectively Select Data from Databases

Subquery Selection

-- Select all employees with salaries higher than the average
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

CTE (Common Table Expressions)

-- Use CTE to find the second highest salary
WITH SalaryRank AS (
    SELECT salary, 
           DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
)
SELECT salary
FROM SalaryRank
WHERE rank = 2;

Window Functions

-- Calculate the running total of salaries
SELECT employee_id, 
       first_name, 
       last_name,
       salary,
       SUM(salary) OVER (ORDER BY employee_id) as running_total
FROM employees;

CASE Statements for Conditional Logic

-- Categorize employees based on salary
SELECT employee_id,
       first_name,
       last_name,
       salary,
       CASE
           WHEN salary < 30000 THEN 'Low Income'
           WHEN salary >= 30000 AND salary < 50000 THEN 'Middle Income'
           ELSE 'High Income'
       END as income_group
FROM employees;

EXISTS and NOT EXISTS

-- Select all departments with employees
SELECT department_name
FROM departments d
WHERE EXISTS (SELECT 1
              FROM employees e
              WHERE e.department_id = d.department_id);

-- Select all departments without employees
SELECT department_name
FROM departments d
WHERE NOT EXISTS (SELECT 1
                  FROM employees e
                  WHERE e.department_id = d.department_id);

Recursive CTEs

-- Hierarchical employee data
WITH RECURSIVE org_chart AS (
    SELECT employee_id, 
           first_name, 
           last_name, 
           manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, 
           e.first_name, 
           e.last_name, 
           e.manager_id
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

Pivot Tables using CASE Statements

-- Summarize sales data by quarter
SELECT 
    product_id,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 1 THEN amount ELSE 0 END) as Q1,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 2 THEN amount ELSE 0 END) as Q2,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 3 THEN amount ELSE 0 END) as Q3,
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 4 THEN amount ELSE 0 END) as Q4
FROM sales
GROUP BY product_id;

APPLY Operators (Specific to SQL Server/Oracle)

-- Using CROSS APPLY to apply a table-valued function
SELECT e.employee_id, e.first_name, e.last_name, s.total_sales
FROM employees e
CROSS APPLY (
    SELECT SUM(amount) as total_sales
    FROM sales s
    WHERE s.employee_id = e.employee_id
) s;

These are advanced techniques to effectively select and manipulate data in SQL. They can handle complex analytical tasks and queries in PostgreSQL, SQL, and SQLite environments.