SQL Lingo

SQL functions + more

What is SQL?

SQL (Structured Query Language) A programming language used to query, manipulate, and manage data stored in relational databases — it's the standard language for talking to a database.Core operations (CRUD):

sql

SELECT   -- read/retrieve data
INSERT   -- add new data
UPDATE   -- modify existing data
DELETE   -- remove data

Quick example:

sql

SELECT name, age 
FROM users 
WHERE age > 30;

"Give me the name and age of everyone older than 30 from the users table."

Where it fits:

Database → SQL (query & manipulate) → Results/Analysis

Think of it as the language you use to have a conversation with your database — asking it questions and telling it what to do.

💡 lil note: You've already seen SQL-like syntax in HiveQL — because Hive was built to mimic SQL for big data.

Table

The structure that holds your data — made up of rows and columns, like a spreadsheet within a database.

users table:
| id | name  | age |
|----|-------|-----|
| 1  | Alice | 30  |

Row (Record)

A single entry in a table — represents one item or event.

| 1  | Alice | 30  |  ← one row/record

Column (Field)

A single attribute or category of data across all rows.

| name  |  ← one column holding everyone's name

Primary Key

A unique identifier for each row in a table — no two rows can have the same value.

id column → 1, 2, 3 (always unique, never null)

Foreign Key

A column in one table that links to the primary key of another table — how tables relate to each other.

orders table → user_id (foreign key linking back to users table)

Query

Any instruction you write in SQL to retrieve or manipulate data.

SELECT * FROM users;  ← this is a query

JOIN

Combining rows from two or more tables based on a related column.

SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id

Index

A performance tool that makes searching and querying a table faster — like a book index, it helps the database find data without scanning every row.

NULL

Represents a missing or unknown value in a table — not zero, not empty, just absent.

WHERE age IS NULL   -- finds rows with no age value

Aggregate Functions

Functions that perform a calculation on a set of rows and return a single value.

COUNT()   -- number of rows
SUM()     -- total value
AVG()     -- average value
MIN/MAX() -- lowest/highest value