PostgreSQL developer Online training

Introduction          

What is Data?

Database

DBMS & RDBMS

What is PostgreSQL

History

Comparison with Different Databases

Installation & Configuration

Download and Installation

Connecting pgAdmin

Database

Creating Database

Creating Schema

Grant Privileges

Connecting to DB

Data Types

Primitives:                             Integer, Numeric, String, Boolean   

Structured                             Date/Time, Array, Range / Multirange, UUID

Document:                            JSON/JSONB, XML, Key-value (Hstore)        

Comments          

Single line comment --

Multiline comment /*….*/

 Data Definition Language (DDL) Deep Dive

CREATE TABLE: Data types (beyond standard: SERIAL, UUID, JSONB, arrays, TIMESTAMPTZ).

Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL.

ALTER TABLE: Adding/dropping columns, changing types.

DROP TABLE vs. TRUNCATE TABLE.

Data Manipulation Language (DML) Mastery

INSERT, UPDATE, DELETE statements.

RETURNING clause: The PostgreSQL superpower for developers.

UPSERT (INSERT ... ON CONFLICT ... DO UPDATE): Handle conflicts gracefully.

Querying Data (DQL)

SELECT: Filtering (WHERE), sorting (ORDER BY), limiting (LIMIT/OFFSET).

Joins: INNER JOIN, LEFT/RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN.

Aggregation and Grouping: GROUP BY, HAVING, aggregate functions (COUNT, SUM, STRING_AGG).

Window Functions: ROW_NUMBER(), RANK(), LAG()/LEAD() for advanced analytics without sub-queries.

Operators            

Comparison Operators (=,!=,<,>,>=,<=)                                                         

Arithmetic Operators (+,-,*,/)                                                             

SQL*PLUS operators : Between, Not Between, IN, NOT IN, IS NULL, IS NOT NULL, REG EXPRESSIONS

Constraints                                                         

                Primary Key                                          Unique + Not Null, table identifier                   

                Foreign Key /Reference Key             References another table’s column                               

                Unique Key                                           Allow Null values and restrict Duplicates                      

                Not Null                                                 Allow duplicate values and not allow null values                        

                Check Constraints                               Applying condition                             

                Default                                                   It is not constraint                

                EXCLUSION CONSTRAINT              Prevents overlapping/range conflicts

Functions

General Functions

UPPER

Lower

Case

Decode

NULLIF

COALESCE

Length

INITCAP

Character Functions

UPPER

LOWER                 

INITCAP                               

SUBSTRING(SUBSTR)                     

INSTRING(INSTR)                              

PADDING              LPAD     RPAD

TRIM                       LTRIM    RTRIM

REPLACE                            

TRANSLATE

Numeric Functions

Round

TRUNC

CEIL

FLOOR

MODULE

POWER

SQUARE ROOT (SQRT)

ABSOLUTE

Date Function

                                CURRENT_DATE

CURRENT_TIME, current_timestamp, , clock_timestamp 

NOW(), age, JUSTIFY_DAYS

EXTRACT (YEAR, MONTH, DAY, HOUR)

Intervals (add, Subtracting), Operators (Adding, Subtracting)

Conversion Functions

                TO_CHAR

                TO_DATE()

TO_TIMESTAMP()

CAST

Aggregation Functions / Group Functions

                SUM, MIN, MAX, AVG, COUNT

                array_agg, json_agg, jsonb_object_agg

Conditional Expressions

CASE

COALESCE

NULLIF

GREATEST and LEAST

JOINS

                INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN

CORSS JOIN

SUB Queries

EXISTS

Recursive query

SELECT IN WITH

 

SET OPERATORS

                UNION ALL

                UNION

                INTERSECT

                MINUS

VIEWS

                View, Materialized views

Managing Views

Index

                B-tree, Hash, GiST, SP-GiST, GIN, and BRIN

 

SYSTEM COLUMNS

                oid, tableoid, xmin, cmin, xmax, cmax, ctid, LIMIT, SERIAL

               

Analytical Functions

                RANK

CUME_DIST

FIRST_VALUE

LAST_VALUE

LAG

LEAD

ROW_NUMBER()

Sequences

SYNONYMS

Query Optimization

Interview Question and Answers

MOCK Interview

Resume Preparations

What is Quality and Defect in Manual Testing?

 

What is Quality in Manual Testing?

In manual testing, quality does not simply mean “no bugs.” It’s a broader concept. Quality is the degree to which a software product meets the stated and implied needs of its users when it is used under specified conditions.

In simpler terms, a high-quality software is:

1. Fit for its purpose: It does what it’s supposed to do.
2. Meets user expectations: It provides a good experience, not just basic functionality.

---

Breaking Down Quality with Examples: The “Ice Cream Cone” Model

