Difference between WHERE vs HAVING Clause in SQL

Disclosure: This post includes affiliate links; I may receive compensation if you purchase products or services from the different links provided in this article.

Hello friends, SQL questions are quite common on programming interviews and one of the popular SQL question is "WHERE vs HAVING clause"? When it comes to filtering records in SQL query, there are two main options, either by using WHERE clause or by using HAVING clause.

While both WHERE and HAVING are used for filtering rows, condition in WHERE clause is applied before grouping of data and condition on HAVING is applied after grouping

I mean, the main difference between them is that you can use WHERE if you want to filter data before grouping, I mean before you group records using GROUP BY clause and use HAVING when you want to filter groups.

This distinction is made by Query Engine on most of popular database like MySQL, Microsoft SQL Server, Oracle, and PostgreSQL

For example,

SELECT *
FROM BOOK
WHERE author="Joshua Bloch"

will only show books where author is "Joshua Bloch", here we have used WHERE clause because there is no grouping.

In case, we need grouping like authors with number of books we can use group by and having clause together and it will print only authors which have more than one book.

SELECT author, count(*) as NumberOfBooks
FROM BOOK
GROUP BY author
HAVING NumberOfBooks > 1

You can also use WHERE and HAVING clause together in one query and in that case WHERE clause will filter before grouping and HAVING clause will filter after grouping as shown in following example:

SELECT author, count(*) as NumberOfBooks
FROM BOOK
WHERE title like '%SQL%'
GROUP BY author
HAVING NumberOfBooks > 1

This will only print author which have multiple books with title 'SQL in them.

By the way, if you are new to SQL, then you can also use websites like Udemy, Coursera, Educative, ZTM Academy, freeCodeCamp, and VladMihalcea' SQL course to learn SQL in depth.

Difference between WHERE and HAVING clause in SQL?

Now that you know what is WHERE and HAVING clause in SQL and what the do, here are more useful difference between WHERE and HAVING clause in SQL :

1. WHERE clause can be used with SELECT, UPDATE and DELETE statements and clauses but HAVING clause can only be used with SELECT statements.

e.g.

SELECT * FROM Employee WHERE EmployeeId=3

This query will print details of employee with id = 3.

Similarly,

SELECT EmployeeName, COUNT(EmployeeName) AS NumberOfEmployee
FROM Employee
HAVING COUNT(EmployeeName) > 2;

this query will print duplicate employees from table.

2. We can't use aggregate functions in the where clause unless it is in a sub query contained in a HAVING clause whereas we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.

3. WHERE clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).

And, if you need more SQL questions like this then you can also see, Grokking the SQL Interview book which covers key topics for SQL interviews

That's all about difference between WHERE and HAVING clause in SQL. This is one of the important SQL questions and if you are preparing for Java developer interview, you should know the answer of this question.
While its a very common concept and we used it on daily basis, not many people can answer it correctly on interview.

Mentioning about keywords like filtering and before and after grouping is key here.

By the way, this is also a common SQL question on Java interviews and if you are preparing for Java interviews, you can also see my earlier articles like 35 Java Questions, 15 Spring Framework Questions and 6 System Design Problem to prepare other topics.

All the best !!

Top 10 Data Structures and Algorithms for System Design Interviews

Disclosure: This post includes affiliate links; I may receive compensation if you purchase products or services from the different links provided in this article.

Hi there, if you are preparing for a System Design Interview, then one thing you should focus on is learning different System Design Algorithms and what problems they solve in Distributed Systems and Microservices.

In the past, I have shared 6 System Design Problems and 10 Essential System Design topics and in this article, I am going to tell you 10 System Design algorithms and distributed data structures which every developer should learn.

Without any further ado, here are the 10 System Design algorithms and distributed Data Structures you can use to solve large-scale distributed system problems:

1. Consistent Hashing
2. MapReduce
3. Distributed Hash Tables (DHT)
4. Bloom Filters
5. Two-phase commit (2PC)
6. Paxos
7. Raft
8. Gossip protocol
9. Chord:
10. CAP theorem

These algorithms and distributed data structures are just a few examples of the many techniques that can be used to solve large-scale distributed system problems.

By the way, if you are preparing for System design interviews and want to learn System Design in depth then you can also checkout sites like ByteByteGo, Design Guru, Exponent, Educative, Codemia.io, bugfree.ai and Udemy which have many great System design courses, and these popular System design YouTube channels, which have many great System design courses and tutorials.

10 Distributed Data Structure and System Design Algorithms for Programmers

It's important to have a good understanding of these algorithms and how to apply them effectively in different scenarios.

So, let's deep dive into each of them and find out what they are, how they work, and when to use them.

1. Consistent Hashing

Consistent hashing is a technique used in distributed systems to efficiently distribute data among multiple nodes.

It is used to minimize the amount of data that needs to be transferred between nodes when a node is added or removed from the system.

The basic idea behind consistent hashing is to use a hash function to map each piece of data to a node in the system. Each node is assigned a range of hash values, and any data that maps to a hash value within that range is assigned to that node.

