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 _________ subquery returns a single column with a single row
DECODE
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
Implicit conversion
Scalar

2. FEEDBACK
Results
DESC dict;
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.
Command returns the number of records returned by a query.

3. You can use the ________________ to subtract months from a given date.
BETWEEN
ADD_MONTHS function
Any
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

4. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
VALUES
ANSI
X
IN ANY ALL SOME

5. Queries that are found in parentheses are done first - just as in the order of operations when doing...
ANSI
Results
Equijoin
Mathematical equations

6. SELECT * FROM instructor ORDER BY phone
F12
DICT_COLUMNS
A correct statement
Rounded edges

7. The key that most database designers prefer and is chosen by the system is the _____________ key.
Surrogate
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
Scale
Semicolon or forward slash

8. The COUNT function is useful for determining whether a table has...
2000
Actual column
Equijoin
Data

9. Match each of the SQL commands on the left with a verb from the list.
Nested
UTC
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Hide data

10. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Surrogate
4
Mathematical equations
999999.9999

11. UNION ALL does not sort - therefore it is less time consuming to use than the...
STORAGE
UNION operator
ROLLBACK
Self-joins

12. List 6 Table-Related Menu Choices
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
SELECT SYSDATE+1/96 - FROM dual
Precision
Surrogate key

13. The ALTER SESSION statement can change the session's...
INTERSECT
Extra spaces
4000
Time zone

14. Comparison operators can compare multiple _________________.
RUN SCRIPT
V$
VALUES
Columns

15. What does SQL stand for?
VALUES
DECODE
UNION operator
Structured Query Language

16. NUMBER functions can be...
INTERSECT
Surrogate key
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Nested

17. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
Logical unit of work
UNION
Any
RUN SCRIPT

18. When you run a script in SQL*Plus you must give the...
Location of the file
FALSE
V$
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

19. 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.
4
RIGHT LEFT JOIN
Crow's foot
BETWEEN

20. What is the tab in SQL Developer where data dictionary view information can be found?
B-Tree Index
Multiple rows
DECODE
Details

21. Explain what a static data dictionary view is and how it is used
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.
Asterisk
VALUES clause of an INSERT statement
99.99

22. Explicit data type conversion is preferable to Oracle's...
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Business problems
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.
Implicit conversion

23. Explain the steps needed to run a script in SQL Developer
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.
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.
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.
USER_ - ALL_ - DBA_ - and V$

24. __________________ inserts the current date and time.
Location of the file
SYSDATE
BETWEEN
@ or START

25. Write a statement for SQL*Plus that shows the dictionary.
DESC dict;
GROUP BY
Equijoin and identical column names
Logical unit of work

26. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
KEEP INDEX
MODIFY
Structured Query Language
DROP INDEX need_to_pass_i

27. To restart a sequence at a lower number - you can drop and ________ the sequence.
Re-create
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Nested
A correct statement

28. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Inconsistencies
Any
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.

29. A transaction is a...
Logical unit of work
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
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Leading-edge

30. The TRUNC function on a date without a format model truncates the timestamp to...
X
Null
12:00:00 A.M
DESC dict;

31. List the reasons that a subquery could not be unnested.
Surrogate
Semicolon or forward slash
+
X

32. Explain what is found in the All Objects report folder in SQL Developer
Hide data
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Re-create
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. ____________ are useful for analyzing and exploring the relationships within your data
Same
Command returns the number of records returned by a query.
Self-joins
GROUP BY

34. Oracle uses the outer join operator to indicate that nulls are shown for...
Crow's foot
COMMIT
LIKE
Non-matching rows

35. What is the maximum value for a column defined as NUMBER(4 -2)?
KEEP INDEX
Precision
99.99
Optional

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

37. The RTRIM function is useful for eliminating ________ in a string.
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
Extra spaces
Leading-edge
X

38. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
DECODE
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.
Another null

39. Joins involving multiple columns must always follow the ____________ relationship path.
Recursive
Logical unit of work
99.99
Primary key/foreign key

40. How many characters can you have using the VARCHAR data type?
@ or START
4000
Time zone
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

41. You use the _________ keyword to add columns or constraints to a table.
Orphan
DECODE
USER_ - ALL_ - DBA_ - and V$
ADD

42. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
IN ANY ALL SOME
All
SELECT DISTINCT menu FROM restaurant
Re-create

43. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
+
Combine
Non-matching rows
Self-joins

44. Which operator is most commonly used?
COMMIT
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.
UNION
12:00:00 A.M

45. Returns rows if the condition is true for all the values in the list
USER_ - ALL_ - DBA_ - and V$
All
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Data

46. The time zone displacement value indicates the time difference from _______.
MODIFY
Rounded edges
UTC
Three

47. What is the first column of a composite index called?
Leading-edge
ALL_OBJECTS
ROLLBACK
12:00:00 A.M

48. It is considered good form to put all SQL commands in...
All caps
X
X
MINUS

49. Write a statement that drops the index need_to_pass_i
Equijoin and identical column names
DROP INDEX need_to_pass_i
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Self-joins

50. The ___________ operator tests whether a matching row cannot be found.
X
Mathematical equations
Precision
Script