DEV Community

Cover image for Welcome to the World of SQL
Mungai M.
Mungai M.

Posted on

Welcome to the World of SQL

#sql #dataengineering #tutorial #assignment

Your First Step into Data Analysis

You might have wondered how your favorite streaming app instantly recommends the perfect movie, or how your bank retrieves your transaction history the second you log in. Behind the scenes, these platforms are almost certainly using SQL.

SQL, which stands for Structured Query Language, is the standard language we use to communicate with databases, asking them to find, organize, or update information. Think of it as the ultimate, super‑powered search bar for raw data. It allows you to ask complex questions, organize massive amounts of information, and update records in the blink of an eye. Whether you want to know how many pairs of shoes a store sold last Tuesday or which customers signed up for a newsletter in the past hour, SQL is the tool that makes finding those exact answers possible.

The Digital Filing Cabinet

To really understand SQL, it helps to first understand what a database actually is. If data is the raw information; like individual receipts, customer names, or the prices of coffee; a database is the secure container that holds it all.

Imagine a massive, highly organized digital filing cabinet. Inside this cabinet, you have different drawers, which we call tables. Instead of a chaotic pile of loose papers, each table is structured much like a spreadsheet with a neat grid of rows and columns. Every row represents a single file or record (like one specific customer), and every column represents a specific attribute (like that customer’s email address).

When building these digital filing cabinets, developers typically choose between two main database types: relational and NoSQL. You would pick a relational database (traditionally accessed using SQL) when your data is highly structured and requires absolute accuracy, like financial ledgers or inventory systems. You might pick a NoSQL database when you are dealing with flexible, unstructured, or rapidly changing data like social media posts.

Speaking the Language of Data

Even though SQL is considered a single language, it actually acts like a Swiss Army knife with several core sublanguages, each assigned to a specific job:

  • DDL (Data Definition Language): This is the builder; it sets up and alters the actual structure of your tables and database.
  • DML (Data Manipulation Language): This handles the day‑to‑day operations, allowing you to insert, update, or delete the actual rows of data.
  • DQL (Data Query Language): This is how you ask questions and fetch the exact information you want to see.
  • DCL (Data Control Language): This acts as the security guard, managing permissions and deciding who gets access to the database.
  • TCL (Transaction Control Language): This is your safety net, allowing you to permanently save (commit) your changes or hit undo (rollback) if something goes wrong.

To keep everything running smoothly, databases use something called data types. Data types matter immensely because they tell the database exactly what kind of information is allowed in each column. This prevents messy errors (like accidentally saving a name in a phone number field) and makes searching the database incredibly fast and efficient.

Here are some of the most common types you will encounter:

  • Integer: Whole numbers without decimals, like 42 or 100.
  • Text/Varchar: Words, names, or alphanumeric characters of varying lengths, like "Coffee Mug".
  • Numeric/Decimal: Exact numbers with decimal points, perfectly suited for a price like 19.99.
  • Timestamp: A specific calendar date and exact time, like 2026-04-13 09:41:00.
  • Boolean: Simple true or false values, used for things like checking if an item is currently in stock (TRUE).
  • JSON: A flexible format used to store nested or unstructured data, like a customer’s specific color and size preferences all in one spot.

Rules of the Game: Schemas and Constraints

To prevent your beautiful filing cabinet from turning into chaos, databases use a schema (the blueprint) and constraints (the strict rules).

A primary key acts as a unique identifier for every single row, ensuring no two records are ever confused. A foreign key acts as a bridge, linking information across different tables so they can talk to each other without duplicating data.

Other constraints keep the data perfectly clean:

  • NOT NULL enforces a strict rule that a field can never be left completely empty.
  • UNIQUE guarantees that no two entries in a column are identical, which is perfect for user email addresses.
  • A CHECK constraint enforces a specific logical rule that the data must pass. For example, you can ensure a product’s price is always greater than zero with a tiny snippet of SQL: 
CHECK (price > 0)
Enter fullscreen mode Exit fullscreen mode

Getting Hands-On: A Mini Example

Let’s bring this all together with a concrete example. Imagine you manage a tiny fictional table called products for a local coffee shop.

products
───────────────
product_id | name   | category | price | in_stock
----------+--------+----------+-------+---------
1         | Coffee | Beverage |  3.50 | TRUE
2         | Tea    | Beverage |  2.50 | TRUE
3         | Mug    | Merch    | 12.00 | FALSE
4         | T-Shirt| Merch    | 20.00 | TRUE
Enter fullscreen mode Exit fullscreen mode

If you wanted to filter this table to only show the names and prices of your beverages, you would use the SELECT and WHERE keywords:

SELECT name, price
FROM products
WHERE category = 'Beverage';
Enter fullscreen mode Exit fullscreen mode

What if you wanted to count exactly how many items you have in each category? You can use COUNT to tally them up and GROUP BY to organize the results into neat buckets:

SELECT category, COUNT(product_id)
FROM products
GROUP BY category;
Enter fullscreen mode Exit fullscreen mode

Finally, if you want to create custom labels on the fly; like categorizing your items into pricing tiers; you can use the CASE WHEN expression. It evaluates your rules row by row and outputs a new category:

SELECT
  name,
  price,
  CASE
    WHEN price > 10 THEN 'Premium'
    ELSE 'Standard'
  END AS price_tier
FROM products;
Enter fullscreen mode Exit fullscreen mode

5 Practical Tips for Beginners

As you start your journey into data analysis, keep these five actionable tips in mind:

  1. Choose the right data types. Always pick the smallest, most specific data type for your columns to keep your database running fast and save storage space.
  2. Avoid the SELECT * trap. Instead of grabbing every single column with an asterisk, only select the exact columns you actually need so you do not slow down the system or fetch unnecessary data.
  3. Test queries safely. Never run a DELETE or UPDATE command without double‑checking your WHERE clause, or you might accidentally overwrite or delete every single row in your table.
  4. Use LIMIT for exploration and pagination. When exploring a massive table for the first time, add a LIMIT (or your database’s equivalent) to the end of your query to prevent overwhelming your screen with millions of rows.
  5. Explore learning resources. Practice makes perfect, so try out free interactive websites like SQLZoo, SQLBolt, or DataLemur to get comfortable writing real queries right in your browser.

Now that you know the basics of how databases work, jump into a free sample dataset and try writing your very first query today!

This article was written to help new data engineers, build an appetite for the Structured Query Language. The article was submitted in fulfilment of a LuxDevHQ Cohort 7 DataEngieering assignment ©adev3loper

Top comments (0)