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 _______ function adds values together for a group of rows.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
INNER JOIN
SUM
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

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

3. You can use the ________________ to subtract months from a given date.
@ or START
MODIFY
Has not changed the name
ADD_MONTHS function

4. The time zone displacement value indicates the time difference from _______.
Running a script file
UNION operator
UTC
GROUP BY

5. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
DICT_COLUMNS
COMMIT
Groups
Has not changed the name

6. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
FALSE
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
+
Run scripts

7. The COUNT function uses the __________ wildcard.
Results
Hide data
Recursive
Asterisk

8. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
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.
Crow's foot
RUN SCRIPT
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

9. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Combine
ON
99.99
Orphan

10. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
B-Tree Index
Extra spaces
FALSE
Export Data

11. Comparison operators can compare multiple _________________.
Any
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Columns
The way NULL values are treated

12. Which format mask displays December 31st - 1999?
CLOB
Month ddth - yyyy
Data
Business problems

13. When is it not a good idea to use an index?
Run scripts
2000
When the table is small (unless your business rules require you too)
V$

14. 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.
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
FALSE
SELECT SYSDATE+1/96 - FROM dual

15. Checks whether any value in the list makes the condition true.
MINUS
Running a script file
UNION
Any

16. You use the _________ keyword to add columns or constraints to a table.
Structured Query Language
Implicit conversion
ADD
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

17. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
FALSE
VALUES
ALL_OBJECTS
DECODE

18. The same function can be used ______ in a SELECT statement.
Twice
UTC
Run scripts
99.99

19. Which one of the following string functions tells you how many characters are in a string?
Black
RUN SCRIPT
LENGTH
Surrogate key

20. The ______ operator compares a list of values for equivalency
**** 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
Equijoin and correlated subquery
ANY
Same

21. Returns rows if the condition is true for all the values in the list
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Primary key/foreign key
All
F12

22. 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.
Has not changed the name
INTERSECT
Scalar
Rounded edges

23. The ROWID pseudocolumn is not an ________- but it acts like one.
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.
Actual column
ALL_OBJECTS
BETWEEN

24. What is Oracle's most popular index storage structure?
RUN SCRIPT
INNER JOIN
B-Tree Index
Leading-edge

25. _________________ relationships are always optional relationships.
X
User-defined reports
Recursive
BETWEEN

26. The ALTER SESSION statement can change the session's...
STORAGE
Another null
Time zone
When the table is small (unless your business rules require you too)

27. What is the first column of a composite index called?
Any
Leading-edge
ADD
Both MIN and MAX

28. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Crow's foot
X
BETWEEN
Surrogate key

29. UNION ALL does not sort - therefore it is less time consuming to use than the...
UNION operator
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
V$
MINUS

30. Users can only see changes made to a database once they have been...
Location of the file
Committed
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
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

31. A _________ subquery returns a single column with a single row
X
Scalar
Same
INNER JOIN

32. SELECT * FROM instructor ORDER BY phone
SELECT DISTINCT menu FROM restaurant
A correct statement
Equijoin and correlated subquery
DESC dict;

33. FEEDBACK
Logical unit of work
Data
Command returns the number of records returned by a query.
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

34. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
Rounded edges
Extra spaces
X
Hide data

35. List the different types of data dictionary views.
Orphan
The indexed column is part of an expression. In this case - consider creating a function-based index instead
USER_ - ALL_ - DBA_ - and V$
Logical unit of work

36. The _____________ clause determines how rows are grouped.
GROUP BY
Default is 14; when you use the default - you repeat the heading every 14 rows.
INNER JOIN
User-defined reports

37. How many characters can you have using the VARCHAR data type?
COMMIT
4000
V$
Fractional seconds

38. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
All
ROLLBACK
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
Time zone

39. What does SQL stand for?
ANY
Logical unit of work
Structured Query Language
99.99

40. SELECT LIST of a subquery in an INSERT statement
Twice
4
Multiple rows
SET clause of an UPDATE statement

41. Oracle uses the outer join operator to indicate that nulls are shown for...
Nested
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
Non-matching rows
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

42. NUMBER functions can be...
Primary key/foreign key
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.
SELECT DISTINCT menu FROM restaurant
Nested

43. A calculation with a null always yields...
Extra spaces
12:00:00 A.M
Another null
V$

44. What is the name of the key automatically generated from a sequence?
Common column value
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
Surrogate key
Twice

45. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
SELECT DISTINCT menu FROM restaurant
Command returns the number of records returned by a query.
Combine
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

46. The ________ operator cannot compare against a list of values.
SELECT DISTINCT menu FROM restaurant
DESC dict;
LIKE
Results

47. The purpose of the HAVING clause is to eliminate___________.
Groups
Scale
SELECT SYSDATE+1/96 - FROM dual
Correlated

48. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
TO_CHAR function
UTC
USER_ - ALL_ - DBA_ - and V$

49. 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?
Actual column
Groups
DICTIONARY
Committed

50. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Structured Query Language
Script
Month ddth - yyyy
Optional