SQL Interview Questions – Part-2

What is Savepoint in SQL?
By designating a point inside a transaction that may be “rolled back to” without impacting any work done in the transaction prior to the savepoint being formed, a savepoint is a method of implementing subtransactions within a relational database management system. A single transaction may contain many savepoints.

In other words, in a relational database management system, a savepoint identifies a point in a transaction that may be “rolled back to” without impacting any work done in the transaction up until the savepoint was created. This is how subtransactions are implemented. There may be several savepoints in a single transaction.

What are commit and rollback?
A user may save any modifications to the current transaction with the COMMIT command. The modifications are thus irreversible.

Users can reverse all modifications and changes made to the current transaction since the last COMMIT by using the ROLLBACK statement.

What are the clauses in SQL?

• FROM clause.
• GROUP BY clause.
• HAVING clause.
• ORDER BY clause.
• OFFSET and FETCH clauses.
• USING clause.

Which wildcards can be used with the logical operator LIKE?

• % Any string of zero or more characters.
  WHERE title LIKE ‘%computer%’ finds all book titles with the word ‘computer’ anywhere in the book title.
• _ (underscore) Any single character.
  WHERE firstName LIKE ‘_ony’ finds all four-letter “first names” that end with only (Mony, Sony, Tony, and so on).
• [ ] Any single character within the specified range ([a-f]) or set ([abcdef]).
  WHERE firstName LIKE ‘[C-P]ony’ finds “first names” ending with “ony” and starting with any single character between C and P, for example, Pony, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
• [^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).
  WHERE firstName LIKE ‘To[^ny]%’ finds all “first names” starting with “To” and where the following letter isn’t “ny”.

What is the difference between physical and logical computed columns?
Logical computed column: A computed column is basically a virtual column that is not physically stored in the table.
Physical computed column: A computed column that is marked as PERSISTED will have its data stored physically.

Why is a naming convention required in SQL?
for READABILITY.

the primary reason for using a naming convention for database objects is to help everyone identify the type and purpose of all objects in the database. It reduces the amount of effort required to read and understand source code after a long period of time. Most of the interfaces sort the database objects in alphabetical order hence the naming convention helps keep the objects that are similar remain in sequence and reduces the time it takes to find a specific object in a database.

What is a sub-query in SQL?
A subquery is a SQL query that runs within another query. They are nested inquiries that offer data to the query that comes before it. Individual values or a list of records can be returned via subqueries. In SQL, a subquery is a select expression surrounded by parentheses and used as a nested query block in a query statement.

What is a correlated sub-query?
A correlated subquery is one that makes use of values from the outer query. Because of this dependency, a correlated subquery cannot be executed as a simple subquery on its own. It is evaluated once for each row that the outer query processes.

What’s the maximum size of a row? What’s the maximum number of columns per table? What’s the number of columns per SELECT statement?
8060 bytes.
1024 columns per table.
4096 columns per SELECT statement.

What is bit datatype and what’s the information that can be stored inside a bit column?
Bit datatype is used to store boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

Define candidate key, alternate key, and composite key.
A candidate key is one that can identify each row of a table uniquely. Generally, a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called a composite key.

What are defaults? Is there a column to which a default can’t be bound?
A default is a value that will be used by a column if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them.

Click here for Part-1
Click here for Part-3