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. Explain how Substitution Variables are used when writing scripts.
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
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 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.
X

2. The RTRIM function is useful for eliminating ________ in a string.
2000
Equijoin and identical column names
Extra spaces
ANSI

3. When column names on the tables are different - you use the ______ condition.
ON
Implicit conversion
Nested
All caps

4. The _______ function lets you perform if-then-else functionality with the SQL language.
Re-create
Running a script file
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
DECODE

5. The key that most database designers prefer and is chosen by the system is the _____________ key.
STORAGE
Twice
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
Surrogate

6. SELECT * FROM instructor ORDER BY phone
DD-MON-YY
A correct statement
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
SET clause of an UPDATE statement

7. List the steps performed by a correlated subquery.
**** 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
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
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
VALUES

8. When is it not a good idea to use an index?
When the table is small (unless your business rules require you too)
X
IN ANY ALL SOME
VALUES clause of an INSERT statement

9. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
Actual column
Asterisk
User-defined reports
IN ANY ALL SOME

10. A transaction is a...
INTERSECT
X
Logical unit of work
SELECT SYSDATE+1/96 - FROM dual

11. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Committed
Location of the file
BETWEEN
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

12. Checks whether any value in the list makes the condition true.
SELECT DISTINCT menu FROM restaurant
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
DD-MON-YY
Any

13. Write the syntax to rename index just_two_more_weeks_i to finally_i
Location of the file
**** 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
Any
RENAME just_two_more_weeks TO finally_i

14. Self-joins are useful for performing comparisons and checking for ___________ in data.
Inconsistencies
FALSE
Details
Static or dynamic

15. How many characters can you have using the VARCHAR data type?
DICT_COLUMNS
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
4000
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

16. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
LENGTH
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
Rounded edges
STORAGE

17. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Month ddth - yyyy
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.
Nested
Equijoin and correlated subquery

18. The TRUNC function on a date without a format model truncates the timestamp to...
Non-matching rows
ON
IN ANY ALL SOME
12:00:00 A.M

19. The DUAL table can be used for...
Testing functions
Fractional seconds
X
FALSE

20. How many characters can be stored in the CHAR column/field?
Structured Query Language
Semicolon or forward slash
2000
Re-create

21. The COUNT function uses the __________ wildcard.
Multiple rows
MODIFY
Asterisk
NATURAL JOIN

22. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
Logical unit of work
4
RENAME just_two_more_weeks TO finally_i
A correct statement

23. The same function can be used ______ in a SELECT statement.
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Month ddth - yyyy
Twice
B-Tree Index

24. The _______ operator determines common rows.
INTERSECT
Logical unit of work
V$
Equijoin and correlated subquery

25. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
Extra spaces
VALUES
X
Data

26. Subqueries can return...
ANSI
USER_ - ALL_ - DBA_ - and V$
Multiple rows
INTERSECT

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

28. What is Oracle's most popular index storage structure?
DESC dict;
DROP INDEX need_to_pass_i
B-Tree Index
ADD_MONTHS function

29. List the reasons that a subquery could not be unnested.
UTC
Parent table
X
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.

30. The time zone displacement value indicates the time difference from _______.
12:00:00 A.M
VALUES clause of an INSERT statement
UTC
When the table is small (unless your business rules require you too)

31. In a view you can give a column a different name than it has in the...
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
Mathematical equations
Parent table
Run scripts

32. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
The way NULL values are treated
Multiple rows
ADD_MONTHS function

33. Write a statement that drops the index need_to_pass_i
Running a script file
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
All caps
DROP INDEX need_to_pass_i

34. _________ rows are not allowed in the relational model.
ANY
Orphan
Self-joins
DICTIONARY

35. What are the main components of a ROWID?
2000
DROP INDEX need_to_pass_i
Data
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.

36. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
LENGTH
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.
Export Data
Same

37. It is considered good form to put all SQL commands in...
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Export Data
All caps
LIKE

38. The ___________ operator tests whether a matching row cannot be found.
Script
Semicolon or forward slash
X
Three

39. What is the name of the key automatically generated from a sequence?
Three
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Surrogate key
SELECT DISTINCT menu FROM restaurant

40. Explain what a static data dictionary view is and how it is used
Leading-edge
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
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.
INNER JOIN

41. When writing an ANSI outer join - the keywords _________ OUTER and _________ OUTER are added to the __________ keyword to indicate the rows from which table to use.
4000
RIGHT LEFT JOIN
Export Data
Both MIN and MAX

42. ____________ are useful for analyzing and exploring the relationships within your data
Crow's foot
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Running a script file
Self-joins

43. The ROWID pseudocolumn is not an ________- but it acts like one.
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Actual column
Rounded edges
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

44. It is always good practice to include the old values in the WHERE clause of UPDATE to ensure that another session or user _________________ in the meantime.
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.
Equijoin and correlated subquery
Location of the file
Has not changed the name

45. Returns rows if the condition is true for all the values in the list
All
Mathematical equations
SYSDATE
Details

46. Which format mask displays December 31st - 1999?
Extra spaces
Business problems
Month ddth - yyyy
VALUES

47. You use the _________ keyword to add columns or constraints to a table.
Location of the file
ADD
Orphan
Business problems

48. List the different types of data dictionary views.
4000
USER_ - ALL_ - DBA_ - and V$
ADD
Run scripts

49. _________________ relationships are always optional relationships.
When the table is small (unless your business rules require you too)
Recursive
Hide data
SET clause of an UPDATE statement

50. What is the most common DATE format found in SQL?
DD-MON-YY
STORAGE
DICTIONARY
Equijoin and correlated subquery