10.3 Non-Relational Databases & Core SQL Commands

Non-Relational Databases & Core SQL Commands

Core Foundation: While relational databases excel at structured transactions and strict schemas, modern digital platforms also require databases capable of scaling across hundreds of cloud servers and handling polymorphic, unstructured data. Non-relational (NoSQL) systems provide flexible data models for these demands, while Structured Query Language (SQL) remains the universal declarative standard for querying and manipulating relational data.

---

The Drivers Behind NoSQL Architectures

For decades, Relational Database Management Systems (RDBMS) dominated enterprise software. However, the explosion of Web 2.0 applications, cloud computing, global mobile devices, and IoT telemetry revealed fundamental architectural bottlenecks in traditional relational engines. NoSQL (standing for "Not Only SQL" or Non-Relational) systems emerged to overcome these constraints.

                    [VERTICAL SCALING (Scale-Up)]        [HORIZONTAL SCALING (Scale-Out)]
                       Traditional Relational RDBMS               Modern Distributed NoSQL
                               ┌───────┐                       ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐
                               │ BIG   │                       │Node │ │Node │ │Node │ │Node │
                               │ SERVER│                       │  1  │ │  2  │ │  3  │ │  4  │
                               └───────┘                       └─────┘ └─────┘ └─────┘ └─────┘
                        Upgrade single machine with            Add commodity cloud servers to
                        more CPU, RAM, & SSDs.                 cluster. Automatic data sharding.
                        High cost, physical limits.            Infinite horizontal elasticity.

1. Vertical Scaling (Scale-Up) vs. Horizontal Scaling (Scale-Out)

• Relational Databases and Vertical Scaling: Relational databases rely heavily on joins across tables and strict ACID transaction locks. Consequently, they are designed to run on a single monolithic server. Scaling requires vertical scaling (scale-up): purchasing a larger physical server with more CPU cores, terabytes of RAM, and high-speed NVMe storage. Vertical scaling incurs exorbitant hardware costs and encounters physical ceiling limits.
• NoSQL and Horizontal Scaling: NoSQL architectures are designed from the ground up for horizontal scaling (scale-out). Instead of buying one massive supercomputer, organizations add dozens or hundreds of inexpensive, standard commodity servers to a distributed cluster. The database automatically partitions and distributes data across nodes (a process known as sharding).

2. Schema Rigidity vs. Dynamic Polymorphic Schemas

• Relational Rigidity (Schema-on-Write): RDBMS tables require an upfront, unyielding schema. If an e-commerce platform sells clothing, books, and electronics, every single product attribute (shirt size, book author, monitor refresh rate) must be shoehorned into common columns or sparse relational tables. Modifying a table with 500 million rows requires costly schema locks and potential system downtime.
• NoSQL Flexibility (Schema-on-Read): NoSQL databases support dynamic schemas. In a NoSQL document database, Document A can store a book with fields {title, author, isbn}, while Document B in the same collection stores a laptop with fields {model, cpu, ram_gb}. Attributes are added on the fly without database downtime.

3. High Ingestion Velocity and Distributed Replication

Modern cloud platforms ingest millions of clickstream events, user messages, and sensor readings every second. Relational engines stall under this write volume due to transaction logging and index updates. NoSQL systems achieve exceptional write throughput by distributing write operations across cluster nodes and utilizing eventual consistency models.

---

The Four Primary NoSQL Data Models

Non-relational databases are classified into four architectural categories based on how they organize and index data.

+-----------------------------------------------------------------------------------------+
|                           THE FOUR PRIMARY NoSQL DATA MODELS                            |
|                                                                                         |
|  +--------------------+  +---------------------+  +-----------------+  +-------------+  |
|  | 1. KEY-VALUE STORE |  | 2. DOCUMENT STORE   |  | 3. COLUMN-STORE |  | 4. GRAPH DB |  |
|  +--------------------+  +---------------------+  +-----------------+  +-------------+  |
|  | Key -> Value Blob  |  | JSON / BSON Trees   |  | Column Families |  | Nodes &     |  |
|  | O(1) Hash Lookup   |  | Nested Structures   |  | Time-Series/IoT |  | Edges       |  |
|  | Redis, DynamoDB    |  | MongoDB, Couchbase  |  | Cassandra       |  | Neo4j       |  |
|  +--------------------+  +---------------------+  +-----------------+  +-------------+  |
+-----------------------------------------------------------------------------------------+

