Sign in Subscribe
3 min read

SQL Query Syntax

In this article, we will delve into the syntax of SQL queries, breaking down the key components and providing examples to enhance understanding of SQL syntax and additional keywords. For the purpose of this article, we won't be discussing every SQL keyword but the ones that are frequently are used.

Anatomy of a SQL Query:

We've seen these keywords frequently in previous articles: the SELECT, FROM, and WHERE keywords. The basic structure of a SELECT query, which is used to retrieve data, looks like this:

SELECT column1, column2, ...
FROM table
WHERE condition;

Let's break down each component:

  1. SELECT Clause: Specifies the columns you want to retrieve from the table
    SELECT FirstName, LastName
    FROM Employees;
  2. FROM Clause: Specifies the table from which to retrieve the data
    SELECT *
    FROM     Orders;
  3. WHERE Clause: Filters the results based on a specified condition
    SELECT ProductName, Price
    FROM Products
    WHERE Price > 50;
  4. Column AS keyword: Consider this example. The AS keyword helps create an alias for a column we want to query. The resulting data set will be specified by that alias
SELECT column AS c1 FROM table;
  1. Consider the tableName.columnName structure in our query. This is done to eliminate ambiguity when running a query that may contain various tables with the same column name. This is commonly seen in queries involving JOINs and Foreign Keys
SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Filtering and Sorting:

SQL queries often involve filtering and sorting data. The WHERE clause is used for filtering, and the ORDER BY clause is used for sorting.

SELECT ProductName, Price
FROM Products
WHERE CategoryID = 1
ORDER BY Price DESC;

In this example, we retrieve product names and prices from the "Products" table where the category is 1, and we order the results by price in descending order (DESC).

An example use case can be that we want to find the top priced product, and ordering the result will immediately give us that information in the first row

SQL Operators:

There are instances in our query in which the WHERE clause can be altered to meet a specific condition. It is not limited to the = operator for comparison

There are operators for =, !=, <>, <, >, <=, >=: These operators are used to compare values.

BETWEEN ... AND ...: Checks if a value is within a range.
IN: Checks if a value is within a set of values.
LIKE: Compares a value to a pattern using wildcard characters.
IS NULL, IS NOT NULL: Checks if a value is (or is not) NULL.

Aggregation Functions:

SQL provides several aggregation functions to perform calculations on data. Common functions include COUNT, SUM, AVG, MIN, and MAX.

SELECT AVG(Price) AS AvgPrice
FROM Products
WHERE CategoryID = 2;

Here, we calculate the average price of products in category 2 using the AVG function.

Grouping Data:

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregation functions to perform calculations on each group. We see the COUNT() aggregation in this query where find the total number of customers and use the GROUP BY clause to list the number of customers by each country

SELECT Country, COUNT(CustomerID) AS CustomerCount
FROM Customers
GROUP BY Country;

This query counts the number of customers in each country.

Subqueries:

Subqueries are queries embedded within other queries. They can be used in various parts of a SQL statement, such as the SELECT, FROM, or WHERE clauses.

SELECT ProductName, Price
FROM Products
WHERE 
  CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName = 'Electronics');

This query retrieves products in the 'Electronics' category by using a subquery to find the corresponding CategoryID. More on Subqueries in a future article.

All the above concepts can be combined together to create even more complex queries based on our business requirements.


Conclusion:

Understanding the syntax of SQL queries is essential for effectively working with databases. Whether you're a beginner or an experienced developer, mastering the structure and components of SQL queries empowers you to interact with and extract valuable insights from relational databases. Regular practice and experimentation with SQL queries will enhance your proficiency and enable you to tackle a wide range of data-related challenges.

Published by:

Tahsin