SQL Foundations: The Complete Beginner's Guide

-- Create a simple employees table
CREATE TABLE employees (
emp_id      NUMBER(10)      PRIMARY KEY,
first_name  VARCHAR2(50)    NOT NULL,
last_name   VARCHAR2(50)    NOT NULL,
department  VARCHAR2(50),
salary      DECIMAL(10,2)   DEFAULT 0,
hire_date   DATE            DEFAULT SYSDATE,
status      CHAR(1)         DEFAULT 'A'
);

-- MySQL / PostgreSQL
CREATE DATABASE retailcoder_db;
-- Switch to the new database (MySQL)
USE retailcoder_db;

-- Add a new column
ALTER TABLE employees ADD email VARCHAR2(100);
-- Modify a column's data type or size
ALTER TABLE employees MODIFY salary NUMBER(12,2);
-- Rename a column (Oracle 12c+)
ALTER TABLE employees RENAME COLUMN status TO emp_status;
-- Add a NOT NULL constraint
ALTER TABLE employees MODIFY email NOT NULL;
-- Drop a column
ALTER TABLE employees DROP COLUMN emp_status;

-- Remove the table and all its data permanently
DROP TABLE employees;
-- Safer version — only drops if the table exists (MySQL/PostgreSQL)
DROP TABLE IF EXISTS employees;
-- Oracle: move to recycle bin (can be recovered with FLASHBACK)
DROP TABLE employees;
-- Oracle: skip recycle bin entirely
DROP TABLE employees PURGE;

-- Insert a single row (specifying all columns in order)
INSERT INTO employees (emp_id, first_name, last_name, department, salary)
VALUES (101, 'Priya', 'Sharma', 'IT', 75000.00);
-- Insert multiple rows (MySQL, PostgreSQL)
INSERT INTO employees (emp_id, first_name, last_name, department, salary)
VALUES
(102, 'Rahul', 'Gupta', 'Finance', 80000.00),
(103, 'Anita', 'Singh', 'Operations', 70000.00);
-- Insert using SELECT (copy from another table)
INSERT INTO employees_archive
SELECT * FROM employees WHERE status = 'I';
COMMIT;

-- Update a single employee's salary
UPDATE employees
SET salary = 85000.00
WHERE emp_id = 101;
-- Update multiple columns at once
UPDATE employees
SET salary    = salary * 1.10,
department = 'Engineering'
WHERE department = 'IT'
AND hire_date < DATE '2023-01-01';
-- Update based on a subquery (Oracle)
UPDATE employees e
SET salary = (
SELECT AVG(salary) * 1.15
FROM employees
WHERE department = e.department
)
WHERE status = 'A';
COMMIT;

-- Delete a specific row
DELETE FROM employees WHERE emp_id = 101;
-- Delete all inactive employees
DELETE FROM employees WHERE status = 'I';
-- Delete with a subquery condition
DELETE FROM employees
WHERE department NOT IN (
SELECT department_name FROM departments WHERE active = 'Y'
);
COMMIT;

-- Basic SELECT — all columns
SELECT * FROM employees;
-- SELECT specific columns
SELECT emp_id, first_name, last_name, salary
FROM employees;
-- SELECT with a calculated column
SELECT first_name,
last_name,
salary,
salary * 12 AS annual_salary
FROM employees;
-- SELECT with a string expression
SELECT first_name || ' ' || last_name AS full_name,
department,
salary
FROM employees
WHERE status = 'A'
ORDER BY salary DESC;

-- Equality filter
SELECT * FROM employees WHERE department = 'Finance';
-- Numeric comparison
SELECT first_name, salary
FROM employees
WHERE salary > 70000;
-- Multiple conditions with AND
SELECT first_name, salary
FROM employees
WHERE department = 'IT'
AND salary > 60000
AND status = 'A';
-- OR condition
SELECT * FROM employees
WHERE department = 'IT'
OR department = 'Engineering';
-- Combining AND and OR — use parentheses to control precedence
SELECT * FROM employees
WHERE status = 'A'
AND (department = 'IT' OR department = 'Engineering');

