SQL Terms Beginners Should Know

Have you started learning SQL? Do you feel a little lost in the maze of new words and ideas? Here’s a list of 10 absolutely essential SQL terms for beginners.

When you start learning SQL, there’s a lot to process. And some of it – even basic SQL terms – gets lost in the shuffle. If this sounds like you, bookmark this article; it will be a handy reference.

I’m going to explain these basic SQL terms in the simplest words possible. We’re not looking for scientific-level precision here; the most important thing is that you understand SQL fundamentals.

Ready? Let’s go!

Section One: Database Basics

Database

A database is a structured set of data stored on a computer. You can process this data in various ways – e.g. analyze it, update it, copy it, delete it, etc.. Nowadays, databases are everywhere. They are used by banks, taxi corporations, websites, hospitals, and even the apps on your smartphone. We live in a world that runs on the data stored in databases.

If you’re interested in eventually creating database structures, check out our Creating Database Structure track. It contains five interactive courses where you will learn about data types, creating table structures in SQL, and using constraints, indexes, and views. The 320+ interactive exercises will give you a whole lot of knowledge – and practice.

Relational database

This is an important term that often has extremely complex definitions. I’ll stick with what Edgar Frank Codd – a genius mathematician and the forefather of databases and SQL – laid down in 1970: a relational database is a predefined structure of one or more tables. The data in the tables can be related based on their values. (For example, two columns in different tables might share the same ID data, which links these two tables together.)

Relational databases are the most popular kind on the market, but there are other kinds available. Some databases use a different kind of data structure; they can be based on:

• Columns - Cassandra, Druid, HBase.
• Key-value pairs - Oracle NoSQL Database, OrientDB, Redis.
• Documents - Apache CouchDB, IBM Domino, MongoDB.
• Graphs - AllegroGraph, InfiniteGraph, Apache Giraph.

If you're thinking of working with databases, focus on relational databases for now. If you don't know right now that you will need a different type of database, you will almost certainly be using a relational database. The SQL Basics course is a good place to start building this knowledge.

Database Management System (DBMS)

As the name suggests, a DBMS is a computer program that allows you to work with a database. The database itself is simply data stored on a hard drive. You have to access it somehow, and this is where a DBMS comes in handy.

At this point, all you need to know is that a DBMS is the software in which you write SQL queries, that it connects to the database and returns the data you ask for. There are many DBMSs on the market, both paid and open-source (which is more popular). The choice between them is mainly a matter of personal preference and purpose. If you choose to learn SQL, you'll be using a DBMS. The most popular DBMSs are:

• PostgreSQL. If you want to learn this popular SQL dialect and work on PostgreSQL databases, I recommend SQL Basics in PostgreSQL.
• Oracle
• MS SQL Server. If you’ll be working on an SQL Server database, check out our SQL Basics in MS SQL Server
• MySQL

My colleague Adrian wrote a great article on DBMS, in which he explains exactly what DBMSs are using examples. If you need more details, it’s a good resource. Do you want to know which DBMS you should choose for business or private use? Take a cue from the professionals: The Most Popular Databases in 2020 contains the results of the latest Stack Overflow survey.

Table

Most of us intuitively understand what a database table is: data in a logical structure of columns and rows. Most likely you can imagine a database table in your head. If not, here’s an example:

With SQL, you can write queries that pick out specific table ‘fragments’, i.e. the value(s) in certain rows or columns. You can even pick out a single value. Furthermore, SQL lets you analyze, perform calculations on, or even change the data in a table. And you won't always be working with just one table. Sometimes you will write a query referring to multiple tables. For example, you might have a list of your customers in one table and a list of orders in another table. With one SQL query, you can find out which customers ordered the most goods and what they ordered. This requires a command known as the SQL JOIN, which is covered in our SQL Basics path.

Column

A column is part of a table. In a relational database, a column is a collection of data values of a specific type; there is one column value for each row in the database.

For example, look at your contact list. You probably have your friends’ first names and surnames, phone numbers, e-mail addresses, etc. If you put them all in a database table, they would look like the example below. Note that all the information in the email column (which we’ve colored in) is of the same type: email addresses. That’s what columns do: store one kind of information.

