Lesson 7 – SQL Syntax Fundamentals

Welcome back, data explorer!

You’ve successfully built your workshop by setting up your SQL environment. You have a direct line to the City of Data and are ready to start sending your own, custom requests. But before you hit the road, you need to learn the city’s traffic laws and the basic grammar of the local language. This is what SQL syntax fundamentals are all about.

Think of it like this: knowing words like “library,” “find,” and “book” is one thing. Knowing how to structure them into a grammatically correct sentence—”Please find the book about the library for me.”—is another. Without proper syntax, your requests will be met with confusion and errors.

This lesson is your driver’s education class. We will cover the essential, non-negotiable rules of the road that govern every single SQL command you will ever write. Mastering these SQL syntax fundamentals will make your code work correctly, make it easy for others (and your future self) to read, and mark you as a true professional.

Let’s learn the rules.

The Anatomy of a SQL Statement

Before we dive into the rules, we first need to understand the basic unit of any SQL communication: the statement. A SQL statement is a complete command sent to the database to perform a task.

Let’s dissect a simple statement to understand its parts:

SELECT first_name, email FROM Customers;

SELECT first_name, email FROM Customers;

This single line of code is composed of several distinct elements:

• Keywords: These are the reserved, core words of the SQL language that tell the database what to do. In our example, SELECT and FROM are keywords. They have a special, fixed meaning.
• Identifiers: These are the names you (or the database architect) give to things like tables and columns. In our example, first_name, email, and Customers are identifiers. They are the specific “nouns” you are referencing.
• Literals: These are explicit data values. In our “Hello World” example (SELECT 'Hello World';), the text 'Hello World' was a string literal.
• Operators: These are symbols used for comparison or arithmetic, like =, >, +, or *. We’ll cover these in a future lesson.
• The Semicolon (;): This is the punctuation that marks the end of a complete statement.

Every query you write will be a combination of these components. Now, let’s learn the rules for putting them together correctly.

Image

Check Your Understanding

1. Question: In the statement UPDATE Products SET price = 50;, which words are keywords and which are identifiers?
   • Answer: UPDATE and SET are keywords. Products and price are identifiers. 50 is a numeric literal.
2. Question: What is the primary role of a keyword in a SQL statement?
   • Answer: To instruct the database on the specific action to perform (e.g., SELECT data, UPDATE data).

The Golden Rules of SQL Syntax

These are the core SQL syntax fundamentals you must know. Internalizing them now will save you countless hours of debugging later.

Rule 1: The Semicolon – The Stop Sign of SQL

The semicolon (;) is the official statement terminator in the SQL standard.

A semicolon marks the end of one SQL statement, allowing you to write multiple, separate commands in the same script and have the database execute them one after another.

Example:

SELECT * FROM Customers;
SELECT * FROM Products;

SELECT * FROM Customers;
SELECT * FROM Products;

Without the semicolon, the database might get confused and try to read this as one long, invalid command.

“But my query worked without a semicolon!”

This is a common point of confusion. Many modern SQL clients (like DBeaver) are smart. If you only write and execute one statement at a time, the client often figures out where it ends and doesn’t require the semicolon.

So, should you use it? YES. ALWAYS.

• It’s a professional habit: It shows you are writing code that is compliant with the official standard.
• It prevents errors: It is absolutely mandatory when you want to run a script containing multiple statements.
• It provides clarity: It explicitly tells anyone reading your code where one logical command ends and the next begins.

Treat the semicolon as the non-negotiable period at the end of every SQL sentence.

Check Your Understanding

1. Question: Why is the semicolon essential when running a script with multiple SQL statements?
   • Answer: It acts as a separator, telling the SQL engine where each distinct command ends so it can execute them sequentially instead of trying to interpret them as one single, jumbled command.
2. Question: If you are only running a single SELECT statement in your GUI client, is the semicolon technically required for the code to run?
   • Answer: In most modern clients, no, it is not technically required. However, it is still a critical best practice to include it every time.

Rule 2: Case Sensitivity – Public Signs vs. Private Names

This is one of the most important SQL syntax fundamentals for beginners to master. The rule is simple but has two parts.

Part A: Keywords are Case-Insensitive (The Public Signs)

SQL keywords like SELECT, FROM, WHERE, and JOIN are case-insensitive. This means that the following three queries are absolutely identical to the database:

SELECT first_name FROM Customers;
select first_name from Customers;
SeLeCt FiRsT_nAmE fRoM cUsToMeRs;

SELECT first_name FROM Customers;
select first_name from Customers;
SeLeCt FiRsT_nAmE fRoM cUsToMeRs;

