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. When column names on the tables are different - you use the ______ condition.
ON
Groups
Semicolon or forward slash
Location of the file

2. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
User-defined reports
GROUP BY
Retrieve
SET clause of an UPDATE statement

3. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
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.
V$
STORAGE
Scale

4. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
All
Precision
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Details

5. Write the syntax to rename index just_two_more_weeks_i to finally_i
RENAME just_two_more_weeks TO finally_i
Correlated
X
Primary key/foreign key

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

7. In SQL Developer field names and table names are shown in the color _______.
Black
DICTIONARY
Correlated
Inconsistencies

8. In a view you can give a column a different name than it has in the...
Precision
RENAME just_two_more_weeks TO finally_i
Parent table
Run scripts

9. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Both MIN and MAX
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
DD-MON-YY
Combine

10. The key that most database designers prefer and is chosen by the system is the _____________ key.
Crow's foot
RIGHT LEFT JOIN
NATURAL JOIN
Surrogate

11. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Black
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
X
Results

12. _________ rows are not allowed in the relational model.
LENGTH
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Orphan
Columns

13. Comparison operators can compare multiple _________________.
Columns
Run scripts
SELECT DISTINCT menu FROM restaurant
12:00:00 A.M

14. The RTRIM function is useful for eliminating ________ in a string.
99.99
Optional
Extra spaces
Null

15. What does the following function return? SELECT LENGTH(NULL) - FROM dual
UNION
Null
BETWEEN
Scalar

16. What is the first column of a composite index called?
LIKE
Nested
Leading-edge
DROP INDEX need_to_pass_i

17. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
DICTIONARY
Export Data
Semicolon or forward slash
Static or dynamic

18. SELECT * FROM instructor ORDER BY phone
Structured Query Language
SELECT DISTINCT menu FROM restaurant
A correct statement
2000

19. 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.
BETWEEN
A correct statement
RUN SCRIPT

20. A _________ subquery returns a single column with a single row
B-Tree Index
Scalar
Export Data
4000

21. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
+
UNION operator
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
RIGHT LEFT JOIN

22. Which format mask displays December 31st - 1999?
Month ddth - yyyy
Business problems
Both MIN and MAX
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

23. SELECT LIST of a subquery in an INSERT statement
SET clause of an UPDATE statement
Retrieve
RUN SCRIPT
Black

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

25. The ______ depicts the M of a 1:M relationship.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

26. To join four tables - you must have at least _____ join conditions.
LENGTH
Optional
Three
Scale

27. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Black
Correlated
ADD_MONTHS function
Same

28. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
ON
USER_ - ALL_ - DBA_ - and V$
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Correlated

29. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
DECODE
Recursive
Equijoin and correlated subquery
ALL_OBJECTS

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

31. You can pass parameters from the operating system when ___________ in SQl*Plus
FALSE
Running a script file
99.99
Fractional seconds

32. Oracle uses the outer join operator to indicate that nulls are shown for...
X
VALUES
DD-MON-YY
Non-matching rows

33. 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?
Static or dynamic
Any
Hide data
DICTIONARY

34. The ALTER SESSION statement can change the session's...
VALUES
BETWEEN
Time zone
Implicit conversion

35. The focus of the logical data model is _____________
4000
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
Business problems
Equijoin and correlated subquery

36. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
User-defined reports
FALSE
SUM
Correlated

37. Explicit data type conversion is preferable to Oracle's...
Black
Running a script file
Testing functions
Implicit conversion

38. Dynamic views begin with a ___________.
4
LENGTH
V$
Precision

39. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Non-matching rows
NATURAL JOIN
Equijoin and identical column names
UTC

40. What is the data type you can use if you need an unlimited number of textual characters?
Primary key/foreign key
DESC dict;
CLOB
Time zone

41. To equijoin a table with another table involves matching the...
LENGTH
USER_ - ALL_ - DBA_ - and V$
DROP INDEX need_to_pass_i
Common column value

42. Explain the steps needed to run a script in SQL Developer
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.
DICTIONARY
Optional
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

43. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Equijoin and correlated subquery
LIKE
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
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

44. The COUNT function uses the __________ wildcard.
FALSE
Asterisk
Null
Static or dynamic

45. The _____________ clause determines how rows are grouped.
FALSE
DD-MON-YY
GROUP BY
Time zone

46. The _______ function adds values together for a group of rows.
SET clause of an UPDATE statement
2000
SUM
User-defined reports

47. Subqueries can return...
Testing functions
UTC
Multiple rows
All caps

48. The term ____________ ___________ is used to express a join that satisfies the join condition.
INNER JOIN
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Columns
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.

49. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
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
ALL_OBJECTS
Results
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

50. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Optional
B-Tree Index
DICTIONARY
Mathematical equations