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. Subqueries can return...
Null
VALUES
Hide data
Multiple rows

2. SELECT * FROM instructor ORDER BY phone
V$
A correct statement
Any
X

3. How many characters can you have using the VARCHAR data type?
IN ANY ALL SOME
4000
Testing functions
Implicit conversion

4. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
VALUES
All
4
X

5. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
MINUS
Common column value
DICT_COLUMNS
Multiple rows

6. A calculation with a null always yields...
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.
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.
Another null
INTERSECT

7. Which format mask displays December 31st - 1999?
INNER JOIN
@ or START
Primary key/foreign key
Month ddth - yyyy

8. Checks whether any value in the list makes the condition true.
Columns
All caps
Any
V$

9. The ROWID pseudocolumn is not an ________- but it acts like one.
Semicolon or forward slash
DECODE
Actual column
ROLLBACK

10. You use the __________ keyword to change characteristics of a column.
Testing functions
Same
MODIFY
Data

11. The _____________ clause determines how rows are grouped.
Null
GROUP BY
UNION operator
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

12. The key that most database designers prefer and is chosen by the system is the _____________ key.
Any
Surrogate
INTERSECT
Semicolon or forward slash

13. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
4
UNION
Primary key/foreign key
STORAGE

14. The ___________ operator tests whether a matching row cannot be found.
Location of the file
Fractional seconds
X
2000

15. To equijoin a table with another table involves matching the...
Same
X
Common column value
INNER JOIN

16. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
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
Scale
ROLLBACK
Implicit conversion

17. Users can only see changes made to a database once they have been...
ALL_OBJECTS
Orphan
Committed
Run scripts

18. What is Oracle's most popular index storage structure?
B-Tree Index
Columns
IN ANY ALL SOME
Common column value

19. LINESIZE
SET clause of an UPDATE statement
Structured Query Language
Common column value
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

20. The ____________ command makes changes permanent.
Correlated
COMMIT
Equijoin and correlated subquery
Any

21. The TRUNC function on a date without a format model truncates the timestamp to...
Retrieve
Another null
12:00:00 A.M
Implicit conversion

22. A _________ subquery returns a single column with a single row
Scalar
Common column value
ALL_OBJECTS
ANY

23. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Semicolon or forward slash
Fractional seconds
DD-MON-YY
Structured Query Language

24. Explain Flashback queries and how they are used. Give an example of a FLASHBACK_TRANSACTION query and explain what it does row by row.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

25. A transaction is a...
NATURAL JOIN
Logical unit of work
Implicit conversion
Both MIN and MAX

26. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
**** 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
BETWEEN
Optional
Precision

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

28. __________________ inserts the current date and time.
Equijoin
VALUES clause of an INSERT statement
Export Data
SYSDATE

29. Write a statement for SQL*Plus that shows the dictionary.
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 way NULL values are treated
SYSDATE
DESC dict;

30. What is the tab in SQL Developer where data dictionary view information can be found?
UNION
Combine
X
Details

31. Which one of the following string functions tells you how many characters are in a string?
DICT_COLUMNS
LENGTH
Twice
RIGHT LEFT JOIN

32. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Multiple rows
X
IN ANY ALL SOME
Equijoin

33. You can use the ________________ to subtract months from a given date.
SYSDATE
ADD_MONTHS function
4000
UNION

34. Explain the steps needed to run a script in SQL Developer
Script
Combine
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.
Has not changed the name

35. The ______ operator compares a list of values for equivalency
Equijoin
Data
ANY
UNION

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

37. 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.
Testing functions
RIGHT LEFT JOIN
All
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

38. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Same
FALSE
Parent table
VALUES clause of an INSERT statement

39. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
X
IN ANY ALL SOME
Correlated
GROUP BY

40. Write the syntax to rename index just_two_more_weeks_i to finally_i
Primary key/foreign key
Non-matching rows
ALL_OBJECTS
RENAME just_two_more_weeks TO finally_i

41. SQL*Plus is used more than SQL Developer when you need to...
Columns
Three
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
Run scripts

42. Joins involving multiple columns must always follow the ____________ relationship path.
RIGHT LEFT JOIN
Semicolon or forward slash
Primary key/foreign key
User-defined reports

43. Write a statement that drops the index need_to_pass_i
Extra spaces
V$
DROP INDEX need_to_pass_i
The indexed column is part of an expression. In this case - consider creating a function-based index instead

44. The using clause of the ANSI join syntax always assumes an...
Equijoin and identical column names
Time zone
KEEP INDEX
ADD

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

46. Explain what is found in the All Objects report folder in SQL Developer
INNER JOIN
Has not changed the name
Same
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

47. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
All caps
VALUES clause of an INSERT statement
Null
Scale

48. The _______ operator determines common rows.
RUN SCRIPT
INTERSECT
Static or dynamic
Inconsistencies

49. The time zone displacement value indicates the time difference from _______.
STORAGE
X
UTC
Results

50. What is the first column of a composite index called?
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Leading-edge
SUM