Wednesday, August 13, 2025

Database

DB Functional

1. Build-in Functions - Group functions, Scalar functions

Group functions -
  • AVG
  • SUM
  • MAX
  • MIN
  • COUNT
  • select SysDate from Dual

    Scalar functions -
  • Character Functions - Upper, Lower, InitCap
  • Length,
  • LPAD - Pads the Character on Left Side-
    Example: Select LPAD('ORACLE', 10, 'XXX) FROM DUAL
  • RPAD - Pads the Character on Right Side -
    Example: Select RPAD('ORACLE', 10, 'XXX) FROM DUAL
  • TRIM() - Removes the specified characters from both sides
  • LTRIM() - Removes the specified characters from left side
  • RTRIM() - Removes the specified characters from right side
  • INSTR() - Returns the position of the character with-in the string
  • SUBSTR() - Returns the sub-string of the string
  • CONCAT() - Join the two strings

  • Number Functions-
  • abs() - Returns the Obsolute Value
  • sqrt() - Returns the Square Root of the Provided value
  • mod() - Returns the Remainder value
  • power() - Returns the Power value
  • Trunc() - Removes the Decimal Point
  • Greatest() - Returns the Largest
  • Least() - Returns the Smallest

  • Date Functions-
  • ADD_MONTHS() - Adds Number of Months to the Provided Date
  • MONTHS_BETWEEN() - Returns the Number of Months between two given Dates
  • NEXT_DAY() - Returns the Next Date of the Provided Date
  • LAST_DAY() - Returns the Last Date of the Provided Date

  • Conversion functions-
  • TO_NUMBER() -
  • TO_DATE() -
  • TO_CHAR() - Converts the Date to specified format


  • Group by Clause - Is used to divide rows into different groups
  • Having Clause - Is used to filter the output from the group by clause
  • Order by Clause - Is used to arrange rows in the table (ascending or descending order)
  • Order of Execution of the Clauses Group By > Having > Order By

    Constraints - At the time of defining the tables, we specify
  • Not Null - Will not accept null values in the column
  • Unique - Does not allow duplicates
  • Primary Key - Combination of Not Null and Unique. Primary Key does not allow duplicate values and also null values. This constraints can be created both at the column level as well as table level. We can create a Primary Key by combining two columns which is also called as composite key
  • Foreign Key or Referential Integrity
  • Check
  • Types of Tables - User Defined Tables and System Tables Set Operators - Can be used only if the column types have same data type
  • Union
  • Union All
  • Intersect
  • Minus
  • Friday, December 13, 2024

    ETL Testing

    ETL - Extract Transform and Load
    Dataware House - Storage Area used for Analytical Purpose is stored in Dataware House. Dataware house combines data from multiple sources. Generally, it includes historical Data.
    1. Non-Volatile Data (Non-Modified).
    2. Subject Oriented
    3. Integrated Data
    4. Time-Variant

    Extract - Raw data is copied or exported from source locations to a staging area during data extraction data mangement.

    Transform - In this stage, raw data undergoes data processing. In this step, the data is processed and consolidated for its intended analytical use case.

    Load - The converted data is moved from the staging area into the target data warehouse in this final stage.

    An ETL pipeline (or data pipeline) is the system that allows ETL activities to take place. Data pipelines are a collection of tools and actions used to transfer data from one system with ites own manner of data storage and processing to another system where it might be kept and managed differently.

    OLAP - Online Analytical Processing- is an approach to answer multi-dimensional analytical queries. Operates on Data Warehouse.

    OLTP - Online Transaction Processing - facilitates and manages transaction oriented applications. Operates on Database.
    CRUD - Create, Read, Update and Delete Operations are performed on the Database
    OLTP Architecture - Database Server, Application Server, User Interface

    OLTP (Online Transactional Processing) OLAP (Online Analytical Processing)
    Current data Historical data
    Day-to-day Transactional operations Data analysis and decision making
    Normalised data structures Star schema or Snow flake Schema
    Simple Queries Complex Queries
    Requires Fast Response Can have Longer Response Times
    Used by Front Line Employees, Managers Analyst, Executives and Decision Makers
    Data in OLTP is processed Data in OLAP is processed is periodically refreshed
    Used for Analysing the Business Used for Running the Business
    Oracle, MySQL, SQL Server, DB2 Tableau, Power BI and SAP
    Provides Summarized and Consolidated Data Provides Detailed and flat relation view of Data
    Example: Database Example: Data warehouse


    In Database Testing, we will focus on tables, relationship, columns and datatypes.
    In ETL Testing, we will focus on data.

    Data Model
    1. Conceptual Data Model
  • Identifies high level relationship between entities.
  • Attributes are not defined. Keys are not defined
  • 2. Logical Data Model
  • Identifies Entities
  • All Attributes are defined
  • Keys and Perform Normalisation
  • Relationships are defined
    3. Physical Data Model - Entities, Attributes and Datatypes are defined


    FeatureConceptualLogicalPhysical
    Entity Name
    Entity Relationships
    Attributes
    Primary Key
    Foreign Keys
    Table Names
    Column Names
    Column Data Types

    Types of Schema - Schema is a collection of entities and their relationships

    Star Schema
    A star schema is a type of data modeling technique used in data warehousing to represent data in a structured way. It cotains fact table in the middle and dimensional tables around it
    Fact Table in a star schema contains the measures or metric. Fact Table contains relationship with other tables. Every column in the fact table is the primary key
    The dimension table in a star schema contain the descriptive
    Snowflake Schema


    Slowly Changing Dimensions (SCD)
  • Type 1: Replaces Old Entry with New Entry
  • Type 2: Inserts a New Entry in the Table
  • Type 3: Original Entry gets modified to Reflect the New Entry


  • ETL Tools
  • Informatical Power Center
  • Query Search
  • Microservice MS SQL Server SSIS
  • (Business Intelligence) BI Tools - Used for Reporting and Reports used for Analysis
  • Business Objects
  • Cognos
  • Tableau
  • Quick View


  • Types of Data Load in ETL Testing
  • Full Load - This is the data loading process, when we do it for the first time. It can be referred as Bulk Load or Fresh Load
  • Incremental Load- The modified data alone will be updated in target followed by full load. The changes will be captured by comparing created or modified date against last run date of the job.
  • Data Cleansing (Data Scrubbing) - Process of removing irrelevant or redundant data and correcting incorrect and incomplete data
  • Irrelevant - Deleting Data which is not required for business or not needed any more.
  • Redundant - Deleting the Duplicate Data
  • InCorrect - Updating Incorrect values with Correct value
  • InComplete - Updating Incomplete values with


  • Masking - Hiding/Encrypting Sensitive Data is called Masking

    Monday, December 9, 2024

    API Testing

    API - Application Programming Interface.
    They are basically collection of procedures and functions which allow us to communicate between two applications or libraries.
    REST API - It is REpresentational State Transfer API.

    Excel Functions

    XMATCH
    XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position.
    The XMATCH function returns the relative position of an item in an array or range of cells. =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

    PROPER
    PROPER function is a Text function, and its purpose is to capitalize the first letter in each word of a text value that will remain in lowercase or convert to lowercase.

    XLOOKUP function: Use =XLOOKUP("last", range, return_array) to get the value of the last non-empty cell1.

    LOOKUP function: Use =LOOKUP("last", range, return_array) to find the last non-blank cell in a row2.

    COUNTA and OFFSET functions: Use =COUNTA(range) to count the number of non-empty cells, and =OFFSET(A1, COUNTA(A:A)-1, 0) to get the last non-blank cell34.

    ADDRESS and LOOKUP functions: Use =ADDRESS(LOOKUP(2, 1/(M:M<>""), ROW(M:M)), 13) to return the address of the last non-blank cell in column M

    Basic Functions & OperationsSUM & COUNT
    A1+B1 Addition SUM(A1: A10) Sum of a range
    A1-B1 Subtraction AVERAGE(A1:A10) Average of a range
    A1*B1 Multiplication COUNT(A1:A10) Count of numeric entries
    A1/B1 DivisionCOUNTIF(A1:A10, ">10") Count cells that meet a condition
    A1*2ExponentationCOUNTIFSCounts the number of cells in a range where one or more conditionsa are met
    MOD(A1, B1)Remainder of divisionSUMIF(A1: A10, ">10", B1: B10) Sum based on a condition
    Logical Functions MIN(A1:A10) Calculates the smallest number in a range
    IF(A1> 10, "Yes", "No") Conditional Statement MEDIAN Finds the median value in a group of values
    AND(A1> 0, B1 < 5) Logical ANDMAXCalculates the highest number a range
    OR(A1 > 10, B1 < 5)Logical OR Text Functions
    NOT(A1 = 10) Logical NOTCONCATENATE(A1, "", B1)Join text strings
    IFERROR(A1/B1, "Error") Handle errors gracefullyTEXT(A1, "0.00")Format number as text
    Date & Time LEFT(A1, 3) Extract Left Most characters
    Today() Current dateRIGHT(A1, 3) Extract Right Most characters
    NOW() Currnet date and time MID(A1, 2, 3)Extract chracters from the middle
    Lookup FunctionsLEN(A1) Length of a Text String
    VLOOKUP Vertifically searches for a value in a tableFIND("text", A1) Find position of text
    INDEXA lookup function that searches a table both vertically and horizontally SUBSTITUTE(A1, "old", "new")Replace text in a string
    XLOOKUP An updated version of VLOOKUP available in newer Excel versions UPPER Converts a string of text to all uppercase
    HLOOKUP Horizontally searches for a value in a tableLOWER Converts a string of text to all lowercase
    MATCHReturns the location of a value in a sequenceVALUE Converts a text to a value
    OFFSET Moves the cell reference by the number of given rows and/or columns

    Saturday, November 30, 2024

    Java Annotations:
    @Entity
    @Author
    @Override - annotation informs the compiler that the element is meant to override an element declared in a superclass.
    @SuppressWarnings - annotation tells the compiler to suppress specific warnings that it would otherwise generate.
    @Deprecated - annotation indicates that the marked element is deprecated and should no longer be used.


    Lambda Expressions: Lambda can be used with certain type of interfaces.
    parameter -> expression
    (parameter1, parameter2) -> expression
    (parameter1, parameter2) -> {expression}


    Any interface with a SAM(Single Abstract Method) is a functional interface, and its implementation may be treated as lambda expressions. When a method inside an interface does not have implementation, it is called abstract method. When an interface has one abstract method, the interface is called Functional Interface. Abstract method in the interface uses the lambda expressions. Interface is preceded with Functional Interface Tag
    Example Interface:
    @Functional Interface
    public Interface printable {
    void print();
    }
    For Loop using Lambda Expressions on Array List:
    List names = Arrays.asList("John", "Freddy", "Samuel");
    names.forEach(name -> System.out.println("Hello, " + name));
    For Loop using Lambda Expressions on Hash Map:
    Map ages = new HashMap<>();
    ages.put("John", 25);
    ages.put("Freddy", 24);
    ages.put("Samuel", 30);
    ages.forEach((name, age) -> System.out.println(name + " is " + age + " years old"));
    Filter list using the Stream API and keep only the names that start with the letter “A”.
    List names = Arrays.asList("Angela", "Aaron", "Bob", "Claire", "David");
    List namesWithA = names.stream()
    .filter(name -> name.startsWith("A"))
    .collect(Collectors.toList());
    System.out.println(namesWithA.get(0).toLowerCase());
    System.out.println(namesWithA.get(1).toLowerCase());
    Replace All in List: List names = Arrays.asList("bob", "josh", "megan");
    names.replaceAll(name -> name.toUpperCase());
    names.forEach(name -> System.out.println("Hello, " + name));
    Replace All in HashMap: Map salaries = new HashMap<>();
    salaries.put("John", 40000);
    salaries.put("Freddy", 30000);
    salaries.put("Samuel", 50000);
    salaries.replaceAll((name, oldValue) ->
    name.equals("Freddy") ? oldValue : oldValue + 10000);
    salaries.forEach((name, value) -> System.out.println(name + " is " + value + " years old"));
    Calculator Operations using Lambda Expressions:
    package org.example;
    public class Main {
    @FunctionalInterface
    interface Operation {
    int calculate(int x);
    }
    public static void main(String[] args) {
    int a = 5;
    Operation square = (int x) -> x * x;
    Operation add = (int x) -> x + x;
    Operation subtract = (int x) -> x - x;
    int ans = square.calculate(a);
    int addedvalue = add.calculate(a);
    System.out.println(ans);
    System.out.println(addedvalue);
    System.out.println(subtract.calculate(6));
    }
    }

    Saturday, February 12, 2022

    Working with Batch Files


    Synchronise List of files from Linux Server to Window using Batch file

    @echo off
    "C:\Users\mmendu\AppData\Local\Programs\WinSCP\WinSCP.com" ^
    /ini=nul ^
    /command ^
    "open sftp://a_MMendu:P "synchronize -filemask=S_EDI_202202*.TXT local C:\Users\mmendu\Documents\UFT /app/retek/rtkstst/transport/extract" ^
    set WINSCP_RESULT=%ERRORLEVEL%
    if %WINSCP_RESULT% equ 0 (
    echo Success
    ) else (
    echo Error
    )

    Get the files by Masking in synchorize command

    synchronize -filemask=RMS_EDI_202202*.TXT local "+FilesFolderpath+" /app/retek/rtkstst/transport/extract"

    Monday, December 11, 2017

    VBScript - Coding Standards

    Constant Names

    Constant Names 

    Names of constants should be meaningful noun or noun phrases with all letters capitalized. Multiple words were separated using the underscore (_) character. 


    Example: MY_VARIABLE

    Function Names
    Function Names (or) Module names should be meaningful words or phrases that describe the abstraction of the module.  Use nouns for object abstractions and verbs for functional abstractions.  Module names should be without spaces, in mixed case, with the first letter uppercase, and the first letter of each subsequent word capitalized.  

    Boolean Function Names

    Functions that return the results of a test of a Boolean condition T should be named IsT, HasT, or CanT.


    Variable Names

    • Append computation qualifiers (Avg, Sum, Min, Max, Index) to the end of a variable name where appropriate.
    • Boolean variable names should contain Is which implies Yes/No or True/False values, such as fileIsFound.
    • Do not use literal numbers or literal strings, such as For i = 1 To 7. Instead, use named constants, such as For i = 1 To NUM_DAYS_IN_WEEK for ease of maintenance and understanding.