Basic Queries in SQL a tester must know - I

Making SQL query of database is must for a tester to know. In this blog, I will explain some basic query of SQL must for a tester to know. In later posts in this series, we will discuss more on nested queries, joins,working with conditional loops in sql and other useful concepts from a tester point of view. The post is prepared using SQL Server, and there might be slight change in the query in Oracle or other database servers.

To start with, let us assume, there is a table name Organisation with following details as shown below:

Let us know some basic queries to fetch information from a single table.

•     Query to get all information from the table
•    Select * from Organisation  
•   Syntax: Select * from [tableName]

•     Query to get particular column(s) information from table
•  Select Org_Name, Org_Code from Organisation
• Syntax : Select [Col_Name1], [Col_Name2] from [tableName]

• ·  Using Where Clause to get subset of information
•    Select * from Organisation where Org_Type = ‘Auto’
• Syntax: Select * from [tableName] where [Col_Name1] = <expected value>                                                      
•    Using Where Clause to get subset based on multiple condition in where clause
•      Select * from Organisation where Org_Type = ‘Auto’ Or Org_Code = 201
• Syntax: Select * from [tableName] where [Col_Name1] = <expected value> Or [Col_Name2] = <expected value> 

•    Using alias to get more readable and presentable form
•     Select Org_Name as Organisation Name from Organisation
• Syntax: Select [Column Name] as AliasName from [TableName]
•    Using Distinct to get distinct records of a table
•    Select distinct Org_Type from Organisation
• Syntax: Select distinct [Column Name] from [TableName]
•     To get multiple distinct combination use:
•   Select distinct Org_Type,Org_Code from Organisation
• Syntax: Select distinct [Column Name] from [TableName] 

•     Using Order by to sort the data in their ascending or descending order:
•   Select * from Organisation Order by Org_Code desc
• Syntax: Select * from [table] Order by [column name] desc/asc
•   Select * from Organisation where Org_Code>100 order by Org_Name desc

•  Finding rows with value as null for a column
•   Select * from Organisation where Org_type is null
•  Syntax: Select * from [table] where [column name]  is null

•        Finding rows with value as not null for a column
•         Select * from Organisation where Org_type is not null
•       Syntax: Select * from [table] where [column name]  is not null

•   Finding Count of records matching the SQL query
•    Select Count(1) from Organisation where Org_Type = ‘Auto’ Or Org_Code = 201
• Using count(1) makes the query more faster compared to count(*)

•    The LIKE operator is used to search for a specified pattern in a column.
• Select * from Organisation where Org_Name like ‘%test%’.
• In the example % acts as wildcard to search for pattern which test displayed anywhere in the Org_Name.
• Other examples include:
• Select * from Organisation Where Org_Name  like '_ estOrg2'; (wildcard for one character)
• Select * from Organisation Where OrgName  like '[tfd]%’; (wildcard for all org_name starting with t, f, or d)

Process Group, Knowledge Area and responsibilities of Project Management

Project Management skills become necessary once you grow into supervisor role and are useful for the success of the project and keeping things in track. In this series, we will cover on questions and solutions for the questions learned through various resources on the internet and knowledge gained during IT experience and implementing the same in our projects.

Question 1: What are the major areas of responsibility of a project Manager?

Answer: The major areas of responsibilities of a project manager are:

•  Identify the requirements of the project – We figure out what needs to be done to make the project a success. This includes identifying and pulling each and every constraint that needs care to be taken care of.

• Establish objectives that can be achieved – Planning the goals that the team needs to work on to make the project a success.

• Balance scope, time and cost – For a successful project, we need to complete the project within schedule and cost expected with the scope of the project completed.

•  Satisfying everyone’s need – There needs to be proper communication between and with different stakeholders to keep them on the same page.

Question 2: What are the major process groups for project Management?

Answer: The major process groups  for project management are:

• Initiating – In this phase, we identify what are the high level goals of the project?

• Planning – In this phase, we plan for how we will do the things in project and define high level milestones for the project.

• Executing - In this phase, we do the actual work that was planned during initiating and planning phase.

• Monitoring and Controlling – In this phase, we define how to track the work, and in case of deviation in milestones defined and actual progress, we need to perform control activities to bring the project back into track

• Closing – This phase includes completion of activities including deliverables, paperwork and finally getting paid once the exit criteria is completed and agreed.