1. Key-Value Stores

A Key-Value Store is the simplest NoSQL data model. Data is stored as an arbitrary value (a string, JSON snippet, image, or serialized object) linked to a unique alphanumeric string key.

• Access Performance: Data access is strictly by key lookup, utilizing internal hash tables that provide blazing $O(1)$ constant-time lookup performance.
• Limitation: Key-value stores cannot inspect, filter, or query the internal contents of the value blob. You cannot request "all users with age > 30"; you can only request the exact value stored at key "user:1042".
• Prominent Examples: Redis, Amazon DynamoDB, Memcached.
• Dominant Use Cases: Web application session state storage, user authentication tokens, high-speed RAM caching, and transient e-commerce shopping carts.

2. Document Databases

A Document Database stores data in self-describing, semi-structured documents, typically formatted in JSON (JavaScript Object Notation), BSON (Binary JSON), or XML.

• Hierarchical Nesting: Unlike relational tables that require separate tables for line items, a document can embed nested sub-documents and arrays directly within a single record.
• Query Capabilities: The database engine understands the internal structure of each document, allowing developers to index and query nested fields directly (e.g., querying for documents where items.price > 100).
• Prominent Examples: MongoDB, Couchbase, Amazon DocumentDB, Apache CouchDB.
• Dominant Use Cases: Content management systems (CMS), e-commerce product catalogs with diverse product features, mobile app user profiles, and blogging platforms.

3. Column-Family (Wide-Column) Stores

A Column-Family Store organizes data by columns rather than rows. Related columns are grouped into logical column families.

• Sparse Storage: In a relational table, an empty column still occupies structural storage overhead. In wide-column databases, rows are sparse: if a row does not contain a specific column, zero disk space is consumed.
• High-Throughput Analytics: Optimized for reading specific attributes across billions of rows without reading unrelated data from disk. Provides phenomenal write throughput across massively distributed clusters.
• Prominent Examples: Apache Cassandra, ScyllaDB, Google Cloud Bigtable.
• Dominant Use Cases: IoT industrial sensor telemetry, financial market time-series tick logging, historical user activity logs, and real-time recommendation engines.

4. Graph Databases

A Graph Database is built on mathematical graph theory, representing data as three distinct structural primitives:

1. Nodes (Vertices): Entities or objects (e.g., a Person, a Company, an Airport, a Device).
2. Edges (Relationships): Directed or undirected links connecting two nodes (e.g., FRIENDS_WITH, WORKS_FOR, TRANSFERRED_MONEY_TO, CONNECTED_TO).
3. Properties: Key-value attributes associated with nodes or edges (e.g., a Person node has {name: "Elena"}, and a FRIENDS_WITH edge has {since: 2021}).

• Relationship Traversal: In an RDBMS, discovering "friends of friends who purchased the same book" requires multiple recursive, expensive table joins that grind the server to a halt. Graph databases traverse complex, multi-hop relationship paths at lightning speed because relationships are stored as direct physical memory pointers on the nodes themselves.
• Prominent Examples: Neo4j, Amazon Neptune, TigerGraph.
• Dominant Use Cases: Social network connections, financial fraud detection rings (identifying money laundering pathways across shell accounts), IT network dependency mapping, and knowledge graphs.

---

Structured Query Language (SQL): Declarative Data Management

While NoSQL dominates distributed unstructured storage, Structured Query Language (SQL) remains the universal standard for interacting with relational databases. SQL is an ANSI/ISO standard language utilized across PostgreSQL, MySQL, Microsoft SQL Server, Oracle, and cloud data warehouses.

