Note:This introduction of SQL is completely for only Software Testers
Working with SUT (software under testing) Database:
In general, test automators are using MS-excel files for testdata reading and test results storing MS-excel files can allow cells formatting and charts preparation for the agile based project testing.But, some test automators can use corresponding SUT database for data reading and data storing or data writing.So, test automators need "SQL commands" to access database.
SQL (Structured Query Language)- SQL is developed by IBM which is universal language to access database developed in ORACLE,MYSQL,DB2,SQL SERVER,SYBASE,MS-ACCESS,BIGDATA and etc.
- SQL is used to communicate with a database.It is the standard language for relational database management systems(RDBMS).
- SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system.
- However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.
Client Server Architecture:
It is the most frequently used architecture model to implement the projects.In this model we will contain a main computer called as 'server',which is connected with multiple other computers called 'clients'.
Below diagram explains you the client server architecture:
The server will be used to store the data in an organized manner with the help of software such as SQL server,ORACLE,DB2,MYSQL and etc.Inorder to access aswellas manipulate the data on each client computer we will contain a GUI tool called as 'Application programme' or 'Application software'.They will be developed using technologies like VBScript, Java, Dot-net and etc.
A project is a combination of one front end and one back end database.It is the responsibility of developer to develop database on the server,design the application programme on the client and implementing the programming logic to back end database.It is the responsibility of a test engineer to perform an operation on the front end application and verify whether it is currently effecting the database back end or not. This process is called "Database Testing".
In simple terms database testing means that giving the data at the frontend application and checking whether that is effecting in backend or not w.r.t to modifications.
SQL server:
This database software was developed by the Microsoft organization like any other microsoft components SQL server can also be used only on windows OS.And currently we are using SQL server 2008R2(10.5) and 2012(11.0) versions.
Management Studio Tool:
Inorder to perform operations such as storing, retrieving and manipulating the data in SQL server we have to use this management tool. It contains a window called object explorer which can be used to perform the operations by following navigations by picking on the 'new Query' icon of the tool bar we will get a 'Query editor' window
using this window we can execute related SQL to perform the operation.
Components of SQL server:
Whatever version of SQL server we are using all will follow the same architecture as specified below:
While operating with SQL server we must use the following steps:- Establish a connection with the server.
- Create a location called database in the connected server.
- Create an object called 'table' inside the database to store actual data.
- Create and use all other objects based on the existing tables.
Connecting to the server:
We can connect to the SQL server in two ways, they are:
- Windows authentication: In this model we use a windows user account to establish a connection with the server. This user accounts will be created and controlled by system administrator.
- SQL server authentication: In this method we use a database user account to establish the connection. They will be created and controlled by the Database administrator(DBA).
Authentication: It is the process of validating the correctness of a user account.
Authorization: It is the process of providing permissions to a valid user.