SQL or structured query language lend an extra power to the testers for testing the data base effectively.
What is a Structured Query Language (SQL)?
– Structured query language is needed whenever it is required to verify the work flow in the automated systems coupled to data bases.
– It becomes the responsibility of the tester to perform effective back end testing by writing proper SQL queries and executing them.
– Usually, many of the testers work without knowledge of structured query language.
– The first and only available automated SQL/PL code testing tool is the quest code tester for oracle.
– This tool is actually a component of the toad development suite developed for oracle and was created by Steven Feuerstein – world’s most prominent oracle expert.
– This tool proves to be quite an effective and reliable means for performing SQL/PL code tests and helps you in reducing the number of functional errors and costly bugs.
– It assists you in creating detailed regression tests which can be run along with the automation script as its part and can be used to verify the corrections made to the code.
– Using this tool you can make changes easily with confidence even if you are not so familiar with the code.
– This tool also assists you in the deployment of the high quality code production.
If you need to write and execute the SQL queries yourself, you require a basic knowledge about SQL.
Parts of Structured Query Language
Structured query language consists of two distinct languages namely DDL and DML.
1. DDL or Data Definition Language: It provides the statements for defining the schema or the structure of the data base. Following are some examples:
a) CREATE – for creating objects in the data base.
b) ALTER – for altering the structure of the data base.
c) TRUNCATE – for removing all the records from a particular file of the data base including all the spaces.
d) DROP – for deleting objects from the data base.
e) RENAME – for renaming an object.
f) COMMENT – for adding comments in the data dictionary.
2. DML or Data Manipulation Language: It provides the statements for the management of the data within the schema. Following are some examples:
a) SELECT – for retrieving the data from the data base.
b) INSERT – for inserting the data values in to the table.
c) UPDATE – for updating the existing data values of a data base.
d) MERGE – for merging two or more than two tables in to one.
e) DELETE – for deleting all the records from a particular table including spaces.
f) EXPLAIN PLAN – for explaining the access path that leads to data.
g) CALL – for calling a SQL/ PL or java sub routine.
h) LOCK TABLE – for controlling the concurrency.
– It does not matter whether you are developing a whole new application from scratch or prototyping or learning SQL; in all cases you need test data for running tests.
– To test the performance of a data base application you need to have enough data so that you can expose the potential problems with its performance.
– Using real data is always preferable whenever possible but if it is not there enough hypothetical data needs to be generated. This becomes easy when you start from scratch.
– Using the above mentioned statements you can create new tables or use the existing tables and views no matter whether the data base is empty or newly created.
– It is quite probable to have many – to – one relationship in the data and so you should know how to populate a child table.
– Also, you should keep in mind that the order in which the rows occur in the table affects the performance of all the queries that you run on it.
– Be careful while generating data since it needs to have a realistic physical order.