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. What is the main difference between the NOT EXISTS and the NOT IN operators?
STORAGE
The way NULL values are treated
VALUES clause of an INSERT statement
IN ANY ALL SOME

2. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
IN ANY ALL SOME
@ or START
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
A correct statement

3. List the different types of data dictionary views.
KEEP INDEX
SUM
USER_ - ALL_ - DBA_ - and V$
Scale

4. List the steps performed by a correlated subquery.
Running a script file
**** 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
999999.9999
B-Tree Index

5. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
Surrogate key
Surrogate
Nested
VALUES clause of an INSERT statement

6. The COUNT function is useful for determining whether a table has...
Data
Equijoin
Surrogate key
Black

7. 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
BETWEEN
Equijoin and identical column names
Crow's foot

8. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
Surrogate
All caps
Month ddth - yyyy

9. Checks whether any value in the list makes the condition true.
COMMIT
Running a script file
INTERSECT
Any

10. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Null
Equijoin
NATURAL JOIN
COMMIT

11. PAGESIZE
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Default is 14; when you use the default - you repeat the heading every 14 rows.
ANSI
LENGTH

12. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Command returns the number of records returned by a query.
SELECT DISTINCT menu FROM restaurant
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

13. Converting a text literal to DATE format requires the use of the...
BETWEEN
The way NULL values are treated
BETWEEN
TO_CHAR function

14. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Results
Another null
Scalar

15. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
Same
Committed
STORAGE
Multiple rows

16. The key that most database designers prefer and is chosen by the system is the _____________ key.
DICTIONARY
Null
2000
Surrogate

17. How many characters can be stored in the CHAR column/field?
DICT_COLUMNS
User-defined reports
Columns
2000

18. A join across three tables can be expressed with the ________ join syntax.
ANSI
IN ANY ALL SOME
Location of the file
Fractional seconds

19. Match each of the SQL commands on the left with a verb from the list.
ADD
Black
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Recursive

20. What does SQL stand for?
Testing functions
Data
Structured Query Language
F12

21. Write a statement that drops the index need_to_pass_i
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.
12:00:00 A.M
DROP INDEX need_to_pass_i
Semicolon or forward slash

22. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
LENGTH
Self-joins
X
DECODE

23. Which format mask displays December 31st - 1999?
Any
Export Data
DROP INDEX need_to_pass_i
Month ddth - yyyy

24. Returns rows if the condition is true for all the values in the list
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.
BETWEEN
All
Re-create

25. The focus of the logical data model is _____________
Location of the file
X
Results
Business problems

26. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
UNION
MINUS
ROLLBACK
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.

27. FEEDBACK
STORAGE
Command returns the number of records returned by a query.
Same
FALSE

28. Oracle uses the outer join operator to indicate that nulls are shown for...
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.
X
Non-matching rows
Rounded edges

29. The ROWID pseudocolumn is not an ________- but it acts like one.
All
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.
Actual column
Non-matching rows

30. The purpose of the HAVING clause is to eliminate___________.
Groups
Combine
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Export Data

31. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
Actual column
DICT_COLUMNS
Equijoin and identical column names
All caps

32. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Script
B-Tree Index
RIGHT LEFT JOIN
ON

33. The time zone displacement value indicates the time difference from _______.
ADD_MONTHS function
UTC
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
Equijoin

34. UNION ALL does not sort - therefore it is less time consuming to use than the...
UNION operator
All
MODIFY
ROLLBACK

35. A calculation with a null always yields...
Month ddth - yyyy
Asterisk
Another null
All caps

36. What are the main components of a ROWID?
FALSE
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
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.
VALUES

37. 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.
IN ANY ALL SOME
Results
Self-joins
FALSE

38. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
VALUES clause of an INSERT statement
RIGHT LEFT JOIN
Same
Details

39. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
UTC
CLOB
Three
Semicolon or forward slash

40. The ________ operator cannot compare against a list of values.
B-Tree Index
LIKE
Same
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

41. The using clause of the ANSI join syntax always assumes an...
Black
SET clause of an UPDATE statement
Another null
Equijoin and identical column names

42. _________________ relationships are always optional relationships.
Actual column
Recursive
ALL_OBJECTS
UNION operator

43. The ALTER SESSION statement can change the session's...
Run scripts
Orphan
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
Time zone

44. __________________ inserts the current date and time.
SYSDATE
Implicit conversion
Hide data
B-Tree Index

45. When column names on the tables are different - you use the ______ condition.
Time zone
ON
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.
Extra spaces

46. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
A correct statement
Both MIN and MAX
Actual column
MODIFY

47. The ___________ operator tests whether a matching row cannot be found.
Scale
VALUES clause of an INSERT statement
X
Self-joins

48. The COUNT function uses the __________ wildcard.
SET clause of an UPDATE statement
Asterisk
Business problems
Time zone

49. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
Testing functions
F12
BETWEEN

50. List the reasons that a subquery could not be unnested.
SYSDATE
X
Scalar
Recursive