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. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
2000
ADD_MONTHS function
LIKE
Results

2. Joins involving multiple columns must always follow the ____________ relationship path.
A correct statement
Primary key/foreign key
CLOB
B-Tree Index

3. The key that most database designers prefer and is chosen by the system is the _____________ key.
12:00:00 A.M
UNION operator
Surrogate
FALSE

4. The ___________ operator tests whether a matching row cannot be found.
Running a script file
Location of the file
V$
X

5. The RTRIM function is useful for eliminating ________ in a string.
Another null
Logical unit of work
Extra spaces
Rounded edges

6. What is the main difference between the NOT EXISTS and the NOT IN operators?
The way NULL values are treated
DECODE
SET clause of an UPDATE statement
BETWEEN

7. How many characters can you have using the VARCHAR data type?
4000
Three
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
Correlated

8. The query retrieves more than 5 to 15 percent of the rows
User-defined reports
Self-joins
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Primary key/foreign key

9. Write a statement for SQL*Plus that shows the dictionary.
BETWEEN
DESC dict;
Leading-edge
VALUES clause of an INSERT statement

10. 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.
SUM
Retrieve
FALSE
Has not changed the name

11. Explain the steps needed to run a script in SQL Developer
UTC
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.
When the table is small (unless your business rules require you too)

12. List the reasons that a subquery could not be unnested.
X
Command returns the number of records returned by a query.
4000
Retrieve

13. 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.
RIGHT LEFT JOIN
Twice
ANY
Non-matching rows

14. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Any
RENAME just_two_more_weeks TO finally_i
DROP INDEX need_to_pass_i
Both MIN and MAX

15. The COUNT function uses the __________ wildcard.
Asterisk
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Structured Query Language
Month ddth - yyyy

16. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Mathematical equations
X
STORAGE
Parent table

17. To equijoin a table with another table involves matching the...
Common column value
FALSE
Any
Fractional seconds

18. Users can only see changes made to a database once they have been...
Surrogate key
B-Tree Index
4
Committed

19. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
Implicit conversion
Precision
@ or START
IN ANY ALL SOME

20. The ______ operator compares a list of values for equivalency
Details
ANY
Leading-edge
RIGHT LEFT JOIN

21. In a view you can give a column a different name than it has in the...
Three
Parent table
Non-matching rows
Any

22. Write a statement that drops the index need_to_pass_i
DROP INDEX need_to_pass_i
B-Tree Index
Hide data
Self-joins

23. 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
Equijoin and identical column names
UNION
Same

24. What is the first column of a composite index called?
Semicolon or forward slash
Leading-edge
NATURAL JOIN
SELECT SYSDATE+1/96 - FROM dual

25. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
B-Tree Index
Script
TO_CHAR function
Correlated

26. You use the _________ keyword to add columns or constraints to a table.
Any
ADD
ON
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

27. To join four tables - you must have at least _____ join conditions.
INNER JOIN
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
12:00:00 A.M
Three

28. List 4 aggregate functions
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.
NATURAL JOIN
Combine
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

29. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
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
UNION
ADD_MONTHS function

30. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Leading-edge
IN ANY ALL SOME
Equijoin and correlated subquery
Default is 14; when you use the default - you repeat the heading every 14 rows.

31. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
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
ALL_OBJECTS
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.
ON

32. Converting a text literal to DATE format requires the use of the...
X
ADD
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.
TO_CHAR function

33. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
4
User-defined reports
Surrogate key
Crow's foot

34. The term ____________ ___________ is used to express a join that satisfies the join condition.
Any
Both MIN and MAX
X
INNER JOIN

35. When you run a script in SQL*Plus you must give the...
X
Location of the file
Correlated
Twice

36. When column names on the tables are different - you use the ______ condition.
Columns
Inconsistencies
UNION operator
ON

37. In SQL Developer field names and table names are shown in the color _______.
Black
BETWEEN
Recursive
X

38. What is the maximum value for a column defined as NUMBER(4 -2)?
Equijoin and correlated subquery
F12
99.99
DECODE

39. UNION ALL does not sort - therefore it is less time consuming to use than the...
Equijoin
Inconsistencies
B-Tree Index
UNION operator

40. What is the most common DATE format found in SQL?
SUM
B-Tree Index
+
DD-MON-YY

41. What is the keyboard shortcut for the ROLLBACK command?
VALUES
F12
Nested
SELECT DISTINCT menu FROM restaurant

42. The time zone displacement value indicates the time difference from _______.
UTC
DICTIONARY
RENAME just_two_more_weeks TO finally_i
Run scripts

43. You use the __________ keyword to change characteristics of a column.
Surrogate
**** 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
MODIFY
Semicolon or forward slash

44. The ________ operator cannot compare against a list of values.
Parent table
LIKE
ADD_MONTHS function
DROP INDEX need_to_pass_i

45. 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?
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.
DICTIONARY
VALUES
12:00:00 A.M

46. ____________ are useful for analyzing and exploring the relationships within your data
Non-matching rows
Business problems
Self-joins
Optional

47. PAGESIZE
B-Tree Index
TO_CHAR function
Default is 14; when you use the default - you repeat the heading every 14 rows.
999999.9999

48. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
USER_ - ALL_ - DBA_ - and V$
BETWEEN
SUM
KEEP INDEX

49. What do views do that makes them so useful for security?
BETWEEN
Hide data
Mathematical equations
Nested

50. What is the tab in SQL Developer where data dictionary view information can be found?
Both MIN and MAX
Details
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
The indexed column is part of an expression. In this case - consider creating a function-based index instead