When a node is added or removed from the system, only the data that was assigned to that node needs to be transferred to another node. This is achieved by using a concept called virtual nodes.

Instead of assigning each physical node a range of hash values, multiple virtual nodes are assigned to each physical node.

Each virtual node is assigned a unique range of hash values, and any data that maps to a hash value within that range is assigned to the corresponding physical node.

When a node is added or removed from the system, only the virtual nodes that are affected need to be reassigned, and any data that was assigned to those virtual nodes is transferred to another node.

This allows the system to scale dynamically and efficiently, without requiring a full redistribution of data each time a node is added or removed.

Overall, consistent hashing provides a simple and efficient way to distribute data among multiple nodes in a distributed system. It is commonly used in large-scale distributed systems, such as content delivery networks and distributed databases, to provide high availability and scalability.

---

2. Map reduce

MapReduce is a programming model and framework for processing large datasets in a distributed system. It was originally developed by Google and is now widely used in many big data processing systems, such as Apache Hadoop.

The basic idea behind MapReduce is to break a large dataset into smaller chunks, distribute them across multiple nodes in a cluster, and process them in parallel. The processing is divided into two phases: a Map phase and a Reduce phase.

In the Map phase, the input dataset is processed by a set of Map functions in parallel. Each Map function takes a key-value pair as input and produces a set of intermediate key-value pairs as output.

These intermediate key-value pairs are then sorted and partitioned by key, and sent to the Reduce phase.

In the Reduce phase, the intermediate key-value pairs are processed by a set of Reduce functions in parallel. Each Reduce function takes a key and a set of values as input, and produces a set of output key-value pairs.

Here is an example of how MapReduce can be used to count the frequency of words in a large text file:

1. Map phase: Each Map function reads a chunk of the input file and outputs a set of intermediate key-value pairs, where the key is a word and the value is the number of occurrences of that word in the chunk.
2. Shuffle phase: The intermediate key-value pairs are sorted and partitioned by key, so that all the occurrences of each word are grouped together.
3. Reduce phase: Each Reduce function takes a word and a set of occurrences as input, and outputs a key-value pair where the key is the word and the value is the total number of occurrences of that word in the input file.

The MapReduce framework takes care of the parallel processing, distribution, and fault tolerance of the computation. This allows it to process large datasets efficiently and reliably, even on clusters of commodity hardware.

---

3. Distributed Hash Tables (DHT)

A Distributed Hash Table (DHT) is a distributed system that provides a decentralized key-value store. It is used in peer-to-peer (P2P) networks to store and retrieve information in a scalable and fault-tolerant manner.

In a DHT, each participating node stores a subset of the key-value pairs, and a mapping function is used to assign keys to nodes.

This allows nodes to locate the value associated with a given key by querying only a small subset of nodes, typically those responsible for keys close to the given key in the mapping space.

DHTs provide several desirable properties, such as self-organization, fault-tolerance, load-balancing, and efficient routing. They are commonly used in P2P file sharing systems, content distribution networks, and distributed databases.

One popular DHT algorithm is the Chord protocol, which uses a ring-based topology and a consistent hashing function to assign keys to nodes. Another widely used DHT is the Kademlia protocol, which uses a binary tree-like structure to locate nodes responsible for a given key.

---

4. Bloom Filters

Bloom Filters are a probabilistic data structure used for efficient set membership tests. They were introduced by Burton Howard Bloom in 1970.

A Bloom Filter is a space-efficient probabilistic data structure that is used to test whether an element is a member of a set or not. It uses a bit array and a set of hash functions to store and check for the presence of an element in a set.

The process of adding an element to a Bloom Filter involves passing the element through a set of hash functions which returns a set of indices in the bit array. These indices are then set to 1 in the bit array.

To check whether an element is present in the set or not, the same hash functions are applied to the element and the resulting indices are checked in the bit array.

If all the bits at the indices are set to 1, then the element is considered to be present in the set. However, if any of the bits are not set, the element is considered to be absent from the set.

Since Bloom Filters use hash functions to index the bit array, there is a possibility of false positives, i.e., the filter may incorrectly indicate that an element is present in the set when it is not.

However, the probability of a false positive can be controlled by adjusting the size of the bit array and the number of hash functions used.

The false negative rate, i.e., the probability of a Bloom filter failing to identify an element that is actually present in the set, is zero.

Bloom Filters are widely used in various fields such as networking, databases, and web caching to perform efficient set membership tests.

---

5. 2 Phase Commit

Two-phase commit (2PC) is a protocol used to ensure the atomicity and consistency of transactions in distributed systems. It is a way to guarantee that all nodes participating in a transaction either commit or rollback together.

The two-phase commit protocol works in two phases:

1. Prepare Phase: In the prepare phase, the coordinator node sends a message to all participating nodes, asking them to prepare to commit the transaction.

Each participant responds with a message indicating whether it is prepared to commit or not. If any participant cannot prepare, it responds with a message indicating that it cannot participate in the transaction.

1. Commit Phase: If all participants are prepared to commit, the coordinator sends a message to all nodes asking them to commit. Each participant commits the transaction and sends an acknowledgement to the coordinator.

