Top 50 SQL Server Interview Questions & Answers 1

In this article, we will discuss the Top 50 SQL Server Interview Questions & Answers that you may be asked during a SQL Server administrator or developer technical job interview. Before we get started, if you want to know about Manual Testing Interview Questions, please go through the following article: Basic Interview Questions for Manual Testing.

Q1. What is RDBMS?

An RDBMS or Relational Database Management System is a type of DBMS having relationships between the tables using indexes and different constraints like primary key, foreign key, etc. The use of indexes and constraints helps in faster retrieval and better management of data within the databases.

Q2. What is the difference between DBMS and RDBMS?

The primary difference between DBMS and RDBMS is, in RDBMS we have relations between the tables of the database. Whereas in DBMS there is no relation between the tables(data may even be stored in files). RDBMS has primary keys and data is stored in tables. DBMS has no concept of primary keys with data stored in navigational or hierarchical form. RDBMS defines integrity constraints in order to follow ACID properties. While DBMS doesn’t follow ACID properties.

Q3. What is a database?

A database is a structured collection of data for faster and better access, storage, and manipulation of data. A database can also be defined as a collection of tables, schema, views, and other database objects.

Q4. What is a table?

Tables are the database object that is used for storing related records in the form of rows and columns.

Q5. What is a field in a table?

A field is an entity used for storing a particular type of data within a table like numbers, characters, dates, etc.

Q6. What is a tuple, record, or row in a table?

A tuple or record is an ordered set of related data items in a table.

Q7. What is SQL?

SQL stands for Structured Query Language, it is a language used for creating, storing, fetching, and updating data and database objects in RDBMS.

Q8. What are the different types of SQL commands?

SQL commands are the set of commands used to communicate and manage the data present in the database. The different types of SQL commands are-

1. DDL – Data Definition Language
2. DML – Data Manipulation Language
3. DCL – Data Control Language
4. TCL – Tractional Control Language

Q9. Explain DDL commands. What are the different DDL commands in SQL?

DDL refers to Data Definition Language, which is used to define or alter the structure of the database. The different DDL commands are-

• CREATE – Used to create a table in the database
• DROP – Drops the table from the database
• ALTER – Alters the structure of the database
• TRUNCATE – Deletes all the records from the database but not its database structure
• RENAME – Renames a database object

Q10. Explain DML commands. What are the different DML commands in SQL?

DML refers to Data Manipulation Language, it is used for managing data present in the database. Some of the DML commands are select, insert, update, delete, etc.

Q11. Explain DCL commands. What are the different DCL commands in SQL?

DCL refers to Data Control Language, these commands are used to create roles, grant permission, and control access to the database objects. The three DCL commands are-

