Introduction to SQL

What SQL is, how relational databases store data, and the five command families every statement belongs to.

What is SQL?

SQL (Structured Query Language) is a standard programming language used to create, modify, query, and manage relational databases.

Why SQL?

SQL is used to store data, retrieve data, update data, and perform operations on relational databases—it is the interface between you and the tables your company runs on.

KEY BENEFITS

Easy to learnData integrityData securityReduced redundancyStandard languageEasy data retrieval

Data Storage Systems

Before writing a query, it helps to know which layer of the data stack you are talking to.

Database

A collection of related data.

DBMS

Software used to manage a database.

RDBMS

A DBMS that stores data in related tables and enforces relationships with keys and constraints. PostgreSQL—the database behind Queryflo—is an RDBMS.

Data Warehouse

A large-scale system built to analyze combined data from many sources rather than run the live application. Examples include BigQuery, Redshift, and Snowflake.

SQL Architecture

Every query travels through the same chain, whether it starts in a dashboard, an application, or a terminal.

Client / User

SQL Interface

PostgreSQL Server

Queryflo Database

Result / Output

Note: The user sends a request, PostgreSQL processes it, the server reads the database, and the result returns to the user.

Types of SQL Commands

Every SQL statement belongs to one of five categories:

DDLData Definition LanguageCREATE · ALTER · DROP · TRUNCATE

DMLData Manipulation LanguageINSERT · UPDATE · DELETE

DQLData Query LanguageSELECT

DCLData Control LanguageGRANT · REVOKE

TCLTransaction Control LanguageCOMMIT · ROLLBACK · SAVEPOINT

Database, Tables & Data Types

Create a Database

CREATE DATABASE queryflo_learning;

Create a Table

Every column needs a name, a data type, and a clear purpose. Queryflo already owns the table below; the DDL is shown to explain its structure and is not run in the practice sandbox.

Column Name	PostgreSQL Type	Description
id	integer	Campaign identifier (primary key)
name	text	Campaign name
channel	text	Acquisition channel
spend	numeric	Campaign spend
start_date	date	Campaign start date
end_date	date	Campaign end date
status	text	Current campaign status
target_segment	text	Audience segment

CREATE TABLE marketing.campaigns (
  id integer PRIMARY KEY,
  name text NOT NULL,
  channel text,
  spend numeric,
  start_date date,
  end_date date,
  status text,
  target_segment text
);

PRIMARY KEY

A column—or combination of columns—that uniquely identifies each row. Here, id uniquely identifies a campaign.

Common Data Types

integer — whole numbers such as a campaign ID
text — strings such as campaign names and channels
numeric — exact decimal values such as campaign spend
date — calendar dates such as campaign start and end dates
boolean — true/false values, used by tables such as product.ab_tests.converted
timestamptz — a timestamp with timezone, used by tables such as growth.users.created_at

Example Table: marketing.campaigns

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

Categories of SQL Commands

Now go one level deeper into each category, starting with the one that shapes the table itself.

DDLData Definition Language

DDL commands define and change a table’s structure—not its rows.

CREATE — build a new table, database, or object
ALTER — change an existing table’s structure
DROP — permanently remove a table or database
TRUNCATE — remove all rows while preserving the table structure

Watch DDL in actionStep 0 of 5

— marketing.campaigns, as defined above —

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

Starting table — press Next to run each simulated DDL command

DQLData Query Language

DQL is the command family analysts use most: SELECT. It reads data without changing it. Every WHERE, JOIN, and GROUP BY lesson in this course is a deeper use of DQL.

SELECT name, spend
FROM marketing.campaigns
WHERE channel = 'google_ads';

DCLData Control Language

DCL controls who is allowed to access the data.

GRANT gives a user or role permission.
REVOKE takes that permission away.

Access controlmarketing.campaigns

analyst_role has no access to marketing.campaigns

TCLTransaction Control Language

TCL controls when a write becomes permanent. A change remains pending until you choose its outcome.

COMMIT makes the change permanent.
ROLLBACK returns to the last committed state.
SAVEPOINT marks a partial rollback point.

Pending transactionsimulation only

UPDATE marketing.campaigns SET spend =65000.00

Uncommitted — choose an outcome

DMLData Manipulation Language

DML is how data enters and changes inside the structures created by DDL.

INSERT adds new rows.
UPDATE modifies existing rows.
DELETE removes selected rows.

UP NEXT

Start reading data with SELECT.

Queryflo practice stays read-only, so the next lessons focus on DQL and business analysis.

Continue to How SQL Queries Work

Beginner business practice