how to work with Multiple tables in SQL for tester– Understanding joins

We have gained knowledge on basic queries in SQL working with a single table in previous articles. Let us extend our knowledge working with multiple tables in SQL. Below are the tables, we will use in our examples.

This post will mainly discuss on concept of joins in SQL.In the next post, we will go further and discuss on nested queries, union, and other useful concepts on SQL.

Problem 1: From the above tables, provide Org_Name for each of the users in User Table?

Answer: First of all, we need to identify which information we need to extract from above table. We need Org_Name from Organisation table and Username from User Table.

Second Point is to identify the common attribute/column in two tables based on which we will create the relation between two tables. As in above tables, Org_Id is the foreign Key in table User which is mapped with Id in the Organisation table and is used to create a relation between the two tables.

We can use joins to get data the two tables. JOIN is used to combine rows from two or more tables, based on a common field between them. In this problem, we will use join or inner join or a equi – join to extract the required information

•  INNER JOIN: Returns all rows when there is at least one match in BOTH tables. An Inner Join is same as Join or equi-join.

•  Since we have make a match between Organisation and user table on Org_Id, we will create inner join or equi-join or simply join as shown in example below

• Syntax - Join : Select * from Organisation a join User b on a.Id = b.Org_Id

• Syntax - InnerJoin : Select * from Organisation a innerjoin User b on a.Id = b.Org_Id

• Syntax – Equi-Join : Select * from Organisation a,User b where a.Id = b.Org_Id

Join and Inner Join are same thing and fetches the same results as Equi-Join between two tables.

Other than Inner Join or equi-Join, Other examples of joins are following:

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

• Syntax- Left Join : Select * from Organisation a LEFT join User b on a.Id = b.Org_Id

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

• Syntax- Right Join : Select * from Organisation a Right join User b on a.Id = b.Org_Id

• FULL JOIN: Return combined result of both LEFT and RIGHT joins.

• Syntax- Full Join : Select * from Organisation a full outer join User b on a.Id = b.Org_Id

In above problem, we will use an inner join and can use any syntax from Join, inner-join or equi-join.Since we require Org_Name and User_Name in Output, Instead of fetching all information using Select *, we will extract Org_Name from Organisation table and similarly User_Name from User table. Also we can use alias to giving meaningful name to column. The syntax will be :

Select a.Org_Name as OrganisationName ,b.User_Name as User from Organisation a join User b on a.Id = b.Org_Id

Problem 2: In the above tables, provide Org_Name, Salary and Designation for each of the user in User Table?

Answer: In this example we need to create multiple join between tables. The query will be as follows. Try to understand the query with the concept explained in previous table.

Select us.User_Name, org.Org_Name, us.Salary, des.staff_role from User us join Organisation org on us.Org_Id =org.Id join User_Designation usd on us.Id = usd.User_Id join Designation des on usd.Designation_Id = des.Id

Basic of SQL a tester must know – II

In the first post on SQL, We discuss on very basic queries in SQL , In this post we will discuss further from where we left in post - 1

The post is prepared using SQL Server, and there might be slight change in the query in Oracle or other database servers. We will cover these wherever applicable. Let us continue with table name Organisation with following details as shown below:

Let us discuss on more queries from where we left in the previous article.

•   Query to get top records from the table

•    Select Top 2 from Organisation

•    Syntax: Select top number|Percentage from [tableName].

•  The above query is specific to SQL Server, The syntax for the same in MySQL and Oracle is as follows:

•    Select * from Organisation where rownum<=2   --- Oracle

•    Select * from Organisation limit 2   --- MySQL 

• 
  

• Query to search for multiple values in a column in SQL – Using In Operator or between Operator, we can search for multiple values for a specific column

•   Select * from Organisation where Org_Name in (testorg1,testOrg3)

•  Syntax: Select * from [tableName] where columnName in (value1,value2) 

•  Query to search for multiple values in a Column in SQL – Using between Operator. The below example will list all Organisation with values of Org_Code between 10 and 2000.

•   Select * from Organisation where Org_Code between 10 and 2000

• Syntax: Select * from [tableName] where columnName between  value1 and value2

• Query to search for multiple values in a Column in SQL – Similar to Between , We can use not Between Operator. The below example will list all Organisation with values of Org_Code excluding values between 10 and 2000.