While all three work, the third one is a nightmare to read. This has led to a very strong, unwritten rule followed by nearly all developers:

Convention: Write all SQL keywords in UPPERCASE.

This makes your code dramatically easier to read by creating a clear visual distinction between the language’s commands (SELECT, FROM) and your specific table/column names (Customers, first_name).

Part B: Identifiers and Data are Often Case-Sensitive (The Private Names)

This is where you need to be careful. While the keywords are like public street signs that can be read in any case, the names of your tables, columns, and especially the data inside them are like private names on a mailbox. You have to get them exactly right.

Whether table and column names are case-sensitive depends on the specific database system and its configuration. However, it is a crucial best practice to always assume they are case-sensitive. Always write your table and column names exactly as they were defined in the schema.

More importantly, the actual data stored within the columns is almost always case-sensitive. If you are searching for a customer in the city of ‘Mumbai’, the following query will likely fail to find them:

-- This query will probably NOT find the customer
SELECT * FROM Customers WHERE city = 'mumbai';

-- This query will probably NOT find the customer
SELECT * FROM Customers WHERE city = 'mumbai';

This is because the value 'mumbai' is not the same as the value 'Mumbai'. For more detail on how specific systems handle this, you can look at the official documentation, such as the PostgreSQL documentation on identifiers.

Check Your Understanding

1. Question: Which of these queries is syntactically correct and follows best practices? SELECT * from customers; or select * FROM CUSTOMERS;?
   • Answer: Neither. The best practice is SELECT * FROM Customers; (Keywords in uppercase, identifiers with their exact defined case).
2. Question: You run a query to find a product named 'Wireless Mouse', but your query uses 'wireless mouse'. Will it find the product?
   • Answer: Most likely not. The data comparison is case-sensitive, so the database will look for an exact match.
3. Question: Why is it useful to write keywords in uppercase?
   • Answer: It’s a readability convention. It creates a strong visual contrast that helps the human eye quickly distinguish between the SQL commands and the specific tables/columns being worked on.

Rule 3: Whitespace – Formatting for Clarity

Imagine reading a book with no paragraphs or line breaks—just one giant wall of text. It would be exhausting.

The SQL engine is very flexible when it comes to whitespace (spaces, tabs, and line breaks). It mostly ignores them. This means the following two queries are identical to the database:

Poorly Formatted:

SELECT customer_id, first_name, email FROM Customers WHERE city = 'Mumbai' ORDER BY first_name;

SELECT customer_id, first_name, email FROM Customers WHERE city = 'Mumbai' ORDER BY first_name;

Well-Formatted:

SELECT
    customer_id,
    first_name,
    email
FROM
    Customers
WHERE
    city = 'Mumbai'
ORDER BY
    first_name;

SELECT
    customer_id,
    first_name,
    email
FROM
    Customers
WHERE
    city = 'Mumbai'
ORDER BY
    first_name;

While the database sees them as the same, the second one is infinitely easier for a human to read and debug. Using whitespace to format your code is not just a suggestion; it’s a hallmark of a professional developer. A great resource for formatting conventions is the SQL Style Guide by Simon Holywell.

General Formatting Rules:

• Put each major keyword (SELECT, FROM, WHERE, GROUP BY, ORDER BY) on a new line.
• Indent the columns listed in your SELECT statement.
• Align all your keywords to the left.

Conclusion: Writing Code for Humans and Machines

You have now learned the essential grammar of SQL. These SQL syntax fundamentals are the bedrock of every query you will write.

You know that every command is a statement that must end with a semicolon. You understand the critical difference between case-insensitive keywords and case-sensitive identifiers and data. And you see the value in using whitespace to write clean, readable code.

Following these rules does more than just make your code work. It makes your code professional. You are learning not just how to talk to the database, but how to do so in a way that is clear, efficient, and understandable to other data professionals.

Now that you know the rules of grammar, let’s learn how to leave helpful notes within our code.

➡️ Next Lesson: SQL Comments: Documenting Your Code

Key Takeaways

• A SQL Statement is a complete command composed of elements like keywords, identifiers, and literals.
• Always end every SQL statement with a semicolon (;). It is the official standard.
• SQL Keywords (SELECT, FROM, etc.) are case-insensitive, but the convention is to write them in UPPERCASE for readability.
• Identifiers (table/column names) and especially Data are often case-sensitive. Always match the case exactly.
• SQL ignores most whitespace, which you should use to format your code with line breaks and indentation to make it easy to read.

Frequently Asked Questions (FAQ)