Essential SQL Commands and Operations

Posted on Feb 24, 2025 in Computing and Communications

CREATE: Create a New Table

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);

INSERT INTO: Add New Records

INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES
  (1, 'John', 'Doe', 'HR', 50000.00),
  (2, 'Jane', 'Smith', 'IT', 60000.00),
  (3, 'Alice', 'Johnson', 'Finance', 55000.00),
  (4, 'Bob', 'Williams', 'IT', 62000.00),
  (5, 'Emily', 'Brown', 'HR', 48000.00);

ALTER TABLE: Modify Table

ALTER TABLE employees
ADD COLUMN new_column INT;

WHERE: Filter Rows

SELECT * FROM employees WHERE salary > 55000.00;

CASE: Conditional Logic

SELECT 
  first_name,
  last_name,
  CASE 
    WHEN salary > 55000 THEN 'High'
    WHEN salary > 50000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_category
FROM employees;

UPDATE: Modify Records

UPDATE employees
SET salary = 55000.00
WHERE employee_id = 1;

DELETE: Remove Records

DELETE FROM employees
WHERE employee_id = 5;

Filtering Data in SQL

SELECT * FROM employees
WHERE department = 'IT';

LIKE: Pattern Matching

SELECT * FROM employees
WHERE first_name LIKE 'J%';

IN: Match Multiple Values

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

BETWEEN: Range Matching

SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 60000;

ORDER BY: Sort Results

SELECT * FROM employees
ORDER BY salary DESC;

SQL Operators

AND: Combine Conditions

SELECT * FROM employees
WHERE department = 'IT' AND salary > 60000;

OR: Alternative Conditions

SELECT * FROM employees
WHERE department = 'HR' OR department = 'Finance';

NOT: Negate Condition

SELECT * FROM employees
WHERE NOT department = 'IT';

LIKE: Search for Pattern

SELECT * FROM employees
WHERE first_name LIKE 'J%';

IN: Check Multiple Values

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

BETWEEN: Select Within Range

SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 60000;

GROUP BY: Aggregate Rows

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

COUNT: Count Rows

SELECT COUNT(*) FROM employees;

SUM: Calculate Sum

SELECT SUM(salary) FROM employees;

AVG: Calculate Average

SELECT AVG(salary) FROM employees;

MIN: Find Minimum Value

SELECT MIN(salary) FROM employees;

MAX: Find Maximum Value

SELECT MAX(salary) FROM employees;

GROUP BY: Group Rows

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Views in SQL

CREATE VIEW: Create Virtual Table

CREATE VIEW high_paid_employees AS
SELECT *
FROM employees
WHERE salary > 60000;

DROP VIEW: Delete View

DROP VIEW IF EXISTS high_paid_employees;