xNARA Research Associate Interview Questions and Answers

Alter command on table affects views created on the table.

• Views are virtual tables created from one or more tables.

• Alter command on a table can change the structure of the table, which can affect the views created on it.

• If the columns used in the view are altered or dropped, the view will become invalid.

• If the data in the table is changed, the data in the view will also change accordingly.

Data transformations in SSIS include Lookup which retrieves data from a source based on a matching condition.

• Lookup is used to retrieve data from a source based on a matching condition

• It can be used to perform joins between two data sources

• Lookup can be used to update or insert data into a destination based on a match

• Other data transformations in SSIS include sorting, aggregating, and merging data

Drop deletes the table structure and data, truncate deletes only data, and delete deletes specific rows.

• Drop: removes the table and all its data permanently

• Truncate: removes all data from the table but keeps the structure

• Delete: removes specific rows from the table based on a condition

• Drop and truncate cannot be rolled back, but delete can be

• Drop and truncate reset the identity of the table, but delete does not

Truncate resets identity value, delete does not.

• Truncate resets the identity value to the original seed value

• Delete does not change the identity value

• Example: Truncate table resets identity value to 1

• Example: Delete from table does not change identity value

Joins are used to combine data from two or more tables based on a related column. Types include inner, outer, left, right, and cross join.

• Inner join returns only the matching rows from both tables.

• Outer join returns all rows from both tables, with null values for non-matching rows.

• Left join returns all rows from the left table and matching rows from the right table.

• Right join returns all rows from the right table and m...read more

Query to join employees who are all managers using id in the table.

• Use self join to join the table on manager id and employee id

• Filter the result to only include employees who are also managers

• Example: SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.id = e2.manager_id WHERE e2.manager_id IS NOT NULL

• Ensure that the table has a manager_id column