-- Sort by salary descending (highest first)
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
-- Sort ascending (default — ASC can be omitted)
SELECT first_name, hire_date
FROM employees
ORDER BY hire_date ASC;
-- Sort by multiple columns
-- Primary sort: department A-Z, then secondary: salary highest first
SELECT first_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
-- Sort by column position (not recommended in production — fragile)
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC;  -- 3 = third column = salary
-- Sort NULLs last (Oracle default is NULLs last for DESC)
SELECT first_name, commission
FROM employees
ORDER BY commission DESC NULLS LAST;

-- All unique departments in the employees table
SELECT DISTINCT department
FROM employees;
-- Distinct combination of department + status
SELECT DISTINCT department, status
FROM employees
ORDER BY department;
-- Count distinct values
SELECT COUNT(DISTINCT department) AS unique_dept_count
FROM employees;

-- Names starting with 'Pr'
SELECT * FROM employees WHERE first_name LIKE 'Pr%';
-- Names ending with 'a'
SELECT * FROM employees WHERE first_name LIKE '%a';
-- Names containing 'an' anywhere
SELECT * FROM employees WHERE first_name LIKE '%an%';
-- Email with exactly 5 characters before the @
SELECT * FROM employees WHERE email LIKE '_____@%';
-- NOT LIKE — exclude a pattern
SELECT * FROM employees WHERE department NOT LIKE '%Sales%';

-- Match any of the listed values (equivalent to multiple OR conditions)
SELECT * FROM employees
WHERE department IN ('IT', 'Finance', 'Engineering');
-- NOT IN — exclude listed values
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Admin');
-- IN with a subquery
SELECT first_name, department
FROM employees
WHERE department IN (
SELECT department_name FROM departments WHERE region = 'North'
);

-- Salary range (inclusive on both ends)
SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 80000;
-- Date range
SELECT * FROM orders
WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-03-31';
-- NOT BETWEEN
SELECT * FROM employees
WHERE salary NOT BETWEEN 40000 AND 60000;

-- Find employees with no email on file
SELECT first_name, last_name
FROM employees
WHERE email IS NULL;
-- Find employees who DO have an email
SELECT first_name, email
FROM employees
WHERE email IS NOT NULL;
-- NULL in arithmetic — any operation on NULL returns NULL
SELECT emp_id, salary, commission,
salary + commission AS total_comp  -- NULL if commission is NULL!
FROM employees;
-- Fix: use NVL (Oracle) or COALESCE (standard) to replace NULL with 0
SELECT emp_id, salary, commission,
salary + NVL(commission, 0) AS total_comp
FROM employees;
-- COALESCE — returns the first non-NULL value in the list
SELECT first_name,
COALESCE(mobile, office_phone, 'No contact') AS contact_number
FROM employees;

SELECT emp_id,
salary,
salary * 12          AS annual_salary,
salary * 1.10        AS salary_after_10pct_raise,
salary + 5000        AS salary_with_bonus,
salary / 22          AS daily_rate,
MOD(emp_id, 10)      AS last_digit_of_id
FROM employees;

--All standard comparisons work in WHERE clauses
SELECT * FROM employees
WHERE salary = 60000
AND hire_date  DATE '2024-01-01'
AND department [-ne] 'HR';      --use sql not equal to operator

-- AND, OR, NOT — combine multiple conditions
SELECT * FROM employees
WHERE status = 'A'
AND (department = 'IT' OR department = 'Engineering')
AND NOT emp_id IN (SELECT emp_id FROM suspended_employees);

-- Oracle uses || for string concatenation
SELECT first_name || ' ' || last_name AS full_name,
department || ' - ' || TO_CHAR(hire_date, 'YYYY') AS dept_year
FROM employees;
-- MySQL uses CONCAT()
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- MySQL / PostgreSQL — LIMIT
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;
-- MySQL / PostgreSQL — with OFFSET for pagination
SELECT * FROM employees ORDER BY salary DESC LIMIT 10 OFFSET 20;  -- page 3
-- Oracle 12c+ — FETCH FIRST (SQL standard)
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;
-- Oracle 12c+ — with OFFSET
SELECT * FROM employees
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- Oracle legacy (pre-12c) — use ROWNUM
SELECT * FROM (
SELECT * FROM employees ORDER BY salary DESC
)
WHERE ROWNUM = 10;
-- SQL Server — TOP
SELECT TOP 10 * FROM employees ORDER BY salary DESC;

