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. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Combine
SET clause of an UPDATE statement
Inconsistencies
Equijoin

2. _________________ relationships are always optional relationships.
STORAGE
Recursive
ADD_MONTHS function
All caps

3. The ____________ command makes changes permanent.
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.
COMMIT
ADD_MONTHS function
@ or START

4. In a view you can give a column a different name than it has in the...
Fractional seconds
BETWEEN
Parent table
Static or dynamic

5. What is the main difference between the NOT EXISTS and the NOT IN operators?
The way NULL values are treated
DROP INDEX need_to_pass_i
LIKE
Run scripts

6. What do views do that makes them so useful for security?
TO_CHAR function
Hide data
LIKE
Crow's foot

7. _________ rows are not allowed in the relational model.
Hide data
FALSE
Orphan
Results

8. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
USER_ - ALL_ - DBA_ - and V$
X
Logical unit of work
Any

9. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
DROP INDEX need_to_pass_i
Location of the file
SELECT DISTINCT menu FROM restaurant
Recursive

10. Self-joins are useful for performing comparisons and checking for ___________ in data.
IN ANY ALL SOME
Surrogate key
VALUES clause of an INSERT statement
Inconsistencies

11. Another name for a data dictionary view is __________________ or _________________.
KEEP INDEX
@ or START
Same
Static or dynamic

12. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Script
Export Data
Non-matching rows
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.

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

14. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
SELECT DISTINCT menu FROM restaurant
X
Rounded edges
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

15. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
Month ddth - yyyy
IN ANY ALL SOME
X
DESC dict;

16. The COUNT function uses the __________ wildcard.
Asterisk
Rounded edges
Correlated
Retrieve

17. When you run a script in SQL*Plus you must give the...
Location of the file
DICT_COLUMNS
VALUES clause of an INSERT statement
Inconsistencies

18. Explain what is found in the All Objects report folder in SQL Developer
Re-create
Nested
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
4

19. To join four tables - you must have at least _____ join conditions.
RIGHT LEFT JOIN
Three
A correct statement
Another null

20. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
NATURAL JOIN
ANY
LENGTH
INNER JOIN

21. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
The way NULL values are treated
Semicolon or forward slash
BETWEEN
FALSE

22. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
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.
Implicit conversion
RIGHT LEFT JOIN

23. Subqueries can return...
Combine
Multiple rows
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Extra spaces

24. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Actual column
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
DD-MON-YY
RUN SCRIPT

25. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
ROLLBACK
IN ANY ALL SOME
Extra spaces
SELECT SYSDATE+1/96 - FROM dual

26. The ROWID pseudocolumn is not an ________- but it acts like one.
STORAGE
Actual column
COMMIT
Self-joins

27. Checks whether any value in the list makes the condition true.
Any
DICTIONARY
Structured Query Language
Columns

28. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
UNION
Structured Query Language
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.
MINUS

29. The focus of the logical data model is _____________
Business problems
RENAME just_two_more_weeks TO finally_i
ROLLBACK
SUM

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. 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?
Business problems
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
DESC dict;
DICTIONARY

32. The TRUNC function on a date without a format model truncates the timestamp to...
SUM
RIGHT LEFT JOIN
Equijoin
12:00:00 A.M

33. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
STORAGE
Month ddth - yyyy
INTERSECT
Both MIN and MAX

34. How many characters can you have using the VARCHAR data type?
Business problems
DICT_COLUMNS
Committed
4000

35. To equijoin a table with another table involves matching the...
Black
DESC dict;
Common column value
Hide data

36. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
USER_ - ALL_ - DBA_ - and V$
SYSDATE
When the table is small (unless your business rules require you too)
FALSE

37. Write a statement for SQL*Plus that shows the dictionary.
DESC dict;
Time zone
RIGHT LEFT JOIN
Default is 14; when you use the default - you repeat the heading every 14 rows.

38. Explain the steps needed to run a script in SQL Developer
MODIFY
UNION
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.
When the table is small (unless your business rules require you too)

39. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Correlated
DICTIONARY
**** 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
Crow's foot

40. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
SUM
Surrogate key
Asterisk
@ or START

41. __________________ inserts the current date and time.
Recursive
SYSDATE
Running a script file
RENAME just_two_more_weeks TO finally_i

42. UNION ALL does not sort - therefore it is less time consuming to use than the...
COMMIT
UNION operator
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.
Parent table

43. The COUNT function is useful for determining whether a table has...
Same
Correlated
Data
Equijoin

44. Oracle uses the outer join operator to indicate that nulls are shown for...
X
KEEP INDEX
Non-matching rows
Orphan

45. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
DICT_COLUMNS
INNER JOIN
Export Data
All caps

46. You use the _________ keyword to add columns or constraints to a table.
CLOB
ADD
The way NULL values are treated
INTERSECT

47. SELECT LIST of a subquery in an INSERT statement
ON
DESC dict;
SET clause of an UPDATE statement
4000

48. How many characters can be stored in the CHAR column/field?
2000
INTERSECT
Any
RIGHT LEFT JOIN

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

50. You can use the ________________ to subtract months from a given date.
ADD_MONTHS function
Rounded edges
Common column value
Multiple rows