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. _________ rows are not allowed in the relational model.
Orphan
The indexed column is part of an expression. In this case - consider creating a function-based index instead
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.
Null

2. The _______ function lets you perform if-then-else functionality with the SQL language.
Run scripts
MODIFY
Month ddth - yyyy
DECODE

3. SQL*Plus is used more than SQL Developer when you need to...
Month ddth - yyyy
F12
Run scripts
DROP INDEX need_to_pass_i

4. UNION ALL does not sort - therefore it is less time consuming to use than the...
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.
Optional
Semicolon or forward slash
UNION operator

5. 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
Location of the file
Surrogate key
DICTIONARY

6. To equijoin a table with another table involves matching the...
Common column value
Equijoin
Command returns the number of records returned by a query.
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

7. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
@ or START
RUN SCRIPT
4000
VALUES clause of an INSERT statement

8. List the reasons that a subquery could not be unnested.
X
Nested
The way NULL values are treated
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

9. Joins involving multiple columns must always follow the ____________ relationship path.
INTERSECT
Month ddth - yyyy
Fractional seconds
Primary key/foreign key

10. A join across three tables can be expressed with the ________ join syntax.
Any
Time zone
Run scripts
ANSI

11. List 6 Table-Related Menu Choices
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
VALUES clause of an INSERT statement
Recursive
F12

12. The ALTER SESSION statement can change the session's...
Time zone
Script
ROLLBACK
Mathematical equations

13. The __________ operator is inclusive of the two values specified.
V$
BETWEEN
Primary key/foreign key
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.

14. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
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
X
KEEP INDEX
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

15. You use the _________ keyword to add columns or constraints to a table.
RUN SCRIPT
Both MIN and MAX
Retrieve
ADD

16. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Implicit conversion
Equijoin
Surrogate
Fractional seconds

17. The ROWID pseudocolumn is not an ________- but it acts like one.
F12
+
X
Actual column

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

19. Users can only see changes made to a database once they have been...
Default is 14; when you use the default - you repeat the heading every 14 rows.
Committed
Command returns the number of records returned by a query.
Same

20. What do views do that makes them so useful for security?
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
12:00:00 A.M
Hide data
F12

21. PAGESIZE
Default is 14; when you use the default - you repeat the heading every 14 rows.
Groups
ANSI
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

22. Explain what is found in the All Objects report folder in SQL Developer
When the table is small (unless your business rules require you too)
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
VALUES clause of an INSERT statement
Run scripts

23. Checks whether any value in the list makes the condition true.
Any
Rounded edges
Testing functions
Results

24. The time zone displacement value indicates the time difference from _______.
F12
BETWEEN
UTC
Fractional seconds

25. To join four tables - you must have at least _____ join conditions.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
999999.9999
Three
TO_CHAR function

26. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
12:00:00 A.M
VALUES
STORAGE
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

27. When you INSERT new information into a table - it is followed with a _______ keyword that contains the information that is going to be added to the table.
VALUES
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
ROLLBACK
MINUS

28. How many characters can be stored in the CHAR column/field?
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
2000
Both MIN and MAX
RIGHT LEFT JOIN

29. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
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.
X
Another null
STORAGE

30. __________________ inserts the current date and time.
Combine
Another null
SYSDATE
DROP INDEX need_to_pass_i

31. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
UNION operator
Combine
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
X

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

33. NUMBER functions can be...
KEEP INDEX
Location of the file
Nested
Null

34. Which format mask displays December 31st - 1999?
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.
MODIFY
Month ddth - yyyy
ANSI

35. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
INNER JOIN
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.
BETWEEN

36. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
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
Null
Data
Equijoin and correlated subquery

37. What is the first column of a composite index called?
B-Tree Index
Leading-edge
Hide data
@ or START

38. The ________ operator cannot compare against a list of values.
Surrogate key
LIKE
DROP INDEX need_to_pass_i
INNER JOIN

39. The using clause of the ANSI join syntax always assumes an...
Testing functions
All caps
X
Equijoin and identical column names

40. Write a statement for SQL*Plus that shows the dictionary.
DESC dict;
TO_CHAR function
SUM
Asterisk

41. Dynamic views begin with a ___________.
V$
Orphan
USER_ - ALL_ - DBA_ - and V$
Inconsistencies

42. What is the data type you can use if you need an unlimited number of textual characters?
RENAME just_two_more_weeks TO finally_i
CLOB
STORAGE
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.

43. When is it not a good idea to use an index?
All caps
ALL_OBJECTS
When the table is small (unless your business rules require you too)
VALUES

44. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
RENAME just_two_more_weeks TO finally_i
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.
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.
X

45. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
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.
Scale
Data
IN ANY ALL SOME

46. The DUAL table can be used for...
Testing functions
NATURAL JOIN
Surrogate
12:00:00 A.M

47. Subqueries can return...
A correct statement
The way NULL values are treated
Multiple rows
Surrogate

48. What is the maximum value for a column defined as NUMBER(4 -2)?
99.99
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
All
VALUES clause of an INSERT statement

49. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Both MIN and MAX
SUM
RUN SCRIPT
RENAME just_two_more_weeks TO finally_i

50. Self-joins are useful for performing comparisons and checking for ___________ in data.
SUM
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.
Inconsistencies
Equijoin and correlated subquery