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. To join four tables - you must have at least _____ join conditions.
Three
DICT_COLUMNS
Month ddth - yyyy
SET clause of an UPDATE statement

2. 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
Running a script file
+
2000

3. LINESIZE
Script
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
Default is 14; when you use the default - you repeat the heading every 14 rows.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

4. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
MODIFY
Default is 14; when you use the default - you repeat the heading every 14 rows.
Both MIN and MAX
RIGHT LEFT JOIN

5. The purpose of the HAVING clause is to eliminate___________.
Non-matching rows
Both MIN and MAX
4000
Groups

6. The ________ operator cannot compare against a list of values.
LIKE
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Twice
Mathematical equations

7. What does SQL stand for?
Multiple rows
Run scripts
Results
Structured Query Language

8. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
@ or START
DROP INDEX need_to_pass_i
DECODE
Correlated

9. The _______ function lets you perform if-then-else functionality with the SQL language.
Location of the file
Recursive
DECODE
Both MIN and MAX

10. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Optional
When the table is small (unless your business rules require you too)
ADD_MONTHS function
Correlated

11. What is the first column of a composite index called?
ON
Leading-edge
999999.9999
RUN SCRIPT

12. SQL*Plus is used more than SQL Developer when you need to...
DICTIONARY
Run scripts
ANSI
Scale

13. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Actual column
Another null
Committed

14. The TRUNC function on a date without a format model truncates the timestamp to...
MINUS
12:00:00 A.M
FALSE
999999.9999

15. What is the name of the key automatically generated from a sequence?
MODIFY
DROP INDEX need_to_pass_i
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Surrogate key

16. What is the data type you can use if you need an unlimited number of textual characters?
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
CLOB
GROUP BY
Twice

17. The same function can be used ______ in a SELECT statement.
Combine
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.
ON
Twice

18. What is Oracle's most popular index storage structure?
Export Data
B-Tree Index
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
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

19. The query retrieves more than 5 to 15 percent of the rows
F12
+
The indexed column is part of an expression. In this case - consider creating a function-based index instead
MODIFY

20. The __________ operator is inclusive of the two values specified.
LIKE
The indexed column is part of an expression. In this case - consider creating a function-based index instead
BETWEEN
+

21. Dynamic views begin with a ___________.
ADD_MONTHS function
DICT_COLUMNS
4
V$

22. Write a statement for SQL*Plus that shows the dictionary.
Equijoin and correlated subquery
Same
Retrieve
DESC dict;

23. Users can only see changes made to a database once they have been...
99.99
ON
Committed
BETWEEN

24. The using clause of the ANSI join syntax always assumes an...
Equijoin and identical column names
When the table is small (unless your business rules require you too)
Crow's foot
The way NULL values are treated

25. When is it not a good idea to use an index?
Location of the file
When the table is small (unless your business rules require you too)
Inconsistencies
DROP INDEX need_to_pass_i

26. The ____________ command makes changes permanent.
Nested
UNION
COMMIT
Any

27. You can pass parameters from the operating system when ___________ in SQl*Plus
ALL_OBJECTS
Recursive
Running a script file
Fractional seconds

28. A _________ subquery returns a single column with a single row
Equijoin and correlated subquery
Scalar
Committed
Testing functions

29. _________ rows are not allowed in the relational model.
Semicolon or forward slash
Structured Query Language
Orphan
DESC dict;

30. What is the most common DATE format found in SQL?
Retrieve
DD-MON-YY
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
FALSE

31. SELECT LIST of a subquery in an INSERT statement
LENGTH
IN ANY ALL SOME
RIGHT LEFT JOIN
SET clause of an UPDATE statement

32. In a view you can give a column a different name than it has in the...
Time zone
Retrieve
Parent table
SYSDATE

33. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
All caps
Optional
X
Rounded edges

34. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Equijoin and correlated subquery
DROP INDEX need_to_pass_i
F12
ALL_OBJECTS

35. The time zone displacement value indicates the time difference from _______.
Recursive
UTC
Surrogate key
ANY

36. The focus of the logical data model is _____________
Business problems
DD-MON-YY
Static or dynamic
Precision

37. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
Export Data
Equijoin and identical column names
Month ddth - yyyy

38. A calculation with a null always yields...
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
UNION operator
Another null
When the table is small (unless your business rules require you too)

39. A transaction is a...
12:00:00 A.M
Location of the file
UNION operator
Logical unit of work

40. It is considered good form to put all SQL commands in...
Same
All caps
Surrogate
Extra spaces

41. 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.
Actual column
SYSDATE
12:00:00 A.M
RIGHT LEFT JOIN

42. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
Re-create
Non-matching rows
VALUES
Export Data

43. Subqueries can return...
TO_CHAR function
Re-create
Surrogate key
Multiple rows

44. Match each of the SQL commands on the left with a verb from the list.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
TO_CHAR function
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Leading-edge

45. You use the _________ keyword to add columns or constraints to a table.
Non-matching rows
Crow's foot
DD-MON-YY
ADD

46. What do views do that makes them so useful for security?
FALSE
Hide data
B-Tree Index
@ or START

47. __________________ inserts the current date and time.
SYSDATE
Command returns the number of records returned by a query.
DICTIONARY
Correlated

48. Write the syntax to rename index just_two_more_weeks_i to finally_i
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.
V$
RENAME just_two_more_weeks TO finally_i
12:00:00 A.M

49. The RTRIM function is useful for eliminating ________ in a string.
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.
Extra spaces
DECODE
Retrieve

50. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
All
Mathematical equations
Committed