DEV Community: Jemmy Dalsaniya

Mastering SQL Join Queries: HR Worker Data Analysis

Jemmy Dalsaniya — Mon, 30 Jun 2025 11:49:09 +0000

Welcome to our SQL Join Queries series, where we dive into advanced techniques for analyzing HR data using join operations. This blog focuses on a worker management database with Worker, Department, and Worker_rating tables. We’ll present 45 SQL queries leveraging various join types (INNER, LEFT, etc.) to tackle complex HR scenarios. These queries are perfect for data analysts, HR professionals, or developers aiming to master SQL joins for workforce insights.

🧩 Database Schema Overview

`Worker`: Stores worker details

Columns:

worker_id
first_name
last_name
salary
joining_date
department_id

`Department`: Contains department information

Columns:

id
department

`Worker_rating`: Tracks worker performance ratings

Columns:

id
worker_id
rating

🏗️ Creating the Tables

CREATE TABLE Department (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department VARCHAR(50) NOT NULL
);

CREATE TABLE Worker (
    worker_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary INT NOT NULL,
    joining_date DATE NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

CREATE TABLE Worker_rating (
    id INT PRIMARY KEY AUTO_INCREMENT,
    worker_id INT,
    rating INT NOT NULL,
    FOREIGN KEY (worker_id) REFERENCES Worker(worker_id)
);

📝 Inserting Sample Data

INSERT INTO Department (id, department) VALUES
(1, 'HR'),
(2, 'Admin'),
(3, 'Account'),
(4, 'Sales');

INSERT INTO Worker (worker_id, first_name, last_name, salary, joining_date, department_id) VALUES
(1, 'Monika', 'Arora', 100000, '1982-02-20', 1),
(2, 'Niharika', 'Verma', 80000, '2014-06-11', 2),
(3, 'Vishal', 'Singhal', 300000, '2015-02-20', 1),
(4, 'Amitabh', 'Singh', 500000, '2014-02-20', 2),
(5, 'Vivek', 'Bhati', 500000, '2016-06-11', 2),
(6, 'Vipul', 'Diwan', 200000, '2014-06-11', 3),
(7, 'Satish', 'Kumar', 75000, '1985-01-20', 4),
(8, 'Geetika', 'Chauhan', 90000, '1988-04-11', 2),
(9, 'Ramesh', 'Sharma', 120000, '2018-03-15', 1),
(10, 'Suresh', 'Yadav', 95000, '2017-07-01', 3),
(11, 'Anjali', 'Kapoor', 85000, '2013-01-25', 4),
(12, 'Pooja', 'Malhotra', 110000, '2016-12-10', 2),
(13, 'Deepak', 'Gupta', 140000, '2019-09-05', 1),
(14, 'Rohit', 'Jain', 70000, '2020-01-20', 3),
(15, 'Sneha', 'Mehra', 80000, '2012-06-25', 4),
(16, 'Arjun', 'Chopra', 130000, '2021-03-14', 1),
(17, 'Kiran', 'Reddy', 120000, '2022-05-18', 2),
(18, 'Meena', 'Naik', 110000, '2020-08-21', 1),
(19, 'Rajesh', 'Pillai', 95000, '2018-11-30', 4),
(20, 'Bhavna', 'Iyer', 105000, '2015-07-22', 3),
(21, 'Prakash', 'Joshi', 98000, '2016-04-25', 2),
(22, 'Gaurav', 'Patel', 88000, '2019-10-15', 3),
(23, 'Manisha', 'Desai', 102000, '2014-11-20', 2),
(24, 'Ajay', 'Khan', 92000, '2021-01-12', 4),
(25, 'Sanjay', 'Ghosh', 135000, '2017-03-08', 1),
(26, 'Priya', 'Das', 80000, '2018-05-14', 2),
(27, 'Anil', 'Nair', 125000, '2020-09-16', 3),
(28, 'Siddharth', 'Rao', 140000, '2022-11-10', 4),
(29, 'Radha', 'Kulkarni', 95000, '2015-12-23', 2),
(30, 'Tanya', 'Mishra', 89000, '2019-06-29', 3);

INSERT INTO Worker_rating (id, worker_id, rating) VALUES
(1, 1, 3),
(2, 1, 2),
(3, 2, 4),
(4, 3, 5),
(5, 4, 2),
(6, 2, 1),
(7, 3, 3),
(8, 5, 2),
(9, 6, 5),
(10, 7, 4),
(11, 8, 3),
(12, 9, 4),
(13, 10, 3),
(14, 11, 4),
(15, 12, 2),
(16, 13, 5),
(17, 14, 3),
(18, 15, 4),
(19, 16, 5),
(20, 17, 2),
(21, 18, 3),
(22, 19, 4),
(23, 20, 5),
(24, 21, 2),
(25, 22, 4),
(26, 23, 3),
(27, 24, 2),
(28, 25, 5),
(29, 26, 3),
(30, 27, 4);

🔍 Advanced SQL Join Queries for Worker Analysis

Below are 45 SQL queries with explanations, leveraging join operations to combine data across the Worker, Department, and Worker_rating tables for actionable HR insights.

1. Retrieve the first and last names of all workers along with their department names

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id;

2. Retrieve the total salary of workers in each department

SELECT d.department, SUM(w.salary) AS total_salary
FROM Department d
JOIN Worker w ON d.id = w.department_id
GROUP BY d.department;

3. Fetch the department-wise worker count

SELECT d.department, COUNT(w.worker_id) AS number_of_workers
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING AVG(w.salary) > 100000;

4. Fetch the department-wise highest salary record

SELECT d.department, MAX(w.salary) AS highest_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

5. Fetch the average rating with worker details whose rating is greater than or equal to 4

6. Get the list of workers with their department name and salary who have a rating of 3 or more

SELECT w.first_name, w.last_name, d.department, w.salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 3;

7. Show the department-wise average salary of workers who have been rated 4 or higher

SELECT d.department, AVG(w.salary) AS average_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 4
GROUP BY d.department;

8. Display the number of workers in each department where the average salary is greater than 100,000

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING AVG(w.salary) > 100000;

9. Get the list of workers who joined before 2015, along with their department names

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE YEAR(w.joining_date) < 2015;

10. Fetch data on workers who joined in 2014–02

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE YEAR(w.joining_date) = 2014 AND MONTH(w.joining_date) = 2;

11. Find the workers hired in the 80s (1980 to 1989)

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE YEAR(w.joining_date) BETWEEN 1980 AND 1989;

12. Display the total number of ratings for each worker and their respective department

13. Show the workers whose salary is greater than or equal to 500,000 along with their department

SELECT w.first_name, w.last_name, d.department, w.salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary >= 500000;

14. Find the number of workers in each department with a rating of less than 3

SELECT d.department, COUNT(DISTINCT w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating < 3
GROUP BY d.department;

15. Retrieve the department and average rating for workers in each department

SELECT d.department, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department;

16. Display the department names where the total salary is more than 1,000,000

SELECT d.department, SUM(w.salary) AS total_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING SUM(w.salary) > 1000000;

17. Get the workers who have a rating of 1 and their department name

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 1;

18. Get the count of workers joining year-wise

SELECT YEAR(w.joining_date) AS join_year, COUNT(w.worker_id) AS worker_count
FROM Worker w
GROUP BY YEAR(w.joining_date);

19. Fetch data whose joining in the month of February

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE MONTH(w.joining_date) = 2;

20. Find the workers who joined the company after the 15th date

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE DAY(w.joining_date) > 15;

21. Find the average salary of workers who have been rated 5 in each department

SELECT d.department, AVG(w.salary) AS average_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 5
GROUP BY d.department;

22. List the departments where the number of workers with a rating of 4 or more exceeds 3

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 4
GROUP BY d.department
HAVING COUNT(DISTINCT w.worker_id) > 3;

23. Show the workers who have a salary greater than 200,000 and were rated 3 or higher

SELECT w.first_name, w.last_name, w.salary, r.rating
FROM Worker w
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE w.salary > 200000 AND r.rating >= 3;

24. Retrieve the department name and the total salary of workers in that department where the average rating is below 3

SELECT d.department, SUM(w.salary) AS total_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING AVG(r.rating) < 3;

25. Display the departments with more than 2 workers who have been rated 2 or higher

SELECT d.department, COUNT(DISTINCT w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 2
GROUP BY d.department
HAVING COUNT(DISTINCT w.worker_id) > 2;

26. Get the department-wise count of workers who joined before 2014

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE YEAR(w.joining_date) < 2014
GROUP BY d.department;

27. Show the department-wise average salary of workers who have a rating of 3 or more

SELECT d.department, AVG(w.salary) AS average_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 3
GROUP BY d.department;

28. List the departments where the total salary of workers exceeds the department's average salary

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING SUM(w.salary) > (SELECT AVG(salary) * COUNT(worker_id) FROM Worker WHERE department_id = d.id);

29. Show the department name along with the average joining date of workers in each department

SELECT d.department, AVG(w.joining_date) AS average_joining_date
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

30. Retrieve the department-wise count of workers who have been rated exactly 4

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 4
GROUP BY d.department;

31. Display the department-wise count of workers whose salary is below 100,000

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary < 100000
GROUP BY d.department;

32. Get the total number of workers in each department with a salary greater than 150,000

SELECT d.department, COUNT(w.worker_id) AS worker_count
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary > 150000
GROUP BY d.department;

33. List the departments that have workers with the highest salary greater than 300,000

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING MAX(w.salary) > 300000;

34. Show the departments with workers who have an average rating of exactly 2

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING AVG(r.rating) = 2;

35. Get the departments where the average rating is less than 3 and total salary is greater than 1,000,000

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING AVG(r.rating) < 3 AND SUM(w.salary) > 1000000;

36. Retrieve the workers who have been rated 1 or 2 and their department names

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 1 OR r.rating = 2;

37. Find the department with the highest total salary

SELECT d.department, SUM(w.salary) AS total_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
ORDER BY total_salary DESC
LIMIT 1;

38. Find the department with the lowest average rating, excluding departments with no ratings

SELECT d.department, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
ORDER BY average_rating ASC
LIMIT 1;

39. Find the total salary and average rating for departments where the total salary is greater than 500,000

SELECT d.department, SUM(w.salary) AS total_salary, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING SUM(w.salary) > 500000;

40. Get department-wise worker names using GROUP_CONCAT

SELECT d.department, GROUP_CONCAT(CONCAT(w.first_name, ' ', w.last_name)) AS worker_names
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

41. Get workers who joined in the last 5 years

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE w.joining_date >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

42. Get the number of workers who joined each year

SELECT YEAR(w.joining_date) AS join_year, COUNT(w.worker_id) AS worker_count
FROM Worker w
GROUP BY YEAR(w.joining_date);

43. Get department-wise earliest and latest joining date

SELECT d.department, MIN(w.joining_date) AS earliest_joining, MAX(w.joining_date) AS latest_joining
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

44. Get workers who have been in the company for more than 10 years

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE w.joining_date < DATE_SUB(CURDATE(), INTERVAL 10 YEAR);

45. Retrieve the workers who have a joining today

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE DATE(w.joining_date) = CURDATE();

🎉 Conclusion

This blog has demonstrated 45 SQL queries leveraging join operations to extract actionable HR insights from a worker database. From analyzing salaries and ratings to tracking joining dates, these queries showcase the power of SQL joins in workforce management. Practice these examples to enhance your HR data analysis and drive informed decisions with efficient join-based queries.

Mastering SQL Basic : (Part : 1)

Jemmy Dalsaniya — Mon, 30 Jun 2025 06:23:17 +0000

Welcome to our SQL Basic Queries series, where we explore essential SQL techniques for analyzing HR data. In this blog, we focus on a worker management database with a single Worker table containing key employee details. We’ll walk through 54 SQL queries to perform fundamental operations like filtering, sorting, grouping, and data manipulation. These queries are ideal for beginners, data analysts, or HR professionals looking to build a strong SQL foundation for workforce management.

🧩 Database Schema Overview

`Worker`: Stores worker details

Columns:

worker_id
first_name
last_name
salary
joining_date
department

🏗️ Creating the Table

CREATE TABLE worker (
    worker_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(64),
    last_name VARCHAR(64),
    salary FLOAT,
    joining_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    department ENUM('HR', 'IT', 'ADMIN', 'ACOOUNTS', 'SALES')
);

📝 Inserting Sample Data

INSERT INTO worker (worker_id, first_name, last_name, salary, joining_date, department) VALUES
(1, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'),
(2, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'),
(3, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'),
(4, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'),
(5, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin'),
(6, 'Vipul', 'Diwan', 200000, '2014-06-11 09:00:00', 'Account'),
(7, 'Satish', 'Kumar', 75000, '2014-01-20 09:00:00', 'Account'),
(8, 'Geetika', 'Chauhan', 90000, '2014-04-11 09:00:00', 'Admin'),
(9, 'Anil', 'Sharma', 120000, '2015-03-15 09:00:00', 'IT'),
(10, 'Pooja', 'Mehta', 95000, '2016-01-22 09:00:00', 'HR'),
(11, 'Rohit', 'Yadav', 150000, '2015-05-10 09:00:00', 'Admin'),
(12, 'Swati', 'Gupta', 110000, '2016-02-18 09:00:00', 'HR'),
(13, 'Rajesh', 'Joshi', 70000, '2016-04-01 09:00:00', 'IT'),
(14, 'Priya', 'Kapoor', 80000, '2017-08-19 09:00:00', 'Account'),
(15, 'Arjun', 'Deshmukh', 140000, '2018-06-23 09:00:00', 'IT'),
(16, 'Meena', 'Reddy', 100000, '2018-09-12 09:00:00', 'Sales'),
(17, 'Suresh', 'Nair', 105000, '2019-01-15 09:00:00', 'Sales'),
(18, 'Kavita', 'Iyer', 95000, '2019-05-03 09:00:00', 'IT'),
(19, 'Deepak', 'Mishra', 120000, '2019-12-07 09:00:00', 'Admin'),
(20, 'Sneha', 'Shah', 115000, '2020-03-11 09:00:00', 'HR'),
(21, 'Akash', 'Bhardwaj', 90000, '2020-07-14 09:00:00', 'IT'),
(22, 'Lakshmi', 'Menon', 85000, '2021-01-25 09:00:00', 'Account'),
(23, 'Gaurav', 'Tiwari', 95000, '2021-06-10 09:00:00', 'IT'),
(24, 'Simran', 'Malhotra', 102000, '2021-11-20 09:00:00', 'Sales'),
(25, 'Anjali', 'Pandey', 108000, '2022-03-18 09:00:00', 'HR'),
(26, 'Aditya', 'Kulkarni', 98000, '2022-07-22 09:00:00', 'IT'),
(27, 'Rani', 'Patel', 89000, '2023-01-05 09:00:00', 'Account'),
(28, 'Rahul', 'Choudhary', 150000, '2023-05-19 09:00:00', 'Admin'),
(29, 'Manish', 'Dutta', 115000, '2023-09-30 09:00:00', 'Sales'),
(30, 'Neha', 'Bajaj', 93000, '2024-01-12 09:00:00', 'Admin');

🔍 Basic SQL Queries for Worker Analysis

Below are 54 SQL queries with explanations, designed to help you master basic SQL operations for HR data analysis.

1. Fetch FIRST_NAME from the Worker table using the alias name as worker_name

SELECT first_name AS worker_name FROM worker;

2. Fetch FIRST_NAME from the Worker table in uppercase

SELECT UPPER(first_name) FROM worker;

3. Fetch unique values of DEPARTMENT from the Worker table

SELECT DISTINCT department FROM worker;

4. Fetch the first three characters of FIRST_NAME from the Worker table

SELECT LEFT(first_name, 3) FROM worker;

5. Fetch unique values of DEPARTMENT from the Worker table and print their lengths

SELECT DISTINCT department, LENGTH(department) FROM worker;

6. Print the FIRST_NAME after replacing 'a' with 'A'

SELECT REPLACE(first_name, 'a', 'A') FROM worker;

7. Concatenate FIRST_NAME and LAST_NAME into a single column COMPLETE_NAME, separated by a space

SELECT CONCAT(first_name, ' ', last_name) AS complete_name FROM worker;

8. Print all Worker details ordered by FIRST_NAME in ascending order

SELECT * FROM worker ORDER BY first_name ASC;

9. Print all Worker details ordered by FIRST_NAME in ascending order and DEPARTMENT in descending order

SELECT * FROM worker ORDER BY first_name ASC, department DESC;

10. Print details of Workers with the FIRST_NAME as "Vipul" or "Satish"

SELECT * FROM worker WHERE first_name IN ('Vipul', 'Satish');

11. Print details of Workers excluding those with FIRST_NAME as "Vipul" or "Satish"

SELECT * FROM worker WHERE first_name NOT IN ('Vipul', 'Satish');

12. Print details of Workers with DEPARTMENT name as "Admin"

SELECT * FROM worker WHERE department = 'Admin';

13. Print details of Workers whose FIRST_NAME contains the letter 'a'

SELECT * FROM worker WHERE first_name LIKE '%a%';

14. Print details of Workers whose FIRST_NAME ends with the letter 'a'

SELECT * FROM worker WHERE first_name LIKE '%a';

15. Print details of Workers whose FIRST_NAME ends with 'h' and has six characters

SELECT * FROM worker WHERE first_name LIKE '_____h' AND LENGTH(first_name)=6;

16. Print details of Workers whose SALARY lies between 100000 and 500000

SELECT * FROM worker WHERE salary BETWEEN 100000 AND 500000;

17. Print details of Workers who joined in February 2014

SELECT * FROM worker WHERE MONTH(joining_date) = 2 AND YEAR(joining_date) = 2014;

18. Fetch the count of employees working in the department "Admin"

SELECT COUNT(*) FROM worker WHERE department = 'Admin';

19. Fetch FIRST_NAME of Workers with SALARY >= 50000 and <= 100000

SELECT first_name FROM worker WHERE salary >= 50000 AND salary <= 100000;

20. Fetch the odd rows of a table

SELECT * FROM worker WHERE worker_id % 2 <> 0;

Explanation: Uses MOD to select rows where worker_id is odd.

21. Fetch the top 5 records of a table

SELECT * FROM worker LIMIT 5;

22. Fetch the departments with less than five employees

SELECT department, COUNT(*) AS emp_count FROM worker 
GROUP BY department HAVING COUNT(*) < 5;

23. Fetch all departments along with the number of employees in each department

SELECT department, COUNT(*) AS emp_count FROM worker GROUP BY department;

24. Calculate the average SALARY of employees in each department

SELECT department, AVG(salary) AS avg_salary FROM worker GROUP BY department;

25. Fetch Workers who joined before January 2014

SELECT * FROM worker WHERE joining_date < '2014-01-01';

26. Fetch Workers in the "Admin" department with SALARY above 80000

SELECT * FROM worker WHERE department = 'Admin' AND salary > 80000;

27. Fetch Workers whose FIRST_NAME contains 'i'

SELECT * FROM worker WHERE first_name LIKE '%i%';

28. Fetch details of Workers whose SALARY lies between 75000 and 200000

SELECT * FROM worker WHERE salary > 75000 AND salary < 200000;

29. Fetch details of Workers who have 'Singh' in their LAST_NAME

SELECT * FROM worker WHERE last_name = 'Singh';

30. Group workers by their DEPARTMENT and count the number of workers in each department

SELECT department, COUNT(*) FROM worker GROUP BY department;

31. Find the average salary for each DEPARTMENT and only include departments with an average salary greater than 100,000

SELECT department, AVG(salary) FROM worker GROUP BY department HAVING AVG(salary) > 100000;

32. Display the department name and the total salary of workers in each department, but only include departments where the total salary is greater than 400,000

SELECT department, SUM(salary) FROM worker 
GROUP BY department HAVING SUM(salary) > 400000;

33. Group workers by DEPARTMENT and find the maximum salary in each department. Only include departments with a maximum salary greater than 200,000

SELECT department, MAX(salary) FROM worker 
GROUP BY department HAVING MAX(salary) > 200000;

34. Display the department name and the number of workers in each department, but only include departments with more than 3 workers

SELECT department, COUNT(worker_id) FROM worker 
GROUP BY department HAVING COUNT(worker_id) > 3;

35. Group workers by DEPARTMENT and calculate the total salary of workers in each department. Show only the departments where the total salary is less than 1,000,000

SELECT department, SUM(salary) FROM worker 
GROUP BY department HAVING SUM(salary) < 1000000;

36. Find departments where the number of workers is less than 4, using HAVING

SELECT department, COUNT(worker_id) FROM worker 
GROUP BY department HAVING COUNT(worker_id) < 4;

37. Find the departments where the number of workers is greater than 5, and their average salary is above 100,000

SELECT department, COUNT(*), AVG(salary) FROM worker 
GROUP BY department HAVING COUNT(*) > 5 AND AVG(salary) > 100000;

38. Group workers by DEPARTMENT and fetch departments that have more than 2 workers with a salary greater than 90,000

SELECT department FROM worker WHERE salary > 90000 
GROUP BY department HAVING COUNT(*) > 2;

39. Display the department and the total number of workers who earn a salary greater than 80,000

SELECT department, COUNT(worker_id) FROM worker 
WHERE salary > 80000 GROUP BY department;

40. Display departments with the minimum salary greater than 60,000 using HAVING

SELECT department, MIN(salary) FROM worker 
GROUP BY department HAVING MIN(salary) > 60000;

41. Group workers by their DEPARTMENT and show only departments where the average salary is above 150,000

SELECT department, AVG(salary) FROM worker 
GROUP BY department HAVING AVG(salary) > 150000;

42. Group workers by DEPARTMENT and show the departments where the total salary is greater than 500,000 but less than 1,000,000

SELECT department, SUM(salary) FROM worker 
GROUP BY department HAVING SUM(salary) > 500000 AND SUM(salary) < 1000000;

43. Display the DEPARTMENT name and the sum of salaries where the sum is greater than 800,000

SELECT department, SUM(salary) FROM worker 
GROUP BY department HAVING SUM(salary) > 800000;

44. Find departments that have at least 3 workers and have a salary greater than 100,000

SELECT department FROM worker WHERE salary > 100000 
GROUP BY department HAVING COUNT(*) >= 3;

45. Calculate the total number of workers in each department and show only those departments with a count greater than 1

SELECT department, COUNT(*) AS worker_count FROM worker 
GROUP BY department HAVING COUNT(*) > 1;

46. Display departments with an average salary above 70,000 and group them by DEPARTMENT

SELECT department, AVG(salary) AS avg_salary FROM worker 
GROUP BY department HAVING AVG(salary) > 70000;

47. Group workers by DEPARTMENT and fetch the minimum salary of workers in each department. Only include departments with a minimum salary above 50,000

SELECT department, MIN(salary) AS min_salary FROM worker 
GROUP BY department HAVING MIN(salary) > 50000;

48. Find departments with a salary total greater than 300,000 and a count of workers greater than 2

SELECT department, SUM(salary) AS total_salary, COUNT(*) AS worker_count FROM worker 
GROUP BY department HAVING SUM(salary) > 300000 AND COUNT(*) > 2;

49. Group workers by DEPARTMENT and only include departments where the number of workers is exactly 2

SELECT department, COUNT(*) AS worker_count FROM worker 
GROUP BY department HAVING COUNT(*) = 2;

50. Calculate the average salary for each DEPARTMENT and show departments where the average salary is below 80,000

SELECT department, AVG(salary) AS avg_salary FROM worker 
GROUP BY department HAVING AVG(salary) < 80000;

51. Find the total salary and average salary for each DEPARTMENT and display only departments where the total salary is greater than 400,000

SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM worker 
GROUP BY department HAVING SUM(salary) > 400000;

52. Cast SALARY as an integer and display the result as SALARY_INT for each worker in the Worker table

SELECT worker_id, first_name, last_name, CAST(salary AS INT) AS SALARY_INT FROM worker;

53. Display the sum of SALARY as a FLOAT value

SELECT SUM(salary) AS total_salary FROM worker;

🎉 Conclusion

This blog has showcased 54 SQL queries to perform essential operations on a worker database, covering filtering, sorting, grouping, and data transformation. These foundational SQL techniques empower HR professionals and analysts to efficiently manage and analyze workforce data. Practice these queries to strengthen your SQL skills and apply them to real-world HR challenges.

Keep exploring, and happy querying!

Mastering SQL Basics: (Part 2)

Jemmy Dalsaniya — Mon, 30 Jun 2025 06:01:55 +0000

Welcome to Part 2 of our SQL Subqueries series, where we dive deeper into leveraging subqueries to unlock powerful insights from an HR database.

In this blog, we focus on a practical employee management system with tables for Employees and Departments. We’ll walk through the schema, insert sample data, and demonstrate 42 advanced SQL queries, emphasizing subqueries to address complex HR and business scenarios. This guide is perfect for data analysts, HR professionals, or developers looking to sharpen their SQL skills with real-world examples.

🧩 Database Schema Overview

`tbl_emp`: Stores employee details

Columns:

emp_id
emp_name
street_address
city
salary
commission
job
deptno
hiredate
company_name

`tbl_department`: Contains department information

Columns:

deptno
dept_name

🏗️ Creating the Tables

CREATE TABLE tbl_department (
    deptno INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE tbl_emp (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    street_address VARCHAR(100),
    city VARCHAR(50),
    salary DECIMAL(10,2),
    commission DECIMAL(10,2),
    job VARCHAR(50),
    deptno INT,
    hiredate DATE,
    company_name VARCHAR(100),
    FOREIGN KEY (deptno) REFERENCES tbl_department(deptno)
);

📝 Inserting Sample Data


INSERT INTO tbl_department (deptno, dept_name) VALUES 
(10, 'Sales'), 
(20, 'HR'), 
(30, 'IT'), 
(40, 'Finance'), 
(50, 'Marketing');

INSERT INTO tbl_emp (emp_id, emp_name, street_address, city, salary, commission, job, deptno, hiredate, company_name) VALUES 
(1, 'Amit Sharma', '123 MG Road', 'Delhi', 12000.00, 500.00, 'Salesman', 10, '2015-06-10', 'First Bank Corporation'),
(2, 'Priya Verma', '456 Park Avenue', 'Mumbai', 25000.00, 2000.00, 'Manager', 20, '2012-08-15', 'First Bank Corporation'),
(3, 'Rajesh Kumar', '789 Green Street', 'Bangalore', 18000.00, 1500.00, 'Salesman', 30, '2016-02-20', 'Second Bank Corporation'),
(4, 'Anjali Gupta', '321 Ocean Drive', 'Chennai', 9500.00, 0.00, 'Clerk', 10, '2018-07-10', 'First Bank Corporation'),
(5, 'Vikram Singh', '654 Palm Street', 'Delhi', 22000.00, 3000.00, 'Salesman', 10, '2014-04-01', 'First Bank Corporation'),
(6, 'Rina Patel', '987 Sunset Boulevard', 'Ahmedabad', 13000.00, 0.00, 'Clerk', 20, '2017-05-23', 'First Bank Corporation'),
(7, 'Manoj Desai', '852 Elm Street', 'Mumbai', 30000.00, 3500.00, 'Manager', 30, '2010-01-12', 'Second Bank Corporation'),
(8, 'Sonia Reddy', '741 Maple Lane', 'Hyderabad', 15000.00, 1000.00, 'Salesman', 30, '2019-11-02', 'First Bank Corporation'),
(9, 'Sandeep Jain', '258 High Street', 'Kolkata', 20000.00, 2500.00, 'Manager', 40, '2011-03-22', 'First Bank Corporation'),
(10, 'Neha Kapoor', '963 River Road', 'Pune', 27000.00, 1500.00, 'Clerk', 50, '2013-09-14', 'Second Bank Corporation');

🔍 Advanced SQL Subqueries for Practice

1. Employees at First Bank Corporation Earning Over $10,000

SELECT * FROM tbl_emp
WHERE company_name = 'First Bank Corporation' AND salary > 10000;

2. Select the employees in department 30

SELECT * FROM tbl_emp
WHERE deptno = 30;

3. List the names, numbers and departments of all clerks

SELECT emp_name, emp_id, deptno FROM tbl_emp
WHERE job = 'Clerk';

4. Department numbers and names of employees in departments with deptno > 20


SELECT DISTINCT d.deptno, d.dept_name
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE d.deptno > 20;

5. Employees whose commission is greater than their salaries

SELECT * FROM tbl_emp
WHERE commission > salary;

6. Employees with Commission Greater Than 60% of Salary

SELECT * FROM tbl_emp
WHERE commission > salary * 0.6;

7. List name, job and salary of all employees in department 20 who earn more than 2000/-

SELECT emp_name, job, salary FROM tbl_emp
WHERE deptno = 20 AND salary > 2000;

8. Salesmen in Department 30 with Salary Over $1,500

SELECT * FROM tbl_emp
WHERE deptno = 30 AND job = 'Salesman' AND salary > 1500;

9. Employees Who Are Managers or Presidents

SELECT * FROM tbl_emp
WHERE job='Manager' or job='President';

10. Managers Not in Department 30

SELECT * FROM tbl_emp
WHERE job = 'Manager' AND deptno != 30;

11. Managers and Clerks in Department 10

SELECT * FROM tbl_emp
WHERE deptno = 10 AND job IN ('Manager', 'Clerk');

12. Managers (Any Dept) and Clerks in Department 20

SELECT * FROM tbl_emp
WHERE job = 'Manager' OR (job = 'Clerk' AND deptno = 20);

13. Find the details of all the managers in dept. 10 and all clerks in dept 20 and all employees who are neither managers nor clerks but whose salary is more than or equal to 2000/-

SELECT * FROM tbl_emp
WHERE (job = 'Manager' AND deptno = 10)
   OR (job = 'Clerk' AND deptno = 20)
   OR (job NOT IN ('Manager', 'Clerk') AND salary >= 2000);

14. Find the names of anyone in dept. 20 who is neither manager nor clerks

SELECT emp_name FROM tbl_emp
WHERE deptno = 20 AND job NOT IN ('Manager', 'Clerk');

15. Employees earning between 1200 and 1400

SELECT * FROM tbl_emp
WHERE salary BETWEEN 1200 AND 1400;

16. Find the employees who are clerks, analysts or salesmen

SELECT * FROM tbl_emp
WHERE job IN ('Clerk', 'Analyst', 'Salesman');

17. Find the employees who are not clerks, analysts or salesmen

SELECT * FROM tbl_emp
WHERE job NOT IN ('Clerk', 'Analyst', 'Salesman');

18. Find the employees who do not receive commission

SELECT * FROM tbl_emp
WHERE commission = 0;

19. Different jobs of employees receiving commission

SELECT DISTINCT job FROM tbl_emp
WHERE commission > 0;

20. Employees with no commission or commission < 100

SELECT * FROM tbl_emp
WHERE commission = 0 OR commission < 100;

21. If all the employees not receiving commission is entitles to a bonus of Rs. 250/- show the net earnings of all the employees

SELECT emp_name, salary + commission + IF(commission = 0, 250, 0) AS net_earning
FROM tbl_emp;

22. Find all the employees whose total earning is greater than 2000/-

SELECT emp_name
FROM tbl_emp
WHERE salary + commission > 2000;

23. Find all the employees whose name begins or ends with 'M'

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE 'M%' OR emp_name LIKE '%M';

24. Find all the employees whose names contain the letter 'M' in any case

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE '%M%' OR emp_name LIKE '%m%';

25. Find all the employees whose names are upto 15 character long and have letter 'R' as 3rd character of their names

SELECT emp_name
FROM tbl_emp
WHERE LENGTH(emp_name) <= 15 AND emp_name LIKE '__R%';

26. Find all the employees who were hired in the month of February (of any year)

SELECT emp_name
FROM tbl_emp
WHERE MONTH(hiredate) = 2;

27. Find all the employees who were hired on last day of the month

SELECT emp_name
FROM tbl_emp
WHERE LAST_DAY(hiredate) = hiredate;

28. Find all the employees who were hired more than 2 years ago

SELECT emp_name
FROM tbl_emp
WHERE hiredate < CURDATE() - INTERVAL 2 YEAR;

29. Find the managers hired in the year 2003

SELECT emp_name
FROM tbl_emp
WHERE job = 'Manager' AND YEAR(hiredate) = 2003;

30. Display the names and jobs of all the employees separated by a space

SELECT CONCAT(emp_name, ' ', job) AS name_and_job
FROM tbl_emp;

31. Display the names of all the employees right aligning them to 15 characters

SELECT LPAD(emp_name, 15, ' ') FROM tbl_emp;

32. isplay the names of all the employees padding them to the right up to 15 characters with '*'

SELECT RPAD(emp_name, 15, '*') FROM tbl_emp;

33. Display the names of all the employees without any leading 'A'

SELECT TRIM(LEADING 'A' FROM emp_name) FROM tbl_emp;

34. Display the names of all the employees without any trailing 'R'

SELECT TRIM(TRAILING 'R' FROM emp_name) FROM tbl_emp;

35. Show the first 3 and last 3 characters of the names of all the employees

SELECT CONCAT(LEFT(emp_name, 3), RIGHT(emp_name, 3)) AS first_last_chars
FROM tbl_emp;

36. Display the names of all the employees replacing 'A' with 'a'

SELECT REPLACE(emp_name, 'A', 'a') FROM tbl_emp;

37. Display the names of all the employees and position where the string 'AR' occurs in the name

SELECT emp_name, POSITION('AR' in emp_name) AS position
FROM tbl_emp;

38. Show the salary of all the employees, rounding it to the nearest Rs. 1000/-

SELECT emp_name, ROUND(salary, -3) AS rounded_salary
FROM tbl_emp;

39. Display the names, jobs and salaries of employees, sorting on job and salary

SELECT emp_name, job, salary
FROM tbl_emp
ORDER BY job, salary;

40. Display the names, jobs and salaries of employees, sorting on descending order of job and within job sorted on salary

SELECT emp_name, job, salary
FROM tbl_emp
ORDER BY job DESC, salary ASC;

41. List the employee names, department names and salary for those employees who have completed 1 year of service

SELECT e.emp_name, d.dept_name, e.salary
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE e.hiredate <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

42. List the employee names, department names and hiredate for those employees who have joined in 2003 . Sort your output in the order of joining date

SELECT e.emp_name, d.dept_name, e.hiredate
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE YEAR(e.hiredate) = 2003
ORDER BY e.hiredate;

🏁 Conclusion

This second part of our SQL Subqueries series has showcased advanced queries to extract critical HR insights, with a focus on subqueries for complex filtering and comparisons.

From analyzing employee earnings to tracking hire dates and name patterns, these queries demonstrate SQL’s power in HR data management.

Use these examples as a foundation to build your own queries and drive informed business decisions.

Git and Github

Jemmy Dalsaniya — Wed, 22 Jan 2025 05:38:35 +0000

Difference between centralised and distributed version control system?

-> Centralized version control: A single copy of the project is stored in a central location, such as a server. Programmers commit their changes to this central copy.
Eg : SVN

Distributed version control: Each developer clones a copy of the project's repository to their own computer, so each developer has a local copy of the project's history
Eg : Git

In centralised there is single point failure so people mostly use the git nowadays.

Why git is called Version Control System ?

-> Because it helps you track different versions of your code and collaborate with other developers.

Diff btw git and GitHub ?

-> Git is a version control system that lets you manage and keep track of your source code history. GitHub is a cloud-based hosting service that lets you manage Git repositories

Branches and types ?

-> Main/ master , feature, release, hotfix.
Main/Master: The primary branch where the stable and production-ready code lives; all completed features, fixes, and releases are merged here.

Feature: A branch created to develop a specific feature or functionality; it is merged into the main or develop branch once the feature is complete.

Release: A branch used to finalize and prepare the code for production release, including bug fixes and final testing, before merging into the main branch.

Hotfix has been used in case like user come with the bug after release of 2 or 3 days and you need a temporary branch that needed to fix the bug.

What has been included in the .git folder ?

-> hooks − This folder contains script files. Git hooks are the scripts that are executed before or after events like commit, push etc.

objects − This folder represents an object database of Git.

config − This is the local configuration file.

refs − This folder stores information about tags and branches.

HEAD − This file stores reference to the current branch. It points to the master branch by default.

index − This is a binary file and stores staging information

Git clone vs fork ?

-> Clone create a local copy of the repository or code of other developer on the other hand fork will create a new repository coping the code of the other developer using the distributed functionality.

Git merge rebase and cherry-pick

-> cherry-pick : use the merge the specific commit using its commit id . syntax : git cherry-pick
rebase : use to merge the branch with linear order of commit on the other hand in the merge the branch commit will be after the main commit

Git fetch vs pull ?

-> Fetch use to download the code from remote to local without merging it to the current working directory on the other hand pull with merge the code to the current working directory.

What is git stash ?

-> The git stash command takes your uncommitted changes (both staged and unstaged), saves them away for later use, and then reverts them from your working copy.

What is webhook ?

-> A webhook in GitHub is a mechanism that allows GitHub to automatically send data to a server when specific events occur in a software system

Life Cycle

Important git command :

git init: Initializes a new Git repository in the current directory.
git remote add origin <url>: Links the local repository to a remote repository at the given URL.
git add .: Stages all changes in the current directory for the next commit.
git commit -m "message": Commits staged changes to the repository with a descriptive message.
git push: Uploads local commits to the remote repository.
git clone <url>: Creates a local copy of a remote repository.
git branch <branch_name>: Creates a new branch with the specified name.
git checkout <branch_name>: Switches to the specified branch.
git checkout -b <branch_name>: Creates a new branch and switches to it immediately.
git log --oneline: Displays a compact, one-line summary of commit history.
git reset --hard/soft <commit_id>: Moves the branch pointer to a specific commit, optionally keeping (soft) or discarding (hard) changes.
git merge <branch_name>: Combines changes from the specified branch into the current branch.
git rebase <branch_name>: Reapplies commits from the current branch onto the specified branch to create a linear history.
git branch : to get the list of branches

Docker All in one 1️⃣

Jemmy Dalsaniya — Sat, 04 Jan 2025 07:10:12 +0000

Define Docker : Docker is an open-source platform that automates the process of deploying applications inside lightweight, portable containers.

Define Docker image : Docker image is kind of template that consists of the code, dependencies or environment variable necessary to run the application in a container.

Define Container : Container is a software package that consists of everything that an application needed to run and function seamlessly.

Define docker daemon : A long-running background process that manages Docker objects, such as images, containers, networks, and storage volumes

Define docker Engine : technology used to create and run the containers.

Define Docker Desktop: Docker Desktop is an easy-to-install application for your Mac, Windows or Linux environment that enables you to build and share containerized applications and microservices.

Define Docker Registries: A Docker registry stores Docker images. Docker Hub is a public registry that anyone can use, and Docker is configured to look for images on Docker Hub by default. You can even run your own private registry.

Docker Architecture:

Docker lifecycle?

-> We can use the above Image as reference to understand the lifecycle of Docker.

There are three important things,

docker build -> builds docker images from Dockerfile
docker run -> runs container from docker images
docker push -> push the container image to public/private regestries to share the docker images.

Image vs Container :

image : template
container : running instance of an image that consists of the package that needed to run the app seamlessly.

what is port mapping and why it is needed : a technique that allows you to expose a container's network services to the host or other devices on the network.

what is docker layer : docker layer is a building block for docker image where for each instruction new layer has been created. And in the similar manner the final image is created. Thus layer has been build incrementally. The benefit of creating incrementally is we can cached the layer for the future use.

Docker volume : It is a file system that is used to store the data outside the container. What happens is when we delete the container or container goes down the data inside it is deleted so inorder to maintain the data we need the docker volume.

What is docker network ? : It is a feature that allows container to communicate with each other, or with the outside host.

-> when container is created by default it had bridge network which is also known as Docker 0. These connect with the localhost using VEth .

What is Veth? -> it is a virtual ethernet device that act as a cable to connect the container with the host bridge network.

What is bind mouunt : It is used to bind the file or directory of the host machine to the container.

Basic commands:

docker run <image_name>: to run the image
docker images : to list the images
docker pull <image_name> : to pull the image from the docker hub repo
docker ps : to list the running container.
docker kill <container_id>: to kill the container
docker rmi <image_name> : to remove the image
docker build -t <image_name>:latest .(location from where to build)
docker exec -it <container_id or container_name> /bin/bash
docker volume create <volume_name> : to create the volume
docker volume ls : to list the volumes created
docker run -v <volume_name>:<location> -p <port_mapping> <image_name>
docker network create <network_name>
docker logs <container_id> : to get the logs of the container if run in the detached mode
docker -v <host_location>:<container_location> <image_name> : for bind mount
docker netwok ls : to get the list of the network
docker inspect <container_name> : to get the details of the container.
docker volume inspect <volume_name> : to get the details of the volume.
docker volume rm <volume_name> : to delete the volume.

Attributes :

-d : detached mode to run the container in background and free the terminal
-p : for port mapping
-t : for giving the tag
-e : for passing the env variable
-it : to run in the interactive manner
--name : to give the name to the container
--network : to attach the network to the container
-v : for specifying the volume

Examples of docker command:

docker build -t test:latest .
docker network create check
docker run -p 3000:3000 --network=check test
docker run -p 3000:3000 --name=test_container --network=check test
docker -v /host_app:/container_app node:16-alpine or 
docker run -d --mount source=<volume_name>, target=/app <image_name>
docker run -it --name=test_mount \
  --mount type=bind,source=$(pwd)/app,target=/usr/src/app \
  node:16-alpine

Commands to install docker on ubuntu :

You can create an Ubuntu EC2 Instance on AWS and run the below commands to install docker.

sudo apt update
sudo apt install docker.io -y

You use the below command to verify if the docker daemon is actually started and Active

sudo systemctl status docker

If you notice that the docker daemon is not running, you can start the daemon using the below command

sudo systemctl start docker

To grant access to your user to run the docker command, you should add the user to the Docker Linux group. Docker group is create by default when docker is installed.

sudo usermod -aG docker ubuntu

docker run hello-world

Demo docker file:

FROM ubuntu:latest

WORKDIR /app

COPY . /app

RUN apt-get update && apt-get install -y python3 python3-pip

ENV 'key' 'value'

CMD ['python', 'app.py']

Docker multistage example :

###########################################
# BASE IMAGE
###########################################

FROM ubuntu AS build

RUN apt-get update && apt-get install -y golang-go

ENV GO111MODULE=off

COPY . .

RUN CGO_ENABLED=0 go build -o /app .

############################################
# HERE STARTS THE MAGIC OF MULTI STAGE BUILD
############################################

FROM scratch

# Copy the compiled binary from the build stage
COPY --from=build /app /app

# Set the entrypoint for the container to run the binary
ENTRYPOINT ["/app"]

Demo compose.yaml file


services:
  backend:
    build: ./mern/backend
    ports:
      - "5050:5050" 
    networks:
      - mern_network
    environment:
      MONGO_URI: mongodb://mongo:27017/mydatabase  
    depends_on:
      - mongodb

  frontend:
    build: ./mern/frontend
    ports:
      - "5173:5173"  
    networks:
      - mern_network
    environment:
      REACT_APP_API_URL: http://backend:5050 

  mongodb:
    image: mongo:latest  
    ports:
      - "27017:27017"  
    networks:
      - mern_network
    volumes:
      - mongo-data:/data/db  

networks:
  mern_network:
    driver: bridge 

volumes:
  mongo-data:
    driver: local  # Persist MongoDB data locally

Files and Folders that containers use from host operating system:

The host's file system: Docker containers can access the host file system using bind mounts, which allow the container to read and write files in the host file system.

Networking stack: The host's networking stack is used to provide network connectivity to the container. Docker containers can be connected to the host's network directly or through a virtual network.

System calls: The host's kernel handles system calls from the container, which is how the container accesses the host's resources, such as CPU, memory, and I/O.

Namespaces: Docker containers use Linux namespaces to create isolated environments for the container's processes. Namespaces provide isolation for resources such as the file system, process ID, and network.

Control groups (cgroups): Docker containers use cgroups to limit and control the amount of resources, such as CPU, memory, and I/O, that a container can access.

What is the issue in production while using docker and how can you overcome that ?

-> Even for the smaller application the docker container size increases significantly and also since we are using one base image and create a one stage docker file then it will create the os related vulnerabilities, inorder to overcome the problem we use the multi-stage docker file with the use of distroless image.

Define distroless image: A distroless image is a Docker image that contains only the essential components needed to run an application or service.
What are the advantages of distroless image?

-> It significantly reduce the size of the docker container and also it provide the highest level of security since it contains the minimal software so the os related vulnerabilities is removed using the disrtoless image.

What is the issue related to the container when it comes to the file system ?

-> Container are emperical in nature it means it does not have permanent storage so when the container is down all the file get lost. So in order to overcome this problem there are two ways. 1) Bind mounts 2) Volumes

Define Bind mounts: It is a way that you can bind the file system of container to the file system of host, and allows you to write the file onto the host system.

Define volume: It is a persistent data store that allows users to manage and store data outside of containers.

Difference between bind mounts and volumes:

-> Bind attach the specific folder to the docker however, volume create a new directory in Docker's storage directory on the host machine.

Bind is managed by the host file system however volume are managed by the docker.

Volumes can be shared between containers, but bind mounts cannot.

Also volume has a lifecycle it means you can create the volume or destroy it.

Volume can also be created using the external sources if you have less amount of space in the host system.

Diff Btw Docker COPY & ADD: Docker add copy the file from the url to the docker container while copy just copy it from the local host machine.

Diff Btw Docker CMD & ENTRYPOINT: CMD commands can be overridden, while ENTRYPOINT commands cannot.

Types of Docker Network:

Bridge networks: The default network type that is suitable for most scenarios.
Overlay networks: Used to allow containers on different Docker hosts to communicate with each other
Macvlan networks: Used to connect Docker containers to host network interfaces, making them appear as a physical device on the host's network
Host networks: Used to bind ports directly to the host's interfaces

Define multi stage build in docker: It means building the container in multiple stages and allows you to copy the artifacts from one stage to other.

Steps to secure container: You can use the distroless image, use the sync to scan the container, and ensure the network configuration properly.

Real time challenges: Docker daemon goes down then entire thing does not work, also if we create more number of containers with huge size then the process may slow down, daemon run as a root user which create the vulnerabilities at the root level.

OOPS Interview Questions 📝

Jemmy Dalsaniya — Fri, 01 Nov 2024 16:58:37 +0000

You can get the oops code here

Table of Content:

What is OOPS?
What is a Class?
What is an Object?
What is Attribites
What are Methods
What is the size of the class when it is empty?
What are Access Modifiers?
What is Padding?
What is Greedy Alignment?
What does this mean?
Shallow vs Deep Cloning
What is a Destructor?
What is the const Keyword?
What is an Initialization List?
What is the Static Keyword?
What are the Pillars of OOPS?
What is Encapsulation?
What is Inheritance?
What is Polymorphism?
What is Abstraction?
What is an Abstract Class?
What is a Pure Virtual Function?
What is a Virtual Function?
What is an Interface?
What is a Friend Function?
What are other programming paradigms besides OOP?
What is a Constructor? & Its types
Structure vs Class
What is an Exception?
What is meant by Exception Handling?
What is Garbage Collection in OOP?
What are the limitations of OOP?
What is Constructor Chaining?
What is Coupling in OOP?

OOPS

Object-oriented programming (OOP) is a computer programming model that organizes software design around data, or objects, rather than functions and logic. An object can be defined as a entity that has unique attributes and behavior.

Class:

Classes are user-defined data types that act as the template individual objects, attributes and methods.

Object:

Objects are the instance of the classes. An object can be defined as a entity that has unique attributes and behaviour.

Attributes:

Attributes represent the state of an object. In other words, they are the characteristics that distinguish classes.

Method or Behaviour:

Methods are functions that objects can perform. They are defined inside a class that describe the behaviors of an object.

What is the size of the class when it is empty??

-> 1 byte because for uniquely identify the object created or to keep track.

Access Modifiers:

It is a restriction of where we can Access the class members.

-> There are 3 Types of Access Modifiers:

Public : Can access the member from any where.
Private : Member can access within the class only
Protected : Member can acces within the class and its child members

Padding:

Padding is the technique of adding extra bytes to a structure or class to align its data members to specific boundaries. This is done to ensure that the data members are accessed efficiently by the processor, which typically fetches data from memory in chunks of fixed sizes, such as 4 or 8 bytes.

Greedy:

Greedy alignment is a technique used to minimize padding by ordering the data members of a class in decreasing order of size. This ensures that the largest data members are placed first in the class, minimizing the amount of padding required to align the subsequent data members.

this:

Refer to the current object of the class.(pointer to current object)

Shallow vs Deep Cloning:

When using copy constructors, shallow copy is when objects share same memory locations for variables. While deep copy is when objects have their own individual memory locations.

-> In shallow cloning if the member is dynamically created then creating the copy of that object will point to the same memory reference thus making change in any property will change for all the object copied but this does not happen in deep copy since they allocate different memory locations.

Destructor:

Used to deallocate the memory which is being allocate previously. It should not have parameter and return value. And starting with the '~' + 'Classname'

Eg: ~Hero(){}

-> For the statistically allocated property Destructor is automatically called. But for the dynamically allocated member you have to manually call the destructor

Eg : ~Hero(){ delete name; }

const Keyword:

🔗 https://www.javatpoint.com/const-keyword-in-cpp

Initialization List:

🔗 https://www.geeksforgeeks.org/when-do-we-use-initializer-list-in-c/

Static Keyword:

When a variable is declared as static, space for it gets allocated for the lifetime of the program. Even if the function is called multiple times, space for the static variable is allocated only once and the value of the variable in the previous call gets carried through the next function call.

Static Member Function
In a class static member is the function that is declared as static because of which function attains certain properties as defined below:

->A static member function is independent of any object of the class.

->A static member function can be called even if no objects of the class exist.

-> A static member function can also be accessed using the class name through the scope resolution operator.

->A static member function can only access static data members and static member functions inside or outside of the class. Static member functions have a scope inside the class and cannot access the current object pointer.

->You can also use a static member function to determine how many objects of the class have been created.

Pillars of OOPS

Encapsulation:

Encapsulation: Wrapping of the data members and function into a single entity is called Encapsulation.

Fully Encapsulated Class: The class is said to be Fully Encapsulated when all its members or properties is marked as private.

Why Encapsulation is needed ??

-> It is used for data hiding or information hiding.

-> Advantages :

Data hiding which increase the security concern.
If we want we can make the class read-only.
Code Resuability

Inheritance:

Inheritance: Inheritance is a mechanism of reusing and extending existing classes without modifying them, thus producing hierarchical relationships between them.

Types of Inheritance:

Single Level : The inheritance in which a single derived class is inherited from a single base class is known as the Single Inheritance.
Multilevel: When one class inherits another class it is further inherited by another class. It is known as multi-level inheritance.
Multiple : Multiple Inheritance is a feature of C++ where a class can inherit from more than one classes.
Hierarchical: one base class is inherited by more than one derived class.
Hybrid :The process of combining more than one type of Inheritance together while deriving subclasses in a program is called a Hybrid Inheritance.

Polymorphism:

Polymorphism means, the same entity (function or object) behaves differently in different scenarios.

Types of Polymorphism:

Compile Time Polymorphism:(also called static polymorphism) When the relationship between the definition of different functions and their function calls, is determined during the compile-time, it is known as compile-time polymorphism.

It has two types : 1) Function Overloading and 2) Operator Overloading

Run Time Polymorphism:(also called dynamic polymorphism): In runtime polymorphism, the compiler resolves the object at run time and then it decides which function call should be associated with that object. It is also known as dynamic or late binding polymorphism.

The type of runtime polymorphism is : Method Overriding.

Rules for method Overriding:

method must have same name
must have same parameter
possible through inheritance only

Abstraction:

It refers to showing only relevant information to the outside world. In simple words, we can say it means hiding any background information from the outside world.

It can be done through 3 ways : 1) Using access modifiers 2) Abstract Class 3) Using Data Abstraction

Abstract CLass:

Abstract class must have at least one pure virtual function. It may have variables and normal functions. The derived classes of an abstract class must implement all the pure virtual functions of their base class or else they too become abstract.

Pure Virtual Function:

A pure virtual function (or abstract function) in C++ is a virtual function for which we can have an implementation, But we must override that function in the derived class, otherwise, the derived class will also become an abstract class. A pure virtual function is declared by assigning 0 in the declaration.

Virtual Function:

A virtual function is a member function that is declared in the base class using the keyword virtual and is re-defined (Overridden) in the derived class. It tells the compiler to perform late binding where the compiler matches the object with the right called function and executes it during the runtime.

Interface:

🔗 https://www.geeksforgeeks.org/cpp-program-to-create-an-interface/

Friend function:

A friend function is a function that isn't a member of a class but has access to the class's private and protected members.

🔗 https://www.geeksforgeeks.org/friend-class-function-cpp/

What are some other programming paradigms other than OOPs?

-> Programming paradigms refers to the method of classification of programming languages based on their features. There are mainly two types of Programming Paradigms:

1) Imperative Programming Paradigm
2) Declarative Programming Paradigm

