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 _____________ clause determines how rows are grouped.
GROUP BY
TO_CHAR function
99.99
Inconsistencies

2. What is the main difference between the NOT EXISTS and the NOT IN operators?
Non-matching rows
Optional
ADD
The way NULL values are treated

3. _________________ relationships are always optional relationships.
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Script
Recursive
Structured Query Language

4. SELECT LIST of a subquery in an INSERT statement
SET clause of an UPDATE statement
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.
Export Data
Correlated

5. The ____________ command makes changes permanent.
Leading-edge
COMMIT
FALSE
99.99

6. 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?
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
Three
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
DICTIONARY

7. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Location of the file
DESC dict;
Crow's foot

8. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
X
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Time zone
Scale

9. What is the data type you can use if you need an unlimited number of textual characters?
CLOB
UTC
RUN SCRIPT
Rounded edges

10. Returns rows if the condition is true for all the values in the list
All
Any
Fractional seconds
Month ddth - yyyy

11. The time zone displacement value indicates the time difference from _______.
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
VALUES clause of an INSERT statement
KEEP INDEX
UTC

12. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
Another null
Three
Same

13. The focus of the logical data model is _____________
2000
STORAGE
Inconsistencies
Business problems

14. Which one of the following string functions tells you how many characters are in a string?
Running a script file
Any
Non-matching rows
LENGTH

15. To equijoin a table with another table involves matching the...
Common column value
Implicit conversion
UNION operator
Rounded edges

16. PAGESIZE
Default is 14; when you use the default - you repeat the heading every 14 rows.
Static or dynamic
Groups
Any

17. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
Script
MINUS
@ or START
Fractional seconds

18. Checks whether any value in the list makes the condition true.
Any
BETWEEN
2000
Columns

19. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
UNION
Any
IN ANY ALL SOME
LENGTH

20. Explain what a static data dictionary view is and how it is used
Primary key/foreign key
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.
Export Data
User-defined reports

21. The COUNT function uses the __________ wildcard.
Asterisk
ALL_OBJECTS
F12
Data

22. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Run scripts
999999.9999
FALSE
Hide data

23. You can pass parameters from the operating system when ___________ in SQl*Plus
Running a script file
A correct statement
Crow's foot
All caps

24. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
Has not changed the name
RENAME just_two_more_weeks TO finally_i
Scalar
Fractional seconds

25. Users can only see changes made to a database once they have been...
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
Committed
The way NULL values are treated
X

26. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
Testing functions
Mathematical equations
SELECT DISTINCT menu FROM restaurant
Null

27. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
ANSI
Correlated
X
BETWEEN

28. Checks whether any value in the list makes the condition true.
Actual column
Any
DICTIONARY
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.

29. You use the __________ keyword to change characteristics of a column.
ADD
RIGHT LEFT JOIN
MODIFY
The way NULL values are treated

30. __________________ inserts the current date and time.
KEEP INDEX
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
SUM
SYSDATE

31. Explain how the SPOOL command is used and what program it can be used in.
SELECT SYSDATE+1/96 - FROM dual
Optional
Twice
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

32. A calculation with a null always yields...
Another null
Details
ROLLBACK
Data

33. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
Multiple rows
Surrogate
FALSE
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

34. How many characters can you have using the VARCHAR data type?
Surrogate key
SELECT DISTINCT menu FROM restaurant
Equijoin and correlated subquery
4000

35. Write a statement for SQL*Plus that shows the dictionary.
RIGHT LEFT JOIN
DESC dict;
Location of the file
Script

36. SQL*Plus is used more than SQL Developer when you need to...
Run scripts
Equijoin and identical column names
V$
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

37. 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
Logical unit of work
99.99
4

38. The _______ function lets you perform if-then-else functionality with the SQL language.
DECODE
Equijoin
ALL_OBJECTS
Multiple rows

39. The same function can be used ______ in a SELECT 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
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.
Twice

40. The _______ function adds values together for a group of rows.
SUM
DROP INDEX need_to_pass_i
Static or dynamic
Equijoin and correlated subquery

41. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
ROLLBACK
Any
STORAGE
Crow's foot

42. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
ROLLBACK
DECODE
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

43. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
VALUES clause of an INSERT statement
12:00:00 A.M
Actual column
RUN SCRIPT

44. When you have issued a DELETE command - you can still ________ those rows and restore the information.
SYSDATE
CLOB
Retrieve
DECODE

45. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
MODIFY
Extra spaces
RUN SCRIPT
All

46. You can use the ________________ to subtract months from a given date.
Extra spaces
Nested
Re-create
ADD_MONTHS function

47. ____________ are useful for analyzing and exploring the relationships within your data
Self-joins
F12
Another null
DROP INDEX need_to_pass_i

48. The ROWID pseudocolumn is not an ________- but it acts like one.
Actual column
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Recursive
A correct statement

49. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
ADD
Export Data
All caps
Equijoin

50. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
ALL_OBJECTS
INNER JOIN
4
Details