Essential SQL commands for database management

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: 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).

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_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!

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.

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.

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

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