Basic SQL Commands: What Are the Most Useful Commands for SQL Beginners?

SQL is a query language used to access and manipulate information held within a database. It is the most popular language for database management, although there are other types of databases (such as NoSQL databases). Many developers have an understanding of SQL, whether they primarily program in Java, C++, Python, or another language altogether. Many applications will need to use SQL to communicate with their data layer, regardless of the primary language they are programmed in.

Most applications today run on some form of SQL database, of which there are many. Oracle DB, MySQL, and Microsoft SQL are some of the most popular databases which use the SQL language.

For someone who works with spreadsheets, SQL is familiar. It stores values in the form of rows and columns. SQL queries can also help with storing and manipulating data. Every table in the database has a name. 

Let’s take a look at an example.

In the below database, we have a list of people. Notice that every person has a name, as well as a country and city of origin. The PersonID is a unique identifier and a primary key. It will only ever uniquely identify one person. That’s important because there could be multiple people named “Mike.”

Country	City	PersonName	PersonID
Germany	Berlin	Brat	1
UK	London	Mike	2

SQL commands are directions or instructions for making contact with the database and performing different tasks that work with records. Depending on functionality, you can divide the SQL commands into four main groups:

1. Data Definition Language
2. Data Manipulation Language 
3. Transaction Control Language
4. Data Control Language

Let’s continue to learn how to use each type of SQL command group to manage, manipulate, and secure data. 

1. Data Definition Language (DDL):

For defining the database, schema data definition language statements are used. Data Definition Language (DDL) uses different commands to create, delete, and modify the structure of the database. DDL generally includes the commands CREATE, RENAME, TRUNCATE, ALTER, and DROP.

For a database administrator, they probably aren’t going to be creating or dropping often. An existing database usually doesn’t need tables added or removed. Instead, the data inside of them are modified. The database structure is extremely important, and a lot of work and care goes into developing it.

But for database architects, creating, renaming, truncating, altering, and dropping databases are all essential. Database architects are the ones who design databases before they are used, with an eye toward both usability and performance.

Because these commands are significant (dropping, in particular, can remove a lot of data), administrators may want to make sure that everything has been appropriately backed up before they make major changes.

CREATE command:

You can use the CREATE command to either create tables or databases. 

First, CREATE DATABASE is best for creating new database architecture. Databases are made of tables, so the CREATE TABLE command adds a new table into the existing database. Without creating a database, you won’t be able to create a table. 

Therefore, we need to create a database first and then create a table into that database.

General syntax for CREATE DATABASE:

CREATE DATABASE databasename;

General syntax for CREATE TABLE:

CREATE TABLE table_name
(
Columnname1 datatype(size),
Columnname2 datatype(size),
Columnname3 datatype(size),
…..
);

Example:

CREATE DATABASE my_first;

We have created our database, so now we can create a table within it.

CREATE TABLE Person
(
PersonID int,PersonName varchar(250), City varchar(250), Country varchar(250)
);

Note that it’s important each column has a relevant data type. There are many data types in SQL, and it can take a while to learn them all. Some of the most common include:

• Char: A string of a set length.
• Varchar: A string of an unset length (a variable length character).
• Int: A regular integer. It can also be smallint or bigint, depending on size.
• Float: A floating point number, often used for currency.
• Floor or ceiling: A number that rounds either up or down.
• Date or datetime: A timestamp for a date or the date and time.
• Blob: A large “blob” of text or other data, such as for storing images.

Let’s take a look at our table. It’s empty, but it has a structure. The structure is the most important aspect of starting databases and tables.

Country	City	PersonName	PersonID

As a general best practice, it’s always important to create index keys. A primary and unique identifier (often with auto increment) will ensure that you can reference every row in your database even if the actual data held there is identical. This way, when someone selects data, they don’t need to be worried about ambiguity.

While it’s a little more advanced, you can also try CREATE VIEW (which creates a view of rows and columns, much like a database, but built with data already in the database).

ALTER TABLE command:

This command gives you the option to add and delete columns from an existing table.

General syntax:

ALTER TABLE table_name  ADD column_name datatype

This syntax adds columns.

ALTER TABLE table_name DROP COLUMN column_name

This syntax will delete the column.

DROP TABLE command:

The DROP TABLE command can delete the entire table from the database. This will also completely remove the table contents, including its structure and schema.

General syntax:

DROP TABLE Person;

