Essential SQL commands for database management

Nowadays, companies collect tons of data, and working with large datasets or databases will often require a working knowledge of SQL, or, Structured Query Language. SQL is the primary way data scientists, database admins, and database engineers pull and manipulate data from relational databases.

SQL syntax can be easily read by anyone who knows the anatomy of a SQL statement and what the most important commands do. These commands will help you perform common tasks such as creating and deleting databases, adding and deleting tables, and inserting and retrieving data.

Today we'll be going over the different components of a relational database, subsets of the SQL language, the general structure of a SQL statement, and some examples of important SQL statements to use when managing a database of your own.

Let's get started!

We'll cover:

• What is a relational database?
• Subsets of the SQL language
• Anatomy of a typical SQL command
• The top SQL commands to learn
  • 1. CREATE DATABASE and ALTER DATABASE
  • 2. USE
  • 3. CREATE TABLE, ALTER TABLE, and DROP TABLE
  • 4. INSERT INTO
  • 5. UPDATE
  • 6. DELETE
  • 7. SELECT
  • 8. ORDER BY
  • 9. GROUP BY
  • 10. HAVING
  • 11. UNION and UNION ALL
  • 12. JOIN
  • 13. CREATE INDEX and DROP INDEX
  • 14. GRANT and REVOKE
  • 15. LIKE
• Wrapping up and next steps

What is a relational database?

A relational database organizes data into structured tables for finding shared data points. Tables are similar to folders in a traditional file system, and each table stores a collection of information.

SQL is the language used to interact with relational databases. SQL commands are used to perform basic database operations such as creating, reading, updating, and deleting (CRUD) anything related to the database.

Relational databases are the most popular type of database used in enterprise settings. These databases help power some of the largest companies in the world, including Facebook, Amazon, and Google.

Subsets of SQL

Commands are also used to create, alter, and drop databases and tables. This article will focus on the SQL commands used frequently in database management.

These commands will be divided into four categories:

• Data manipulation language (DML) commands
• Data definition language (DDL) commands
• Data control language (DCL) commands
• Transaction control statements (TCS)

DML commands are used to manipulate and perform operations on data in a database. Examples of DML commands include SELECT, INSERT, and UPDATE.

DDL commands are used to define the structure of a database. You can change the database schema by creating new tables and objects or altering their attributes (such as their data type, table name, etc.). Examples of DDL commands include CREATE and ALTER.

DCL commands are used to control user permissions and access to a database. Examples of DCL commands include GRANT and REVOKE.

TCS commands are used to manage transactions in a database. Transactions are units of work that can be either committed or rolled back. Examples of TCS commands include COMMIT and ROLLBACK.

Now that we've reviewed the different types of SQL commands, let's take a closer look at an example of a typical SQL query.

Anatomy of a typical SQL query

SQL queries are written using specific syntax and order. The queries are usually composed of a few commands (clauses) that are (almost) always capitalized.

While SQL commands are not case sensitive, it is considered good practice to write them in all uppercase.

Most SQL queries come in the form of DML statement^[1], and the basic syntax for this is as follows:

SELECT column_name AS alias_name   FROM table_name   WHERE condition   GROUP BY column_name   HAVING condition   ORDER BY column_name DESC;

Let's break down each part of this syntax:

• SELECT: The name of the SQL command you want to execute. For DML queries, this command can be SELECT or UPDATE.
• column_name: The name of the column you want to query.
  • You can give the column a temporary alias by using the AS keyword and providing an alias name.
• FROM: Here, you are specifying a query from a specific table, in this case, table_name.
• WHERE: This clause is used to filter the query results that meet a specific condition.
• The WHERE clause can be used in conjunction with AND, OR, BETWEEN, IN, LIKE to create queries.
• GROUP BY: A clause that groups rows with the same values into summary rows.
• HAVING: This clause filters the results of the query (similar to the WHERE clause), but it can be used with aggregate functions.
• ORDER BY: An optional clause that is used to sort the query results in ascending or descending order.
• DESC: The order of your result set is set to ascending (ASC) by default. DESC can be used to set a descending order.

Note: This is the basic syntax for many SQL queries! Not all queries follow this exact syntax but knowing this structure will help a great deal with database management, analysis, and more.

