Category Archive Business Analyst Skills

SQL Tutorial for Business Analysts Part 3

SQL tutorial for Business Analysts

This is the third and the concluding part of the SQL tutorial for business analysts. In the first part of this SQL tutorial for Business analysts, you learnt about the basics of a database system and SQL. In the second part of this SQL tutorial for business analysts, you learnt about INSERT, DELETE and CREATE TABLE commands.

In this concluding part, you are going to learn about SELECT command.

Introduction to SELECT Command

SELECT command is probably the most used command in SQL. It’s used primarily to get data from a single table or multiple tables. The usage of SELECT command are as follows:

a) If you are a SQL developer, you may use it for creating reports or display data

b) If you are a Business Analyst or a testing professional, you may use SELECT to validate the data stored in the table(s) (to check if correct data is getting saved).

c) Business Analysts may also use it to get data for analysis

Syntax of SELECT command

The syntax of SELECT command is as shown below:

SELECT <<col_name1>>, <<col_name2>>…..

FROM <<table_1>>, <<table_2>>

WHERE <<conditions>> AND <<Joins>>

We will explain the syntax shortly but first of all, let’s see one example. We have been referring to STUDENTS table in this tutorial and we added rows to it as well. Let’s see, how can we fetch the data from that table. Write the following command in the W3 SQL editor and hit “Run SQL” button:

SELECT * FROM STUDENTS;

The result of this command is as shown below:

SELECT command in SQL

You can see that it returns all the rows (I had only inserted one row). If you have completed the exercise in the previous tutorial, it should return all the rows you had inserted.

Please note “*” in SELECT command is meant for showing all the columns and not for all the rows. 

So what if there are multiple rows and we only want a specific row. Let’s take an example to understand that. There are multiple rows in the CUSTOMERS table in example database. As of now, if I run the following command on CUSTOMERS table:

SELECT * FROM CUSTOMERS;

It returns 91 rows as shown below:

Select command in SQL

What if I want only specific row or rows? What should I do?

Using conditions in SELECT

That’s where the WHERE <<condition>> section comes into picture. So, let’s say we would like to see the customers who belong to “Berlin” (city name). So how will we write this SELECT?

SELECT * FROM CUSTOMERS

WHERE city = “Berlin”;

This SELECT statement will return you only those customers, which belong to “Berlin”. It’s also possible to have multiple conditions in a single SELECT statement.

The table contains multiple Customers from the USA. As of now, there are 13 customers from the USA. But if we only want to know about the customers from the city “Seattle” in the USA, we can write the SELECT statement as shown below:

SELECT * FROM CUSTOMERS
WHERE country=”USA”
AND city = “Seattle”

This will get only those customers, which are from “Seattle” in the USA. You can have as many conditions in the SELECT statement.

Practice Exercises

You can use the tables provided in SQL editor to practice.

Joins in SELECT Command

In the last section of this three part SQL tutorial, we are going to discuss about JOINS. JOINS are used if we would like to get data from more than one table. In that case, we need to create the join on the columns, which are common between the tables.

We have already seen the syntax in this post. Let’s look at our example case. In the sample database given in W3 SQL editor, let’s look at PRODUCTS and CATEGORIES tables. If you see the data in the PRODUCTS table, you can see that products belong to various categories and these categories are stored in the PRODUCTS table as CategoryID.

SQL Tutorial image

This CategoryID is actually a column in the CATEGORIES table. If we try to get the data from CATEGORIES table, we will get the data with CATEGORYID as 1, 2 etc. But that’s not very easy to understand, what if we would like to see the proper Category name along with the product names and unit. Let’s see how to write the SELECT command in this case.

SELECT productname, categoryname, unit FROM PRODUCTS A, CATEGORIES B

WHERE A.categoryID = B.CategoryID

As a result, we will get the following result:

SQL Tutorial image

We have used A & B immediately after the table names for convenience. These are also known as aliases. The join is created using the following line:

A.categoryID = B.CategoryID

Since CategoryID is the common column name, we have equated them to avoid Cartesian product (Remember Set theory?). If there are more than one common columns between the tables, you should equate all of them, else you will get wrong results.

Summing up SQL Tutorial

