Setting Up Your Database

PostgreSQL

Installation

# Update package list and install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

Creating a New Database

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

# Start PostgreSQL prompt
psql

# Create a new database
CREATE DATABASE my_database;

# Create a new user with a password
CREATE USER my_user WITH PASSWORD 'password';

# Grant privileges on the database to the user
GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

# Exit the prompt
\q

SQLite

Installation

# Install SQLite3
sudo apt update
sudo apt install sqlite3

Creating a New Database

# Create a new SQLite database
sqlite3 my_database.db

# Exit the SQLite prompt
.exit

Initial Setup: Common SQL

Creating a Table

-- Switch to your SQLite or PostgreSQL database
-- For PostgreSQL
\c my_database

-- Common SQL for creating a sample table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary NUMERIC(10, 2)
);

Inserting Data

-- Insert sample data
INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Engineering', 75000.00);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'HR', 60000.00);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Engineering', 70000.00);
INSERT INTO employees (name, department, salary) VALUES ('Dana', 'Marketing', 65000.00);

Verify Data Insertion

-- Query to check inserted data
SELECT * FROM employees;

This setup will enable you to create and interact with PostgreSQL and SQLite databases.

Basic Sorting with ORDER BY in SQL

Sort Data Alphabetically in PostgreSQL and SQLite

Query to Sort Data Alphabetically by Column 'name'

SELECT * FROM your_table
ORDER BY name ASC;

Query to Sort Data in Reverse Alphabetical Order by Column 'name'

SELECT * FROM your_table
ORDER BY name DESC;

Sort Data Numerically in PostgreSQL and SQLite

Query to Sort Data by Column 'age' in Ascending Order

SELECT * FROM your_table
ORDER BY age ASC;

Query to Sort Data by Column 'age' in Descending Order

SELECT * FROM your_table
ORDER BY age DESC;

Sort Data by Multiple Columns in PostgreSQL and SQLite

Query to Sort Data by Columns 'last_name' then 'first_name'

SELECT * FROM your_table
ORDER BY last_name ASC, first_name ASC;

Query to Mix Sorting Orders by Columns 'last_name' then 'date_of_birth'

SELECT * FROM your_table
ORDER BY last_name ASC, date_of_birth DESC;

Using ORDER BY with LIMIT to Get Top N Rows in PostgreSQL and SQLite

Query to Get Top 5 Youngest Users

SELECT * FROM your_table
ORDER BY age ASC
LIMIT 5;

Query to Get Top 5 Oldest Users

SELECT * FROM your_table
ORDER BY age DESC
LIMIT 5;

Using ORDER BY with OFFSET for Pagination in PostgreSQL and SQLite

Query to Get Rows 6 to 10 Sorted by 'name'

SELECT * FROM your_table
ORDER BY name ASC
LIMIT 5 OFFSET 5;

These queries will help you sort and filter your data efficiently.

Filtering Data with WHERE in SQL

1. Filtering Data in SQLite

-- Select all rows where the age is greater than 21
SELECT *
FROM users
WHERE age > 21;

-- Select all rows where the status is 'active'
SELECT *
FROM projects
WHERE status = 'active';

-- Select all rows where the created_date is in the year 2023
SELECT *
FROM orders
WHERE created_date BETWEEN '2023-01-01' AND '2023-12-31';

2. Filtering Data in PostgreSQL

-- Select all rows where the salary is greater than 50000
SELECT *
FROM employees
WHERE salary > 50000;

-- Select all rows where the department is 'Finance'
SELECT *
FROM departments
WHERE department_name = 'Finance';

-- Select all rows where the joining_date is after 1st Jan 2022
SELECT *
FROM staff
WHERE joining_date > '2022-01-01';

3. Combined Filtering with AND/OR

SQLite

-- Select all rows where age is greater than 30 and status is 'completed'
SELECT *
FROM tasks
WHERE age > 30 AND status = 'completed';

-- Select all rows where country is 'USA' or 'Canada'
SELECT *
FROM customers
WHERE country = 'USA' OR country = 'Canada';

PostgreSQL

-- Select all rows where level is greater than 5 and category is 'Senior'
SELECT *
FROM levels
WHERE level > 5 AND category = 'Senior';

-- Select all rows where city is 'New York' or 'Los Angeles'
SELECT *
FROM locations
WHERE city = 'New York' OR city = 'Los Angeles';