Now, these paradigms can be further classified based:

Imperative Programming Paradigm: HOW to execute program logic and defines control flow as statements.

This can be further classified as:
a) Procedural Programming Paradigm: Procedural programming specifies the steps a program must take to reach the desired state, usually read in order from top to bottom.
b) Object-Oriented Programming or OOP: Object-oriented programming (OOP) organizes programs as objects, that contain some data and have some behavior.
c) Parallel Programming: Parallel programming paradigm breaks a task into subtasks and focuses on executing them simultaneously at the same time.

Declarative Programming Paradigm: WHAT to execute and defines program logic, but not a detailed control flow.

Declarative paradigm can be further classified into:
a) Logical Programming Paradigm: refers to a set of sentences expressing facts and rules about how to solve a problem
b) Functional Programming Paradigm: Functional programming is a programming paradigm where programs are constructed by applying and composing functions.
c) Database Programming Paradigm: Database programming model is used to manage data and information structured as fields, records, and files.

What is a constructor? & Its type

-> Constructors are special methods whose name is the same as the class name. The constructors serve the special purpose of initializing the objects.

Default constructor: The default constructor is the constructor which doesn’t take any argument. It has no parameters.

Parameterized constructor: The constructors that take some arguments are known as parameterized constructors.

Copy constructor: A copy constructor is a member function that initializes an object using another object of the same class.

