Mastering SQL Fundamentals
Data Retrieval Basics
Asking for Data
Databases store information, but to get that information out, you need to ask for it. In SQL, the way you ask is with a query. The most fundamental query for retrieving data is built with two keywords: SELECT and FROM.
Think of it like ordering from a menu. SELECT is what you want, and FROM is which section of the menu it's on. Every data retrieval query you write will have this basic structure.
The SELECT statement is the star of SQL—it retrieves data from tables.
Let's imagine we have a table named Employees that contains information about staff members. If you wanted to see absolutely everything in that table, you would use an asterisk (*), which is a wildcard character that means "all columns."
SELECT *
FROM Employees;
This query says, "Select all columns from the Employees table." While simple, using the wildcard is often discouraged in production code. It can be inefficient if the table has many columns you don't need, and it can cause errors if the table structure changes later. It’s better to be specific.
To select specific columns, you simply list them after SELECT, separated by commas.
SELECT FirstName, LastName, JobTitle
FROM Employees;
This is much more intentional. It asks only for the first name, last name, and job title of every person in the Employees table. The result is a new, temporary table containing just the data you requested.
Refining Your Request
Sometimes, the raw column names in a database aren't very readable. They might be abbreviated or use a naming convention that isn't clear to an outside observer. SQL lets you create temporary, more descriptive names for your columns in the output using an alias. The AS keyword assigns an alias to a column for the duration of that query.
SELECT
FirstName AS "First Name",
LastName AS "Last Name",
StartDate AS "Start Date"
FROM
Employees;
In the result, the columns will be labeled "First Name", "Last Name", and "Start Date". Notice the use of double quotes for aliases that contain spaces. This makes reports and data exports much easier to understand.
What if you just want to see the unique values in a column? For instance, maybe you want a list of every unique job title in the company. If you just select the JobTitle column, you'll get a lot of duplicates: "Software Engineer", "Software Engineer", "Project Manager", etc. The DISTINCT keyword solves this by filtering out any repeated values.
SELECT DISTINCT JobTitle
FROM Employees;
This query returns a clean list of job titles, with each title appearing only once, no matter how many employees hold that position.
Writing Clean SQL
You could write an entire SQL query on a single line, and it would still work. But that doesn't mean you should. As queries become more complex, formatting becomes crucial for readability and maintenance.
There are a few conventions that make SQL easier to read:
- Capitalize Keywords: Write keywords like
SELECT,FROM, andASin uppercase. - New Lines: Start each major clause on a new line.
- Indentation: Indent the list of columns to set it apart from the
SELECTkeyword.
This formatting helps distinguish the structure of the query (the keywords) from the specific data you're working with (the identifiers, like table and column names).
| Term | Example | Description |
|---|---|---|
| Keyword | SELECT, FROM, AS | A reserved word with a special meaning in SQL. |
| Identifier | Employees, FirstName | A name you give to a database object like a table or column. |
| Literal | 'Software Engineer', 2023 | A fixed value, such as a string or a number. |
Adopting these formatting habits early will make your code much easier for you and others to debug and understand down the line.
What are the two essential keywords required to retrieve data from a database table?
Which query correctly retrieves all columns from a table named Products?
Now you have the basic building blocks for retrieving data from any table. You can grab all the data, select specific pieces, and clean up the output with aliases and unique values.