Collabera Technologies Interview Questions and Answers

Types of indexes in SQL Server and definition of clustered index.

• Types of indexes: clustered, non-clustered, unique, filtered, columnstore, full-text

• Clustered index determines physical order of data in table

• Only one clustered index per table

• Primary key is automatically a clustered index if not specified otherwise

Denormalization is the process of adding redundant data to improve query performance.

• Reduces the number of joins required to retrieve data

• Improves read performance

• Increases data redundancy

• May require additional effort to maintain data consistency

SP is used to execute a set of SQL statements, while function returns a single value.

• SP can have input/output parameters, while function can only have input parameters.

• SP can modify data, while function cannot.

• SP can be used to execute multiple SQL statements, while function can only execute a single SQL statement.

• Functions can be used in SELECT statements, while SP cannot.

• Examples of functions include SUM, AVG, and COUNT, while examples of SPs include stored procedures for i...read more

Corelated queries use data from multiple tables, while non-corelated queries use data from a single table.

• Corelated queries involve joining tables to retrieve data

• Non-corelated queries involve selecting data from a single table

• Corelated queries are typically slower than non-corelated queries

• Examples of corelated queries include inner join, left join, and right join

• Examples of non-corelated queries include select, insert, and update

I have worked on domains like e-commerce, finance, and healthcare.

• E-commerce: developed an online shopping platform with payment gateway integration

• Finance: worked on a trading platform with real-time data analysis

• Healthcare: developed a patient management system for a hospital

DML stands for Data Manipulation Language.

• DML is a subset of SQL used to manipulate data in a database.

• It includes commands like INSERT, UPDATE, DELETE, and SELECT.

• DML is used to modify the data stored in tables.

• DML commands are used in conjunction with Data Definition Language (DDL) commands.

• Examples of DML commands include: INSERT INTO, UPDATE SET, DELETE FROM, and SELECT WHERE.

Views are virtual tables that display data from one or more tables in a database.

• Views are created using SELECT statements.

• They can be used to simplify complex queries.

• They can also be used to restrict access to sensitive data.

• Views do not store data themselves, but rather display data from underlying tables.

• They can be used to join multiple tables together.

• Views can be updated, but there are some restrictions.

• They can improve performance by reducing the amount of data that n...read more

A cross join returns the Cartesian product of two tables.

• It combines each row of one table with every row of another table.

• It does not use any join condition.

• It can result in a large number of rows if the tables are large.

• Syntax: SELECT * FROM table1 CROSS JOIN table2;

• Example: SELECT * FROM employees CROSS JOIN departments;