Structure vs CLass:

-> CLass:

-> Class is saved in heap memory.
-> It is normally used for data abstraction and further inheritance.
->Members of a class are private by default.

Structure:

-> The structure is saved in the stack memory
-> Abstraction is not possible in abstraction.
->Members of a structure are public by default.

What is an exception?

-> An exception can be considered as a special event, which is raised during the execution of a program at runtime, that brings the execution to a halt.

What is meant by exception handling?

-> The exceptions can be handled in the program beforehand and prevent the execution from stopping. This is known as exception handling. Try-catch is the most common method used for handling exceptions in the program.

What is meant by Garbage Collection in OOPs world?

-> Garbage collection refers to this mechanism of handling the memory in the program. Through garbage collection, the unwanted memory is freed up by removing the objects that are no longer needed.

What are the limitations of OOPs?

->Usually not suitable for small problems
Takes more time to solve the problem
Requires proper planning
The programmer should think of solving a problem in terms of objects.

What is constructor chaining?

Constructor chaining is a method to call one constructor from another concerning a current object reference. It can be done in two ways: –

Using the “this” keyword, the reference can be made to the constructor in the current class.
To call the constructor from the base class “super” keyword will be used.

What is Coupling in OOP, and why is it helpful?

-> The degree of dependency between the components is called coupling.

