SQL Study Notes Part I: Foundations of Structured Query Language
SQL fundamentals covering data retrieval, filtering, arithmetic, table creation, updates, deletes, NULL handling, and safe querying for beginners.
Data sits at the core of modern technology, analytics, and security operations. Whether analysts are reviewing logs, investigating incidents, or building dashboards, structured access to data is essential. SQL, or Structured Query Language, remains the most widely used language for interacting with relational databases.
This installment of the SQL Study Notes series introduces the foundational concepts of SQL, focusing on clarity, reproducibility, and practical usage. Part I is designed to help learners understand how to retrieve, filter, modify, and manage data safely and efficiently using core SQL statements.
Rather than relying on spreadsheets or manual exports, SQL allows users to work directly with data at scale, reducing errors and improving auditability across analytical workflows.
Download SOC Analyst Study Notes Part IV here ⬇️
Who This Document Is For
This guide is intended for beginners and early practitioners building confidence with data querying:
Students learning SQL for data analysis or technical roles
Analysts transitioning from spreadsheets to databases
Professionals working with structured datasets
Anyone seeking a clear, step-by-step introduction to SQL fundamentals
The progression starts with basic data retrieval and moves through filtering, arithmetic operations, and data modification commands.
First Time Seeing This? Please Subscribe
What Is SQL
Structured Query Language is a programming language specifically designed to manage and manipulate data stored in relational databases. SQL enables users to retrieve records, filter datasets, perform calculations, and modify database content in a controlled and auditable manner.
One of SQL’s strengths is its accessibility. The language is semantically straightforward, making it easier to learn than many general-purpose programming languages. At the same time, it provides direct access to large datasets without the need to extract data into external tools, improving accuracy and consistency.
Retrieving Data with SELECT
The SELECT statement is the foundation of SQL. It allows users to retrieve data from a table, either in full or by specifying individual columns.
Users can select all columns from a table or limit output to only the fields relevant to the analysis. Column aliases can also be applied to improve readability without altering the underlying database structure.
The LIMIT clause helps control result size, while the WHERE clause enables filtering records based on defined conditions. Together, these commands form the backbone of everyday data exploration and reporting.
Filtering Data with Comparison Operators
SQL provides comparison operators to refine queries and isolate specific records. These include equality, inequality, greater-than, and less-than comparisons.
Using comparison operators within a WHERE clause allows analysts to filter datasets by values such as location, time period, or numeric thresholds. This capability is essential for narrowing large datasets into meaningful subsets without altering the underlying data.
Arithmetic Operations in SQL
SQL supports arithmetic operations such as addition, subtraction, multiplication, and division. These operations can be performed across columns within the same row to create derived values.
For example, analysts can combine multiple columns to calculate totals or averages. When calculations need to span multiple rows, aggregate functions are required, which are covered in later parts of the series. Understanding row-level arithmetic is a key step toward more advanced analysis.
Creating Tables
The CREATE TABLE statement defines the structure of a new table within a database. This includes specifying column names, data types, and optional constraints.
Creating tables establishes the schema that governs how data is stored and queried. A well-designed table structure improves data integrity, readability, and long-term maintainability of databases.
Inserting Data into Tables
The INSERT INTO statement is used to add new records to a table. Users can specify both column names and values or insert values for all columns when the table structure is known.
This command allows structured data entry while respecting the table’s predefined schema and constraints, ensuring consistency across records.
Understanding NULL Values
A NULL value represents the absence of data. It is not equal to zero or an empty string and cannot be evaluated using standard comparison operators.
SQL provides the IS NULL and IS NOT NULL operators to explicitly test for missing values. Proper handling of NULLs is critical for accurate filtering, reporting, and data integrity.
Updating Existing Records
The UPDATE statement modifies existing records within a table. It allows users to change one or more fields based on a defined condition.
The document emphasizes caution when using UPDATE. Omitting the WHERE clause results in all records being modified, which can cause irreversible data changes. Precision and verification are essential when updating live datasets.
Deleting Records Safely
The DELETE statement removes records from a table based on specified criteria. Like UPDATE, DELETE must be used carefully, as removing records without a WHERE clause deletes all rows while leaving the table structure intact.
Understanding the scope and impact of DELETE operations is essential for safe database management.
Using Aliases for Readability
Aliases provide temporary names for columns or tables within a query. They improve readability and make query results easier to interpret, especially when working with calculated fields or complex expressions.
Aliases do not alter the database itself and exist only for the duration of the query, making them a safe and flexible tool for analysis.
Conclusion
SQL Study Notes Part I establishes a strong foundation for working with relational data. By mastering core commands such as SELECT, WHERE, INSERT, UPDATE, and DELETE, learners gain the ability to retrieve and manage data accurately and efficiently.
These fundamentals prepare readers for more advanced topics in later parts of the series, including aggregation, joins, and analytical querying. SQL remains a critical skill for anyone working with data, and a solid grasp of its basics is an essential first step.
If you find this material useful, subscribe to CyberMaterial’s Substack for daily cybersecurity briefings, practical insights, and structured learning resources.
Really solid breakdown of SQL essentials. The emphasis on WHERE clauses before UPDATE/DELETE operations is critical and something I learned the hardway early in my analytics work. One thing I'd add is that practicing NULL handling across different database engines can be suprisingly inconsistent, especially when JOINs get involved. The progresion from basic SELECT to safe data modification is spot on tho.