A question we are frequently asked by newbie students is why we need to learn SQL. We hope this blog will provide an insight into the question.
As testers first and teachers next, we rely on our practical experience and do our best to teach skills that we have used day in and day out in our testing career. Hence, Unix, Windows and SQL are some of the topics that we have included in the course because of this approach.
First let us talk about data and forms:
For many things in our real life, we fill application forms. The forms collect data like Social Security Number or Social Insurance Number, First Name, Last Name, Address, Gender, Home Phone, Mobile Phone, Email Id etc.
Eg Bank Application form, School application forms, Insurance forms etc.
The content of these forms are different, i.e. based on the context, the forms change.
So we see how in our everyday use we (any customer) fills up a lot of forms. Some data are mandatory, if the customer does not fill it, the form is returned. After the customer fills the data, the data is stored in a file. Some data might need to be verified, so the customer is told to come back in 3-5 working days. And in the 3-5 working days, the customer gets the service he requested.
What happens to these Forms? These are stored and processed. By processing, the details are checked to see if the service requested is available etc. So Business rules are applied. Some rules might give discounts based on Gender, Age etc. Some rules might dictate additional information from the customer.
This is exactly what a Software Program/Application does! Instead of a human, a computer and webpages are used.
An application (any) is all about data collection, storage, modification based on business rules, presentation of this data based on rules or filters in reports and sometimes deletion of this data.
In a nutshell, an application is basically CRUD – Create, Retrieve, Update, Delete of data. So data and databases are the key.
Let us consider the Yahoo application.
As a System Tester, let us say we are checking the Create Account part of the Yahoo application.
The tests will include:
1. creation of new accounts
2. viewing the account info
3. modification/update of some of the details entered during the Create Account eg. Lastname, password etc
4. deletion of the account.
Let us say: you created an Account successfully. You get the success message and you are able to login. So you mark this test case as Passed. (The tables in the database have not been checked, so there is no real guarantee all the data has been correctly updated in the tables.)
Next you go to the View the account info test case – you find the Date Of Birth is displayed incorrectly (dd/mm/yyyy instead of mm/dd/yyyy), the Firstname and Lastname are interchanged, the city is missing etc. Now comes the confusion – which test failed – the Create or the View? You log a defect against View the account info but the developer comes back and tells you it is against the Create Account info! This is the first reason you need to know SQL, to identify which test case really failed.
2. SQL queries are also needed when you need to create test data, sometimes pre-populate the database tables before testing commences.
3. Thirdly, when you need a production cut of data, the System Admin will ask you what data you need from which tables, what dates etc.
So a basic understanding of databases and SQL queries is a must for all System Testers. The Development team and System Admin will help you with advanced queries, but basics are a must.
The Software Design Document will contain details of tables updated, the Primary Key-Foreign key relationships etc. So a review of the SDD when creating test cases gives a deeper understanding of the application.
This is why basic SQL is a part of our regular QA course. We also run a separate SQL course for those interested in enhancing their SQL skills.
http://www.w3schools.com/sql/ is a good reference for SQL.