Types of Coupling:

Tight Coupling – If the dependency between components is high, these components are called tightly coupled.

Loose Coupling – If the dependency between components is low, it is called loose coupling.

Loose coupling is preferred because of the following reasons:-
It increases the maintainability of code
It provides reusability of code

Top 10 SQL Queries for Freshers 📝

Jemmy Dalsaniya — Thu, 31 Oct 2024 17:47:07 +0000

SQL Query to find the second highest salary of Employee:

-> SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

Write SQL Query to display the current date?

-> SELECT GetDate();

Write an SQL Query to find the year from date.

-> SELECT YEAR(GETDATE()) as "Year"

Write a query to fetch values in table test_a that are and not in test_b without using the NOT keyword.

-> select * from test_a except select * from test_b;

select a.id from test_a a left join test_b b on a.id = b.id where b.id is null;

Write a SQL query to find the 10th highest employee salary from an Employee table.

-> SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 9,1;

SELECT Salary FROM (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 10) AS Emp ORDER BY Salary LIMIT 1;

SELECT * from Employee as e1 where n-1 = (Select count(distinct Salary) from Employee as e2 where e1.Salary < e2.Salary)

Write an SQL query to fetch three max GPA from a table using co-related subquery.

-> SELECT DISTINCT GPA FROM Student S1 WHERE 3 >= (SELECT COUNT(DISTINCT GPA) FROM Student S2 WHERE S1.GPA <= S2.GPA) ORDER BY S1.GPA DESC;

