13 Common SQL Queries for Managing Data

Databases represent large swimming pools. If you don’t know how to swim, the deep end of the pool at summer camp can seem like a very daunting, dark place. However, that same pool could be a stunning and refreshing place on a sunny day, if you know how to swim. 

Similarly, a huge database can be overwhelming if you don’t have an efficient way to sort, manage, and find the data you need. That same database can turn into a fun and useful pool of possibilities if you know the most common and simple queries of SQL.

SQL makes it easy to work with massive databases without getting overwhelmed or needing to spend hours manually scrolling through Excel spreadsheets. This post covers 13 of the most used SQL queries that you can use to find, sort, arrange, modify, and manage data in a database of any size.

Before we start talking about them, we should realize that these queries work on all types of SQL engines available in the market. 

Demystifying SQL: What is it?

SQL, or Structured Query Language, serves as a programming language designed to help you communicate with relational databases in your system. The SQL language uses simple statements and queries to manage, organize, and update data in a database.

For a simple metaphor, think about how we turn a car while driving. When you want to turn your vehicle, you rotate the car’s steering wheel without worrying about the mechanical reaction taking place within the wheel’s axle. Similarly, when you use SQL, you only have to fire simple queries to retrieve and manage data from your database without having to understand and work with behind-the-scenes internal database operations. 

SQL provides you with an easy-to-understand interface, allowing you to communicate with your database efficiently.

Getting started with using SQL

Download a SQL engine

To use SQL, you must have a database management application. You can find plenty of SQL engines available online for free such as MySQL Workbench, Sequel Pro, and SQLite3 (a great option for beginners using SQL!). Once you download your chosen engine, you’ll be able to connect your database to the application. 

You should wisely choose a SQL engine that will be compatible with your company’s database systems. If not sure what option will be best, check with your IT team or product team to get their opinion.

Run the engine

When you’ve downloaded a SQL engine and connected it to your database, run the engine and start using SQL. 

To run your engine, simply open the command line on your computer and type:

sqlite test.db

Once you’ve got it up, you should see a SQL prompt on your screen. You can now begin using SQL to view, sort, and manage your data.

What is a SQL query?

When writing in the SQL language, you will use a variety of SQL keywords to create statements and queries.

A statement involves a string of characters and SQL keywords that conforms to the formatting and syntax rules of the language and may affect data or control processes in relation to your data. You can think of a statement as a “complete sentence” in the SQL language that can be successfully sent to a database management system.

A query reflects a special type of statement written to retrieve data based on specific criteria. The result of writing a SQL query will be a set of data that meet the criteria you outlined in your query. You will often rely on SQL keywords to help you define the criteria in your query.

A query will return data. 

Consider the top most commonly used SQL queries:

1. Creating a table (CREATE TABLE)

To create a brand new, empty table, you will use the SQL keyword “CREATE TABLE.” As an example, let’s look at the following SQL query:

CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);

This query will create a table called “student” with zero rows and three columns. This table will store records of students. It will have student ID, student name, and student age. Its structure will be like this:

ID (Primary key)

Name

Age

Here, the column ID, set as a primary key, means that the ID column can have only unique values, and cells in this column cannot be left blank. 

When writing this type of query, you must define the character type after the title of each column. In the example above, the Name column, defined in the query as “TEXT,” means that it can include characters. The Age column, defined in the query as “INTEGER,” means it can only hold numbers (not letters or special characters).

2. Inserting records in a table (INSERT INTO)

You can easily insert rows of data, or records, into a new or existing table. To do this, you will use the INSERT INTO command, with the SQL keyword, VALUES. This SQL query comprises two parts: the first part identifies the columns that will have data in them, and the second part defines the data to put into those columns. 

You will first type INSERT INTO, followed by the name of the table you are working with. Then, in parentheses, list the columns that will contain data in this new row. 

Then, you will type VALUES and, in parentheses, define the exact values that will be filled in the respective columns, in the same order that you defined the column names. These values should be written inside single quotation marks, separated by commas.

Let’s say you want to add a new student named Alan, aged 28, to your student table. 

 Simply type the following SQL query:

INSERT INTO student (id, name, age) VALUES (‘1’, ‘Alan’, 28);

After firing the above query, our table will look like this:

ID	Name	Age
1	Alan	28

We can fire more such queries to fill records in our table. For example:

insert into student (id, name, age) values (‘2’, ‘Amy’, ‘26’);

insert into student (id, name, age) values (‘3’, ‘Bob’, ‘27’);

insert into student (id, name, age) values (‘4’, ‘Chris’, ‘28’);

insert into student (id, name, age) values (‘5’, ‘Dan’, ‘26’);

With the queries above, the table now becomes:

ID	Name	Age
1	Alan	28
2	Amy	26
3	Bob	27
4	Chris	28
5	Dan	26

3. Viewing all records from a table (SELECT)