Ready to be used - just replace table and column names as per your dataset.

Advanced Filtering Techniques in SQL (PostgreSQL and SQLite)

Filtering with Multiple Conditions

SELECT *
FROM employees
WHERE department = 'Sales'
  AND salary > 50000
  AND hire_date BETWEEN '2015-01-01' AND '2020-12-31';

Using IN and NOT IN

SELECT *
FROM products
WHERE product_category IN ('Electronics', 'Furniture');

SELECT *
FROM employees
WHERE department NOT IN ('HR', 'Admin');

Using LIKE and ILIKE (PostgreSQL) for pattern matching

-- Case-sensitive pattern matching using LIKE
SELECT *
FROM customers
WHERE email LIKE '%@example.com';

-- Case-insensitive pattern matching using ILIKE (PostgreSQL only)
SELECT *
FROM customers
WHERE email ILIKE '%@example.com';

Using Regular Expressions (PostgreSQL)

SELECT *
FROM users
WHERE username ~* '^[a-zA-Z0-9_.-]+$';  -- Case-insensitive regex match

Filtering with Subqueries

-- Filter using a subquery
SELECT *
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE country = 'USA'
);

-- Using EXISTS with a subquery
SELECT *
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM projects p
  WHERE p.manager_id = e.employee_id
);

Combining Filters with OR and AND

SELECT *
FROM employees
WHERE (department = 'Engineering' OR department = 'Sales')
  AND salary > 70000;

Using CASE Statements for Conditional Filtering

SELECT employee_id, 
       name, 
       (CASE 
            WHEN department = 'Sales' THEN 'Sales - High Priority'
            WHEN department = 'Engineering' THEN 'Engineering - High Priority'
            ELSE 'Other'
        END) AS department_priority
FROM employees;

NULL Checking

-- Check for NULL values
SELECT *
FROM employees
WHERE manager_id IS NULL;

-- Check for NOT NULL values
SELECT *
FROM employees
WHERE manager_id IS NOT NULL;

Filtering with JSON Fields (PostgreSQL)

-- Assuming a JSON field called 'profile' with a key 'age'
SELECT *
FROM users
WHERE (profile->>'age')::int > 30;

-- Assuming a JSONB field called 'data' with a key 'status'
SELECT *
FROM orders
WHERE data @> '{"status": "shipped"}';

Date Functions for Filtering

-- Filtering by current date
SELECT *
FROM events
WHERE event_date = CURRENT_DATE;

-- Filtering by date range
SELECT *
FROM events
WHERE event_date BETWEEN '2021-01-01' AND '2021-12-31';

Conclusion

This set of advanced filtering techniques in SQL can be applied directly to real-life scenarios for efficient data querying in PostgreSQL and SQLite databases. Adapt these queries according to your database schema and specific requirements.

-- Combine Sorting and Filtering in PostgreSQL/SQLite

-- Assume you have a table named 'products' with columns 'id', 'name', 'category', 'price', 'stock'

-- 1. Filter products where stock > 0 and category is 'Electronics'
-- 2. Sort the filtered results by 'price' in ascending order

SELECT id, name, category, price, stock
FROM products
WHERE stock > 0 AND category = 'Electronics'
ORDER BY price ASC;

-- Another example with more complex filtering criteria

-- 1. Filter orders table, selecting orders from the last year with 'status' as 'completed'
-- 2. Sort the filtered results by 'order_date' in descending order

SELECT order_id, customer_id, order_date, total_amount, status
FROM orders
WHERE order_date >= DATE('now', '-1 year') AND status = 'completed'
ORDER BY order_date DESC;

-- Example using JOIN operation

-- Assuming there are tables 'customers' and 'orders':
-- 1. Join 'customers' and 'orders' on 'customer_id'
-- 2. Filter to get orders from customers in 'New York' with order amount > 100
-- 3. Sort the results by 'order_amount' in descending order

SELECT c.customer_id, c.name, o.order_id, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'New York' AND o.total_amount > 100
ORDER BY o.total_amount DESC;

-- Example with multiple sorting criteria

-- 1. Filter products where stock > 10
-- 2. Sort first by 'category' alphabetically, then by 'price' in descending order within each category

SELECT id, name, category, price, stock
FROM products
WHERE stock > 10
ORDER BY category ASC, price DESC;