The top SQL commands to learn

1. CREATE DATABASE and ALTER DATABASE

The CREATE DATABASE command creates a new database. A database must be created to store any tables or data.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE fruit_database;

The ALTER DATABASE command modifies an existing database. For example, the ALTER DATABASE command can add or remove files from a database.

Syntax:

ALTER DATABASE database_name action;

Example:

ALTER DATABASE fruit_database ADD FILE 'mango.txt';

2. USE

USE is selects a database. This command is frequently used to begin working with a newly created database.

Syntax:

USE database_name;

Example:

USE fruit_database; 

Once a database has been selected, all subsequent SQL commands will be executed on that database.

Keep in mind that the USE command can only select databases that have already been created.

If a database with the specified name does not exist, then an error will be returned.

3. CREATE TABLE, ALTER TABLE, and DROP TABLE

The CREATE TABLE command creates a new table in a database. A table must be created before any data can be inserted into it.

Syntax:

CREATE TABLE table_name (    column_name data_type,    column_name data_type,    ...);

Example:

CREATE TABLE people_table (    id INTEGER,    name VARCHAR(255),    age INTEGER);

In this example, we are creating a table called people_table with three columns: id, name, and age.

The data type for each column must be specified. Some common data types include INTEGER, VARCHAR, and DATE.

The ALTER TABLE command modifies an existing table. For example, the ALTER TABLE command can be used to add or remove columns from a table.

Syntax:

ALTER TABLE table_name action;

Example:

ALTER TABLE people_table ADD email VARCHAR(255);

In this example, we are adding a new column called email to the people_table table. The data type for the new column must be specified.

It's also possible to use the ALTER TABLE command to modify an existing column's data type.

Syntax:

ALTER TABLE table_name MODIFY COLUMN column_name data_type;

Example:

ALTER TABLE people_table MODIFY COLUMN last_name VARCHAR(128);

In this example, we are modifying the last_name column to have a data type of VARCHAR(128).

Note that you cannot use the ALTER TABLE command to modify the data type of a column if any data is stored in that column.

To change the data type of a column, you must first delete all the data from that column.

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE people_table DROP COLUMN email;  

In this example, we are removing the email column from the people_table. Note that this command will permanently delete all data stored in that column.

The DROP TABLE command deletes an entire table from a database. This command will permanently delete all data stored in the table.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE people_table;

In this example, we are deleting the people_table table from the database.

It's important to be careful when using the DROP TABLE command, as it cannot be undone! Once a table is deleted, all data stored in that table is permanently lost.

An alternative to DROP TABLE is to use TRUNCATE TABLE instead. This command will delete all data from a table, but it will not delete the table itself.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE people_table;

In this example, we are deleting all data from the people_table table. The table itself is not deleted, so any column information is retained.

4. INSERT INTO

The INSERT INTO command inserts data into a table.

Syntax:

INSERT INTO table_name (column_name, column_name, ...)VALUES (value, value, ...);

Example:

INSERT INTO people_table (id, name, age)VALUES (NULL, 'Crystal', 64);

In this example, we are inserting a new row into people_table. The first column in the table is id. We have specified that this column should be set to NULL, which means that the database will automatically generate a unique id for this row.

The second and third columns in the table are name and age, respectively. We have specified that these columns should be set to 'Crystal' and 64 for this row.

5. UPDATE

The UPDATE command modifies data already stored in a table.

Syntax:

UPDATE table_nameSET column_name = value, column_name = value, ...WHERE condition;

Example:

UPDATE people_tableSET name = 'Crystal Sequel', age = 65WHERE id = 100;

In this example, we are updating the row with id = 100 in the people_table table. We are setting the name column to 'Crystal Sequel' and the age column to 65.

Important: The WHERE clause is required when using the UPDATE command. Without a WHERE clause, all rows in the table would be updated!

6. DELETE

The DELETE command deletes data from a table.

Syntax:

DELETE FROM table_nameWHERE condition;

Example:

DELETE FROM people_tableWHERE id = 100;

In this example, we are deleting the row with id=100 from the people_table table.

