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. Explain what is found in the All Objects report folder in SQL Developer
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
999999.9999
Data
ANSI

2. What is the main difference between the NOT EXISTS and the NOT IN operators?
Orphan
@ or START
The way NULL values are treated
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

3. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
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
LENGTH
BETWEEN
ALL_OBJECTS

4. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Mathematical equations
12:00:00 A.M
Twice
UTC

5. What is the first column of a composite index called?
Hide data
Leading-edge
Equijoin and identical column names
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

6. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
Surrogate key
X
UNION
Fractional seconds

7. Another name for a data dictionary view is __________________ or _________________.
Static or dynamic
SELECT DISTINCT menu FROM restaurant
GROUP BY
Inconsistencies

8. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Correlated
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Rounded edges
Leading-edge

9. It is considered good form to put all SQL commands in...
All caps
SET clause of an UPDATE statement
Any
Time zone

10. SELECT * FROM instructor ORDER BY phone
Both MIN and MAX
A correct statement
GROUP BY
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

11. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
VALUES clause of an INSERT statement
SELECT DISTINCT menu FROM restaurant
12:00:00 A.M
Run scripts

12. The COUNT function uses the __________ wildcard.
12:00:00 A.M
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.
B-Tree Index
Asterisk

13. Write the syntax to rename index just_two_more_weeks_i to finally_i
Export Data
RENAME just_two_more_weeks TO finally_i
Time zone
F12

14. To join four tables - you must have at least _____ join conditions.
Three
X
Fractional seconds
Black

15. A _________ subquery returns a single column with a single row
DD-MON-YY
Scalar
Static or dynamic
Extra spaces

16. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
LENGTH
UNION operator
999999.9999

17. The ___________ operator tests whether a matching row cannot be found.
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
Both MIN and MAX
X

18. SQL*Plus is used more than SQL Developer when you need to...
User-defined reports
Run scripts
NATURAL JOIN
ROLLBACK

19. ____________ are useful for analyzing and exploring the relationships within your data
Correlated
4000
Self-joins
KEEP INDEX

20. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Orphan
999999.9999
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.
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

21. What does SQL stand for?
The indexed column is part of an expression. In this case - consider creating a function-based index instead
4
LIKE
Structured Query Language

22. To equijoin a table with another table involves matching the...
X
Results
Common column value
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. You use the __________ keyword to change characteristics of a column.
MODIFY
All
Primary key/foreign key
Combine

24. Users can only see changes made to a database once they have been...
Committed
Asterisk
Recursive
Data

25. What is the data type you can use if you need an unlimited number of textual characters?
CLOB
Testing functions
MINUS
Retrieve

26. The __________ operator is inclusive of the two values specified.
LENGTH
DICT_COLUMNS
BETWEEN
Optional

27. List the different types of data dictionary views.
GROUP BY
Precision
USER_ - ALL_ - DBA_ - and V$
Command returns the number of records returned by a query.

28. The same function can be used ______ in a SELECT statement.
INTERSECT
Twice
DECODE
FALSE

29. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Same
Command returns the number of records returned by a query.
Structured Query Language
Recursive

30. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
Scale
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
4000
Primary key/foreign key

31. In SQL Developer field names and table names are shown in the color _______.
VALUES clause of an INSERT statement
Twice
Black
Command returns the number of records returned by a query.

32. Match each of the SQL commands on the left with a verb from the list.
RIGHT LEFT JOIN
Committed
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
NATURAL JOIN

33. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
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
Combine
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
99.99

34. What is the maximum value for a column defined as NUMBER(4 -2)?
X
Any
99.99
Location of the file

35. When column names on the tables are different - you use the ______ condition.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
The way NULL values are treated
ON
Extra spaces

36. __________________ inserts the current date and time.
Multiple rows
Same
MINUS
SYSDATE

37. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
BETWEEN
Results
Correlated
Semicolon or forward slash

38. The _______ function adds values together for a group of 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
Has not changed the name
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

39. A transaction is a...
Hide data
ALL_OBJECTS
Logical unit of work
Columns

40. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
Has not changed the name
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
UNION operator
IN ANY ALL SOME

41. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
ANY
Running a script file
KEEP INDEX
STORAGE

42. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
999999.9999
Any
Three
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

43. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
RIGHT LEFT JOIN
VALUES clause of an INSERT statement
Both MIN and MAX
Retrieve

44. The _______ operator determines common rows.
The way NULL values are treated
ALL_OBJECTS
Primary key/foreign key
INTERSECT

45. What is the name of the key automatically generated from a sequence?
Surrogate key
The way NULL values are treated
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.

46. Oracle uses the outer join operator to indicate that nulls are shown for...
Twice
Hide data
Non-matching rows
Orphan

47. 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
Three
X
4000

48. The ____________ command makes changes permanent.
Retrieve
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
DD-MON-YY
COMMIT

49. Returns rows if the condition is true for all the values in the list
User-defined reports
Retrieve
All
USER_ - ALL_ - DBA_ - and V$

50. The _______ function lets you perform if-then-else functionality with the SQL language.
Month ddth - yyyy
2000
DECODE
Mathematical equations