Question 1

How does Oracle prevent other users from modifying data that has been changed in a pending transaction?
A)The records are locked
B)The records are marked as dirty
C)The records are not available
D)There is nothing to prevent another user from modifying the records

Accepted Answer

Oracle prevents other users from modifying data that has been changed in a pending transaction by locking the records. This ensures that no other user can update or modify the records until the pending transaction is completed or rolled back.

Question 2

A join can only be used to retrieve data from two tables that are related via a foreign key relationship.

Accepted Answer

A join is a database operation that combines rows from two or more tables based on a related column between them. The related column is typically a foreign key, which is a column in one table that references a primary key in another table.

Question 3

You must use the GROUP BY clause in a query if you are using a group function.

Accepted Answer

Group functions can be used without the GROUP BY clause if you are not aggregating data across multiple rows, such as when using them with a single row or without any columns that need grouping.

Question 4

In an outer join,all rows will be returned from the outer table.

Accepted Answer

The answer of In an outer join,all rows will be...

Question 5

What format model would you use to display 1237.35 as $1237.35
A)1111.11
B)9999.99
C)$####.##
D)$9999.99

Accepted Answer

The best choice to display 1237.35 as $1237.35 would be the format model D, which is $9999.99. The "#" symbol is used in the format model to represent any digit or no digit. In this case, the "$" symbol is placed before the format model to indicate that the currency symbol should be displayed. The format model restricts the output to a maximum of four digits before the decimal point and two digits after the decimal point, which is suitable for displaying currency values.

Question 6

The UNION ALL operator excludes duplicate values.

Accepted Answer

The UNION ALL operator includes all rows from both queries including duplicates. If you want to exclude duplicates, you can use the UNION operator instead.

Question 7

You must use a table alias when creating a self-join.

Accepted Answer

When creating a self-join, we need to use a table alias to differentiate the columns from the same table that we are joining with itself. If we don't use an alias, the query will return an error.

Question 8

Which command is used to give a user permission to alter another user's table?
A)ADD
B)GIVE
C)GRANT
D)ALTER

Accepted Answer

The GRANT command is used to give users permissions to access and modify database objects. In this case, the command would be used to grant a user the ability to alter another user's table. ADD is not a valid command for granting permissions. GIVE is not a command in SQL. ALTER is a command for modifying the structure of a table, not granting permissions.

Question 9

The query INSERT INTO student ____ ('abc','123');inserts a new row into the student table.
A)DATA
B)COLS
C)ROWS
D)VALUES

Accepted Answer

The correct syntax for inserting values into a table is to use the keyword "VALUES" followed by a list of values enclosed in parentheses. Therefore, the correct choice is D: "VALUES".

Question 10

If you are joining three tables you must select a column from each table in the select portion of the statement.

Accepted Answer

While it's possible to select a column from each table, it is not required in order to perform a join. You may only need to select columns from one or two of the tables involved in the join, depending on what information you need to retrieve.

Question 11

What is the difference between deleting all records from a table and truncating the table?
A)Deleting is faster
B)Truncating does not save roll back information while delete does
C)Only a DBA can truncate,anyone can delete
D)Delete does not save roll back information while truncate does

Accepted Answer

Truncating a table does not save rollback information in the transaction log, which makes it faster than deleting all records. Deleting requires a rollback log to maintain the rollback information, which takes more time. However, anyone with proper permissions can perform both delete and truncate operations.

Question 12

All database views are read-only.

Accepted Answer

While it is common for database views to be read-only, it is possible to create views that allow for data modification.

Question 13

If you think that you may wish to rollback part of a transaction,but not the whole thing,it is a good idea to create a ____.
A)bookmark
B)savepoint
C)transaction marker
D)buffer

Accepted Answer

Creating a savepoint allows you to mark a point within a transaction where you can rollback to if needed, without rolling back the entire transaction. This provides more control and precision in managing transactions. Bookmarks, transaction markers, and buffers are not relevant to managing transactions in this context.

Question 14

When using a nested query,the main query is executed after the subquery.

Accepted Answer

The subquery (nested query) must be executed first in order to provide the necessary data for the main (outer) query to use in its execution.

Question 15

When you drop a view,you delete the data values that appear in the view.

Accepted Answer

When you drop a view, you are only deleting the view definition or the query used to create the view. The underlying table or data source remains intact and the data values are not affected.

Question 16

How would you select the current value of the sequence s_id_seq?
A)DISPLAY s_id_seq.CURRVAL;
B)SELECT s_id_seq.CURRVAL;
C)SELECT s_id_seq.CURRVAL FROM DUAL;
D)SHOW s_id_seq.CURRVAL

Accepted Answer

To select the current value of a sequence, we use SELECT statement with the sequence name followed by .CURRVAL. We also need to use the from clause with DUAL table. This is because some databases require the from clause to work with SELECT statement even if no table is selected. Therefore, the correct syntax to select the current value of the sequence is SELECT s_id_seq.CURRVAL FROM DUAL.

Question 17

Subqueries can be nested to many levels.

Accepted Answer

Subqueries can be nested to many levels in SQL. This means that a subquery can contain another subquery, which can contain another subquery, and so on.

Question 18

How does Oracle keep sequence information unique for each user?
A)Only one user has access to a sequence
B)Using user session
C)Each user has a private copy of the sequence
D)It is not always unique - there is a possibility that two users may receive the same sequence number

Accepted Answer

Oracle uses the user session to keep track of sequence information and ensure that each user receives a unique sequence number. This allows multiple users to access the same sequence without receiving duplicate numbers. Option A and C are incorrect because multiple users can access the same sequence, and Oracle does not create a separate copy of the sequence for each user. Option D is incorrect because Oracle's sequence generator is designed to ensure that each number is unique.

Question 19

What will happen when the following query is executed: UPDATE student
SET s_class='SR';
A)All records in the student table will be updated
B)No records in the student table will be updated
C)An error will occur
D)The first record in the student table will be updated

Accepted Answer

The query will update all records in the student table setting the s_class value to 'SR'.

Question 20

A search condition begins with the keyword ____.
A)SELECT
B)WHERE
C)COMMIT
D)BEGIN

Accepted Answer

A search condition in SQL always begins with the keyword WHERE. This keyword is used to specify the search criteria for a query. The SELECT keyword is used to indicate the columns to be returned in the result set, while COMMIT and BEGIN are used for transaction management.

Quiz 3: Using SQL Queries to Insert, update, delete, and View Data

How does Oracle prevent other users from modifying data that has been changed in a pending transaction?

A join can only be used to retrieve data from two tables that are related via a foreign key relationship.

You must use the GROUP BY clause in a query if you are using a group function.

In an outer join,all rows will be returned from the outer table.

What format model would you use to display 1237.35 as $1237.35

The UNION ALL operator excludes duplicate values.

You must use a table alias when creating a self-join.

Which command is used to give a user permission to alter another user's table?

The query INSERT INTO student ____ ('abc','123') ;inserts a new row into the student table.

If you are joining three tables you must select a column from each table in the select portion of the statement.

What is the difference between deleting all records from a table and truncating the table?

All database views are read-only.

If you think that you may wish to rollback part of a transaction,but not the whole thing,it is a good idea to create a ____.

When using a nested query,the main query is executed after the subquery.

When you drop a view,you delete the data values that appear in the view.

How would you select the current value of the sequence s_id_seq?

Subqueries can be nested to many levels.

How does Oracle keep sequence information unique for each user?

What will happen when the following query is executed: UPDATE student SET s_class='SR';

A search condition begins with the keyword ____.