An Introductory SQL Tutorial: How to Write Simple Queries Effectively

Why SQL Matters in Bespoke Software Development

A beginner friendly guide

Structured Query Language (SQL) is the standard way to work with a relational database. If you can read and write SQL, you can retrieve data, change data, and analyse data with speed and accuracy.

At ioSTUDIOS, SQL sits at the core of the systems we build. From CRM tools to ecommerce platforms, SQL keeps database objects reliable, scalable, and simple to query.

1) What SQL is and why it matters

SQL is a programming language for storing and managing data in tables and columns. Spreadsheets struggle with millions of rows. SQL databases are built for that scale. They keep data structured, secure, and easy to access.

Every developer at ioSTUDIOS uses SQL to select data, update data, and join tables to produce useful results. Because SQL is a common standard across SQL Server, PostgreSQL, MySQL, and others, it powers most modern business systems.

2) Relational databases in plain terms

A relational database organises information into tables. Each table has columns and rows. Tables link to each other through keys:

• Primary key identifies a row in its own table.
• Foreign key points to a row in another table.

Popular systems include SQL Server, MySQL, Oracle, and PostgreSQL. Syntax may vary a little, for example Transact SQL (T‑SQL) in SQL Server, but the core SQL language stays consistent. We design solutions so your database is scalable, secure, and optimised for your needs.

3) Setting up your SQL environment

Choose tools that match your database:

• SQL Server: SQL Server Management Studio (SSMS) or Azure Data Studio
• MySQL: MySQL Workbench
• PostgreSQL: pgAdmin

Create a small practice table and run simple queries to learn the basics. A common convention is to write SQL keywords in uppercase and table or column names in lowercase. We follow the same approach in our projects.

Example practice table

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name  VARCHAR(50),
  last_name   VARCHAR(50),
  department  VARCHAR(50),
  salary      DECIMAL(10,2)
);

4) Writing basic queries

Selecting data

SELECT reads data from a table.

SELECT first_name, last_name
FROM employees;

To fetch every column:

SELECT *
FROM employees;

Best practice is to list only the columns you need. This keeps queries fast and results clear.

Filtering rows

Use WHERE to narrow results. Combine conditions with AND or OR.

SELECT *
FROM employees
WHERE department = 'Sales';

Inserting, updating, and deleting data

• INSERT adds new rows
• UPDATE changes existing rows
• DELETE removes rows

INSERT INTO products (product_name, price)
VALUES ('Chair', 49.99);
UPDATE products
SET price = 39.99
WHERE product_name = 'Chair';
DELETE FROM products
WHERE product_name = 'Chair';

Important: always include a WHERE clause with UPDATE and DELETE. Without it, the query will change every row in the table. When possible, test the WHERE using a SELECT first.

5) Joining multiple tables

Most databases spread information across several tables. JOIN combines rows that relate to each other.

• INNER JOIN returns only matching rows in both tables.
  Use it for standard links such as customers and their orders.
• LEFT JOIN returns all rows from the left table and matching rows from the right table.
  Use it when the left table holds the main business context.
• RIGHT JOIN returns all rows from the right table and matches from the left table.
  Less common. You can often swap table order and use a LEFT JOIN instead.

Example

SELECT o.order_id, c.customer_name, o.order_total
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.customer_id;

6) Analysing data with aggregates

When you need totals, averages, or extremes, use aggregate functions:

• COUNT() counts rows
• SUM() adds values
• AVG() returns averages
• MAX() and MIN() find highest and lowest values

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

GROUP BY vs HAVING

• GROUP BY groups rows to produce one result per group.
• HAVING filters groups after aggregation.
  WHERE filters rows before aggregation.

SELECT department, SUM(salary) AS payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > 250000;

7) Views and stored procedures

• Views are saved queries. They simplify repeated logic and control what data users can see.
  Example: a view for monthly revenue that reports can reuse.
• Stored procedures are saved blocks of SQL that can accept parameters and run several steps as one unit.
  They help insert data, enforce business rules, and improve security and consistency.

8) Simple performance wins

• Indexes speed up searches on columns that appear in WHERE, JOIN, or ORDER BY.
• Subqueries place one query inside another. Use with care to keep plans efficient.
• Query optimisation means selecting only what you need, filtering early, and avoiding unnecessary work.

At ioSTUDIOS we apply these techniques so bespoke systems stay fast as data grows.

9) Best practices for clean, reliable SQL

• Use clear, descriptive names for tables and columns.
• Avoid SELECT * unless you truly need every column.
• Comment complex queries so others can maintain them.
• Handle NULL values explicitly.
• Sort results only when the order matters.
• Test UPDATE and DELETE with a SELECT first, and use transactions for safety.

Conclusion

This quick tutorial covered how to:

• read data with SELECT
• change data with INSERT, UPDATE, and DELETE
• combine tables with JOIN
• summarise information with aggregates, GROUP BY, and HAVING
• reuse logic with views and stored procedures
• keep performance high with indexing and careful query design

These skills map directly to modern systems. They are the same techniques we use in ioSTUDIOS projects, from small prototypes to enterprise databases.

FAQs

What is the role of SQL in bespoke software?
SQL is the standard language for working with a relational database. Our developers use it to retrieve data with SELECT, manage database objects, and power core business features.

How do JOINs help with reporting across tables?
JOINs match rows from different tables using related keys. This produces one result set that ties areas of the system together, for example linking customers to their order history.

Why use stored procedures?
Stored procedures bundle repeatable logic in one place. They can insert data, delete data, or run calculations with parameters. We use them to standardise rules and improve performance.

What is the risk of using SELECT *?
It returns every column, which can move more data than needed and slow queries. Listing only the required columns makes queries faster and easier to maintain.

Looking to build a bespoke system with smart database design and clear SQL? Get in touch with ioSTUDIOS to turn data into lasting business value.