The Declarative Paradigm

Unlike procedural or imperative programming languages (such as C++, Java, or Python), which require the programmer to code step-by-step algorithms, SQL uses a declarative paradigm:

• The developer specifies WHAT data is required, not HOW to locate it.
• The RDBMS contains a sophisticated software component called the Query Optimizer. The optimizer analyzes database indexes, table statistics, and disk caching buffers to automatically construct the fastest physical execution plan (e.g., deciding whether to perform an index seek or a full table scan).

Sublanguages of SQL: DDL vs. DML

SQL commands are formally partitioned into specialized functional sublanguages:

                                        [SQL COMMAND STRUCTURE]
                                                   │
             ┌─────────────────────────────────────┴─────────────────────────────────────┐
             ▼                                                                           ▼
   [DATA DEFINITION LANGUAGE (DDL)]                            [DATA MANIPULATION LANGUAGE (DML)]
   • Manages database structure and schema                     • Manages rows and data records inside tables
   • CREATE TABLE                                              • SELECT  (Read records)
   • ALTER TABLE                                               • INSERT  (Create new records)
   • DROP TABLE                                                • UPDATE  (Modify existing records)
   • TRUNCATE TABLE                                            • DELETE  (Remove records)

• Data Definition Language (DDL): Commands that define, alter, or remove database structures, tables, views, and schemas. DDL modifies the blueprint of the database.
  • CREATE: Builds new structures (e.g., CREATE TABLE, CREATE DATABASE, CREATE INDEX).
  • ALTER: Modifies an existing schema structure (e.g., adding a new column to a table).
  • DROP: Permanently erases an entire table or database structure along with all its data.
  • TRUNCATE: Rapidly purges all rows from a table while preserving the empty table structure.
• Data Manipulation Language (DML): Commands that query, insert, modify, and delete the actual data records stored within tables.
  • SELECT: Retrieves data rows satisfying specified criteria.
  • INSERT: Adds new data rows into a table.
  • UPDATE: Modifies existing column values in one or more records.
  • DELETE: Removes specific data rows from a table.
• (Supplementary Sublanguages:) Data Control Language (DCL) manages user privileges (GRANT, REVOKE), and Transaction Control Language (TCL) manages transaction boundaries (COMMIT, ROLLBACK).

---

Mapping CRUD Operations to SQL Syntax

In software engineering, persistent storage interactions are summarized by the acronym CRUD (Create, Read, Update, Delete). SQL provides direct syntactic commands for each CRUD operation:

+---------------------------------------------------------------------------------+
|                            CRUD TO SQL SYNTAX MAPPING                           |
|                                                                                 |
|   CRUD Operation  │  SQL Command Syntax                │ Operational Action     |
|  ─────────────────┼────────────────────────────────────┼──────────────────────  |
|   C - CREATE      │  INSERT INTO ... VALUES (...)      │ Adds new row(s)        |
|   R - READ        │  SELECT ... FROM ... WHERE ...     │ Queries existing rows  |
|   U - UPDATE      │  UPDATE ... SET ... WHERE ...      │ Edits row attribute(s) |
|   D - DELETE      │  DELETE FROM ... WHERE ...         │ Removes row(s)         |
+---------------------------------------------------------------------------------+

1. Create $\rightarrow$ INSERT INTO

The INSERT INTO statement creates new rows within a specified table. Developers designate the destination table, enumerate the target columns, and supply corresponding values.

-- Create: Insert a new employee record into the employees table
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (1042, 'Marcus', 'Vance', 'Cybersecurity', 92000.00);

2. Read $\rightarrow$ SELECT

The SELECT statement retrieves records from one or more tables, filtering rows and projecting specified columns.

-- Read: Retrieve names and salaries of cybersecurity personnel
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Cybersecurity';

3. Update $\rightarrow$ UPDATE

The UPDATE statement modifies existing attribute values in one or more records that satisfy a specific condition.

