Common SQL Theory Interview question

Whenever we prepare for interview for full Stack developer. There is essential need of to know the common SQL theory interview question. Reason behind that every interviewer known as some popular topic in popular programming language. Every programming language have highlighted topic. From that highlights topic every interview ask the common question to every candidate.

What are difference between primary key and unique key?

In database design, primary keys and unique keys are both used to enforce uniqueness of data in a table. However, they have some important differences.

Primary Key –

Ensures that each value in the primary key column is unique across the table. This is essential for identifying each record uniquely throught the table.

Unique Key –

Its Also ensures that each value in the unique key column is unique, but it does not necessarily have to identify each record in the table.

Primary Key: Cannot contain NULL values. Each row must have a value for the primary key column.

Unique Key: Can contain a single NULL value (depending on the database system), which makes it possible to have a row without a unique value in that column.

Primary Key: A table can only have one primary key.
Unique Key: A table can have multiple unique keys.

Example:

Suppose we have a Users table:

Primary Key: Could be UserID, which is unique for each user and cannot be NULL.

Unique Key: Could be Email, which must be unique but can be NULL if some users haven't provided an email.

What are different ways to replace NULL vales?

In databases, replacing NULL values is a common task, especially when dealing with incomplete data. Here are some different ways to replace NULL values.

Using the COALESCE() Function
The COALESCE function returns the first non-NULL value in a list of values.

Ex. 
SELECT COALESCE(column_name, 'Default Value') FROM table_name;
Ans- If column_name is NULL, it will return 'Default Value'.

Using the ISNULL() Function (SQL Server)
 The ISNULL function checks if a value is NULL and replaces it with a specified value.

Ex.
SELECT ISNULL(column_name, 'Default Value') FROM table_name;
Ans - Similar to COALESCE, but it only accepts two arguments: the column and the replacement value.

Using CASE WHEN (SQL)
The CASE WHEN statement can replace NULL values by checking the condition and providing an alternative.

Ex.
SELECT CASE WHEN column_name IS NULL THEN 'Default Value' ELSE column_name END AS column_name FROM table_name;
Ans-This method is more customizable if you need additional logic when replacing NULL values.

Using UPDATE Statement for Permanent Replacement
If you want to permanently update a column with a non-NULL value

Ex.
UPDATE table_name
SET column_name = 'Default Value'
WHERE column_name IS NULL;

Using REPLACE() Function- This is very rarely used function.