Test your basic knowledge |

Oracle SQL

Instructions:

Answer 50 questions in 15 minutes.
If you are not ready to take this test, you can study here.
Match each statement with the correct term.
Don't refresh. All questions and answers are randomly picked and ordered every time you load a test.

This is a study tool. The 3 wrong answers for each question are randomly chosen from answers to other questions. So, you might find at times the answers obvious, but you will see it re-enforces your understanding as you take the test each time.

1. Oracle uses the outer join operator to indicate that nulls are shown for...
Non-matching rows
Both MIN and MAX
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
SYSDATE

2. The query retrieves more than 5 to 15 percent of the rows
Logical unit of work
Load the file into the SQL Statement Box - by opening the location the file is stored and inserting it into the statement Box. Next you can press the F5 key.
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Columns

3. The focus of the logical data model is _____________
Data
STORAGE
Business problems
DICT_COLUMNS

4. A transaction is a...
Hide data
Committed
The main components of a ROWID are the data object number - the number of the data block - the numbers of rows within the data block - and the data file number.
Logical unit of work

5. Write a statement that drops the index need_to_pass_i
Rounded edges
Surrogate
DROP INDEX need_to_pass_i
IN ANY ALL SOME

6. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Another null
Actual column
Correlated
Common column value

7. You can use the ________________ to subtract months from a given date.
A correct statement
2000
SELECT SYSDATE+1/96 - FROM dual
ADD_MONTHS function

8. ____________ are useful for analyzing and exploring the relationships within your data
Self-joins
UNION operator
The main components of a ROWID are the data object number - the number of the data block - the numbers of rows within the data block - and the data file number.
Run scripts

9. SELECT * FROM instructor ORDER BY phone
A correct statement
NATURAL JOIN
SELECT DISTINCT menu FROM restaurant
Orable's flashback query feature allows you to look at values of a query at a specific time in the past - such as before specfic DML statements occured. This can be usefull in case a suer accidentally performs an unintended but committed DML change.

10. A _________ subquery returns a single column with a single row
Scalar
STORAGE
Three
The main components of a ROWID are the data object number - the number of the data block - the numbers of rows within the data block - and the data file number.

11. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
**** Select a row from the outer query - Determine the value of the correlated column - Execute the inner query for each record of the outer query. - Feed the results of the inner query to the outer query and evaluate it. It if satisfies the criteria
The main components of a ROWID are the data object number - the number of the data block - the numbers of rows within the data block - and the data file number.
ANSI
Export Data

12. __________________ inserts the current date and time.
+
User-defined reports
SYSDATE
ANY

13. Explain what a static data dictionary view is and how it is used
RIGHT LEFT JOIN
ROLLBACK
Another null
The static data dictionary stores details about database objects - such as tables - indexes - and views. Whenever a new object is added or an object is changed - data about the object is recorded in the data dictionary.

14. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
Non-matching rows
RENAME just_two_more_weeks TO finally_i
ALL_OBJECTS
Parent table

15. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Semicolon or forward slash
LENGTH
Multiple rows
Inconsistencies

16. What is the most common DATE format found in SQL?
DD-MON-YY
IN ANY ALL SOME
Scalar
2000

17. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
Results
Any
Groups
STORAGE

18. When you have issued a DELETE command - you can still ________ those rows and restore the information.
Retrieve
B-Tree Index
Run scripts
2000

19. Converting a text literal to DATE format requires the use of the...
VALUES clause of an INSERT statement
TO_CHAR function
STORAGE
Columns

20. Explicit data type conversion is preferable to Oracle's...
Multiple rows
DD-MON-YY
Implicit conversion
Committed

21. Another name for a data dictionary view is __________________ or _________________.
Details
Scalar
Static or dynamic
Has not changed the name

22. Returns rows if the condition is true for all the values in the list
Precision
SELECT SYSDATE+1/96 - FROM dual
Leading-edge
All

23. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Fractional seconds
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
INNER JOIN
INTERSECT

24. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Month ddth - yyyy
Rounded edges
Static or dynamic
KEEP INDEX

25. To join four tables - you must have at least _____ join conditions.
Black
Both MIN and MAX
Three
Inconsistencies

26. List the different types of data dictionary views.
USER_ - ALL_ - DBA_ - and V$
Details
Primary key/foreign key
Scale

27. How many characters can be stored in the CHAR column/field?
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
LENGTH
2000
Has not changed the name

28. FEEDBACK
**** Select a row from the outer query - Determine the value of the correlated column - Execute the inner query for each record of the outer query. - Feed the results of the inner query to the outer query and evaluate it. It if satisfies the criteria
X
ROLLBACK
Command returns the number of records returned by a query.

29. SELECT LIST of a subquery in an INSERT statement
ALL_OBJECTS
SET clause of an UPDATE statement
TO_CHAR function
V$

30. Explain how Substitution Variables are used when writing scripts.
X
When you want to run the same script repeatedly - but want to be able to change a variable you would use a substitution variable. During the execution of the script the user will be prompted to input the variable to use.
The static data dictionary stores details about database objects - such as tables - indexes - and views. Whenever a new object is added or an object is changed - data about the object is recorded in the data dictionary.
Run scripts

31. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
ROLLBACK
Null
Surrogate
Any

32. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Asterisk
Rounded edges
LIKE
Script

33. _________________ relationships are always optional relationships.
ADD
Recursive
Primary key/foreign key
ADD_MONTHS function

34. Write a solution that adds 20 minutes to a given date.
Equijoin
Details
Leading-edge
SELECT SYSDATE+1/96 - FROM dual

35. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
+
Hide data
Semicolon or forward slash
LIKE

36. The key that most database designers prefer and is chosen by the system is the _____________ key.
KEEP INDEX
Testing functions
Surrogate
X

37. Explain why constraints are used when building tables.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

38. The _______ function adds values together for a group of rows.
SUM
Parent table
Script
Groups

39. Match each of the SQL commands on the left with a verb from the list.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
DICTIONARY
4000
RIGHT LEFT JOIN

40. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
ALL_OBJECTS
BETWEEN
Fractional seconds
Combine

41. The DUAL table can be used for...
Testing functions
Black
Combine
Parent table

42. When you run a script in SQL*Plus you must give the...
Any
SELECT SYSDATE+1/96 - FROM dual
Location of the file
Static or dynamic

43. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
ADD_MONTHS function
Both MIN and MAX
SUM
UNION

44. List 6 Table-Related Menu Choices
Script
MINUS
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Details

45. The ______ operator compares a list of values for equivalency
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
ANY
X

46. The TRUNC function on a date without a format model truncates the timestamp to...
Semicolon or forward slash
12:00:00 A.M
Crow's foot
ADD_MONTHS function

47. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
@ or START
DICT_COLUMNS
Implicit conversion
VALUES clause of an INSERT statement

48. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
INTERSECT
KEEP INDEX
When you want to run the same script repeatedly - but want to be able to change a variable you would use a substitution variable. During the execution of the script the user will be prompted to input the variable to use.
The indexed column is part of an expression. In this case - consider creating a function-based index instead

49. The ALTER SESSION statement can change the session's...
RENAME just_two_more_weeks TO finally_i
F12
Time zone
ANY

50. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Non-matching rows
Orable's flashback query feature allows you to look at values of a query at a specific time in the past - such as before specfic DML statements occured. This can be usefull in case a suer accidentally performs an unintended but committed DML change.
Command returns the number of records returned by a query.
999999.9999