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. A calculation with a null always yields...
LENGTH
Another null
Semicolon or forward slash
COMMIT

2. Write the syntax to rename index just_two_more_weeks_i to finally_i
RENAME just_two_more_weeks TO finally_i
Re-create
When the table is small (unless your business rules require you too)
FALSE

3. The _______ operator determines common rows.
ANY
FALSE
B-Tree Index
INTERSECT

4. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
4000
Both MIN and MAX
Month ddth - yyyy
Inconsistencies

5. What is the tab in SQL Developer where data dictionary view information can be found?
Re-create
The way NULL values are treated
Equijoin
Details

6. The ______ operator compares a list of values for equivalency
FALSE
Parent table
+
ANY

7. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
Recursive
The way NULL values are treated
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
Scale

8. The _______ function lets you perform if-then-else functionality with the SQL language.
Implicit conversion
DECODE
Parent table
SELECT SYSDATE+1/96 - FROM dual

9. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
KEEP INDEX
Precision
Structured Query Language
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

10. You use the _________ keyword to add columns or constraints to a table.
CLOB
Equijoin and correlated subquery
RIGHT LEFT JOIN
ADD

11. 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
RENAME just_two_more_weeks TO finally_i
Retrieve
FALSE

12. UNION ALL does not sort - therefore it is less time consuming to use than the...
MINUS
Running a script file
USER_ - ALL_ - DBA_ - and V$
UNION operator

13. Subqueries can return...
Multiple rows
Three
Self-joins
Any

14. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
4
INTERSECT
SET clause of an UPDATE statement
Non-matching rows

15. PAGESIZE
FALSE
Default is 14; when you use the default - you repeat the heading every 14 rows.
ANY
SUM

16. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
The indexed column is part of an expression. In this case - consider creating a function-based index instead
KEEP INDEX
CLOB

17. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
KEEP INDEX
Black
999999.9999
SELECT SYSDATE+1/96 - FROM dual

18. Write a statement for SQL*Plus that shows the dictionary.
DROP INDEX need_to_pass_i
LENGTH
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.
DESC dict;

19. Converting a text literal to DATE format requires the use of the...
TO_CHAR function
VALUES clause of an INSERT statement
SYSDATE
UTC

20. The _____________ clause determines how rows are grouped.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
GROUP BY
Surrogate
UNION

21. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Recursive
Combine
X
Any

22. The ____________ command makes changes permanent.
Precision
COMMIT
DECODE
Retrieve

23. SELECT LIST of a subquery in an INSERT statement
Recursive
Crow's foot
SET clause of an UPDATE statement
Structured Query Language

24. List 4 aggregate functions
NATURAL JOIN
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
ADD
Asterisk

25. The DUAL table can be used for...
ADD_MONTHS function
Testing functions
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
Black

26. List 6 Table-Related Menu Choices
99.99
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Twice
SELECT DISTINCT menu FROM restaurant

27. What do views do that makes them so useful for security?
INNER JOIN
Recursive
Hide data
KEEP INDEX

28. Dynamic views begin with a ___________.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
TO_CHAR function
V$
VALUES clause of an INSERT statement

29. Match each of the SQL commands on the left with a verb from the list.
Running a script file
Columns
Asterisk
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

30. Users can only see changes made to a database once they have been...
FALSE
Primary key/foreign key
Both MIN and MAX
Committed

31. FEEDBACK
SELECT DISTINCT menu FROM restaurant
Time zone
Command returns the number of records returned by a query.
Leading-edge

32. Explain how the SPOOL command is used and what program it can be used in.
X
RENAME just_two_more_weeks TO finally_i
DICT_COLUMNS
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

33. Self-joins are useful for performing comparisons and checking for ___________ in data.
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
Equijoin and identical column names
Inconsistencies
UNION

34. To restart a sequence at a lower number - you can drop and ________ the sequence.
Re-create
VALUES
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.
A correct statement

35. How many characters can you have using the VARCHAR data type?
Script
Location of the file
LIKE
4000

36. The ROWID pseudocolumn is not an ________- but it acts like one.
12:00:00 A.M
X
Actual column
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

37. In SQL Developer field names and table names are shown in the color _______.
Black
NATURAL JOIN
Results
Common column value

38. Joins involving multiple columns must always follow the ____________ relationship path.
Scalar
FALSE
Primary key/foreign key
LIKE

39. 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
SYSDATE
Same
Precision

40. 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
FALSE
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

41. Checks whether any value in the list makes the condition true.
Any
FALSE
Surrogate key
The indexed column is part of an expression. In this case - consider creating a function-based index instead

42. Which format mask displays December 31st - 1999?
Month ddth - yyyy
Business problems
Surrogate key
Leading-edge

43. What is the most common DATE format found in SQL?
All
DD-MON-YY
ANSI
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

44. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Actual column
Semicolon or forward slash
X
Logical unit of work

45. How many characters can be stored in the CHAR column/field?
When the table is small (unless your business rules require you too)
2000
Data
LENGTH

46. Oracle uses the outer join operator to indicate that nulls are shown for...
Non-matching rows
User-defined reports
Optional
LIKE

47. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Correlated
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
4000
Equijoin

48. In a view you can give a column a different name than it has in the...
Crow's foot
Parent table
Null
MINUS

49. The RTRIM function is useful for eliminating ________ in a string.
INTERSECT
BETWEEN
Equijoin and identical column names
Extra spaces

50. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
X
Scalar
Optional