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. UNION ALL does not sort - therefore it is less time consuming to use than the...
UNION operator
Testing functions
INTERSECT
Retrieve

2. 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.
Scalar
Scale
SELECT SYSDATE+1/96 - FROM dual
RIGHT LEFT JOIN

3. Converting a text literal to DATE format requires the use of the...
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.
Semicolon or forward slash
TO_CHAR function
MINUS

4. Explicit data type conversion is preferable to Oracle's...
Run scripts
Implicit conversion
NATURAL JOIN
Static or dynamic

5. What is the main difference between the NOT EXISTS and the NOT IN operators?
ON
The way NULL values are treated
Fractional seconds
TO_CHAR function

6. __________________ inserts the current date and time.
SYSDATE
Has not changed the name
CLOB
Location of the file

7. NUMBER functions can be...
All caps
Nested
RUN SCRIPT
SELECT DISTINCT menu FROM restaurant

8. What is the maximum value for a column defined as NUMBER(4 -2)?
LIKE
A correct statement
Groups
99.99

9. Checks whether any value in the list makes the condition true.
Any
DROP INDEX need_to_pass_i
Precision
Data

10. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
FALSE
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
MINUS
Orphan

11. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
BETWEEN
+
Export Data
FALSE

12. Explain in detail the impact of INDEXES and how they are used by Oracle.
DICTIONARY
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
Self-joins
Semicolon or forward slash

13. A transaction is a...
Logical unit of work
@ or START
Equijoin and correlated subquery
Asterisk

14. The ________ operator cannot compare against a list of values.
UNION operator
LIKE
Combine
Surrogate

15. You can pass parameters from the operating system when ___________ in SQl*Plus
Surrogate key
Running a script file
X
All caps

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?
All
Non-matching rows
Three
DICTIONARY

17. What are the main components of a ROWID?
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.
F12
Orphan
MODIFY

18. Write a solution that adds 20 minutes to a given date.
SELECT SYSDATE+1/96 - FROM dual
Twice
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.
Location of the file

19. The time zone displacement value indicates the time difference from _______.
Re-create
Multiple rows
Same
UTC

20. The TRUNC function on a date without a format model truncates the timestamp to...
Extra spaces
12:00:00 A.M
SELECT DISTINCT menu FROM restaurant
Run scripts

21. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Default is 14; when you use the default - you repeat the heading every 14 rows.
Semicolon or forward slash
UNION operator
Twice

22. Oracle uses the outer join operator to indicate that nulls are shown for...
Another null
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Asterisk
Non-matching rows

23. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
The way NULL values are treated
ON
Script
Surrogate key

24. You use the _________ keyword to add columns or constraints to a table.
ADD
NATURAL JOIN
Fractional seconds
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.

25. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Results
Both MIN and MAX
Multiple rows
MODIFY

26. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
MINUS
ANSI
Results
A correct statement

27. Subqueries can return...
Multiple rows
Month ddth - yyyy
Run scripts
DROP INDEX need_to_pass_i

28. What is the most common DATE format found in SQL?
CLOB
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.
DD-MON-YY
Retrieve

29. How many characters can you have using the VARCHAR data type?
Null
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
4000
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

30. The _____________ clause determines how rows are grouped.
Static or dynamic
GROUP BY
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Groups

31. The _______ operator determines common rows.
Command returns the number of records returned by a query.
Extra spaces
INTERSECT
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.

32. 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

33. You use the __________ keyword to change characteristics of a column.
Self-joins
Static or dynamic
MODIFY
Non-matching rows

34. The ____________ command makes changes permanent.
VALUES
Surrogate
Leading-edge
COMMIT

35. List the steps performed by a correlated subquery.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
**** Select a row from the outer query - Determine the value of the correlated column - Execute the inner query for each record of the outer query. - Feed the results of the inner query to the outer query and evaluate it. It if satisfies the criteria
Non-matching rows
TO_CHAR function

36. It is considered good form to put all SQL commands in...
Non-matching rows
All caps
Crow's foot
UTC

37. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
4
Parent table
Command returns the number of records returned by a query.
CLOB

38. What is the first column of a composite index called?
MINUS
4000
Implicit conversion
Leading-edge

39. List 6 Table-Related Menu Choices
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Another null
SELECT SYSDATE+1/96 - FROM dual
999999.9999

40. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Scale
12:00:00 A.M
ALL_OBJECTS

41. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
Inconsistencies
Orphan
X
INTERSECT

42. In SQL Developer field names and table names are shown in the color _______.
Black
When the table is small (unless your business rules require you too)
Any
@ or START

43. Write a statement for SQL*Plus that shows the dictionary.
Equijoin and identical column names
DESC dict;
Non-matching rows
Parent table

44. To equijoin a table with another table involves matching the...
BETWEEN
Orphan
Common column value
Script

45. A _________ subquery returns a single column with a single row
4000
X
Scalar
NATURAL JOIN

46. Checks whether any value in the list makes the condition true.
USER_ - ALL_ - DBA_ - and V$
Any
SELECT DISTINCT menu FROM restaurant
KEEP INDEX

47. Self-joins are useful for performing comparisons and checking for ___________ in data.
SELECT SYSDATE+1/96 - FROM dual
SYSDATE
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.
Inconsistencies

48. SQL*Plus is used more than SQL Developer when you need to...
Run scripts
Inconsistencies
@ or START
+

49. The same function can be used ______ in a SELECT statement.
Twice
X
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Equijoin and identical column names

50. Match each of the SQL commands on the left with a verb from the list.
Scalar
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
**** Select a row from the outer query - Determine the value of the correlated column - Execute the inner query for each record of the outer query. - Feed the results of the inner query to the outer query and evaluate it. It if satisfies the criteria