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 is inclusive of the two values specified.
BETWEEN
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
X
DICTIONARY

2. What are the main components of a ROWID?
Recursive
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.
999999.9999
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

3. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Mathematical equations
MODIFY
Data
Equijoin and identical column names

4. Write the syntax to rename index just_two_more_weeks_i to finally_i
BETWEEN
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
RENAME just_two_more_weeks TO finally_i
Implicit conversion

5. The _______ function adds values together for a group of rows.
Parent table
FALSE
SUM
Scalar

6. The ___________ operator tests whether a matching row cannot be found.
DD-MON-YY
X
Columns
Fractional seconds

7. UNION ALL does not sort - therefore it is less time consuming to use than the...
LIKE
Null
UNION operator
Recursive

8. Write a solution that adds 20 minutes to a given date.
SELECT SYSDATE+1/96 - FROM dual
Equijoin and identical column names
Non-matching rows
+

9. Another name for a data dictionary view is __________________ or _________________.
RIGHT LEFT JOIN
Recursive
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Static or dynamic

10. The ALTER SESSION statement can change the session's...
Time zone
Structured Query Language
999999.9999
V$

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

12. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
Run scripts
2000
Correlated
User-defined reports

13. The ____________ command makes changes permanent.
Nested
COMMIT
SELECT SYSDATE+1/96 - FROM dual
NATURAL JOIN

14. What does the following function return? SELECT LENGTH(NULL) - FROM dual
X
UTC
@ or START
Null

15. The query retrieves more than 5 to 15 percent of the rows
Location of the file
Fractional seconds
COMMIT
The indexed column is part of an expression. In this case - consider creating a function-based index instead

16. Explain the steps needed to run a script in SQL Developer
LIKE
99.99
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.
Rounded edges

17. 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.
Black
Twice
VALUES
Script

18. Dynamic views begin with a ___________.
Common column value
ON
V$
Equijoin and identical column names

19. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
**** 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
X
Both MIN and MAX
DROP INDEX need_to_pass_i

20. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
Semicolon or forward slash
ALL_OBJECTS
SET clause of an UPDATE statement
DD-MON-YY

21. What is the name of the view where you can find the collection of static and data dictionary tables and views - along with their descriptions?
Has not changed the name
2000
DICTIONARY
DROP INDEX need_to_pass_i

22. To join four tables - you must have at least _____ join conditions.
X
2000
Rounded edges
Three

23. What is the most common DATE format found in SQL?
Running a script file
Hide data
DD-MON-YY
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

24. ____________ are useful for analyzing and exploring the relationships within your data
Semicolon or forward slash
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.
Self-joins
Results

25. A _________ subquery returns a single column with a single row
Scalar
Location of the file
Mathematical equations
Common column value

26. Explicit data type conversion is preferable to Oracle's...
Implicit conversion
Business problems
Nested
Default is 14; when you use the default - you repeat the heading every 14 rows.

27. Explain what is found in the All Objects report folder in SQL Developer
Structured Query Language
Re-create
ADD_MONTHS function
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

28. LINESIZE
ANSI
GROUP BY
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
VALUES clause of an INSERT statement

29. What is the main difference between the NOT EXISTS and the NOT IN operators?
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
SUM
ADD_MONTHS function
The way NULL values are treated

30. When is it not a good idea to use an index?
Optional
Twice
INTERSECT
When the table is small (unless your business rules require you too)

31. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Crow's foot
Multiple rows
Equijoin and correlated subquery
Correlated

32. The _____________ clause determines how rows are grouped.
User-defined reports
GROUP BY
Surrogate key
DROP INDEX need_to_pass_i

33. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
DESC dict;
STORAGE
BETWEEN
X

34. Comparison operators can compare multiple _________________.
ANY
FALSE
Details
Columns

35. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
+
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
LENGTH
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

36. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
DECODE
Optional
4
Non-matching rows

37. SELECT LIST of a subquery in an INSERT statement
Common column value
SET clause of an UPDATE statement
RENAME just_two_more_weeks TO finally_i
IN ANY ALL SOME

38. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
NATURAL JOIN
SET clause of an UPDATE statement
Nested
LIKE

39. List the steps performed by a correlated subquery.
VALUES
X
**** 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
Structured Query Language

40. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
DECODE
Data
999999.9999
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

41. Explain what a static data dictionary view is and how it is used
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Groups
ON
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.

42. 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.
FALSE
V$
Time zone
Combine

43. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
2000
Correlated
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
Crow's foot

44. Which operator is most commonly used?
UNION
Equijoin and identical column names
Business problems
999999.9999

45. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
KEEP INDEX
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
STORAGE
SELECT DISTINCT menu FROM restaurant

46. A join across three tables can be expressed with the ________ join syntax.
ANSI
A correct statement
CLOB
ON

47. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
Fractional seconds
SELECT SYSDATE+1/96 - FROM dual
Static or dynamic
Precision

48. The ________ operator cannot compare against a list of values.
Rounded edges
LIKE
Any
Black

49. Joins involving multiple columns must always follow the ____________ relationship path.
Common column value
Columns
Primary key/foreign key
DICT_COLUMNS

50. List 4 aggregate functions
Time zone
DESC dict;
DECODE
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)