Question 3: What are the different knowledge areas and their definition?

Answer: The different knowledge areas that need to be taken care during knowledge management are as follows:

• Risk – Planning for things that could happen, which can be either positive or negative, and dealing with them when they happen
• Quality – This is to ensure product build is correct and satisfies the requirement of customer.
• Scope – Defining the work that needs to be done in the project.
• Procurement –Defining contracts with different stakeholder working in the project.
• Communication – Understanding stakeholders and different roles of stakeholders and making proper communication with them to ensure project growth.
• Time – Making estimates of how long it will take the project to complete and make sure the project adhers to the timeline.
• Integration – Making everyone works towards the goal and dealing with the changes if any arising in the scope and schedule of project.
•  Cost – Budgeting project cost and monitoring the money spend in the project.
•  Staffing – This is getting the required staff, infrastructure for getting the work done. 

KNOWLEDGE AREAS

Understanding Agile Methodology and principles

In 2001, At a summit seventeen practitioners of different software methodologies met together and created the agile manifesto. Principles of Agile Manifesto are implemented in various projects for agile development.

The Manifesto for agile development states developing keeping the following values as focus:

·         Individuals and interactions over processes and tools

·         Working software over comprehensive documentation

·         Customer collaboration over contract negotiation

·         Responding to change over following a plan

Agile Methodology thus focuses on close interaction between resources and customers to create working software and responding to changes in software based on feedback between the teams. 

If we go back to Iterative and Incremental model, we can say Agile Methodology as a subset of IID Model. Software Testers works in a collaborative model as a part of Agile teams providing necessary feedback together with reporting defects in the development.

Agile as per dictionary definition means:  able to move quickly and easily and able to think and understand quickly. The Definition defines agile methodology in a nice manner. Using Agile in projects, we can move in quick manner thinking over the problem and understanding it quickly and easily by working in a collaborative manner.

In terms of Software testing, agile methodology is different from previously used methodology, e.g. Waterfall and V-Model, as it allows tester a role early in the development in a collaborative manner, thus uncovering the issues from early analysis phase. Also Testers are integral part of agile teams instead of independent teams. The Collaborative approach separates agile from other SDLC Models, and helps better communication between various stakeholders including customer, managers, developers, testers, and analysts bringing responsiveness to changing requirements and a more efficient product developed since all the stakeholders works as a team with better understanding of the requirements.

Key Principles of Agile Manifesto:

1.  Satisfying the customer through early and continuous delivery of valuable software.

2.  Agile processes harness change for the customer competitive advantage.

3.  Delivering working software frequently, from a couple of weeks to a couple of months, with a preference to the shorter timescale.

4.  Business people and developers must work together daily throughout the project.

5.  Build projects around motivated individuals. Give them the environment and support they need, and trust them to get the job done.

6.  The most efficient and effective method of conveying information to and within a development team is face-to-face conversation.

7.  Working software is the primary measure of progress.

8.  Agile processes promote sustainable development. The sponsors, developers, and users should be able to maintain a constant pace indefinitely.

9.  Continuous attention to technical excellence and good design enhances agility.

10. Simplicity–the art of maximizing the amount of work not done–is essential.

11. The best architectures, requirements, and designs emerge from self-organizing teams.

12. At regular intervals, the team reflects on how to become more effective, then tunes and adjusts its behavior accordingly.

Reference:  http://agilemanifesto.org/principles.html

Where to Use and Where not to use Agile?

• In case, most of the project in the organization is handled through traditional development Model, it is better to start with a smaller project. The reason we cannot predict the success or failure of the agile is the collaborative nature of the project and this includes developers, management, Customers, and testers. The traditional models do not allow regular interactions between the stakeholders. E.g. we get the requirement from the customers, based on requirements the analyst prepares the design, developers code the design and testers test the code. Thus each of the groups works as individual units with collaboration missing which is a must for Agile.

• Since the individual as well as customer had worked in the traditional model for a longer part of their career, it becomes difficult to switch to agile methodologies, where we need to work collaboratively. So to understand the approach and mindset for Agile methodologies before working on larger project with large number of resources and stakeholder.

• Agile Methodologies should be used in project whose business severity is neither too high nor too low, so that proper importance is provided to the project.

• Agile should not be used on Projects where large documentation is required e.g. Legal or Compliance Projects.