The SELECT statement represents one of the simplest and most frequently used in SQL. It allows you to view all of the records from a table that you specify. 

To use the SELECT statement, simply type:

SELECT * FROM student;

Use the SQL keyword FROM to define the name of the table from which you’d like to see records. 

The result of this query will be a display of all rows present in the table you named:

ID	Name	Age
1	Alan	28
2	Amy	26
3	Bob	27
4	Chris	28
5	Dan	26

4. Arranging the records in a table (ORDER BY)

You use the ORDER BY clause with your SELECT query to arrange the displayed results in a particular order. 

For example, if you type the following:

SELECT * FROM student ORDER BY age;

You will get the following return:

ID	Name	Age
2	Amy	26
5	Dan	26
3	Bob	27
1	Alan	28
4	Chris	28

As you can see above, the output is arranged in increasing order by age. The ORDER BY clause will automatically arrange the records in increasing order. If you want to see your records displayed in decreasing order, you can simply insert the SQL keyword DESC keyword into your query immediately after the column name.

This would look as follows:

SELECT * FROM student DESC ORDER BY age;

You will then get the following return:

ID	Name	Age
1	Alan	28
4	Chris	28
3	Bob	27
2	Amy	26
5	Dan	26

5. Viewing only selected records from a table (SELECT COUNT)

If a huge number of rows exist in a table, and you do not want all the records to fill your display screen, then SQL gives an option to view only selected rows. You can use the SELECT COUNT clause to do this, as seen below:

SELECT COUNT(1) FROM student;

The output of this query will be:

ID	Name	Age
1	Alan	28

If you fire the following:

SELECT COUNT * FROM student;

It will return the number of rows in your table. 

You can also use the MAX function or MIN function in your SELECT COUNT query. For example, if you want to retrieve details of students up to a maximum age, you can fire the following query:

SELECT id , name , MAX(age) FROM student;

You will get this return:

ID	Name	Age
1	Alan	28
4	Chris	28

You can also use the SELECT query with the SUM keyword to receive the sum of a numeric column. For example, you can fire:

SELECT sum(age)FROM student;

You will receive 135 as output (the sum of all of the ages in your table).

Keep in mind: you can only use MAX, MIN, and SUM functions with numeric columns. If you try to use these functions with a text column, you will receive an error message.

6. Deleting records from a table (DELETE)

To delete selected rows from a table, fire the following query:

DELETE FROM student WHERE name = ‘alan’;

This query will look at the table called “Student” and will delete the entire row (or multiple rows) wherever the Name column has the value “alan.”

In our case, the result of this query will be the following table:

ID	Name	Age
2	Amy	26
3	Bob	27
4	Chris	28
5	Dan	26

7. Changing data in existing records in a table (UPDATE)

The UPDATE clause allows you to change information for records that already exist in your table. Suppose you want to change the age of a student named ‘Amy’ in your table. You would fire this query:

UPDATE student SET age = 22 WHERE name = ‘amy’;

Note that if the column referenced contains characters (as the Name column does), you must specify the specific value within single quotation marks: ‘amy.’

After writing the query above, if you then fire:

SELECT * FROM student;

You will get the following table as output:

ID	Name	Age
2	Amy	22
3	Bob	27
4	Chris	28
5	Dan	26

As you can see above, Amy’s age has been changed from its original value (26) to 22. 

Be careful when using the UPDATE or DELETE queries with the help of the WHERE clause. Suppose that your Student table contains more than one student with the name ‘Amy.’ If you write the UPDATE query above, the age of all students with the name ‘Amy’ will be updated to 22. 

You should always use PRIMARY KEY in WHERE clause while updating or deleting. There will be a unique primary key for every row. Even if three records exist with the name Amy, they will each have a distinct primary key. Therefore, you can use Primary Key rather than Name in your query to update or delete information, ensuring that your query impacts only one record.

You also need to take note of the data type for a given column when changing the data in it. A numeric column can only contain numbers, while a text column can only contain text. If you tried to use the UPDATE statement to put age = ‘Amy’ in the Age column, SQL will not be able to complete the request and will return with an exception. 

8. Viewing records from a table without knowing exact details (LIKE)

In real life, when you interact with a database, you probably may not know all of the exact column values. For example, as a data operator in school, you may be aware of a student named Kellie in your school from hearing other teachers talking about her. Perhaps you want to find the entire records for Kellie, but you do not know how she spells her name —  “Kellie,” “Kelly,” or “Kelli.” 

In this case, you can use the LIKE operator in SQL to help you find a row of data when you only know some of the characters included.

You may fire the following query:

SELECT * FROM student WHERE name LIKE ‘kell%’;

The output of this query will show you all rows of students where the data in the Name column begins with “Kell” and ends with any letters. This can help you find data when you only have partial information, not sure of the exact details.

9. Using more than one condition in WHERE clause to retrieve records

To understand the requirement of using the SQL keyword, WHERE, let us first insert one more row in our table — we will insert a sixth row with a student named Dan (using an INSERT INTO query, as described above).

