This blog is a complete guide that covers essential SQL and PostgreSQL queries, functions, and practical examples. It's designed to be a one-stop reference for preparing for interviews, exams, or real-world database tasks. It includes everything from table creation and insertion to arithmetic operations, string functions, and more, all demonstrated with examples.

1. Table Creation and Data Insertion

-- Create table
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    math_marks INT,
    science_marks INT
);

-- Insert data
INSERT INTO students (name, age, math_marks, science_marks) VALUES
('Alice', 18, 85, 90),
('Bob', 19, 78, 88),
('Charlie', 17, 92, 80),
('David', 20, 70, 95);

2. Arithmetic Operations on Columns

-- Add math and science marks
SELECT name, math_marks + science_marks AS total_marks FROM students;

-- Average marks using division
SELECT name, (math_marks + science_marks) / 2.0 AS average_marks FROM students;

-- Multiply marks
SELECT name, math_marks * 2 AS double_math FROM students;

3. String Functions (PostgreSQL Supported)

-- Convert name to lowercase
SELECT LOWER(name) AS lowercase_name FROM students;

-- Concatenate name with age
SELECT name || ' is ' || age || ' years old' AS bio FROM students;

-- String length
SELECT name, LENGTH(name) AS name_length FROM students;

4. Filtering Rows Using WHERE Clause

The WHERE clause filters rows based on a given condition. It's used in SELECT, UPDATE, and DELETE statements.

Basic Comparison Operators

-- Students with math marks greater than 80
SELECT * FROM students WHERE math_marks > 80;

SELECT * FROM students WHERE maths + english > 80;

-- Students with science marks less than or equal to 90
SELECT * FROM students WHERE science_marks <= 90;

-- Students not equal to age 18
SELECT * FROM students WHERE age != 18;

-- Students with exact name 'Alice'
SELECT * FROM students WHERE name = 'Alice';

Logical Operators (AND, OR, NOT)

-- Students with math > 80 AND science > 85
SELECT * FROM students WHERE math_marks > 80 AND science_marks > 85;

-- Students with math > 90 OR science > 90
SELECT * FROM students WHERE math_marks > 90 OR science_marks > 90;

-- Students who are NOT 18 years old
SELECT * FROM students WHERE NOT age = 18;

IN Operator and BETWEEN Operator

-- Students whose age is 17, 18, or 19
SELECT * FROM students WHERE age IN (17, 18, 19);

-- Students with math marks between 75 and 90 (inclusive)
SELECT * FROM students WHERE math_marks BETWEEN 75 AND 90;

NULL Checks

-- Students whose name is NULL
SELECT * FROM students WHERE name IS NULL;

-- Students whose name is NOT NULL
SELECT * FROM students WHERE name IS NOT NULL;

5. UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

⚠️ Always use a WHERE clause with UPDATE to avoid updating all rows unintentionally.

-- Update math_marks of student 'Alice' to 95
-- update , table_name , set , columns name with update parameter , conditions
UPDATE students
SET math_marks = 95
WHERE name = 'Alice';

-- Update Multiple Columns

-- Update both math and science marks for 'Bob'
UPDATE students
SET math_marks = 88, science_marks = 85
WHERE name = 'Bob';

❗ Update Without WHERE (Affects All Rows)

-- Increase all students' math marks by 5
UPDATE students
SET math_marks = math_marks + 5;

7. DELETE Statement

The DELETE statement is used to remove rows from a table.

⚠️ Just like UPDATE, use WHERE to avoid deleting all rows!

✅ Delete Specific Row

-- Delete student with name 'David'
DELETE FROM students
WHERE name = 'David';

❗ Delete All Rows (Use with Caution)

-- Delete all rows in the table
DELETE FROM students;

🔐 Bonus: TRUNCATE vs DELETE

• DELETE removes rows one by one and can be rolled back.

• TRUNCATE removes all rows instantly and cannot be rolled back (in many DBMSes).

-- Truncate the table (reset all data)
TRUNCATE TABLE students;