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. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Semicolon or forward slash
Black
ANSI
Inconsistencies

2. The COUNT function uses the __________ wildcard.
Semicolon or forward slash
Testing functions
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Asterisk

3. PAGESIZE
Default is 14; when you use the default - you repeat the heading every 14 rows.
Any
Recursive
Orphan

4. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
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.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Details
Rounded edges

5. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
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
Both MIN and MAX
All
ADD_MONTHS function

6. Subqueries can return...
Multiple rows
CLOB
@ or START
X

7. Comparison operators can compare multiple _________________.
Columns
ALL_OBJECTS
4
Primary key/foreign key

8. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
B-Tree Index
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
GROUP BY
DD-MON-YY

9. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
+
Re-create
999999.9999
MINUS

10. Converting a text literal to DATE format requires the use of the...
Equijoin and correlated subquery
Rounded edges
TO_CHAR function
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

11. What is the first column of a composite index called?
ANY
Business problems
RIGHT LEFT JOIN
Leading-edge

12. You can use the ________________ to subtract months from a given date.
ADD_MONTHS function
SELECT SYSDATE+1/96 - FROM dual
DD-MON-YY
Asterisk

13. LINESIZE
LIKE
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Black
TO_CHAR function

14. The ROWID pseudocolumn is not an ________- but it acts like one.
DICT_COLUMNS
Location of the file
Committed
Actual column

15. What are the main components of a ROWID?
RIGHT LEFT JOIN
BETWEEN
GROUP BY
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.

16. Oracle uses the outer join operator to indicate that nulls are shown for...
Run scripts
DECODE
Non-matching rows
Running a script file

17. What does SQL stand for?
Any
Actual column
SET clause of an UPDATE statement
Structured Query Language

18. Write the syntax to rename index just_two_more_weeks_i to finally_i
RENAME just_two_more_weeks TO finally_i
Structured Query Language
Equijoin and identical column names
Location of the file

19. In SQL Developer field names and table names are shown in the color _______.
UNION operator
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
ON
Black

20. You use the _________ keyword to add columns or constraints to a table.
UNION
ADD
Results
SELECT SYSDATE+1/96 - FROM dual

21. Which one of the following string functions tells you how many characters are in a string?
LENGTH
Mathematical equations
UNION operator
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

22. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
MINUS
Surrogate
STORAGE
DICTIONARY

23. Dynamic views begin with a ___________.
Business problems
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.
V$
MODIFY

24. Explain why constraints are used when building tables.

25. To restart a sequence at a lower number - you can drop and ________ the sequence.
Run scripts
BETWEEN
Script
Re-create

26. What is the name of the key automatically generated from a sequence?
Semicolon or forward slash
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.
Surrogate key
Any

27. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
UTC
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.
Equijoin
Details

28. The _______ function lets you perform if-then-else functionality with the SQL language.
Multiple rows
DECODE
Asterisk
SUM

29. The query retrieves more than 5 to 15 percent of the rows
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
The indexed column is part of an expression. In this case - consider creating a function-based index instead
4000

30. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
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
VALUES
Implicit conversion
Same

31. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
GROUP BY
Results
Business problems
A correct statement

32. Checks whether any value in the list makes the condition true.
Another null
Any
Equijoin and correlated subquery
IN ANY ALL SOME

33. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
Hide data
Logical unit of work
ROLLBACK
Self-joins

34. What is the keyboard shortcut for the ROLLBACK command?
Fractional seconds
DROP INDEX need_to_pass_i
4000
F12

35. Write a statement that drops the index need_to_pass_i
Another null
Equijoin
DROP INDEX need_to_pass_i
SET clause of an UPDATE statement

36. What is the maximum value for a column defined as NUMBER(4 -2)?
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
A correct statement
Primary key/foreign key
99.99

37. How many characters can you have using the VARCHAR data type?
Export Data
Columns
X
4000

38. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
Orphan
4000
MINUS
MODIFY

39. The same function can be used ______ in a SELECT statement.
Twice
SUM
Actual column
Static or dynamic

40. The ______ depicts the M of a 1:M relationship.

41. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Three
Null
Hide data
FALSE

42. _________________ relationships are always optional relationships.
A correct statement
Recursive
STORAGE
Extra spaces

43. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Multiple rows
Optional
Nested
Logical unit of work

44. SQL*Plus is used more than SQL Developer when you need to...
NATURAL JOIN
Data
Run scripts
MINUS

45. The DUAL table can be used for...
Actual column
GROUP BY
Testing functions
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

46. You use the __________ keyword to change characteristics of a column.
UTC
MODIFY
Logical unit of work
Orphan

47. A _________ subquery returns a single column with a single row
TO_CHAR function
Scalar
ANY
Mathematical equations

48. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
Any
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
When the table is small (unless your business rules require you too)
X

49. Explain in detail the impact of INDEXES and how they are used by Oracle.
Parent table
Location of the file
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

50. Match each of the SQL commands on the left with a verb from the list.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
SUM
RENAME just_two_more_weeks TO finally_i