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 data NUMBER(10 -4) there will be _______ digits following the decimal point.
Has not changed the name
4
+
Actual column

2. The purpose of the HAVING clause is to eliminate___________.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Nested
ANSI
Groups

3. PAGESIZE
Default is 14; when you use the default - you repeat the heading every 14 rows.
Equijoin and correlated subquery
Combine
DICT_COLUMNS

4. Checks whether any value in the list makes the condition true.
INTERSECT
Precision
Orphan
Any

5. Dynamic views begin with a ___________.
VALUES clause of an INSERT statement
Surrogate
V$
BETWEEN

6. The _______ operator determines common rows.
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.
Columns
STORAGE
INTERSECT

7. When column names on the tables are different - you use the ______ condition.
4
Hide data
DICTIONARY
ON

8. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
RENAME just_two_more_weeks TO finally_i
Export Data
Has not changed the name
DESC dict;

9. To equijoin a table with another table involves matching the...
ANY
All caps
Common column value
Default is 14; when you use the default - you repeat the heading every 14 rows.

10. How many characters can be stored in the CHAR column/field?
2000
Running a script file
ALL_OBJECTS
Retrieve

11. The ALTER SESSION statement can change the session's...
Time zone
Hide data
RUN SCRIPT
Testing functions

12. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
VALUES
B-Tree Index
Fractional seconds
DICT_COLUMNS

13. To restart a sequence at a lower number - you can drop and ________ the sequence.
Rounded edges
Re-create
Precision
Another null

14. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Correlated
Business problems
X
Multiple rows

15. To join four tables - you must have at least _____ join conditions.
SELECT SYSDATE+1/96 - FROM dual
RIGHT LEFT JOIN
Three
ADD

16. List the reasons that a subquery could not be unnested.
UNION
SYSDATE
X
INTERSECT

17. A calculation with a null always yields...
Another null
Retrieve
BETWEEN
Any

18. FEEDBACK
Command returns the number of records returned by a query.
Business problems
ROLLBACK
Twice

19. Users can only see changes made to a database once they have been...
Committed
999999.9999
MINUS
B-Tree Index

20. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Hide data
Has not changed the name
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
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

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

22. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
DESC dict;
VALUES clause of an INSERT statement
ANSI
ADD

23. SELECT * FROM instructor ORDER BY phone
Equijoin
A correct statement
Actual column
Equijoin and correlated subquery

24. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
NATURAL JOIN
Any
ADD_MONTHS function

25. What is the most common DATE format found in SQL?
FALSE
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
+
DD-MON-YY

26. When is it not a good idea to use an index?
4000
When the table is small (unless your business rules require you too)
@ or START
MINUS

27. What is the data type you can use if you need an unlimited number of textual characters?
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.
CLOB
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.
Equijoin and identical column names

28. _________ rows are not allowed in the relational model.
Orphan
99.99
GROUP BY
ADD_MONTHS function

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

30. Explain the steps needed to run a script in SQL Developer
Inconsistencies
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
Nested
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.

31. What does SQL stand for?
Structured Query Language
Multiple rows
Mathematical equations
UTC

32. What are the main components of a ROWID?
Another null
Actual column
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.
Surrogate key

33. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
SUM
VALUES clause of an INSERT statement
USER_ - ALL_ - DBA_ - and V$
STORAGE

34. List 4 aggregate functions
Another null
VALUES
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
12:00:00 A.M

35. Oracle uses the outer join operator to indicate that nulls are shown for...
Non-matching rows
The indexed column is part of an expression. In this case - consider creating a function-based index instead
COMMIT
X

36. The _____________ clause determines how rows are grouped.
V$
GROUP BY
Time zone
BETWEEN

37. The __________ operator is inclusive of the two values specified.
SYSDATE
BETWEEN
Export Data
IN ANY ALL SOME

38. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
SYSDATE
User-defined reports
RENAME just_two_more_weeks TO finally_i
Null

39. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
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
Self-joins
Details
FALSE

40. The ________ operator cannot compare against a list of values.
DROP INDEX need_to_pass_i
SELECT SYSDATE+1/96 - FROM dual
4
LIKE

41. What is the main difference between the NOT EXISTS and the NOT IN operators?
Recursive
The way NULL values are treated
Run scripts
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

42. __________________ inserts the current date and time.
SYSDATE
A correct statement
X
Semicolon or forward slash

43. The query retrieves more than 5 to 15 percent of the rows
The indexed column is part of an expression. In this case - consider creating a function-based index instead
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.
Scale
Testing functions

44. Explicit data type conversion is preferable to Oracle's...
Nested
X
Data
Implicit conversion

45. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Correlated
Re-create
Rounded edges
ALL_OBJECTS

46. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Running a script file
Scalar
Black
BETWEEN

47. The ROWID pseudocolumn is not an ________- but it acts like one.
Actual column
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
X
Null

48. 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.
CLOB
ALL_OBJECTS
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

49. The COUNT function is useful for determining whether a table has...
Null
Running a script file
Data
Common column value

50. The term ____________ ___________ is used to express a join that satisfies the join condition.
INNER JOIN
Re-create
Any
KEEP INDEX