SQL Basic Commands: How to Write Simple Queries with Examples

This article explains writing simple SQL queries from the most basic ones and gradually improving the script to solve some mathematical and date-related problems. Additionally, we are going to clarify the concepts surrounding SQL queries.

Although this article is primarily for beginners, it contains hints that will be helpful for any experience level.

What is SQL Query in Database?

Let us first talk a little bit about SQL queries to understand them properly before getting hands-on experience of writing.

SQL stands for structured query language that is an essential language used to query relational databases.

T-SQL vs SQL – What is the Difference?

T-SQL or Transact-SQL is the Microsoft version of SQL with more functions and features than traditional SQL language also known as ANSI SQL has.

Hence, T-SQL is a traditional SQL language plus more things added by Microsoft. It is more commonly used and mentioned when we talk about SQL.

This article will refer to the Microsoft SQL version whether we use the word SQL or T-SQL.

Why Do we Use SQL Querying in a Relational Database?

A relational database is a database with keys used to connect tables rather than connect tables physically.

For instance, you have a table called Book which you link to another table called BookType via keys to add more meanings to the records.

The tables on the illustration are linked through a key. There is no need to connect them physically. This is the fundamental rule of relational databases – you create relations between the two tables with the help of key(s).

Read also relational database vs NoSQL – what to choose for big data management?

What is Querying a Database?

You query the database to get answers to questions related to that database. In other words, you write queries against a database to get information about the data it contains.

For example, you are interested to view all book records along with their types in the particular database. You need to query that database to view the required data. For that, you must write and run a script against the database.

What Do you Need to Make a Query?

A few prerequisites must be present to query a database as we cannot just write and run a query anywhere against anything.

The following things are mandatory to query a database:

1. A database server such as SQL Server (installed locally or remotely) where you store the database.
2. A database management tool such as SQL Server Management Studio or dbForge Studio for SQL Server which you will use to write and run your queries
3. A database against which you run your queries. You can create any sample database for learning purposes.

Also, you must have a basic understanding of your database, such as which table contains the desired information and so on. Understanding of relational database concepts is a plus too.

We assume that you have already met the above requirements. But you might also refer to the following article for more details:

Basics of SQL Server Management Studio (SSMS) – Part 1

Things to Do Before Writing Queries in SQL

We are going to write simple SQL queries now.

Please set up the environment to start writing your SQL queries. Get the tools ready. Open dbForge Studio for SQL Server or SQL Server Management Studio and connect to the SQL instance. Here we start our SQL journey:

Once successfully connected press CTRL+N or go to File > New > Query with Current Connection:

Now, you are successfully connected to the master (system database) of the currently connected server.

Important Tip: Always create a sample database to run your queries (scripts) against it. Running queries against the system databases is not a good practice except for three cases:

1. You work with a sample database, and then the script crated for it will execute against the system (master) database.
2. You query the master database on purpose to get some information out of it.
3. The queries are safe to run against system (master) database.

Setup a Sample Database

Let us create a sample database called BookSimple without any tables. Write the following script against the master database to create a sample database and Press F5 to execute the query:

-- Create sample database BookSimple
USE MASTER
GO

CREATE DATABASE BookSimple
GO

USE BookSimple

The Set-based Concept behind SQL Queries

Just before you write even the simplest SQL query, you must understand that SQL is a set-based language.

It means when you want to query your database using SQL you should think in terms of sets or groups.

SQL is by design and by default very efficient to serve set-based requests. If you architect your scripts (queries) keeping in mind the set-based logic, you understand and implement SQL quicker than those following the typical path of learning (which has its own benefits).

Let us think of a naturally occurring set, such as class or group. When we refer to a class, we refer to all students in that class. SQL can help to query that class as a whole.

Similarly, a Book is a table of books. It contains all records for books. We can simply query that table as if we are talking about a single book, but, actually, we are querying the whole table of books represented by the Book table.

We will see more benefits of the set-based concept later when we explore some basic examples.

Simple SQL SELECT Statement

SELECT is a T-SQL statement that retrieves all or selected rows and columns (based on a criteria) from a database.

In other words, SELECT lets us view (select) data from a table or number of tables based on certain criteria, which, if not mentioned generally, shows all the data.

Therefore, SELECT is the first statement to look for if we want to retrieve rows and columns from a database. The simplest form of SELECT syntax is as follows:

SELECT * FROM <Table>

Remember that we will modify the syntax with time to gradually improve the learning process.

