DDL, DML, DQL Queries:

CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY, 
    Name VARCHAR(50), 
    Age INTEGER, 
    DepartmentID INTEGER, 
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

INSERT INTO Employees(empid, ename, age, deptid) VALUES(1, 'Kavi', 32, 101), (2, 'Sugi', 30, 102);

UPDATE Employees SET age=31 WHERE Name='Nila';

DELETE FROM Employees WHERE Name='Nila';

SELECT e.*, d.DepartmentName 
FROM Employees e 
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

SELECT e.EmpName AS Employee, m.EmpName AS Manager
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmpID;

INNER JOIN:

• Returns only the rows where there is a match between the columns in both tables.
• If no match is found, the row is not included in the result.
• It’s the most common type of join.

OUTER JOIN:

• Returns all rows from one or both tables, even if there is no match in the other table.
  • LEFT OUTER JOIN (or just LEFT JOIN): Returns all rows from the left table, and the matched rows from the right table. If no match, the result will have NULL values for columns from the right table.
  • RIGHT OUTER JOIN (or just RIGHT JOIN): Returns all rows from the right table, and the matched rows from the left table. If no match, the result will have NULL values for columns from the left table.
  • FULL OUTER JOIN: Returns all rows from both tables. If there is no match, the result will have NULL values for the non-matching table’s columns.

GROUP BY:

• Groups rows that have the same values in specified columns into summary rows (like finding the total count, sum, average, etc.).
• It is typically used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), MIN().

HAVING:

• Used to filter records after the GROUP BY has been applied.
• It works similarly to the WHERE clause, but WHERE is used for filtering individual rows before grouping, while HAVING filters the grouped results.

SELECT DeptName, COUNT(*)
FROM Employees
GROUP BY DeptName;

DISTINCT:

• Used to remove duplicate rows from the result set based on the specified columns.
• If you specify only one column, it will return the distinct values of that column.
• If you specify multiple columns, the combination of values in those columns will be considered to determine uniqueness.

SELECT DISTINCT DeptName FROM Employees;

SELECT DISTINCT DeptName, EmpName FROM Employees;

Difference between DELETE and TRUNCATE:

• Removes rows one by one and logs each deletion, which can be slower for large datasets.
• You can use a WHERE clause to specify which rows to delete.
• Can be rolled back if you’re working within a transaction (assuming no COMMIT has been done).
• Can fire triggers if there are any triggers defined on the table (for example, BEFORE DELETE or AFTER DELETE triggers).

TRUNCATE:

• Removes all rows in the table in one go, without scanning them individually.
• Does not support a WHERE clause, so it always deletes all rows.
• It’s much faster than DELETE because it doesn’t log individual row deletions (but it does log the deallocation of the table’s data pages).
• Cannot be rolled back in most databases (unless in a transaction, depending on the DBMS), and there are no triggers involved.

UNION:

• Combines the results of two or more queries.
• Removes duplicates: Only unique rows are included in the final result.
• It performs a sort operation to eliminate duplicates, which can have a slight performance cost.

UNION ALL:

• Also combines the results of two or more queries.
• Keeps duplicates: All rows from the queries are included in the final result, even if they are the same.
• It doesn’t perform the sort operation, which usually makes it faster than UNION.

SELECT EmpID, EmpName FROM Employees
UNION ALL
SELECT EmpID, EmpName FROM Contractors;

SELECT EmpID, EmpName FROM Employees
UNION 
SELECT EmpID, EmpName FROM Contractors;

COALESCE():

First Non null value will be taken, For ex. in select statement, some names are null, that time some default value can be used or another field value.
SELECT COALESCE(NULL, ‘Hello’, ‘World’);
Output: Hello

INSERT INTO users (name, nickname) VALUES
(‘Alice’, NULL),
(NULL, ‘Bob’),
(NULL, NULL);

SELECT id, COALESCE(name, nickname, ‘Unknown’) AS display_name FROM users;

NULLIF()

NULLIF(expression1, expression2)
Returns null if both expressions or column values are equal, else return first the first column value, ie expression1
SELECT NULLIF(10, 10); — Output: NULL
SELECT NULLIF(10, 20); — Output: 10
SELECT NULLIF(10, NULL) OR — Output: 10
SELECT NULLIF(NULL, 10) — Output: NULL

IF Condition:

The IF statement is used to check conditions and execute SQL code accordingly.

IF condition THEN
    -- Code to execute if the condition is true
ELSIF condition THEN
    -- Code block to execute if another condition is true
ELSE
    -- Code to execute if the condition is false
END IF;

IF NOT FOUND THEN
    RAISE NOTICE 'Employee with ID % not found!', emp_id;
    emp_bonus := 0;
END IF;

CASE WHEN:

The CASE WHEN expression is used for conditional logic within a query (similar to IF but more flexible in SQL).

SELECT 
    name,
    salary,
    CASE 
        WHEN salary > 5000 THEN 'High Salary'
        WHEN salary BETWEEN 3000 AND 5000 THEN 'Average Salary'
        ELSE 'Low Salary'
    END AS salary_category
FROM employees;

FOR LOOP:

DECLARE 
    i INT;
BEGIN
    FOR i IN 1..5 LOOP
        -- Perform an action for each iteration (e.g., insert or update a record)
        INSERT INTO audit_log (action, timestamp) 
        VALUES ('Employee update', NOW());
    END LOOP;
END;

FOR record IN SELECT column1, column2 FROM employees LOOP
-- Code block using record.column1, record.column2
END LOOP;

RAISE – used for printing something (SOP in java)

RAISE NOTICE ‘Employee: %, Salary: %’, emp_name, emp_salary;
RAISE EXCEPTION ‘An error occurred: %’, error_message; — This will print and halt the execution.
RAISE INFO ‘Employee: %, Salary: %’, emp_name, emp_salary;

Stored Procedures in SQL:

A stored procedure is a reusable block of SQL code that performs specific tasks. It is stored in the database and can be called as needed. Stored procedures are used for:

• Modularizing complex SQL logic.
• Improving performance by reducing network traffic.
• Ensuring code reuse and security (by granting permissions to execute rather than to the tables directly).

Example:

A stored procedure to insert a new employee record:

CREATE PROCEDURE add_employee(emp_name VARCHAR, emp_salary NUMERIC)
LANGUAGE plpgsql AS 
$$ 
BEGIN 
  INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary); 
END; 
$$;

Execution:

CALL add_employee(‘John Doe’, 50000);

Functions in SQL:

A SQL function is a reusable block of SQL code that performs specific tasks. It is stored in the database and can be called as needed. It is similar to a procedure but returns a single value or table. Functions are typically used for computations or transformations.
Example: A function to calculate the yearly salary:

