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. Another name for a data dictionary view is __________________ or _________________.
@ or START
NATURAL JOIN
DECODE
Static or dynamic

2. In a view you can give a column a different name than it has in the...
Surrogate key
STORAGE
Parent table
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

3. SQL*Plus is used more than SQL Developer when you need to...
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
FALSE
Run scripts
B-Tree Index

4. The TRUNC function on a date without a format model truncates the timestamp to...
All
SELECT DISTINCT menu FROM restaurant
12:00:00 A.M
Same

5. Explain the steps needed to run a script in SQL Developer
Equijoin and correlated subquery
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.
**** 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
DD-MON-YY

6. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Any
Both MIN and MAX
V$
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.

7. What is the maximum value for a column defined as NUMBER(4 -2)?
DESC dict;
Optional
Scale
99.99

8. The ______ operator compares a list of values for equivalency
ANY
Self-joins
Common column value
SELECT DISTINCT menu FROM restaurant

9. Comparison operators can compare multiple _________________.
Black
Columns
Implicit conversion
RIGHT LEFT JOIN

10. The purpose of the HAVING clause is to eliminate___________.
FALSE
Export Data
Groups
Non-matching rows

11. The time zone displacement value indicates the time difference from _______.
UTC
DICT_COLUMNS
RUN SCRIPT
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

12. What do views do that makes them so useful for security?
SET clause of an UPDATE statement
99.99
LENGTH
Hide data

13. The focus of the logical data model is _____________
Surrogate key
Business problems
BETWEEN
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

14. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
V$
+
Equijoin and identical column names
Combine

15. The _______ function lets you perform if-then-else functionality with the SQL language.
RUN SCRIPT
DECODE
ANY
Fractional seconds

16. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
Export Data
+
Run scripts
Extra spaces

17. What are the main components of a ROWID?
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.
99.99
VALUES clause of an INSERT statement
Time zone

18. In SQL Developer field names and table names are shown in the color _______.
Common column value
Black
Mathematical equations
DESC dict;

19. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
STORAGE
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
**** 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

20. Oracle uses the outer join operator to indicate that nulls are shown for...
Mathematical equations
Non-matching rows
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.
All

21. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
V$
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Equijoin and correlated subquery
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.

22. The term ____________ ___________ is used to express a join that satisfies the join condition.
INNER JOIN
Implicit conversion
Any
Three

23. Users can only see changes made to a database once they have been...
FALSE
Committed
TO_CHAR function
GROUP BY

24. Which one of the following string functions tells you how many characters are in a string?
The indexed column is part of an expression. In this case - consider creating a function-based index instead
LENGTH
Recursive
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

25. 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
BETWEEN
Orphan
Month ddth - yyyy

26. What is the main difference between the NOT EXISTS and the NOT IN operators?
Rounded edges
Hide data
The way NULL values are treated
SUM

27. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Data
4
Black
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

28. List 6 Table-Related Menu Choices
DROP INDEX need_to_pass_i
Hide data
INTERSECT
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

29. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Mathematical equations
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
2000
ALL_OBJECTS

30. 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.
ADD
Has not changed the name
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.
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.

31. PAGESIZE
Nested
Default is 14; when you use the default - you repeat the heading every 14 rows.
Fractional seconds
Scalar

32. UNION ALL does not sort - therefore it is less time consuming to use than the...
UNION operator
The indexed column is part of an expression. In this case - consider creating a function-based index instead
SELECT SYSDATE+1/96 - FROM dual
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.

33. Subqueries can return...
Multiple rows
ANSI
DROP INDEX need_to_pass_i
+

34. The same function can be used ______ in a SELECT statement.
Static or dynamic
Export Data
Twice
Null

35. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
DECODE
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Time zone

36. What is the data type you can use if you need an unlimited number of textual characters?
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Equijoin
CLOB
UTC

37. Explicit data type conversion is preferable to Oracle's...
Implicit conversion
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Inconsistencies
LIKE

38. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
ON
When the table is small (unless your business rules require you too)
SELECT SYSDATE+1/96 - FROM dual
FALSE

39. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
ANSI
Correlated
Black
Primary key/foreign key

40. A transaction is a...
Logical unit of work
UTC
Results
NATURAL JOIN

41. What is the keyboard shortcut for the ROLLBACK command?
UNION operator
MODIFY
F12
VALUES clause of an INSERT statement

42. _________ rows are not allowed in the relational model.
X
Orphan
LENGTH
Has not changed the name

43. Self-joins are useful for performing comparisons and checking for ___________ in data.
Inconsistencies
Multiple rows
Groups
Static or dynamic

44. A join across three tables can be expressed with the ________ join syntax.
ANSI
Script
SUM
Rounded edges

45. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
@ or START
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
12:00:00 A.M
FALSE

46. Write a solution that adds 20 minutes to a given date.
Groups
The indexed column is part of an expression. In this case - consider creating a function-based index instead
SELECT SYSDATE+1/96 - FROM dual
INTERSECT

47. 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.
INNER JOIN
FALSE
SET clause of an UPDATE statement
+

48. Joins involving multiple columns must always follow the ____________ relationship path.
Inconsistencies
Primary key/foreign key
ROLLBACK
SYSDATE

49. You can use the ________________ to subtract months from a given date.
ON
ADD_MONTHS function
999999.9999
Rounded edges

50. Converting a text literal to DATE format requires the use of the...
Precision
TO_CHAR function
999999.9999
KEEP INDEX