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. ____________ are useful for analyzing and exploring the relationships within your data
USER_ - ALL_ - DBA_ - and V$
Leading-edge
Self-joins
Multiple rows

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

3. Explain how the SPOOL command is used and what program it can be used in.
STORAGE
Run scripts
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

4. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
BETWEEN
Re-create
COMMIT
+

5. The focus of the logical data model is _____________
NATURAL JOIN
Both MIN and MAX
COMMIT
Business problems

6. The ______ operator compares a list of values for equivalency
X
TO_CHAR function
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
ANY

7. _________ rows are not allowed in the relational model.
V$
Scale
X
Orphan

8. The RTRIM function is useful for eliminating ________ in a string.
BETWEEN
Extra spaces
Twice
SET clause of an UPDATE statement

9. You can pass parameters from the operating system when ___________ in SQl*Plus
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.
Running a script file
Scalar
RIGHT LEFT JOIN

10. What is the tab in SQL Developer where data dictionary view information can be found?
ANY
V$
INNER JOIN
Details

11. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Combine
Nested
Another null
USER_ - ALL_ - DBA_ - and V$

12. Oracle uses the outer join operator to indicate that nulls are shown for...
Non-matching rows
Details
RENAME just_two_more_weeks TO finally_i
All caps

13. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
DROP INDEX need_to_pass_i
VALUES
Run scripts
Script

14. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
Retrieve
Results
KEEP INDEX
IN ANY ALL SOME

15. How many characters can you have using the VARCHAR data type?
4000
Inconsistencies
ANSI
SUM

16. 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.
Details
UNION
SET clause of an UPDATE statement
Has not changed the name

17. A calculation with a null always yields...
Another null
Hide data
Script
Equijoin

18. Write a statement for SQL*Plus that shows the dictionary.
Retrieve
Implicit conversion
DESC dict;
INTERSECT

19. The using clause of the ANSI join syntax always assumes an...
Equijoin and identical column names
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
Any
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

20. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
Black
Running a script file
SELECT DISTINCT menu FROM restaurant
Precision

21. The ___________ operator tests whether a matching row cannot be found.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
ON
When the table is small (unless your business rules require you too)
X

22. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Running a script file
Re-create
Both MIN and MAX
Logical unit of work

23. What do views do that makes them so useful for security?
VALUES clause of an INSERT statement
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
Hide data
**** 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

24. Returns rows if the condition is true for all the values in the list
INTERSECT
ALL_OBJECTS
All
MODIFY

25. The time zone displacement value indicates the time difference from _______.
TO_CHAR function
UTC
Scale
Multiple rows

26. FEEDBACK
ADD_MONTHS function
Command returns the number of records returned by a query.
Equijoin
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

27. The key that most database designers prefer and is chosen by the system is the _____________ key.
Surrogate
ANY
VALUES
Actual column

28. The _____________ clause determines how rows are grouped.
VALUES clause of an INSERT statement
Equijoin and identical column names
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
GROUP BY

29. To join four tables - you must have at least _____ join conditions.
Scalar
Export Data
Three
Results

30. You can use the ________________ to subtract months from a given date.
ADD_MONTHS function
X
The indexed column is part of an expression. In this case - consider creating a function-based index instead
4000

31. The ROWID pseudocolumn is not an ________- but it acts like one.
Actual column
DECODE
Crow's foot
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.

32. SELECT LIST of a subquery in an INSERT statement
Mathematical equations
SET clause of an UPDATE statement
999999.9999
4

33. Self-joins are useful for performing comparisons and checking for ___________ in data.
Testing functions
IN ANY ALL SOME
ANY
Inconsistencies

34. How many characters can be stored in the CHAR column/field?
Scale
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
2000
Scalar

35. A _________ subquery returns a single column with a single row
CLOB
Data
Scalar
X

36. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
BETWEEN
Run scripts
Same
X

37. The __________ operator is inclusive of the two values specified.
LENGTH
Fractional seconds
4
BETWEEN

38. The term ____________ ___________ is used to express a join that satisfies the join condition.
Equijoin and identical column names
X
INNER JOIN
SYSDATE

39. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
ADD_MONTHS function
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
4
Optional

40. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
CLOB
Semicolon or forward slash
RIGHT LEFT JOIN
ANSI

41. What is the maximum value for a column defined as NUMBER(4 -2)?
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
SELECT SYSDATE+1/96 - FROM dual
99.99
The indexed column is part of an expression. In this case - consider creating a function-based index instead

42. When column names on the tables are different - you use the ______ condition.
Structured Query Language
Has not changed the name
ON
DD-MON-YY

43. What does SQL stand for?
Structured Query Language
Running a script file
STORAGE
Logical unit of work

44. PAGESIZE
UTC
INNER JOIN
Default is 14; when you use the default - you repeat the heading every 14 rows.
All

45. When you run a script in SQL*Plus you must give the...
A correct statement
CLOB
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Location of the file

46. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
ROLLBACK
LENGTH
Committed
X

47. To equijoin a table with another table involves matching the...
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
Common column value
UTC

48. The purpose of the HAVING clause is to eliminate___________.
ADD_MONTHS function
Time zone
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
Groups

49. Another name for a data dictionary view is __________________ or _________________.
Non-matching rows
Static or dynamic
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
INNER JOIN

50. Explicit data type conversion is preferable to Oracle's...
Export Data
Implicit conversion
User-defined reports
Columns