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 name of the view where you can find the collection of static and data dictionary tables and views - along with their descriptions?
Black
@ or START
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
DICTIONARY

2. The focus of the logical data model is _____________
Scalar
X
Precision
Business problems

3. NUMBER functions can be...
Nested
Equijoin and identical column names
ADD
INNER JOIN

4. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
VALUES clause of an INSERT statement
Semicolon or forward slash
ALL_OBJECTS
MINUS

5. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
Surrogate
MINUS
Mathematical equations
ADD

6. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
NATURAL JOIN
Run scripts
Mathematical equations
KEEP INDEX

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 ____________
Logical unit of work
UNION operator
SELECT DISTINCT menu FROM restaurant
Equijoin and correlated subquery

8. Checks whether any value in the list makes the condition true.
Committed
The way NULL values are treated
Any
TO_CHAR function

9. 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
Committed
Rounded edges
Combine

10. The ______ operator compares a list of values for equivalency
MINUS
CLOB
ANY
SELECT SYSDATE+1/96 - FROM dual

11. What is the maximum value for a column defined as NUMBER(4 -2)?
99.99
4000
DICT_COLUMNS
ADD_MONTHS function

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

13. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
2000
4
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Running a script file

14. Which operator is most commonly used?
ON
UNION
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Equijoin and correlated subquery

15. The DUAL table can be used for...
CLOB
LIKE
RUN SCRIPT
Testing functions

16. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Twice
BETWEEN
USER_ - ALL_ - DBA_ - and V$
Business problems

17. Write a solution that adds 20 minutes to a given date.
FALSE
X
SELECT SYSDATE+1/96 - FROM dual
ANY

18. Explain in detail the impact of INDEXES and how they are used by Oracle.
Equijoin and correlated subquery
Data
2000
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

19. LINESIZE
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.
LIKE
Details
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

20. 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
VALUES clause of an INSERT statement
Leading-edge
99.99

21. Explain how the SPOOL command is used and what program it can be used in.
DECODE
Run scripts
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
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.

22. What does the following function return? SELECT LENGTH(NULL) - FROM dual
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.
Null
DROP INDEX need_to_pass_i
@ or START

23. Comparison operators can compare multiple _________________.
When the table is small (unless your business rules require you too)
X
Columns
Scale

24. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
V$
Any
Same
ADD

25. You use the __________ keyword to change characteristics of a column.
MODIFY
The way NULL values are treated
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.
VALUES clause of an INSERT statement

26. In a view you can give a column a different name than it has in the...
MINUS
Primary key/foreign key
Parent table
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. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
X
+
F12
X

28. Match each of the SQL commands on the left with a verb from the list.
Running a script file
SET clause of an UPDATE statement
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Equijoin and identical column names

29. The ___________ operator tests whether a matching row cannot be found.
Any
COMMIT
X
BETWEEN

30. The TRUNC function on a date without a format model truncates the timestamp to...
Leading-edge
12:00:00 A.M
Implicit conversion
99.99

31. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Mathematical equations
V$
Scale
DD-MON-YY

32. A join across three tables can be expressed with the ________ join syntax.
Logical unit of work
ANSI
Optional
A correct statement

33. PAGESIZE
CLOB
Default is 14; when you use the default - you repeat the heading every 14 rows.
Inconsistencies
Asterisk

34. __________________ inserts the current date and time.
CLOB
SYSDATE
X
Multiple rows

35. The ________ operator cannot compare against a list of values.
RUN SCRIPT
LIKE
Precision
X

36. How many characters can you have using the VARCHAR data type?
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
BETWEEN
4000
DD-MON-YY

37. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Mathematical equations
NATURAL JOIN
Equijoin
Details

38. List the steps performed by a correlated subquery.
Month ddth - yyyy
2000
Retrieve
**** 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

39. The RTRIM function is useful for eliminating ________ in a string.
Common column value
Extra spaces
Same
ADD

40. Write a statement that drops the index need_to_pass_i
Re-create
DROP INDEX need_to_pass_i
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
ADD

41. Checks whether any value in the list makes the condition true.
INNER JOIN
Any
DECODE
COMMIT

42. How many characters can be stored in the CHAR column/field?
Surrogate
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.
Orphan
2000

43. Subqueries can return...
X
Multiple rows
+
Both MIN and MAX

44. The ROWID pseudocolumn is not an ________- but it acts like one.
Actual column
Three
All
DESC dict;

45. Self-joins are useful for performing comparisons and checking for ___________ in data.
Any
Optional
SUM
Inconsistencies

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

47. To join four tables - you must have at least _____ join conditions.
RENAME just_two_more_weeks TO finally_i
Three
Optional
DICT_COLUMNS

48. The _____________ clause determines how rows are grouped.
Time zone
GROUP BY
SELECT DISTINCT menu FROM restaurant
DECODE

49. When you have issued a DELETE command - you can still ________ those rows and restore the information.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Retrieve
ANSI
All caps

50. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
X
All caps
FALSE
X