If any participant cannot commit, it rolls back the transaction and sends a message to the coordinator indicating that it has rolled back.

If the coordinator receives acknowledgements from all participants, it sends a message to all nodes indicating that the transaction has been committed.

If the coordinator receives a rollback message from any participant, it sends a message to all nodes indicating that the transaction has been rolled back.

The two-phase commit protocol ensures that all nodes in a distributed system agree on the outcome of a transaction, even in the presence of failures.

However, it has some drawbacks, including increased latency and the possibility of deadlock. Additionally, it requires a coordinator node, which can be a single point of failure.

---

6. Paxos

Paxos is a distributed consensus algorithm that allows a group of nodes to agree on a common value, even in the presence of failures. It was introduced by Leslie Lamport in 1998 and has become a fundamental algorithm for distributed systems.

The Paxos algorithm is designed to handle a variety of failure scenarios, including message loss, duplication, reordering, and node failures.

The algorithm proceeds in two phases: the prepare phase and the accept phase. In the prepare phase, a node sends a prepare message to all other nodes, asking them to promise not to accept any proposal with a number less than a certain value.

Once a majority of nodes have responded with promises, the node can proceed to the accept phase. In the accept phase, the node sends an accept message to all other nodes, proposing a certain value.

If a majority of nodes respond with an acceptance message, the value is considered accepted.

Paxos is a complex algorithm, and there are several variations and optimizations of it, such as Multi-Paxos, Fast Paxos, and others.

These variations aim to reduce the number of messages exchanged, optimize the latency of the algorithm, and reduce the number of nodes that need to participate in the consensus. Paxos is widely used in distributed databases, file systems, and other distributed systems where a high degree of fault tolerance is required.

---

7. Raft

Raft is a consensus algorithm designed to ensure fault-tolerance in distributed systems. It is used to maintain a replicated log that stores a sequence of state changes across multiple nodes in a cluster.

Raft achieves consensus by electing a leader, which coordinates the communication among the nodes and ensures that the log is consistent across the cluster.

The Raft algorithm consists of three main components: leader election, log replication, and safety. In the leader election phase, nodes in the cluster elect a leader using a randomized timeout mechanism.

The leader then coordinates the log replication by receiving state changes from clients and replicating them across the nodes in the cluster. Nodes can also request entries from the leader to ensure consistency across the cluster.

The safety component of Raft ensures that the algorithm is resilient to failures and ensures that the log is consistent across the cluster.

Raft achieves safety by ensuring that only one node can be the leader at any given time and by enforcing a strict ordering of log entries across the cluster.

Raft is widely used in distributed systems to provide fault-tolerance and high availability. It is often used in systems that require strong consistency guarantees, such as distributed databases and key-value stores.

---

8. Gossip

The gossip protocol is a peer-to-peer communication protocol used in distributed systems to disseminate information quickly and efficiently.

It is a probabilistic protocol that allows nodes to exchange information about their state with their neighbors in a decentralized manner.

The protocol gets its name from the way it spreads information like a rumor or gossip.

In a gossip protocol, nodes randomly select a set of other nodes to exchange information with. When a node receives information from another node, it then forwards that information to a subset of its neighbors, and the process continues.

Over time, the entire network becomes aware of the information as it spreads from node to node.

One of the key benefits of the gossip protocol is its fault-tolerance. Since the protocol relies on probabilistic communication rather than a central authority, it can continue to function even if some nodes fail or drop out of the network.

This makes it a useful tool in distributed systems where reliability is a critical concern.

Gossip protocols have been used in a variety of applications, including distributed databases, peer-to-peer file sharing networks, and large-scale sensor networks.

They are particularly well-suited to applications that require fast and efficient dissemination of information across a large number of nodes.

---

9. Chrod

Chord is a distributed hash table (DHT) protocol used for decentralized peer-to-peer (P2P) systems. It provides an efficient way to locate a node (or a set of nodes) in a P2P network given its identifier.

Chord allows P2P systems to scale to very large numbers of nodes while maintaining low overhead.

In a Chord network, each node is assigned an identifier, which can be any m-bit number. The nodes are arranged in a ring, where the nodes are ordered based on their identifiers in a clockwise direction.

Each node is responsible for a set of keys, which can be any value in the range of 0 to 2^m-1.

To find a key in the network, a node first calculates its hash value and then contacts the node whose identifier is the first clockwise successor of that hash value.

If the successor node does not have the desired key, it forwards the request to its successor, and so on, until the key is found. This process is known as a finger lookup, and it typically requires a logarithmic number of messages to find the desired node.

To maintain the consistency of the network, Chord uses a protocol called finger tables, which store information about other nodes in the network.

Each node maintains a finger table that contains the identifiers of its successors at increasing distances in the ring. This allows nodes to efficiently locate other nodes in the network without having to maintain a complete list of all nodes.

Chord also provides mechanisms for maintaining consistency when nodes join or leave the network. When a node joins the network, it notifies its immediate successor, which updates its finger table accordingly.

When a node leaves the network, its keys are transferred to its successor node, and the successor node updates its finger table to reflect the departure.

