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. To restart a sequence at a lower number - you can drop and ________ the sequence.
Recursive
Script
IN ANY ALL SOME
Re-create

2. The _______ function adds values together for a group of rows.
FALSE
Structured Query Language
F12
SUM

3. Write a statement that drops the index need_to_pass_i
Export Data
The way NULL values are treated
RIGHT LEFT JOIN
DROP INDEX need_to_pass_i

4. The ________ operator cannot compare against a list of values.
Optional
DROP INDEX need_to_pass_i
FALSE
LIKE

5. The ROWID pseudocolumn is not an ________- but it acts like one.
ALL_OBJECTS
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.
4000
Actual column

6. SELECT * FROM instructor ORDER BY phone
Hide data
A correct statement
Results
4000

7. What is the first column of a composite index called?
Leading-edge
Groups
Null
DESC dict;

8. What does SQL stand for?
Another null
Structured Query Language
Default is 14; when you use the default - you repeat the heading every 14 rows.
12:00:00 A.M

9. What is Oracle's most popular index storage structure?
BETWEEN
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
B-Tree Index
A correct statement

10. Match each of the SQL commands on the left with a verb from the list.
DECODE
SUM
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
FALSE

11. What is the maximum value for a column defined as NUMBER(4 -2)?
99.99
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Mathematical equations
CLOB

12. The purpose of the HAVING clause is to eliminate___________.
Black
Any
Default is 14; when you use the default - you repeat the heading every 14 rows.
Groups

13. A join across three tables can be expressed with the ________ join syntax.
KEEP INDEX
The way NULL values are treated
Running a script file
ANSI

14. 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?
Self-joins
FALSE
DICTIONARY
4

15. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Nested
Mathematical equations
INTERSECT
ALL_OBJECTS

16. The ____________ command makes changes permanent.
Structured Query Language
Semicolon or forward slash
COMMIT
Static or dynamic

17. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
X
Three
Correlated
The indexed column is part of an expression. In this case - consider creating a function-based index instead

18. What is the tab in SQL Developer where data dictionary view information can be found?
Testing functions
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Details
STORAGE

19. A calculation with a null always yields...
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
Another null
The indexed column is part of an expression. In this case - consider creating a function-based index instead
All

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

21. You use the _________ keyword to add columns or constraints to a table.
B-Tree Index
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
ADD
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

22. The time zone displacement value indicates the time difference from _______.
Scalar
Leading-edge
ON
UTC

23. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
VALUES
Fractional seconds
Non-matching rows
RUN SCRIPT

24. A _________ subquery returns a single column with a single row
4000
ANSI
Scalar
Rounded edges

25. You use the __________ keyword to change characteristics of a column.
MODIFY
Business problems
STORAGE
LIKE

26. A transaction is a...
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.
Twice
Logical unit of work
Details

27. List 4 aggregate functions
Script
Correlated
ANSI
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

28. What is the keyboard shortcut for the ROLLBACK command?
F12
TO_CHAR function
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
Crow's foot

29. What is the name of the key automatically generated from a sequence?
Implicit conversion
VALUES
Surrogate key
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.

30. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
2000
RUN SCRIPT
DICT_COLUMNS
Structured Query Language

31. ____________ are useful for analyzing and exploring the relationships within your data
KEEP INDEX
ANSI
Self-joins
Extra spaces

32. Subqueries can return...
Multiple rows
KEEP INDEX
Scale
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

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

34. List 6 Table-Related Menu Choices
Run scripts
Semicolon or forward slash
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
SYSDATE

35. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Data
Precision
X
NATURAL JOIN

36. 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.
Run scripts
Columns
Month ddth - yyyy

37. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Equijoin
SET clause of an UPDATE statement
Crow's foot
Equijoin and identical column names

38. Explain how Substitution Variables are used when writing scripts.
99.99
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.
DD-MON-YY
999999.9999

39. The using clause of the ANSI join syntax always assumes an...
SYSDATE
Equijoin and identical column names
Export Data
RENAME just_two_more_weeks TO finally_i

40. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
User-defined reports
Re-create
MINUS
STORAGE

41. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Rounded edges
RIGHT LEFT JOIN
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
12:00:00 A.M

42. Dynamic views begin with a ___________.
Groups
LIKE
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
V$

43. What is the most common DATE format found in SQL?
Equijoin and correlated subquery
Committed
When the table is small (unless your business rules require you too)
DD-MON-YY

44. Write a statement for SQL*Plus that shows the dictionary.
MODIFY
Time zone
Location of the file
DESC dict;

45. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
SET clause of an UPDATE statement
@ or START
Static or dynamic
Scale

46. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
DD-MON-YY
ROLLBACK
Correlated
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.

47. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Export Data
Correlated
Hide data

48. The COUNT function uses the __________ wildcard.
Any
Asterisk
MODIFY
2000

49. To equijoin a table with another table involves matching the...
FALSE
Business problems
Export Data
Common column value

50. The TRUNC function on a date without a format model truncates the timestamp to...
12:00:00 A.M
GROUP BY
Logical unit of work
Export Data