SQL — Structured Query Language Basics; NoSQL Databases

SQL — Structured Query Language Basics

What is SQL?

SQL (Structured Query Language) is the standard language for interacting with RDBMS.

SQL Categories

Basic SQL Examples

-- CREATE TABLE
CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    department  VARCHAR(50),
    salary      DECIMAL(10, 2),
    hire_date   DATE
);

-- INSERT data
INSERT INTO employees (emp_id, emp_name, department, salary, hire_date)
VALUES (1, 'Alice', 'Engineering', 75000.00, '2022-01-15');

INSERT INTO employees VALUES (2, 'Bob', 'Marketing', 65000.00, '2021-06-01');

-- SELECT queries
SELECT * FROM employees;    -- all rows, all columns

SELECT emp_name, salary FROM employees WHERE salary > 70000;

-- WHERE with multiple conditions
SELECT * FROM employees 
WHERE department = 'Engineering'
AND salary > 60000;

-- ORDER BY
SELECT emp_name, salary FROM employees ORDER BY salary DESC;

-- GROUP BY + aggregation
SELECT department, COUNT(*) as count, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

-- JOIN (combine multiple tables)
SELECT e.emp_name, d.dept_name, d.location
FROM employees
e
INNER JOIN departments d ON e.department = d.dept_id;

-- UPDATE
UPDATE employees SET salary = 80000 WHERE emp_id = 1;

-- DELETE
DELETE FROM employees WHERE emp_id = 2;

SQL Aggregate Functions

NoSQL Databases

What is NoSQL?

NoSQL (Not Only SQL) databases are designed for:

• Large-scale distributed data storage
• Flexible schemas — no fixed table structure
• High performance at scale (reads and writes)
• Horizontal scaling across commodity servers

Why NoSQL?

Types of NoSQL Databases

8.1 Key-Value Stores

Key → Value
"user:1001" → {"name":"Alice","age":28,"city":"Mumbai"}

Operations: GET(key), PUT(key, value), DELETE(key) — O(1)

Examples: Redis, Amazon DynamoDB, Memcached Use cases: Session management, caching, shopping cart

8.2 Document Databases

{
  "_id": "abc123",
  "name": "Alice",
  "address": {
    "street": "123 Main St",
    "city": "Mumbai"
  },
  "orders": [
    {"product": "Laptop", "amount": 50000},
    {"product": "Mouse", "amount": 1000}
  ]
}

Examples: MongoDB, CouchDB, Amazon DocumentDB Use cases: Content management, user profiles, e-commerce catalogs

8.3 Column-Family (Wide-Column) Stores

Row Key → Column Family → Columns
user_001 → profile: {name, email, age}
           orders:  {order_1, order_2}
           stats:   {login_count, last_seen}

Examples: Apache Cassandra, Google Bigtable, HBase (Hadoop) Use cases: IoT time series, analytics, messaging systems

8.4 Graph Databases

(Alice) --[FRIENDS]-- (Bob)
   |                    |
[LIKES]              [BOUGHT]
   |                    |
(Product)           (Laptop)

Examples: Neo4j, Amazon Neptune, ArangoDB Use cases: Social networks, recommendation engines, fraud detection

NoSQL vs RDBMS Comparison