Row/Record

Rows (also called records) contain cells (also called fields) that store individual column values. Unlike columns, rows can contain multiple types of data. What makes rows different is that all the data in the row describes one specific thing. And, also unlike columns, you can have any number of rows in a table; you don’t have to know the number of rows in advance.

In the example of a contact list, each row represents all the contact info you have for a single person. In this example, one row = one person. In other cases, one row might represent one item, one sales transaction, one piece of sports equipment, etc.

Section Two: Basic SQL Terms

Structured Query Language (SQL)

SQL or Structured Query Language is the language you use to communicate with the database. Unlike some programming languages, SQL is pretty simple and is based on plain English. SQL commands are more like sentences than computer codes. In other words, SQL is the language you'll use to find, change, add, or delete data in a database.

I find that learning SQL is more like learning Spanish or French than a computer programming language. In fact, I’d say it’s even easier because you already know all the words. You just need to find out how to put the right words together correctly in an SQL query.

SQL is over 40 years old. And it’s not going anywhere. It is an official industry standard, endorsed by the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI, which is a member organization of the ISO).

All modern relational database solutions use SQL as the basic tool. Even though there are different SQL dialects ( e.g. SQL Server, MySQL, or PostgreSQL), the differences between standard SQL and a dialect are usually minor. You can easily check each dialect’s documentation to find out what needs to be done differently.

Query

A query is a command sent to the database. In our case, it will be what you write in SQL to get the result you want. SQL queries can be used to extract results from the database or modify the database itself.

Imagine the database is your friend Joe. He knows where to get good pizza. You are very hungry and you’d like a pizza. What do you do? You ask Joe, saying something like: "Hey Joe, where is a good pizza place?" Assuming you were asking in SQL, it would look something like this:

SELECT pizza_place
FROM Joe
WHERE restaurant_review = "good";

It’s simple, isn’t it? Even without knowing how to write an SQL query, you can figure out that you’re getting information about a pizza place from Joe. And furthermore, this pizza place has to have a good review. Joe the database would return the result (i.e. give you the information you asked for): Papa Mario's tiny restaurant. But that’s enough food references for now – my stomach has really started to rumble!

GROUP BY

GROUP BY is an important SQL clause that allows you to group rows that have the same values in a given column.

Suppose you want to see how many customers you have in a given city. Adding GROUP BY city to your query will list all the rows (customers) with the same value in the city column together in your results. This is very helpful for summarizing data and making reports. To learn more, check out the Creating Basic SQL Reports course – after, of course, you’ve finished your basic SQL training.

SQL also offers several GROUP BY extensions, so-called because they extend the grouping capabilities of GROUP BY. These are ROLLUP, CUBE, and GROUPING SETS, and they let you do cool things like add subtotals to an analysis. GROUP BY extensions are more advanced, so wait until you’re comfortable with GROUP BY before you learn them. Once you’re ready, see our GROUP BY Extensions in SQL course. If you work with data or reports, what you learn in this course will really help you.

Aggregate Functions

Aggregate functions allow you to compute numerical calculations on your data. The query then returns summarized information about a given column or a list of results.

There are five basic SQL aggregate functions:

• MIN() returns the smallest value in a column.
• MAX() returns the highest value in a column
• SUM() returns the sum of all numeric values in a column.
• AVG() returns the average value for all numeric values in a column.
• COUNT() returns the number of all values in a column (e.g. if there are 45 ID values in the id column, COUNT(id) will return 45).

If you want to know how to use these functions, read our SQL Aggregate Functions: A Comprehensive Guide for Beginners. You can also watch our YouTube tutorial, where my colleague Adrian will explain everything in a really nice way. (Please like and subscribe!) But remember – SQL aggregate functions are best learned when you’re already proficient in SQL basics.

Save This SQL Basics Cheat Sheet

That’s it – my list of the basic concepts that I think anyone starting to learn SQL should know. If you liked my article or if you want to see other SQL terms explained, please post a comment below.

Remember, if you don't understand something correctly at the very start of your study, learning advanced concepts will be a lot harder. So don't be afraid to ask for help. We all need to ask questions – it’s how we improve!