Prosper Informatics Interview Questions and Answers

SQL statements are executed in a specific order to retrieve, manipulate, or delete data from a database.

• SQL statements are parsed and validated first

• Next, the query optimizer creates an execution plan

• The execution plan is executed by the SQL engine

• Finally, the results are returned to the user

Primary key uniquely identifies a record in a table, while unique key ensures all values in a column are different.

• Primary key enforces uniqueness and not null constraint

• Unique key enforces uniqueness but allows null values

• A table can have only one primary key but multiple unique keys

• Primary key is automatically indexed for faster retrieval

There are four types of joins in SQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

• INNER JOIN: Returns rows when there is at least one match in both tables.

• LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.

• RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.

• FULL JOIN: Returns rows when there is a match in one of the tables.

Overcome

• To conquer or defeat

• To successfully deal with a problem or difficulty

• To prevail over obstacles

Yes, stored procedures can return result sets or scalar values.

• Stored procedures can return result sets containing multiple rows and columns

• They can also return scalar values like integers, strings, etc.

• Results can be accessed by calling the stored procedure and fetching the output

Indexes are data structures used to improve the speed of data retrieval operations in databases.

• Types of indexes include clustered, non-clustered, unique, and composite indexes.

• Indexes can be created on one or multiple columns in a table.

• Examples of indexes are primary key indexes, foreign key indexes, and secondary indexes.

Functions are defined by specifying the function name, parameters, return type, and body of the function.

• Functions are defined using the 'function' keyword followed by the function name.

• Parameters are listed inside parentheses after the function name.

• The return type of the function is specified before the function name.

• The body of the function is enclosed in curly braces {} and contains the code to be executed.

• Example:...read more

Magic table is a virtual table in SQL Server that is used to access the inserted, deleted, or updated data during triggers.

• Magic table is used in SQL Server triggers to access the data affected by INSERT, UPDATE, or DELETE operations.

• It is not a physical table, but a virtual table that stores the affected data temporarily.

• The 'inserted' and 'deleted' tables are examples of magic tables in SQL Server triggers.