Overall, Chord provides an efficient and scalable way to locate nodes in a P2P network using a simple and decentralized protocol.

---

10. CAP Theorem

The CAP theorem, also known as Brewer's theorem, is a fundamental concept in distributed systems that states that it is impossible for a distributed system to simultaneously guarantee all of the following three properties:

1. Consistency: Every read receives the most recent write or an error.
2. Availability: Every request receives a response, without guarantee that it contains the most recent version of the information.
3. Partition tolerance: The system continues to function and provide consistent and available services even when network partitions occur.

In other words, a distributed system can only provide two out of the three properties mentioned above.

This theorem implies that in the event of a network partition, a distributed system must choose between consistency and availability.

For example, in a partitioned system, if one node cannot communicate with another node, it must either return an error or provide a potentially stale response.

The CAP theorem has significant implications for designing distributed systems, as it requires developers to make trade-offs between consistency, availability, and partition tolerance.

Conclusion

That's all about the essential System Design Data Structure, Algorithms and Protocol You can learn in 2023. In conclusion, system design is an essential skill for software engineers, especially those working on large-scale distributed systems.

These ten algorithms, data structure, and protocols provide a solid foundation for tackling complex problems and building scalable, reliable systems. By understanding these algorithms and their trade-offs, you can make informed decisions when designing and implementing systems.

Additionally, learning these algorithms can help you prepare for system design interviews and improve their problem-solving skills. However, it's important to note that these algorithms are just a starting point, and you should continue to learn and adapt as technology evolves.

By the way, if you are preparing for System design interviews and want to learn System Design in depth then you can also checkout sites like ByteByteGo, Design Guru, Exponent, Educative, Codemia.io, bugfree.ai and Udemy which have many great System design courses, and these popular System design YouTube channels, which have many great System design courses and tutorials.

Also, here is a nice System design template from DesignGuru which you can use to answer any System design question on interviews. It highlights key software architecture components and allows you to express your knowledge well.

All the best for your System design interviews!!

50+ Core Java Interview Questions for 1 to 3 Years Experienced Developers

Disclosure: This post includes affiliate links; I may receive compensation if you purchase products or services from the different links provided in this article.

Hello devs, are you preparing for Java developer interviews? If Yes, here is a list of some useful Java interview questions for experienced Java programmers having experience in range of 2 to 5 years.

As an experienced developer you are expected to learn about OOP concepts, Java basics, Java Collection framework, Multi-threading and Concurrency utilities introduced in Java 5 and 6, Debugging Java application, Algorithm and Data structure, Some questions on design patterns, JVM and Garbage collection and couple of puzzles.

Actually its mix of everything you do in your day to day work.

If you are going for Java developer with some exposure on web development you will also be asked about popular Java frameworks like Spring, Hibernate, Struts 2.0 and others.

If you have more than 5 years of experience you can also expect questions about build tools like Maven, ANT and Gradle, Java best practices, Unit testing and JUnit and your experience about solving production issues.

One of the most common question I have faced is talking about the last production problem you have faced and how did you solved it.

If you are asked same question, give them step by step detail, right from analyzing problem to tactical fix to strategic solution.

In this article, I am going to share my list of Java Interview question for Java guys having 2 to 5 years of experience. Since I had similar experience couple of year ago, I know what questions are asked and keeping a list for your own always helps when you start looking for new challenge in your career.

I am not providing answers of these question in this post due to two reasons, questions are quite simple and you guys probably know the answer, second providing answer means I cannot use this post for my own preparation later, which is more important.

Though, I could write another article answering all these question if anyone request or I feel people need it.

By the way, if you are new to Java programming language or want to improve Java skills then you can also checkout sites like CodeGym, ZTM and karpado to learn Java by building Games and projects.

Java Interview Questions for 1 to 2 years Experienced

This list contains questions from different topics e.g. OOP concepts, multi-threading and concurrency, Java collections, Web services, Spring, Hibernate, Database and JDBC, it doesn't cover all topics you need to prepare.

I will add few more topics later when I have some time, for now, try to answer these questions without doing Google :)

Java Interview questions on OOP Concepts

Here are a couple of questions on OOP design, SOLID principle and baseic programming concepts

1. What is the difference between loose coupling and tight coupling?
Loose coupling allows components to interact with each other with minimal dependencies, while tight coupling creates strong dependencies between components.

2. What is the difference between cohesion and coupling?
Cohesion refers to the degree to which elements within a module belong together, while coupling refers to the degree of interdependence between modules.

3. What is Liskov Substitution principle? Can you explain with an example?
Liskov Substitution principle states that objects of a superclass should be replaceable with objects of its subclasses without affecting the correctness of the program.

For example, if you have a class hierarchy with a superclass "Shape" and subclasses "Circle" and "Square", any method that works with Shape should also work with Circle or Square without causing errors.

4. What is the difference between abstract class and interface in Java?
Abstract classes can have both abstract and concrete methods, while interfaces can only have abstract methods. Additionally, a class can implement multiple interfaces but can only extend one abstract class.

