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. It is considered good form to put all SQL commands in...
Actual column
X
All caps
12:00:00 A.M

2. 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.
VALUES
The indexed column is part of an expression. In this case - consider creating a function-based index instead
DESC dict;
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.

3. The query retrieves more than 5 to 15 percent of the rows
DROP INDEX need_to_pass_i
KEEP INDEX
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Precision

4. A calculation with a null always yields...
STORAGE
Another null
Run scripts
SYSDATE

5. Which one of the following string functions tells you how many characters are in a string?
LENGTH
Committed
Optional
Re-create

6. ____________ are useful for analyzing and exploring the relationships within your data
CLOB
Self-joins
Three
Has not changed the name

7. PAGESIZE
UNION operator
RIGHT LEFT JOIN
Default is 14; when you use the default - you repeat the heading every 14 rows.
TO_CHAR function

8. Which format mask displays December 31st - 1999?
FALSE
SUM
Month ddth - yyyy
Orphan

9. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Retrieve
Equijoin and correlated subquery
Rounded edges
Extra spaces

10. What is Oracle's most popular index storage structure?
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.
Data
B-Tree Index
TO_CHAR function

11. Write the syntax to rename index just_two_more_weeks_i to finally_i
Script
Testing functions
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
RENAME just_two_more_weeks TO finally_i

12. Self-joins are useful for performing comparisons and checking for ___________ in data.
Committed
Null
Inconsistencies
INTERSECT

13. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Self-joins
Optional
VALUES

14. Checks whether any value in the list makes the condition true.
Any
**** 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
GROUP BY
The way NULL values are treated

15. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
MINUS
ON
Static or dynamic
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

16. You can pass parameters from the operating system when ___________ in SQl*Plus
Running a script file
Correlated
Self-joins
Structured Query Language

17. What are the main components of a ROWID?
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.
ANY
A correct statement
Semicolon or forward slash

18. Which operator is most commonly used?
UNION
+
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Groups

19. When you have issued a DELETE command - you can still ________ those rows and restore the information.
BETWEEN
DICT_COLUMNS
Retrieve
ANY

20. The same function can be used ______ in a SELECT statement.
VALUES clause of an INSERT statement
UTC
Recursive
Twice

21. The ___________ operator tests whether a matching row cannot be found.
MODIFY
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.
Structured Query Language
X

22. To restart a sequence at a lower number - you can drop and ________ the sequence.
F12
SYSDATE
@ or START
Re-create

23. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Semicolon or forward slash
DECODE
Both MIN and MAX
FALSE

24. To equijoin a table with another table involves matching the...
Logical unit of work
Equijoin and identical column names
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
Common column value

25. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
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
INNER JOIN
Precision
KEEP INDEX

26. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
4000
Non-matching rows
Static or dynamic
X

27. 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.
Self-joins
Equijoin
Has not changed the name
Combine

28. A transaction is a...
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
DICTIONARY
ON
Logical unit of work

29. LINESIZE
Running a script file
Both MIN and MAX
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Time zone

30. The COUNT function is useful for determining whether a table has...
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Details
Data
ANY

31. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
4000
All caps
Equijoin
Nested

32. What is the main difference between the NOT EXISTS and the NOT IN operators?
The way NULL values are treated
Another null
Both MIN and MAX
Inconsistencies

33. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
A correct statement
Script
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
999999.9999

34. Explain what a static data dictionary view is and how it is used
Leading-edge
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.
UTC
Data

35. What is the keyboard shortcut for the ROLLBACK command?
USER_ - ALL_ - DBA_ - and V$
Asterisk
F12
Both MIN and MAX

36. __________________ inserts the current date and time.
SYSDATE
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
ON
Script

37. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Location of the file
Equijoin
SYSDATE
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

38. The _______ function adds values together for a group of rows.
SUM
Primary key/foreign key
Committed
X

39. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
MODIFY
RENAME just_two_more_weeks TO finally_i
Scale
Data

40. The _______ operator determines common rows.
X
COMMIT
The indexed column is part of an expression. In this case - consider creating a function-based index instead
INTERSECT

41. You use the _________ keyword to add columns or constraints to a table.
Month ddth - yyyy
Testing functions
ADD
DESC dict;

42. The _____________ clause determines how rows are grouped.
KEEP INDEX
MINUS
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
GROUP BY

43. The TRUNC function on a date without a format model truncates the timestamp to...
12:00:00 A.M
Any
BETWEEN
DROP INDEX need_to_pass_i

44. Joins involving multiple columns must always follow the ____________ relationship path.
Columns
ROLLBACK
Primary key/foreign key
Testing functions

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

46. What is the first column of a composite index called?
VALUES clause of an INSERT statement
Leading-edge
ANSI
STORAGE

47. When column names on the tables are different - you use the ______ condition.
Scale
Structured Query Language
All caps
ON

48. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
KEEP INDEX
UTC
Non-matching rows

49. List the different types of data dictionary views.
SET clause of an UPDATE statement
The way NULL values are treated
Black
USER_ - ALL_ - DBA_ - and V$

50. The key that most database designers prefer and is chosen by the system is the _____________ key.
COMMIT
Semicolon or forward slash
Common column value
Surrogate