Imagine a user wants to buy an ice cream cone from a shop. Let’s see how different levels of quality apply.

1. Functional Quality: “Does it work?”

This is the most basic level. Does the software perform its intended functions correctly?

• Example: The user pays for a chocolate ice cream cone.
  • Low Quality: The server gives them an empty cone. Or the payment system crashes. The core function fails.
  • High Quality: The user correctly receives a chocolate ice cream cone. The core function works.

2. Non-Functional Quality: “How well does it work?”

This is where user experience (UX) comes into play. The function works, but is it a good experience?

• Reliability:
  • Low Quality: The ice cream cone breaks immediately, spilling ice cream everywhere. (The software crashes under normal use).
  • High Quality: The cone is sturdy and holds the ice cream reliably.
• Usability (Ease of Use):
  • Low Quality: The ice cream is scooped into the cone upside down, making it messy to eat. The menu is confusing and hard to read. (The software is clunky and illogical).
  • High Quality: The ice cream is neatly presented. The menu is clear. It’s easy and intuitive to order.
• Performance:
  • Low Quality: The user has to wait 10 minutes in line while the server struggles to scoop the ice cream. (The software is slow and sluggish).
  • High Quality: The user is served quickly and efficiently.
• Compatibility:
  • Low Quality: The shop only accepts cash, but the user only has a card. (The software only works on one specific browser or device).
  • High Quality: The shop accepts cash, card, and mobile pay. (The software works across different browsers, devices, and operating systems).
• Security:
  • Low Quality: The cashier writes down the user’s credit card number on a piece of paper for everyone to see. (The software has vulnerabilities that leak user data).
  • High Quality: The payment is processed securely through an encrypted system.

3. Perceived Quality: “How does it feel?”

This is the emotional response the product generates.

• Example:
  • Low Quality: The ice cream is served in a dirty, chipped bowl. The server is rude. (The software has a poor, outdated design with spelling mistakes).
  • High Quality: The ice cream is served in a nice wafer cone with a chocolate flake on top. The shop is clean, and the server is friendly. (The software has a beautiful, modern UI with smooth animations and a helpful tone). The user feels good about their purchase.

The Manual Tester’s Role in Ensuring Quality

A manual tester’s job is to verify all these aspects of quality.

• For Functional Quality: They execute test cases to ensure “the chocolate ice cream is indeed chocolate and is given to the user.”
• For Non-Functional Quality: They explore the software to ask: “Is it easy to find the ‘order’ button? Does the page load quickly? Does it look right on my phone?”
• For Perceived Quality: They provide feedback like: “This error message is harsh and confusing. The color scheme is unpleasant. This workflow feels frustrating.”

Conclusion: Finding a bug where the software crashes (functional quality) is crucial. But a great manual tester also identifies issues that lead to a poor user experience (non-functional and perceived quality). True quality is achieved when the software not only works correctly but is also a pleasure to use.

What is a Defect in Manual Testing?

In manual testing, a defect (also called a bug or fault) is any imperfection, flaw, or deviation in the software application that causes it to behave in an unintended, unexpected, or incorrect way compared to its requirements or expected behavior.

The core idea is a variance between the actual result and the expected result.

---

4 Detailed Examples of Defects

Here are four common types of defects a manual tester might find, ranging from simple to complex.

Example 1: Functional Defect (Calculation Error)

• Scenario: Testing a calculator app.
• Expected Result: When a user enters 5 + 5 and presses =, the result should be 10.
• Actual Result: The app displays 55.
• The Defect: The “+” button is concatenating the numbers (behaving like a string operator) instead of performing mathematical addition. This is a clear functional failure.

Example 2: User Interface (UI) Defect

• Scenario: Testing an e-commerce website’s product page on a mobile phone.
• Expected Result: The “Add to Cart” button should be fully visible and clickable without needing to scroll sideways.
• Actual Result: The “Add to Cart” button is cut off on the right side of the screen, and the user cannot click it.
• The Defect: This is a UI or layout bug. The page is not responsive for a specific mobile screen size, directly preventing the user from making a purchase.

Example 3: Content Defect

• Scenario: Testing a banking application’s fund transfer confirmation screen.
• Expected Result: The confirmation message should be professional, clear, and free of spelling errors, e.g., “Your transfer of $100 to John Doe was successful.”
• Actual Result: The message says, “Your transfer of $100 to John Doe was sucessful.” (misspelling of “successful”).
• The Defect: This is a content or cosmetic bug. While it doesn’t break functionality, it looks unprofessional and can erode user trust in the application’s attention to detail.

Example 4: Logical / Workflow Defect