• Select * from Organisation where Org_Code not between 10 and 2000
•  Syntax: Select * from [tableName] where column_Name  not between  value1 and value2

•  Query to search for multiple values in a Column in SQL – Similar to In and Between, We can use LIKE operator together with Wild cards to search for a specified pattern in a column.

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

• Using Group By with aggregate functions can help us to extract useful information from the tables. For e.g In above Organisation table, we need to know how many organizations’ belongs to Organisation Type ‘Auto’ and how many to ‘Manual’. We can use group by Clause together with aggregate function as shown below:

•     Select Org_Type , count(*) as CntOrg from Organisation group by Org_Type.
•       The best practice is to provide Column names used in Group By Clause in Select Clause also to generate meaningful information.
•       We can use multiple columns in Group by Clause, e.g : Select Org_Type, Is_Active, count(*) from Organisation where Org_Type is not null group by Org_Type,Is_Active.

• We can use having clause to search for required in SQL for aggregate function similar to Where clause for a column in table: 

• e.g : Select Org_Type, count(1) as Org_Cnt from Organisation group by Org_Type having Org_Cnt>2.
• 
  

•   Different Aggregate function used in SQL  are as follows:

• Sum() - returns the total sum of a numeric column.

• Avg() - returns the average of a numeric column.

• Count() - return the count of rows satisfying condition

• Max() - returns them ax value of numeric column

• Min() - returns the minimum value of a numeric column

• The syntax for aggregate function is :  Select aggregate_Function(column_Name) from Table Name  e.g: Select max(Org_Code) from Organisation.

In the first two posts in SQL, we are done with the basic concept of SQL useful if we need to extract information from a single table. In the next post, we will discuss on string functions, joins and working with multiple tables.

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.

Understand Iterative and Incremental Development Model

In Iterative and Incremental Model, the whole requirement is divided into various sub–requirements or builds. Each build in itself follows waterfall model, i.e. each build passes through requirement, design, implementation and coding. The need for incremental and iterative model arise and is very useful in scenario where requirement changes frequently or we are working on an idea and the detailed requirement may not be clear at the start of development but a high level vision of the end product is available to us.

As the name suggest, this model will consider both iterative and incremental development. Before we discuss this in detail, let us know what iterative and incremental means. Various parts of the system are developed at different time and are integrated into the existing developed work once they are completed. And iterative strategy assigns time for rework and improvement in the part of system developed, thus continuously working in iterations to rework and improve the system adding more features in an iterative manner.

IID Model

To start with this model, we divide the project in a number of modules or stories. Each module/story represents functionality or use case in the product. Stories should be created or planned in such a manner that each story covers a feature of product, has set of entry and acceptance criteria and needs to be of shorter span of time, may be few days to few weeks. 

This is the analysis phase where we have analyzed the various stories from the requirement based on the features expected from the product. With each story having its own story means features  which will have a short span of time to complete.

Now once we have analyzed all the stories in the requirement, how do we react to stories, Do we start working on all stories together in a bang bang manner or do we need to priority to the stories, We will start with the story with most risk and complexity, and before this will complete on other smaller stories on which the most complex and risk story is developed. Once the complex piece is selected, we work on following a small waterfall on the story, 

We start with analysis, then design, coding and then testing the code, but with regular feedback at each of the steps.

The first piece will take a longer time compared to next stories but will provide useful immense information on the feasibility of the project, Knowledge to team on the product and possible bottlenecks in the development of project. It is strictly advised not to set deadlines for the initial few pieces as it will act as guidance to rest of stories on how long it will take to develop another story in the project.

Once we develop another story, we add it to existing product thus increment the functionality added to existing product and at the same time, we do iteration to improve and rework to existing developed piece based on defects, feedback from various stakeholder. 

All the pieces are developed, integrated with existing developed functionality, once the piece is fitted with rest of the developed pieces, we can have feedback and testing on the incremented product so that we can do rework and review the product. The development happens in an incremental and iterative manner unless the completed product is developed.

Where to Use IID Model

• Major functionality and goal of development are well defined, but features and enhancement mayevolve with time.

• IID is very useful in case of development with new technologies to validate a prototype filling preparing the complete.

• High Risk features which may change in future.

Positives in the Model

• An initial working product is developed in the early stages quickly.

• Developing the initial product early helps in getting feedback and defect Identification early in the lifecycle.

• We can work on different stories/pieces at the same time.

