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. The _______ function lets you perform if-then-else functionality with the SQL language.
VALUES
ON
Re-create
DECODE

2. 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.
Data
GROUP BY
99.99

3. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
Scalar
ROLLBACK
INTERSECT
Fractional seconds

4. 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
Black
Committed
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

5. The COUNT function is useful for determining whether a table has...
Command returns the number of records returned by a query.
Data
CLOB
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

6. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
Export Data
DICT_COLUMNS
Both MIN and MAX
BETWEEN

7. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
Precision
ANSI
VALUES clause of an INSERT statement
Columns

8. Checks whether any value in the list makes the condition true.
Any
Extra spaces
All
Asterisk

9. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
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.
Correlated
ADD_MONTHS function
Asterisk

10. What is Oracle's most popular index storage structure?
@ or START
DESC dict;
Hide data
B-Tree Index

11. Write the syntax to rename index just_two_more_weeks_i to finally_i
RENAME just_two_more_weeks TO finally_i
Self-joins
Mathematical equations
FALSE

12. LINESIZE
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
2000
Has not changed the name
Common column value

13. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Both MIN and MAX
Semicolon or forward slash
RIGHT LEFT JOIN
KEEP INDEX

14. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
Command returns the number of records returned by a query.
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.
IN ANY ALL SOME
Three

15. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
STORAGE
GROUP BY
NATURAL JOIN
SET clause of an UPDATE statement

16. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
The way NULL values are treated
NATURAL JOIN
Combine
Parent table

17. Match each of the SQL commands on the left with a verb from the list.
12:00:00 A.M
GROUP BY
X
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

18. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
User-defined reports
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Business problems
Orphan

19. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
X
Surrogate
CLOB
Null

20. Which format mask displays December 31st - 1999?
Month ddth - yyyy
LENGTH
Committed
V$

21. The DUAL table can be used for...
Testing functions
X
Committed
SET clause of an UPDATE statement

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

23. The COUNT function uses the __________ wildcard.
A correct statement
LENGTH
Asterisk
All caps

24. The _______ function adds values together for a group of rows.
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
SYSDATE
SUM
ROLLBACK

25. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
Scale
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.
DICT_COLUMNS
SUM

26. Self-joins are useful for performing comparisons and checking for ___________ in data.
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
Inconsistencies
Another null
Semicolon or forward slash

27. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
DROP INDEX need_to_pass_i
ALL_OBJECTS
999999.9999
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.

28. What is the data type you can use if you need an unlimited number of textual characters?
ALL_OBJECTS
Inconsistencies
CLOB
FALSE

29. _________________ relationships are always optional relationships.
Crow's foot
Recursive
Surrogate
ADD_MONTHS function

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

31. You can pass parameters from the operating system when ___________ in SQl*Plus
X
Multiple rows
Running a script file
Testing functions

32. In SQL Developer field names and table names are shown in the color _______.
Combine
Black
Scale
Scalar

33. When you run a script in SQL*Plus you must give the...
Location of the file
ANY
IN ANY ALL SOME
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

34. It is considered good form to put all SQL commands in...
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
All caps
ANSI
Data

35. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
X
ANY
Surrogate key
Optional

36. ____________ are useful for analyzing and exploring the relationships within your data
VALUES clause of an INSERT statement
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Self-joins
F12

37. Checks whether any value in the list makes the condition true.
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.
DECODE
Export Data
Any

38. List the reasons that a subquery could not be unnested.
Black
X
Recursive
VALUES

39. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
KEEP INDEX
Combine
RIGHT LEFT JOIN
Groups

40. To restart a sequence at a lower number - you can drop and ________ the sequence.
Re-create
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.
DESC dict;
Scale

41. The ALTER SESSION statement can change the session's...
USER_ - ALL_ - DBA_ - and V$
@ or START
Time zone
Self-joins

42. A transaction is a...
Surrogate
Twice
Logical unit of work
Location of the file

43. Explain how the SPOOL command is used and what program it can be used in.
Correlated
All caps
Any
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

44. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Parent table
Script
+
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.

45. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
GROUP BY
Semicolon or forward slash
ROLLBACK
SELECT SYSDATE+1/96 - FROM dual

46. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Black
Surrogate key
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
Command returns the number of records returned by a query.

47. Comparison operators can compare multiple _________________.
KEEP INDEX
SELECT SYSDATE+1/96 - FROM dual
Black
Columns

48. 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.
FALSE
Leading-edge
Script
Same

49. Users can only see changes made to a database once they have been...
DECODE
Leading-edge
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Committed

50. __________________ inserts the current date and time.
Has not changed the name
B-Tree Index
SYSDATE
Three