CREATE FUNCTION calculate_yearly_salary(monthly_salary NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql AS 
$$
BEGIN
  RETURN monthly_salary * 12;
END;
$$;

Execution:

SELECT calculate_yearly_salary(5000); OR EXECUTE calculate_yearly_salary(5000); (If we are using inside a trigger)

Key Differences Between Procedures and Functions:

Return Type:

• Function: Always returns a value.
• Procedure: Does not return a value.

Usage:

• Function: Can be used in SQL queries (e.g., SELECT).
• Procedure: Called using CALL, cannot be used in SQL queries.

Transaction Control:

• Function: Cannot manage transactions.
• Procedure: Can manage transactions (e.g., COMMIT, ROLLBACK).

Side Effects:

• Function: Should not have side effects (e.g., modifying data).
• Procedure: Can modify data and have side effects.

Calling Mechanism:

Procedure: Called using CALL procedure_name().

Function: Called within SQL expressions, like SELECT function_name().

TRIGGER:

A trigger is a special kind of stored procedure that automatically executes (or “fires”) when certain events occur in the database, such as INSERT, UPDATE, or DELETE. Triggers can be used to enforce business rules, validate data, or maintain audit logs.
Key Points:

Types of Triggers:

• BEFORE Trigger: Fires before the actual operation (INSERT, UPDATE, DELETE).
• AFTER Trigger: Fires after the actual operation.
• INSTEAD OF Trigger: Used to override the standard operation, useful in views. (This is in SQL Server only not in postgres)

• Trigger Actions: The trigger action can be an operation like logging data, updating related tables, or enforcing data integrity.
• Trigger Events: A trigger can be set to fire on certain events, such as when a row is inserted, updated, or deleted.
• Trigger Scope: Triggers can be defined to act on either a row (executing once for each affected row) or a statement (executing once for the entire statement).
• A trigger can be created to log changes in a Users table whenever a record is updated, or it could prevent deleting a record if certain conditions aren’t met.

Example:

CREATE TRIGGER LogEmployeeAgeUpdate
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.Age <> NEW.Age THEN
        INSERT INTO EmployeeLogs (EmployeeID, OldAge, NewAge)
        VALUES (OLD.EmployeeID, OLD.Age, NEW.Age);
    END IF;
END;

Example:

CREATE OR REPLACE FUNCTION prevent_employee_delete()
RETURNS TRIGGER AS 
$$
BEGIN
-- Check if the employee is in a protected department (for example, department_id = 10)
  IF OLD.department_id = 10 THEN
     RAISE EXCEPTION 'Cannot delete employee in department 10';
  END IF;
  RETURN OLD;
END;
$$ 
LANGUAGE plpgsql;

-- Attach the function to a trigger
CREATE TRIGGER prevent_employee_delete_trigger
BEFORE DELETE ON Employees
FOR EACH ROW
EXECUTE FUNCTION prevent_employee_delete();

Creates a trigger which is used to log age and related whenever insert, delete, update action on employee rows:

CREATE OR REPLACE FUNCTION log_employee_changes()
RETURNS TRIGGER AS 
$$
BEGIN
-- Handle INSERT operation
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO EmployeeChangeLog (EmployeeID, OperationType, NewAge,    ChangeTime)
    VALUES (NEW.EmployeeID, 'INSERT', NEW.Age, CURRENT_TIMESTAMP);
    RETURN NEW;
     -- Handle UPDATE operation
  ELSIF (TG_OP = 'UPDATE') THEN
    INSERT INTO EmployeeChangeLog (EmployeeID, OperationType, OldAge, NewAge, ChangeTime)
    VALUES (OLD.EmployeeID, 'UPDATE', OLD.Age, NEW.Age,  CURRENT_TIMESTAMP);
    RETURN NEW;
  -- Handle DELETE operation
  ELSIF (TG_OP = 'DELETE') THEN
    INSERT INTO EmployeeChangeLog (EmployeeID, OperationType, OldAge, ChangeTime)
    VALUES (OLD.EmployeeID, 'DELETE', OLD.Age, CURRENT_TIMESTAMP);
    RETURN OLD;
  END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER log_employee_changes_trigger
AFTER INSERT OR UPDATE OR DELETE 
ON Employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_changes();

Step 3: Attach the Trigger to the Employees Table

Now that we have the function, we can attach it to the Employees table to log changes. We’ll create a trigger that fires on insert, update, and delete operations.

TG_OP: This is a special variable in PostgreSQL that holds the operation type (either INSERT, UPDATE, or DELETE).
NEW and OLD: These are references to the row being inserted or updated (NEW) or the row before it was updated or deleted (OLD).
EmployeeChangeLog: This table stores the details of the changes (employee ID, operation type, old and new values, timestamp). – Programmer defined.

What happens when you omit FOR EACH ROW?

1. Statement-Level Trigger: The trigger will fire once per SQL statement, regardless of how many rows are affected. This means it won’t have access to the individual rows being modified.
   • For example, if you run an UPDATE statement that affects 10 rows, the trigger will fire once (for the statement) rather than for each of those 10 rows.
2. No Access to Row-Specific Data: You won’t be able to use OLD or NEW values to capture the individual row’s data. The trigger will just execute as a whole, without row-specific actions.
3. With FOR EACH ROW: The trigger works on each row affected, and you can track specific changes (e.g., old vs new values).Without FOR EACH ROW: The trigger fires once per statement and doesn’t have access to specific row data.

CREATE TRIGGER LogEmployeeAgeUpdate
AFTER UPDATE ON Employees
BEGIN
    -- Perform some operation, but it won't track individual rows.
    INSERT INTO AuditLogs (EventDescription)
    VALUES ('Employees table updated');
END;

NORMALIZATION:

1st NF:

1. Each column/attribute should have atomic value or indivisible value, ie only one value.
2. Rows should not be repeated, ie unique rows, there is not necessary to have PKey here.

2nd NF:

1. Must fulfill the 1st NF. [cadidate key(composite key to form the uniqueness)]
2. All non-candidate-key columns should be fully dependent on the each attribute/column of the composite keys to form the cadidate key. For ex. If the DB is in denormalalized form (ie before normalization, all tables and values are together in a single table) and the candidate key is (orderId+ProductId), then the non-key(not part of the candidate key) if you take orderdate, orderedStatus, qty, item_price are not dependent on each part of the candidate key ie it depends only orderId, not ProductId, ProductName are not dependent on Order, like that customer details are not dependent on ProductId. So only related items should be there in a table, so the table is partitioned based on the column values, so that each attribute will depend on its candidate key.
   So Products goto separate table, orders separate and customers going to separate table.
3. Primary key is created based for each separated table and ensure that all non-key columns completely dependent on the primary key. Then the foreign key relationships also established to connect all the tablesis not fullly dependent on.

3rd NF:

1. Must fulfill till 2ndNF.
2. Remove the transitional dependency (In a decentralized DB, One column value(Order ID) is functionally dependent on another column(Product ID) and OrderId is functionally dependent on the OrderId, so that disturbing one value will affect another row with same column value), so to avoid that separate the table, for Ex. from orders table Sales People’s data is separated.

What is a Transitive Dependency? Let’s break this down with a simple example:
StudentID Department HODName
S001 IT Dr. Rajan
S002 CS Dr. Priya

Primary Key: StudentID
Non-prime attributes: Department, HODName

StudentID → Department (StudentID determines the department).
Department → HODName (Department determines the HOD name). It should be like StudentID only should determine HOD, not the dept. HODName depends indirectly on StudentID through Department.

This is a transitive dependency, and we need to remove it.

A transitive dependency means a non-prime attribute (not part of the candidate key) depends indirectly on the primary key through another non-prime attribute.

Reference: https://www.youtube.com/watch?v=rBPQ5fg_kiY and Learning with the help of chatGPT

Query Optimization:

Query Optimization is the process of improving the performance of a SQL query by reducing the amount of time and resources (like CPU, memory, and I/O) required to execute the query. The goal is to retrieve the desired data as quickly and efficiently as possible.

Important implementation of Query Optimization:

1. Indexing: Indexes on frequently used columns: As you mentioned, indexing columns that are part of the WHERE, JOIN, or ORDER BY clauses can significantly improve performance. For example, if you’re querying a salary column frequently, indexing it can speed up those queries.
   Composite indexes: If a query filters by multiple columns, a composite index on those columns might improve performance. For instance, INDEX (first_name, last_name) could be more efficient than two separate indexes on first_name and last_name.
2. Instead of SELECT * FROM, can use the required columns and use of LIMIT for the required no. of rows.
3. Optimizing JOIN Operations: Use appropriate join types: For example, avoid OUTER JOIN if INNER JOIN would suffice. Redundant or unnecessary joins increase query complexity and processing time.
4. Use of EXPLAIN to Analyze Query Plan:
   Running EXPLAIN before a query allows you to understand how the database is executing it. You can spot areas where indexes are not being used, unnecessary full table scans are happening, or joins are inefficient.

How to Implement Query Optimization:

1. Use Indexes:

• Create indexes on columns that are frequently queried or used in JOIN, WHERE, or ORDER BY clauses. For example, if you frequently query a column like user_id, an index on user_id will speed up lookups. Use multi-column indexes for queries involving multiple columns.
• CREATE INDEX idx_user_id ON users(user_id);

2. Rewrite Queries:

• Avoid using SELECT * and instead select only the necessary columns.
• Break complex queries into simpler ones and use temporary tables or Common Table Expressions (CTEs) if needed.
• SELECT name, age FROM users WHERE age > 18;

3. Use Joins Efficiently:

• Ensure that you are using the most efficient join type for your query (e.g., prefer INNER JOIN over OUTER JOIN when possible).
• Join on indexed columns to speed up the process.

4. Optimize WHERE Clauses:

• Make sure conditions in WHERE clauses are selective and reduce the number of rows as early as possible.
• Use AND and OR operators appropriately to filter data early in the query.

5. Limit the Number of Rows:

• Use the LIMIT clause when dealing with large datasets to fetch only a required subset of data.
• Avoid retrieving unnecessary data from the database.

6. Avoid Subqueries When Possible:

• Subqueries can be inefficient because they often lead to additional scans of the same data. Use joins instead of subqueries when possible.
• If you must use subqueries, try to write them in a way that they don’t perform repeated calculations.

7. Analyze Execution Plans:

• Use EXPLAIN to see how the database is executing your query. This will give you insights into whether indexes are being used, how tables are being scanned, etc.
• Example:

1. EXPLAIN SELECT * FROM users WHERE age > 18;

8. Use Proper Data Types:

1. Choose the most efficient data types for your columns. For instance, use INTEGER for numeric values rather than VARCHAR, which takes more space and requires more processing.

9. Avoid Functions on Indexed Columns:

1. Using functions like UPPER(), LOWER(), or DATE() on indexed columns in WHERE clauses can prevent the database from using indexes effectively.
2. Instead, try to perform transformations outside the query or ensure indexes are used.

10. Database Configuration:

1. Ensure the database system is configured properly for the hardware it’s running on. For example, memory and cache settings can significantly affect query performance.

Example of Optimized Query:

Non-Optimized Query:

SELECT * FROM orders
WHERE customer_id = 1001
AND order_date > '2023-01-01';

This query might perform a full table scan if customer_id and order_date are not indexed.

Optimized Query:

CREATE INDEX idx_customer_order_date ON orders(customer_id, order_date);

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 1001
AND order_date > '2023-01-01';

In this optimized version, an index on customer_id and order_date helps the database efficiently filter the rows without scanning the entire table.

Reference : Learnt from ChatGPT

The order of execution in a SQL query:

FROM and/or JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT nad/or OFFSET

Command Types:

References : Aysha Beevi

CAST()

CAST is used to typecast or we can use ::target data type.

SELECT ‘The current date is: ‘ || CURRENT_DATE::TEXT;
SELECT ‘2024-12-21’::DATE::TEXT;
SELECT CAST(‘2024-12-21’ AS DATE);

|| –> Concatenation operator

DATE functions:

SELECT CURRENT_DATE; — Output: 2024-12-21
SELECT CURRENT_TIME; — Output: 09:15:34.123456+05:30
SELECT NOW(); — Output: 2024-12-21 09:15:34.123456+05:30
SELECT AGE(‘2020-01-01’, ‘2010-01-01’); — Output: 10 years 0 mons 0 days
SELECT AGE(‘1990-05-15’); — Output: 34 years 7 mons 6 days (calculated from NOW())
SELECT EXTRACT(YEAR FROM NOW()); — Output: 2024
SELECT EXTRACT(MONTH FROM CURRENT_DATE); — Output: 12
SELECT EXTRACT(DAY FROM TIMESTAMP ‘2024-12-25 10:15:00’); — Output: 25

The DATE_TRUNC() function truncates a date or timestamp to the specified precision. This means it “resets” smaller parts of the date/time to their starting values.
SELECT DATE_TRUNC(‘month’, TIMESTAMP ‘2024-12-21 10:45:30’);
— Output: 2024-12-01 00:00:00 –> The ‘month’ precision resets the day to the 1st, and the time to 00:00:00.
SELECT DATE_TRUNC(‘year’, TIMESTAMP ‘2024-12-21 10:45:30’);
— Output: 2024-01-01 00:00:00
SELECT DATE_TRUNC(‘day’, TIMESTAMP ‘2024-12-21 10:45:30’);
— Output: 2024-12-21 00:00:00

SELECT NOW() + INTERVAL ‘1 year’;
— Output: Current timestamp + 1 year
SELECT CURRENT_DATE – INTERVAL ’30 days’;
— Output: Today’s date – 30 days
SELECT NOW() + INTERVAL ‘2 hours’;
— Output: Current timestamp + 2 hours
SELECT NOW() + INTERVAL ‘1 year’ + INTERVAL ‘3 months’ – INTERVAL ’15 days’;

Window Functions

This is the function that will operate over the specified window. Common window functions include ROW_NUMBER(), RANK(), SUM(), AVG(), etc

.PARTITION BY: (Optional) Divides the result set into partitions to which the window function is applied. Each partition is processed separately.ORDER BY: (Optional) Orders the rows in each partition before the window function is applied.

window_function() OVER (--RANK() or SUM() etc. can come in window_function
    PARTITION BY column_name(s)
    ORDER BY column_name(s)
 );

SELECT 
    department_id,
    employee_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_total
FROM employees;

CURSOR:

DO $$
DECLARE
emp_name VARCHAR;
emp_salary DECIMAL;
emp_cursor CURSOR FOR SELECT name, salary FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_name, emp_salary;
EXIT WHEN NOT FOUND; — Exit the loop when no rows are left
RAISE NOTICE ‘Employee: %, Salary: %’, emp_name, emp_salary;
END LOOP;
CLOSE emp_cursor;

Basic Data Types in PostgreSQL

TEXT, VARCHAR, CHAR: Working with strings.
INTEGER, BIGINT, NUMERIC: Handling numbers.
DATE, TIMESTAMP: Date and time handling.

OVER CLAUSE

In PostgreSQL, the OVER() clause is used in window functions to define a window of rows over which a function operates. Just create a serial number (Row_number) from 1 (Rows are already ordered by salary desc)
SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees
WHERE row_num <= 5;

RANK()

Parition the table records based on the dept id, then inside each partition order by salary desc with rank 1,2,3… – In RANK() if same salary then RANK repeats.

SELECT department_id, name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
Output:
department_id name salary rank
101 Charlie 70,000 1
101 Alice 50,000 2
101 Frank 50,000 2
102 Eve 75,000 1
102 Bob 60,000 2
103 David 55,000 1

• Divides employees into 3 equal salary buckets (quartiles).
  SELECT id, name, salary,
  NTILE(3) OVER (ORDER BY salary DESC) AS quartile
  FROM employees;
  id name salary quartile
  5 Eve 75,000 1
  3 Charlie 70,000 1
  2 Bob 60,000 2
  4 David 55,000 2
  1 Alice 50,000 3
  6 Frank 50,000 3
• Retrieves the first name in each department based on descending salary.
  SELECT department_id, name, salary,
  FIRST_VALUE(name) OVER (PARTITION BY department_id ORDER BY salary DESC) AS top_earner
  FROM employees;
  Output:
  department_id name salary top_earner
  101 Charlie 70,000 Charlie
  101 Alice 50,000 Charlie
  101 Frank 50,000 Charlie
  102 Eve 75,000 Eve
  102 Bob 60,000 Eve
  103 David 55,000 David

First from table will be taken, then WHERE condition will be applied

• In the WHERE clause directly you cannot call the RANK(), it should be stored in result set, from there only we can call it. So only RANK() will get executed ie Windows CTE (Common Table Expression), that’s why first the CTE will get executed and stored in a temp result set, then SELECT from that result set.
• Below we gave in the subquery, so it will get executed and then that value is getting used by the outer query.

In each dept top earner name with his name and salary (consider the above table employees)
SELECT department_id, name, salary
FROM (
SELECT department_id, name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
) ranked_employees
WHERE rank = 1;

department_id name salary
101 Charlie 70,000
102 Eve 75,000
103 David 55,000

Resultset – here RankedSalaries is Resultset

WITH RankedSalaries AS (
SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
)
SELECT salary
FROM RankedSalaries WHERE rank = 2;

Here, RankedSalaries is a temporary result set or CTE (Common Table Expression)

Reference: Learnt from ChatGPT and Picture from Ms.Aysha

What is Spring Framework?
Before we proceed with the definition let’s first understand what is framework.

Framework in software industry, is an environment where we can see collection of reusable software components or tools which are used to build the applications more efficiently with minimal code and time. It makes the developers life easier.

For example, If we are going to travel and stay in some place: furnished flat will be preferable than setting-up a new home. All available ready-made to pick-up and use. Another example LibreOffice Draw where you can draw, paint or creating logo. Here We will have set of drawing tools, just pick up and use it.

Definition:
Spring is a comprehensive framework, provides a broad set of tools and solutions for almost all kind of application development, whether you are building a small standalone application or a complex enterprise system, in particular web applications.

What is Spring Boot?
Spring Boot is a layer on top of Spring that simplifies application development, making the developer to focus mostly on the business logic and leaving all boiler plate codes to the spring boot framework.

Spring vs SpringBoot:
The main difference is in Spring the developer have the higher responsibility (or must be an Advanced Developer) to handle all work step by step (obviously will take more time) whereas with SpringBoot, we can do the same stuff very easily, quickly and safely (We can do with Spring also, but here SpringBoot will takes care of lot of tasks and minimize the coder’s work)

Ex. Spring – Birthday party event arranging by parents. (Each activity should be taken care of, like venue, invitation, cakes, decoration, food arrangements, return gifts etc.)

Spring Boot – An event organizer will take care of everything, so the parents just concentrate on the child and guests (like business logic) – whatever they want event organizer(spring boot) will assist by providing it.

What are Spring Boot’s Advantages?
Spring Boot is a layer on top of Spring that simplifies application development by providing the following:

1. Faster Setup (Based on the dependencies and annotations).
2. Simplifies development by Auto Configuration, application.properties or application.yml
3. Embedded web servers with our finished product (.jar/.war)-eliminates the need for an external server like Tomcat to run the application, during deployment.
4. Production-Ready features (Ex. Health checks-monitor application’s health, logging etc.)
5. Simplified Deployment.
6. Opinionated defaults. (TBD)
7. Security features.
8. Community and Ecosystem

Spring Framework’s main advantages are,
– Inversion of Control
– Dependency Injection

IoC (Inversion of Control):
The core principle of the Spring Framework. Usually the program flow or developer will control the execution here as the name suggests, control reversed, ie framework controls the flow. Ex. The event organizer having everything whatever the party or parent needs. It makes the developers with minimal code and better organized.

It makes everything ready to build the application instead searching or creating whenever required . My understanding here is,

1. It scans the dependencies – based on that creates the required bean, checks the required .jar files are available in the class path.
2. Through dependency injection passing beans as parameter to another bean when @Autowired detected.

Spring Boot starts and initializes the IoC container (via ApplicationContext – its the container for all beans).IoC scans the classpath for annotated classes like @Component, @Service, @Controller, @Repository.It creates beans (objects) for those classes and makes them available for dependency injection.Spring Boot scans application.properties or application.yml, applying those configurations to the beans or the application as needed.

Dependency Injection (DI):
–A design pattern that reduces the connection between the system components making the code more modular, maintainable and testable. It avoids the tight coupling between the classes and make the classes loosely coupled.

Coupling here is one class depends on another class.

For ex. In the same birthday party, if the parents arranged the setup one one theme (Dora-Bujju) for the kid and later the kid changed its mind set and asking for another theme (Julie – Jackie Chan). Now its a wastage of time and money, and parent’s frustration also. Instead, if they tell the organizer to change the theme (as its their work and having some days also) – its easily getting updated.

In Dependency Injection, we can consider like one class wants to use another class, then it should not use its object (bean) directly inside the body (Tight coupling). Future modification is getting tougher here, instead, just pass the bean (object) as a parameter (injecting that bean) into the required class (Constructor DI). In case if the injected bean (passed object as a parameter) wants to get changed in the future, just replace it with another bean(object) in the parameter section.

To Be Continued…

Reference: https://docs.spring.io/spring-framework/docs/3.2.x/spring-framework-reference/html/mvc.html

What is plaintext in my point of view:
Its simply text without any makeup or add-on, it is just an organic content. For example,

• A handwritten grocery list what our mother used to give to our father
• A To-Do List
• An essay/composition writing in our school days

Why plaintext is important?
– The quality of the content only going to get score here: there is no marketing by giving some beautification or formats.
– Less storage
– Ideal for long term data storage because Cross-Platform Compatibility
– Universal Accessibility. Many s/w using plain text for configuration files (.ini, .conf, .json)
– Data interchange (.csv – interchange data into databases or spreadsheet application)
– Command line environments, even in cryptography.
– Batch Processing: Many batch processes use plain text files to define lists of actions or tasks that need to be executed in a batch mode, such as renaming files, converting data formats, or running programs.

So plain text is simple, powerful and something special we have no doubt about it.

What is IRC?
IRC – Internet Relay Chat is a plain text based real time communication System over the internet for one-on-one chat, group chat, online community – making it ideal for discussion.

It’s a popular network for free and open-source software (FOSS) projects and developers in olden days. Ex. many large projects (like Debian, Arch Linux, GNOME, and Python) discussion used. Nowadays also IRC is using by many communities.

Usage :
Mainly a discussion chat forum for open-source software developers, technology, and hobbyist communities.

Why IRC?
Already we have so many chat platforms which are very advanced and I could use multimedia also there: but this is very basic, right? So Why should I go for this?

Yes it is very basic, but the infrastructure of this IRC is not like other chat platforms. In my point of view the important differences are Privacy and No Ads.

Advantages over other Chat Platforms:

• No Ads Or Popups: We are not distracted from other ads or popups because my information is not shared with any companies for tracking or targeted marketing.
• Privacy: Many IRC networks do not require your email, mobile number or even registration. You can simply type your name or nick name, select a server and start chatting instantly. Chat Logs also be stored if required.
• Open Source and Free: Server, Client – the entire networking model is free and open source. Anybody can install the IRC servers/clients and connect with the network.
• Decentralized : As servers are decentralized, it could able to work even one server has some issues and it is down. Users can connect to different servers within the same network which is improving reliability and performance.
• Low Latency: Its a free real time communication system with low latency which is very important for technical communities and time sensitive conversations.
• Customization and Extensibility: Custom scripts can be written to enhance functionality and IRC supports automation through bots which can record chats, sending notification or moderating channels, etc.
• Channel Control: Channel Operators (Group Admin) have fine control over the users like who can join, who can be kicked off.
• Light Weight Tool: As its light weight no high end hardware required. IRC can be accessed from even older computers or even low powered devices like Rasberry Pi.
• History and Logging: Some IRC Servers allow logging of chats through bots or in local storage.

Inventor
IRC is developed by Jarkko Oikarinen (Finland) in 1988.

Some IRC networks/Servers:
Libera.Chat(#ubuntu, #debian, #python, #opensource)
EFNet-Eris Free Network (#linux, #python, #hackers)
IRCnet(#linux, #chat, #help)
Undernet(#help, #anime, #music)
QuakeNet (#quake, #gamers, #techsupport)
DALnet- for both casual users and larger communities (#tech, #gaming, #music)

Some Clients-GUI
HexChat (Linux, macOS, Windows)
Pidgin (Linux, Windows)
KVIrc (Linux, Windows, macOS)

Some IRC Clients for CLI (Command Line Interface) :
WeeChat
Irssi

IRC Clients for Mobile :
Goguma
Colloquy (iOS)
LimeChat (iOS)
Quassel IRC (via Quassel Core) (Android)
AndroIRC (Android)

Directly on the Website – Libera WebClient – https://web.libera.chat/gamja/ You can click Join, then type the channel name (Group) (Ex. #kaniyam)

How to get Connected with IRC:
After installed the IRC client, open.
Add a new network (e.g., “Libera.Chat”).
Set the server to irc.libera.chat (or any of the alternate servers above).
Optionally, you can specify a port (default is 6667 for non-SSL, 6697 for SSL).
Join a channel like #ubuntu, #python, or #freenode-migrants once you’re connected.

Popular channels to join on libera chat:
#ubuntu, #debian, #python, #opensource, #kaniyam

Local Logs:
Logs are typically saved in plain text and can be stored locally, allowing you to review past conversations.
How to get local logs from our System (IRC libera.chat Server)
folders – /home//.local/share/weechat/logs/ From Web-IRCBot History:
https://ircbot.comm-central.org:8080

References:
https://kaniyam.com/what-is-irc-an-introduction/
https://www.youtube.com/watch?v=CGurYNb0BM8

Our daily meetings :
You can install IRC client, with the help of above link, can join.
Timings : IST 8pm-9pm
Server : libera.chat
Channel : #kaniyam

ALL ARE WELCOME TO JOIN, DISCUSS and GROW

TASKS:

• // 1. Reverse an ArrayList without using inbuilt method
• // 2. Find Duplicate Elements in a List
• // 3. Alphabetical Order and Ascending Order (Done in ArrayList)
• // 4. Merge Two Lists and Remove Duplicates
• // 5. Removing Even Nos from the List
• // 6. Array to List, List to Array

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;


public class CollectionsInJava {
	public static void main(String[] args) {
		// 1. Reverse an ArrayList without using inbuilt method
		// 2. Find Duplicate Elements in a List 
		// 3. Alphabetical Order and Ascending Order (Done in ArrayList)
		// 4. Merge Two Lists and Remove Duplicates
		// 5. Removing Even Nos from the List
		// 6. Array to List, List to Array
		ArrayList<String> names = new ArrayList<>(Arrays.asList("Abinaya", "Ramya", "Gowri", "Swetha",  "Sugi", "Anusuya", "Moogambigai","Jasima","Aysha"));
		ArrayList<Integer> al2 = new ArrayList<>(Arrays.asList(100,90,30,20,60,40));
		
		ArrayList<Integer> al =  insertValuesIntoAL();
		System.out.println("Before Reversing ArrayList="+ al);
		System.out.println("Reversed ArrayList="+ reverseArrayList(al));
		
		System.out.println("Duplicates in ArrayList="+findDuplicates(al));
		
		System.out.println("Before Order = "+names);
		Collections.sort(names);
		System.out.println("After Alphabetical Order = " + names);
		Collections.sort(al);
		System.out.println("Ascending Order = "+ al);
		
		System.out.println("List -1 = "+al);
		System.out.println("List -2 = "+al2);
		System.out.println("After Merging and Removing Duplicates="+mergeTwoLists(al,al2));
		System.out.println("After Removing Even Nos fromt the List-1 = "+removeEvenNos(al));
		
		arrayToListViceVersa(al,new int[] {11,12,13,14,15}); //Sending ArrayList and anonymous array
	}
	
	// 1. Reverse an ArrayList without using inbuilt method
	private static ArrayList<Integer> reverseArrayList(ArrayList<Integer> al) {
		int n=al.size();
		int j=n-1, mid=n/2;
		for (int i=0; i<mid; i++) {
			int temp = al.get(i);
			al.set(i, al.get(j));
			al.set(j--, temp);
		}
		return al;
	}
	
	// 2. Find Duplicate Elements in a List 
	private static ArrayList<Integer> findDuplicates(ArrayList<Integer> al) {
		HashSet<Integer> hs = new HashSet<>();
		ArrayList<Integer> arl = new ArrayList<>();
		for (int ele:al) {
			if (!hs.add(ele)) arl.add(ele);
		}
		return arl;
	}
	
	//4. Merge Two Lists into one and Remove Duplicates
	private static HashSet<Integer> mergeTwoLists(ArrayList<Integer> arl1,ArrayList<Integer> arl2) {
		ArrayList<Integer> resAl = new ArrayList<>();
		HashSet<Integer> hs = new HashSet<>();
		hs.addAll(arl1);
		hs.addAll(arl2);
		return hs;
	}
	
	// 5. Removing Even Nos from the List
	private static ArrayList<Integer> removeEvenNos(ArrayList<Integer> al) {
		ArrayList<Integer> res = new ArrayList<>();
		Iterator itr = al.iterator();
		while (itr.hasNext()) {
			int ele = (int)itr.next();
			if (ele%2==1) res.add(ele);
		}
		return res;
	}
	
	// 6. Array to List, List to Array
	private static void arrayToListViceVersa(ArrayList<Integer> arl, int[] ar) {
		Integer arr[] = arl.toArray(new Integer[0]);
		System.out.println("Convert List to Array = " + Arrays.toString(arr));
		List<Integer> lst = Arrays.asList(arr);
		System.out.println("Convert Array to List = " +  lst);
	}
	
	private static ArrayList<Integer> insertValuesIntoAL() {
		Integer[] ar = {30,40,60,10,94,23,05,46, 40, 94};
		ArrayList<Integer> arl = new ArrayList<>();
		Collections.addAll(arl, ar);
		//Collections.reverse(al);   //IN BUILT METHOD
		return arl;
			//Arrays.sort(ar);  
		//List lst = Arrays.asList(ar);    //TBD
		//return new ArrayList<Integer>(lst);
		
	}

}

OUTPUT:

What is Exception?

An Exception is an unexpected or unwanted event which occurs during the execution of a program that typically disrupts the normal flow of execution.

This is definition OK but what I understand : The program’s execution is getting stopped abnormally when it reaches some point which have some mistake/error – it may be small or big, compilation or runtime or logical mistake. And its not proceeding with further statements. Handling this situation is called exception handling. Cool. Isn’t it?

Why Exception Handling?

If we do not handle the exception program execution will stop. To make the program run smoothly and avoid stopping due to minor issues/exceptions, we should handle it.

So, how it is represented, In Java everything is class, right? The derived classes of java.lang.Throwable are Error and Exception. For better understanding lets have a look at the hierarchical structure.

Here I could see Exception and Error – when we hear these words looks similar right. So What could be the difference?

Errors are some serious issues which is beyond our control like System oriented errors. Ex. StackOverFlowError, OutOfMemoryError (Lets discuss this later).

Exception is a situation which we can handle,

1. through try-catch-(finally) block of code
2. Throws keyword in method signature.

try-catch-Finally :

What is the meaning? As we are the owner of our code, we do have some idea about the possible problems or exceptions which we can solve or handle through this try-catch block of code.

For Ex. Task : Make a Tasty Dish.

What could be the exceptions?

1. Some spice added in lower quantity.
2. Chosen vessel may be smaller in size
3. some additional stuff may not be available

To overcome these exception we can use try-catch block.

try {
  Cooking_Process();
}
catch(VesselException chosenLittle) {
   Replace_with_Bigger_One();
}
catch(QtyException_Spice spiceLow) {
   add_Little_More_Spice();
}
catch(AddOnsException e) {
  ignore_Additional_Flavors_If_Not_Available();
}
catch(Exception e) {
  notListedIssue();
}
Finally {
  cleanUp_Kitchen();
}

Here there could be more catches for one try as one task may encounter many different issues. If it is solvable its called exception and we try to catch in catch blocks. The JVM process our code (cookingProcess) and if it encounter one problem like QtyException_Spice, it will throw the appropriate object. Then it will be caught by the corresponding catch, which will execute add_Little_More_Spice() and prevent the code from failing.

Here we see one more word, Exception, which is the parent class of all exceptions. Sometimes we may encounter the issue that is not listed (perhaps forgotten) but its solvable. In such cases, we can use the parent class object (since a parent class object can act as a child object) to catch any exception that is not listed.

Fine, all good. But what is the purpose of Finally here? Finally is the block of code that will always be executed, no matter if exception occurs or not. It doesn’t matter if you made a good dish or a bad one, but the kitchen must be cleaned. The same applies here: the finally block is used for releasing system resources that were mainly used (Ex. File). However, we can also write our own code in the finally block based on the specific requirements.

We have a situation where you have one cylinder to cook, and it gets emptied during cooking, so we cannot proceed. This will fail our process TastyDish, this situation cannot be handled immediately. This is called Error. Now lets recall the definition “Errors are serious issues that are beyond our control like a system crash or resource limitations.” Now we could understand, right?

Ex. OutOfMemoryError – when we load too much data, JVM runs out of memory. StackOverFlowError – when an infinite loop or recursion without base condition will make the stack overflow.

Lets revisit exceptions – they can be classified into two categories:

• Checked Exception
• UnChecked Exception.

What is Checked Exceptions?

Checked Exception is the exception which occurs at compile time. It will not allow you to run the code if you are not handling through try-catch or declares throws method.

Lets get into deeper for the clear understanding, the compiler predicts/doubts the part of our code which may throw the exceptions/mistakes which lead to stopping the execution. So that it will not allow you to run, it is forcing you to catch the exception through the above one of mechanisms.

If it is not clear, let us take an example, in the above code we have VesselException and QtyException_Spice . You are at your initial stage of cooking under the supervision of your parent. So we are ordered/ instructed to keep the big vessel and the spices nearby in case you may need it when the problem arise. If you are not keeping it nearby parent is not allowing you to start cooking (initial time ). Parent is compiler here.

throws:

So Expected exception by the compiler is called Checked Exception, and the compiler force us to handle. One solution we know try-catch-finally, what is that through declaration in the method? The exception in which method can be expected, that method should use the keyword “throws <ExceptionClassName-1>” that is, it specifies this method may lead to the exceptions from the list of classes specified after throws keyword. After throws can be one class or more than one. whoever using this method with this declaration in method signature will aware of that and may handle it.

The good example for this is, IOException (parent) – FileNotFoundException (child). If you are trying to open a file, read it, the possible exceptions are: File Path Incorrect, File doesn’t exist, File Permission, Network issues etc. For Ex.

public static void main(String[] args) {
        try {
            // Calling the method that may throw a FileNotFoundException
            readFile("nonexistentfile.txt");
        } catch (FileNotFoundException e) {
            // Handle exception here
            System.out.println("File not found! Please check the file path.");
            e.printStackTrace();
        }
    }   

 // Method that throws FileNotFoundException
    public static void readFile(String fileName) throws FileNotFoundException {
        File file = new File(fileName);
        Scanner scanner = new Scanner(file);  // This line may throw FileNotFoundException
        while (scanner.hasNextLine()) {
            System.out.println(scanner.nextLine());
        }
        scanner.close();
    }

What is Unchecked Exception?

The compiler will not alert you about this exception, instead you will experience at runtime only. This not required to be declared or caught, but handling is advisable. These are all subclasses of RunTimeException (Error also will throw runtime exception only). It could be thrown when runtime issues, illegal arguments, or programming issues.

Ex.Invalid index in an array, or trying to take value from a null object, or dividing by zero.

Ex. NullPointerException

String str = null; System.out.println(str.length()); /* Throws NullPointerException */

ArrayIndexOutOfBoundsException

int[] arr = new int[3]; System.out.println(arr[5]); /* Throws ArrayIndexOutOfBoundsException */

What is throw?

Instead JRE throws error, the developer can throw the exception object (Predefined or UserDefined) to signal some erroneous situation and wants to stop the execution. For ex, you have the idea of wrong input and wants to give your own error message.

public class SampleOfThrow {
    public static void main(String[] args) {
        // a/b --> b should not be 0
        Scanner scn = new Scanner(System.in);
        int a = scn.nextInt();
        int b = scn.nextInt();
        if (b==0) throw new ArithmeticException("b value could not be zero");
        System.out.print(a/b);
    }
}

Hey, wait, I read the word, User Defined Exception above. which means the developer (we) also can create our own exception and can throw it. Yes, absolutely. How? In Java everything is class, right? So through class only, but on one condition it should extend the parent Exception class in order to specify it is an exception.

//User Defined Exception
class UsDef extends Exception {
    public UsDef(String message) {
        super(message); //will call Exception class // and send the own error message
    }
}

public class MainClass {
    public static void main(String[] args) {
        try {
            Scanner scn = new Scanner(System.in);
            boolean moreSalt = scn.nextBoolean(); 
            validateFood(moreSalt);
 // This method will throw an TooMuchSaltException
        } catch (TooMuchSaltException e) {
            System.out.println(e.getMessage());  // Catching and handling the custom exception
        }
    }

    // Method that throws TooMuchSaltException if food contains too much salt and can't eat
    public static void validateFood(boolean moreSalt) throws TooMuchSaltException {
        if (moreSalt) {
            throw new TooMuchSaltException("Food is too salty.");
        }
        System.out.println("Salt is in correct quantity");
    }
}

Now Lets have a look at some important Exception Handling points in java of view. (The following are taken from chatGPT)

Error Vs. Exception

Checked vs. Unchecked Exception:

throw vs. throws:

References : 1. https://www.geeksforgeeks.org/exceptions-in-java/

Public Speaking – It’s just two words, but it makes many people feel frightened. Even I did. I felt embarrassed to stand in front of my schoolmates/colleagues.

Usually, I am present in college during working days, but if it’s seminar days, you can’t find me – I will be absent. But whatever you try to avoid in life, one day you’ll face it, right? That was what happened in my interview. Fear! Fear!!

But how could we compare an interview with public speaking? Why not? If the interview panel has multiple people, and they ask you questions you may or may not know the answers – but at least in public speaking, you will speak about what you know.

I still have that fear. So, I decided not to run away but FACE THE ISSUE. A few good people supported me in overcoming this situation. First, my professor Muthu Sir, who advised me to join open-source communities, specifically ILUGC and KanchiILUGC. He said, “Just join, they will take care of you if you follow them.” I joined, and under Mr. Shrini’s guidance, I started doing simple projects. In between, he asked me to give a presentation at an ILUGC meet.

I said OK immediately (I already wanted to overcome my fear). I felt I accepted in a rush, and suddenly had mixed feelings like run away But he was so fast – I received an email to give my name, and the formalities proceeded. The real race started in my heart.

My inner thoughts: “What, Sugi? What are you going to do? The subject is fine, but can you speak in front of people?”

I said to myself, It’s OK, whatever, I have to do. Then, Muthu Sir, Ms.Jothy, friends, classmates, my family and all others encouraged me.

I still remember what Muthu Sir said: “What’s the worst that can happen? One, you can do well. If so, you’ll feel good and confident. Two, you may not do well, but that will push you to do better next time. Both outcomes will yield good and positive results, so just go for it.”

Then I practiced alone and felt OK. I had some paper notes in my hand, but when the laptop screen turned on, my heart rate went up, and my hands started shaking. When people asked me to start, I said, “I am Sugirtha,” and then forgot everything.

Thank God I at least remembered my name! Fine, let’s see the paper – What is this? I couldn’t read it, nothing was going inside my brain. It felt like Latin, which I don’t understand. I threw the paper aside, started recollecting, and said, “HTML stands for HyperText Markup Language.” Inside, I thought, Oh my God, this is not my first line to say, I thought I would start differently. For about 5 to 10 minutes, I fumbled with the points but didn’t deliver them as I expected. But when I started working on the code, I felt OK, as I got immersed in it.

Finally, it was over. There was still some tension, and after some time, I thought, I don’t know if my presentation was good or not, but at least I finished it. Then, after a while, I thought, Oh God, you did it, Sugi! Finally, you did something.

Now, I wonder if I get another chance, could I do it again? Back then, I somehow managed, but now… the fear returns. But this not the same as before which I feel I can overcome easily. So to overcome this I have to do more and more. I don’t want to prove anything to anyone, but I just want to prove something to myself. For my own satisfaction, I want to do more. I feel I will do better.

If I can, why can’t you?

Project Title : Address Book

S/w Used : HTML, CSS, JavaScript with LocalStorage for storing data (little Bootstrap)

Framework Used : Text Editor

Description : A small addressbook which is having name, email, contact no, and city which is having CRUD operations.

Till Today :

• From v1.0, design changed completely.
• Code done for Adding new Contact with modal window.
• And whenever new entry added that will get displayed in the table dynamically with del and edit buttons.
• Delete button action also done.
• Alignment I could not able to do well, so took chatGPT help.

To Do:

• Edit action and Search are pending.
• Add New screen is not getting closed after adding – has to be rectified.
• Design should get changed in AddNew screen
• Table – Headings font size should get changed. (As used bootstrap table class – th is not getting changed through css – have to research in it.
• Some Code duplication is there, have to be optimized like keep in one function (inside validationPassed & addNewContact).

Technical WorkFlow:

function validationPassed : This function checks all the fields are not empty.

function getAddrBook : This function returns an Array which extracts the existing contacts from localStorage, if available, and parse it. Otherwise an empty array will be returned.

function addNewContact : If validationPassed, then new contact entry is created from 4 fields (Name, Email, ContactNo and City), together will form an object and pushed into addrBook array (got through getAddrBook) and will get saved into localStorage. showBook() function is getting called immediately to show the added contact.

function showBook() : This is actually a table where rows(contacts) with delete and edit buttons are getting added dynamically and will be shown.

function deleteCont(idx) : As the name suggests it will take the index of the array as input and delete the contact when the delete button pressed.

Output till now :

AddNew Screen:

gitlab : https://gitlab.com/Sugirtha/Kaniyam.git

package taskPkg;

import java.util.Scanner;

public class SubArray {

	public static void main(String[] args) {
		// FINDING SUBARRAY AND ITS POSITION
		System.out.println("Enter a sentence to search in.");
		Scanner scn = new Scanner(System.in);
		String sentn = scn.nextLine();
		System.out.println("Enter a word to be searched.");
		String word = scn.next();
		scn.close();
		char[] sentence = sentn.toCharArray();
		char[] key = word.toCharArray();
		int n = sentence.length, m = key.length;
		int i = 0, j = 0, st = 0; // i- maintain index in the sentence, j-maintain index in key
		boolean match = false;
		while (i < n && j < m) {// 
			//System.out.println("i=" + i + "     sentence[i]=" + sentence[i] + "    j=" + j + "   key[j]=" + key[j]);
			if (sentence[i] == key[j]) { //if it matches incrementing both pos
				i++;
				j++;
				if (j == m) { //if the key reaches end, made the match and exit
					match = true;
					break;
				}
			} 
			else {    // if no match move on to the next letter, but key should start from 0
				j = 0;
				i=st+1;
				st = i;  // this is to save the starting of the subarray
			}
		}
		if (match)
			System.out.println("SubArray is found at " + st);
		else
			System.out.println("SubArray is not found");
	}
}

OUTPUT:

package taskPkg;

public class MatrixCalc {
	public static void main(String[] args) {
		// 1. MATRIX ADDITION ( A + B )
		// 2. MATRIX MULTIPLICATION ( C*D ) IF C is nxk AND D IS kxm RESULTANT MUST BE
		// nxm
		int[][] A = { { 4, 2, 5 }, { 6, 3, 4 }, { 8, 1, 2 } };
		int[][] B = { { 5, 1, 3 }, { 2, 3, 2 }, { 1, 6, 4 } };
		int[][] C = { { 4, 2 }, { 2, 1 }, { 5, 3 } };
		int[][] D = { { 1, 2, 3 }, { 3, 2, 5 } };
		int[] desc = { 9, 5, 2, 1 };
		int[][] M = new int[C.length][D[0].length];

		MatrixCalc matOp = new MatrixCalc();
		matOp.add(A, B);
		System.out.println("-------------------------");
		matOp.multiply(C, D, M);
		System.out.println("-------------------------");
		matOp.LargestInEachRow(M);
		System.out.println("-------------------------");
		matOp.isDescendingOrder(desc);
	}

	private void add(int[][] A, int[][] B) {
		int r = A.length, c = A[0].length;
		int[][] S = new int[r][c];
		for (int i = 0; i < r; i++) {
			for (int j = 0; j < c; j++) {
				S[i][j] = A[i][j] + B[i][j];
			}
		}
		print2Darray(A, "A");
		print2Darray(B, "B");
		System.out.println("SUM : S = A+B");
		print2Darray(S, "S");
		System.out.println();
	}

	private void multiply(int[][] C, int[][] D, int[][] M) {
		int n = C.length, q = D.length, m = D[0].length;
		for (int i = 0; i < n; i++) {
			for (int j = 0; j < m; j++) {
				for (int k = 0; k < q; k++) {
					M[i][j] += C[i][k] * D[k][j];
				}
			}
		}
		print2Darray(C, "C");
		print2Darray(D, "D");
		System.out.println("MULTIPLY : M = CxD");
		print2Darray(M, "M");
	}

	private void LargestInEachRow(int[][] M) {
		int L[] = new int[M.length];
		System.out.println("LARGEST IN EACH ROW OF : M ");
		System.out.print("[");
		for (int i = 0; i < M.length; i++) {
			L[i] = M[i][0];
			for (int j = 0; j < M[0].length; j++) {
				L[i] = L[i] < M[i][j] ? M[i][j] : L[i];
			}
			System.out.print(" " + L[i] + " ");
		}
		System.out.println("]");
	}

	private void isDescendingOrder(int[] des) {
		boolean desc = true;
		System.out.print("Array des [");
		for (int i = 0; i < des.length; i++) {
			System.out.print(" " + des[i] + " ");
			if (i > 0 && desc && des[i - 1] < des[i])
				desc = false;
		}
		System.out.println("]");
		if (desc)
			System.out.println("Given Array des is In Descending Order");
		else
			System.out.println("Given Array des is NOT In Descending Order");
	}

	private void print2Darray(int[][] ar, String arName) {
		System.out.print("Array " + arName + " : ");
		for (int i = 0; i < ar.length; i++) {
			if (i > 0)
				System.out.print("          ");
			System.out.print("[");
			for (int j = 0; j < ar[0].length; j++) {
				System.out.print(" " + ar[i][j] + " ");
			}
			System.out.println("]");
		}
		System.out.println();
	}

}

OUTPUT:

import java.util.Scanner;

public class Array2DstudentsMarks {
	Scanner scn = new Scanner(System.in);
	String stud[];
	int[][] marks;
	int[] tot, highestScorer;
	int subj, totHighStud;
	public static void main(String[] args) {
		// STUDENTS MARKS WITH TOTAL - 2D ARRAY
		Array2DstudentsMarks twoDArray =  new Array2DstudentsMarks();
		twoDArray.studentMarksTotal();
		twoDArray.dispMarkAndTotal();
	}

	private void studentMarksTotal() {
		System.out.println("Enter no. of Students");
		int n = scn.nextInt();
		System.out.println("Enter no. of Subjects");
		subj = scn.nextInt();
		stud = new String[n];
		marks = new int[n][subj];
		tot = new int[n];
		highestScorer= new int[subj];
		int maxTot = 0, highScore=0;
		for (int i=0; i<n; i++) {
			System.out.println("Enter Student name.");
			stud[i] = scn.next(); 

			System.out.println("Enter "+subj+" subjects marks of "+stud[i]+" one by one.");
			for (int j=0; j<subj; j++) {
				marks[i][j] = scn.nextInt();
				tot[i] += marks[i][j];
				if (marks[highestScorer[j]][j] < marks[i][j]) {
					highestScorer[j] = i;
					//highScore = marks[i][j];
				}
			}
			if (maxTot < tot[i]) {
				maxTot = tot[i];
				totHighStud = i;
			}
		}
	}
	private void dispMarkAndTotal() {
		System.out.println("------------------------------------------------------------------------");
		System.out.println("                       STUDENTS MARK LIST                               ");
		System.out.println("------------------------------------------------------------------------");
		for (int i=0; i<stud.length; i++) {
			System.out.println("Student Name : "+stud[i]);
			for (int j=0; j<subj; j++) {
				System.out.println("Subject-"+(j+1)+" = "+marks[i][j]);
			}
			System.out.println();
			System.out.println("Total Marks = "+tot[i]);
			System.out.println("Percentage = "+(tot[i]/subj)+ "%");	
			System.out.println("------------------------------------------------------------------------");
		}
		for (int i=0; i<highestScorer.length; i++) {
			int student = highestScorer[i];
			System.out.println("Subject-"+(i+1)+" Highest Mark is "+marks[student][i]+" achieved by "+stud[student]);
		}
		System.out.println("------------------------------------------------------------------------");
		System.out.println("Over All Highest Total "+tot[totHighStud]+" achieved By "+stud[totHighStud]);
		System.out.println("------------------------------------------------------------------------");
	}
}

OUTPUT:

Project Title : Address Book

S/w Used : HTML, CSS, JavaScript with LocalStorage for storing data (little Bootstrap)

Framework Used : Text Editor

Description : A small addressbook which is having name, email, contact no, and city which is having CRUD operations.

Till Today : Just designed the front screen for Adding New Contact – simple html screen with css only done.

<html>
<head>
 <title>Address Book</title>
 <meta name="viewport" content="width=device-width, initial-scale=1.0">
 <meta name="author" content="Sugirtha">
 <meta name="description" content="Simple AddressBook with Name, Contact No, Email and City">
 <link rel="stylesheet" href="addr.css">
</head>
<body>
 <h3>Simple Address Book</h3>
 <form id="frmAddr" name="formAddr">
 <div class="container">
  <table>
   <tr>
    <th>Name</th>
    <td><input type="text" id="txtName" placeholder="Enter your Name"></td>
   </tr>
   <tr>
    <th>Email</th>
    <td><input type="text" id="txtEmail" placeholder="Enter your Email"></td>
   </tr>
   <tr>
    <th>Contact No.</th>
    <td><input type="text" id="txtPhone" placeholder="ContactNo. including ISD Code"></td>
   </tr>
   <tr>
    <th>City</th>
    <td><input type="text" id="txtCity" placeholder="Enter your City here"></td>
   </tr>
  </table>
   <div class="submitPos">
    <input type="submit" id="btnSubmit" value="SAVE" class="button">
    <input type="button" id="btnCancel" value="CLEAR" class="button">
   </div>

 </div>
 </form>
</body>
</html>

CSS

body
{
    background-color:#3d4543;
    color:white;
}
h3
{
    text-align:center;  
    font-family:Helvetica;
}
input[type=text]
{

    background-color:#bccd95;
    color:black;
    height:30px;
    width:220px;
    border-radius:3px;
    border-color:white;
}
::placeholder
{
    opacity: 1;
    font-family:Helvetica;
    font-size:15px;
}
.container 
{
    background-color:#3d4543;
    position: relative;
    top:4px;
    left:38%;
    width:450px;
    height:350px;
}
.submitPos
{
    position: relative;
    top:38px;
    left:10%;   
}
th
{
    text-align:left;
    font-family:Helvetica;
    font-size:15px;
    font-weight:bold;
}
.button
{
    position:relative;
    color:white;
    border:none;
    border-radius:3px;
    width:100px;
    height:26px;
    margin-left:10px;
    background-color:#303030; 
    border-radius:8px;
    border-bottom:black 2px solid;  
    border-top:2px 303030 solid; 
    font-family:Helvetica;
    font-size:13px;
    font-weight:bold;
}

OUTPUT

    // 1. REMOVING UNWANTED SPACE
    // 2. CAPITALIZE EACH WORD

package taskPkg;

import java.util.Scanner;

public class PlayingWithCharAr {

	public static void main(String[] args) {
		// 1. REMOVING UNWANTED SPACE
		// 2. CAPITALIZE EACH WORD
		Scanner scn = new Scanner(System.in);
		System.out.println("Enter a sentence...");
		String sentence = scn.nextLine();
		scn.close();
		char[] sen = sentence.toCharArray();
		PlayingWithCharAr obj = new PlayingWithCharAr();
		obj.removeSpace(sen);
		obj.wordStartWithCaps(sen);
	}

	private void removeSpace(char[] words) {
		int k = 0, n = words.length;
		System.out.print("Removed unwanted spaces :");
		while (k < n && words[k] == ' ')
			k++;
		for (; k < n - 1; k++) {
			if (!(words[k] == ' ' && words[k + 1] == ' '))
				System.out.print(words[k]);
		}
		if (words[n - 1] != ' ')
			System.out.print(words[n - 1]);
		System.out.println();
	}

	private void wordStartWithCaps(char[] words) {
		int i = 0, n = words.length;
		System.out.print("Capitalize Each Word :");
		if (words[i] != ' ')
			System.out.print((char) (words[i] - 32));
		for (i = 1; i < n; i++) {
			if (words[i - 1] == ' ' && words[i] != ' ' && (words[i] >= 'a' && words[i] <= 'z'))
				System.out.print((char) (words[i] - 32));
			else
				System.out.print(words[i]);
		}
	}

}

OUTPUT:

1. First Letter Occurrence, Last Letter Occurrence in the given Word
2. Print Reversed Array
3. Cricketers array and their best score array – Give name as input and get the high score.

package taskPkg;
import java.util.Scanner;
public class ArraysBasics {
//1. PRINTING 1ST LETTER OF THE CHAR ARRAY
//2. PRINT REVERSED A CHAR ARRAY	
//3. 
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ArraysBasics obj = new ArraysBasics();
		Scanner scn = new Scanner(System.in);
		System.out.println("Enter a name to find its first and Last Letter.");
		String nam =  scn.next();
		char[] name = nam.toCharArray();
		System.out.println("First Letter of "+nam+" is repeated at "+obj.findFirstLtr(name));
		System.out.println("Last Letter of "+nam+" is repeated at "+obj.findLastLtr(name));
		obj.printReversedArray(name);
		System.out.println();  
		
		String[] cricketers = {"Rohit","Suryakumar","Ashwin", "Jadeja","Yashasvi"}; 
		int[] scores = {100,120,86,102,98};
		System.out.println("Enter one cricketer name to get his high score");
		String givenName = scn.next();
		System.out.println("Score for " + givenName+  " is "+obj.getScore(cricketers,scores,givenName));
		
		
	}
	private int findFirstLtr(char name[]) {
		char ch = name[0];
		for (int i=1; i<name.length; i++) {
			if (ch==name[i]) return i;
		}
		return -1;
	}
	private int findLastLtr(char name[]) {
		char ch = name[name.length-1];
		for (int i=0; i<name.length-1; i++) {
			if (ch==name[i]) return i;
		}
		return -1;
	}
	private void printReversedArray(char[] name) {
		System.out.println("Reversed Array = ");
		for (int i=name.length-1; i>=0; i--) {
			System.out.println(" "+name[i]);
		}
	}
	private int getScore(String[] name, int[] score, String givenName) {
		int n = name.length;
		for(int i=0; i<n; i++) {
			if(name[i].equals(givenName)) return score[i];
		}
		return 0;
	}
	
}

OUTPUT:

<html>
<head>
    <title>Simple Calculator</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="author" content="Sugirtha">
    <meta name="description" content="Calculator using HTML, CSS, JavaScript">   
    <link rel="stylesheet" href="calc.css">
</head>
<body>
    <h2 align="center">CALCULATOR</h2>
<div class="borderContainer">
<div class="container" >
<div class="resultsContainer" ><input type="text" id="results" name="results" class="result" readonly></div>
<div class="numPadContainer">
    <p>
    <input type="button" value="7" class="button black" onclick='calc("7")'>
    <input type="button" value="8" class="button black" onclick='calc("8")'>
    <input type="button" value="9" class="button black" onclick='calc("9")'>
    <input type="button" value="/" class="button opr" onclick='calc("/")'>
    </p>
    <p>
    <input type="button" value="4" class="button black" onclick='calc("4")'>
    <input type="button" value="5" class="button black" onclick='calc("5")'>
    <input type="button" value="6" class="button black" onclick='calc("6")'>
    <input type="button" value="*" class="button opr" onclick='calc("*")'>
   </p>
    <p>
    <input type="button" value="1" class="button black" onclick='calc("1")'>
    <input type="button" value="2" class="button black" onclick='calc("2")'>
    <input type="button" value="3" class="button black" onclick='calc("3")'>
    <input type="button" value="-" class="button opr" onclick='calc("-")'>
    </p>
    <p>
    <input type="button" value="0" class="button black" onclick='calc("0")'>
    <input type="button" value="00" class="button black" onclick='calc("00")'>
    <input type="button" value="." class="button black" onclick='calc(".")'>
    <input type="button" value="+" class="button opr" onclick='calc("+")'>

    </p>
    <p>
    <input type="button" value="mod" class="button opr" onclick='calc("%")'>
    <input type="button" value="C" class="button red" onclick='results.value=""'>
    <input type="button" value="=" class="button orange" onclick='doOper()' >

   </p>
</div>
</div>
</div>
<script>
    var answered=false;
    function calc(val) 
    {
        optrs = "+-*/%"
        if (answered && !optrs.includes(val)) 
        {
         
                //alert("not included opr - clearing ans")
                document.getElementById("results").value="";
                
            
        }
        document.getElementById("results").value += val;
        answered=false;
    }
    function doOper()
    {
        try
        {
            dispAns(eval(document.getElementById("results").value));
            
        }
        catch
        {
            dispAns("Input Error");
        }
    }
    function dispAns(output) 
    {
        document.getElementById("results").value = output;
        answered=true;
    }

</script>
</body>
</html>

body 
{
    background-color:black;
    color:lightgrey;
}



.borderContainer 
{
    position:relative;
    top:10px;
    left:36%;
    width:370;
    height:500;
    background-color: lightgrey;
}
.container
{
    position:relative;
    left:8%;
    top:25px;
    width:310px;
    height:450px;
    background-color:#3d4543;
    border-radius:3px;
}
.numPadContainer
{
    position:relative;
    left:25px;
    top:40px; 
}
.result
{
    position:relative;
    left:24px;
    top:28px;
    background-color:#bccd95;
    color:black;
    text-align:right;
    height:40px;
    width:260px;
    border-radius:10px;
    border-color:white;
}
.button
{
    position:relative;
    color:white;
    border:none;
    border-radius:8px;
    cursor:pointer;
    width:48px;
    height:42px;
    margin-left:10px;
}
.button.black
{
    background-color:#303030; 
    border-radius:8px;
    border-bottom:black 2px solid;  
    border-top:2px 303030 solid; 
}
.button.red
{
    background-color:#cf1917;  
    border-bottom:black 2px solid; 
    font-weight:bold;
}
.button.opr
{
    background-color:grey;  
    border-bottom:black 2px solid; 

}
.button.orange
{
    background-color:orange;
    border-bottom:black 2px solid;
    width:110px;
    font-weight:bold;
}

OUTPUT:

public class Patterns {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//PRINTING 1's and 0's IN SPECIFIC PATTERN
		int n=5;
		Patterns ptn = new Patterns();
		ptn.pattern1(n);
		System.out.println();
		ptn.pattern2(n);
		System.out.println();
		ptn.pattern3("SUGIRTHA");
	}
	private void pattern1(int n) {
		int val=0;
		for (int r=1; r<=n; r++) {
			val = r%2==0?0:1;
			for (int c=1; c<=n-r+1; c++) {
				System.out.print(" "+val);
				val=1-val;
			}
			System.out.println();
		}
	}
	private void pattern2(int n) {
		int val=1;
		for (int r=1; r<=n; r++) {
			for (int c=1; c<=n-r+1; c++) {
				System.out.print(" "+val);
			}
			val=1-val;
			System.out.println();
		}
	}
	private void pattern3(String name) {
		int n = name.length();
		for (int r=1; r<=n; r++) {
			for (int c=0; c<r; c++) {
				System.out.print(" "+name.charAt(c));  //TBD 
			}
			System.out.println();
		}
	}

}

OUTPUT:

package taskPkg;

public class PatternsPrintNos {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		PatternsPrintNos ptnNos = new PatternsPrintNos();
		int n=9;
		for (int i=0; i<=9; i++) {
			ptnNos.printNo(i,n);
			System.out.println();
		}
	}
	private void printNo(int num, int n) {
		int m=n/2+1;
		switch(num) {
		case 0:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (r==1 || r==n || c==1 || c==n) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;			
		case 1:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (c==m) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;
		case 2:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (r==1 || r==n || r==m || (r<=m && c==n) || (c==1 && r>=m)) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;
		case 3:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (r==1 || r==n || r==m || c==n) System.out.print(" *"); 
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;
		case 4:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if ((c==1 && r<=m) || c==m ||  r==m ) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;
		case 5:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (r==1 || r==n || r==m || (r<=m && c==1) || (c==n && r>=m)) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;
		case 6:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (c==1  ||  r==n || r==m || (r>m && c==n)) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;
		case 7:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (r==1 || (c==n-r+1)) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;
		case 8:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (r==1 || r==m || r==n || c==1 || c==n) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;		
		case 9:
			for (int r=1; r<=n; r++) {
				for (int c=1; c<=n; c++) {
					if (r==1 || r==m || (c==1 && r<=m) || c==n) System.out.print(" *");
					else  System.out.print("  ");
				}
				System.out.println();
			}
			break;				
		}

	}
}

Output: