Study guide
10.3 Non-Relational Databases & Core SQL Commands
CompTIA Tech+ FC0-U71
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: IT Concepts & Terminology
1.1 CompTIA Tech+ Overview & The Computing Cycle 1.2 Notational Systems & Character Representation 1.3 Units of Measure for Storage, Throughput & Speed
2 Chapter 2: Troubleshooting Methodology & Computing Devices
2.1 CompTIA 6-Step Troubleshooting Process 2.2 Computing Device Categories & Purposes 2.3 IoT, Smart Devices & Specialized Hardware
3 Chapter 3: Internal Hardware Components & System Storage
3.1 Motherboards, CPUs & Memory Architecture 3.2 Firmware, Power Delivery & Hardware Configuration 3.3 Storage Media & Drive Technologies
4 Chapter 4: Peripherals, Interfaces & Connections
4.1 Peripheral Device Types & Configuration 4.2 Wired I/O Interfaces & Video Cable Standards 4.3 Wireless Peripheral Connectivity & Short-Range RF
5 Chapter 5: Networking Technologies & Internet Services
5.1 Internet Access Technologies & Connection Types 5.2 Network Scopes & Physical Infrastructure 5.3 Wi-Fi Standards, Bands & Performance 5.4 IP Addressing & Fundamental Network Protocols
6 Chapter 6: Virtualization, Cloud Computing & AI Fundamentals
6.1 Virtualization Concepts & Hypervisor Types 6.2 Cloud Architecture & Service Categories 6.3 Artificial Intelligence Concepts & Modern Use Cases
7 Chapter 7: Operating Systems Architecture & Management
7.1 Operating System Categories & Core Functions 7.2 OS Architecture, Process Management & Utilities 7.3 File Systems, Directory Trees & Access Control
8 Chapter 8: Applications, Software Licensing & Web Browsing
8.1 Software Applications & Business Productivity Suites 8.2 Software Deployment Architectures & Licensing 8.3 Web Browser Architecture & Security Configuration
9 Chapter 9: Software Development & Programming Concepts
9.1 Programming Language Categories & Paradigms 9.2 Data Types, Variables & Identifiers 9.3 Programming Logic, Control Flow & Data Structures
10 Chapter 10: Data Concepts, Analytics & Database Fundamentals
10.1 The Value of Data, Information & Big Data Analytics 10.2 Relational Database Concepts & Schema Structure 10.3 Non-Relational Databases & Core SQL Commands
11 Chapter 11: Data Protection, Backup & Recovery
11.1 Data Backup Methodologies & Recovery Strategies 11.2 Backup Media Destinations & Disaster Recovery
12 Chapter 12: Security Concepts, Access Control & Device Hardening
12.1 Foundational Security Principles & the CIA Triad 12.2 Security Threats, Social Engineering & Malware Defense 12.3 Physical Security & Endpoint Hardening Practices
13 Chapter 13: Identity, Encryption & Wireless Network Security
13.1 Password Security Practices & Multi-Factor Authentication 13.2 Encryption Concepts & Cryptographic Applications 13.3 Wireless Network Security & Access Controls
10.3 Non-Relational Databases & Core SQL Commands
Key Takeaways Explain This Topic AI
Non-relational (NoSQL) databases emerged to support horizontal scaling (scale-out across distributed commodity clusters), flexible dynamic schemas (schema-on-read), and high-velocity ingestion of unstructured and semi-structured datasets.
The four principal NoSQL models serve specialized workloads: Key-Value stores (ultra-fast caching and session state), Document databases (hierarchical JSON/BSON content catalogs), Column-Family stores (high-throughput time-series telemetry), and Graph databases (complex multi-hop relationship networks).
Structured Query Language (SQL) utilizes a declarative paradigm, partitioned into Data Definition Language (DDL) for structural schema management (CREATE, DROP, ALTER) and Data Manipulation Language (DML) for record operations (SELECT, INSERT, UPDATE, DELETE).
Core SQL query clauses execute in logical sequence (SELECT, FROM, WHERE, ORDER BY, LIMIT), where the WHERE clause filters rows based on explicit conditions prior to sorting or returning results.
Omitting a WHERE clause during an UPDATE or DELETE command is a catastrophic administrative error that alters or erases every record across the entire table, while INNER JOIN operations link normalized tables across primary and foreign key constraints.
Last updated: September 2026
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 ) O(1) 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:
Nodes (Vertices): Entities or objects (e.g., a Person, a Company, an Airport, a Device).
Edges (Relationships): Directed or undirected links connecting two nodes (e.g., FRIENDS_WITH , WORKS_FOR , TRANSFERRED_MONEY_TO , CONNECTED_TO ).
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.
NoSQL Model Data Organization Primary Strength Dominant Use Cases Concrete Technologies
Key-Value Key → \rightarrow → Value (Arbitrary blob) Sub-millisecond lookup speed via hash index Session state, RAM caching, shopping carts Redis, Amazon DynamoDB, Memcached
Document Hierarchical JSON / BSON documents Flexible schema, deep nested queries E-commerce catalogs, CMS, user profiles MongoDB, Couchbase, Amazon DocumentDB
Column-Family Rows contain variable Column Families Massive write throughput, distributed scale IoT sensor feeds, time-series telemetry Apache Cassandra, Google Bigtable, ScyllaDB
Graph Nodes (Entities) and Edges (Relationships) High-speed multi-hop relationship traversal Social networks, fraud detection, IT topologies Neo4j, Amazon Neptune, TigerGraph
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 → Z , 0 → 9 A \rightarrow Z, 0 \rightarrow 9 A → Z , 0 → 9 (the default behavior).
DESC (Descending): Highest to lowest, Z → A , 9 → 0 Z \rightarrow A, 9 \rightarrow 0 Z → A , 9 → 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:
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.
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;
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.