Write a query to create a new table which consists of data and structure copied from the other table.

-> CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;

Write an SQL query to fetch the last five records from a table.

-> SELECT * FROM (SELECT * FROM Student ORDER BY STUDENT_ID DESC LIMIT 5
) AS subquery ORDER BY STUDENT_ID;

Write a query to fetch 50% records from the EmployeeInfo table.

-> SELECT * FROM EmployeeInfo WHERE EmpID <= (SELECT COUNT(EmpID)/2 from EmployeeInfo);

DBMS Interview Question for Freshers 📝

Jemmy Dalsaniya — Tue, 13 Aug 2024 04:16:40 +0000

Questions Index

Difference between Data and Information?
What is metadata & Data Dictionary?
What is Database and DBMS?
What are the 3 levels of ANSI Database?
Define Data Abstraction?
Define Mapping and Data Independence?
Types of Database Users?
Define ER Diagram?
Generalisation vs Specialization
Types of Keys?
Types of Joins?
Types of Indexing?
What is RDMS?
Advantages of DBMS over File System
Explain Different Languages Present in DBMS
What is Meant by ACID Properties in DBMS?
What is Meant by Normalization and Denormalization?
What is a Lock? Explain the Major Difference Between Shared Lock and Exclusive Lock
What is Data Warehousing?
Types of Normalization
Various Types of Anomalies
Difference Between Having and Where Clause
What is a Trigger?
What is a Stored Procedure?
What is Clustered and Non-Clustered Index?
What is a Live Lock?
What is the Purpose of SQL?
What is a Clause in Terms of SQL?
What is the Main Goal of RAID Technology?
State vs Schema
Describe the Role of a Transaction Log in a DBMS
Define SQL Injection
What is data mining in DBMS?
Char vs Varchar
What happens when u reach the max value of auto increment in SQL?
Default port for MySQL server:
How to store images in database?
Serial vs Non Serial Schedule
Serializability and Serialization
Conflict Serialization
View Serializable
Recoverable Schedule
Cascading Schedule
Cascadeless Schedule
Strict Schedule

Difference between Data and Information?

Data is raw, unprocessed, unorganized facts that are seemingly random and do not yet carry any significance or meaning.

Information refers to data that has been organized, interpreted, and contextualized by a human or machine so that it possesses relevance and purpose.

What is metadata & Data Dictionary?

A data dictionary in a Database Management System (DBMS) serves the vital purpose of providing a comprehensive repository of metadata, defining data structures, attributes, relationships, and constraints within a database. This enhances data integrity, facilitates efficient data management, and aids in database schema design.

For instance, in a healthcare DBMS, a data dictionary would detail patient records, specifying fields like ‘patient ID’, ‘name’, ‘DOB’, and associated constraints, streamlining data organization and retrieval.

What is Database and DBMS?

Database: A collection of logically related data.

DBMS (Database Management System) is a set of applications or programs that enable users to create and maintain a database. DBMS provides a tool or an interface for performing various operations such as inserting, deleting, updating, etc., into a database.

What are the 3 levels of ANSI Database?

Internal level (Physical level):

Describes how data is stored on the storage device.
Deals with physical storage of data.
Structure of records on disk - files, pages, blocks, indexes, and ordering of records.
Internal view is described by the internal schema.

Conceptual level (Logical level):

Describes what data is stored and the relationships among those data.
Hides low-level complexities of physical storage.
For example, a STUDENT database may contain STUDENT and COURSE tables, visible to users but without revealing storage details.
Database administrator works at this level to determine what data to keep in the database.

External level (View level):

It describes only part of the entire database that an end user concern or how data are viewed by each user.
Different user needs different views of the database, so there can be many views in a view level abstraction of the database. Used by end users and application programmers.
End users need to access only part of the database rather than the entire database.

Define Data Abstraction?

Data abstraction is the process of hiding unwanted and irrelevant details from the end user. It helps to store information in such a way that the end user can access data which is necessary, the user will not be able to see what data is stored or how it is stored in a database.

Define Mapping and Data Independence?

Mapping: The process of transforming requests and results between the three levels is called mapping.

Data Independence: The ability to modify a schema definition at one level without affecting schema definitions at the next higher level.

Physical Data Independence

Physical Data Independence is the ability to modify the physical schema without requiring any change in logical (conceptual) schema and application programs.
Modifications at the internal levels are occasionally necessary to improve performance.
Possible modifications at internal levels are changes in file structures, compression techniques, hashing algorithms, storage devices, etc.

Logical Data Independence

Logical data independence is the ability to modify the conceptual schema without requiring any change in application programs.
Modification at the logical levels is necessary whenever the logical structure of the database is changed.
Application programs are heavily dependent on logical structures of the data they access. So any change in logical structure also requires programs to change.

Types of Database Users?

Naive Users (End Users): Unsophisticated users who have zero knowledge of database system
End user interacts to database via sophisticated software or tools
e.g. Clerk in bank
Application Programmers : Programmers who write software using tools such as Java, .Net, PHP etc…
e.g. Software developers
Sophisticated Users : Interact with database system without using an application program
Use query tools like SQL
e.g. Analyst
Specialized Users (DBA): User write specialized database applications program. Use administration tools
e.g. Database Administrator

Define ER Diagram?

ER Diagram is a graphical representation of Database.The Entity Relational Model is a model for identifying entities to be represented in the database and representation of how those entities are related.

Generalisation vs Specialization

Generalisation: The process of taking the union of two or more lower-level entity sets to create a higher-level entity set (Bottom-up approach).
Specialization: The process of creating lower-level entity sets from a higher-level entity set (Top-down approach).

Types of Keys?

There are mainly seven types of keys in a database:
1) Candidate Key: The candidate key represents a set of properties that can uniquely identify a table.
2) Super Key: The super key defines a set of attributes that can uniquely identify a tuple.
3) Primary Key: The primary key defines a set of attributes that are used to uniquely identify every tuple.
4) Unique Key: The unique key is very similar to the primary key except that primary keys don’t allow NULL values in the column but unique keys allow them. So essentially unique keys are primary keys with NULL values.
5) Alternate Key: All the candidate keys which are not chosen as primary keys are considered as alternate Keys.
6) Foreign Key: The foreign key defines an attribute that can only take the values present in one table common to the attribute present in another table.
7) Composite Key: A composite key refers to a combination of two or more columns that can uniquely identify each tuple in a table.

Types of Joins?

A Join is one of the SQL statements which is used to join the data or the rows from 2 or more tables on the basis of a common field/column among them.

There are 4 types of SQL Joins:

Inner Join: This type of join is used to fetch the data among the tables which are common in both tables.
Left Join: This returns all the rows from the table which is on the left side of the join but only the matching rows from the table which is on the right side of the join.
Right Join: This returns all the rows from the table which is on the right side of the join but only the matching rows from the table which is on the left side of the join.
Full Join: This returns the rows from all the tables on which the join condition has been put and the rows which do not match hold null values.

Types of Indexing?

Indexing is used for the faster retrieval of the data and get the query result faster.

There are various types of indexing. They are :

Cluster Indexing: Clustered index can be defined as an ordered data file. Sometimes the index is created on non-primary key columns which may not be unique for each record. In this case, to identify the record faster, we will group two or more columns to get the unique value and create index out of them. This method is called a clustering index.
Primary Indexing: This is a type of Clustered Indexing wherein the data is sorted according to the search key and the primary key of the database table is used to create the index.
Secondary Indexing: gives us a list of virtual pointers or references to the location where the data is actually stored.
Dense Key: There is an index record for every search key in the data file.

Spare Key: Index record are not created for every search key. Here index record contain the search key and pointer to the actual address where data has been stored. And if the data has not been found then it will start the sequential search.

What is RDMS?

RDBMS stands for Relational Database Management System. RDBMS stores data in the form of tables as compared to DBMS which stores data as files and tables are related to each other result in the faster accessing of data.

Advantages of DBMS over File System

Data redundancy and inconsistency: The file system cannot control the redundancy of data as each user defines and maintains the needed files for a specific application to run. There may be a possibility that two users are maintaining the data of the same file for different applications. Hence changes made by one user do not reflect in files used by second users, which leads to inconsistency of data. Whereas DBMS controls redundancy by maintaining a single repository of data that is defined once and is accessed by many users. As there is no or less redundancy, data remains consistent.
Data sharing: The file system does not allow sharing of data or sharing is too complex. Whereas in DBMS, data can be shared easily due to a centralized system.
Data searching: For every search operation performed on the file system, a different application program has to be written. While DBMS provides inbuilt searching operations. The user only has to write a small query to retrieve data from the database.
Data integrity: There may be cases when some constraints need to be applied to the data before inserting it into the database. The file system does not provide any procedure to check these constraints automatically. Whereas DBMS maintains data integrity by enforcing user-defined constraints on data by itself.
System crashing: In some cases, systems might have crashed due to various reasons. It is a bane in the case of file systems because once the system crashes, there will be no recovery of the data that’s been lost. A DBMS will have the recovery manager which retrieves the data making it another advantage over file systems.

Explain Different Languages Present in DBMS

Following are various languages present in DBMS:

DDL(Data Definition Language): It contains commands which are required to define the database.
E.g., CREATE, ALTER, DROP, TRUNCATE, RENAME, etc.
DML(Data Manipulation Language): It contains commands which are required to manipulate the data present in the database.
E.g., SELECT, UPDATE, INSERT, DELETE, etc.
DCL(Data Control Language): It contains commands which are required to deal with the user permissions and controls of the database system.
E.g., GRANT and REVOKE.
TCL(Transaction Control Language): It contains commands which are required to deal with the transaction of the database.
E.g., COMMIT, ROLLBACK, and SAVEPOINT.

What is Meant by ACID Properties in DBMS?

ACID stands for Atomicity, Consistency, Isolation, and Durability in a DBMS these are those properties that ensure a safe and secure way of sharing data among multiple users.

Atomicity: This property reflects the concept of either executing the whole query or executing nothing at all, which implies that if an update occurs in a database then that update should either be reflected in the whole database or should not be reflected at all.
Consistency: This property ensures that the data remains consistent before and after a transaction in a database.
Isolation: This property ensures that each transaction is occurring independently of the others. This implies that the state of an ongoing transaction doesn’t affect the state of another ongoing transaction.
Durability: This property ensures that the data is not lost in cases of a system failure or restart and is present in the same state as it was before the system failure or restart.

What is Meant by Normalization and Denormalization?

Normalization is a process of reducing redundancy by organizing the data into multiple tables. Normalization leads to better usage of disk spaces and makes it easier to maintain the integrity of the database.
Denormalization is the reverse process of normalization as it combines the tables which have been normalized into a single table so that data retrieval becomes faster. JOIN operation allows us to create a denormalized form of the data by reversing the normalization.

What is a Lock? Explain the Major Difference Between Shared Lock and Exclusive Lock

A database lock is a mechanism to protect a shared piece of data from getting updated by two or more database users at the same time.

Shared Lock: A shared lock is required for reading a data item and many transactions may hold a lock on the same data item in a shared lock. Multiple transactions are allowed to read the data items in a shared lock.
Exclusive lock: An exclusive lock is a lock on any transaction that is about to perform a write operation. This type of lock doesn’t allow more than one transaction and hence prevents any inconsistency in the database.

What is Data Warehousing?

A data warehouse can be considered as a central repository where data flows from transactional systems and other relational databases and is used for data analytics. A data warehouse comprises a wide variety of an organization’s historical data that supports the decision-making process in an organization.

Types of Normalization

First Normal Form (1NF): In 1NF, each table cell should contain only a single value, and each column should have a unique name.
Second Normal Form (2NF): 2NF eliminates redundant data by requiring that each non-key attribute be dependent on the primary key. This means that there should be no partial dependency which means (prime -> nonprime)
Third Normal Form (3NF): 3NF builds on 2NF by requiring that all non-key attributes are independent of each other. There should be no transitive dependency (nonprime -> nonprime)
Boyce-Codd Normal Form (BCNF): BCNF is a stricter form of 3NF that ensures that each determinant in a table is a candidate key. This means that every prime attribute must be derived from the super key
(super key -> prime)

Various Types of Anomalies

Anomalies in DBMS arise primarily due to flawed database design, particularly when normalization rules are not properly applied. These issues manifest in three main forms:

Insertion Anomaly: Occurs when you cannot add data to the database due to the absence of other related data. For instance, being unable to add a new employee because their department does not exist in the database yet.
Deletion Anomaly: Happens when removing a record also unintentionally removes other valuable data. For example, deleting the last employee in a department might inadvertently remove the entire department from the database.
Update Anomaly: Arises when a piece of information that appears in multiple places is updated in one location but not the others, leading to inconsistent data across the database.

