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. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
999999.9999
Export Data
SELECT DISTINCT menu FROM restaurant
All caps

2. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
Surrogate key
X
SELECT DISTINCT menu FROM restaurant
DROP INDEX need_to_pass_i

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

4. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
UNION
Re-create
ROLLBACK
Surrogate key

5. The _____________ clause determines how rows are grouped.
GROUP BY
ADD
Rounded edges
Crow's foot

6. What is the keyboard shortcut for the ROLLBACK command?
Static or dynamic
Columns
F12
Business problems

7. A join across three tables can be expressed with the ________ join syntax.
Structured Query Language
Extra spaces
X
ANSI

8. PAGESIZE
2000
Nested
Default is 14; when you use the default - you repeat the heading every 14 rows.
Non-matching rows

9. What is the data type you can use if you need an unlimited number of textual characters?
Business problems
CLOB
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
Results

10. What do views do that makes them so useful for security?
Details
Scalar
Hide data
Month ddth - yyyy

11. SQL*Plus is used more than SQL Developer when you need to...
Run scripts
Location of the file
BETWEEN
Optional

12. Joins involving multiple columns must always follow the ____________ relationship path.
Static or dynamic
F12
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Primary key/foreign key

13. Which one of the following string functions tells you how many characters are in a string?
LENGTH
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.
DROP INDEX need_to_pass_i
Business problems

14. A calculation with a null always yields...
Time zone
DICT_COLUMNS
Another null
MINUS

15. What is the name of the key automatically generated from a sequence?
Actual column
Surrogate key
Nested
UNION

16. Explicit data type conversion is preferable to Oracle's...
Columns
Committed
RUN SCRIPT
Implicit conversion

17. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
STORAGE
Details
LIKE
Twice

18. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
X
GROUP BY
Both MIN and MAX
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

19. _________________ relationships are always optional relationships.
When the table is small (unless your business rules require you too)
Export Data
Recursive
4

20. The purpose of the HAVING clause is to eliminate___________.
Committed
Self-joins
Groups
A correct statement

21. SELECT LIST of a subquery in an INSERT statement
Default is 14; when you use the default - you repeat the heading every 14 rows.
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
SET clause of an UPDATE statement
DD-MON-YY

22. How many characters can you have using the VARCHAR data type?
The way NULL values are treated
4000
The indexed column is part of an expression. In this case - consider creating a function-based index instead
VALUES clause of an INSERT statement

23. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
Another null
X
**** 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
Run scripts

24. Write a solution that adds 20 minutes to a given date.
VALUES clause of an INSERT statement
Logical unit of work
Re-create
SELECT SYSDATE+1/96 - FROM dual

25. Match each of the SQL commands on the left with a verb from the list.
@ or START
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
KEEP INDEX
All

26. A _________ subquery returns a single column with a single row
Retrieve
UNION operator
Scalar
X

27. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
V$
99.99
USER_ - ALL_ - DBA_ - and V$
DICT_COLUMNS

28. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Another null
Mathematical equations
Multiple rows
Equijoin and correlated subquery

29. What is the maximum value for a column defined as NUMBER(4 -2)?
UNION
Surrogate
99.99
Results

30. Self-joins are useful for performing comparisons and checking for ___________ in data.
Inconsistencies
Self-joins
BETWEEN
UNION operator

31. Converting a text literal to DATE format requires the use of the...
Equijoin and correlated subquery
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Leading-edge
TO_CHAR function

32. The COUNT function uses the __________ wildcard.
Groups
LENGTH
DICT_COLUMNS
Asterisk

33. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Equijoin and identical column names
Rounded edges
Subqueries allow you to nest multiple queries into each other. This gives you the ability to use one statement to break down a problem into individual components. Without nesting queries - you would be required to have multiple queries which could be
UNION

34. You use the _________ keyword to add columns or constraints to a table.
LIKE
ADD
Subqueries allow you to nest multiple queries into each other. This gives you the ability to use one statement to break down a problem into individual components. Without nesting queries - you would be required to have multiple queries which could be
Equijoin and correlated subquery

35. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Black
999999.9999
USER_ - ALL_ - DBA_ - and V$
Nested

36. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
**** 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
12:00:00 A.M
BETWEEN
+

37. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Data
VALUES clause of an INSERT statement
2000

38. NUMBER functions can be...
GROUP BY
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
DICTIONARY
Nested

39. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
KEEP INDEX
All caps
ON
Logical unit of work

40. Explain what a static data dictionary view is and how it is used
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.
INTERSECT
User-defined reports
Rounded edges

41. Explain what is found in the All Objects report folder in SQL Developer
VALUES clause of an INSERT statement
All
Details
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

42. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
Results
SELECT SYSDATE+1/96 - FROM dual
4
To achieve good performance for data retrieval and data manipulation statements Oracle uses indexes. In the same way you would use an index in the back of a book to quickly find information - Oracle uses indexes to speed up data retrieval. If the app

43. When you have issued a DELETE command - you can still ________ those rows and restore the information.
Retrieve
Logical unit of work
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Data

44. FEEDBACK
Optional
Command returns the number of records returned by a query.
Precision
Primary key/foreign key

45. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
User-defined reports
Business problems
SELECT DISTINCT menu FROM restaurant

46. Dynamic views begin with a ___________.
V$
Common column value
BETWEEN
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

47. When is it not a good idea to use an index?
When the table is small (unless your business rules require you too)
INTERSECT
Equijoin and identical column names
Export Data

48. Explain how Substitution Variables are used when writing scripts.
When the table is small (unless your business rules require you too)
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.
F12

49. Explain in detail the impact of INDEXES and how they are used by Oracle.
RIGHT LEFT JOIN
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.
To achieve good performance for data retrieval and data manipulation statements Oracle uses indexes. In the same way you would use an index in the back of a book to quickly find information - Oracle uses indexes to speed up data retrieval. If the app
Rounded edges

50. It is considered good form to put all SQL commands in...
All caps
RIGHT LEFT JOIN
To achieve good performance for data retrieval and data manipulation statements Oracle uses indexes. In the same way you would use an index in the back of a book to quickly find information - Oracle uses indexes to speed up data retrieval. If the app
DECODE