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. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
KEEP INDEX
DD-MON-YY
Month ddth - yyyy
BETWEEN

2. Which format mask displays December 31st - 1999?
X
Surrogate key
INNER JOIN
Month ddth - yyyy

3. The time zone displacement value indicates the time difference from _______.
Committed
Three
SUM
UTC

4. The __________ operator is inclusive of the two values specified.
Same
B-Tree Index
BETWEEN
Inconsistencies

5. Explain how the SPOOL command is used and what program it can be used in.
Results
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
B-Tree Index

6. The ______ operator compares a list of values for equivalency
X
COMMIT
RUN SCRIPT
ANY

7. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Both MIN and MAX
Null
Retrieve
Data

8. How many characters can be stored in the CHAR column/field?
2000
B-Tree Index
RIGHT LEFT JOIN
Parent table

9. The ____________ command makes changes permanent.
CLOB
COMMIT
Static or dynamic
B-Tree Index

10. __________________ inserts the current date and time.
SYSDATE
Orable's flashback query feature allows you to look at values of a query at a specific time in the past - such as before specfic DML statements occured. This can be usefull in case a suer accidentally performs an unintended but committed DML change.
Static or dynamic
DESC dict;

11. When you INSERT new information into a table - it is followed with a _______ keyword that contains the information that is going to be added to the table.
VALUES
Columns
12:00:00 A.M
INTERSECT

12. Write a statement for SQL*Plus that shows the dictionary.
DICTIONARY
DESC dict;
Non-matching rows
COMMIT

13. To join four tables - you must have at least _____ join conditions.
X
Nested
Three
Results

14. When column names on the tables are different - you use the ______ condition.
Combine
Default is 14; when you use the default - you repeat the heading every 14 rows.
ON
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

15. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
LIKE
Command returns the number of records returned by a query.
FALSE
UNION operator

16. The ALTER SESSION statement can change the session's...
STORAGE
Time zone
Asterisk
The way NULL values are treated

17. Checks whether any value in the list makes the condition true.
Any
Correlated
Black
Orable's flashback query feature allows you to look at values of a query at a specific time in the past - such as before specfic DML statements occured. This can be usefull in case a suer accidentally performs an unintended but committed DML change.

18. Match each of the SQL commands on the left with a verb from the list.
SELECT DISTINCT menu FROM restaurant
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Another null
ON

19. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
UTC
Equijoin and correlated subquery
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Semicolon or forward slash

20. UNION ALL does not sort - therefore it is less time consuming to use than the...
Logical unit of work
UNION operator
Fractional seconds
Combine

21. The ___________ operator tests whether a matching row cannot be found.
SYSDATE
A correct statement
X
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

22. Joins involving multiple columns must always follow the ____________ relationship path.
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
DESC dict;
Structured Query Language
Primary key/foreign key

23. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Optional
DICTIONARY
2000
X

24. What is Oracle's most popular index storage structure?
CLOB
B-Tree Index
Orphan
Orable's flashback query feature allows you to look at values of a query at a specific time in the past - such as before specfic DML statements occured. This can be usefull in case a suer accidentally performs an unintended but committed DML change.

25. 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
ADD_MONTHS function
NATURAL JOIN
Details

26. Returns rows if the condition is true for all the values in the list
ANSI
All
Re-create
BETWEEN

27. What is the tab in SQL Developer where data dictionary view information can be found?
4000
GROUP BY
SELECT DISTINCT menu FROM restaurant
Details

28. The COUNT function is useful for determining whether a table has...
Common column value
Data
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
B-Tree Index

29. List the reasons that a subquery could not be unnested.
Surrogate key
X
Running a script file
Static or dynamic

30. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Script
Equijoin and identical column names
B-Tree Index
SELECT SYSDATE+1/96 - FROM dual

31. Explain what is found in the All Objects report folder in SQL Developer
Nested
Asterisk
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
SUM

32. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
RUN SCRIPT
SYSDATE
Twice
NATURAL JOIN

33. LINESIZE
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
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Orable's flashback query feature allows you to look at values of a query at a specific time in the past - such as before specfic DML statements occured. This can be usefull in case a suer accidentally performs an unintended but committed DML change.

34. What is the maximum value for a column defined as NUMBER(4 -2)?
VALUES clause of an INSERT statement
99.99
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Orable's flashback query feature allows you to look at values of a query at a specific time in the past - such as before specfic DML statements occured. This can be usefull in case a suer accidentally performs an unintended but committed DML change.

35. The RTRIM function is useful for eliminating ________ in a string.
Extra spaces
Implicit conversion
Running a script file
ROLLBACK

36. To restart a sequence at a lower number - you can drop and ________ the sequence.
Run scripts
Rounded edges
Re-create
GROUP BY

37. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Retrieve
Static or dynamic
999999.9999
STORAGE

38. Write a statement that drops the index need_to_pass_i
DROP INDEX need_to_pass_i
X
Same
Parent table

39. Explicit data type conversion is preferable to Oracle's...
Null
FALSE
99.99
Implicit conversion

40. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
4
SUM
Command returns the number of records returned by a query.
Semicolon or forward slash

41. Which operator is most commonly used?
UNION
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
Recursive
ANY

42. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Both MIN and MAX
Surrogate key
The way NULL values are treated
FALSE

43. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Results
Static or dynamic
VALUES
DROP INDEX need_to_pass_i

44. PAGESIZE
Any
DD-MON-YY
Actual column
Default is 14; when you use the default - you repeat the heading every 14 rows.

45. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
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
BETWEEN
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Retrieve

46. What are the main components of a ROWID?
Hide data
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.
Any
All

47. The _____________ clause determines how rows are grouped.
Non-matching rows
+
GROUP BY
Asterisk

48. _________________ relationships are always optional relationships.
999999.9999
All
Asterisk
Recursive

49. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Columns
ANSI
Equijoin

50. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
COMMIT
DICT_COLUMNS
SELECT SYSDATE+1/96 - FROM dual
Correlated