-- Column alias (AS keyword)
SELECT
first_name || ' ' || last_name AS full_name,
salary * 12                    AS annual_salary,
ROUND(salary / 22, 2)          AS daily_rate
FROM employees;
-- Column alias without AS (also valid, less readable)
SELECT salary * 12 annual_salary FROM employees;
-- Table alias — essential for self-joins and readability
SELECT e.first_name AS employee, m.first_name AS manager
FROM   employees e
JOIN   employees m ON e.manager_id = m.emp_id;
-- Table alias in subqueries
SELECT dept_stats.department, dept_stats.avg_salary
FROM (
SELECT department, AVG(salary) AS avg_salary
FROM   employees
GROUP  BY department
) dept_stats
WHERE dept_stats.avg_salary > 65000;

-- Single-line comment: starts with --
SELECT first_name, salary  -- returns active employees only
FROM   employees
WHERE  status = 'A';       -- 'A' = Active
/* Multi-line comment:
used for longer explanations,
author attribution, or temporarily
disabling blocks of code           /
SELECT dept_id,
/ AVG(salary) AS avg_salary, -- temporarily disabled /
COUNT()      AS headcount
FROM   employees
GROUP  BY dept_id;

-- UPPER / LOWER — change case
SELECT UPPER(first_name), LOWER(email) FROM employees;
-- LENGTH — number of characters
SELECT first_name, LENGTH(first_name) AS name_length FROM employees;
-- SUBSTR / SUBSTRING — extract a portion of a string
-- Oracle: SUBSTR(str, start_pos, length)
SELECT SUBSTR('RetailCoder', 1, 6) FROM dual;  -- 'Retail'
-- TRIM — remove leading/trailing spaces
SELECT TRIM('  hello world  ') FROM dual;  -- 'hello world'
-- REPLACE — substitute text
SELECT REPLACE(phone, '-', '') AS clean_phone FROM employees;
-- CONCAT / || — join strings
SELECT CONCAT(first_name, ' ', last_name) FROM employees;  -- MySQL
SELECT first_name || ' ' || last_name FROM employees;       -- Oracle

-- ROUND — round to n decimal places
SELECT ROUND(salary / 22, 2)    AS daily_rate FROM employees;
SELECT ROUND(3.14159, 2)        FROM dual;    -- 3.14
-- CEIL / CEILING — round up to nearest integer
SELECT CEIL(4.1)  FROM dual;    -- 5
-- FLOOR — round down to nearest integer
SELECT FLOOR(4.9) FROM dual;    -- 4
-- ABS — absolute value
SELECT ABS(-5000) FROM dual;    -- 5000
-- MOD — remainder (modulo)
SELECT MOD(17, 5) FROM dual;    -- 2
-- POWER — exponentiation
SELECT POWER(2, 10) FROM dual;  -- 1024

-- SYSDATE / CURRENT_DATE — current date and time
SELECT SYSDATE FROM dual;           -- Oracle
SELECT CURRENT_DATE FROM dual;      -- Standard SQL, MySQL, PostgreSQL
-- TRUNC — remove time component from a date
SELECT TRUNC(SYSDATE) FROM dual;    -- Today's date with time set to 00:00:00
-- ADD_MONTHS — add months to a date (Oracle)
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;  -- 3 months from today
-- MONTHS_BETWEEN — months between two dates (Oracle)
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) AS tenure_months
FROM employees;
-- TO_CHAR — format a date as a string (Oracle)
SELECT TO_CHAR(hire_date, 'DD-Mon-YYYY') AS formatted_date
FROM employees;
-- TO_DATE — parse a string to a date (Oracle)
SELECT * FROM employees
WHERE hire_date >= TO_DATE('01-Jan-2023', 'DD-Mon-YYYY');
-- DATEADD (SQL Server) / DATE_ADD (MySQL)
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date FROM orders;