SQL Statements and Database Objects

What it is

Structured query language, or SQL, is the standard language for working with relational databases. Its statements fall into groups by purpose. Some define structure, some work with data and some control access.

Alongside tables, a database holds other objects such as views, indexes and stored procedures that make data easier or faster to use.

How it works

Statement groups

CREATE TABLE Products (ProductID INT PRIMARY KEY, Name VARCHAR(50), Price DECIMAL(8,2));
INSERT INTO Products VALUES (1, 'Pen', 1.20);
UPDATE Products SET Price = 1.50 WHERE ProductID = 1;
DELETE FROM Products WHERE ProductID = 1;
GRANT SELECT ON Products TO analyst1;

An UPDATE or DELETE without a WHERE clause changes every row in the table, which is a classic mistake.

The parts of a query

A SELECT statement reads data. Each clause has one job.

SELECT Region, SUM(Total) AS Revenue
FROM Orders
WHERE OrderDate >= '2025-01-01'
GROUP BY Region
HAVING SUM(Total) > 10000
ORDER BY Revenue DESC;

Common aggregate functions are COUNT, SUM, AVG, MIN and MAX. The database applies FROM first, then WHERE, GROUP BY, HAVING, SELECT and ORDER BY last.

Joins

A join combines rows from two tables using a matching column, usually a foreign key.

SELECT c.Name, o.Total
FROM Customers AS c
INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID;

An INNER JOIN returns only rows that match on both sides. A LEFT JOIN keeps every row of the left table and fills in nulls where there is no match. A RIGHT JOIN does the same for the right table, and a FULL JOIN keeps unmatched rows from both.

Database objects

A clustered index decides the physical order of the rows in a table, so a table can have only one. A table can have many non clustered indexes, each a separate structure that points back to the rows.

A worked scenario

Managers keep asking for last month's revenue by region. A DBA saves the query as a view named MonthlyRevenue, so managers run a simple SELECT and never see the underlying joins. To make it fast, an index is added on OrderDate. For month end closing, a stored procedure runs the same steps every time with a date as its parameter. The users of each object need only the permission for that object, which a GRANT statement provides.

Reading a query step by step

Take the query that totals revenue by region. The database first reads Orders, then keeps only rows from 2025 onward, then forms one group for each region. It adds up Total in each group, discards groups of 10,000 or less, and finally sorts what remains from highest to lowest. Reading in this order, rather than top to bottom, explains why WHERE cannot use a total and HAVING can.

Handy operators and NULL

NULL means unknown or missing. It is not zero and it is not an empty string, and = NULL never matches. You must write IS NULL.

Aggregate functions

COUNT(*) counts all rows in a group, while COUNT(Column) counts only rows where that column is not NULL.

Why use a view?

A view hides complexity and protects data. It can join several tables so users see one simple object, and it can expose only some columns. You can then GRANT access to the view without granting access to the tables behind it.