Difference Between Having and Where Clause

HAVING is used to specify a condition for a group or an aggregate function used in a select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike the HAVING clause, the WHERE clause cannot contain aggregate functions.

What is a Trigger?

Trigger in DBMS is a special type of stored procedure that is automatically executed in response to certain database events such as an INSERT, UPDATE, or DELETE operation. Triggers can be used to perform actions such as data validation, enforcing business rules, or logging.

What is a Stored Procedure?

A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.

What is Clustered and Non-Clustered Index?

Clustered Index : A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.
Non-Clustered Indexes : A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.

What is a Live Lock?

Livelock situation can be defined as when two or more processes continually repeat the same interaction in response to changes in the other processes without doing any useful work These processes are not in the waiting state, and they are running concurrently. This is different from a deadlock because in a deadlock all processes are in the waiting state.

What is the Purpose of SQL?

SQL stands for Structured Query Language whose main purpose is to interact with the relational databases in the form of inserting, deleting and updating/modifying the data in the database.

What is a Clause in Terms of SQL?

This is used with the SQL queries to fetch specific data as per the requirements on the basis of the conditions that are put in the SQL. This is very helpful in picking the selective records from the complete set of records.

What is the Main Goal of RAID Technology?

RAID stands for Redundant Array of Inexpensive (or sometimes “Independent”)Disks.

RAID is a method of combining several hard disk drives into one logical unit (two or more disks grouped together to appear as a single device to the host system). RAID technology was developed to address the fault-tolerance and performance limitations of conventional disk storage.

State vs Schema

The collection of information stored in a database at a particular moment in time is called database state while the overall design of the database is called the database schema.

Describe the Role of a Transaction Log in a DBMS

A transaction log (also known as a redo log or audit trail) is a file that records all changes made to the database during transactions. It serves several important purposes:

Recovery: In the event of a system failure, the transaction log can recover the database to a consistent state by replaying or undoing transactions.
Concurrency Control: The transaction log can support concurrency control mechanisms such as locking and rollback, ensuring that transactions are isolated and maintaining data integrity.
Audit Trail: The transaction log records all changes made to the database, enabling auditing and compliance with regulatory requirements.

Define SQL Injection

SQL injection, also known as SQLI, is a common attack vector that uses malicious SQL code for backend database manipulation to access information that was not intended to be displayed.

What is data mining in DBMS?

Data mining is the process of sorting through large data sets to identify patterns and relationships that can help solve business problems through data analysis.

Char vs Varchar

Char:

CHAR datatype is used to store character strings of fixed length. In CHAR, if the length of the string is less than set or fixed-length then it is padded with extra memory space.

Varchar:

In VARCHAR, if the length of the string is less than the set or fixed-length then it will store as it is without padded with extra memory spaces. VARCHAR datatype is used to store character strings of variable length.

What happens when u reach the max value of auto increment in SQL?

When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails.

Default port for MySQL server:

3306

How to store images in database?

There are several ways to store images in a database, including as binary data, file paths, or using cloud storage. The best method depends on the specific requirements and constraints of the project. Other way can be storing images in SQL databases using BLOB (Binary Large Object) data types.

Serial vs Non Serial Schedule

Serial: Schedules in which the transactions are executed non-interleaved, i.e., a serial schedule is one in which no transaction starts until a running transaction has ended are called serial schedules.

Non Serial: The type of scheduling in which transaction is interleaved and more than one transaction execute concurrently then such type of scheduling is called Non Serial.

Serializability and Serialization

Serializability in DBMS ensures that multiple transactions can access and modify the same data without interfering with each other's operations. It helps to prevent data inconsistencies and anomalies that can occur when multiple transactions try to access and modify the same data concurrently.

Serialization refers to the process of managing transactions in such a way that their operations are executed in a serial (one after another) order, ensuring the consistency and isolation of data. This process is crucial for maintaining database integrity when multiple transactions are executed concurrently.

Conflict Serialization

A schedule is called conflict serializable if non-serial schedule can be transformed into a serial schedule by swapping non-conflicting operations.

View Serializable

A Schedule is called view serializable if it is view equal to a serial schedule (no overlapping transactions). A conflict schedule is a view serializable but if the serializability contains blind writes, then the view serializable does not conflict serializable.

Recoverable Schedule

Even if the schedule is conflict or view serializable still it does guarantee that the transaction is consistent considering the case of system failure. Schedules in which transactions commit only after all transactions whose changes they read commit are called recoverable schedules. In other words, if some transaction Tj is reading value updated or written by some other transaction Ti, then the commit of Tj must occur after the commit of Ti.

Cascading Schedule

When there is a failure in one transaction and this leads to the rolling back or aborting other dependent transactions, then such scheduling is referred to as Cascading rollback or cascading abort.

Cascadeless Schedule

Schedules in which transactions read values only after all transactions whose changes they are going to read commit are called cascadeless schedules.

Strict Schedule

Tj can read or write updated or written value of Ti only after Ti commits/aborts.

Blockchain Interview Questions for Freshers 📝

Jemmy Dalsaniya — Thu, 08 Aug 2024 07:30:34 +0000

What is Blockchain?
What is Byzantine Fault Tolerance?
What is Consensus Protocol?
What is a Block?
What values does a Block contain?
What is the Genesis Block?
What is a Ledger? How is Blockchain's distributed ledger different from a traditional ledger?
What is Cryptography?
What are the different types of Blockchain?
What is a Smart Contract and its applications?
What is the Ethereum Network? How many Ethereum networks are there?
Where do nodes run smart contract code? What is EVM?
What is a Dapp?
What is Proof of Work?
What is Proof of Stake?
What is the nonce, and how is it used in mining?
List and explain the parts of EVM memory.
What does gas usage depend on, and how is the transaction fee calculated?
What is a 51% attack?
What are function modifiers in Solidity? Mention the most widely used modifiers.
What features does Blockchain provide?
What are Merkle Trees? Explain the concept.
How do verifiers check if a block is valid?
What is a trapdoor function, and why is it needed in blockchain development?
What is mining?
What is DAO and DAO Attack?
What is chain forking and its types?
What is Blockchain Wallet and How Does It Work?
What is pruning?
Blockchain vs Hyperledger?
Can You explain the components of Blockchain Ecosystem?
What is NFT?
Explain interoperability.
What is Mempool?

What is Blockchain?

Blockchain is a distributed, immutable ledger that stores transaction details in the form of permanent records called blocks, which are secured through cryptography.

What is Byzantine Fault Tolerance?

The recovery mechanism allow the algorithm to tolerate any number of faults over the lifetime of the system provided fewer than 1/3 of the replicas become faulty within small window of vulnerability.

In simple words if the block wanted to get inserted in blockchain or if any transaction have to be done in the blockchain , it has to be approved by the 66.66% or 2/3 of peers or block miners even though 1/3 block miner or peers reject it.

What is Consensus Protocol?

The Consensus protocol says that if only 51% of miners have the majority to add the block then that block will be added to the blockchain. Thus it need only 51% majority while Byzantine Fault Tolerance need approximately 67%.

What is a Block?

Blocks are files stored by a blockchain, where transaction data are permanently recorded. A block records some or all of the most recent transactions not yet validated by the network. Once the data are validated, the block is closed. Then, a new block is created for new transactions to be entered into and validated.

What values does a Block contain?

A block contains:

Nonce: A random number for mining and validation
Timestamp: Date and time of creation
Data: Information or transactions in the block
Prev Hash: Hash of the previous block, linking it to the current block
Hash: Unique identifier of the block, generated by hashing its contents

What is the Genesis Block?

The genesis block is the first block in the Blockchain which is also known as block 0. In Blockchain, it is the only block that doesn’t refer to its previous block.
It defines the parameters of the Blockchain such as,level of difficulty,consensus mechanism etc. to mine blocks

What is a Ledger? How is Blockchain's distributed ledger different from a traditional ledger?

A ledger is a constantly growing file. It maintains a permanent record of all transactions between two parties on the blockchain network. Blockchain ledgers are transparent, irreversible, and secure. They have no central authority and no single point of failure, and they share a copy of the ledger among all participants.

What is Cryptography?

Blockchain uses cryptography to secure identities and ensure safe transactions with a hash function. SHA-256, a cryptographic algorithm, generates a 64-character output for any input, which is irreversible.

What are the different types of Blockchain?

There are three types:

Public Blockchain: Visible to all users; open for any user to add transactions (e.g., Bitcoin, Ethereum)
Private Blockchain: Permissioned, specific users can verify and add transactions (e.g., Blockstack)
Consortium Blockchain: Consensus controlled by specific nodes, but visible to all participants (e.g., Ripple)

What is a Smart Contract and its applications?

Smart contracts are self-executing contracts with agreed terms between parties. They’re used in various applications like financial services, supply chain management, and legal agreements.

What is the Ethereum Network? How many Ethereum networks are there?

Ethereum is a blockchain-based distributed computing platform featuring smart contract functionality that enables users to create and deploy their decentralized applications

There are three types of networks in Ethereum:

Live network (main network) - Smart contracts are deployed on the main network
Test network (like Ropsten, Kovan, Rinkeby) - Allow users to run their smart contracts with no fees before deploying it on the main network.
Private network - Are those which are not connected to the main network. They run within the premises of the organization but carry the features of an Ethereum network.

Where do nodes run smart contract code? What is EVM?

Nodes run smart contracts code on Ethereum Virtual Machine (EVM). It is a virtual machine designed to operate as a runtime environment for Ethereum-based smart contracts.

EVM is operated in a sandboxed environment (isolated from the main network). This is a perfect testing environment.

You can download the EVM, run your smart contract locally in an isolated manner and once you have tested and verified it, you can deploy it on the main network.

What is a Dapp?

A Dapp is a decentralized application which is deployed using smart contract. A Dapp has its back-end code (smart contract) which runs on a decentralized peer-to-peer network

What is Proof of Work?

In Blockchain, PoW is the process of solving a complex mathematical puzzle called mining. Here, the probability of mining a block is based upon the amount of computational work done by a miner. Miners spend a lot of computing power (with hardware) for solving the cryptographic puzzle.

What is Proof of Stake?

PoS is an alternative to PoW in which the Blockchain aims to achieve distributed consensus. The probability of validating a block relies upon the number of tokens you own. The more tokens you have, the more chances you get to validate a block. It was created as a solution to minimize the use of expensive resources spent in mining.

What is the nonce, and how is it used in mining?

In Blockchain, mining is a process to validate transactions by solving a difficult mathematical puzzle called proof of work. Now, proof of work is the process to determine a number (nonce) along with a cryptographic hash algorithm to produce a hash value lower than a predefined target. The nonce is a random value that is used to vary the value of hash so that the final hash value meets the hash conditions.

List and explain the parts of EVM memory.

Storage:Storage values are stored permanently on the Blockchain network.It is extremely expensive
Memory:Memory is a temporary modifiable storage. It can be accessed only during contract execution. Once the execution is finished, its data is lost
Stack:A stack is temporary and non-modifiable storage.Here, when the execution completes, the content is lost. Back to Top↑

What does gas usage depend on, and how is the transaction fee calculated?

Gas usage depends on storage and instructions in a smart contract. The transaction fee is calculated as:

Ether = Tx Fees = Gas Limit * Gas Price

What is a 51% attack?

In Blockchain, a 51% attack refers to a vulnerability where an individual or group of people controls the majority of the mining power (hash rate). This allows attackers to prevent new transactions from being confirmed. Further, they can double-spend the coins. In a 51% attack, smaller cryptocurrencies are being attacked.

What are function modifiers in Solidity? Mention the most widely used modifiers.

It can build additional features or apply restrictions on the function of smart contracts. The most extensively used function modifiers in solidity are:

View, which are functions that cannot modify the state of a smart contract. They are read-only functions. Refer to our video to see an example of a View function
Pure, which are functions that neither read nor write the state of a smart contract. They return the same result determined by its input values. Refer to our video to see an example of a Pure function

What features does Blockchain provide?

Decentralized: Blockchain is a decentralized technology. There is no central governing authority that manages the network. Rather every node has a copy of the ledger and each node contribute to maintaining the Blockchain network.
Immutable: Data stored in a blockchain cannot be altered or manipulated. Once a transaction is added to the ledger, it resides there permanently.
Security: It uses a strong encryption algorithm like SHA-256 which ensures another layer of security.
Open Ledger: The ledger is the record of transactions done and because it is visible to everyone, therefore is called an open ledger. Every node in the network has a copy of the ledger. So there is trust among participants as they can clearly check what is happening in the network.
Consensus Mechanism: Blockchain works on some consensus protocols. A consensus algorithm is a procedure through which all the nodes of the Blockchain network reach a common agreement about the present state of the distributed ledger.

What is Merkle Trees? Explain the concept.

Merkle trees data structure is also called a binary hash tree. It helps to verify whether a transaction can be added to a block or not. Every transaction is hashed through the proper algorithms. Each leaf node is a hash of the transaction and the non-leaf node is a hash of its previous hashes (child hashes). The hash generated at the end (or top of the tree) is called the Merkle Root.

This Merkle root is stored in the block header. Thus, a block header contains Merkle Root, Hash of the previous block, Timestamp, and Nonce. All of them help to make the block tamper-proof and maintain the integrity of the data.

This Merkle Tree structure is used by both Bitcoin and Ethereum.

How do verifiers check if a block is valid?

The block data structure is syntactically valid
The block header hash is equal to or less than the target (enforces the Proof-of-Work)

The block timestamp is less than two hours in the future (allowing for time errors)

The block size is within acceptable limits

The first transaction (and only the first) is a coinbase transaction

All transactions within the block are valid using the transaction checklist discussed in Independent Verification of Transactions
Back to Top↑

What is a trapdoor function, and why is it needed in blockchain development?

A trapdoor function is a function that is easy to compute in one direction but difficult to compute in the opposite direction unless you have special information. Trapdoor functions are essential for public key encryption—that’s why they are commonly used in blockchain development to represent the ideas of addresses and private keys.

