Essential SQL commands for database management
Tags: sql, database, datascience, tutorial
Published: 2022-07-26 20:28:29

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?](#rdbms)
* [Subsets of the SQL language](#sql-subsets)
* [Anatomy of a typical SQL command](#sql-anatomy)
* [The top SQL commands to learn](#sql-commands)
	* [1. CREATE DATABASE and ALTER DATABASE](#database)
	* [2. USE](#use)
	* [3. CREATE TABLE, ALTER TABLE, and DROP TABLE ](#table)
	* [4. INSERT INTO](#insert-into)
	* [5. UPDATE](#update)
	* [6. DELETE](#delete)
	* [7. SELECT](#select)
	* [8. ORDER BY](#order-by)
	* [9. GROUP BY](#group-by)
	* [10. HAVING](#having)
	* [11. UNION and UNION ALL](#union)
	* [12. JOIN](#join)
	* [13. CREATE INDEX and DROP INDEX](#index)
	* [14. GRANT and REVOKE](#grant-revoke)
    * [15. LIKE](#like)
* [Wrapping up and next steps](#next)

<a name="relational-database"></a>

## What is a relational database? 
A [relational database](https://www.educative.io/blog/relational-database-deep-dive?eid=5082902844932096) 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)](https://www.educative.io/blog/crud-operations?eid=5082902844932096) 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. 

<a name="sql-subsets"></a>

## 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. 

<a name="sql-anatomy"></a>

## Anatomy of a typical SQL query
SQL [queries](https://www.educative.io/blog/what-is-database-query-sql-nosql?eid=5082902844932096) 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](https://www.educative.io/blog/sql-server-tips?eid=5082902844932096) to write them in all uppercase.

Most SQL queries come in the form of DML statement<sup>[[1]](https://www.ibm.com/docs/en/informix-servers/12.10?topic=SSGU8G_12.1.0/com.ibm.dbdk.doc/ids_dbdk_028.htm)</sup>, 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. 

<a name="sql-commands"></a>

## The top SQL commands to learn

<br>
<a name="database"></a>

### 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';
```
<a name="use"></a>
### 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. 

<a name="table"></a>

### 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. 

<a name="insert-into"></a>

### 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.

<a name="update"></a>

### 5. UPDATE
The `UPDATE` command modifies data already stored in a table. 

Syntax: 

```
UPDATE table_name
SET column_name = value, column_name = value, ...
WHERE condition;
```

Example:

``` 
UPDATE people_table
SET name = 'Crystal Sequel', age = 65
WHERE 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! 

<a name="delete"></a>

### 6. DELETE

The `DELETE` command deletes data from a table. 

Syntax: 
```
DELETE FROM table_name
WHERE condition;
```

Example:
``` 
DELETE FROM people_table
WHERE 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. 

<a name="select"></a>

### 7. SELECT and FROM

The `SELECT` command queries data `FROM` a table. 

Syntax:
``` 
SELECT column_name, column_name, ...
FROM table_name
WHERE condition;
```

Example: 
```
SELECT name, age
FROM people_table
WHERE 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. 

<a name="order-by"></a>

### 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) 

<a name="group-by"></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. 

<a name="having"></a>

### 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. 

<a name="union"></a>

### 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_name1
UNION
SELECT column_name FROM table_name2;
```

Example: 
```
SELECT names FROM employee_table
UNION
SELECT 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_one
UNION ALL
SELECT column_name FROM table_name_two;
```

Example: 
```
SELECT names FROM people_table
UNION ALL ALL
SELECT 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.

<a name="join"></a>

### 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_table
INNER JOIN right_table 
ON left_table.column_name = right_table.column_name;
```

Example: 
```
SELECT name, email FROM people_table
INNER 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_table
LEFT 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_table
RIGHT 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_table
FULL OUTER JOIN right_table ON left_table.column_name = right_table.column_name;
```

[Joins](https://www.educative.io/blog/what-are-sql-joins?eid=5082902844932096) 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.

<a name="index"></a>

### 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. 

<a name="grant-revoke"></a>

### 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.

<a name="like"></a>

### 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".

<a name="next"></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**](https://www.educative.io/courses/introductory-guide-to-sql?eid=5082902844932096) course.
 
*Happy learning!* 

### Continue learning about SQL on Educative
* [What are SQL joins? Types of SQL joins explained](https://www.educative.io/blog/what-are-sql-joins?eid=5082902844932096)
* [Crack the top 40 SQL interview questions](https://www.educative.io/blog/top-40-sql-interview-questions?eid=5082902844932096)
* [MySQL tutorial: The beginner's guide to using MySQL](https://www.educative.io/blog/mysql-tutorial?eid=5082902844932096)

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