JOINS in simple way:

             JOIN concept can be applied in database, file system and in day to day scenarios. I am putting it in a simple way so that all can understand the same.

Let’s speak about AJ our friend who is in Grade X in Public school and also plays Football.

If we want to know, how many AJ’s Football team members from Grade X then it will be INNER JOIN between GRADE_X and FOOTBALL_TEAM.

Scenario 1 & 2 in below table explains the same.

If we want to know, all the AJ’s classmates from Grade X with Football membership, then it will LEFT OUTER JOIN between GRADE_X and FOOTBALL_TEAM.

Scenario 1, 2, 3 & 4 in below table explains the same.

If we want to know, all the AJ’s Football team and will details of his class mate, then it will RIGHT OUTER JOIN between GRADE_X and FOOTBALL_TEAM.

Scenario 1, 2, 5 & 6 in below table explains the same.

If we want to know, all the AJ’s classmates from Grade X who aren’t the member of Football team, then it is LEFT OUTER JOIN between GRADE_X and FOOTBALL_TEAM and condition that Foot_Team_Member is NULL.

Scenario 3 & 4 in below table explains the same.

If we want to know, all the AJ’s Football team who are not from GRADE X, then it will RIGHT OUTER JOIN between GRADE_X and FOOTBALL_TEAM.and condition that GRADE_X_Student_Name  is NULL.

Scenario 5 & 6 in below table explains the same.

If we want to know, all the AJ’s Football as well as his classmates from GRADE X, then it will FULL OUTER JOIN between GRADE_X and FOOTBALL_TEAM.

Scenario 1, 2, 3, 4, 5 & 6 in below table explains the same

             

Scenario Number	Grade X Student Name	Football Team Member
1	AJ	AJ
2	DJ	DJ
3	KK	<NULL>
4	DK	<NULL>
5	<NULL>	GK
6	<NULL>	PK

The important point in JOINs is the KEY. We need KEY to join and in the above scenario it is Student Name. In practice, this JOIN KEY is numeric as it is more efficient. So in real time scenarios we use KEY like Student Number, Serial Number etc.

JOIN without KEY is called as Cartesian product where all rows from each table JOINS with another table. For e.g. if each table has 10 rows then result will have total 10 X 10 = 100 rows.

Joining the table with same table is called as SELF JOIN.

Type of JOIN and selecting KEY is skill as JOIN queries are expensive. These queries consume lot of CPU time and performance of system downgrades. Hence there is need of INDEXES, HINT and FILTER Criteria.

INDEXES: Increases the performance.

HINTS: Helps in selecting the right index.

FILTER Criteria:  Reduces no. of rows from comparison and hence total query time.

BASIC EXCEL TIPS

We can't think the life without computers now. We've computers in Office as well as at home. The major change this computer brought to us is ease in calculations.

All our bank statements, score card are now available in csv or in excel format and we check the results.

However many of us don't know real power of excel apart from just viewing the data. To use Excel in simple and smart way please find few tips.

1. Format and Freeze: We can Format the rows and column ususally in AUTOFIT selection by FORMAT > COLUMN > AUTOFIT SELECTION and FORMAT > ROW > AUTOFIT SELECTION.

We lose the headings that are located at the top and the left side of the worksheet when we scroll large spreadsheets.  To avoid this problem use WINDOWS > FREEZE PANES feature in Excel.

2. HEADER and MACRO: We like to highlight HEADER with different font, type and/or with different fill color. This task becomes absolute necessity, once we open every new spreadsheet. We can record this operation in MACRO and using the CTRL key, we can do this operation more efficiently. TOOLS > MACRO > RECORD THE NEW MACRO. We can store this MACRO with CTRL key in MACRO list and refer wherever we need.

3. SORT: To make the data in desired order, we need to sort it and sorting can be done by selecting the required data and DATA > SORT > SELECT FIELDS.

4. DUPLICATES / UNIQUE ROWS: We can prepare the list of Unique values from the selected field as DATA > FILTER > ADVANCED FILETER > SELECT FIELD NAMES.

5. UNIQUE FIELD / COMPARE ‘N’ FIELDS: To compare two fields we’ve functions like exact. However to compare set of N Fields with another set of same N fields, we don’t have any direct function. However we can create the Unique records by contacting N fields and using normal compare function we can compare the data. For e.g.: Unique field can be prepared from three fields as  “= A1&B1&C1”.

6. TRANSPOSE: In Copy and Paste option, we’ve PASTE SPECIAL > TRANSPOSE option which can be used to convert rows into column and vice versa. This option is beneficial in preparing header record.

7. MATCH, FIND (VLOOKUP): If we want to match the pattern in another set of data we’ve powerful function called on VLOOKUP. The VLOOKUP function matches the input pattern in the range provided and display matched Index field. The HLOOKUP () is similar function and will find matching pattern in Horizontal row.

8. Conditions (IF Statement): Conditional statements such as if provide way to build the complex Business Logic. IF( Condition, Value if True, Value of False)

9. EXACT ():  The exact function displays comparison result in binary format as TRUE or FALSE. The syntax is Exact( trext1, text2)

10. HYPERLINK: This is easy option to move between intermediate sheets. RIGHT CLICK > HYPERLINK will help to add the desired path for the same.

These are 10 basic things which we use frequently in Excel in data management.