This will completely delete the table “Person” from the database. Of course, this is very dangerous because it also deletes all the information within the table. Most people rarely drop tables, rather they just delete the information from the table.

RENAME command:

As the name suggests, RENAME command is what you use to rename a table.

General syntax:

RENAME TABLE {table_name} TO {new table_name};

Example:

RENAME TABLE Person TO Workers;

You can also “DROP DATABASE” if you want to delete the database itself. Renaming the table can be complicated, though, because it means that any references to the table inside of an application will also need to be modified.

TRUNCATE command:

TRUNCATE command deletes the complete data from the table. Unlike DROP command, TRUNCATE command saves the structure and schema of the table.

General syntax:

TRUNCATE TABLE table_name;

This is a valuable option while in development, as developers may have test data that they need to regularly delete from the table. You can also use it to “refresh” the database from its starting point so it can seed in the current data.

MYSQLDUMP

At this point, let’s also talk about the MYSQLDUMP command. Through the CMD utility, you can use the syntax:

MYSQLDUMP database_name > file_name;

This will push all of the database’s information into a file in SQL format. If you open that file, you will see a lot of “CREATE” and “INSERT” queries; essentially, it will rebuild the database from scratch if something ever happens to that database.

You can also do the reverse:

MYSQLDUMP database_name < file_name;

This is usually used as a restore process, but you should note that it isn’t a standard restoration; it runs all the SQL statements in the file. If the database already contains data, it will not delete that data. So, you will want to empty the database before bringing back a dump file.

Using MYSQLDUMP isn’t always the preferred way of backing up a database, but it’s something fast that you can do on-the-fly before making any major changes. As long as you keep the relevant dump files, you’ll be able to restore your database to that save point. This is especially important when you’re learning and experimenting.

Apart from using it to restore existing databases, you can use MYSQLDUMP to create tables and databases. You can add all your query language into a single dump file and then run it. It will give you errors as usual if there are any errors in your syntax.

2. Data Manipulation Language (DML):

Of course, once a database is built, it needs to be interacted with. To retrieve, delete, store, and modify the data from the table, Data Manipulation Language commands are used. The most common commands are SELECT, INSERT, UPDATE, and DELETE.

While these are just four simple commands, they do the bulk of operations within a database. In fact, someone can start using a database very quickly if they understand the basics of these four commands.

But while they do seem simple, they can become far more complex. SQL is a programming language, and there are ways to do just about anything logic-based that a programmer wants to do.

SELECT command:

The SELECT command is used to retrieve the data or records from the database table. Most databases are going to use SELECT queries more than anything else. It can select a single record, a number of records, or all records. You can even use it to select all records or specifically matching records.

General syntax:

SELECT * FROM table_name;

Example:

Let us take our “Person” table for this example.

SELECT * FROM Person;

This will retrieve the complete data from the “Person” table. But SELECT queries can also be quite complex. You can use a SQL IN modifier (SELECT IN) to select values from inside of a column or SQL ORDER BY to return the table’s rows in a specific order. The SQL WHERE statement is commonly used to select specific rows:

SELECT * FROM Person WHERE name = “Mike”;

And you can do a SQL SELECT and SELECT DISTINCT if you want to select only distinct data. A SQL UNION can join data from multiple tables, as can a SQL INNER JOIN. But you can also just select from multiple tables:

SELECT orders.item, customers.name FROM orders, customers WHERE customers.uniqueID = orders.customerID;

The above query would produce a list of customer names and the items that they ordered by linking the orders and customers table—nothing additional necessary. Of course, this might not be a very useful query because it will produce all of them; to be useful, you might want to track unfilled orders or orders over a certain period of time.

There are also some SQL statements that perform aggregate functions. You can use SQL COUNT to count the number of rows returned during a SELECT statement, SQL AVERAGE to average out rows returned, and SQL SUM to sum them up. 

An example of this would be:

SELECT count(uniqueID) from USERS;

This would give you a count of all the users within your table. You could also use count(*) without specifying the uniqueID, but in this case, what we’re really counting is the uniqueID.

A SQL SELF JOIN can also join a table in the database to itself during selection, but this is a little more advanced. Just know that if you need to, you can compare a data table to itself. This doesn’t usually come up, but it can.

Finally, you can SQL JOIN multiple tables with a LEFT JOIN, FULL JOIN, or OUTER JOIN. The JOIN returns will still come in the form of a SELECT statement. A LEFT JOIN will join records from the right table to the left table, while a FULL JOIN or FULL OUTER JOIN will join multiple tables and returning rows even if they do not match. Comparatively, an INNER JOIN will return only rows that match.