As with the UPDATE command, it's important to note that the WHERE clause is required when using the DELETE command. As you may have already guessed, all rows in the table would be deleted without a WHERE clause.

7. SELECT and FROM

The SELECT command queries data FROM a table.

Syntax:

SELECT column_name, column_name, ...FROM table_nameWHERE condition;

Example:

SELECT name, ageFROM people_tableWHERE id = 100;

In this example, we are querying people_table for the name and age of the row where id=100.

The SELECT and FROM commands are two of the most important SQL commands, as they allow you to specify and retrieve data from your database.

8. ORDER BY

The ORDER BY command sorts the results of a query.

Syntax:

SELECT column_name, column_name, ...   FROM table_name   WHERE condition   ORDER BY column_name [ASC | DESC];

Example:

SELECT name, age   FROM people_table   WHERE id = 100   ORDER BY age DESC;

In this example, we are querying people_table for the name and age of the row with id=100. We are then sorting the results by age, in descending order.

The ORDER BY command is often used in conjunction with the SELECT command to retrieve data from a table in a specific order.

It's important to note that the ORDER BY command doesn't just work with numeric data it can also be used to sort text data alphabetically!

ASC: By default, the order is ascending (A, B, C, . . . Z)

DESC: Descending order (Z, Y, X, . . . A)

9. GROUP BY

The GROUP BY command groups the results of a query by one or more columns.

Syntax:

SELECT column_name, aggregate_function(column_name)    FROM table_name    WHERE condition    GROUP BY column_name;

Example:

SELECT name, count(*)   FROM people_table   WHERE country='US'   GROUP BY names;

In this example, we are querying people_table for all of the unique names in the table. We are then using the COUNT() function to count how many times each name occurs.

The GROUP BY command is often used with aggregate functions (such as COUNT(), MIN(), MAX(), SUM(), etc.), to group data together and calculate a summary value.

The columns specified by the GROUP BY clause must also be included in the SELECT clause.

10. HAVING

The HAVING command filters the results of a query based on one or more aggregate functions.

Syntax:

SELECT column_name, aggregate_function(column_name)    FROM table_name    WHERE condition    GROUP BY column_name    HAVING condition;

Example:

SELECT name, count(*)   FROM people_table   WHERE country='US'   GROUP BY names   HAVING count(*) > 0;

In this example, we are querying the people_table for all of the unique names in the table. We then use the COUNT() function to count how many times each name occurs.

Finally, we use the HAVING clause to filter out any names that don't occur at least once in the table.

Similar to the GROUP BY clause, we can also use the HAVING clause alongside aggregate functions to filter query results.

Aggregate functions:

• COUNT(): counts the number of rows in a table
• MIN(): finds the minimum value in a column
• MAX(): finds the maximum value in a column
• SUM(): calculates the sum of values in a column
• AVG(): calculates the average of values in a column

Columns specified in the GROUP BY clause must also be included in the SELECT clause.

HAVING is very similar to WHERE, but there are some important differences:

• WHERE is used to filter data before the aggregation takes place, while HAVING is used to filter data after the aggregation takes place.
• WHERE can be used with aggregate functions, but HAVING can only be used with columns included in the GROUP BY clause.
• WHERE is applied to individual rows, while HAVING is applied to groups of rows.

11. UNION and UNION ALL

The UNION command combines the results of two or more queries into a single dataset. It is often used to combine data from multiple tables into a single dataset.

Syntax:

SELECT column_name FROM table_name1UNIONSELECT column_name FROM table_name2;

Example:

SELECT names FROM employee_tableUNIONSELECT email FROM people_table;

In this example, we use SELECT and UNION to query names from the employee_table and then combine them with emails from the people_table into a single result set.

The number and order of columns must be the same in all of the SELECT statements being combined with UNION. Also, all the columns need to be the same data type.

To combine data from multiple tables where the number and order of columns are not the same into a single dataset, use UNION ALL instead of UNION.

Syntax:

SELECT column_name FROM table_name_oneUNION ALLSELECT column_name FROM table_name_two;

Example:

SELECT names FROM people_tableUNION ALL ALLSELECT email FROM people_table;

