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. SELECT * FROM instructor ORDER BY phone
Columns
Equijoin
A correct statement
Three

2. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
999999.9999
Details
ADD_MONTHS function
Export Data

3. When is it not a good idea to use an index?
When the table is small (unless your business rules require you too)
FALSE
Three
Recursive

4. Which format mask displays December 31st - 1999?
Three
Month ddth - yyyy
Business problems
RUN SCRIPT

5. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Script
Has not changed the name
RIGHT LEFT JOIN
A correct statement

6. The DUAL table can be used for...
Recursive
SELECT SYSDATE+1/96 - FROM dual
Testing functions
Correlated

7. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
CLOB
Rounded edges
ALL_OBJECTS
Static or dynamic

8. What does the following function return? SELECT LENGTH(NULL) - FROM dual
TO_CHAR function
SUM
Null
**** 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

9. _________________ relationships are always optional relationships.
Fractional seconds
COMMIT
Recursive
Precision

10. The ________ operator cannot compare against a list of values.
Nested
INNER JOIN
STORAGE
LIKE

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

12. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
User-defined reports
Structured Query Language
Retrieve
Rounded edges

13. Checks whether any value in the list makes the condition true.
Any
DICT_COLUMNS
Equijoin
Multiple rows

14. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
Groups
ADD
X
User-defined reports

15. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
IN ANY ALL SOME
Same
Leading-edge
FALSE

16. Converting a text literal to DATE format requires the use of the...
When the table is small (unless your business rules require you too)
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.
TO_CHAR function
Combine

17. The focus of the logical data model is _____________
Parent table
ALL_OBJECTS
Business problems
DICTIONARY

18. Write a solution that adds 20 minutes to a given date.
SELECT SYSDATE+1/96 - FROM dual
Default is 14; when you use the default - you repeat the heading every 14 rows.
Equijoin and correlated subquery
DICT_COLUMNS

19. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Logical unit of work
Any
4
Equijoin

20. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Run scripts
Combine
Running a script file
X

21. In a view you can give a column a different name than it has in the...
12:00:00 A.M
Any
Actual column
Parent table

22. The _______ operator determines common rows.
Command returns the number of records returned by a query.
UNION
Testing functions
INTERSECT

23. Explicit data type conversion is preferable to Oracle's...
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Implicit conversion
Any
Multiple rows

24. What is the most common DATE format found in SQL?
VALUES clause of an INSERT statement
DD-MON-YY
Surrogate
VALUES

25. Explain what a static data dictionary view is and how it is used
Three
DD-MON-YY
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.
Leading-edge

26. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Fractional seconds
NATURAL JOIN
TO_CHAR function
COMMIT

27. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
ALL_OBJECTS
Optional
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.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

28. The query retrieves more than 5 to 15 percent of the rows
Retrieve
The indexed column is part of an expression. In this case - consider creating a function-based index instead
12:00:00 A.M
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. 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.
RIGHT LEFT JOIN
SYSDATE
Parent table
Fractional seconds

30. What is the name of the key automatically generated from a sequence?
Surrogate key
TO_CHAR function
Same
All

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

32. You use the _________ keyword to add columns or constraints to a table.
Location of the file
Leading-edge
@ or START
ADD

33. The ROWID pseudocolumn is not an ________- but it acts like one.
Inconsistencies
SELECT DISTINCT menu FROM restaurant
Actual column
F12

34. Queries that are found in parentheses are done first - just as in the order of operations when doing...
STORAGE
Mathematical equations
ANY
RENAME just_two_more_weeks TO finally_i

35. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
DESC dict;
Fractional seconds
B-Tree Index
Extra spaces

36. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
X
FALSE
Actual column
Structured Query Language

37. The TRUNC function on a date without a format model truncates the timestamp to...
12:00:00 A.M
BETWEEN
FALSE
Primary key/foreign key

38. Explain how Substitution Variables are used when writing scripts.
Inconsistencies
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.
USER_ - ALL_ - DBA_ - and V$
Location of the file

39. To equijoin a table with another table involves matching the...
Testing functions
4000
INTERSECT
Common column value

40. The purpose of the HAVING clause is to eliminate___________.
Null
2000
Groups
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

41. SELECT LIST of a subquery in an INSERT statement
STORAGE
SET clause of an UPDATE statement
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
Same

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

43. 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.
A correct statement
FALSE
IN ANY ALL SOME
Non-matching rows

44. List the different types of data dictionary views.
USER_ - ALL_ - DBA_ - and V$
Mathematical equations
Time zone
Any

45. Joins involving multiple columns must always follow the ____________ relationship path.
Actual column
Primary key/foreign key
Non-matching rows
UNION operator

46. The same function can be used ______ in a SELECT statement.
VALUES
MINUS
Nested
Twice

47. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
KEEP INDEX
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Fractional seconds
ADD

48. What is the keyboard shortcut for the ROLLBACK command?
FALSE
USER_ - ALL_ - DBA_ - and V$
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
F12

49. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Both MIN and MAX
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
DECODE
Groups

50. __________________ inserts the current date and time.
Equijoin and correlated subquery
ROLLBACK
UNION operator
SYSDATE