You have learnt basic commands of SQL in this tutorial, there are many commands as discussed in Part-I of this SQL tutorial. To get a stronger grip on the SQL command, you need to practice a lot more . This is an important skill, expected from a Business Analyst in the job interviews.

We are coming up with a more comprehensive tutorial for learning Business Analysis skills . That tutorial will provide steps to learn the business analyst skills with specific resources including videos and tutorials.

You may also like to look at our other tutorials and videos, specially for professionals, who are looking to become a business analyst.

Business Analyst Tutorials

Finally, SQL is an important skill for Business Analysts specially if you are looking to become a business analyst. Techcanvass provides a Certified Business Analyst (ECBA) training program, to know more about it you can click on the following image.

Certified Business Analyst Training

SQL Tutorial for Beginners Part 2

 

SQL Tutorial for Beginners

This is the second part of SQL Tutorial for beginners. In this first part of this SQL tutorial for beginners, you learnt about the basics of a database system and SQL.

In this part, we are going to learn about CREATE TABLE & INSERT/DELETE commands. These commands are SQL commands and are used to interact with the database. We are going to use W3 School Online SQL editor in this tutorial, so keep it open in another tab in your browser.

CREATE TABLE COMMAND

Create table command is used to create a table. We have discussed STUDENTS table in the part I of this tutorial. Let’s re-produce that table for our reference.

Tables in database

Tables in database

To create this table using SQL, we use CREATE TABLE command. The Syntax of this command is as follows:

CREATE TABLE <<TABLE_NAME>>

(

Column_name1 datatype,

Column_name2 datatype,

….

)

The datatype is the type of data, which will be stored in the column like Text, number or date etc. So to create the STUDENTS table, we can write the following CREATE TABLE command:

CREATE TABLE STUDENTS
(

student_name varchar2(100),
date_of_joining date,
course_name varchar2(100),
contact_number number(11)

)

You can type the above command in the SQL statement box and click on “Run SQL” button. You can see that a new entry will be created at the bottom “Your database” section on the right, as indicated in the image below:

Create table command

Create table commandCreate table command

One important aspect, you must remember, the table & column names must be a single word. We cannot use “Student name” as column name and that’s the reason, we have used “student_name” as column name.

Note: All Database systems will not allow duplicate names for tables and other database components.

Practice Exercises

You can see a lot of tables in the right side section of the W3 SQL editor. Click on them and try and create tables with similar column names but different table names.

INSERT Command

INSERT Command is used to put data into the table. It’s a simple command and the syntax is as shown below:

INSERT INTO <<table_name>> (column names….) values (….);

To understand it well, let’s again refer to our STUDENTS table above. Let’s try and put the first row into the table STUDENTS, which we have created in the previous section. Let’s say that we have to put the first row in the table STUDENTS, the INSERT command will be written as follows:

INSERT INTO STUDENTS VALUES (“Ramesh Chandra”, “01-Jan-2014”, “.NET”, 999999999);

Please note that we have used ” ” for text and date values but we have not used ” ” for contact number, which is a number column.

Practice Exercises

Refer to STUDENTS table and INSERT the remaining rows in the STUDENTS table.

DELETE Command

DELETE command is used to delete the rows from the tables. It’s again a simple command and its syntax is as follows:

DELETE FROM <<table_name>> condition;

It’s important to note that if we don’t use the condition, the DELETE command will delete all the rows from the table. We will see how to specify the condition in the last part of this tutorial.

What’s next: Part-III of this SQL tutorial, where we will discuss SELECT statement.

We are coming up with a more comprehensive tutorial for learning Business Analysis skills . That tutorial will provide steps to learn the business analyst skills with specific resources including videos and tutorials.

You may also like to look at our other tutorials and videos.

Business Analyst Tutorials

Finally,  SQL is an important skill for Business Analysts specially if you are looking to become a business analyst.

Techcanvass has multiple levels of training program for business analysts.

IIBA ECBA Business Analyst Training

Certified Agile Business Analyst (CABA) Training

Business Analyst in Risk Management

 

Which requirements elicitation approach will you use in the given situation?

Question

