I applied for a developer role in a large scale data migration project job a few months ago. here are questions of the first screen test FYI:
QUESTIONNAIRE FOR ORACLE/SQL DEVELOPER

Note: Some questions may have more than one answer.

1. In Oracle, when is it better to use materialized views?

A. It is always better to use materialized views because views are obsolete and materialized views replace them
B. When a query is slow and its results need to be cached
C. When the involved query is called many times from different places
D. When there are many materials involved in the view
E. Every time a view is slow


2. When should Statistics usually be run?

A. Immediately after creating a new empty table
B. Immediately after inserting data to a table for the first time
C. Immediately after inserting a new row to a table
D. Every time user usage statistics is needed
E. Every time a large amount of rows was added to the table


3. We have two tables, each with one column X of type number which is the primary key. Table A has 100 rows, Table B has 50 rows. When doing an Inner Join between A and B on X, which option would be correct?

A. The maximum number of rows is 100, the minimum number of rows 50
B. The maximum number of rows is 5000, the minimum number of rows 0
C. The maximum number of rows is 50, the minimum number of rows 0
D. The maximum number of rows is 100, the minimum number of rows 100
E. It is not possible to estimate a maximum and minimum. More information is needed.



4. We have two tables, each with one column X of type number which is the primary key. Table A has 100 rows, Table B has 50 rows. When doing a Left Outer Join from A to B on X, which option would be correct?

A. The maximum number of rows is 100, the minimum number of rows 50
B. The maximum number of rows is 5000, the minimum number of rows 0
C. The maximum number of rows is 50, the minimum number of rows 0
D. The maximum number of rows is 100, the minimum number of rows 100
E. It is not possible to estimate a maximum and minimum. More information is needed.


5. What is the difference between a primary key and a unique key?

A. A primary key does not have to be unique; a unique key has to be unique
B. A primary key is mandatory; a unique key is not mandatory
C. Primary keys are not allowed on multiple columns, unique keys are allowed on multiple columns
D. A primary key must start with PK; a unique key can start with any character
E. No answer is correct


6. What is the difference between truncate and delete commands?

A. Truncate deletes the table without writing to the transaction log; delete allows roll back
B. There is no difference; truncate is just a new name that is a synonym to delete
C. Truncate requires a commit, delete does not require
D. Delete requires a commit, truncate does not require
E. No answer is correct


7. A table “T” is given with a column “X” of type number. What does the following command do: "SELECT MAX(X) -1 FROM T"

A. It will return the second largest value X in T
B. It will return the largest value of X, which is greater than minus one
C. It will return a value that is greater by one than the maximum value of X in T
D. It will return a value that is lesser by one than the maximum value of X in T
E. It is an invalid query since MAX function must have a GROUP BY X in the end


8. What is Dual?

A. Dual is an internal Oracle database used for background backup and optimization
B. Dual does not exist in Oracle, only in SQL Server
C. Dual is an SQL command used to provide the dual value of a complex number
D. Dual is an internal Oracle table that can be used for immediate queries
E. No answer is correct


9. The tables “ALL_TAB_COLUMNS” and “USER_TAB_COLUMNS”:

A. Are both system tables
B. ALL_TAB_COLUMNS contains tab characters for all columns, USER_TAB_COLUMNS contains tab characters for user columns
C. ALL_TAB_COLUMNS can only accessed by DBA
D. USER_TAB_COLUMNS is a subset of ALL_TAB_COLUMNS in the current schema
E. All answers are correct


10. To concatenate two strings in Oracle, which of the following options can be used?

A. Use one single pipe character
B. Use two single pipe characters
C. Use one ampersand character
D. Use two ampersand characters
E. Use the CONCAT function


11. Complete the statement: A sequence in Oracle…

A. Will order a table automatically using some sequence
B. Will automatically increment every time it is accessed
C. Defines the sequence of columns in a table
D. Defines the execution sequence of a stored procedure
E. Is a thread safe built-in mechanism


12. What is the difference between UNION and UNION ALL commands?

A. No difference, they are the same. UNION ALL is obsolete.
B. UNION creates a union set of rows of two tables. UNION ALL does the same, but keeps only distinct rows.
C. UNION ALL creates a union set of rows of two tables. UNION does the same, but keeps only distinct rows.
D. UNION is faster than UNION ALL.
E. UNION ALL is faster than UNION.


13. When should the NVL command be used?

A. To retrieve the next value from a sequence
B. To handle empty strings or numbers with a zero value
C. To provide a default value for null values
D. To detect if a column has a null value - if yes it will return True, otherwise False
E. All of the above


14. Usually when a query is said to have a "Full table scan", it means that:

A. The query is optimized to full speed scanning of the table.
B. The query is not optimized to full speed scanning of the table; a FULL SCAN hint is needed.
C. The query is not optimized - One of the tables needs a statistics full scan.
D. The query is not optimized - For one of the tables in the query each row is going to be read.
E. The query is optimized since it is performing a full scan of an index.


15. Complete the statement: Oracle partitions…

A. Partition a table into files, but some partition columns.
B. Partition an index into files, with some defined partition columns.
C. Are always used with functions like rank ().
D. Are usually used only on very large tables.
E. Should only be used on small to medium tables since partitions slow down queries.


16. In a query we have, there is usage of an Index “I” on column “X”. What can we expect?

A. A linear running time worst case, logarithmic running time best case.
B. A linear running time always.
C. A logarithmic running time always.
D. A linear running time best case, logarithmic running time worst case.
E. No answer is correct.