Introduction to SQL

Now when we understood the concept of Data, Database & DBMS, an important question arise: Can a database management system decide on its own what kind of action it needs to perform? Well, the answer is NO. Just like any other computer software, a database management system needs a set of commands which can help it decide the nature of the task, This set of commands is provided by a computer language, which is the SQL.

Before we get started with installing SQL and writing commands to shape databases, we need to find the answers to a few things:

• What is SQL?
• Why use SQL?

At the very end, we will take a look at how this tutorial is beneficial to you.

 

What is SQL?

Developed in the early 1970s by Donald  D. Chamberlin and Raymond F. Boyce at IBM, SQL stands for Structured Query Language. At first, it was called SEQUEL, a name which is still used as an alternative pronunciation for SQL but was later renamed to just SQL.

SQL is a standard computer language for relational database management and data manipulation. SQL is used to query, insert, update and modify data. You can think of SQL as a medium of communication between the user and the DBMS.

So, in short, SQL is a programming language that we use to talk with and access our database.

 

Here we came across a new term, SQL Queries, what is it basically, let’s take a look.

 

What is the SQL Query?

To define a SQL Query, we first need to understand what is query? A  query can be defined as a request for data from the database through DBMS. A query can be considered as an instruction send to DBMS to retrieve a set of data based on a criteria. Such a query can be developed using SQL and is called a SQL Query.

A simple example of an SQL query would be: Select * from Table. After looking at this query you can easily figure out what it is trying to do. It simply says that, select all the data (represented by *) from a Table. Let us now understand different parts of SQL

SQL code is divided into four main categories:

• Clauses: The SELECT statement is the omnipresent part of the statements to perform queries. It is further divided into clauses, which include SELECT, FROM, WHERE and ORDER BY. 
• Data Manipulation Language (DML): Commands which are used to manipulate the data in a database, like add, update or delete data. It comes under the Data Manipulation Language (DML). DML consists consist of statements like SELECT, INSERT, DELETE, and UPDATE along with BEGIN TRANSACTION, SAVEPOINT, COMMIT and ROLLBACK as some of the control statements.
• Data Definition Language (DDL): To manage the tables and index structures, Data Definition Language (DDL) is used. The statements DDL includes are mainly CREATE, ALTER, TRUNCATE and DROP.
• Data Control Language (DCL): The GRANT and REVOKE statements are two main statements used to assign and revoke database rights and permissions. These commands make up the Data Control Language (DCL).

We just got a bit into SQL Queries. Later on, in this series of tutorial, we will be dealing with SQL Queries with examples that will help you understand how these queries work and how to use them.

For a bit of understanding, I created a sample table and we will run three queries on it. This sample table consists of first name and City of multiple people. The task that we want to achieve with the queries will be to

• Retrieve all the data:

 

• Retrieve the data from the table of person with name Vishu 

 

• Retrieve the data where the city is Delhi:

 

These three operations are some of the most basic data retrieval queries. There are a whole lot of such queries present in SQL. Don’t worry if you don’t understand anything about them now. We will learn about the queries later on, at present it is only to show you what queries look like.

Let’s take a look at one basic query to see how the implementation of an SQL Query works. The query is discussed in detail in the article, SQL Database Commands.

So now, let’s move on to the second question, which is:

 

Why use SQL?

SQL is widely used and quite popular language while working with databases. SQL is used in businesses quite often, to alter, retrieve and manipulate existing tabled data. The major advantage of SQL is that it  is quite easy and has straightforward syntax with ease of use. But what are the various advantages of using SQL, let’s take a look at them.

Advantages:

• SQL queries can retrieve a large amount of data from a database in a fast and efficient fashion quite easily compared to other query languages. This corresponds to the high speed of SQL. 
• Apart from the high speed, SQL also follows well-defined standards, i.e. SQL database use standard which is long-established and being adopted by ANSI & ISO. SQL became a standard of the American National Standards Institute (ANSI) in 1986 and International Organization of Standardization (ISO) in 1987.
• One of the biggest advantages of using SQL is SQL allows one to manage a database without writing a considerable amount of code.
• With the emergence of Object-Oriented DBMS (ORDBMS), the capability to store objects has been extended to relational databases, hence leading to the emergence of ORDBMS and modifying the older notion that SQL databases are synonym with relational databases.

 

Why is this tutorial beneficial to a Software Tester?

Before we start with the tutorial, lets understand why it is important for a Software Tester to have SQL skills. As of now, we have discussed SQL and it’s benefits but there must be some reason for a Software Tester to take this tutorial. 

Need of SQL for a Tester

A software may or may not need SQL skills but with certain projects it becomes necessary. Projects such as data conversion or migration, data upgrade or data warehousing may require some serious SQL skills. The major need for a tester of SQL asks him to have knowledge of the following things:

• Understanding the data types used in the database.
• Write simple queries such as select etc.
• Understand the connection between database tables, keys, and indices.
• Write some complex queries.

But how do these skills help a software tester? Let’s understand this based on examples:

• One of the most basic operations that need testing is the storage of data obtained from the frontend, like storing data filled in a form, etc. One needs to verify whether the data has been stored in the backend and for that the tester can conduct the test using the select query. Apart from this, the tester can verify whether the correct type of data is stored or not.
• One more such examples could be the reverse of this, i.e if data values displayed on the frontend are changed from the backend is the change displayed on the frontend? To understand a thing of an e-commerce website like Amazon or Flipkart, if the price is changed at the backend like for a headphone, is the change displayed on the frontend.

There are numerous examples for the same and such things should be verified at the time of testing and hence the knowledge of SQL becomes necessary.

 

What does this tutorial provide

So, as we now know the basics, advantages of SQL, and why a Software Tester require it, but what will we learn from this tutorial and why is it helpful? Well by the end of this tutorial, you’ll get a hold on a variety of things like:

• Installation of SQL.
• How to create databases.
• How to work with the database, like, adding and retrieving data.
• You’ll also learn about various complex queries which include the aggregate function, string function, date and time, sorting results and logic operators.
• You’ll also be comfortable with interrelated databases which are just chunk of complex Data-structures and will learn about the join statements.

With all this knowledge you’ll be able to implement your own databases and carry out various functions on them. SQL backends are quite easy to understand and implement. So, by completing this tutorial, you’ll get a fair insight into how to understand a SQL backend. You’ll be able to work backends quite easily making it easy for you to work on any website or app.

Note: We will discuss a few more important topics related to SQL where we will again get into concepts like DML, DDL, etc. in much detail.

We will move on to the Installation of SQL in our next tutorial.