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. Joins involving multiple columns must always follow the ____________ relationship path.
Scale
SET clause of an UPDATE statement
Primary key/foreign key
NATURAL JOIN

2. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
999999.9999
All caps
@ or START
**** 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

3. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
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
Semicolon or forward slash
TO_CHAR function
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

4. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Same
Testing functions
Parent table
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

5. The ________ operator cannot compare against a list of values.
LIKE
CLOB
X
RUN SCRIPT

6. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
User-defined reports
Re-create
ADD
Fractional seconds

7. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Correlated
ANY
Implicit conversion
Scalar

8. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
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
FALSE
Orphan
MODIFY

9. What are the main components of a ROWID?
Twice
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.
MODIFY

10. The ROWID pseudocolumn is not an ________- but it acts like one.
Actual column
Equijoin
Running a script file
X

11. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Month ddth - yyyy
NATURAL JOIN
Equijoin and correlated subquery
STORAGE

12. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
All caps
Black
DICT_COLUMNS
Another null

13. Write a statement for SQL*Plus that shows the dictionary.
DESC dict;
Combine
FALSE
Correlated

14. List the different types of data dictionary views.
USER_ - ALL_ - DBA_ - and V$
Month ddth - yyyy
SELECT SYSDATE+1/96 - FROM dual
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

15. Explain what is found in the All Objects report folder in SQL Developer
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
GROUP BY
Self-joins
LIKE

16. The key that most database designers prefer and is chosen by the system is the _____________ key.
Self-joins
Inconsistencies
Location of the file
Surrogate

17. SELECT LIST of a subquery in an INSERT statement
X
SET clause of an UPDATE statement
SELECT SYSDATE+1/96 - FROM dual
FALSE

18. 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.
Structured Query Language
Mathematical equations
X

19. The DUAL table can be used for...
Testing functions
4
VALUES
Optional

20. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Inconsistencies
ADD_MONTHS function
Nested
Both MIN and MAX

21. The TRUNC function on a date without a format model truncates the timestamp to...
USER_ - ALL_ - DBA_ - and V$
BETWEEN
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.
12:00:00 A.M

22. PAGESIZE
BETWEEN
Run scripts
TO_CHAR function
Default is 14; when you use the default - you repeat the heading every 14 rows.

23. What do views do that makes them so useful for security?
Hide data
Non-matching rows
X
Surrogate key

24. Explain the steps needed to run a script in SQL Developer
99.99
LENGTH
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.
Another null

25. The __________ operator is inclusive of the two values specified.
Another null
Null
RIGHT LEFT JOIN
BETWEEN

26. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
Run scripts
RUN SCRIPT
VALUES clause of an INSERT statement
STORAGE

27. What is the tab in SQL Developer where data dictionary view information can be found?
Details
Hide data
Surrogate
DICT_COLUMNS

28. The _______ function adds values together for a group of rows.
Command returns the number of records returned by a query.
Rounded edges
COMMIT
SUM

29. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
2000
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Combine

30. The ______ operator compares a list of values for equivalency
ANY
IN ANY ALL SOME
@ or START
GROUP BY

31. What is the first column of a composite index called?
4
Command returns the number of records returned by a query.
Leading-edge
When the table is small (unless your business rules require you too)

32. The COUNT function uses the __________ wildcard.
**** 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
GROUP BY
Asterisk
MINUS

33. Another name for a data dictionary view is __________________ or _________________.
Another null
Static or dynamic
RUN SCRIPT
DESC dict;

34. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Results
Structured Query Language
SET clause of an UPDATE statement
Crow's foot

35. You can use the ________________ to subtract months from a given date.
ADD_MONTHS function
ON
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
X

36. You use the _________ keyword to add columns or constraints to a table.
VALUES
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
All caps
ADD

37. 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.
INTERSECT
DROP INDEX need_to_pass_i
Non-matching rows
FALSE

38. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
Any
INTERSECT
IN ANY ALL SOME
KEEP INDEX

39. _________ rows are not allowed in the relational model.
User-defined reports
Orphan
Precision
ADD

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

41. Subqueries can return...
DESC dict;
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
Multiple rows
SUM

42. The focus of the logical data model is _____________
Business problems
Retrieve
Recursive
BETWEEN

43. Self-joins are useful for performing comparisons and checking for ___________ in data.
Inconsistencies
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
SUM
DD-MON-YY

44. Explicit data type conversion is preferable to Oracle's...
12:00:00 A.M
Implicit conversion
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
TO_CHAR function

45. ____________ are useful for analyzing and exploring the relationships within your data
ANSI
Self-joins
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
Correlated

46. Oracle uses the outer join operator to indicate that nulls are shown for...
Non-matching rows
Scale
Any
**** 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

47. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
FALSE
Script
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Nested

48. The _______ operator determines common rows.
INTERSECT
Equijoin and correlated subquery
Combine
BETWEEN

49. 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?
ANSI
ALL_OBJECTS
DICTIONARY
Results

50. The purpose of the HAVING clause is to eliminate___________.
INNER JOIN
KEEP INDEX
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Groups