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 query retrieves more than 5 to 15 percent of the rows
Implicit conversion
The indexed column is part of an expression. In this case - consider creating a function-based index instead
CLOB
ADD

2. Explain in detail the impact of INDEXES and how they are used by Oracle.
Data
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
ON
Surrogate

3. Self-joins are useful for performing comparisons and checking for ___________ in data.
NATURAL JOIN
Extra spaces
VALUES clause of an INSERT statement
Inconsistencies

4. 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.
F12
Business problems
Has not changed the name
All

5. Another name for a data dictionary view is __________________ or _________________.
Static or dynamic
ANY
RUN SCRIPT
SUM

6. A calculation with a null always yields...
INNER JOIN
Another null
Both MIN and MAX
FALSE

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

8. The _____________ clause determines how rows are grouped.
DICT_COLUMNS
GROUP BY
Export Data
INNER JOIN

9. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Optional
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
X

10. It is considered good form to put all SQL commands in...
FALSE
Inconsistencies
UNION operator
All caps

11. 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
Mathematical equations
Twice
Combine

12. Explicit data type conversion is preferable to Oracle's...
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Implicit conversion
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
Asterisk

13. A transaction is a...
Logical unit of work
DECODE
A correct statement
CLOB

14. To equijoin a table with another table involves matching the...
+
@ or START
Run scripts
Common column value

15. Converting a text literal to DATE format requires the use of the...
TO_CHAR function
Null
ROLLBACK
DICT_COLUMNS

16. You can pass parameters from the operating system when ___________ in SQl*Plus
X
Recursive
Black
Running a script file

17. In SQL Developer field names and table names are shown in the color _______.
Black
Equijoin and identical column names
Export Data
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

18. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
Three
**** Select a row from the outer query - Determine the value of the correlated column - Execute the inner query for each record of the outer query. - Feed the results of the inner query to the outer query and evaluate it. It if satisfies the criteria
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
User-defined reports

19. The ______ operator compares a list of values for equivalency
Columns
99.99
ANY
LIKE

20. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
A correct statement
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
INNER JOIN
IN ANY ALL SOME

21. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
Groups
Time zone
TO_CHAR function
4

22. SELECT LIST of a subquery in an INSERT statement
Location of the file
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
RENAME just_two_more_weeks TO finally_i
SET clause of an UPDATE statement

23. The using clause of the ANSI join syntax always assumes an...
A correct statement
Equijoin and identical column names
UNION operator
Mathematical equations

24. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
LENGTH
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.
ADD

25. LINESIZE
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Black
Another null
Command returns the number of records returned by a query.

26. What do views do that makes them so useful for security?
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Hide data
Self-joins
Month ddth - yyyy

27. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
DECODE
Asterisk
Any
BETWEEN

28. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
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.
User-defined reports
Any
FALSE

29. __________________ inserts the current date and time.
SYSDATE
DD-MON-YY
Recursive
Nested

30. The same function can be used ______ in a SELECT statement.
Non-matching rows
CLOB
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Twice

31. Dynamic views begin with a ___________.
B-Tree Index
Retrieve
Three
V$

32. Checks whether any value in the list makes the condition true.
V$
DROP INDEX need_to_pass_i
Business problems
Any

33. The COUNT function is useful for determining whether a table has...
CLOB
DD-MON-YY
Data
Orphan

34. The ALTER SESSION statement can change the session's...
All
Time zone
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
@ or START

35. How many characters can be stored in the CHAR column/field?
Scalar
X
2000
Fractional seconds

36. The TRUNC function on a date without a format model truncates the timestamp to...
Scale
X
12:00:00 A.M
RUN SCRIPT

37. SELECT * FROM instructor ORDER BY phone
Optional
Black
A correct statement
Non-matching rows

38. What does SQL stand for?
Structured Query Language
SELECT SYSDATE+1/96 - FROM dual
12:00:00 A.M
X

39. A join across three tables can be expressed with the ________ join syntax.
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.
Correlated
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.
ANSI

40. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
NATURAL JOIN
SET clause of an UPDATE statement
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
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

41. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
DICTIONARY
Twice
FALSE
Scale

42. List the reasons that a subquery could not be unnested.
User-defined reports
Static or dynamic
B-Tree Index
X

43. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
999999.9999
Fractional seconds
F12
Location of the file

44. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
Scale
Static or dynamic
12:00:00 A.M
@ or START

45. Which format mask displays December 31st - 1999?
ADD_MONTHS function
Month ddth - yyyy
Time zone
ROLLBACK

46. What is the name of the key automatically generated from a sequence?
TO_CHAR function
Default is 14; when you use the default - you repeat the heading every 14 rows.
Same
Surrogate key

47. Joins involving multiple columns must always follow the ____________ relationship path.
Primary key/foreign key
MINUS
ALL_OBJECTS
Precision

48. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
X
Surrogate
Results
Columns

49. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
Recursive
MINUS
Same
ALL_OBJECTS

50. Returns rows if the condition is true for all the values in the list
Leading-edge
All
INTERSECT
Control - Revoke - Define - TRUNCATE Manipulate - MERGE