You are contracted to develop a software system involving multiple divisions of an organization. Each division is a stakeholder in the organization and there are approximately 25 divisions. How will you go about requirements gathering?

Question Objective: This is a scenario based question and is asked to check your understanding of processes and the ability to customize approach based on situations.

Answer

In this case, I would like to create a small team for each division by choosing members from the customer teams themselves. Each team will be led by process champion. Each divisional team will be responsible for interacting with divisional stakeholders to gather requirements and document it. It will require some training for the process champions and that can be done in an consolidated manner.

Once the process champions, complete their work, my team (of business analysts ) will sit with the process champions and their respective team for further discussion, interviews etc to elicit & to consolidate the requirements.

 

For the profile of business analyst, which matters more: either technical soundness towards tool i.e. SQL, R, SAS, Excel etc., or experience and knowledge about business?

In response to this question on Quora, here is the answer:

Business Analyst’s role has come into prominence because of issues relating to requirements understanding. That’s also been the primary reason for projects not being successful in achieving the desired goals. So understanding of business is probably the most important skill along with the problem solving skill.

The tools are just a means to solve a problem and should never be the key skills. Different projects may need different tools, how many tools one can master? Moreover, the speed at which new tools/techniques are propping up, you will always be a follower. In my view, any tool can be picked up in a few weeks time but business understanding comes with years of hard work and experience.

Go for business domain knowledge but focus on one or two segments only

 

Business Analysis for AGILE Projects

AGILE is the way to GO……………

This statement resonates with most of software professionals. AGILE methodologies like SCRUM, Kanban, XP are getting adopted by more and more projects. Customers prefer this approach as AGILE methodologies de-risks the software automation project.

Techcanvass is a big proponent of giving more important to business analysts in every project as they play an important role in the success of every project. Business Analysis for AGILE Projects is a natural requirement/skill. In this webinar, this little ambiguous topic of Business Analysis for AGILE Projects is discussed at great length. There were some interesting questions and differing opinion from some of the participants.

Watch it and enjoy

Techcanvass conducts these online sessions on interesting topics relating to AGILE and Business Analysis. If you would like to be notified of these new videos, please subscribe to Techcanvass YouTube channel

Techcanvass on Youtube

Mythical Man Months in Software Projects

Man Months is one of the most misunderstood words in software industry. It’s used to refer to the effort required to execute a software project. Simply speaking, a man month refers to the effort of one developer working for a month.  However, the concept goes beyond this simplicity.

Frederick Brooks’s The Mythical Man-Month: Essays on Software Engineering, published in 1975, is one of the most influential books on software engineering. Amazon website says

Few books on software project management have been as influential and timeless as The Mythical Man-Month. With a blend of software engineering facts and thought-provoking opinions, Fred Brooks offers insight for anyone managing complex projects.

This book has made the term “Mythical man months” extremely famous term though relevant. The basic premise of the book, also referred to as Brooks’ law:

Adding manpower to a late software project makes it later.

Which essentially means that man months is not a mathematical concept. So, if we estimated that a project is going to take 35 man months to complete, does not mean that putting 35 people on the project, will finish the project in 1 month.

Extending this concept further, if a project is getting executed, which was supposed to be 45 man months of effort, started with 5 members. After 3 months of work, we can say that 15 man months of effort is spent and the remaining effort is 30 man months. But this project had a deadline of 5 months. Now how do you finish the project in 2 more months (remember, 30 man months of effort is still pending)?

One may say, put 10 more people, making the team size to be 15. So 15 people working for 2 months will be equivalent to 30 months, done deal. Not True, this is what Brooks’ has discussed in great details in his book.

Sue Kim, in the blog post Mythical Man Month – The Cliff Notes mentions that:

Men and months are interchangeable commodities only when a task can be partitioned among many workers with no communication among them.

This points to an important aspect of human communication in a team. More people open up more channels of communication leading to errors and delays. Any business communication text will explain that 15 members in a team, will have 15 *(15-1)/2 = 135 possible ways of conversations amongst them, leading to confusion and errors.

In the blog post titled Modeling the Mythical Man-Month, a mathematical model has been mentioned to explain Books’ law. Another mathematical model namely Putnam Model was published immediately after Books’ book to provide a mathematical model.

Cheers