Few SQL Interview Question Answers

Replication :

Replication is the process of copying and distributing data and database objects from one database to another and then synchronizing between databases for consistency.

Transactional Replication
Merge Replication
Snapshot Replication

Snowflake Schema :

A snowflake schema is a logical arrangement of tables in a multidimensional database

Correlated SubQuery :

A correlated subquery is a subquery where the inner query is evaluated once for every value returned by the outer query.

SELECT O.OrderID, O.OrderDate,
(SELECT MAX (Od.UnitPrice) FROM [Order Details] AS Od
WHERE Od.OrderID = O.orderid) AS MaxUnitPrice
FROM Orders AS O

Having and Where :

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows.

Difference Between Union vs. Union All

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records
returned are unique from your union, use UNION ALL instead, it gives faster results.

SQL - Wildcard Operators :

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.


How do you sort in SQL?

The ORDER BY keyword is used to sort the result-set by a specified column.

Explain the difference between optimistic and pessimistic locking.

In optimistic locking the row will not be locked until the actual update is performed. In order to prevent the lost update issue a version number column will be used.

In pessimistic locking the row will be locked at the time when the data is selected from the database. This will prevent other users who want to update the same record. This should not prevent the readers but this behaviour varies from database to database.