• Scenario: Testing a user registration flow.
• Expected Result: After successfully filling out a form and clicking “Submit,” the user should receive a “Registration Successful!” message and be logged into their new account.
• Actual Result: The user gets a “Registration Successful!” message but is then redirected back to the login page instead of being logged in automatically.
• The Defect: This is a logical or workflow bug. The individual steps work (form validation, saving data, showing message), but the overall sequence of events is incorrect and creates a poor user experience.

What a Tester Does Upon Finding a Defect:

A manual tester doesn’t just find the defect; they report it systematically. A good defect report includes:

1. Title: A short, clear summary (e.g., “Add to Cart button not visible on iPhone 12 screen”).
2. Description: Detailed explanation of the issue.
3. Steps to Reproduce: Exact steps to make the defect appear consistently.
4. Expected vs. Actual Result: As shown in the examples above.
5. Evidence: A screenshot or screen recording.
6. Severity & Priority: How bad the bug is and how quickly it needs to be fixed.

Finding these defects early is the primary goal of manual testing, as it saves the company time, money, and protects its reputation.

Product vs Project-Based Companies: Key Differences

 

The Core Difference (Simple Analogy)

• A Product-Based Company is like a chef who creates their own unique recipe (the product) and serves it to many customers.
• A Project-Based Company is like a caterer who is hired by different clients to create and manage events (the projects) based on the client’s specific menu and requirements.

---

What is a Product-Based Company?

A product-based company invests in building its own product or software. Its primary goal is to develop, market, sell, and support this product to its customers.

Examples:

• Tech Giants: Google (Search, Gmail, YouTube), Microsoft (Windows, Office 365), Apple (iOS, MacOS), Meta (Facebook, Instagram, WhatsApp), Adobe (Photoshop, Acrobat).
• Product Startups/SaaS Companies: Spotify, Netflix, Slack, Zoom, Canva, and thousands of smaller companies building a specific software product.

Key Characteristics:

• Owns the Product: The company has full ownership and control over its product’s vision, roadmap, and intellectual property.
• Focus on Innovation: The culture is heavily focused on innovation, user experience (UX), and creating the best possible product to win the market.
• End-Users are Customers: The people using the product are the company’s customers. The goal is to acquire and retain millions of them.
• Work Nature: Work is often organized in long-term cycles or sprints focused on releasing new features and versions (v1.0, v2.0) of the product.
• Scale: The product is built to serve a massive number of users simultaneously (scalability is a huge focus).

---

What is a Project-Based Company?

A project-based company (often called a service-based or client-servicing company) does not have its own product. Instead, it provides software services and solutions to other companies (clients) based on the client’s requirements.

Examples:

• IT Service Giants: TCS, Infosys, Wipro, Accenture, Capgemini, Cognizant, Deloitte.
• Digital Agencies & Consultancies: Companies that build websites, apps, or custom software for clients.

Key Characteristics:

• Works on Client Projects: The company works on projects defined and paid for by a client. The client owns the final software/product.
• Focus on Client Requirements: The culture is focused on meeting client specifications, delivering on time, and within the allocated budget.
• The Client is the Customer: The company’s primary relationship is with the client, not the end-user of the software.
• Work Nature: Work is organized into fixed-duration projects with a defined start and end date. Once the project is delivered, teams might be moved to a new project for a different client.
• Scope: The project’s scope is defined by a contract and a Statement of Work (SoW) from the client.

---

Comparison Table: Product vs. Project-Based Companies

Feature	Product-Based Company	Project-Based Company
Core Business	Develop and sell its own software product.	Provide software services and solutions to clients.
Ownership	Company owns the product and its IP.	The client owns the final deliverable and its IP.
Primary Goal	Product success, user growth, and market share.	Client satisfaction, project delivery (on time & budget).
Work Culture	Often more innovative, flexible, and creative.	Often more structured, process-driven (e.g., strict deadlines).
End-User	Millions of external users/customers.	The client’s employees or customers (you rarely interact with them).
Technology	Usually works on modern, cutting-edge tech stacks.	Technology is often dictated by the client’s existing systems.
Job Stability	Can be less stable; if the product fails, layoffs can happen.	Often more stable; income comes from multiple clients.
Learning	Deep, specialized knowledge in the product’s domain and tech.	Broad exposure to different technologies, domains, and clients.

Which is Better for a Manual Tester?

Both offer excellent opportunities, but the experience differs:

• In a Product Company:
  • Pros: You become a domain expert in your product. You deeply understand the user and work on improving the same product over years. You often have more freedom to suggest improvements and focus on UX. Compensation and perks are generally higher.
  • Cons: You might work on the same technology or product for a long time.
• In a Project Company:
  • Pros: You get exposure to various industries (e.g., banking, healthcare, retail) and different technologies by working on multiple client projects. It’s a fantastic way to build a broad skillset and resume quickly.
  • Cons: Work can be repetitive (e.g., lots of regression testing for a bank’s mainframe system). You may have less creative input, as requirements are strictly set by the client.