5. What is the difference between composition, aggregation, and association?
Composition implies a strong ownership relationship where the lifetime of the contained object is dependent on the container.

Aggregation implies a weaker relationship where the contained object can exist independently of the container. Association implies a relationship between two classes without any ownership or lifecycle dependency.

---

Java Interview questions on Collections

Now, let's see a few questions form Collections and Stream

1. Difference between List, Set, and Map in Java?
Lists maintain elements in sequential order and allow duplicates (e.g., ArrayList, LinkedList). Sets do not allow duplicates and do not guarantee order (e.g., HashSet, TreeSet). Maps store key-value pairs and do not allow duplicate keys (e.g., HashMap, TreeMap).

2. Difference between synchronized and concurrent collection in Java?
Synchronized collections use explicit locking to achieve thread-safety, allowing only one thread to modify the collection at a time. Concurrent collections use non-blocking algorithms and are designed for high concurrency, allowing multiple threads to modify the collection concurrently without explicit locking.

3. How does the get method of HashMap work in Java?
The get method of HashMap calculates the hash code of the provided key, determines the index in the underlying array based on the hash code, and then searches for the key at that index. If found, it returns the corresponding value; otherwise, it returns null.

4. How is ConcurrentHashMap different from Hashtable? How does it achieve thread-safety?
ConcurrentHashMap allows concurrent access to the map without blocking, while Hashtable uses synchronized methods to achieve thread-safety, resulting in potential performance bottlenecks. ConcurrentHashMap achieves thread-safety by dividing the map into segments, each with its lock, allowing multiple threads to modify different segments concurrently.

5. When to use LinkedList over ArrayList in Java?
Use LinkedList when frequent insertion and deletion operations are required, as LinkedList provides constant-time insertion and deletion at any position. Use ArrayList when random access and iteration are frequent, as ArrayList provides constant-time access by index.

---

Java Interview questions on Concurrency and Threads

Now, its time to see questions from Java multithreading and concurrency concepts:

1. How do notify and notifyAll work, and what's the difference between them? Why prefer notifyAll to notify?
Both notify and notifyAll are methods in Java used to wake up threads waiting on a monitor (i.e., waiting to acquire an object's lock). notify wakes up one randomly selected thread, while notifyAll wakes up all waiting threads. notifyAll is preferred because it ensures that all waiting threads are notified, preventing potential indefinite waiting and improving system responsiveness.

2. What is a race condition and how do you avoid it?
A race condition occurs when the outcome of a program depends on the timing or interleaving of multiple threads. To avoid race conditions, you can use synchronization mechanisms like locks, semaphores, or atomic operations to ensure that critical sections of code are executed atomically or only by one thread at a time.

3. What is a deadlock and how do you avoid it?
Deadlock occurs when two or more threads are stuck waiting for each other to release resources that they need to proceed. To avoid deadlock, you can use techniques such as resource ordering, avoiding nested locks, or using timeouts for acquiring locks. Additionally, designing code with a clear and consistent locking order can help prevent deadlocks.

4. What are some of the high-level concurrency classes provided by java.util.concurrent and how do they work?
Some high-level concurrency classes provided by java.util.concurrent include ExecutorService, ThreadPoolExecutor, CountDownLatch, Semaphore, CyclicBarrier, BlockingQueue, and ConcurrentHashMap. These classes provide thread-safe implementations of common concurrency patterns and mechanisms like thread pools, synchronization primitives, and concurrent data structures.

5. Can you implement a producer-consumer solution in Java?
Yes, here is the code:

import java.util.concurrent.ArrayBlockingQueue;

class Producer implements Runnable {
    private final ArrayBlockingQueue<Integer> queue;
    private int count = 0;

    Producer(ArrayBlockingQueue<Integer> queue) {
        this.queue = queue;
    }

    public void run() {
        try {
            while (true) {
                queue.put(produce());
                Thread.sleep(1000); // Simulate some work
            }
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }

    private int produce() {
        System.out.println("Producing: " + count);
        return count++;
    }
}

class Consumer implements Runnable {
    private final ArrayBlockingQueue<Integer> queue;

    Consumer(ArrayBlockingQueue<Integer> queue) {
        this.queue = queue;
    }

    public void run() {
        try {
            while (true) {
                consume(queue.take());
            }
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }

    private void consume(int item) {
        System.out.println("Consuming: " + item);
    }
}

public class Main {
    public static void main(String[] args) {
        ArrayBlockingQueue<Integer> queue = new ArrayBlockingQueue<>(10);
        Producer producer = new Producer(queue);
        Consumer consumer = new Consumer(queue);

        Thread producerThread = new Thread(producer);
        Thread consumerThread = new Thread(consumer);

        producerThread.start();
        consumerThread.start();
    }
}

---

Java Interview questions on Database, SQL, and JDBC

JDBC is used for connecting database from Java program, let's ee a few questions on Database and JDBC
1. How do you prevent SQL injection attacks?
To prevent SQL injection attacks, use parameterized queries (prepared statements) with bound parameters, input validation, and escape characters. Avoid dynamic SQL queries constructed by concatenating user input.

2. What is the difference between WHERE and HAVING clause? The WHERE clause filters rows before the grouping and aggregation process, while the HAVING clause filters aggregated data after the grouping process based on specified conditions.

3. What are transactions? What is ACID?
Transactions are a set of SQL statements that are executed as a single unit of work. ACID is an acronym for Atomicity, Consistency, Isolation, and Durability, which are properties that ensure the reliability of transactions in a database system.

4. Difference between truncate, delete, and drop clause in SQL?

• TRUNCATE: Removes all rows from a table but retains the table structure and any associated constraints or indexes.
• DELETE: Removes specific rows from a table based on a condition, but retains the table structure and associated constraints.
• DROP: Deletes an entire table, including its structure, data, and associated constraints and indexes.

5. What are window functions? How do they work?
Window functions perform calculations across a set of rows related to the current row within a query result set. They allow you to perform aggregate functions (such as SUM, AVG, COUNT) over a specified window or subset of rows, defined by the OVER clause. Window functions operate on a set of rows and return a single value for each row based on that set of rows. They are often used for tasks such as ranking, aggregation, and calculating running totals.

See, Grokking the SQL Interview book if you need more questions on Database and SQL

Java Interview questions on Hibernate

Now, its time to see questions from Hibernate, one of the popular Java framework:

1. When is it better to use plain SQL instead of ORM?
It's better to use plain SQL when:

• Complex queries need to be optimized for performance.
• The database schema or query requirements are not well-supported by the ORM framework.
• Direct control over SQL statements, database connections, or transactions is required.

2. Difference between sorted and ordered collection?
In Java, a sorted collection maintains elements in a specific order defined by a comparator or by the natural ordering of elements, while an ordered collection maintains elements in the order they were inserted.

3. How does second level cache work?
Second level cache in Hibernate stores objects in a shared cache region, typically across multiple sessions. When an entity is queried for the first time, it is fetched from the database and stored in the second level cache. Subsequent queries for the same entity can then be satisfied from the cache instead of hitting the database, improving performance.

4. What is the difference between save() and persist() in Hibernate?
Both save() and persist() methods in Hibernate are used to save an entity to the database. However, save() returns the generated identifier immediately, while persist() doesn't guarantee immediate execution of the SQL INSERT statement; it may be executed later during flush time. Additionally, persist() is part of the JPA specification, while save() is specific to Hibernate.

5. What is the difference between Hibernate and MyBatis?

• Hibernate is a full-fledged ORM framework that maps Java objects to database tables, manages database connections, and provides various querying mechanisms. MyBatis, on the other hand, is a lightweight persistence framework that uses SQL mapping files to map Java objects to SQL queries.
• Hibernate is typically used for domain-driven development, where object-oriented modeling is prominent, while MyBatis is often preferred for projects where direct control over SQL queries is required, such as legacy database systems or complex SQL scenarios.
• Hibernate provides caching mechanisms, automatic dirty checking, and transaction management, while MyBatis offers more control over SQL queries and mappings, allowing developers to write SQL queries directly.

---

Java Interview questions on Web Services and Microservices

Now, let's see questions form Microservice architecture and REST web services

1. Difference between SOAP-based and REST-based web services? SOAP is protocol-based with rigid structure, while REST is architectural style based on stateless communication with flexible endpoints.

2. What is SOAP Envelope?
It encapsulates the entire SOAP message and defines its structure.

3. How to implement security in RESTful web service?
Implement SSL/TLS for encryption and authentication.

4. What is Payload in REST?
It's the data transmitted in the body of the HTTP request or response.

5. What is Microservices? It's an architectural style where applications are composed of small, independent services.

6. What is the difference between Microservices and REST? Microservices refer to architectural design, while REST is an architectural style for networked applications.

7. What is the difference between Monolithic and Microservices?
Monolithic has single codebase, while Microservices have multiple, independent components; Monolithic can have higher latency.

8. What problem does SAGA pattern solve?
It manages distributed transactions in Microservices architecture.

9. What is service discovery in Microservices?
It's the mechanism for locating services dynamically within a Microservices architecture.

10. What are common Microservices Patterns you have used in your project?
Service Registry, Circuit Breaker, API Gateway.

---

Java and Spring Interview Preparation Material

Before any Java and Spring Developer interview, I always read the Grokking the Java Interview and Grokking the Spring boot Interviw

Here are few more questions from these books:

and,

And, if you are new to Java then you can also checkout sites like CodeGym, ZTM and karpado to learn Java by building Games and projects.

 
Thank you guys for now. You can find the answers in web easily but if there are enough interest, I can also update the post. Let me know if you have also asked these questions before. If anyone knows answer, can also post as comment.

Good luck for your Java Interview.

By the way, if you are new to Java programming language or want to improve Java skills then you can also checkout following best Java courses to get better:

Microservices vs Monolithic Architecture for System Design Interview

image_credit - DesignGuru

Hello friends, if you are preparing for a System design interview, then you must have come across questions on Microservices architecture.

In the last few articles, I have answered popular System design questions like API Gateway vs Load Balancer and Horizontal vs Vertical Scaling, Forward proxy vs reverse proxy, and today, I will answer another interesting System design question, *"difference between monolith and Micro service architecture?".

With the growing popularity of Microservices, I am seeing more and more questions from Microservices on System Design Interviews, and this is one of the starter questions.

In system design interviews, understanding the difference between microservices and monolithic applications is crucial. While monolithic architectures offer simplicity and ease of development, microservices provide scalability, flexibility, and resilience through their distributed nature and modular design.

For example, in the case of monolith architecture, your entire application is packaged and deployed together, while in the case of Microservices architecture, an application is broken into a collection of small, independent services that communicate with each other over a network, mostly over HTTP.**

Each service is responsible for a specific business capability and can be developed, deployed, and scaled independently. This makes it easier to make changes to the application without affecting other parts of the system.

Microservices also enable applications to be developed and deployed faster, and they are better suited to large and complex applications where different parts of the application may need to evolve at different speeds.

By the way, Microservices are not a silver bullet, there are debugging and troubleshooting issues with Microservices because application log files are scattered across multiple services.

Also, for a latency-sensitive application, Microservices is not a good choice because it increases latency.

Now that we are familiar with the basic idea of Microservices and Monolithic architecture, it's time to dive deep and see the pros and cons of both software architectures.

By the way, if you are in a hurry, then the diagram from DesignGurus.io, one of the best resources for system design interviews and creator of Grokking the System Design Interview nicely explains it; he even added a comparison to serverless architecture:

And, if you are preparing for a System design interview, along with Design Guru, Educative, ByteByteGo, and Exponent are great resources to further improve your preparation.

---

Difference between Monolithic vs Microservices Architecture

Now that you have a basic idea of what Microservices offer in terms of Monolithic applications, it makes sense to deep dive and find out more technical differences between these two architecture style to build software applications.

Here are the key differences, advantages, and disadvantages of Monolithic and Microservices architecture:

1. Deployment and Management

Monolithic applications are simple to deploy and manage, since all components are included in a single package, but Microservices are complex to deploy and manage, since each service is deployed independently and must communicate with other services over a network.

Microservice architecture also have increased operational overhead, as each service must be deployed, monitored, and managed individually.

2. Easy to Understand

In the case of Monolithic architecture its easy to understand the entire system, since all components are integrated tightly, while its difficult to understand the flow in Microservices because of multiple services.

---

3. Debugging

Monolithic applications are easier to debug as compared to Microservices because the entire application runs in a single process, while Debugging can be more difficult in Microservice architecture, since issues can span multiple services.

For example, if data is updated in one service it can have origin in some other service like authentication or authorization

---

4. Development

Microservices promote flexible development and are better suited to large and complex applications where different parts of the application may need to evolve at different speeds.

While Microservices are better suited for small, latency sensitive application. In short, Microservices enable faster development and deployment, since services can be developed and deployed independently.

---

5. Coupling

In the case of Monolithic architecture, components are tightly coupled, which makes it difficult to make changes to the application without causing unintended consequences, while microservices promote low coupling.

It's also easier to make changes to the application, since each service is responsible for a specific business capability.

---

6. Maintainence

Monolithic applications are easier to start but difficult to maintain. As the application grows, the code base becomes larger and more complex, making it harder to maintain.

On the other hand, Microservices are easier to maintain as you can make changes in one service without deploying other services.

---

7. Performance and Scalability

Microservice architecture allows for better scalability and performance improvement, since each service can be scaled independently, while performance bottlenecks can easily happen in a monolithic application, since all components share the same resources.

In short, while monolithic architectures offer simplicity and ease of development, microservices provide scalability, flexibility, and resilience through their distributed nature and modular design. So both have their places.

And, here is also a nice diagram to highlight the difference between API Gateway and Load Balancer from ByteByteGo, one of the best places to prepare for System design interviews

---

That's all about difference between Microservices and monolithic architecture and applications. As I said, monolithic architecture is simpler and easier to deploy and manage, but is less flexible and harder to change. Microservices architecture is more flexible and easier to change, but is more complex and harder to deploy and manage.

While Microservices is latest trend in Software development, and a well-designed microservices architecture can provide benefits such as scalability and faster development, especially on the cloud, it requires a more complex deployment and management infrastructure.

On the other hand, a well-designed monolithic architecture can provide benefits such as simpler deployment and easier debugging, but can become more difficult to change as the application grows.

This is a really useful concept for System design interviews, especially Microservice architecture, and you shouldn't miss out on that. For better preparation, I also suggest checking out sites like DesignGurus, Educative, ByteByteGo, and Exponent, all of which are great resources for tech interview preparation, particularly System design. 

Forward Proxy vs Reverse Proxy in System design

Disclosure: This post includes affiliate links; I may receive compensation if you purchase products or services from the different links provided in this article.

image_credit - DesignGurus.io

Hello folks, in last few article, I was answering popular System design questions like API Gateway vs Load Balancer and Horizontal vs Vertical Scaling, and today, we are going to take a look at another interesting System design question, Reverse Proxy vs Forward Proxy.

These questions are different than system design problems like how to design WhatsApp and YouTube but they are equally important and if you have knowledge of them you can mention in most of the system design problems.

Now coming back to the topic, In network architecture world, proxies play a pivotal role in managing and securing communication between clients and servers.

There are two common types of proxies, forward and reverse proxies, they serve distinct purposes and operate at different layers of the networking stack. Forward proxies are used to shield clients from external networks while Reverse proxy acts as a frontend Facade for backend Servers, much like API Gatewawy and load balancers.

Let's go deep into the intricacies of forward and reverse proxies to know their differences and understand their respective roles in system design.

By the way, if you are in hurry then below diagram from DesignGuru.io, one of the best resource for system design interviews and creator of Grokking the System Design Interview nicely explain it:

---

What is Forward Proxy?

A forward proxy, also known as an outbound proxy, acts as an intermediary between clients and external servers, intercepting outbound requests from clients and forwarding them to their intended destinations.

Here is what forward proxies do for you:

1. Client-Side Proxying
   Forward proxies are typically deployed on the client side of a network, serving as a gateway for outbound traffic. Clients configure their network settings to route traffic through the forward proxy, which then forwards requests to external servers on behalf of the clients.

2. Anonymity and Privacy
   Forward proxies can enhance user privacy and anonymity by masking the IP addresses of clients. External servers only see the IP address of the forward proxy, making it difficult to trace the origin of requests back to individual clients.

3. Content Filtering and Caching
   Forward proxies can implement content filtering policies to restrict access to certain websites or content categories based on predefined rules. Additionally, they can cache frequently accessed content, reducing bandwidth usage and improving performance for subsequent requests.

4. Security and Access Control
   Forward proxies can also enforce security policies and access controls, allowing organizations to regulate access to external resources, block malicious websites, and inspect outbound traffic for threats or policy violations.

You can see in the diagram below that the forward proxy routes user requests to back-end servers

By the way, if you are preparing for System design interviews and want to learn System Design in depth then you can also checkout sites like ByteByteGo, Design Guru, Exponent, Educative, Codemia.io, bugfree.ai and Udemy which have many great System design courses

Now that we know what a forward proxy is let's take a look at a reverse proxy and what services it provides:

---

What is a Reverse Proxy?

A reverse proxy, also known as an inbound proxy, operates on the server side of a network, serving as a front-end facade for backend servers.

It intercepts incoming requests from clients and forwards them to the appropriate back-end servers based on predefined rules.

Key aspects of reverse proxies include:

1. Server-Side Proxying
   Reverse proxies are deployed on the server side of a network, typically in front of backend web servers or application servers. They accept incoming requests from clients on behalf of backend servers and forward them internally.

2. Load Balancing and Traffic Distribution
   Reverse proxies can distribute incoming traffic across multiple backend servers to improve scalability, reliability, and performance. They use algorithms such as round-robin, least connections, or weighted distribution to evenly distribute requests.

3. SSL Termination and Encryption
   Reverse proxies can handle SSL/TLS termination, offloading the encryption and decryption process from backend servers. This simplifies management of SSL certificates and improves performance by reducing the computational overhead on backend servers.

4. Content Delivery and Optimization
   Reverse proxies can cache static content, compress data, and optimize delivery to clients, reducing latency and bandwidth usage. They can also perform content rewriting or transformation to adapt content for different client devices or browsers.

Here is also a nice diagram which shows how reverse proxy work which is quite useful for system design interview, and if you are preparing for one, Educative.io's Modern System Design Guide is another awesome resource I recommend.

---

Difference between Forward and Reverse Proxies and Use Cases

While both forward and reverse proxies act as intermediaries in network communication, their primary objectives and deployment scenarios differ:

For example, Forward proxy is primarily used to shield clients from external networks, enhance privacy and security, and enforce access controls and it's ideal for individual users, organizations, or networks requiring outbound traffic management and anonymity.

On the other hand, Reverse Proxy is primarily used to front-end backend servers, improve scalability and performance, and provide centralized management of incoming traffic.

It is ideal for web servers, application servers, or microservices architectures requiring load balancing, SSL termination, and content optimization.

And, here is a nice diagram which highlights the difference between Forward Proxy and Reverse Proxy from ByteByteGo, one of the best places to learn System Design for interviews. If you are preparing for a system design interview, you should definitely check it out. They also have an awesome YouTube channel.

Conclusion

In conclusion, both forward and reverse proxies are indispensable components in modern network architectures, each serving unique purposes and offering distinct capabilities.

While forward proxies focus on client-side traffic management and security, reverse proxies excel at server-side load balancing, scalability, and optimization.

Understanding their differences is essential for designing resilient, efficient, and secure systems that meet the diverse needs of modern applications and services.

And, if you are preparing for a system design interview, then you may also like my previous articles

By the way, if you are preparing for System design interviews and want to learn System Design in depth then you can also checkout sites like ByteByteGo, Design Guru, Exponent, Educative, Codemia.io, bugfree.ai and Udemy which have many great System design courses

Thank you !!