Quosphere InfoSolutions Interview Questions and Answers

Views, functions, and stored procedures are database objects used to manipulate and retrieve data.

• Views are virtual tables that display data from one or more tables in a specific way.

• Functions are reusable code blocks that perform a specific task and return a value.

• Stored procedures are precompiled SQL statements that can be executed with parameters.

• All three objects can improve performance and simplify complex queries...read more

WHERE clause filters rows before grouping and HAVING clause filters groups after grouping.

• WHERE clause is used to filter rows based on a condition

• HAVING clause is used to filter groups based on a condition

• WHERE clause is used with SELECT, UPDATE, DELETE statements

• HAVING clause is used with GROUP BY clause

• WHERE clause is executed before GROUP BY clause

• HAVING clause is executed after GROUP BY clause

Query to retrieve salary based on employee tenure

• Use the DATEDIFF function to calculate the tenure of each employee

• Join the employee and salary tables on the employee ID

• Filter the results based on the tenure of the employee

• Select the employee name and salary

Query to calculate manager commission based on employee commission

• Join the employee and manager tables on the manager ID

• Calculate the total commission earned by each employee

• Calculate the total commission earned by all employees under each manager

• Calculate the manager commission as a percentage of the total employee commission

• Return the manager name and commission amount

Triggers are database objects that automatically execute in response to certain events or changes in data.

• Triggers can be used to enforce business rules and data integrity

• They can be used to audit changes to data

• Triggers can be used to replicate data to other systems

• Examples of events that can trigger a trigger include INSERT, UPDATE, and DELETE statements

To delete duplicate rows, use the DISTINCT keyword or GROUP BY clause.

• Use the DISTINCT keyword to select unique rows from a table.

• Use the GROUP BY clause to group rows with the same values together.

• Use the HAVING clause to filter out groups with more than one row.

• Use the DELETE statement with a subquery to delete duplicate rows.

• Example: DELETE FROM table_name WHERE id NOT IN (SELECT MIN(id) FROM table_name GROUP BY col