What is mining?

Mining is the process of reaching consensus in blockchain networks. Mining serves two purposes. First, it creates new coins in a generated block. Second, it includes transactions in a distributed ledger by providing proof of work to the network; that is, proof that the generated block is valid.

What is DAO and DAO Attack?

A decentralized autonomous organization (DAO) is a self-governing organization implemented on the blockchain. It operates based on smart contracts and allows members to participate in decision-making and governance without central authority.

On June 17, 2016, the DAO was subjected to an attack exploiting a combination of vulnerabilities, including the one concerning recursive calls, that resulted in the transfer of 3.6 million Ether - around a third of the 11.5 million Ether that had been committed to The DAO - valued at the time at around $50 million.

“The DAO” is the name of the first DAO launched by ‘Slock.it‘, a blockchain firm built on the Ethereum platform.

The DAO was a type of investor-directed venture capital fund created through a token sale, and it grew to become one of the greatest crowdfunding campaigns in history.

What is chain forking and its types?

Forks in blockchain mean copying the code and modifying it to create a new software or product.

There are two types of forking:

Hard Forking: When the blockchain protocol is altered in a non-backwards-compatible way. When there is a change in the software that runs on the full nodes to function as a network participant, the change is such that the new blocks mined on the basis of new rules (in the Blockchain protocol) are not considered valid by the old version of the software.

Soft Forking: When the blockchain protocol is altered in a backwards-compatible way. In a soft fork, you tend to add new rules such that they do not clash with the old rules.

Example: Bitcoin Cash and Bitcoin, Ethereum and Ethereum Classic.

What is Blockchain Wallet and How Does It Work?

A blockchain wallet is a piece of digital software that stores private and public keys, as well as tracks and records all transactions involving those keys on the blockchain. A blockchain wallet, in theory, does not store cryptocurrency; instead, all records belonging to these keys are stored on the blockchain on which the wallet is hosted.

Blockchain wallets have public and private keys. A public key and a private key are used in a similar way in blockchain wallets. A public key is similar to an email address in that it can be shared with others. When your wallet is created, a public key is created as well, which you can share with others to obtain funds. The private key is a closely guarded secret. It’s similar to your password in that it shouldn’t be compromised, and you shouldn’t share it.

What is pruning?

Pruning in blockchain is a process where old, unnecessary data is removed to save space and keep the blockchain efficient. It focuses on retaining only the essential information needed for the current state of the blockchain, allowing it to operate without storing the full history of all past transactions. This helps reduce storage requirements and improve performance.

Blockchain vs Hyperledger?

Blockchain: A decentralized, distributed ledger technology that records transactions across many computers securely and transparently. It's often public and open for anyone to participate in, like Bitcoin and Ethereum.

Hyperledger: A project under the Linux Foundation that provides a framework for building private, permissioned blockchain networks. Unlike public blockchains, Hyperledger networks are designed for businesses, where only authorized participants can access and contribute to the ledger. It's more focused on confidentiality and control within a consortium of trusted parties.

Can You explain the components of Blockchain Ecosystem?

Components of a blockchain: A blockchain network consists of nodes, a distributed ledger, an asset, and a consensus algorithm. Sometimes, it also includes a virtual machine and a state database.

Nodes: Nodes form the structure of the blockchain network. Nodes on the network are tasked with different responsibilities, such as a miner and a validator.

Distributed ledger: The distributed ledger is also known as the database of the blockchain. Every node on the network has a copy of the ledger to provide fault tolerance to the network. A ledger is immutable and visible to everyone on the blockchain network.

Asset: An asset can be virtually anything, either physical or non-physical, having a value recognized by the nodes in the network. Some examples of assets are as follows:

Financial transactions: Blockchains like Bitcoin and Dogecoin use a ledger to store the data of transactions of their cryptocurrencies.

Code blocks: Blockchains like Ethereum use it to store code in the form of smart contracts, which serves as the basis of the concept of Dapps.

Medical records: Private blockchains usually store patients’ medical records to maintain integrity in an untrustworthy environment.

Business transactions: Businesses can use private blockchains while performing transactions involving other businesses to ensure the integrity of the product and that certain conditions are met, leading to more security and accountability.

Consensus algorithm: The consensus algorithm sends the transaction performed by the miner to the validator nodes to reach a consensus. If consensus is reached, the transaction is added to the ledger, or else it is discarded.

Virtual machine: Blockchains like Ethereum run virtual machines on their nodes. They are used to execute code written in smart contracts. This is done to ensure that if the code being executed on the nodes contains malware, it will not affect the node executing the code. Instead, it will just affect the virtual machine running on top of the actual hardware, saving the node.

State Database: It is a key-value database that represents the current state of the network. It is calculated by traversing the ledger. It is used to save time during transactions, as during every transaction, the updated state of the network is required, and the traversal of the whole ledger in every transaction will slow down the network.

What is NFT?

Non-fungible tokens, often referred to as NFTs, are blockchain-based tokens that each represent a unique asset like a piece of art, digital content, or media. An NFT can be thought of as an irrevocable digital certificate of ownership and authenticity for a given asset, whether digital or physical.

Explain interoperability.

Interoperability refers to the ability of different blockchains or DApps to communicate with each other, exchange data, and interact with each other seamlessly. It allows for the flow of information and value across different blockchain networks.

What is Mempool?

The mempool acts as a waiting area for unmined blockchain transactions. These transactions have been validated but still need to be added to the blockchain. The phrase "mempool" is a contraction of "memory" and "pool," and it refers to a node's storage area where validated transactions wait to be mined and added to the blockchain.

OS interview Questions for Freshers 📝

Jemmy Dalsaniya — Tue, 06 Aug 2024 14:56:55 +0000

What is OS?
Modes of OS?
Define system call
Function of the OS
Types of Operating Systems
Define process & Thread
What is process control block?
What is a Pipe and when it is used?
What is bootstrap program?
Explain demand paging
Define Race condition
Define Process synchronization
What is the difference between main memory and secondary memory?
What is Critical Section?
What is thrashing in OS?
What do you mean by Sockets in OS?
What do you mean by cascading termination?
Define Aging
What is the difference between paging and segmentation?
What is virtual memory?
What do you mean by Semaphore in OS? Why is it used?
Difference between micro kernel and monolithic kernel?
What do you mean by Belady’s Anomaly?
Necessary Conditions for Deadlock
What are overlays?
What is Buffer?
What is Banker's Algorithm?
Logical Address vs Physical Address?
What is Interrupt?
What is the Direct Access Method?
What is Cycle Stealing?
What is a trapdoor in OS?
What is a dispatcher?
What is a critical section?
Additional Topics

What is OS?

An Operating System (OS) is a collection of software that manages computer hardware resources and provides common services for computer programs. It acts as an interface between the software and computer hardware.

Back to Top↑

Modes of OS

User mode: Applications do not have direct access to system resources; they must make system calls. A failure in user mode affects only that process, with each process having a separate virtual address space.

Kernel mode: Programs have direct access to system resources, and a failure can crash the entire OS. All processes share a single virtual address space.

Back to Top↑

Define system call

A system call is a mechanism used by programs to request services from the operating system (OS). In simpler terms, it is a way for a program to interact with the underlying system, such as accessing hardware resources or performing privileged operations.

Function of the OS

Memory and Processor Management
Providing User Interface
File and Device Management
Resource Scheduling
Error Detection
Security

Types of Operating Systems

There are several types of Operating Systems which are mentioned below.

Batch Operating System : This type of operating system does not interact with the computer directly. There is an operator which takes similar jobs having the same requirements and groups them into batches.
Multi-Programming System : Multiprogramming Operating Systems can be simply illustrated as more than one program is present in the main memory and any one of them can be kept in execution.
Multi-Processing System : Multi-Processing Operating System is a type of Operating System in which more than one CPU is used for the execution of resources
Multi-Tasking Operating System : Multitasking Operating System is simply a multiprogramming Operating System with having facility of a Round-Robin Scheduling Algorithm. It can run multiple programs simultaneously.
Time-Sharing Operating System : Each task is given some time to execute so that all the tasks work smoothly. Each user gets the time of the CPU as they use a single system. The time that each task gets to execute is called quantum.
Distributed Operating System : A distributed System is a collection of autonomous computer systems that are physically separated but are connected by a centralized computer network that is equipped with distributed system software
Network Operating System : a network operating system(NOS) is software that connects multiple devices and computers on the network and allows them to share resources on the network
Real-Time Operating System : A real-time operating system (RTOS) is a special kind of operating system designed to handle tasks that need to be completed quickly and on time.

There are two types of RTOS :

Hard real time : missing an occasional deadline can cause any permanent damage. Many of these are found in
industrial process control, car engine control system.

Soft real time : missing an occasional deadline does not cause any permanent damage. Used in digital audio,
multimedia system.

Define process & Thread

Process: Program in execution.
Thread: Segment of a process.

Process States: new, ready, run, block, exit.

Back to Top↑

What is process control block?

A Process Control Block (PCB) is a data structure maintained by the OS for every process.

What is a Pipe and when it is used?

The pipe is generally a connection among two or more processes that are interrelated to each other. It is a mechanism that is used for inter-process communication using message passing. One can easily send information such as the output of one program process to another program process using a pipe. It can be used when two processes want to communicate one-way i.e., inter-process communication (IPC).

What is bootstrap program?

It is generally a program that initializes OS during startup i.e., first code that is executed whenever computer system startups. OS is loaded through a bootstrapping process or program commonly known as booting.

Explain demand paging

Demand paging is a method that loads pages into memory on demand. This method is mostly used in virtual memory. In this, a page is only brought into memory when a location on that particular page is referenced during execution.

Define Race condition

The situation where several processes access and manipulate shared data concurrently. The final value of the shared data depends upon which process finishes last.

Define Process synchronization

Process synchronization in OS is the task of coordinating the execution of processes in such a way that no two processes can access the same shared data and resources.

What is the difference between main memory and secondary memory?

Main memory: Main memory in a computer is RAM (Random Access Memory).The programs and data that the CPU requires during the execution of a program are stored in this memory.
- Secondary memory: Secondary memory in a computer are storage devices that can store data and programs. Such storage devices are capable of storing high-volume data. Storage devices can be hard drives, USB flash drives, CDs, etc.

What is Critical Section?

Critical Section: The part of program where the shared resource is accessed is called critical section or critical region.

What is thrashing in OS?

It is generally a situation where the CPU performs less productive work and more swapping or paging work. It spends more time swapping or paging activities rather than its execution.

What do you mean by Sockets in OS?

The socket in OS is generally referred to as an endpoint for IPC (Interprocess Communication). Here, the endpoint is referred to as a combination of an IP address and port number.

What do you mean by cascading termination?

Cascading termination is a process termination in which if the parent process is exiting or terminating then the children process will also get terminated. It does not allow the child to continue processing as its parent process terminates. It is generally initiated by OS.

Define Aging

It simply increases the priority of processes that wait in the system for resources for a long period of time.

What is the difference between paging and segmentation?

Paging: It is generally a memory management technique that allows OS to retrieve processes from secondary storage into main memory. It is a non-contiguous allocation technique that divides each process in the form of pages.
Segmentation: It is generally a memory management technique that divides processes into modules and parts of different sizes. These parts and modules are known as segments that can be allocated to process.

What is virtual memory?

It is a memory management technique feature of OS that creates the illusion to users of a very large (main) memory. It is simply space where a greater number of programs can be stored by themselves in the form of pages.

What do you mean by Semaphore in OS? Why is it used?

Semaphore is a normal variable that is used to control access to shared resources in multi-threaded or multi-process systems. It maintains a count of available resources and provides two atomic operations: wait() and signal().

Types of Semaphores

There are two main types of semaphores:

Binary semaphore: A binary semaphore is a synchronization object that can only have two values: 0 and 1.
Counting semaphore: A counting semaphore is a synchronization object that can have a value greater than 1.

Difference between micro kernel and monolithic kernel?

MicroKernel :

In this software or program, kernel services and user services are present in different address spaces.
If a service crashes, it does affect on working of the microkernel.

Monolithic Kernel:

In this software or program, kernel services and user services are usually present in the same address space.
If a service crashes, the whole system crashes in a monolithic kernel.

What do you mean by Belady’s Anomaly?

Belady’s Anomaly is a phenomenon in which if we increase the number of frames in memory, then the number of page faults also increases. It is generally experienced when we use FIFO (First in First out) page replacement algorithm.

Necessary Conditions for Deadlock

There are basically four necessary conditions for deadlock as given below:

Mutual Exclusion: Mutual Exclusion condition requires that at least one resource be held in a non-shareable mode, which means that only one process can use the resource at any given time.
Hold and Wait: The hold and wait condition specifies that a process must be holding at least one resource while waiting for other processes to release resources that are currently held by other processes.
No Pre-emption: resources cannot be taken forcibly from a process a process can only release resources voluntarily after completing its task.
Circular Wait or Resource Wait: This condition implies that circular processes must exist, with each process waiting for a resource held by the next process in the chain.

What are overlays?

The concept of overlays is that whenever a process is running it will not use the complete program at the same time, it will use only some part of it.

What is Buffer?

A buffer is a memory area that stores data being transferred between two devices or between a device and an application.

What is Banker's Algorithm?

The Bankers Algorithm in OS is a deadlock avoidance algorithm used to avoid deadlocks and to ensure safe execution of processes. The algorithm maintains a matrix of maximum and allocated resources for each process and checks if the system is in a safe state before allowing a process to request additional resources.

Logical Address vs Physical Address?

Logical:

Logical Address Space is a set of all logical addresses generated by the CPU in reference to a program.
Logical address are generated by CPU.
Users can view the logical address of a program.

Physical Address:

Physical Address is a real address where actual data has been stored in the machine and set of all physical addresses mapped to the corresponding logical addresses.
Computed by MMU.
Users can never view the physical address of the program.

What is Interrupt?

