The 5 Basic SQL Queries Explained with Examples

SQL (Structured Query language) can be used to interact with relational database management systems. It is designed for the retrieval, creation, manipulation, updation, storage, and management of data in a relational database. For a better understanding, we can compare SQL to a car key. We just use the key or push the button to start the car without worrying about the background process that happens automatically. We just have to throw simple SQL queries & statements to manage and retrieve the data from the database without having to understand or work with it under the counter of the internal database operations. In addition to that, both proprietary and open-source relational database management systems such as Microsoft SQL Server, Oracle Database, IBM DB2, and MySQL have been built around SQL. That is why it is important to know the 5 basic SQL queries we’ll be exploring in our blog.

SQL syntax is fairly simple and consists of commands as written statements like CREATE, TABLE, and ALTER COLUMN. These commands can be used to modify database tables & index structures, add, update, & delete rows of data, and retrieve subsets of information from the database for transactional processing and analytical operations. Being a leading data analytics testing company, we have vast experience in performing such actions in our testing process. But the basic SQL queries we will be discussing in this blog will be beneficial to everyone in general and not just for testers. But before that, let’s find out what is an SQL query and why you will need it.

What is an SQL query and Why do we need it?

While working with SQL language, you will use numerous keywords and SQL commands to compose statements and queries. Queries are basically requests we can use to communicate our requirements to the database and get the appropriate outputs. You can use SQL queries to pre-aggregate, filter, and select only the data necessary for the project you are working on. The selected data are generally called views. So if you have a table that contains millions of rows of data, creating a view of them will enable you to perform analysis and arrive at results much faster. Let’s now take a look at the 5 basic SQL queries that will help you create a view.

• Getting Data from Columns using SELECT & FROM
• To fetch non-repeated records by using DISTINCT
• Filtering Data based on requirements by using WHERE Clause
• Query using Not in operator
• Sort the Result set by using the ORDER BY Keyword

Getting Data from Columns using SELECT & FROM

SELECT * FROM WORKER_TABLE;

Explanation:

SELECT – It is used to specify the column of the table from which the data has to be fetched & displayed. In the example, we have used ‘*’ instead of a specific column name as ‘*’ is used to denote all the columns.

FROM – It is used to specify the database from which the data has to be fetched from. In our query, we have specified ‘worker_table 1’ to be the database we are gathering data from.

Output:

To fetch non-repeated records by using DISTINCT

SELECT DISTINCT DEPT AS DEPARTMENT FROM WORKER_TABLE

Explanation:

There is a high chance that the database we selected has a lot of duplicate values. So DISTINCT is the next query we’re going to see from our list of basic SQL queries as it can be used to retrieve only the unique data and avoid duplication.

By using DISTINCT in the above example, we will be collecting unique data from the dept column shown in the worker_table 1 and show it under the alias name ‘Department’. An Alias is generally created to give a temporary name to the unique data by using AS as the keyword.

Output:

Filtering Data based on requirements by using WHERE Clause

WHERE EMPLOYEE_ID >105

Explanation:

We have now used 2 of our basic SQL queries to extract data that has no duplicates. But in most scenarios, you’ll need more refinement than that. So you can use the WHERE clause to filter the data based on your requirements.

Let’s say you want to get the data of the employees who have joined the company after a specific date. So you can set a requirement that the employee_id should be greater than 105.

Output:

Query using Not in operator

SELECT * FROM WORKER_TABLE 
WHERE FIRST_NAME NOT IN ('Zoe', 'Sadie')

Explanation:

You can further drill down your results by adding exceptions using the NOT IN command. NOT IN is an important command that can be used along with the WHERE clause. It can be used to retrieve data from rows that don’t satisfy the specified condition.

In this case, we are retrieving a list of the employees whose first names are not Amitabh and Aruna. Here, Amitabh and Aruna are the exceptions that we wanted to avoid.

Output:

Sort the Result set by using the ORDER BY Keyword

SELECT * FROM WORKER_TABLE
ORDER BY DEPT DESC;

Explanation:

By using all the basic SQL queries, you would now be able to have a refined set of data as per your need. The last query we are going to discuss, ORDER BY can be used to sort the result we have according to the specified column.

You have to specify the column by which the data has to be sorted by placing it after the ORDER BY keyword. By default, the column will be alphabetically sorted (i.e) asc in ascending order. But if you wish to fetch the rows in descending order, you can do so by adding ‘desc’ after the column name as mentioned above.

Output:

Conclusion

Hopefully, you now have a clear understanding of what makes these 5 basic SQL queries so important. They lay the foundation that you can build upon to grow your expertise in SQL. We have been providing data testing services for many years and our testers have found SQL to be very important. We will be publishing more informative content, so make sure you subscribe to our newsletter to never miss out on any of our latest content.