In this example, we are querying people_table for all of the unique names in the table. We are then using the UNION ALL command to combine this dataset with another dataset containing all the unique email addresses in the table.

12. JOIN

A JOIN is a way to combine data from two or more tables into a single, new table. The tables being joined are called the left table and the right table.

The most common type of join is an INNER JOIN. An inner join will combine only the rows from the left table that have a match in the right table.

Syntax:

SELECT column_name FROM left_tableINNER JOIN right_table ON left_table.column_name = right_table.column_name;

Example:

SELECT name, email FROM people_tableINNER JOIN employee_table ON people_table.id = yourtable.id;

In this example, we are using INNER JOIN to combine data from the people_table and employee_table. We are joining the tables using the id column.

Although inner joins are the most common type of join, there are other types of joins that you should be aware of.

LEFT OUTER JOIN: A left join will combine all of the rows from the left table, even if there is no match in the right table.

Syntax:

SELECT column_name(s) FROM left_tableLEFT OUTER JOIN right_table ON left_table.column_name = right_table.column_name;

RIGHT OUTER JOIN: A right join will combine all of the rows from the right table, even if there is no match in the left table.

Syntax:

SELECT column_name(s) FROM left_tableRIGHT OUTER JOIN right_table ON left_table.column_name = right_table.column_name;

FULL OUTER JOIN: A full outer join will combine all of the rows from both tables, even if there is no match in either table.

Syntax:

SELECT column_name(s) FROM left_tableFULL OUTER JOIN right_table ON left_table.column_name = right_table.column_name;

Joins can be very useful when combining data from multiple tables into a single result set. However, it's important to note that joins can limit performance and should be used sparingly.

13. CREATE INDEX and DROP INDEX

An index is a data structure that can be used to improve the performance of SQL queries. Indexes can speed up the data retrieval from a table by allowing the database to quickly find the desired data without having to scan the entire table. Creating an index on a column is a relatively simple process.

Syntax:

CREATE INDEX index_name ON table_name (column_name);

Example:

CREATE INDEX people ON employee_table (names);

Once an index is created, the database can use it to speed up the execution of SQL queries. Indexes are an important tool for database administrators to know about, and they can be handy for improving the performance of SQL queries.

Syntax:

DROP INDEX index_name ON table_name;

Example:

DROP INDEX people ON employee_table;

Once an index is dropped, it can no longer be used by the database to speed up SQL query execution.

14. GRANT and REVOKE

The GRANT and REVOKE commands manage permissions in a database.

The GRANT command gives a user permission to perform an action, such as creating a table or inserting data into a table.

Syntax:

GRANT permission_type ON object_name TO user;

Example:

GRANT CREATE TABLE ON important_database TO bob;

The REVOKE command removes a user's permission to perform actions.

Syntax:

REVOKE permission_type ON object_name FROM user;

Example:

REVOKE CREATE TABLE ON important_database FROM bob;

Managing permissions in a database is an important task for database administrators. The GRANT and REVOKE commands are two of the most important commands for managing permissions.

15. LIKE

The LIKE operator is used to search for data that matches a specific value.

Syntax:

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

Example:

SELECT first_name FROM class_roster WHERE first_name LIKE '%a';

In the example above, the query would return all of the records from the class_roster table where the first_name column contains a value that ends with the letter a.

Placing the modulo % after the letter a would return all of the records where the first_name column contains a value that starts with the letter a.

Putting a modulo % before and after the letter "a" would return all of the records where the first_name column contains a value that contains the letter "a".

Wrapping up and next steps

Learning SQL is a valuable skill for anyone who works with data. In this article, we've covered some of the most important SQL commands that you need to know for managing databases.

If you're interested in learning more about SQL, we encourage you to keep practicing with real datasets. The more you use SQL, the better you'll become at writing SQL queries!

To get started learning these concepts and more, check out Educative's Introductory Guide to SQL course.

Happy learning!

Continue learning about SQL on Educative

• What are SQL joins? Types of SQL joins explained
• Crack the top 40 SQL interview questions
• MySQL tutorial: The beginner's guide to using MySQL

Start a discussion

Are there any other essential SQL commands that we missed? Was this article helpful? Let us know in the comments below!