Another way to use SELECT statement is as follows:

SELECT <Expression>

The expression can be many things including the following:

1. Constant (such as a fixed number like 1).
2. Variable (such as @X which can be changed to any number).
3. Combination of constants or variables (such as 1+2 or @X +@Y).

However, whether you use SELECT <expression> or SELECT * FROM <Table>, you have to think in terms of sets.

How to Write a Simple SQL Query

Let us query the BookSimple database by refreshing the Databases node under Object Explorer. Right-click on BookSimple > New Query:

Write down the simplest SQL query – write and run the following script against the sample database:

-- Display 1
SELECT 1

Running the query (pressing F5) will show the following results:

So, SELECT 1 returns 1, but with an unnamed column (No column name).

1 is a constant. It will remain 1, so we expect the result to be 1 as well. However, there is one more important thing to understand: our output is converted into a set of 1 column and 1 row. Since we have not supplied any name to the column and there is no table involved (to get the name from), we are getting the 1 value for an unnamed column of an unnamed table (set).

The set-based language has automatically returned our supplied value to an unnamed column of an unnamed table having one row.

Let us name the column by modifying the script as follows:

-- Show value 1 for the column Number of an unnamed table (set)
SELECT 1 AS Number

Run the script to see the following result set this time:

Since this query is always returning the same value (number) which we input (supply), there is not much to discuss apart from understanding why the set-based thinking is vital to understand how queries work.

Calculation in Select Statement

Let us use the above case for quick calculations in SQL – we turn the single value into an expression.

For example, we want to quickly add two numbers 1000 and 200. We can simply write the following query without mentioning of any table using the SELECT statement only:

-- Adding two numbers 1000 and 200 
SELECT 1000+200 AS [Sum Of 1000 And 200]

The result is below:

Similarly, we can add, multiple, divide, and subtract numbers.

It is helpful to add two columns of a table containing numeric values. However, we can also use this approach to add two variables. Using variables is beneficial as we can add any two numbers by initializing those variables with the desired values. This is illustrated by the following script:

-- SQL script to add any two numbers
DECLARE @X INT, @Y INT, @Addition INT

SET @X=550 -- Initialise variable X with a value (number)
SET @Y=350 -- Initialise variable Y with a value (number)
 
SET @Addition=@X+@Y -- SUM X and Y

SELECT @X AS FirstNumber_X,@Y as SecondNumber_Y,@Addition as SumOfNumbers

Running the script shows us the following output:

We can save this query to reuse it any time to add any two numbers (by changing the values of @X and @Y). If we think of this in terms of a set, we may say that the output is returned as an unnamed table (set) with one row and the following three columns:

1. FirstNumber_X
2. SecondNumber_Y
3. SumOfNumbers

However, you can do more with the SELECT <expression> with a blank database (having no tables).

One More Data Calculation Example

Here we are going to use the GETDATE() function to retrieve the current date and the expected delivery date of an unknown order which is not yet a part of our table.

Assume we don’t have any order table yet, but we want to quickly calculate an expected product order delivery date. If we add any number to the GETDATE() function, it is going to tell us the date added with the supplied number of the days.

In other words, if we expect the order delivery date as two days after the order placement, we can calculate it by using SELECT with GETDATE()+2.

To see it in action, run the following SQL script:

-- Calculating order date and expected delivery date
SELECT GETDATE() AS [Order Date],GETDATE()+2 as [Expected Delivery Date]

The result set is as follows:

This time we could calculate the expected order delivery date directly using the SELECT statement with the GETDATE() function in the absence of any table.

However, if we had a table, we would have gotten the expected delivery date for all orders in that table.

Once again, if we analyze this output in terms of Set Logic, we have one unnamed table (set) with two columns and one row. SQL is a Set-based language which works very fast with sets, such as tables. If tables are not there, it treats the input values (to be processed) as unnamed sets.

In short, an SQL query requires a SELECT statement followed by an expression to do some number or date-based calculations even against a blank database (no tables).

Congratulations! You have learned the basics of SQL queries and wrote some simple queries with the help of SELECT statement against a sample database.

Stay in touch, as writing simple SQL queries against the database tables is yet to come.

Things to Learn How to Better Write SQL Queries

Now that you can write some basic SQL queries, try the following exercises:

1. Create a sample database BookSimple2.
2. Create a query to multiply any two numbers by defining them as variables.
3. Write a query to create an order date as the current date, the shipment date as two days after the order date, and the expected delivery date as two days after the shipment date.