The interrupt is a signal emitted by hardware or software when a process or an event needs immediate attention. It alerts the processor to a high-priority process requiring interruption of the current working process. In I/O devices one of the bus control lines is dedicated for this purpose and is called the Interrupt Service Routine (ISR).

What is the Direct Access Method?

Direct memory access (DMA) is a method that allows an input/output (I/O) device to send or receive data directly to or from the main memory, bypassing the CPU to speed up memory operations. The process is managed by a chip known as a DMA controller (DMAC).

What is Cycle Stealing?

Cycle stealing is a method of accessing computer memory (RAM) or bus without interfering with the CPU. It is similar to direct memory access (DMA) for allowing I/O controllers to read or write RAM without CPU intervention.

What is a trapdoor in OS?

A trap door is a method of gaining access to information or OS features that are normally beyond a user's access authority. The distinguishing feature of a trap door is that it is intentionally hidden and used only when needed.

What is a dispatcher?

The dispatcher is the module that gives process control over the CPU after it has been selected by the short-term scheduler. This function involves the following:

Switching context
Switching to user mode
Jumping to the proper location in the user program to restart that program

What is a critical section?

When more than one processes access the same code segment that segment is known as the critical section. The critical section contains shared variables or resources which are needed to be synchronized to maintain the consistency of data variables.

Additional topics to be done

Critical section problem
Solution for critical section: Software, Hardware, OS based solution
Peterson Algorithm
Banker Algorithm
Semaphore in detail
Semaphore solution for the Producer Consumer Problem, Dinning Philosopher Problem, Reader Writer Problem. Back to Top↑

Redis For Beginners

Jemmy Dalsaniya — Mon, 10 Jun 2024 18:14:05 +0000

Basic Overview 📖

Why Redis is needed ??

Ans: In contemporary application development, Redis plays a critical role by serving as a highly efficient in-memory data store. Its primary function is to cache computed data, which significantly reduces server load caused by excessive database queries. This caching mechanism ensures that frequently accessed data can be retrieved quickly, thus minimizing response times.

By integrating Redis, applications achieve two key optimizations:

Reduced Query Load: Redis stores frequently requested data in memory, alleviating the need for repeated queries to the primary database. This not only reduces the workload on the database server but also enhances overall system performance by avoiding bottlenecks associated with frequent data retrieval operations.

Enhanced Response Time: When data is cached in Redis, it can be returned to the user almost instantaneously. This rapid data access is crucial for applications requiring real-time responses, thereby improving user experience and making the application more responsive and efficient.

Define Redis: Redis is an open source in-memory data store that can be used as a database, cache, or message broker. It's often used for caching web pages and reducing the load on servers.Redis run on the port number 6379
Redis have various data-types to store the data. It includes String,Set,List,Hash,etc

Redis Architecture: ⚙️

Cache Lookup: Upon receiving a request, the system first checks Redis to see if the requested data is already cached. If the data is found (cache hit), it is immediately returned to the client, ensuring a fast response.
Cache Miss Handling: If the data is not found in Redis (cache miss), the system then queries the persistent database to retrieve the necessary data. This ensures that the data retrieval process continues, albeit at a slightly slower pace compared to a cache hit.
Cache Priming: Once the data is fetched from the persistent database, it is not only returned to the client but also stored in Redis. This step, known as priming the cache, ensures that subsequent requests for the same data can be served quickly from the cache, improving overall system performance.

Installation ⚒️

Firstly we create the redis container in Docker. For that you must have installed the Docker Desktop. You may visit my blog inorder to know how to install the Docker: Click here
Open the Docker Desktop and open the command line and paste the following command.

docker run -d --name redis-stack -p 6379:6379 -p 8001:8001 redis/redis-stack:latest

As soon as you run the command you will find the container named "redis stack" running in the docker desktop.

Open the chrome and write localhost:8001, if you get following output then your redis installation is successfull:

Now write the following command to get into the container shell :

docker exec -it redis-stack bash and write redis-cli --version to get the redis version :

Write the redis-cli command to get into the redis server

Now you are ready to write the redis command and execute.

Setting up in VS Code in order to code along

Create a folder in vs code and do npm install in terminal. You have to install ioredis package to use it with nodejs. Run the following command to do it :
npm i ioredis
Make a file name client.js and write the code shown below: code

Commands for Strings:

-> set key value : For eg. set name redis : To set the data

-> get key: for eg. get name => "redis" : To get the data

-> set key value nx : Here nx is a special command that will cached the data only if not already cached before.

-> mget key1 key2 key3 : To simultaneously get more than 1 cached data

-> mset key1 "value1" key2 "value2": To set multiple cached data simultaneously

-> incr key : to increment the value by 1

-> incrby key n : to increment value by n

-> expire key nsec : will delete the key value after n sec

Implementing string data type in nodejs: code

Additional Commands :

-> SETRANGE key offset value
-> GETRANGE key start end
-> SUBSTR key start end

Commands for List:

-> lpush key value: push the element into the list from left side

-> rpush key value: push element into the list from right side

-> lrange key start stop: return the element in range

-> llen key value: return the length of the list

-> lpop key value: removes and return the head of the list

-> del key: to delete the list

Similar like it there is other command to do operation from right side such as rpush, rpop, etc.
Additional Commands :

-> LMOVE source destination : atomically moves elements from one list to another.
-> LRANGE key start stop : extracts a range of elements from a list.
-> LTRIM key start stop : reduces a list to the specified range of elements.

Implementing list data type in nodejs: code

Commands for Set:

-> sadd key value: to add value to the set

->srem key value: to remove from the set

-> sismember key value: to check that value present in set or not

-> sinter key1 key2: to get the common element between two set

-> smembers key: to get the all element in set

Commands for Hash:

-> hset key field value field value: to add the data to the hash set

-> hget key field : to get the value of particular field

->hget all key: to get all the value of key

-> hmget key field1 field2: : to get the value of multiple field of key

-> hincrby key value n : to increment field value by n

Implementing hash data type in nodejs: code

Commands for Sorted Set:

->zadd key n value: sort with according to the lowest value of n

-> zrange key start stop: to get all the value

-> zrevrange key start stop: to get all the value in reverse order

-> zrank key value: to get the rank of the given value

Practical Example of Fetching todo list:

Code :

Before implementing Redis :

After implementinf Redis:

Thus we can see that after implementing redis the time taken to fetch the todos is very less compare to the previous one. This is because the data is already cached and get it directly from there.

Conclusion

In this blog, we delved into the essential role of Redis in modern application development. By serving as a high-performance in-memory data store, Redis significantly reduces server load and enhances response times through effective caching mechanisms. We explored Redis's architecture, which efficiently handles cache lookups, cache misses, and cache priming to ensure swift data retrieval and improved system performance. Furthermore, we provided detailed installation steps, essential commands for various Redis data types, and practical examples to illustrate its implementation in Node.js. The practical comparison demonstrated how integrating Redis dramatically reduces data fetching times, highlighting its value in optimizing application performance and user experience. For those interested in hands-on implementation, the provided GitHub repository offers a comprehensive guide to getting started with Redis.

Redux For Beginners

Jemmy Dalsaniya — Sat, 08 Jun 2024 18:58:43 +0000

Basic Overview 📖

Why Redux is needed ??

Ans : In large-scale applications with numerous interdependent components, maintaining the state for each component can become highly complex and cumbersome. Shifting components within such a system necessitates adjustments not only to their individual states but also to the states of all related components. React-Redux simplifies this process significantly. It effectively addresses the issue of "props drilling," where props must be passed through multiple layers of components, thereby streamlining state management and enhancing maintainability.

Define Redux: Redux is a library for managing global application state. Redux is typically used with the React-Redux library for integrating Redux and React together.

Redux Architecture: ⚙️

Action Creation: When a user interacts with the UI and triggers an event, an action is created. An action is a plain JavaScript object that describes what happened and typically contains a type and payload.
Event Dispatcher: This action is dispatched to the Redux Store via the dispatch function. The dispatch function is responsible for sending the action to the Redux Store.
Reducers: The Redux Store then uses reducers to handle the action. Reducers are pure functions that take the current state and the dispatched action as arguments, process the action, and return a new state.
Store Update: The Redux Store updates its state with the new value returned by the reducer. Since reducers are pure functions, they ensure that the state updates are predictable and consistent.
State Subscription: Components that have subscribed to the Redux Store are notified of the state changes. These components can then re-render with the updated state, ensuring the UI is always in sync with the application state.

How Redux solve the problem ??
Ans : Redux solves state management problems in large-scale React applications by centralizing the state in a single store called the Redux Store. This architecture eliminates the issue of prop drilling, where props must be passed through multiple layers of components. Instead, all state is managed in one place, making it easier to maintain and update. When a user interacts with the UI, an action is dispatched to the Redux Store. Reducers, which are pure functions, then process these actions to update the state. Components subscribed to the store are notified of these changes and re-render accordingly, ensuring the UI stays in sync with the application state. This centralized, predictable state management streamlines complex interactions and enhances maintainability.

Commands and Methods:

-> createStore from redux lib : use to create the redux store.

-> Provider from react-redux lib : Act as a provider for the app which use the store as props

-> reducer : it is function that act as a reducer which has two parameter : state and action

-> useDispatch from react-redux lib : act as a event dispatcher for the actions.

-> useSelector from react-redux lib: use to select the value from the redux store.

However this all methods are just for the basic information about how the redux architecture work in actuall. But in practical we use the Redux toolkit which makes our work easy and simpler.
You may find the practical counter example in the given github link in the folder react-basics: link
Run the following command to execute the code :

git clone https://github.com/jemmyasjd/React_Redux
cd react-basics
npm i 
npm run dev

Redux toolkit Methods:

-> configureStore: use to create the redux store using redux toolkit.

-> createSlice : In redux toolkit we make the reducer using the concept of sicer. this method is used to create the slice. Which take name,initital state and reducer as a object parameter. In reducer we make the methods which take state and action as a input to change the state based on the action.

-> createSelector : use to create the selector for easy convinence

-> useDispatch : in redux toolkit we dont need to mention explicitely we just directly pass action from the slice as a parameter.

-> useSelector from react-redux lib: use to select the value from the redux store.

-> action.payload : it contains change what we had done through dispatcher.

Practical Add to Cart Example:

Firstly we create a store using configureStore method, in that we pass the reducer which is also consider as a slicer

We Create the cart reducer using createSlice method

Then we dispatch the event using useDispatch method

Finally we get the data from the store using useSelector

OUTPUT

Get the source code : click here
Run the following command to execute the code :

git clone https://github.com/jemmyasjd/React_Redux
cd React Toolkit Code
npm i 
npm start

Api calling methods:

-> createasyncThunk : used to create the action of fetching the data which take ("name", callback function) which trigger when action dispatch.

-> Addtionally we have

extraReducers: (builder) => {
builder.addCase(fetchTodos.pending, (state, action) => {
  state.isLoading = true;
});
builder.addCase(fetchTodos.fulfilled, (state, action) => {
  state.isLoading = false;
  state.data = action.payload;
});
builder.addCase(fetchTodos.rejected, (state, action) => {
  console.log("Error", action.payload);
  state.isError = true;
});

Practical todo fetching example:

fetch the todos using createThunk method

Get code here

Conclusion

This blog explored how Redux simplifies state management in large-scale React applications by centralizing state in the Redux Store, eliminating prop drilling, and promoting consistency. The architecture of actions, dispatchers, reducers, and subscriptions enhances maintainability. The Redux Toolkit further streamlines workflows by reducing boilerplate. Practical examples, like "Add to Cart" and API handling with createAsyncThunk, showcase Redux's capabilities. These tools enable developers to build scalable and maintainable React applications, making Redux essential in modern web development. For a deeper understanding, the provided GitHub repository offers practical implementations.

DEV Community: Jemmy Dalsaniya

Mastering SQL Join Queries: HR Worker Data Analysis

🧩 Database Schema Overview

Worker: Stores worker details

Department: Contains department information

Worker_rating: Tracks worker performance ratings

🏗️ Creating the Tables

📝 Inserting Sample Data

🔍 Advanced SQL Join Queries for Worker Analysis

🎉 Conclusion

Mastering SQL Basic : (Part : 1)

🧩 Database Schema Overview

Worker: Stores worker details

🏗️ Creating the Table

📝 Inserting Sample Data

🔍 Basic SQL Queries for Worker Analysis

🎉 Conclusion

Mastering SQL Basics: (Part 2)

🧩 Database Schema Overview

tbl_emp: Stores employee details

tbl_department: Contains department information

🏗️ Creating the Tables

📝 Inserting Sample Data

🔍 Advanced SQL Subqueries for Practice

🏁 Conclusion

Git and Github

Life Cycle

Important git command :

Docker All in one 1️⃣

Basic commands:

Attributes :

Examples of docker command:

Commands to install docker on ubuntu :

Demo docker file:

Docker multistage example :

Demo compose.yaml file

OOPS Interview Questions 📝

Table of Content:

OOPS

Class:

Object:

Attributes:

Method or Behaviour:

What is the size of the class when it is empty??

Access Modifiers:

Padding:

Greedy:

this:

Shallow vs Deep Cloning:

Destructor:

const Keyword:

Initialization List:

Static Keyword:

Pillars of OOPS

Encapsulation:

Inheritance:

Polymorphism:

Abstraction:

Abstract CLass:

Pure Virtual Function:

Virtual Function:

Interface:

Friend function:

What are some other programming paradigms other than OOPs?

What is a constructor? & Its type

Structure vs CLass:

What is an exception?

What is meant by exception handling?

What is meant by Garbage Collection in OOPs world?

What are the limitations of OOPs?

What is constructor chaining?

What is Coupling in OOP, and why is it helpful?

Top 10 SQL Queries for Freshers 📝

DBMS Interview Question for Freshers 📝

Questions Index

Difference between Data and Information?

What is metadata & Data Dictionary?

What is Database and DBMS?

What are the 3 levels of ANSI Database?

Define Data Abstraction?

`Worker`: Stores worker details

`Department`: Contains department information

`Worker_rating`: Tracks worker performance ratings

`Worker`: Stores worker details

`tbl_emp`: Stores employee details

`tbl_department`: Contains department information