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. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
Leading-edge
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
X
NATURAL JOIN

2. You can use the ________________ to subtract months from a given date.
KEEP INDEX
Actual column
ADD_MONTHS function
Equijoin

3. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
SELECT DISTINCT menu FROM restaurant
When the table is small (unless your business rules require you too)
Month ddth - yyyy
Re-create

4. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
The way NULL values are treated
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
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.
Three

5. The same function can be used ______ in a SELECT statement.
Precision
Twice
4
Re-create

6. Explicit data type conversion is preferable to Oracle's...
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Implicit conversion
Another null
ROLLBACK

7. SQL*Plus is used more than SQL Developer when you need to...
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.
Both MIN and MAX
@ or START
Run scripts

8. What does SQL stand for?
Structured Query Language
Null
Has not changed the name
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

9. The COUNT function is useful for determining whether a table has...
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
VALUES
Data
USER_ - ALL_ - DBA_ - and V$

10. A calculation with a null always yields...
Results
RUN SCRIPT
Null
Another null

11. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
Extra spaces
Re-create
ROLLBACK
KEEP INDEX

12. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
IN ANY ALL SOME
Rounded edges
NATURAL JOIN
B-Tree Index

13. Which one of the following string functions tells you how many characters are in a string?
Business problems
Implicit conversion
DECODE
LENGTH

14. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Export Data
Results
ANY
RIGHT LEFT JOIN

15. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
INNER JOIN
VALUES clause of an INSERT statement
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Crow's foot

16. What is the keyboard shortcut for the ROLLBACK command?
Black
F12
Same
Equijoin

17. The time zone displacement value indicates the time difference from _______.
UTC
Semicolon or forward slash
Any
99.99

18. The DUAL table can be used for...
ON
Any
SELECT DISTINCT menu FROM restaurant
Testing functions

19. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
KEEP INDEX
Null
Inconsistencies
NATURAL JOIN

20. The RTRIM function is useful for eliminating ________ in a string.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Location of the file
Extra spaces
TO_CHAR function

21. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
ALL_OBJECTS
SELECT SYSDATE+1/96 - FROM dual
Primary key/foreign key
Testing functions

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

23. SELECT LIST of a subquery in an INSERT statement
X
SET clause of an UPDATE statement
User-defined reports
Orphan

24. Checks whether any value in the list makes the condition true.
KEEP INDEX
Any
RUN SCRIPT
LIKE

25. 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.
When the table is small (unless your business rules require you too)
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
FALSE
Surrogate key

26. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
The indexed column is part of an expression. In this case - consider creating a function-based index instead
4000
Semicolon or forward slash
Leading-edge

27. The using clause of the ANSI join syntax always assumes an...
Another null
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Orphan
Equijoin and identical column names

28. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
COMMIT
4000
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.
4

29. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Export Data
Business problems
+
999999.9999

30. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Surrogate key
BETWEEN
Columns
X

31. Dynamic views begin with a ___________.
V$
Parent table
IN ANY ALL SOME
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

32. PAGESIZE
BETWEEN
Default is 14; when you use the default - you repeat the heading every 14 rows.
COMMIT
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

33. List 4 aggregate functions
99.99
Precision
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

34. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Hide data
ANSI
FALSE
Both MIN and MAX

35. The focus of the logical data model is _____________
DICTIONARY
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
DROP INDEX need_to_pass_i
Business problems

36. _________ rows are not allowed in the relational model.
CLOB
Orphan
Scalar
Run scripts

37. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Precision
Equijoin and identical column names
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Same

38. Which format mask displays December 31st - 1999?
Export Data
V$
Data
Month ddth - yyyy

39. The _______ function adds values together for a group of rows.
ALL_OBJECTS
Extra spaces
SUM
X

40. The ALTER SESSION statement can change the session's...
User-defined reports
B-Tree Index
Time zone
IN ANY ALL SOME

41. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
LENGTH
Combine
ROLLBACK
Surrogate key

42. Write the syntax to rename index just_two_more_weeks_i to finally_i
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
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.
All caps
RENAME just_two_more_weeks TO finally_i

43. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Rounded edges
KEEP INDEX
CLOB

44. 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.
Has not changed the name
INTERSECT
Black
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

45. Checks whether any value in the list makes the condition true.
Any
Actual column
When the table is small (unless your business rules require you too)
Has not changed the name

46. __________________ inserts the current date and time.
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.
Extra spaces
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
SYSDATE

47. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
User-defined reports
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.
Actual column
RENAME just_two_more_weeks TO finally_i

48. The _______ operator determines common 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.
INTERSECT
X
Non-matching rows

49. List the reasons that a subquery could not be unnested.
X
Running a script file
Three
ON

50. What is Oracle's most popular index storage structure?
B-Tree Index
IN ANY ALL SOME
Common column value
Scale