• Continuously improving the product by rework and review is possible based on iterative nature of development.

• Risks are identified in a better manner and can be implemented in the remaining project development.

Negatives in the model

• The timelines of the project are not fixed and can change drastically based on requirement change.

• Sometimes incorrect implementation of model can lead of frequent requirement change and estimation of project.

• Initial development which helps in identifying risks in the system required experienced risk analysts as incorrect risk analysis during initial phase may lead to failure of development.

• Management complexity is higher.

Understanding Waterfall Model from a tester's perspective

      In this article, I will describe the waterfall model and understand it in a better way myself too.

      Waterfall Model is a sequential process model used in SDLC in which sequence of activities flows from top to bottom in a linear manner with next activity starting once previous activity is completed. Normally, The Output of the first activity acts as an input to the subsequent phase in waterfall model, also changes in the analysis can be traced back to previous phases if any change is identified in later phases of SDLC.

      Waterfall model can also be described as a document driven approach as communication depends on the quality and quantity of documentation.

This was first of the models used in Software Engineering provided by Winston W. Royce in an article published in 1970. Since the Software Industry evolved a lot in the past 40 years, new models and methodologies have also evolved which covers the shortcoming of Traditional waterfall model, but some or other features of waterfall Model are used in various other software methodologies. Therefore it is useful to understand the waterfall model before discussing other models.

Different Phases in Waterfall Model are described in the section below:

a. Requirement Analysis: 

In this phase, the requirements are understood from the client and analyzed. To Understand requirement , customer are consulted, the feasibility of the requirements are understood and based on all the inputs, business requirement specification is developed and is signed off by client as an agreement between client and supplier on the agreed functionality.

b. System Design: 

Before starting with system design, all the requirements are freeze in the requirement phase. The requirement documents acts as an input to this phase and based on this a system design is prepared in this phase.

c. Architecture Design:

  The system design is analysed and an architecture design dividing requirement into module based on functionality is developed. The input for this phase is the system requirement documents and output helps in preparation of low level design for the projects.

d. Low Level Design: 

In this phase, low level design at module level is developed which acts as input to coding phase. The high level design or Architecture design acts as input to this phase.

e. Coding: 

The Low level design is implemented to code. All the code is merged together and various modules are integrated together to form a system as defined in System design.

f. Testing:  

When Coding is completed, the code is passed on for testing and various testing types are conducted on the code and the defects are passed on to development team . Based on the defect impact, there needs to be changes implemented at code level or can impact the previous levels. The more the impacted phases, more will be the cost of fix.

g. Operations and Maintenance:

 Once testing is completed, the system goes to Production. Support and maintenance team helps in maintenance of the product.

Waterfall Model

Where to Use Waterfall Model: 

Waterfall model is most useful to be followed in projects where:

a.  Requirement is very well documented and probability of requirement changes is low.

b.  Expertise of resources is available to ensure each phase is completed with minimum gaps and rework.

c.  The time span of the project is short.

d.  The Project is stable with minimal requirement changes 

 Waterfall Model Positives:

a. Easy to understand and manage as the milestones in project are defined and mostly at a time only one phase is active, so needs to manage one phase only at times.

b. Processes are well defined.

c. Documentation is done properly for each of the stages in the life cycle.

d. Developing a culture of analysis before coding as there is thorough analysis from requirement to design to coding.

  

Waterfall Model Negatives:

a. The major negative of the model is the risk and uncertainty associated with the model which can arise due to below reason:
        a. Requirement change at a later stage.
        b. Since the application is not developed till a major part of life cycle, Actual feedback on functionality happens late in the life cycle

        c. The cost of fixes is high if uncovered late in the life cycle

b. Most of the defects are uncovered in system testing phase which results in a lot of rework and at times changes in the design, which can impact the timeline and acts as major threat to the success of the project.

c. At times, the testing time shrinks in the waterfall model due to delay in earlier stages of model. This can led to defects escaping the testing phase, which if uncovered in production can result in higher costs and can impact business too.

 Conclusion: 

Waterfall model was first of the model to be used in Software development model and acted as a basic framework to track and relate various phases of development. With time, various new models of Software development life cycle evolved, for e.g. V-Model, Iterative model, agile Model and so on, but somewhere we can see glimpses of waterfall in each of the models. Hence Understanding the most basic of models is highly useful before going to understand newer model that evolved over the years in SDLC.