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. The ______ operator compares a list of values for equivalency
Leading-edge
ANY
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
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.

2. A calculation with a null always yields...
Multiple rows
ANSI
Nested
Another null

3. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Combine
All caps
Run scripts
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.

4. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Semicolon or forward slash
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Any
Business problems

5. Explain what a static data dictionary view is and how it is used
Rounded edges
Structured Query Language
+
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.

6. The ________ operator cannot compare against a list of values.
Null
When the table is small (unless your business rules require you too)
LIKE
Running a script file

7. What is Oracle's most popular index storage structure?
B-Tree Index
Surrogate
RENAME just_two_more_weeks TO finally_i
VALUES

8. What does SQL stand for?
INTERSECT
Structured Query Language
+
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

9. List 4 aggregate functions
DESC dict;
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
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
IN ANY ALL SOME

10. Which format mask displays December 31st - 1999?
999999.9999
Actual column
Month ddth - yyyy
Common column value

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

12. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
+
Precision
NATURAL JOIN

13. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
ROLLBACK
KEEP INDEX
SUM
999999.9999

14. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
RUN SCRIPT
MINUS
STORAGE

15. ____________ are useful for analyzing and exploring the relationships within your data
Structured Query Language
SELECT SYSDATE+1/96 - FROM dual
CLOB
Self-joins

16. 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.
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
Results
Retrieve
Has not changed the name

17. When you run a script in SQL*Plus you must give the...
Three
Same
Location of the file
Details

18. Explain in detail the impact of INDEXES and how they are used by Oracle.
Script
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
SYSDATE
Black

19. When you create a report in SQL Developer all the column names must match identically the names of the columns found in the tables you are referencing.
Retrieve
FALSE
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

20. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
Any
Data
2000
KEEP INDEX

21. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
999999.9999
Actual column
DICT_COLUMNS
Combine

22. 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.
DROP INDEX need_to_pass_i
RIGHT LEFT JOIN
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Default is 14; when you use the default - you repeat the heading every 14 rows.

23. FEEDBACK
Command returns the number of records returned by a query.
ANY
ON
Extra spaces

24. You use the _________ keyword to add columns or constraints to a table.
Recursive
ADD
Three
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.

25. __________________ inserts the current date and time.
Orphan
Running a script file
SYSDATE
The way NULL values are treated

26. List the different types of data dictionary views.
USER_ - ALL_ - DBA_ - and V$
Business problems
The indexed column is part of an expression. In this case - consider creating a function-based index instead
B-Tree Index

27. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
X
VALUES clause of an INSERT statement
DECODE
RIGHT LEFT JOIN

28. Write the syntax to rename index just_two_more_weeks_i to finally_i
Parent table
RENAME just_two_more_weeks TO finally_i
Recursive
Rounded edges

29. The term ____________ ___________ is used to express a join that satisfies the join condition.
F12
RUN SCRIPT
Surrogate
INNER JOIN

30. The purpose of the HAVING clause is to eliminate___________.
Groups
Default is 14; when you use the default - you repeat the heading every 14 rows.
Rounded edges
All

31. Match each of the SQL commands on the left with a verb from the list.
4000
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Black
Location of the file

32. NUMBER functions can be...
+
999999.9999
Surrogate
Nested

33. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
All caps
Surrogate
INNER JOIN
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

34. Subqueries can return...
Multiple rows
NATURAL JOIN
IN ANY ALL SOME
Business problems

35. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
Any
Primary key/foreign key
Static or dynamic
IN ANY ALL SOME

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

37. Write a statement that drops the index need_to_pass_i
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
UNION operator
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

38. What is the maximum value for a column defined as NUMBER(4 -2)?
99.99
Crow's foot
The way NULL values are treated
DICTIONARY

39. The RTRIM function is useful for eliminating ________ in a string.
99.99
Extra spaces
The way NULL values are treated
Has not changed the name

40. The ALTER SESSION statement can change the session's...
Hide data
4
Data
Time zone

41. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Logical unit of work
Results
DESC dict;
Actual column

42. _________________ relationships are always optional relationships.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Time zone
Recursive
A correct statement

43. You use the __________ keyword to change characteristics of a column.
ADD
@ or START
DROP INDEX need_to_pass_i
MODIFY

44. The _______ operator determines common rows.
ROLLBACK
Another null
Asterisk
INTERSECT

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

46. The _______ function lets you perform if-then-else functionality with the SQL language.
DECODE
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
Leading-edge
TO_CHAR function

47. Which one of the following string functions tells you how many characters are in a string?
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.
LENGTH
DD-MON-YY
Non-matching rows

48. What is the most common DATE format found in SQL?
Columns
DD-MON-YY
X
Has not changed the name

49. The ____________ command makes changes permanent.
Has not changed the name
All caps
COMMIT
ALL_OBJECTS

50. SELECT LIST of a subquery in an INSERT statement
CLOB
ADD_MONTHS function
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.
SET clause of an UPDATE statement