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. SQL*Plus is used more than SQL Developer when you need to...
Implicit conversion
Black
12:00:00 A.M
Run scripts

2. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
Any
RUN SCRIPT
Self-joins
Same

3. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
FALSE
4
VALUES clause of an INSERT statement
Re-create

4. FEEDBACK
Testing functions
Recursive
Command returns the number of records returned by a query.
Optional

5. Checks whether any value in the list makes the condition true.
Any
Multiple rows
Rounded edges
All

6. The ______ operator compares a list of values for equivalency
RUN SCRIPT
ANY
Orphan
Recursive

7. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Equijoin
INTERSECT
ADD_MONTHS function
Details

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

9. NUMBER functions can be...
ROLLBACK
Nested
Data
LIKE

10. The ____________ command makes changes permanent.
COMMIT
Common column value
ADD_MONTHS function
LENGTH

11. The same function can be used ______ in a SELECT statement.
Any
Twice
4
**** 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

12. A transaction is a...
Logical unit of work
X
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
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

13. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
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.
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
Surrogate key

14. When you have issued a DELETE command - you can still ________ those rows and restore the information.
All
DECODE
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Retrieve

15. Queries that are found in parentheses are done first - just as in the order of operations when doing...
ANSI
Mathematical equations
BETWEEN
FALSE

16. SELECT * FROM instructor ORDER BY phone
Three
A correct statement
Equijoin and correlated subquery
Month ddth - yyyy

17. Comparison operators can compare multiple _________________.
Columns
User-defined reports
DROP INDEX need_to_pass_i
Combine

18. 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
ADD_MONTHS function
Semicolon or forward slash
Re-create

19. It is always good practice to include the old values in the WHERE clause of UPDATE to ensure that another session or user _________________ in the meantime.
Multiple rows
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.
Structured Query Language
Has not changed the name

20. List the different types of data dictionary views.
USER_ - ALL_ - DBA_ - and V$
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
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
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.

21. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Optional
Null
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
ANSI

22. The using clause of the ANSI join syntax always assumes an...
Equijoin and identical column names
DECODE
Asterisk
KEEP INDEX

23. The focus of the logical data model is _____________
Scale
Static or dynamic
Business problems
ALL_OBJECTS

24. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
Nested
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
+
ALL_OBJECTS

25. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
ALL_OBJECTS
DECODE
A correct statement
Equijoin and correlated subquery

26. What is the data type you can use if you need an unlimited number of textual characters?
Black
CLOB
Location of the file
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

27. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
INTERSECT
Both MIN and MAX
Primary key/foreign key
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.

28. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Any
Optional
Twice

29. You use the _________ keyword to add columns or constraints to a table.
INTERSECT
BETWEEN
Any
ADD

30. The ALTER SESSION statement can change the session's...
IN ANY ALL SOME
Time zone
Fractional seconds
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

31. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Retrieve
V$
Twice
BETWEEN

32. Subqueries can return...
**** 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
Structured Query Language
All caps
Multiple rows

33. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
12:00:00 A.M
Semicolon or forward slash
V$
SET clause of an UPDATE statement

34. The query retrieves more than 5 to 15 percent of the rows
Any
ROLLBACK
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Equijoin

35. Explain what is found in the All Objects report folder in SQL Developer
ALL_OBJECTS
4
Script
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

36. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
ROLLBACK
ADD_MONTHS function
Self-joins
Details

37. The COUNT function is useful for determining whether a table has...
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.
Data
Three
4

38. _________ rows are not allowed in the relational model.
Orphan
SUM
DD-MON-YY
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

39. What does SQL stand for?
UNION operator
Structured Query Language
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.
When the table is small (unless your business rules require you too)

40. What do views do that makes them so useful for security?
Hide data
User-defined reports
Common column value
All caps

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

42. The _______ operator determines common rows.
INTERSECT
Details
Both MIN and MAX
2000

43. Which format mask displays December 31st - 1999?
Common column value
Run scripts
Month ddth - yyyy
LENGTH

44. The key that most database designers prefer and is chosen by the system is the _____________ key.
+
Surrogate
Details
ALL_OBJECTS

45. _________________ relationships are always optional relationships.
@ or START
Recursive
SET clause of an UPDATE statement
2000

46. To join four tables - you must have at least _____ join conditions.
Re-create
Three
Parent table
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

47. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
Primary key/foreign key
Run scripts
ROLLBACK
@ or START

48. UNION ALL does not sort - therefore it is less time consuming to use than the...
NATURAL JOIN
X
UNION operator
Equijoin

49. A calculation with a null always yields...
Re-create
Multiple rows
Another null
ANY

50. The ROWID pseudocolumn is not an ________- but it acts like one.
Nested
Actual column
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Equijoin