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 _____________ clause determines how rows are grouped.
USER_ - ALL_ - DBA_ - and V$
STORAGE
GROUP BY
RUN SCRIPT

2. List the reasons that a subquery could not be unnested.
X
Crow's foot
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
Testing functions

3. List the steps performed by a correlated subquery.
IN ANY ALL SOME
**** 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
Running a script file
Surrogate

4. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Equijoin and correlated subquery
Leading-edge
UTC
X

5. The time zone displacement value indicates the time difference from _______.
UTC
Same
12:00:00 A.M
Inconsistencies

6. Which format mask displays December 31st - 1999?
Month ddth - yyyy
The indexed column is part of an expression. In this case - consider creating a function-based index instead
99.99
Twice

7. Match each of the SQL commands on the left with a verb from the list.
Another null
The way NULL values are treated
Fractional seconds
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

8. The key that most database designers prefer and is chosen by the system is the _____________ key.
Surrogate
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Both MIN and MAX
Mathematical equations

9. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
Crow's foot
ADD_MONTHS function
Nested
Scale

10. _________________ relationships are always optional relationships.
V$
Surrogate key
Any
Recursive

11. The __________ operator is inclusive of the two values specified.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Equijoin
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.
BETWEEN

12. FEEDBACK
Null
Another null
Non-matching rows
Command returns the number of records returned by a query.

13. The ROWID pseudocolumn is not an ________- but it acts like one.
Actual column
X
Leading-edge
DICTIONARY

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

15. NUMBER functions can be...
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Nested
@ or START
The indexed column is part of an expression. In this case - consider creating a function-based index instead

16. What do views do that makes them so useful for security?
Hide data
X
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
The way NULL values are treated

17. The DUAL table can be used for...
Testing functions
Recursive
Equijoin and identical column names
Precision

18. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
The way NULL values are treated
DICT_COLUMNS
CLOB
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

19. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
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
Both MIN and MAX
V$
X

20. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
Implicit conversion
ROLLBACK
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
UNION operator

21. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
Static or dynamic
RUN SCRIPT
STORAGE
RIGHT LEFT JOIN

22. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
VALUES clause of an INSERT statement
VALUES
B-Tree Index
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.

23. Explain in detail the impact of INDEXES and how they are used by Oracle.
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
Script
User-defined reports
CLOB

24. The _______ operator determines common rows.
Self-joins
User-defined reports
Mathematical equations
INTERSECT

25. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
NATURAL JOIN
2000
Hide data
Business problems

26. What is Oracle's most popular index storage structure?
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Results
B-Tree Index
SYSDATE

27. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Three
4000
Results
Asterisk

28. SQL*Plus is used more than SQL Developer when you need to...
Run scripts
Equijoin and correlated subquery
99.99
Logical unit of work

29. The COUNT function is useful for determining whether a table has...
Data
Any
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.
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

30. ____________ are useful for analyzing and exploring the relationships within your data
Common column value
Self-joins
Static or dynamic
ANY

31. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
UNION operator
Fractional seconds
TO_CHAR function
FALSE

32. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
Rounded edges
**** 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
SELECT DISTINCT menu FROM restaurant
NATURAL JOIN

33. List 4 aggregate functions
X
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Static or dynamic
Logical unit of work

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

35. 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?
Fractional seconds
RUN SCRIPT
DICTIONARY
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.

36. The ___________ operator tests whether a matching row cannot be found.
Scalar
X
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
DD-MON-YY

37. What is the tab in SQL Developer where data dictionary view information can be found?
Primary key/foreign key
Export Data
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Details

38. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
IN ANY ALL SOME
VALUES clause of an INSERT statement
User-defined reports
Surrogate

39. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
Running a script file
LIKE
X
VALUES clause of an INSERT statement

40. Users can only see changes made to a database once they have been...
Committed
KEEP INDEX
LENGTH
ADD

41. SELECT LIST of a subquery in an INSERT statement
SET clause of an UPDATE statement
User-defined reports
DESC dict;
Logical unit of work

42. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
KEEP INDEX
TO_CHAR function
DICT_COLUMNS
X

43. The ________ operator cannot compare against a list of values.
INNER JOIN
Logical unit of work
Default is 14; when you use the default - you repeat the heading every 14 rows.
LIKE

44. Checks whether any value in the list makes the condition true.
SET clause of an UPDATE statement
The way NULL values are treated
Any
Structured Query Language

45. Explain what a static data dictionary view is and how it is used
TO_CHAR function
Orphan
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.
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.

46. Oracle uses the outer join operator to indicate that nulls are shown for...
LIKE
Groups
Static or dynamic
Non-matching rows

47. What does the following function return? SELECT LENGTH(NULL) - FROM dual
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Equijoin and correlated subquery
Month ddth - yyyy
Null

48. 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.
Primary key/foreign key
ROLLBACK
RIGHT LEFT JOIN
MINUS

49. Explain how Substitution Variables are used when writing scripts.
DICT_COLUMNS
Hide data
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.
SUM

50. Queries that are found in parentheses are done first - just as in the order of operations when doing...
ON
Location of the file
Scalar
Mathematical equations