unit = input(“Enter C/F : “)
temp = float( input(“Enter the Temprature : “))
if unit == “C”:
print(“Converted Fahrenheit is : “,(temp*9/5) + 32)
if unit == “F”:
print(“Converted Celsius is : “,(temp-32)*5/9)
Generating random number with restricted given number of times
import random
computer_Num = random.randint(1,100)
limit=5
while limit > 0:
guess = int (input(“Guess the Number :”))
limit-=1
if guess == computer_Num:
print(“Guess is “,guess,”Computer Number is “,computer_Num,”You Won”)
if guess != computer_Num:
print(“Guess is “,guess,”Computer Number is “,computer_Num,”Wrong guess”)
computer_Num = random.randint(1,100)
if limit == 0:
print(“Your limit is reached”)
Not getting proper output
import requests
from bs4 import BeautifulSoup
url=”https://www.moneycontrol.com/stocks/marketstats/nsehigh/index.php”
page=requests.get(url)
soup=BeautifulSoup(page.content,”html.parser”)
company = soup.find_all(“a”,class_=”ReuseTable_gld13__HzxFN undefined”)
#print(company)
for cmp in company:
print(cmp.prettify(), end=”\n\n”)
MAY I KNOW WHAT MISTAKE I DID HERE.
2.Once i installed how to check in terminal
3.Once a virtual env created, for next project again should i need to create another venv?
OOP/OOPs – Object Oriented Programming/s
DevOps – Development and Operation
HTML – Hyper-Text Markup Language
API – Application Programming Interface
IDE – Integrated Development Environment
WWW – World Wide Web
HTTP – Hyper-Text Transfer Protocol
HTTPS – Hyper-Text Transfer Protocol Secured
XML – extensible Markup Language
PY – Python
GUI – Graphical User Interface
APP – Application
UI/UX – User Interface / User experience
PHP – Hyper-Text Preprocessor (previously called as Personal Home Page)
TDL – Tally Defination Language
TCP – Tally Complaint Product
.NET – Network Enabled Technology
XLS – Excel Spreadsheet
XLSX – Excel Open XML Spreadsheet
CSV – Comma-Separated Value
PDF – Portable Document Format
JSON – Java Script Object Notation
JPG/JPEG – Join Photographic Experts Group
PNG – Portable Network Graphics
.SQL – Structured Query Language
RDBMS – Relational DataBase Management System
Structured Query Language
Relational Data-Base Management System
SQL is a Free Open Source Software
MySQL Client – front end MySQL Server – back end
Functions of SQL Client
2. Receiving input from client end and convert it as token and send to sql server
3. Getting the results from SQL server to user
Functions of SQL Server
SQL server consists 2 Major part
Receiving the request from client and return the response after processing
1.Management Layer
a.Decoding the data
b.Validating and parsing(analyzing) the data
c.Sending the catched queries to Storage Engine
2.Storage Engine
a.Managing Database,tables,indexes
b.sending the data to other shared SQL Server
Install SQL in Ubuntu
sudo apt-get install mysql-server
To make secure configure as below
sudo mysql_secure_installation
1.It used to removes Anonymous users
2.Allow the root only from the local host
3.Removing the test database
MySQL Configuration options
/etc/mysql is the MySQL configuration directory
To Start MySQL
sudo service mysql start
To Stop MySQL
sudo service mysql stop
To Restart MySQL
sudo service mysql restart
MySQL Clients
Normally we will use mysql in command line
But in linux we can access through following GUI
MySQL Work Bench
sudo apt-get install MySQL-workbench
MySQL Navigator
sudo apt-get install MySQL-navigator
EMMA
sudo apt-get install emma
PHP MYAdmin
sudo aptitude install phpmyadmin
MySQL Admin
sudo apt-get install MySQL-admin
Kinds of MySQL
1.GUI based Desktop based application
2.Web based application
3.Shell based application -(text-only based applications)
To connect the server with MySQL client
mysql -u root -p
To connect with a particular host , user name, database name
mysql - h
mysql -u
mysql -p
if not given the above host/username/password , it will take default local server/ uinux user name and without password for authentication.
to find more options about mysql
mysql -?
to disconnect the client with server
exit
from page 33 to 39 need to understand and read agan.
Service center database software – Desktop based
Draft
ABC Computer Service Center
#123, Greater Road, South Extension, Old Mahabalipuram Road, Chennai
Job Sheet No: Abc20250001 JS Date:28/01/2025
Customer Name: S.Ganesh Contact No.:9876543210
Email:sganesh123@gmail.com Job Recd by.xxxx
Address: R.Ganesh, No.10/46, Madhya Kailash, Job alloted to:Eng.0001 Rajiv gandhi road, OMR, chennai – 600 000.
Product Details :
Product: Laptop Model:Dell Inspiron G123 Color: black 1TB Hdd, 8GB Ram
Customer Remarks:
Laptop in not working condition. it was working very slow. battery need to change.
Remarks from Engineer:
1.Charges informed approx.rs.2800 for battery and service charges
2.System got ready and informed to customer
3.Total amount rs.3500 collected and laptop given. (job sheet needs to close)
Job Status:
1.*Pending for process 2.* Pending for customer approval 3.*Completed and informed to customer 4.*Completed and closed
Job Sheet Closed Date: 30/01/2025
Job Closed by:Eng.0001
Revenue Details
Spare actual cost Rs.2500 (to be enter at the time of job closing)
Spare Sale income Rs.2800
Service income:700
IN THIS, REQUIRED DATA ONLY ADD FOR JOB SHEET PRINT. CONSOLIDATED ENGINEER REVENUE DETAILS WILL BE ENABLED ONLY FOR ADMIN USER.
I import sqlite3
class Database:
def __init__(self,db):
self.con=sqlite3.connect(db)
self.cur=self.con.cursor()
sql=”””
CREATE TABLE IF NOT EXISTS Customer(
id Integer Primary key,
name text,
mobile text,
email text,
address text,
)
“””
#self.cur.execute (sql) (getting error while executing this line. if removed i have getting empty database output sheet)
self.con.commit()
O=Database(“Customer.db”)
In this code Customer db is getting generated but there is no data
<!DOCTYPE html>
<html>
<head>
<style>
html{
background-color: gray;
}
body{
width:400px;
height:400px;
margin:0 auto;
margin-top: 50px;
background-color: red;
}
.row{
width:50px;
height:50px;
float:left;
}
.color1{
width:50px;
height:50px;
background-color:white;
border-color: black;
border-right:none;
border-style:groove;
border-width: 1px;
float:left;
}
.color2{
width:50px;
height:50px;
background-color:red;
border-color: black;
border-right:none;
border-style:groove;
border-width: 1px;
float:left;
}
</style>
</head>
<body>
<div class=”row”>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
</div>
<div class=”row”>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
</div>
<div class=”row”>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
</div>
<div class=”row”>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
</div>
<div class=”row”>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
</div>
<div class=”row”>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
</div>
<div class=”row”>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
</div>
<div class=”row”>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
<div class=”color2″></div>
<div class=”color1″></div>
</div>
</body>
</html>
Login
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:OUTER JOIN: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:
COUNT(), SUM(), AVG(), MAX(), MIN().HAVING:
GROUP BY has been applied.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:
SELECT DISTINCT DeptName FROM Employees; SELECT DISTINCT DeptName, EmpName FROM Employees;
DELETE and TRUNCATE:WHERE clause to specify which rows to delete.COMMIT has been done).BEFORE DELETE or AFTER DELETE triggers).TRUNCATE:WHERE clause, so it always deletes all rows.DELETE because it doesn’t log individual row deletions (but it does log the deallocation of the table’s data pages).UNION:
UNION ALL:
UNION.SELECT EmpID, EmpName FROM Employees UNION ALL SELECT EmpID, EmpName FROM Contractors; SELECT EmpID, EmpName FROM Employees UNION SELECT EmpID, EmpName FROM Contractors;
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(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
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;
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;
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 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;
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:
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);
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)
Return Type:
Usage:
CALL, cannot be used in SQL queries.Transaction Control:
Side Effects:
Calling Mechanism:
Procedure: Called using CALL procedure_name().
Function: Called within SQL expressions, like SELECT function_name().
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:
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.
FOR EACH ROW?UPDATE statement that affects 10 rows, the trigger will fire once (for the statement) rather than for each of those 10 rows.OLD or NEW values to capture the individual row’s data. The trigger will just execute as a whole, without row-specific actions.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;
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 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.
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.2. Rewrite Queries:
SELECT * and instead select only the necessary columns.SELECT name, age FROM users WHERE age > 18;3. Use Joins Efficiently:
INNER JOIN over OUTER JOIN when possible).4. Optimize WHERE Clauses:
WHERE clauses are selective and reduce the number of rows as early as possible.AND and OR operators appropriately to filter data early in the query.5. Limit the Number of Rows:
LIMIT clause when dealing with large datasets to fetch only a required subset of data.6. Avoid Subqueries When Possible:
7. Analyze Execution Plans:
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.EXPLAIN SELECT * FROM users WHERE age > 18;8. Use Proper Data Types:
INTEGER for numeric values rather than VARCHAR, which takes more space and requires more processing.9. Avoid Functions on Indexed Columns:
UPPER(), LOWER(), or DATE() on indexed columns in WHERE clauses can prevent the database from using indexes effectively.10. Database Configuration:
SELECT * FROM ordersWHERE customer_id = 1001AND order_date > '2023-01-01';
This query might perform a full table scan if customer_id and order_date are not indexed.
CREATE INDEX idx_customer_order_date ON orders(customer_id, order_date);SELECT order_id, order_date, total_amountFROM ordersWHERE customer_id = 1001AND 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
FROM and/or JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT nad/or OFFSET
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
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’;
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;
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;
TEXT, VARCHAR, CHAR: Working with strings. INTEGER, BIGINT, NUMERIC: Handling numbers. DATE, TIMESTAMP: Date and time handling.
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;
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
First from table will be taken, then WHERE condition will be applied
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
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:
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,
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
Below listed are the high level steps involved to create a basic Django application.
In the previous demo, I have used seperate Javascript function to achieve the result. But it looks like it is not the best practice to have multiple JS functions and also the HTML content should be isolated from that of the JS content. In my previous example I have used the HTML content within the JS code.
In this demo, I have called just one JS function with different parameters whenever different links are being clicked. Attaching the code for the same
| <html> | |
| <head> | |
| <link rel="stylesheet" href="portfoliostyle.css"> | |
| <script src="portfolio_2.js"></script> | |
| </head> | |
| <body> | |
| <nav class="navbar"> | |
| <div id="name">Kathir</div> | |
| <div id="menu"> | |
| <a id="about" href="#" onclick="show('about')">About Me</a> | |
| <a id="summary" href="#" onclick="show('summary')">Technical Summary</a> | |
| <a id="projects" href="#" onclick="show('project')">Projects</a> | |
| <a id="contact" href="#" onclick="show('contact')">Contact</a> | |
| </div> | |
| </nav> | |
| <div id="pAbout" style="display:none">About Me About me About me</div> | |
| <div id="pSummary" style="display:none">Summary Me Summary me Summary me</div> | |
| <div id="pProject" style="display:none">Project Me Project me Project me</div> | |
| <div id="pContact" style="display:none">Contact Me Contact me Contact me</div> | |
| </body> | |
| </html> |
| function show(menuID) | |
| { | |
| switch(menuID) | |
| { | |
| case 'about': | |
| document.getElementById("pSummary").style.display='none'; | |
| document.getElementById("pProject").style.display='none'; | |
| document.getElementById("pContact").style.display='none'; | |
| document.getElementById("pAbout").style.display='block'; | |
| break; | |
| case 'summary': | |
| document.getElementById("pProject").style.display='none'; | |
| document.getElementById("pContact").style.display='none'; | |
| document.getElementById("pAbout").style.display='none'; | |
| document.getElementById("pSummary").style.display='block'; | |
| break; | |
| case 'project': | |
| document.getElementById("pSummary").style.display='none'; | |
| document.getElementById("pContact").style.display='none'; | |
| document.getElementById("pAbout").style.display='none'; | |
| document.getElementById("pProject").style.display='block'; | |
| break; | |
| case 'contact': | |
| document.getElementById("pSummary").style.display='none'; | |
| document.getElementById("pProject").style.display='none'; | |
| document.getElementById("pAbout").style.display='none'; | |
| document.getElementById("pContact").style.display='block'; | |
| break; | |
| } | |
| } |
| .navbar | |
| { | |
| display:flex; | |
| justify-content:space-between; | |
| } | |
| a:hover | |
| { | |
| color:orange; | |
| } |
OUTPUT:
This is about a sample html page with Nav bar.
Requirement:
When we highlight the menu it should change the font color. And when we click on the menu only the content related to that link has to be displayed.
Solution:
I tried this with a basic html page calling seperate function for each menu item onclick event. Attaching the code snippet.
| <html> | |
| <head> | |
| <link rel="stylesheet" href="style.css"> | |
| <script src="portfolio.js"></script> | |
| </head> | |
| <body> | |
| <nav class="navbar"> | |
| <div id="name">Kathir</div> | |
| <div id="menu"> | |
| <a id="about" href="#" onclick="clickAbout()">About Me</a> | |
| <a id="summary" href="#" onclick="clickSummary()">Technical Summary</a> | |
| <a id="projects" href="#" onclick="clickProjects()">Projects</a> | |
| <a id="contact" href="#" onclick="clickContact()">Contact</a> | |
| </div> | |
| </nav> | |
| <p id="details"></p> | |
| </body> | |
| </html> |
| function clickAbout() | |
| { | |
| document.getElementById("details").innerHTML="About Me About Me About Me About Me About Me About Me About Me About Me About Me About Me " | |
| } | |
| function clickSummary() | |
| { | |
| document.getElementById("details").innerHTML="Technical Summary Technical Summary Technical Summary Technical Summary Technical Summary Technical Summary " | |
| } | |
| function clickProjects() | |
| { | |
| document.getElementById("details").innerHTML="Projects Summary Projects Summary Projects Summary Projects Summary Projects Summary Projects Summary " | |
| } | |
| function clickContact() | |
| { | |
| document.getElementById("details").innerHTML="Contact Contact Contact Contact Contact Contact Contact Contact Contact Contact Contact Contact " | |
| } |
| .navbar | |
| { | |
| display:flex; | |
| justify-content:space-between; | |
| } | |
| a:hover | |
| { | |
| color:orange; | |
| } |
அன்பு ஆசிரியர் நக்கீரன் அவர்களுக்கு பாசம் கலந்த வணக்கங்கள்.
சில நாட்களுக்கு முன்பு தங்களது துருவங்கள் தொடரை படிப்பதற்கான வாய்ப்பு கிடைத்து அதை படித்தும் முடித்தேன். ஒரு புத்தகத்தை எடுத்து அதை முழுவதுமாக படித்ததாக எனக்கு சரித்திரமே கிடையாது. அந்த வகையில் நானும் ஒரு மழலையே!
முதலில் மதன் கதாபாத்திரத்தை பற்றிய எனது பார்வையில் சில வரிகள்.
“இரத்தம் சதை சுவாசம் இவை அனைத்திலும் linux” ஊறியிருந்தால் மட்டுமே அப்படி ஒரு பாத்திரத்தை எழுத முடியும். அதிலும் மதன் கார்த்திகா இருவருக்கும் இடையில் ஒரு மென்பொருள் பாலமாக அமைந்தது அரிதிலும் அரிது.
மேலும் மதன் இயற்கையாகவே அமைதி விரும்பி போலும். அதாவது அவர் தோற்றம் பார்ப்பதற்கு சிறிது கரடுமுரடாக இருப்பினும் மனம் லீனுஸ்ய்(linux) மேன்மையையும் நேர்மையையும் கொண்டதாகவே நான் உணர்கிறேன்.
அதே சமயத்தில், தனக்கோ தன சுற்றத்திற்கோ ஏதாவது தவறாக நடக்குமாயின் அதையும் லீனுஸ் (linux) லவே இரும்பு கரம் கொண்டு கட்டுப்படுகிறார். மற்றுமோர் குணமாக வரிந்தோருக்கு வழுங்குதலையும் ஒன்றாகவே இருப்பதாக உணர்கிறேன்.
எதிர்முனையில் கார்த்திகா தெளிந்த நீரோடை போன்று எப்போதுமே காணப்படுகிறார்.
கதையில் என்னை கவர்ந்த ஒரு சில இடங்களை சொல்ல முற்படுகிறேன். ஆண்களை எப்போதுமே தவறாக சித்தரிக்கும் இந்த கால சூழ்நிலையில் ஒரு ஆண் எவ்வளவு மென்மையானவன் என்பதை உணர்த்தும் வகையில் மதன் கதாபாத்திரம் எழுதப்பட்டிருந்தது.
அதுவும் குறிப்பாக, கார்த்திகா எப்போதெல்லாம் மதனுடன் தனிமையில் இருக்கிறாரோ, அப்போதெல்லாம் மதனின் ஆண்மைக்கு சவாலாகவே பார்க்கிறேன். ஒரு சராசரி ஆண்மகன், அந்த மாதிரியான சூழ்நிலையில் எவ்வாறு நடந்து கொள்வார் என்று நாம் பார்த்திருப்போம் அல்லது ஆவ்வாரே இதுவரையில் கதையிலும் திரையிலும் காட்டப்பட்டுள்ளது.
குறிப்பாக இருவரும் ஹோட்டல் அறையின் balconyil நின்று பேசும் போதும் சரி கார்த்திகாவின் வீட்டில் குணா படம் பார்க்கும் பொழுதும் சரி ஒரு ஆண் இப்படி தான் நடந்து கொள்ளவேண்டும் என்பதை அழுத்தம்திருத்தமாக சொல்லப்பட்டிருந்தது.
மறுமுனையில் ஒரு பெண் எப்போதுமே பின்பற்ற வேண்டிய அச்சம் மடம் நாணம் போன்ற குணங்கள் சற்றும் பொருந்தாத புதுமை பெண்ணாக வலம் வருகிறார். ஏன் எப்போதும் ஆண் முன்வந்து தன காதலயோ உணர்ச்சியையோ சொல்லவோ காட்டவோ வேண்டும். பாரதி கண்ட புதுமைபெண்ணல்லவோ நான் என்பது போல் துணிச்சலாக தன் உணர்ச்சிகளை வெளிப்படுத்துகிறாள்.
இந்த தொடரின் முக்கியமான வெற்றி எதுவெனில்? எப்போதெல்லாம் மென்பொருள் பற்றிய உரையாடல் நீள்கிறதோ என்ற எண்ணம் மேலோங்குகிறதோ அப்போது காதல் உள்ளே வந்துவிடும். அல்லது எங்கே மென்பொருளை மறந்து காதல் கதையாக இத்தொடர் மாறியதோ என்ற எண்ணம் வரும்பொழுது linux உள்ளே வரும். இப்படி மாறி மாறி கதைக்களம் நகர்வாதாலேயே சுவாரசியம் சற்றும் குறைய வில்லை.
மேலும் நான் இக்கதையை படிக்கும் பொழுது linux மடிக்கணினியை பக்கம் வைத்தே படித்தேன். மதன் கார்த்திகாவிற்கு எடுத்துரைக்கும் விடயங்களை வாசகர்களாகிய நாமும் செய்து பார்க்க முடிந்தது. அதற்காக மதன் கூறிய எல்லாவற்றையும் செய்து பார்க்க முடியுமா என்றால் முடியாது. ஏனெனில் அது இந்த கதையின் நோக்கமும் அல்ல. linux என்னும் தீப்பொறியையும் அதனின் நீட்சியையும் நம்முள் முதல் பாதியில் கத்திவிட்டு, இரண்டாம் பாதியில் லீனிக்ஸின் விஸ்வரூபத்தை விவரித்தது அருமை.
கதை சார்ந்த அரசியல் பேசவும் நீங்கள் மறக்கவில்லை . அதை சுவைக்கு தேவையான அளவு உப்பு சேர்ப்பது போல சேர்த்ததாலேயே இதை எல்லோராலும் ஏற்றுக்கொள்ள முடிகிறது.
ஒரே ஒரு இடத்தில மட்டுமே மதனின் கதாபாத்திரத்தை சற்றே அவசரக்காரர் போல காட்ட முற்பட்டிர்களா என்று தெரியவில்லை. கார்த்திகாவின் அப்பாவிடம் அவர் கோவப்படும் அந்த சூழல் இன்னும் சற்றே விவரித்திருக்கலாமோ என்று தோன்றியது.. ஓரிறிரு கருத்துக்களை மட்டும் வைத்தே அவர் அவ்வாறானவர் என்று முடிவுக்கு வந்து அதற்கு எதிர்வினையாற்றியதும் ஆச்சர்யத்தை ஊட்டியது. என்னை பொறுத்தவரை மதன் அவ்வாறானவர் இல்லை என்பது மட்டும் உறுதி
கதையில் மற்ற கதாபாத்திரங்களும் சிறப்பாகவே சித்தரிக்கப்பட்டிருக்கிறார்கள் உதாரணத்திற்கு கார்த்திகாவின் தோழி , மதனின் நண்பர் உதய் , நண்பரின் காதலி, நண்பரின் அம்மா, கார்த்திகாவின் பெற்றோர், மதன் வேலை செய்யும் அலுவலகத்தின் ceo கடைசியில் வரும் ஒலிபெருக்கியை கையாண்ட பொடியன் வரை அனைவரும் அருமை.
கடைசியாக உங்களின் ARR மீதான காதலை என்னவென்று சொல்வது. எல்லோரும் அந்த பாடல்களை கேட்டிருப்போம் நீங்கள் கூறிய பின்புலத்தோடு கேட்டிருப்போமா என்பது கேள்விக்குறியே. அதுவும் காதலன் படத்தில் வரும் இந்திரயோ பாடல் இதுநாள் வரை வைரமுத்து எழுதியதாகவே என்னை போல் பலரும் நினைத்திருப்பார்கள்.
இதே கதையை இரு ஆண்களுக்குஇடையே நடப்பதாகவோ, இரு பெண்களுக்குஇடையில் நடப்பதாகவோ சொல்லிருந்தால் இவளவு சுவாரசியம் இருந்திருக்குமா என்பது சந்தேகமே. அதேபோல் கிடைக்கும் இடத்தில எல்லாம் linux சார்ந்த தகவல்களை தரவுகளை விட்டுச்சென்றதும் பாராட்டுக்குரியதே. உதாரணமாக IRC, ILUGC. அருமை.
மொத்தமாக, ஒரு வரியில் கூறவேண்டுமாயின். கதை படித்துமுடித்ததும் குறிப்பாக மூவரை என் மனம் பார்க்க வேண்டுமென ஏங்கியது.
1) மதன் – அவருடைய linux ஆற்றலுக்காக
2) கார்த்திகா – நீங்கள் வர்ணித்தது நிஜமா என்று ஆராய
3) நக்கீரன் என்னும் உங்களை – இப்படி ஒரு பித்தனை உருவாக்கியதற்கு. மென்மேலும் எழுதி என்னை போல் பலரையும்
உங்கள் வலைக்குள் சிக்கவைக்க எனது மனமார்ந்த வாழ்த்துக்கள்.
துருவங்களை தந்தமைக்கு நன்றி
சேது
In chapter-3, the author introduces us to a new concept called 80/20. You can see this pattern in almost everything you comes across in our day to day life. Like in a given area/country 20 percent people will govern the rest of 80 percent people or 20 percent people will be wealthier compared to 80 percent, 20 percent of the customers will be responsible for 80 percent of the sales, etc., In the same way 20 percent of your activities will reflect the 80 percent of your results.
So framing the 20 percent is more important and the author is calling that 20 percent as “Vital few” and the remaining 80 percent as “trivial many”. As discussed in the previous chapters, we tend to spend our energy addressing things from the 80 percent and feel that we did not achieve anything. But that has to happen in the reverse order. From the entire pile of the activities you have listed, pick the top items that you feel either difficult to achieve or tedious yet valuable.
Focus should be given to that 20 percent items. So if you knock off the items from the “Vital few” you feel accomplished and achieving the things from 80 percent or the “Trivial many” shouldn’t be that tough.
As a reader even I am trying to follow and implement these principles in my day to day life and will share my experience in the future blogs. But to me this is very much achievable and we all should give this a try.
Happy reading and see you all in the next chapter discussion.
In continuation from Chapter 1, Chapter 2 starts with how to approach the priority list. Because for a normal person[without any priority like me :-)] every task will be a priority. Unlike that, the author is suggesting an alternative.
It goes like this. Take a piece of paper or use a text editor and name that as “Priorities” and start writing all the tasks that comes to your mind which you wanted to do. Not necessarily in a day, week or month. Just random. For eg: Complete reading a book, finish the assignment, save a minimum amount, practice meditation etc., By the end of this activity, you will have an exhaustive list of task that you wish you could complete.
Next, take one more sheet of paper or open one more text editor and name this as monthly. In here, from the list that you created in “Priorities”, pick up those tasks which could be or has to be completed in the next month. From our example, we can choose “Finish an assignment” and add it to the “monthly” list.
Now the monthly list will be comparatively less than the entire priorities and you have a clear idea of what needs to be done in next 30 days. From here, create one more list for “Weekly”. And do the same exercise of choosing the things that has to or could be completed in next 7 days. And start including them in the weekly list.
Hard part is now complete. From here, connect the things that was described in Chapter#1. Pick up the frog that is bigger to eat and add it to your daily list.
From a larger picture, the moment you knock off one task from daily it creates a ripple effect. That is, it knocks off a task from weekly, then monthly and from the entire priorities. You will accomplished by the end of first week. And if we do this on week 2 we will feel even more happier and accomplished.
This is all about Chapter-2. Once again, this is my understanding and nothing to do with authors narration.
See you again in Chapter-3! Thank you for reading!
