SQL Terms Beginners Should Know - Part 3

This is the third part of my list of essential SQL terms for beginners. In the first, you learned what tables, relational databases, and queries are. In the second part of the tutorial, I told you about aliases, SQL JOINs, primary keys, and subqueries. Now, it's time for more advanced database terms. But don't be afraid! I won’t crush you with scientific definitions. I will explain everything simply so that you can easily understand.

If you want to review any basic SQL terms, see my previous articles, SQL Terms Beginners Should Know and SQL Terms Beginners Should Know - Part 2. Remember to bookmark this article so you can easily come back to it!

Let's get down to business! Here are some more SQL terms you need to know.

SQL Terms

SELECT

If you've already started learning SQL, your first SQL statement was probably SELECT. If not, think about whether you have a good source of SQL knowledge.

SELECT is the basis of the basics, especially when it comes to data analysis. We use SELECT to literally choose our desired data from a database. We can select anything, including specific columns or rows using filtering. But first, let’s look at the simplest use of SELECT. Suppose we have a Courses table that looks like this:

Now let's write the simplest SQL query using SELECT and, for example, a WHERE clause (which lets us choose specific data). Suppose we want to find the SQL Reporting course in MS SQL. The query would look like this:

SELECT Name
FROM Courses
WHERE Dialect=‘MS SQL’
AND Topic=‘SQL Reporting’

After the SELECT statement, we defined what we want to get – in our case, the name of the course. After FROM, we wrote where the data should come from: the Courses table. Next, we used a WHERE clause to indicate what kind of data we wanted. We specified two conditions, so we used AND to let the database know our desired result had to match both conditions. As you can see, this query is not much different from a common English sentence.

The result will be information on the course Creating Basic SQL Reports in SQL Server. If your company uses a database solution from Microsoft and you would like to make better strategic decisions based on your data, this is the perfect course for you. It is part of the SQL Reporting in MS SQL Server track.

My colleague Adrian wrote a great article on how to learn the SELECT statement in SQL. You will learn much more about SELECT there. And once you learn the basics, it is worth considering more advanced topics. If you want to learn SQL well, I recommend our SQL Basics course. This is the first step towards becoming a SQL professional.

INSERT INTO

INSERT INTO is one of the main SQL commands used in all popular databases. It is essential for working with data and tables, especially if you are a software engineer, database administrator, data engineer, or someone who works with the technical side of databases.

What does INSERT INTO do? Exactly what it says – it puts data into the table. This can be a single row or a group of rows.

In programming, we use the acronym CRUD — create, retrieve, update, and delete — to describe the four basic operations you can do on data. INSERT is one of them. (Later, I will describe UPDATE and DELETE, as well. All of them are used to modify databases.)

Let's take the same table, Courses, that we just used to demonstrate the SELECT command and add some information to it with the INSERT (or INSERT INTO) statement.

Suppose we want to add another course from LearnSQL.com to the table. This time, it's about the SQL Practice Set in Standard SQL – great for practicing your new skills or preparing for a job interview or exam. Let's add it to our table using the INSERT INTO command. Our SQL query would look like this:

INSERT INTO Courses (id, Name, Dialect, Topic) 
VALUES (5, ‘SQL Practice Set’, ‘Standard’, ‘Basic’);

This adds another row to our table:

You can add data one row at a time or you can insert multiple rows at once using one SQL statement. How? It would look something like this:

INSERT INTO Courses (id, Name, Dialect, Topic) 
VALUES ('6', ‘a’, ‘b’, ‘c’),
       ('7', ‘x’, ‘y’, ‘z’),
       …;

If you add values to all the columns in a table, you do not need to include column names in your query. However, be careful that you enter the values in the correct order. Otherwise, you will have chaos. If you were adding data to all columns, your query would look like this:

INSERT INTO Courses 
VALUES (5, ‘SQL Practice Set’, ‘Standard’, ‘Basic’);

Remember, specifying columns is not necessary, but that doesn’t mean you can’t do it if you want to be sure that the data goes into the right places.

I know that you are most likely just getting started with INSERT INTO; I’ve described its simplest usage scenario. However, this statement can be very interesting – INSERT INTO can appear, for example, in subqueries. Do you want to learn more about it? Then read the article How to Use Subqueries in INSERT, UPDATE, and DELETE Statements. You can also insert data from one table to another, as my friend Rebecca explains in SQL Insert, Update, and Delete.

If you really want to learn INSERT well, I recommend our How to INSERT, UPDATE, and DELETE Data in SQL course. It contains over 50 interactive exercises, thanks to which you will learn the secrets of database modification in SQL.

UPDATE

Now that you know INSERT INTO, it will be much easier for you to understand UPDATE – it's just a way of changing the data in one or more table rows. But be careful when you use this command; if you’re not paying attention, you can really cause yourself a lot of trouble. Wrongly used, UPDATE can destroy the entire table. There’s no need to worry, though; just learn to use this SQL command well before you start updating your data.

Here is Courses again:

Suppose you decided that the Topic of the SQL Practice Set course should not be 'Basics' but 'SQL Practice'. Although it is aimed at beginners, this course is also useful for more advanced users who want to refresh their knowledge. To change this, we will use the UPDATE command. The SQL query will look like this:

UPDATE Courses 
SET Topic='SQL Practice’
WHERE ID=5;

Our table will look like this:

It is very important to apply the WHERE clause in this case. If it were omitted, all the values in the topic column would be changed, and we would have this (which is not cool):

Like INSERT INTO, UPDATE allows us to modify more than one row at once. If we go back to our original table Courses, we could, for example, rename the ‘Standard’ dialect to ‘SQL Standard’ for all the courses that use this dialect. Here's how to do it:

UPDATE Courses
SET Dialect='SQL Standard'
WHERE Dialect='Standard';

What do you think we'll get? Of course, an even nicer table:

DELETE

The third important SQL command is DELETE, which removes existing data from the table. Here I urge you to be even more careful than with UPDATE. If you don't do regular backups of your data, deleting something from the database may be irreversible. Something like this happened to me once, and I learned a life-long lesson from it!

Let's take our table Courses and remove the PostgreSQL course from it. All we need to do is use the following query:

DELETE FROM Courses 
WHERE Dialect=’PostgreSQL';

And voila! There is no PostgreSQL JOINs course in the table, which is a pity because it is really good.

As with the previous commands, DELETE also works on more than one row. If there were more courses in the table with a dialect defined as ‘PostgreSQL’, they would all be deleted.

What's more, you can delete all the rows of the table with one SQL line – even if the table has hundreds of thousands of them. In our case, it would be enough to write:

DELETE FROM Courses;

This does not mean we’re deleting the table itself: its structure, attributes, and indexes will remain in place. However, it will not contain any records. There is another DROP command that removes the entire table; maybe I'll tell you about that in another article.

ORDER BY

If you are extracting data from a table, it's a good idea to have a nice orderly result. Sometimes you want to sort the results of your query. Here you will need ORDER BY, which allows you to arrange the data in ascending (A-Z, 1-10) or descending (Z-A, 10-1) order.

Since we like to have everything neatly organized, let's take a look at our Courses table. This time, we want to see the course names in alphabetical order. If any courses have the same name but are in different SQL dialects, we’ll sort the dialect names alphabetically as well:

SELECT *
FROM Courses 
ORDER BY Name;

The result will be lovely.

In the first part of my tutorial on SQL terms for beginners, I described GROUP BY. At first glance, it is very similar to ORDER BY. However, there are big differences, which we explain in the article Difference Between GROUP BY and ORDER BY in Simple Words. And what happens if any NULL values appear in the table? Find out by reading How ORDER BY and NULL Work Together in SQL.

HAVING

HAVING is used for data filtering. HAVING is one of SELECT's friends and cannot exist without it; it also works with WHERE and GROUP BY. This clause specifies the condition or conditions for a group or an aggregation.

Here is a musical example of what I'm talking about. Let's take the music_artists table (The data comes from Wikipedia). Here are eight of the top-grossing artists.

To calculate the sum of artists’ album sales by their country, you’d write this query:

SELECT Country, SUM(Sale_mln)
FROM music_artists
GROUP BY Country;

The result is a list of the artists’ countries and the total number of albums sold by artists from each country.

Suppose we want to filter the result even more and want our query to only return countries whose top artists have sold more than a billion albums:

SELECT Country, SUM(Sale_mln)
FROM music_artists
GROUP BY Country
HAVING SUM(Sale_mln)>=1000;

Sorry, Canada, for getting left out of the results. Drake has to sell a few more albums. In the end, we got a US and UK comparison.

This example is just the tip of the iceberg. If you want to learn more about HAVING, I recommend you read The SQL HAVING Clause Explained and our SQL HAVING Tutorial. Both will give you a solid foundation for the use of HAVING in SQL. You can also learn and practice it in our SQL Basics course.

SQL Order of Operations

Imagine that an SQL query is a recipe for a delicious dish. Each of the elements in this query is a successive step. For example, GROUP BY is the cooking stage and ORDER BY is the garnish on the plated food.

An SQL query most often consists of several commands that are executed by the database in a specific order. For example, it is impossible to sort the result before using HAVING to filter it; it would be unnecessary work to sort all the records instead of just the ones we’ll have in the final result.

Even if you’ll be using SQL every day, you don't need to memorize all the rules. All you need to do is remember the general order in which the individual commands and clauses are implemented. However, if you plan to become a software developer, you should know what will happen in the database after entering a specific query.

Do you want to know more about this? My colleague Ignacio described everything perfectly in his article SQL Order of Operations. Remember that, depending on which clauses appear in your query, the sequence of actions may differ. There is a general rule, however, which is illustrated in this picture:

Database Tools

Now, let’s take a look at the most popular relational database management systems or RDBMSs.

MySQL

MySQL is currently the most popular DBMS in the world. According to a study by Stack Overflow, it had more than 50 percent of the market last year – although PostgreSQL is close behind.

MySQL was founded 25 years ago in Sweden. In 2008, it was purchased by Sun Microsystems, and since 2010 it has been developed under the Oracle umbrella. Interestingly, the IT giant also has its own database solution, which I’ll describe in a moment.

This DBMS is free, works for most applications, and runs on most popular platforms, including Linux, Windows, and macOS. All of this – along with an active community and good support – makes MySQL a great choice for many uses. Personally, I'm a fan of PostgreSQL, but MySQL certainly isn't missing anything you’d need.

If you want to learn more about popular databases, I recommend reading our article on The Most Popular Databases in 2020.

Oracle

To say that Oracle is an IT giant is like saying Amazon runs an online store. Oracle’s solutions are used in one form or another by 90 percent of Fortune 100 companies. It has been one of the main players in the software market from the very beginning of its existence (i.e. the late 1970s). You might know Oracle as the owner of Java or OpenOffice.

Fun fact: Oracle’s name came from the code name of one of the CIA-sponsored projects that its co-founder, Larry Ellison, worked on.

One of this company’s main products is Oracle Database, a very popular DBMS. It’s most often found in businesses; much less often, you’ll see it in-home use. Currently, Oracle DB licenses are available in several versions:

• Oracle Enterprise Edition - The DBMS’s flagship version.
• Oracle Standard Edition - A slightly reduced version, without the possibility of expansion.
• Oracle Standard Edition One - A bit more reduced than above; you can’t run a cluster configuration, and this version can only be used on computers with one or two processors.
• Oracle Express Edition - A free version that can only be used on hardware with one processor core and 1GB RAM. It can handle up to 4 GB of data.
• Oracle Personal - For home users.
• Oracle Lite - For mobile applications.

Apart from some of the versions described above, Oracle Database is paid software. This has its pros and cons. The obvious downside is the cash – you have to pay the corporation to use their solution. The pros include reliable technical support and guaranteed updates and security. Oracle spends a lot of money on developing its products. Nevertheless, each year it loses users to free and open-source DBMSs such as PostgreSQL or MySQL.

As I wrote earlier, controversy arose after Oracle acquired MySQL. For now, both projects (MySQL and Oracle DB) are being developed separately – but no one knows what the long-term plans are. Perhaps, over time, MySQL will cease to be free? Or will both solutions be combined into one? It's hard to predict, but for me, it's one of the arguments in favor of looking for MySQL alternatives.

SQLite

SQLite is something worth watching. It’s gaining supporters and popularity. The SQLite project has been in development since 2000. In 2018, 19.7 percent of professional developers used this database; in 2020, it was over 30 percent. That’s not a coincidence.

SQLite is an open-source DBMS that has excellent features and is free to use. While PostgreSQL or MS SQL are more “classic" database systems, SQLite is the most frequently chosen by mobile application developers. Why? Mainly due to its capabilities and characteristics.

The data is stored in a file of up to 140 TB. The library implements the SQL engine, allowing the app to use the database without running a separate process. This means that everything works quickly and efficiently, which is crucial for web apps. Every millisecond counts.

SQLite is based on the SQL-92 standard. This means that you will be able to work with it after completing our SQL A to Z track. Of course, SQLite has its drawbacks, so it's not a universal solution. But it has already taken its position on the market and is not going anywhere.

Moving Beyond SQL Beginner Terms

Here's another installment of the list of basic SQL concepts you should know. I hope my translation helped you understand them. I know sometimes these aren't precise or scientific definitions, but as a former beginner, I also know that sometimes precise or scientific isn’t of much help at first.

If you have problems with other SQL terms, please let me know in the comments.