SQL Sample Database

SQL Sample Database

In the coming articles, we will be using our sample database which is an HR database. It has multiple seven tables which will be interlinked to get answered our question.

The description of tables are as mentioned below

The jobs table stores the job data including job title and salary range.

The dependents table stores the employee’s dependents.

The employees table stores the data of employees.

The departments table stores department data.

The regions table stores the data of regions such as Asia, Europe, America, and the Middle East and Africa. The countries are grouped into regions.

The locations table stores the location of the departments of the company.

The countries table stores the data of countries where … Continue Reading

What is SQL

Introduction to SQL language

SQL is a programming language that is used to manage data stored in a relational database management system (RDBMS).

SQL pronounced as /ˈɛs kjuː ˈɛl/ or /ˈsiːkwəl/. SQL full form is Structured Query Language.

SQL has three components:

  • Data Defination Language (DDL): The data definition language deals with the schema/Table/View creation and modification like, CREATE TABLE statement allows us to create a new table in the database and the ALTER TABLE statement helps in changing the structure of an existing table.

  • Data Manupulation Language(DML): The data manipulation language helps to query data such as the SELECT statement and to update the data such as INSERTUPDATE, and DELETE statements.

  • Data Control Language (DDL): The
Continue Reading

New Excel Functions in Excel 2021: 5 Awesome Excel formulas

New Excel Functions in Excel 2021

Every time when Microsoft excel launches a new version new excel functions are also been added there. Excel 2021 also has new functions like  FILTER, SORT, SORT BY, UNIQUE, SEQUENCE. These all excel functions help us reducing our processing time. Let us see in brief what these functions are and how they work. Let us go through these New Excel Functions in Excel 2021.

FILTER function in excel

Excel FILTER function allows us to filter a range of data from a dataset based on some criteria. It is one of the New Excel Functions in Excel 2021. Let us understand first the syntax of Filter formula excel.

New Excel Functions in Excel 2021
New Excel Functions in Excel
Continue Reading

Project Charter: What is a six sigma project charter and how to create it in 7 steps[Free download]

What is project charter in six sigma

Project charter in six sigma very important requirement for the project. It is a living document for the project and it lays out every important project variable. The creation of a project charter is one of the first steps in six sigma project. It is a part of the Define phase of DMAIC process. This includes everything from who is participating in the project, to the project goals. Let us take few minutes to understand the different components of project charter step by step.

Step-1: Defining the project name

The very step in making a Project Charter is to name the project. The project name should reflect what the project is looking … Continue Reading

Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples]

Flash fill in excel

What is Flash fill in excel?

Flash Fill is a data tool in Microsoft Excel that allows us to combine, extract or transform data based on a few examples. We only need to provide a couple of examples of the results we want, excel will guess the pattern and fill in the rest of the data for us.

What is Flash fill in excel?
What is Flash fill in excel?

In the above gif file, we can see that we only provided one row of input, and after using Flash fill it automatically separates values.

How to Enable Flash Fill in Excel?

To enable flash fill in excel go to File then Options. After that select Advanced and … Continue Reading

How to refresh Pivot Table: 2 ways to refresh pivot tables in excel

How to refresh Pivot Table

In our previous post we learnt about how to create pivot table in excel. Now in this post we will try to understand different ways to refresh pivot table when new data is being added in the data range of pivot table. There are different ways to refresh excel pivot table, but we can categories them in two different ways.

  • Manually and
  • by VBA code.

How to refresh pivot table manually

1) To refresh a pivot table manually first select any cell on excel pivot table then go to PivotTable Analyze tab then in Data column click on Refresh. Now we ill get 2 options Refersh and Refresh all.

How to refresh Pivot Table
How to refresh Pivot
Continue Reading

Table in Excel: How to create excel table step by step by 2 methods

How to create excel table

Table in excel is a powerful feature that helps us in organizing our data by grouping data together. It is very quick to create a table in excel by just clicking a few keys or using a shortcut key and converting your flat data into a table with a lot of benefits.

The benefit of using excel table is as below but it is not limited to these only:

  • Clean Formulas : When we are using excel table feature then it become very easier to read forumals even it is very complicated.
  • Referencing a range : It is very easy to reference a range as every table has its own name.
  • Auto expand : It
Continue Reading

How to transpose excel rows to columns:3 Ways to Transpose Data in Excel

Excel rows to columns transpose

What is excel transpose function

Excel transpose function is a very useful functions in excel. It is used to switch rows to columns or excel columns to rows. In our regular excel use, we usually come across situations where we need to change excel columns to rows or rows to columns to make calculations easier or to visualize data in excel. Let’s learn how to transpose excel rows to columns.

Method 1: Transpose Data with Paste Special Command

In this method, we will first copy the data that we wanted to transpose which is in rows. After copying the data we will select any cell where we wanted to transpose the data. Then we will … Continue Reading

How to return a value if the VLOOKUP value is in a range || Excel VLOOKUP ||DataWitzz

How to return a value if the VLOOKUP value is in a range

Till now we have learnt about VLOOKUP and how to make VLOOKUP range dynamic with the help of excel MATCH function. Now let us learn how to return a value if the VLOOKUP value is in a range. Recently I got a data set where in i was asked to lookup values but the lookup reference table has data in range only.

Let us see the below image to understand this scenario then we will discuss how to selve this issue.

How to return a value if the VLOOKUP value is in a range
lookup value from range

In the above image we can see there are two tables. The one which is on left side Marks column and … Continue Reading

VLOOKUP and MATCH combo function in excel: How to create a dynamic VLOOKUP in excel ||DataWitzz

VLOOKUP and MATCH combo function in excel

We already know about the VLOOKUP function in excel and its functionality. VLOOKUP basically search value based on the given criteria. There are few limitations in VLOOKUP like it picks only the first value available in the reference table, its columns are static. In this article, we will be discussing how to make a dynamic column range in VLOOKUP in excel.

The problem with static column number in VLOOKUP

Let us understand this by the below example.

VLOOKUP and MATCH combo function in excel
VLOOKUP and MATCH combo function in excel

In the above image, we can see that we are trying to get the Quantity value of Product ID 1108. The Quantity of product id is in the Quantity … Continue Reading