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 revert back to a state that happened before you ran your change statement - you issue a ____________ command.
Actual column
Fractional seconds
Recursive
ROLLBACK

2. How many characters can you have using the VARCHAR data type?
2000
Month ddth - yyyy
Semicolon or forward slash
4000

3. Explain what a static data dictionary view is and how it is used
SUM
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.
LENGTH
UNION operator

4. 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.
MINUS
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
IN ANY ALL SOME

5. Which operator is most commonly used?
ANSI
**** 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
Columns
UNION

6. When you run a script in SQL*Plus you must give the...
SYSDATE
B-Tree Index
INNER JOIN
Location of the file

7. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
@ or START
Columns
Rounded edges
Hide data

8. The purpose of the HAVING clause is to eliminate___________.
Static or dynamic
Groups
RUN SCRIPT
STORAGE

9. 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.
The way NULL values are treated
SET clause of an UPDATE statement
Has not changed the name
Script

10. Dynamic views begin with a ___________.
All
Surrogate
V$
Both MIN and MAX

11. What is the first column of a composite index called?
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Leading-edge
LENGTH
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.

12. You can pass parameters from the operating system when ___________ in SQl*Plus
Running a script file
FALSE
Business problems
Surrogate key

13. What is the most common DATE format found in SQL?
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.
Columns
DD-MON-YY
Equijoin and correlated subquery

14. What is Oracle's most popular index storage structure?
F12
STORAGE
B-Tree Index
SELECT SYSDATE+1/96 - FROM dual

15. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
Correlated
User-defined reports
Hide data
BETWEEN

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

17. List the steps performed by a correlated subquery.
Null
Equijoin
Equijoin and identical column names
**** 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

18. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
Equijoin
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Multiple rows
Export Data

19. What does the following function return? SELECT LENGTH(NULL) - FROM dual
The way NULL values are treated
Null
ALL_OBJECTS
X

20. The _______ operator determines common rows.
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
INTERSECT
TO_CHAR function
Orphan

21. Explain how Substitution Variables are used when writing scripts.
Extra spaces
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.
X
Orphan

22. You can use the ________________ to subtract months from a given date.
Logical unit of work
Same
CLOB
ADD_MONTHS function

23. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Equijoin
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
User-defined reports

24. Match each of the SQL commands on the left with a verb from the list.
Asterisk
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Both MIN and MAX
SUM

25. To restart a sequence at a lower number - you can drop and ________ the sequence.
All
Re-create
Leading-edge
+

26. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
VALUES
Scale
Static or dynamic

27. When is it not a good idea to use an index?
When the table is small (unless your business rules require you too)
Script
STORAGE
**** 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

28. It is considered good form to put all SQL commands in...
X
All caps
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
SET clause of an UPDATE statement

29. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
Has not changed the name
Surrogate key
Another null

30. LINESIZE
SELECT SYSDATE+1/96 - FROM dual
Extra spaces
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Implicit conversion

31. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Results
Recursive
ON

32. What is the tab in SQL Developer where data dictionary view information can be found?
UTC
Details
DECODE
Self-joins

33. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
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
SELECT DISTINCT menu FROM restaurant
DESC dict;

34. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
Equijoin and correlated subquery
X
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
Columns

35. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
V$
Combine
NATURAL JOIN
999999.9999

36. Subqueries can return...
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
RENAME just_two_more_weeks TO finally_i
Multiple rows
Both MIN and MAX

37. The ________ operator cannot compare against a list of values.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
LIKE
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
DECODE

38. The key that most database designers prefer and is chosen by the system is the _____________ key.
DECODE
GROUP BY
Surrogate
X

39. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Scale
FALSE
ADD
999999.9999

40. Which one of the following string functions tells you how many characters are in a string?
Logical unit of work
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.
LENGTH
Mathematical equations

41. The TRUNC function on a date without a format model truncates the timestamp to...
12:00:00 A.M
Running a script file
TO_CHAR function
**** 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

42. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Optional
The way NULL values are treated
Implicit conversion
Script

43. Write a solution that adds 20 minutes to a given date.
Equijoin and correlated subquery
SELECT SYSDATE+1/96 - FROM dual
V$
The way NULL values are treated

44. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
Orphan
ALL_OBJECTS
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.
Time zone

45. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
KEEP INDEX
VALUES clause of an INSERT statement
12:00:00 A.M
SYSDATE

46. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
SYSDATE
@ or START
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
BETWEEN

47. The COUNT function uses the __________ wildcard.
Equijoin and identical column names
SYSDATE
Asterisk
Self-joins

48. The ______ operator compares a list of values for equivalency
UNION
ANY
The indexed column is part of an expression. In this case - consider creating a function-based index instead
MODIFY

49. In a view you can give a column a different name than it has in the...
Parent table
SELECT DISTINCT menu FROM restaurant
LIKE
Null

50. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
Business problems
SYSDATE
Fractional seconds
Results