INSERT command:

What’s a database without data?

You’re going to need to create data before you can manipulate it. The SQL INSERT INTO command is what you want to use when inserting records into the database table. INSERT INTO command basically adds rows and columns into the table.

The most critical element of INSERT is that you need to be inserting data into the correct columns. For instance, if you have a column that is an “int,” you can’t insert a “string” into it. SQL does have typing; you will get an error if you try to do that.

General syntax:

INSERT INTO table_name(column1,column2,column3,…..) VALUES (value1,value2,value3,….);

Example:

Let us take our table “Person” for this.

INSERT INTO name(PersonID, PersonName, City, Country) VALUES (‘3’,‘Nick’,‘Naples’,‘Italy’);

Country	City	PersonName	PersonID
Germany	Berlin	Brat	1
UK	London	Mike	2
Italy	Naples	Nick	3

The INSERT INTO command is, understandably, one of the most important commands because it’s the primary method of inserting information into the database. It’s important when inserting info into a database that the new data is always the correct type.

It’s also worth it to note that most developers will not insert the PersonID. Instead, they will set the PersonID to “auto increment,” and they won’t include the PersonID at all.

Auto increment means that the SQL server itself will increase the PersonID by one each time. So, if you inserted another row right now, it would be “4,” and the row after that would be “5” and so forth.