Conclusion: There’s no single “better” option. It depends on your career goals. If you love deep expertise and innovation, aim for a product role. If you want diverse experience and want to see how different businesses work, a project role is great. Many testers start in project-based companies to gain experience and then move to product-based companies.

What is Manual Testing? Key Skills, Career Path, and Why You Should Choose It

1. What is Manual Testing?

Manual Testing is the process of verifying and validating a software application manually (i.e., without using any automation tools or scripts) to ensure it behaves as expected and is free from defects.

A manual tester takes on the role of an end-user. They execute test cases step-by-step, using the application’s features to identify any unexpected behavior, bugs, or usability issues. The goal is to find as many defects as possible before the software is released to real users.

Key Characteristics:

• Human-Centric: Relies on the tester’s intuition, experience, and creativity.
• No Automation Scripts: Tests are executed by a person, not a program.
• Exploratory: Allows testers to go “off-script” to explore the application and find hidden bugs.
• UI and Usability Focus: Excellent for evaluating user experience, look-and-feel, and ease of use.

---

2. Why Do We Choose a Career in Manual Testing? (Why Choose a Career as a Manual Tester?)

There are many compelling reasons to choose a career in manual testing:

1. Foundation of Software Quality: It is the fundamental process for ensuring software quality. Every application is tested manually at some point, making it an always-relevant skill.
2. High Demand: The software industry is booming, and every software product needs testers. There is a constant demand for skilled manual testers in every sector.
3. Low Barrier to Entry: It is one of the most accessible ways to start a career in tech. You don’t need an advanced computer science degree or deep programming knowledge to begin.
4. Creative and Critical Thinking: It’s not just about following steps. It requires analytical thinking to design test cases and creativity to “break” the software and think of unusual user scenarios.
5. User Advocacy: You become the voice of the customer. You ensure the final product is not just functionally correct but also user-friendly and intuitive.
6. Stepping Stone to Other Roles: It’s a fantastic launchpad for careers in automation testing, performance testing, QA leadership, product management, or business analysis, as it gives you a deep understanding of the software development lifecycle (SDLC).

---

3. Key Skills Required for Manual Testing

To be a successful manual tester, you need a blend of technical, analytical, and soft skills.

A) Analytical Skills

• Analytical Thinking: The ability to break down complex software requirements into smaller, testable units.
• Attention to Detail: A keen eye to spot the smallest irregularities, like a misaligned button, a wrong color, or an incorrect error message.
• Critical Thinking: The ability to question “what if?” and think of scenarios that developers might not have considered.

B) Technical Skills

• Understanding of SDLC & STLC: Strong knowledge of the Software Development Life Cycle and the Software Testing Life Cycle (Requirements Analysis, Test Planning, Case Development, Test Execution, etc.).
• Writing Test Cases & Bug Reports: The ability to write clear, concise, and comprehensive test cases and detailed bug reports. A good bug report includes steps to reproduce, expected vs. actual results, and evidence (screenshots/videos).
• Test Design Techniques: Knowledge of techniques like Equivalence Partitioning, Boundary Value Analysis, Decision Table Testing, and State Transition Diagrams to create effective test cases with minimum effort.
• Basic SQL Knowledge: Ability to write simple SQL queries (SELECT, UPDATE, WHERE, JOIN) to verify data in the database backend.
• Basic Knowledge of APIs: Understanding what an API is and how to use tools like Postman or Swagger to test them manually is a huge plus.
• Familiarity with Testing Tools: Experience with tools like Jira, Trello (for bug tracking), TestRail, Zephyr (for test management), and browser DevTools.

C) Domain Knowledge

• Understanding the specific industry you are testing for (e.g., banking, healthcare, e-commerce) is a massive advantage. It helps you think like a real user.

D) Soft Skills

• Communication Skills: You must communicate clearly and effectively with developers (to report bugs) and with managers (to report progress). This is arguably the most important soft skill.
• ** Curiosity & a “Tester’s Mindset”:** A natural desire to explore, experiment, and ask questions. A good tester is naturally skeptical and doesn’t take “it works” for an answer.
• Patience and Persistence: Testing can be repetitive. It requires patience to execute hundreds of test cases and persistence to re-test a fixed bug multiple times.
• Time Management: The ability to prioritize testing efforts and meet deadlines.

Summary

Aspect	Description
What it is	Manually testing software without automation scripts to find bugs.
Why choose it	High demand, great entry into tech, creative, user-focused, and a strong career foundation.
Key Skills	Analytical thinking, attention to detail, test case design, bug reporting, basic SQL/API knowledge, and excellent communication.

Manual testing is a rewarding and critical field that ensures the software we use every day is reliable, functional, and enjoyable. It’s a career built on the principle of protecting the user from bad experiences.