Starting SQL can feel daunting. It might seem like an entirely new language being thrown at you, but after approaching the core ideas it becomes relatively simple to understand. Lets start with a few definitions.
- Server - This is the actual software process (like PostgreSQL or MySQL) running on a machine.
- Database - It's an organized collection of data structured to be easily accessed, managed, and updated.
- Schema - an organizational layer within a database, used to group related tables together.
- Data - This is raw, unorganized facts and figures and can come in various formats such as numbers, text or images
- DBMS (Database Management System) - is the software that enables users to create, manage, and interact with databases. Some of the most common relational DBMS options you'll encounter are: MySQL, PostgreSQL, Microsoft SQL Server
So What Is SQL Anyway?
SQL (Structured Query Language) is a standard language used to communicate with relational databases. Put simply, it's how we talk to a database and how we ask it to store, change, or hand back information.
It is further divided into sub-languages based on the exact action needed to be performed on the database. In this article we will tackle the three first and most basic sub-languages
DDL - Data Definition Language
DDL commands deal with the structure of the database. This includes creating, changing or deleting the table.
Key commands: CREATE, ALTER, DROP
DML - Data Manipulation Language
DML commands deal with the records inside the database. They modify the data without changing the structure of the table itself.
Key commands: INSERT, UPDATE, DELETE
DQL - Data Query Language
DQL command deals with reading the data inside the database. It ask questions of the data without changing anything about the data or the structure of the database.
Key command: SELECT
Data Types
Every column in a table needs a defined data type, this decides what kind of value that column can hold. Picking the right one keeps your data both accurate and efficient.
Numbers
INT — a whole number
NUMERIC / DECIMAL — numbers with fixed decimal places (useful for prices, where rounding errors are unacceptable)
SERIAL — a number PostgreSQL generates automatically for you, usually used for IDs
Text
CHAR(n) — for text that is always exactly n characters (e.g., a 10-digit phone number stored as a fixed-length code)
VARCHAR(n) — for text up to n characters (variable length, up to a maximum of n)
TEXT — for text with no fixed length limit
Date & Time
DATE — stores a date only
TIME — stores a time only
TIMESTAMP — stores both date and time together
Boolean
BOOLEAN — only two possible values: true or false
Constraints
Constraints are rules applied to columns or tables to limit the type of data that can be inserted, updated, or deleted. They're your first line of defense against messy, inconsistent data.
NOT NULL — the column can never be left empty
DEFAULT(value) — if nothing is given, this value is used automatically
UNIQUE — no two rows may share the same value in this column (e.g., two customers can't share one email address)
PRIMARY KEY — uniquely identifies each row in a table; it's essentially a combination of NOT NULL and UNIQUE
FOREIGN KEY — a column whose values must already exist as a primary key somewhere else; this is how tables link to each other
CHECK — a custom rule the value must pass, e.g., price > 0
Building A School Database
Lets build a database including what we have learned and exploring new ideas in querying, filtering and updating.
Creating the table
The SET SEARCH_PATH command ensures the data we input is sent to the relevant schema. Alternatively you can refer to the schema every time you reference the table you're working on e.g FROM greenwood_academy.students
Altering the table
Tables are rarely perfect on the first try. ALTER TABLE is DDL's way of letting you change a table's structure without dropping and recreating it.
Adding a column - the school forgot to capture phone numbers:
Renaming a column - if credit should really be called credit_hours:
Dropping a column - if phone_number turns out not to be needed after all:
Fixing and Removing Data
Once rows exist, DML gives you three tools: INSERT to input the values, UPDATE to correct existing rows and DELETE to remove them.
INSERT adds the values to the table following the constraints put while building the tables.
UPDATE changes the value of one or more columns in rows that match a condition:
DELETE removes whole rows that match a condition:
Disclaimer:The WHERE clause is doing critical work in every one of these statements. Leave it off an UPDATE or DELETE, and you'll change or remove every row in the table. Always double-check your WHERE clause before running either of these.
Filtering Rows with WHERE
WHERE is how you narrow a SELECT down to only the rows you care about, using comparison operators (=, !=, >, <, >=, <=) and logical operators (AND, OR, NOT) to combine conditions.
AND narrows your results (every condition must hold), while OR widens them (any condition can hold), mixing the two up is one of the most common beginner mistakes in SQL.
Range, Membership, and Pattern Matching
Beyond basic comparisons, SQL gives you a few operators purpose-built for common filtering patterns.
BETWEEN checks whether a value falls within an inclusive range. This mostly used for both numbers and dates:
IN and NOT IN check membership against a list of values, saving you from writing a long chain of OR conditions:
LIKE matches text patterns using % as a wildcard for any number of characters:
Note the % on both sides of 'Studies' in that last query: %Studies% matches the word anywhere in the name, while %Studies (with only a leading %) would only match names that end in "Studies"
Summarizing Data with COUNT
COUNT is an aggregate function — instead of returning individual rows, it returns a single number summarizing how many rows matched. Giving the result a readable alias with AS (like form3_students) makes the output much easier to interpret than a column literally named count.
Adding Conditional Logic with CASE WHEN
Sometimes you want a query to label rows based on a condition, without writing separate queries for each case. CASE WHEN works like an if/else chain, evaluated top to bottom for each row, and lets you create a new derived column right inside your SELECT.
Something to note from the above examples, CASE WHEN checks conditions in order and stops at the first match, so put your most specific conditions first (marks >= 80 is checked before marks >= 60).
Conclusion
SQL can feel like a lot of new vocabulary at first, but it boils down to a few core ideas:
- A server hosts databases, which are organized into schemas.
- A DBMS is the software managing all of this.
- SQL is how you talk to it, split into sub-languages including DDL (structure), DML (data changes) and DQL (reading data).
- Data types define what a column can hold, and constraints define what values are actually allowed in.
- ALTER TABLE lets a schema evolve after tables already exist by adding, renaming, or dropping columns.
- UPDATE and DELETE modify or remove existing rows, always guided by a WHERE clause.
- WHERE, combined with comparison and logical operators, is how you filter a SELECT down to exactly the rows you need.
- BETWEEN, IN/NOT IN, and LIKE handle ranges, lists, and text patterns.
- COUNT summarizes matching rows into a single number, and CASE WHEN lets a query label rows with its own conditional logic.
With just CREATE TABLE, INSERT, and SELECT, you already have enough to build and query a real relational database — and with ALTER TABLE, UPDATE, DELETE, WHERE, and CASE WHEN in your toolkit, you can maintain that database and ask real questions of it.
Top comments (1)
I appreciated how the article broke down the basics of SQL, especially the explanation of DDL, DML, and DQL sub-languages, which can be overwhelming for beginners. The use of concrete examples, such as the school database, helped to illustrate the concepts, like creating tables and applying constraints. I've found that understanding the different data types, such as NUMERIC and SERIAL, is crucial for efficient database design. What are some common pitfalls or best practices to keep in mind when choosing data types for a new database schema?