This is important because when writing an application, you would otherwise need to check to see who the last person had been (Nick, #3), and then you would need to make the next person “Stacy” #4 manually. But with auto increment, the software platform does this all for you. 

UPDATE command:

Surprisingly, INSERT isn’t the most often used command when it comes to manipulating data. Once data has been inserted and created, it has to be updated.

The SQL UPDATE command updates the present records in the database table. UPDATE uses WHERE to specify which row will be affected. If we don’t use the WHERE clause, then the entirety of the table will be affected.

Both SELECT and UPDATE commands are likely to be the most often-used commands for any database administrator.

General Syntax:

UPDATE table-name
SET column_1=value1,column_2=value2,column_3=value3,…….WHERE any column=any value;

Example:

UPDATE Person SET PersonName=’Brat’, City=’Berlin’                      
WHERE PersonName=’Angela’;

UPDATE is both powerful and dangerous. You could easily just say:

Update Person set PersonName = ‘Angela’;

If you run this code accidentally, without a WHERE command, you would update every single person’s name to “Angela.” And without COMMIT and ROLLBACK functionality (which every server does not support, and which we discuss later on), you may not have an easy way of ever restoring that data.

So keep in mind that you’ll have to control these update commands with care. Usually, an interface handles updates — they’re great tools to ensure that changes don’t apply to wide swathes of the database by mistake.

As you can see from the examples above, UPDATE commands can also get pretty intricate because you need to set every column you’re going to change. You have to be careful about specifying where you want to change it. But once you get good with UPDATE commands, they’re simple to write.

DELETE commands:

Finally, sometimes data has largely outlived its usefulness and has to be deleted.

The SQL DELETE command is how you delete data from a database. It can also delete all the data from the table without deleting the database table, and it also protects the structure and attributes of the table.

General Syntax:

DELETE FROM table_name  WHERE any column=any value;

And the syntax for deleting whole data from the table is:

DELETE * FROM table_name;

Example:

DELETE FROM Person WHERE PersonName=’brat’;

Alternatively, if you:

DELETE * FROM Person;

You will have deleted all the data from the table. This is also a dangerous command because it’s very easy to delete a lot of information from a database at once.

In fact, in general practice, many databases never delete rows from a database. Instead, they will update the rows to be inactive or delete information from the rows. Often, though, the database will leave the rows intact. While this does mean that the database requires more by way of space and resources, it also means that the database always preserves information rather than potentially losing it forever.

You should never manually delete from a table without backing it up first because it can be so easy to accidentally delete more than you should.

3. Transaction Control Language (TCL):

Sometimes you may have to make adjustments to the database, requiring it to be rolled back. The Transaction Control Language can help in these situations, though it often has to be manually implemented (most databases, like MySQL, don’t have transactional queries turned on by default).

The commands it includes are COMMIT, ROLLBACK, and SAVEPOINT.

COMMIT command:

The COMMIT command makes it so that any changes made to the database will commit in full. 

General syntax:

COMMIT;

You wouldn’t use a COMMIT until you had created a SAVEPOINT. Remember that these transactional control language statements may differ depending on the platform you’re using and the programming language you’re using.

ROLLBACK command:

When you need to restore the database into the last committed state, ROLLBACK is the most effective command. 

The general syntax is:

ROLLBACK to savepoint-name;

A simple example is during user creation. If a user decides not to finish their account, the database might roll back to where they were when the user first started creating their account.

Rollback is extremely useful. But you should be aware that this also commits a large amount of overhead to the database. Whenever you work with commits and rollbacks, the database has to commit these savepoints to memory. Keep in mind that the database may be doing a huge number of transactions at a single point in time.

Because of this, savepoint, rollback, and commit really aren’t used unless it’s absolutely essential that the database remains correct. This usually involves some form of currency transaction.

SAVEPOINT command:

This command will create a checkpoint in the database so that you can rollback to the same point whenever required.

General syntax:

SAVEPOINT savepoint-name;

You should do this before manipulating any sensitive data, such as banking or currency transactions. Liberal use of savepoints can save the database from corruption, but again it does come at a performance cost.

4. Data Control Language (DCL):

Not every user in a database needs to have all permissions available. Some users may only be able to view the database, for instance, rather than editing it. DCL commands exist to provide security and permissions. 

Those who are programming with databases may never dig too deep into permissions. They will already have permissions granted to the server that they are programming on and will be interfacing through the server and a programming language.

But those who are building databases will need to use the GRANT and REVOKE commands quite frequently.

As with other types of security, it’s always best for each user to have only the privileges that they absolutely require. Thus, if a hacker breaches an account, the cyberattack won’t be as significant as it would be otherwise. It’s poor practice to have multiple administrator accounts or to give all accounts administrator privilege.

GRANT command:

The administrator of the database can GRANT or remove the privileges for other users. 

General syntax:

GRANT privilege-name ON object-name
TO {user-name |PUBLIC| role-name} [with GRANT option]

Privilege name is a privilege given to the user. The object name is the name of the database (the database object), and the GRANT option specifies the specific rights given to the user. 

REVOKE command:

REVOKE command is what you would use to remove access or privileges given to the user from database objects. Privileges basically define the access rights granted to the user on database objects. There are two types of privileges:

1. System privileges
2. Object privileges

General syntax:

REVOKE privilege-name ON object-name
FROM {user-name |PUBLIC| Role-name}

Common SQL Functions

Apart from the above, there are some important SQL functions to note. These functions are used alongside commands to give them greater utility.

Among some important SQL functions are:

• TRIM(), which strips extraneous letters and numbers from a variable.
• UPPER(), which converts to upper case, and LOWER(), which converts to lower.
• ABS() returns the absolute number of a number, so “1” for -1 or +1.
• GETDATE() the current database time, very important when time stamping.

There are tons of SQL functions out there that you might need occasionally, such as CEILING or FLOOR. These SQL functions are going to be something that you should familiarize yourself with, but it’s often enough to know that if you’re trying to do something in SQL to manipulate data, there is probably a built-in function for that.

In addition to this, languages such as PHP will have built-in functions that work with SQL, depending on the type of SQL you’re using. 

Variations in Language

SQL stands for Structured Query Language. It functions as a set of standards, but SQL platforms like MySQL, Oracle DB, and Microsoft SQL all use slightly different variations of the SQL language. When in doubt, you should consult the manual and documentation for the system you’re using.

Most SQL will be universal. But things like the Transaction Control Language may differ depending on the platform that you’re using. There may be additional features that you can use, especially at scale.

Summary

SQL is a complicated language. However, there’s not that much that someone needs to know to get started. By understanding CREATE, SELECT, UPDATE, and DELETE, most people can do the basics in SQL. But by learning more, they can take advantage of very powerful and universal database technology.

The easiest way to start to learn SQL is to create a test database, such as a database that includes “users.” From there, you can put information into that database, manipulate it, and remove it. The more practice you have, the more intuitive it will become.

Is it worth it to learn SQL? For most programmers, absolutely. While programmers may know a language like Java or C++, they will have to rely on SQL for most databases. And there are many database administrators who don’t otherwise program but still need to have a thorough understanding of SQL.

In terms of longevity and viability in the marketplace, quite a few applications use SQL; it’s the de facto database standard when dealing with relational databases. Understanding SQL can significantly broaden someone’s opportunities within the tech market and serve as a jumping-off point for careers such as database manager or database architect.