What is Query in SQL?

SQL stands for Structured Query Language. It is used in computer programming to handle or manipulate databases. We use queries in SQL to handle databases. A query is more like a question or a request in simple terms. Suppose you have a query -- Please provide me with the Employee ID of all the employees working in the Accounts department. Or a query like -- how many seats are booked for the show? Hence, we are querying or requesting for some information.

In general terms, a query in SQL is a request to databases to fetch (or retrieve) the information. We use a common language - SQL, to query our databases. It is used whenever companies have a ton of data that they want to manipulate. If you store your data in a relational database, then you are welcome to use SQL as well!

Let us get a short overview of SQL before learning more about queries.

Short Overview Of SQL

SQL stands for Structured Query Language. It is one of the primary query languages used for data stream processing and relational database management. SQL allows us to access and manipulate databases. SQL has a wide range of applications in today’s world.

Let us take a look at SQL's imperative uses for data collection, storing and manipulation, etc. --

• SQL can be used to execute(or run) queries against a database.
• SQL can insert, update, delete or retrieve data from databases.
• SQL can create new tables or stored procedures, views, etc.
• SQL can set permissions on tables, procedures, and views.

Why Use SQL Query?

SQL Query is used to query or retrieve information from the databases. We can perform the following operations using the SQL query --

• Use SQL query to create a new database and insert data into the database
• Use the SQL query to retrieve (or fetch) data from the database. Also, to modify or update the existing data in the database.
• To delete or drop the data or table from the database using the SQL query . Also, we can create a new table after that,
• Using the SQL query to set permissions for the tables, views and procedures. Also, to create functions, views and stored procedures.

How To Write SQL Query?

The basis of a query in SQL Server is the SELECT sentence which allows selecting the data to be displayed.

An SQL SELECT statement retrieves records from a database table according to clauses (for example, FROM and WHERE) that specify the criteria based on which our data will be selected. The syntax for the SQL SELECT statement is:

In the above SQL statement:

• The SELECT clause specifies one or more columns to be retrieved from the database. To specify multiple columns, we should use a comma and a space between the column names. However, if we want to retrieve all columns, we can use the wild card * (an asterisk) SELECT * FROM ....
• The FROM clause specifies one or more tables that are to be queried. We can use a comma and space between the table names while specifying multiple tables, for example, FROM Names, Addresses, Phone_Numbers, where the Names, Addresses, etc. are the table names.
• The WHERE clause selects only the rows in which the specified column contains the mentioned value. WHERE allows you to filter a query to be more specific. The value is generally enclosed in single quotes (for example, WHERE color = 'teal').
• The semicolon (;) is the statement terminator. However, if you have a single-line SQL query statement, you may skip the semicolon (;). But it is mandatory if you have multi-lines of the query. In general, it is a better practice to include the semicolon after every terminating statement of the SQL query.

In the above explanation and syntax, we used capitals for every SQL clause. However, please note that SQL is not case sensitive. That is, SELECT is the same as select, FROM is the same as from, etc.

SQL Queries Examples

Let us take a few examples to understand more about SQL Queries. Suppose we have a table named Customers. Let us perform some SQL operations to retrieve and manipulate data from our table.

Example 1 - The SELECT Statement

To get all the columns from our SQL from the "Customers" table, we can use the following SQL statement:

After executing the above query, we will get some output as below:

Output:

Hence, using * (asterisk) gives us the whole table as output.

Now, suppose we want to get only the customer's first name, last name and the country they live in as the output. For that, we will execute the below SQL query:

Executing the above query will give us the following output:

Output:

In the above output, you can see that we only got the first name, last name, and country after executing the above query. This is because we only specified those column names in our select query.

Let us also take an example where we reverse the order of our table names and check whether we get the output in the same order or not.

Output:

Hence, we can conclude that reversing the order of the table names in the select statement also reverses the order in the output.

Example 2 - The FROM Clause

Suppose we have another table Orders. Firstly, let us see the contents of the Orders table.

Code:

Output:

So, the above given is the content of the Orders table. Let us perform some operations combining both the Customers and Orders tables to learn more about the operation of the FROM clause.

Code:

Output:

In the above output, you can see that we tried to select the first name, last name and item from both tables, resulting in above output.

Also, we have used 'as', which is used as an alias. The AS command is used to rename a column or table with an alias. An alias only exists for the duration of the query. Hence, after this query, the column names/table names will be the same as the original.

One thing to notice in both tables is that both of them have a common column name, which is the customer ID column. So, for displaying the customerID, we need to explicitly mention the table name followed by a dot (.) operator and then mention the column name. Let us see this through an example.

Code:

Output:

Hence, in the above code example, after explicitly mentioning the customer ID with its table's name, we can get the above output. If we had not mentioned Customers.customer_id, then we would get an error of Error: ambiguous column name: customer_id.

Let us also see an example of using DISTINCT. The SELECT DISTINCT statement is used to return only distinct or different values. Inside a table, a column sometimes contains many duplicate values, and you often only want to list the different distinct values. So let us look at its usage.

Code:

Output:

In the above output, we only got the different values of the item and amount. We did not get any values that were duplicated throughout.

Example 3 - The WHERE Clause

Finally, let us look more into the usage of the where clause. As you already know that the WHERE clause is used to filter records. It is used to extract only those records that fulfil a specified condition.

Code:

Output:

In the above output, you can see that we filtered our above records to only the records where the amount is greater than 400. Hence, you can see that we have got only those records as output where the amount is greater than 400.

Let us write some more queries using the WHERE clause by using AND, OR, and NOT.

Code:

Output:

In the above example, we have used the AND operator. The AND operator displays a record if all the conditions separated by AND are TRUE.

Code:

Output:

In the above example, we have only got the customers who have purchased the items by using the comparison operator '='. We compared the customer IDs with their Orders made.

Let us see the last example, where we delete some data from our table.

Code:

Output:

The DELETE statement is used to delete existing records in a table. In the above example, all the records with first_name as 'John' got deleted.