Our table now becomes as follows:

ID	Name	Age
2	Amy	22
3	Bob	27
4	Chris	28
5	Dan	26
6	Dan	24

Now, if we would like to see the new student Dan’s records, we may fire our query as:

SELECT * FROM student WHERE name = ‘dan’;

The output will then be as follows:

ID	Name	Age
5	Dan	26
6	Dan	24

As you can see above, you were unable to fetch a unique record just by using the Name value in the WHERE clause. In this case, there exist multiple records with the same data in the Name column. 

Now, consider combining more than one condition in the WHERE clause. This can be easily done using conditional keywords like AND and OR. 

For example, if you fire:

SELECT * FROM student WHERE name = ‘dan’ AND age = 24;

You will get the following output:

ID	Name	Age
6	Dan	24

By defining both the Name and Age values and by connecting them with the SQL keyword, AND, you are able to isolate one person named Dan from another person with the same name.

You can also combine the AND and OR conditions in the WHERE clause to refine your search even further. Let’s say that you want to find any records where the student’s name is Dan, or the student is over 25 years old. You could fire the following query:

SELECT * FROM student WHERE name = ‘dan’ OR age > 25

Your output will be:

ID	Name	Age
3	Bob	27
4	Chris	28
5	Dan	26
6	Dan	24

As you can see above, the result will include all students with the name Dan as well as all students with an age above 25.

You can use different conditions like AND, OR and < , > in a combination or individually in WHERE clause to fetch the desired rows. 

10. Knowing the structure of a table

With so much data that organizations have to manage, you cannot be expected to memorize the columns and format of every table in your database. When working with mountains of data and viewing it in pieces, you can easily forget how many columns a specific table has, which column represents the primary key, or what character types belong in each column. 

Fortunately, the designers of various SQL engines have provided easy ways to check on the structure of a particular table, so you can refresh yourself on the setup. Each SQL engine has its own command for this action, so you’ll need to learn which one applies to your SQL engine.

For example, taking the table we defined as ‘student,’ in order to check on this table’s structure in SQLite3, you would use the following command:

.schema student;

Whereas in PostgreSQL, the command will be:

 \d student

MySQL uses the following command:

describe student;

All of these will provide you with the same result — showing you the structure of the ‘student’ table, identifying how many columns it has, their names and formats, along with which column is used as the primary key.

11. Adding or deleting columns in a table (ALTER TABLE)

If you would like to create new columns in an existing table in your database, you can use the ALTER TABLE command combined with the SQL keyword, ADD. In your query, type ALTER TABLE, followed by the table name, and then ADD, followed by the name of the new column and the character type for the data in this column (for instance, the character type may be TEXT or INTEGER). 

Fire a query as follows:

ALTER TABLE student

ADD email VARCHAR;

This query will insert a new column into your existing table, labeled Email.

In the example above, the character type for the Email column is defined as VARCHAR, meaning it can contain letters, special characters, and numbers — as email addresses may contain a combination of these. 

You can similarly use the ALTER TABLE command with the SQL keyword, DELETE, to remove existing columns from a table in your database. 

ALTER TABLE can also modify existing columns (such as to change the column name or character type) and add or delete constraints set in the table.

12. Finding empty values in a table (IS NULL / IS NOT NULL)

The IS NULL operator allows you to differentiate empty cells in a table (having no data in them) from cells that have data.

Note that a “null” value is not the same as a value of zero. A cell that has 0 in it still shows a value; it is simply a value of 0. “Null” is a SQL keyword that refers to a cell being left empty or blank, with no characters or numbers in it at all.

Let’s say you want to look for empty, data-less columns in your database. You can fire the following query:

SELECT ID, name, age

FROM student

WHERE age IS NULL;

This will show you all of the rows in your student table where the Age column is left blank or empty.

You can use IS NOT NULL to do the exact opposite if you want to select and only show data that is filled in with a value.

You can fire the following query:

SELECT ID, name, age

FROM student

WHERE age IS NOT NULL;

This will show you the set of data from your student table for all rows that have a value in the Age column (including the value of 0).

13. Checking the performance of a query (EXPLAIN)

EXPLAIN is an advanced query that allows you to check the performance of another query. This becomes particularly useful if you need to figure out why an existing query seems to be running slowly. 

Use EXPLAIN before a SQL statement to obtain a breakdown of the timings of the various parts of that query.

For example, to check the performance of a SELECT query that you wrote in relation to your ‘student’ table, you can fire the following:

EXPLAIN QUERY SELECT * FROM student;

You can use EXPLAIN before a SQL statement to obtain a breakdown of the timings of the various parts of the query. This is useful for identifying the reason behind a slow query.

In the PostgreSQL engine, you can use EXPLAIN and EXPLAIN ANALYZE before your query.

In SQLite3, you can use EXPLAIN QUERY PLAN before your query.