• GRANT – Grants permission to a database user
• REVOKE – Removes access privileges from a user provided with the GRANT command
• Deny – Explicitly prevents a user from receiving particular permission(e.g. preventing a particular user belonging to a group to receive the access controls

Q12. Explain TCL commands. What are the different TCL commands in SQL?

TCL refers to Traction Control Language, it is used to manage the changes made by DML statements. These are used to process a group of SQL statements comprising a logical unit. The three TCL commands are-

• COMMIT – Commit write the changes to the database
• SAVEPOINT – Savepoints are the breakpoints, these divide the traction into smaller logical units which could be further roll-backed.
• ROLLBACK – Rollbacks are used to restore the database since the last commit.

Q13. What are SQL constraints?

SQL constraints are the set of rules that impose some restrictions while the insertion, deletion, or updating of data in the databases. In SQL we have both column-level as well as table-level constraints which are applied to columns and tables respectively. Some of the constraints in SQL are – Primary Key, Foreign Key, Unique Key Key, Not NULL, DEFAULT, CHECK, and Index constraint.

Q14. What is database testing?

Database testing is checking the integrity of actual data in the front end with the data present in the database. It involves validating the data in the database, checking that there are no orphan records (records with a foreign key to a parent record that has been deleted”), no junk records are present, updating records in the database, and verifying the value in the front end.

Q15. What is a Unique constraint?

A unique constraint is used to ensure that the field/column will have only a unique value(no duplication).

Q16. What is a Primary Key?

A primary key is a column or a combination of columns that uniquely identifies a record in the database. A primary key can only have unique and not NULL values and there can be only one primary key in a table.

Q17. What is the difference between a unique key and a primary key?

A unique key allows a null value(although only one) but a primary key doesn’t allow null values. A table can have more than one unique keys columns while there can be only one primary key. A unique key column creates a non-clustered index whereas a primary key creates a clustered index on the column.

Q18. What is a composite key?

A composite key is a primary key with multiple columns as in the case of some tables a single field might not guarantee unique and not null values, so a combination of multiple fields is taken as the primary key.

Q19. What is a NULL value?

A NULL value in SQL is an unknown or blank value. Since NULL is an unknown value, a NULL value cannot be compared with another NULL value. Hence we cannot use the ‘=’ operator in conditioned with NULL. For this, we have IS NULL clause that checks if the value in the field is NULL or not.

Q20. What is a Not Null constraint?

A Not NULL constraint is used for ensuring that the value in the field cannot be NULL.

Q21. What is a Foreign Key?

A foreign key is used for enforcing referential integrity in which a field marked as a foreign key in one table is linked with a primary key of another table. With this referential integrity, we can have only the data in a foreign key that matches the data in the primary key of the other table.

Q22. What is a Check constraint?

A check constraint is used to limit the value entered in a field. E.g. we can ensure that field ‘Salary’ can only have a value greater than 1000 using check constraint-

Q23. What is a Default constraint?

A Default constraint is used for providing a default value to a column when no value is supplied at the time of insertion of the record in the database.

Q24. What is a clustered index?

Clustered indexes physically sort the rows in the table based on the clustering key(by default primary key). The clustered index helps in the fast retrieval of data from the databases. There can be only one clustered index in a table.

Q25. What is a non-clustered index?

Non-clustered indexes have a jump table containing key values pointing to rows in the table corresponding to the keys. There can be multiple clustered indexes in a table.

Q26. What is the difference between the delete, truncate and drop commands?

The difference between the Delete, Truncate and Drop commands is –

• Delete command is a DML command, it removes rows from the table based on the condition specified in the where clause, being a DML statement we can roll back changes made by the delete command.
• Truncate is a DDL command, it removes all the rows from a table and also frees the space held, unlike the delete command. It takes a lock on the table while the delete command takes a lock on rows of the table.
• Drop is a DDL command, it removes the complete data along with the table structure(unlike the truncate command that removes only the rows).

Q27. What are the different types of joins in SQL?

Joins are used to combine records from multiple tables. The different types of joins in SQL are-

1. Inner Join – To fetch rows from two tables having matching data in the specified columns of both tables.
   SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

   1 2 3

   SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

2. Left Join – To fetch all rows from the left table and matching rows of the right table
   SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

   1 2 3

   SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

3. Right Join – To fetch all rows from the right table and match rows of the left table
   SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

   1 2 3

   SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

4. Full Outer Join – To fetch all rows of a left table and all rows of the right table
   SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

   1 2 3

   SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;

5. Self Join – Joining a table to itself, for referencing its own data
   SELECT * FROM TABLE1 T1, TABLE1 T2 WHERE T1.columnA = T2.columnB;

   1 2 3

   SELECT * FROM TABLE1 T1, TABLE1 T2 WHERE T1.columnA = T2.columnB;

Q28. What is the difference between a cross-join and a full outer join?

A cross join returns the cartesian product of the two tables, so there is no condition or on clause as each row of table is joined with each row of tables whereas a full outer join will join the two tables on the basis of the condition specified in the on clause and for the records not satisfying the condition null value is placed in the join result.

Q29. What is the difference between having and where clause?

A ‘where’ clause is used to fetch data from a database that specifies particular criteria (specified after the where clause). Whereas a ‘having’ clause is used along with ‘group by’ to fetch data that meets particular criteria specified by the aggregate function. For example-

Employee	Project
A	P1
B	P2
C	P3
B	P3

In the above table, if we want to fetch Employees working in project P2, we will use the ‘where’ clause-

The fundamental difference between Union and Union All command is, Union is by default distinct i.e. it combines the distinct result set of two or more select statements. Whereas, Union combines all the rows including duplicates in the result set of different select statements.

Q31. Define the selection into a statement.

Select into statement is used to directly select data from one table and insert it into another, the new table gets created with the same name and type as the old table-

Q32. What is a View in SQL?

A view is a virtual table, it is a named set of SQL statements that can be later referenced and used as a table.

Yes, we can use the ‘where’ clause with ‘groupBy’. The rows that don’t meet the where conditions are removed first and then the grouping is done based on the group by column.

Database normalization is the process of organization of data in order to reduce the redundancy and anomalies in the database. We have different Normalisation forms in SQL like – First Normal Form, Second Normal Form, Third Normal Form, and BCNF.

Q35. Explain First Normal Form(1NF).

According to First Normal Form, a column cannot have multiple values, each value in the columns must be atomic.

Q36. Explain Second Normal Form(2NF).

For a table to be considered in Second Normal Form, it must follow 1NF and no column should be dependent on the primary key.

Q37. Explain Third Normal Form(3NF).

For a table to be in Third Normal Form, it must follow 2NF and each non-prime attribute must be dependent on the primary key of the table. For each functional dependency X -> Y either-
X should be the super key or Y should be the prime attribute(part of one of the candidate keys) of the table

Q38. Explain Boyce and Codd Normal Form(BCNF).

BCNF is the advanced or stricter version of 3NF. For each functional dependency X -> Y-X should be the super key

Q39. What are transactions in SQL?

The transaction is a set of operations performed in a logical sequence. It is executed as a whole, if any statement in the traction fails, the whole traction is marked as failed and not committed to the database.

Q40. What are ACID properties?

ACID properties refer to the four properties of traction in SQL-

1. Atomicity – All the operations in the transaction are performed as a whole or not performed at all.
2. Consistency – The state of the database changes only on successfully committed transactions.
3. Isolation – Even with concurrent execution of multiple transactions, the final state of the DB would be the same as if transactions got executed sequentially. In other words, each transaction is isolated from one other.
4. Durability – Even in the state of crash or power loss the state of the committed transaction remains persistent.

Q41. What are locks in SQL?

Locks in SQL are used for maintaining database integrity in case of concurrent execution of the same piece of data.

Q42. What are the different types of locks in the database?

The different types of locks in the database are-

1. Shared locks – Allows data to be read-only(Select operations), preventing the data to be updated when in the shared lock.
2. Update locks – Applied to resources that can be updated. There can be only one update lock on data at a time.
3. Exclusive locks – Used to lock data being modified(INSERT, UPDATE, or DELETE) by one transaction thus ensuring that multiple updates cannot be made to the same resource at the same time.
4. Intent locks – A notification mechanism using which a transaction conveys that intends to acquire a lock on data.
5. Schema locks- Used for operations when the schema or structure of the database is required to be updated.
6. Bulk Update locks – Used in case of bulk operations when the TABLOCK hint is used.

Q43. What are aggregate functions in SQL?

Aggregate functions are SQL functions that return a single value calculated from multiple values of columns. Some of the aggregate functions in SQL are-

• Count() – Returns the count of the number of rows returned by the SQL expression
• Max() – Returns the max value out of the total values
• Min() – Returns the min value out of the total values
• Avg() – Returns the average of the total values
• Sum() – Returns the sum of the values returned by the SQL expression

Q44. What are scalar functions in SQL?

Scalar functions are the functions that return a single value by processing a single value in SQL. Some of the widely used SQL functions are-

• UCASE() – used to convert a string to upper case
• LCASE() – Used to convert a string to lowercase
• ROUND() – Used to round a number to the decimal places specified
• NOW() – Used to fetch current system date and time
• LEN() – Used to find the length of a string
• SUBSTRING() or MID() – MID and SUBSTRING are synonyms in SQL. They are used to extract a substring from a string by specifying the start and end index. Syntax – SUBSTRING(ColumnName,startIndex, EndIndex).
• LOCATE() – Used to find the index of the character in a string. Syntax – LOCATE(character,ColumnName)
• LTRIM() – Used to trim spaces from left
• RTRIM() – Used to trim spaces from right

Q45. What is a coalesce function?

Coalesce function is used to return the first, not NULL value out of the multiple values or expressions passed to the coalesce function as parameters. Example- COALESCE(NULL, NULL, 5, ‘ArtOfTesting’) will return the value 5. COALESCE(NULL, NULL, NULL) will return a NULL value as no not NULL value is encountered in the parameters list.

Q46. What are cursors in SQL?

Cursors are objects in SQL that are used to traverse the result set of a SQL query one by one.

Q47. What are stored procedures? Explain there advanatages?

Stored procedures are SQL procedures(a bunch of SQL statements) that are stored in the database and can be called by other procedures, triggers, and other applications.

The advantages of the stored procedure are-

1. Stored procedures improve performance as the procedures are pre-compiled as well as cached.
2. Make queries easily maintainable and reusable as any change is required to be made at a single location.
3. Reduce network usage and traffic.
4. Improve security as stored procedures restrict direct access to the database.

Q48. What are triggers in SQL?

Triggers are a special type of stored procedure that gets executed when a specified event occurs. Syntax-

Q49. What are orphan records?

Orphan records are records having a foreign key to a parent record that doesn’t exist or got deleted.

Q50. How can we remove orphan records from a table?

In order to remove orphan records from the database, we need to create a join on the parent and child tables and then remove the rows from the child table where the id IS NULL.

The article was published on May 5, 2017 @ 10:27 PM