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. The same function can be used ______ in a SELECT statement.
Twice
X
Common column value
Self-joins

2. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Equijoin and correlated subquery
DD-MON-YY
Three
999999.9999

3. Explain how Substitution Variables are used when writing scripts.
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.
STORAGE
INNER JOIN
User-defined reports

4. SQL*Plus is used more than SQL Developer when you need to...
BETWEEN
Run scripts
Three
Self-joins

5. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
FALSE
DICTIONARY
Optional
Asterisk

6. The DUAL table can be used for...
Optional
Testing functions
Mathematical equations
X

7. What is the main difference between the NOT EXISTS and the NOT IN operators?
The way NULL values are treated
Surrogate key
UNION operator
ALL_OBJECTS

8. Comparison operators can compare multiple _________________.
Columns
SUM
Null
Common column value

9. Explicit data type conversion is preferable to Oracle's...
Mathematical equations
UNION
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Implicit conversion

10. The purpose of the HAVING clause is to eliminate___________.
All caps
Three
Groups
Columns

11. The query retrieves more than 5 to 15 percent of the rows
Leading-edge
Implicit conversion
Recursive
The indexed column is part of an expression. In this case - consider creating a function-based index instead

12. How many characters can be stored in the CHAR column/field?
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
2000
VALUES clause of an INSERT statement
MODIFY

13. Match each of the SQL commands on the left with a verb from the list.
Inconsistencies
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
FALSE
Multiple rows

14. To equijoin a table with another table involves matching the...
Equijoin
Common column value
INNER JOIN
ADD

15. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Non-matching rows
12:00:00 A.M
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
Correlated

16. 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?
ON
V$
DICTIONARY
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

17. SELECT LIST of a subquery in an INSERT statement
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
SET clause of an UPDATE statement
MINUS
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

18. Explain why constraints are used when building tables.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

19. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
TO_CHAR function
BETWEEN
Recursive
UTC

20. Subqueries can return...
SELECT DISTINCT menu FROM restaurant
Multiple rows
DESC dict;
UNION operator

21. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Another null
When the table is small (unless your business rules require you too)
Null
Semicolon or forward slash

22. When column names on the tables are different - you use the ______ condition.
Self-joins
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
ON
Rounded edges

23. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
2000
X
4000
STORAGE

24. When you have issued a DELETE command - you can still ________ those rows and restore the information.
Retrieve
ON
Both MIN and MAX
CLOB

25. The ALTER SESSION statement can change the session's...
DICTIONARY
Time zone
Retrieve
FALSE

26. Checks whether any value in the list makes the condition true.
Optional
Any
F12
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. Another name for a data dictionary view is __________________ or _________________.
Recursive
Static or dynamic
Any
Black

28. To restart a sequence at a lower number - you can drop and ________ the sequence.
+
Re-create
Another null
Nested

29. Explain what a static data dictionary view is and how it is used
F12
Crow's foot
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.
Surrogate

30. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Month ddth - yyyy
Both MIN and MAX
SELECT DISTINCT menu FROM restaurant

31. The focus of the logical data model is _____________
Business problems
Rounded edges
Implicit conversion
DICTIONARY

32. Users can only see changes made to a database once they have been...
Committed
Groups
Any
Semicolon or forward slash

33. The ROWID pseudocolumn is not an ________- but it acts like one.
DROP INDEX need_to_pass_i
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.
Actual column
Re-create

34. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
Black
Groups
SELECT DISTINCT menu FROM restaurant
ALL_OBJECTS

35. Explain what is found in the All Objects report folder in SQL Developer
Run scripts
X
Common column value
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

36. Joins involving multiple columns must always follow the ____________ relationship path.
Primary key/foreign key
User-defined reports
DESC dict;
A correct statement

37. How many characters can you have using the VARCHAR data type?
Rounded edges
ON
Same
4000

38. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
4
Hide data
RIGHT LEFT JOIN
Mathematical equations

39. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
RIGHT LEFT JOIN
B-Tree Index
Month ddth - yyyy
KEEP INDEX

40. Which one of the following string functions tells you how many characters are in a string?
LENGTH
SET clause of an UPDATE statement
Extra spaces
The indexed column is part of an expression. In this case - consider creating a function-based index instead

41. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
12:00:00 A.M
@ or START
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.
X

42. The time zone displacement value indicates the time difference from _______.
UTC
+
SYSDATE
Export Data

43. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
Twice
NATURAL JOIN
Month ddth - yyyy
+

44. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
Business problems
GROUP BY
Optional
Export Data

45. When you create a report in SQL Developer all the column names must match identically the names of the columns found in the tables you are referencing.
FALSE
All caps
Null
COMMIT

46. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Combine
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
STORAGE
Non-matching rows

47. What is Oracle's most popular index storage structure?
Surrogate
Time zone
B-Tree Index
Extra spaces

48. What do views do that makes them so useful for security?
Retrieve
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
BETWEEN
Hide data

49. Explain Flashback queries and how they are used. Give an example of a FLASHBACK_TRANSACTION query and explain what it does row by row.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

50. In a view you can give a column a different name than it has in the...
Static or dynamic
Parent table
ON
ALL_OBJECTS