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. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
UNION
Precision
Default is 14; when you use the default - you repeat the heading every 14 rows.

2. Explain how Substitution Variables are used when writing scripts.
999999.9999
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.
ALL_OBJECTS
B-Tree Index

3. SELECT * FROM instructor ORDER BY phone
Asterisk
A correct statement
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
SELECT SYSDATE+1/96 - FROM dual

4. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
Actual column
@ or START
Command returns the number of records returned by a query.
UTC

5. When column names on the tables are different - you use the ______ condition.
ON
X
Month ddth - yyyy
ALL_OBJECTS

6. List the reasons that a subquery could not be unnested.
Retrieve
X
RENAME just_two_more_weeks TO finally_i
When the table is small (unless your business rules require you too)

7. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Both MIN and MAX
4
Fractional seconds
STORAGE

8. The ROWID pseudocolumn is not an ________- but it acts like one.
Has not changed the name
INTERSECT
DROP INDEX need_to_pass_i
Actual column

9. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Extra spaces
VALUES clause of an INSERT statement
Crow's foot
Semicolon or forward slash

10. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
INNER JOIN
ANY
+
VALUES clause of an INSERT statement

11. To join four tables - you must have at least _____ join conditions.
DICT_COLUMNS
User-defined reports
Three
ALL_OBJECTS

12. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Equijoin and correlated subquery
X
The indexed column is part of an expression. In this case - consider creating a function-based index instead
ANY

13. The _____________ clause determines how rows are grouped.
Self-joins
Same
GROUP BY
Asterisk

14. Match each of the SQL commands on the left with a verb from the list.
LENGTH
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Twice
VALUES

15. The ____________ command makes changes permanent.
Rounded edges
X
Scalar
COMMIT

16. How many characters can you have using the VARCHAR data type?
999999.9999
Implicit conversion
DD-MON-YY
4000

17. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
DROP INDEX need_to_pass_i
BETWEEN
IN ANY ALL SOME
FALSE

18. UNION ALL does not sort - therefore it is less time consuming to use than the...
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
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
UNION operator
All caps

19. What are the main components of a ROWID?
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
999999.9999
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.
TO_CHAR function

20. What is the tab in SQL Developer where data dictionary view information can be found?
Null
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
Details
Equijoin and correlated subquery

21. Returns rows if the condition is true for all the values in the list
User-defined reports
When the table is small (unless your business rules require you too)
All
999999.9999

22. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
X
DD-MON-YY
Script
Actual column

23. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
USER_ - ALL_ - DBA_ - and V$
RUN SCRIPT
Any

24. The DUAL table can be used for...
Semicolon or forward slash
Multiple rows
Testing functions
DD-MON-YY

25. When you INSERT new information into a table - it is followed with a _______ keyword that contains the information that is going to be added to the table.
99.99
VALUES
Crow's foot
Combine

26. To equijoin a table with another table involves matching the...
A correct statement
Common column value
MODIFY
Command returns the number of records returned by a query.

27. Subqueries can return...
Primary key/foreign key
Multiple rows
Running a script file
DICT_COLUMNS

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

29. What is the main difference between the NOT EXISTS and the NOT IN operators?
User-defined reports
Non-matching rows
The way NULL values are treated
Primary key/foreign key

30. _________________ relationships are always optional relationships.
CLOB
2000
X
Recursive

31. NUMBER functions can be...
Any
Nested
SET clause of an UPDATE statement
The way NULL values are treated

32. Self-joins are useful for performing comparisons and checking for ___________ in data.
Crow's foot
DESC dict;
Inconsistencies
Combine

33. LINESIZE
2000
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
USER_ - ALL_ - DBA_ - and V$
RENAME just_two_more_weeks TO finally_i

34. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
ALL_OBJECTS
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
MODIFY
Primary key/foreign key

35. ____________ are useful for analyzing and exploring the relationships within your data
SYSDATE
Has not changed the name
Self-joins
STORAGE

36. Explain what is found in the All Objects report folder in SQL Developer
All caps
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
MINUS
Precision

37. Joins involving multiple columns must always follow the ____________ relationship path.
Same
Asterisk
Primary key/foreign key
Combine

38. In SQL Developer field names and table names are shown in the color _______.
4
Three
Leading-edge
Black

39. To restart a sequence at a lower number - you can drop and ________ the sequence.
Re-create
Non-matching rows
MINUS
2000

40. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Location of the file
Optional
Retrieve
X

41. Users can only see changes made to a database once they have been...
Black
Any
Committed
SYSDATE

42. The ___________ operator tests whether a matching row cannot be found.
Crow's foot
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Location of the file
X

43. The ______ operator compares a list of values for equivalency
ANY
SUM
NATURAL JOIN
Running a script file

44. Dynamic views begin with a ___________.
V$
Correlated
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Multiple rows

45. The ______ depicts the M of a 1:M relationship.

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

46. When you have issued a DELETE command - you can still ________ those rows and restore the information.
X
Retrieve
ADD
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.

47. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
X
Running a script file
Location of the file
Crow's foot

48. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Both MIN and MAX
All caps
Correlated
RENAME just_two_more_weeks TO finally_i

49. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
X
Any
Rounded edges
Combine

50. Which format mask displays December 31st - 1999?
RUN SCRIPT
Month ddth - yyyy
X
Equijoin and identical column names