Below are some of the important sql testing interview questions.
1. Explain how a “where” clause differs from a “having” clause?
The “where” is used as a command for restricting data from a database but a “having” clause is used as a command for filtering. For example, a “where” clause may be used to retrieve results and a “have” clause may be used to filter the results of the retrieved data.
2. How can you speed up table reads for a database?
In order to tune the database one should do the following:
a) Check for proper index use
b) Check for correct location of database objects across multiple files, table spaces, etc.
c) Create a particular area with a special data type (such as a table space) to place some of the data
3. Explain database replication.
Database replication is the practice of moving or copying data from one database to another. There are three types of replication supported by SQL Server: merge, snapshot, and transactional.
4. Name a few trade-offs for using indexes?
- Allows faster selection, but updates are slower because both the table and the index must be updated.
- Indexes require additional storage space.
5. What is the size limit for a row?
A row can be a maximum of 8060 bytes.
6. Explain how a “join” command is used.
A “join” command is used to logically connect two or more tables. This can be done either with or without a common field.
7. What are user-defined data types and give an example of how they can be used?
User-defined data types provides the database with a descriptive name and format and allows the base SQL Server data types to be extended. As an example, let’s say a database has a column named bin_num that is varchar(8) and is used in several tables. A user-defined data type called bin_num_type of varchar(8) can be created and used in all of the tables.
8. What is the difference between “normalization” and “Denormalization”?
When data is normalizing redundant information is removed from the table and it is organized to make future changes simpler. For denormalization, redundant data is allowed to remain in the table. Denormalization can be beneficial because reducing the number of joins used for data processing can make data manipulation and retrieval simpler and therefore and improvement in performance.
9. Describe the purpose of a constraint.
Constraints provide a means to check tables for referential integrity. SQL Server has four types of constraints and a default:
DEFAULT – indicates a column default value in the event that one is not provided by an insert operation
https://norge-ed.com/kjope...risk/NOT NULL – does not allow null values in the specified column
PRIMARY/UNIQUE – requires values in a specific column to be unique
FOREIGN KEY – checks that each value in a particular column exists in a column in a different table.
CHECK – verifies that all stored values in particular column exist in a list that is specified.
10. What is the difference between a primary and a unique key?
While the primary and unique keys enforce uniqueness in their columns, a clustered index is created in the column of the primary key, and a non-clustered index is created in the column of the unique key. Also, NULL values are not allowed with a primary key but up to one is allowed with a unique key.
Some more SQl Testing Interview Questions
11. What is a “trigger”?
Triggers are procedures that are created for a database to enforce rules of integrity. They are executed whenever an operation to modify data such as insert or delete is performed.
12. What type of information is stored in a column that is specified with a bit data type?
The bit data type is used for Boolean data, which is either true or false, 1 or 0. SQL Server versions prior to version 6.5 only allowed the storage of 1’s or 0’s, no NULL values. However, version 7.0 and beyond allows the storage of NULL values.
13. Explain RAID.
RAID is an acronym for Redundant Array of Inexpensive Disk. RAIDs provide fault tolerance for database servers and has six (numbered 0-5) levels of different fault tolerance performance.
14. When does blocking occur?
Blocking occurs when an application connection holds a lock and a conflicting lock type if required for a second connection. In this case, the second connection is considered blocked by the first connection.
15. Explain isolation levels.
Isolation levels include the following: read uncommitted, read committed (default), repeatable read, and serializable. Isolation levels determine the extent of data isolation between simultaneous transactions.
16. What is a deadlock?
A deadlock is a condition where two processes have established a lock on an article of data and each tries to obtain a lock on the other process’ piece. In this case, unless one process is terminated, both processes will wait indefinitely for the data to be released. If SQL Server detects a deadlock, it will kill one of the processes.
17. What is a live lock?
A live lock occurs when a request for an exclusive lock is denied over and over due to interference from shared locks that overlap. This can also occur when a read transaction dominates a page or a table and forces indefinite waiting for a write transaction. If the SQL Server detects four denials of this type, it automatically refuses subsequent shared locks.
18. Give a few SQL server options to move data and/or databases between servers and other databases.
Some options are the following:
- Replication
- DTS
- BCP
- BACKUP/RESTORE command
- Attaching/detaching databases
- Log shipping
- SELECT…INTO
- INSERT…SELECT
- Generating data with INSERT scripts
19. Explain cursors and their disadvantages.
Cursors are used for row-by-row processing of results. There are four types of coursers: dynamic, static, keyset-driven, and forward only. One of the disadvantages of using curser is that whenever a row from a cursor is fetched, it results in a network round-trip. Cursors also require more temporary storage and other resources. In addition to this, some types of cursors impose restrictions on the use of SELECT statements.
20. What is the difference between TRUNCATE TABLE and DELETE TABLE?
The DELETE TABLE command is a little slow because it is recorded in the transaction log. Like DELETE TABLE, the TRUNCATE TABLE command also deletes all the rows in a table, but it is a bit faster because this is not logged. Instead, the TRUNCATE TALE command logs the de-allocation of the table data pages.
21. What is a transaction and what are its properties?
A transaction is considered to be a unit of work with steps in which all or none must be performed. The properties of a transaction can be defined using the acronym ACID, which stands for Atomicity, Consistency, Isolation, and Durability.
read also business analyst interview questions
