DML Quiz

What is the use of MERGE command in DML?

• The MERGE command is used to modify data in a table.

• The MERGE command is used to delete data from a table.

• The MERGE command is used to insert data into a table.

• The MERGE command is used to perform an "upsert"(update and insert) operation, which updates existing data or inserts new data depending on a specified condition.

Which of the following is the correct syntax of merge statement

• USING source_table AS S

  MERGE target_table AS T

  ON (T.key_column = S.key_column)

  WHEN MATCHED THEN

      UPDATE SET T.column1 = S.column1, T.column2 = S.column2, ...

  WHEN NOT MATCHED THEN

      INSERT (key_column, column1, column2, ...)

      VALUES (S.key_column, S.column1, S.column2, ...);

• MERGE target_table AS T

  USING source_table AS S

  WHEN MATCHED THEN

      UPDATE SET T.column1 = S.column1, T.column2 = S.column2, ...

  WHEN NOT MATCHED THEN

      INSERT (key_column, column1, column2, ...)

      VALUES (S.key_column, S.column1, S.column2, ...)

  ON (T.key_column = S.key_column);

• MERGE target_table AS T

  USING source_table AS S

  ON (T.key_column = S.key_column)

  WHEN MATCHED THEN

      UPDATE SET T.column1 = S.column1, T.column2 = S.column2, ...

  WHEN NOT MATCHED THEN

      INSERT (key_column, column1, column2, ...)

      VALUES (S.key_column, S.column1, S.column2, ...);

• MERGE target_table AS T

  USING source_table AS S

  ON (T.key_column = S.key_column)

  WHEN MATCHED

      UPDATE SET T.column1 = S.column1, T.column2 = S.column2, ...

  WHEN NOT MATCHED

      INSERT (key_column, column1, column2, ...)

      VALUES (S.key_column, S.column1, S.column2, ...);

The three basic commands required to build a view are the following:

• VIEW, SELECT, WHERE

• CREATE VIEW, SELECT, JOIN

• CREATE VIEW, SELECT, FROM

• VIEW, WHERE, FROM

Which of the following statements are true of a view?

• A view can insert data into tables

• A view simplifies how users work with data.

• A view is not reusable.

• A view joins static tables using user-defined parameters.

Removes rows from a table.

• Drop table

• Remove table

• Delete table

• Alter table

Add new rows to a table.

• Add

• Insert

• Create

• Select

Finding the first not NULL values in a list of columns

• INITCAP

• CONCAT

• COALESCE

• TRUNC

• SUBSTR

_____ is particularly useful when paired with aggregate functions, such as SUM or COUNT.

• GROUP BY

• ORDER BY

• HAVING

• (take risk for extra points)

Answer explanation

A single value returned from an SQL query that includes an aggregate function.

• Vector Aggregate

• Sir gene is always the answer

• Scalar aggregate

What is the purpose of using a savepoint in a database transaction?

• To mark a point in the transaction beyond which changes cannot be rolled back.

• To ensure that changes made till a particular point in the transaction remain unchanged even in case of a rollback.

• To allow multiple users to access the same data simultaneously.

• To ensure that all changes made in a transaction are immediately committed to the database.