-- Update: Increase Marcus Vance's salary
UPDATE employees
SET salary = 98000.00
WHERE employee_id = 1042;

4. Delete $\rightarrow$ DELETE FROM

The DELETE FROM statement removes one or more rows from a table based on a filtering condition.

-- Delete: Remove employee 1042 from the table
DELETE FROM employees
WHERE employee_id = 1042;

---

Core Query Clauses: Anatomy of a SQL Query

To construct powerful queries, developers combine modular SQL clauses. An RDBMS processes clauses in a rigorous logical order.

SELECT column1, column2, ...      -- 1. Specifies which columns to display
FROM table_name                   -- 2. Identifies source table
WHERE condition                   -- 3. Filters rows based on criteria
ORDER BY column_name [ASC|DESC]   -- 4. Sorts output rows
LIMIT count;                      -- 5. Restricts number of returned rows

1. The SELECT and FROM Clauses

• SELECT specifies the columns to return. Using SELECT * returns every column in the table, but selecting explicit column names (SELECT first_name, email) is standard engineering practice to reduce network transmission overhead and improve memory performance.
• FROM declares the source table containing the records.

2. The WHERE Filtering Clause

The WHERE clause filters rows before any sorting or display occurs. Only rows that evaluate to TRUE for the specified logical expression are included in the result.

• Comparison Operators: =, != (or <>), >, <, >=, <=.
• Logical Operators: AND (both conditions must be true), OR (either condition is true), NOT (reverses condition).
• Special Evaluation Operators:
  • BETWEEN low AND high: Inclusive range filtering (e.g., WHERE salary BETWEEN 50000 AND 90000).
  • IN (val1, val2, ...): Matches any value in a designated list (e.g., WHERE department IN ('HR', 'Finance', 'IT')).
  • LIKE '%pattern%': Performs pattern matching with wildcards (% represents zero or more characters; _ represents a single character). For example, WHERE email LIKE '%@company.com' selects all corporate addresses.

3. The ORDER BY Sorting Clause

The ORDER BY clause sorts the result set based on one or more columns:

• ASC (Ascending): Lowest to highest, $A \rightarrow Z, 0 \rightarrow 9$ (the default behavior).
• DESC (Descending): Highest to lowest, $Z \rightarrow A, 9 \rightarrow 0$.

4. The LIMIT Clause

The LIMIT clause (termed TOP in Microsoft T-SQL or FETCH FIRST in Oracle) specifies the maximum number of rows returned to the client application. Widely used for user-interface pagination and "top-N" reporting (e.g., finding the top 5 highest-earning employees).

-- Concrete Example: Find top 3 highest-paid active IT engineers
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering' AND is_active = TRUE
ORDER BY salary DESC
LIMIT 3;

---

The Catastrophic Hazard of Omitting the WHERE Clause

A critical operational hazard tested on IT certification exams involves executing UPDATE or DELETE statements without an accompanying WHERE clause.

-- CATASTROPHIC ACCIDENT #1: OMITTING WHERE IN UPDATE
UPDATE employees
SET salary = 100000.00;
-- Consequence: EVERY SINGLE EMPLOYEE in the entire company now has their
-- salary overwritten to $100,000! Historical compensation data is erased.

-- CATASTROPHIC ACCIDENT #2: OMITTING WHERE IN DELETE
DELETE FROM employees;
-- Consequence: EVERY SINGLE ROW in the entire employees table is deleted!
-- The table structure remains, but every employee record is wiped out.

Why This Occurs

In SQL grammar, the WHERE clause is syntactically optional. When an UPDATE or DELETE statement omits WHERE, the database engine assumes the condition evaluates to TRUE for all rows in the table. The engine executes the command unconditionally against the entire dataset.

Defensive Administration Safeguards

Professional database administrators implement strict safeguards against this pitfall:

1. Always Draft as a SELECT First: Before executing an UPDATE or DELETE, write the query as a SELECT ... WHERE to visually inspect exactly which records will be affected.
2. Explicit Transaction Wrapping: Wrap modifications in explicit transactions. Inspect the row count returned, and issue a ROLLBACK if unexpected rows were touched:

   BEGIN TRANSACTION;
   UPDATE employees SET salary = 100000.00 WHERE employee_id = 1042;
   -- Inspect rows affected. If rows affected = 1, then:
   COMMIT;
   -- If rows affected = 50,000, then:
   ROLLBACK;
   

3. Safe Updates Mode: Configure database client sessions with safety flags (such as MySQL's sql_safe_updates = 1), which automatically abort any UPDATE or DELETE statement that lacks a key-based WHERE condition.

---

Relational Joining: Merging Tables with INNER JOIN

Because relational databases normalize data into separate tables to eliminate redundancy, practical business queries frequently require combining columns from multiple tables. This is achieved using the INNER JOIN.

      [CUSTOMERS TABLE]                                     [ORDERS TABLE]
┌─────────────┬─────────────┐                         ┌──────────┬─────────────┬───────────┐
│ customer_id │ name        │                         │ order_id │ customer_id │ amount    │
├─────────────┼─────────────┤                         ├──────────┼─────────────┼───────────┤
│ 1           │ Alice       │ ──┐                 ┌── │ 501      │ 1           │ $120.00   │
│ 2           │ Bob         │   │  INNER JOIN ON  │   │ 502      │ 2           │ $450.00   │
│ 3           │ Charlie     │   └─ customer_id ───┘   │ 503      │ 1           │ $85.00    │
└─────────────┴─────────────┘                         └──────────┴─────────────┴───────────┘
                                         │
                                         ▼
                            [COMBINED INNER JOIN RESULT]
               ┌──────────┬─────────────┬─────────┬───────────┐
               │ order_id │ name        │ amount  │ (Joined)  │
               ├──────────┼─────────────┼─────────┼───────────┤
               │ 501      │ Alice       │ $120.00 │ Matched   │
               │ 502      │ Bob         │ $450.00 │ Matched   │
               │ 503      │ Alice       │ $85.00  │ Matched   │
               └──────────┴─────────────┴─────────┴───────────┘

How INNER JOIN Operates

An INNER JOIN evaluates rows from two tables and returns only those rows where the join condition evaluates to TRUE (i.e., where there is an exact match between the foreign key in one table and the primary key in the other). If a customer has placed no orders (like Charlie in the diagram above), Charlie is excluded from the inner join result.

Syntax Breakdown

SELECT orders.order_id, customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.amount > 100.00
ORDER BY orders.amount DESC;

• FROM customers: Identifies the primary table.
• INNER JOIN orders: Declares the secondary table to merge.
• ON customers.customer_id = orders.customer_id: The join predicate. Instructs the engine to align rows where the primary key customers.customer_id matches the foreign key orders.customer_id.
• Table Aliasing: In complex queries, developers assign short aliases (e.g., FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id) to streamline query readability.

---

Practical Diagnostic Scenarios & Exam Pitfalls

• Trap 1: Confusing DDL with DML. CompTIA questions frequently ask candidates to identify which command modifies schema architecture vs. table data. Remember: CREATE, DROP, ALTER, and TRUNCATE are DDL (they define structure). SELECT, INSERT, UPDATE, and DELETE are DML (they manipulate row records).
• Trap 2: Forgetting the Catastrophic Consequence of Missing WHERE. If an exam scenario describes an administrator running DELETE FROM customers;, the answer is not an error message. The command will successfully execute and permanently wipe every single customer record from the database.
• Trap 3: Selecting a Relational Database for High-Velocity Graph or Caching Workloads. If a question requires traversing complex multi-hop social relationships or sub-millisecond RAM key lookups, do not select an RDBMS. Graph databases (like Neo4j) and Key-Value stores (like Redis) are explicitly architected for these specialized workloads.
• Trap 4: Believing SELECT Modifies Data. SELECT is strictly a read-only query command. It never alters, locks, or mutates underlying table records on disk.