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. What is the first column of a composite index called?
Equijoin and identical column names
Actual column
Leading-edge
Crow's foot

2. The purpose of the HAVING clause is to eliminate___________.
Groups
X
Mathematical equations
Primary key/foreign key

3. Comparison operators can compare multiple _________________.
Precision
UTC
ROLLBACK
Columns

4. LINESIZE
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Nested
X
All caps

5. The _______ function lets you perform if-then-else functionality with the SQL language.
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
Logical unit of work
X
DECODE

6. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
ALL_OBJECTS
Parent table
NATURAL JOIN
UNION operator

7. List 4 aggregate functions
Equijoin
Both MIN and MAX
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Precision

8. What is the data type you can use if you need an unlimited number of textual characters?
Location of the file
TO_CHAR function
CLOB
INTERSECT

9. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Committed
4
Equijoin and correlated subquery
Twice

10. Queries that are found in parentheses are done first - just as in the order of operations when doing...
User-defined reports
999999.9999
ANY
Mathematical equations

11. The key that most database designers prefer and is chosen by the system is the _____________ key.
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
Surrogate
Orphan

12. Subqueries can return...
Multiple rows
Twice
USER_ - ALL_ - DBA_ - and V$
Equijoin and correlated subquery

13. The ____________ command makes changes permanent.
Results
COMMIT
Hide data
@ or START

14. The ________ operator cannot compare against a list of values.
X
Business problems
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
LIKE

15. When you have issued a DELETE command - you can still ________ those rows and restore the information.
Mathematical equations
Three
Retrieve
Nested

16. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Black
SET clause of an UPDATE statement
ADD
Script

17. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
IN ANY ALL SOME
ROLLBACK
Command returns the number of records returned by a query.
VALUES clause of an INSERT statement

18. You can pass parameters from the operating system when ___________ in SQl*Plus
ROLLBACK
USER_ - ALL_ - DBA_ - and V$
Running a script file
Has not changed the name

19. Checks whether any value in the list makes the condition true.
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
Hide data
Any
+

20. The term ____________ ___________ is used to express a join that satisfies the join condition.
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.
V$
DICT_COLUMNS
INNER JOIN

21. The time zone displacement value indicates the time difference from _______.
Nested
UTC
ON
ANY

22. You can use the ________________ to subtract months from a given date.
Orphan
MINUS
Same
ADD_MONTHS function

23. A join across three tables can be expressed with the ________ join syntax.
Time zone
B-Tree Index
ANSI
Logical unit of work

24. Another name for a data dictionary view is __________________ or _________________.
Equijoin
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
Static or dynamic
X

25. Explicit data type conversion is preferable to Oracle's...
Correlated
Black
Implicit conversion
SUM

26. FEEDBACK
Asterisk
Retrieve
Command returns the number of records returned by a query.
Any

27. The DUAL table can be used for...
ROLLBACK
Testing functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Multiple rows

28. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
IN ANY ALL SOME
COMMIT
Mathematical equations
NATURAL JOIN

29. Self-joins are useful for performing comparisons and checking for ___________ in data.
All caps
USER_ - ALL_ - DBA_ - and V$
Inconsistencies
Re-create

30. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
VALUES clause of an INSERT statement
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
SELECT SYSDATE+1/96 - FROM dual
IN ANY ALL SOME

31. ____________ are useful for analyzing and exploring the relationships within your data
Self-joins
X
Asterisk
RENAME just_two_more_weeks TO finally_i

32. Write a solution that adds 20 minutes to a given date.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Recursive
SELECT SYSDATE+1/96 - FROM dual
Any

33. UNION ALL does not sort - therefore it is less time consuming to use than the...
Static or dynamic
UNION operator
Location of the file
Groups

34. How many characters can you have using the VARCHAR data type?
+
SET clause of an UPDATE statement
User-defined reports
4000

35. Which one of the following string functions tells you how many characters are in a string?
Actual column
**** 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
LENGTH
USER_ - ALL_ - DBA_ - and V$

36. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Three
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.
Equijoin
Rounded edges

37. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
Mathematical equations
All caps
Both MIN and MAX
Precision

38. The _______ operator determines common rows.
KEEP INDEX
Semicolon or forward slash
Three
INTERSECT

39. The ______ depicts the M of a 1:M relationship.

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

40. List the different types of data dictionary views.
ANSI
Run scripts
USER_ - ALL_ - DBA_ - and V$
Primary key/foreign key

41. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
ADD
Actual column
Surrogate
Results

42. What does SQL stand for?
Structured Query Language
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Re-create
ANY

43. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
All
F12
Fractional seconds
When the table is small (unless your business rules require you too)

44. List the reasons that a subquery could not be unnested.
User-defined reports
X
DD-MON-YY
FALSE

45. Write a statement that drops the index need_to_pass_i
DROP INDEX need_to_pass_i
LIKE
**** 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
+

46. NUMBER functions can be...
RENAME just_two_more_weeks TO finally_i
Nested
UNION
Precision

47. What is the name of the key automatically generated from a sequence?
999999.9999
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
A correct statement
Surrogate key

48. 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.
2000
Both MIN and MAX
Extra spaces

49. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Has not changed the name
Same
RUN SCRIPT
Committed

50. The _______ function adds values together for a group of rows.
Logical unit of work
X
Any
SUM