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. Another name for a data dictionary view is __________________ or _________________.
NATURAL JOIN
Static or dynamic
Data
X

2. How many characters can be stored in the CHAR column/field?
2000
B-Tree Index
The indexed column is part of an expression. In this case - consider creating a function-based index instead
V$

3. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Semicolon or forward slash
Leading-edge
RIGHT LEFT JOIN
ROLLBACK

4. 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

5. __________________ inserts the current date and time.
All
SYSDATE
CLOB
VALUES clause of an INSERT statement

6. Explain the steps needed to run a script in SQL Developer
4
INNER JOIN
Export Data
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.

7. Users can only see changes made to a database once they have been...
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.
ANY
Leading-edge
Committed

8. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
IN ANY ALL SOME
Combine
Export Data
UTC

9. The ______ operator compares a list of values for equivalency
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Re-create
ANY
USER_ - ALL_ - DBA_ - and V$

10. The key that most database designers prefer and is chosen by the system is the _____________ key.
Static or dynamic
Surrogate
Self-joins
Scale

11. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Correlated
SELECT SYSDATE+1/96 - FROM dual
When you issue a INSERT - UPDATE - DELETE - or MERGE statement - Oracle automatically locks the modified rows. The lock prevents other sessions from making changes to these rows. The lock is released when the session initiating change commits or roll
RENAME just_two_more_weeks TO finally_i

12. List the reasons that a subquery could not be unnested.
X
B-Tree Index
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Combine

13. Explain how Substitution Variables are used when writing scripts.
LIKE
A correct statement
UNION operator
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.

14. List 6 Table-Related Menu Choices
VALUES clause of an INSERT statement
Time zone
BETWEEN
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

15. The TRUNC function on a date without a format model truncates the timestamp to...
ON
KEEP INDEX
Scale
12:00:00 A.M

16. The RTRIM function is useful for eliminating ________ in a string.
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.
Actual column
Extra spaces
MINUS

17. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
Rounded edges
12:00:00 A.M
VALUES clause of an INSERT statement

18. The ROWID pseudocolumn is not an ________- but it acts like one.
Actual column
DICT_COLUMNS
Location of the file
LIKE

19. Explain Flashback queries and how they are used. Give an example of a FLASHBACK_TRANSACTION query and explain what it does row by row.

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

20. Write a statement that drops the index need_to_pass_i
Columns
DROP INDEX need_to_pass_i
Leading-edge
999999.9999

21. How many characters can you have using the VARCHAR data type?
4000
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.
DICTIONARY
Retrieve

22. When you run a script in SQL*Plus you must give the...
Location of the file
Both MIN and MAX
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.
Self-joins

23. The ________ operator cannot compare against a list of values.
Both MIN and MAX
LIKE
ADD_MONTHS function
DICT_COLUMNS

24. The query retrieves more than 5 to 15 percent of the rows
Running a script file
MINUS
The indexed column is part of an expression. In this case - consider creating a function-based index instead
12:00:00 A.M

25. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
Scalar
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.
Committed
SELECT DISTINCT menu FROM restaurant

26. Which format mask displays December 31st - 1999?
12:00:00 A.M
Script
Month ddth - yyyy
Default is 14; when you use the default - you repeat the heading every 14 rows.

27. Comparison operators can compare multiple _________________.
Multiple rows
Business problems
Columns
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.

28. Returns rows if the condition is true for all the values in the list
DD-MON-YY
All
DICTIONARY
Time zone

29. What is the keyboard shortcut for the ROLLBACK command?
Logical unit of work
VALUES clause of an INSERT statement
4000
F12

30. A _________ subquery returns a single column with a single row
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.
Scalar
Actual column
Retrieve

31. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
Command returns the number of records returned by a query.
Retrieve
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
X

32. The same function can be used ______ in a SELECT statement.
Same
Twice
MINUS
USER_ - ALL_ - DBA_ - and V$

33. NUMBER functions can be...
Fractional seconds
Nested
B-Tree Index
Running a script file

34. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
BETWEEN
Crow's foot
IN ANY ALL SOME
The way NULL values are treated

35. What are the main components of a ROWID?
**** 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.
RIGHT LEFT JOIN
Constraints are used to enforce the business rules of a system. For instance - 'The salary of an employee may not be a negative number' may be enforced with a check constraint on the salary column. Constraints also ensure integrity and data consisten

36. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
Implicit conversion
Fractional seconds
The way NULL values are treated
All caps

37. In a view you can give a column a different name than it has in the...
ANY
Self-joins
Parent table
Non-matching rows

38. When column names on the tables are different - you use the ______ condition.
INTERSECT
When the table is small (unless your business rules require you too)
Business problems
ON

39. When is it not a good idea to use an index?
KEEP INDEX
When the table is small (unless your business rules require you too)
Time zone
ANY

40. SQL*Plus is used more than SQL Developer when you need to...
Inconsistencies
Run scripts
Fractional seconds
999999.9999

41. What is the maximum value for a column defined as NUMBER(4 -2)?
99.99
DESC dict;
Default is 14; when you use the default - you repeat the heading every 14 rows.
+

42. ____________ are useful for analyzing and exploring the relationships within your data
Self-joins
UNION
Leading-edge
CLOB

43. What does SQL stand for?
Rounded edges
The SPOOL command - together with a file name - spools any subsequently issued SQL Plus or SQL command to a file. Just as with other file names - you can add a path to store the file in a directory other than your SQL Plus default directory. SQL Deve
VALUES
Structured Query Language

44. The ___________ operator tests whether a matching row cannot be found.
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.
Semicolon or forward slash
Recursive

45. The __________ operator is inclusive of the two values specified.
When the table is small (unless your business rules require you too)
USER_ - ALL_ - DBA_ - and V$
BETWEEN
UTC

46. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Non-matching rows
Equijoin
Crow's foot

47. The _______ function adds values together for a group of rows.
SUM
DICTIONARY
Results
Combine

48. The COUNT function is useful for determining whether a table has...
Data
ROLLBACK
INTERSECT
Asterisk

49. To equijoin a table with another table involves matching the...
Common column value
Equijoin and identical column names
Equijoin
Orphan

50. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Implicit conversion
@ or START
Both MIN and MAX
RENAME just_two_more_weeks TO finally_i