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. Explain in detail the impact of INDEXES and how they are used by Oracle.
When the table is small (unless your business rules require you too)
Common column value
12:00:00 A.M
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

2. The term ____________ ___________ is used to express a join that satisfies the join condition.
Groups
Black
INNER JOIN
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

3. List the different types of data dictionary views.
Leading-edge
Rounded edges
USER_ - ALL_ - DBA_ - and V$
DESC dict;

4. List 6 Table-Related Menu Choices
**** 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
ON
Results
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

5. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
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.
STORAGE
Export Data
Script

6. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Twice
Correlated
BETWEEN
DESC dict;

7. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
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
X
RENAME just_two_more_weeks TO finally_i
UNION

8. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Retrieve
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.
NATURAL JOIN
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

9. A _________ subquery returns a single column with a single row
Scalar
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
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

10. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
@ or START
SELECT SYSDATE+1/96 - FROM dual
FALSE
Location of the file

11. What is the first column of a composite index called?
INNER JOIN
Optional
Leading-edge
NATURAL JOIN

12. Explain the steps needed to run a script in SQL Developer
Self-joins
All
+
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.

13. The ___________ operator tests whether a matching row cannot be found.
Extra spaces
X
Non-matching rows
Twice

14. SQL*Plus is used more than SQL Developer when you need to...
Self-joins
Run scripts
BETWEEN
Same

15. When you run a script in SQL*Plus you must give the...
4000
DROP INDEX need_to_pass_i
Details
Location of the file

16. Another name for a data dictionary view is __________________ or _________________.
Asterisk
UNION
ADD_MONTHS function
Static or dynamic

17. The _____________ clause determines how rows are grouped.
Run scripts
IN ANY ALL SOME
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
GROUP BY

18. 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.
DD-MON-YY
FALSE
DESC dict;
Multiple rows

19. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
All
Three
SELECT DISTINCT menu FROM restaurant
Both MIN and MAX

20. The ____________ command makes changes permanent.
Rounded edges
COMMIT
Common column value
ANSI

21. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
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
Same
RENAME just_two_more_weeks TO finally_i
UNION

22. PAGESIZE
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Rounded edges
Default is 14; when you use the default - you repeat the heading every 14 rows.
User-defined reports

23. The DUAL table can be used for...
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
ROLLBACK
Testing functions
STORAGE

24. What is the maximum value for a column defined as NUMBER(4 -2)?
F12
99.99
FALSE
Testing functions

25. A calculation with a null always yields...
Combine
Another null
Black
B-Tree Index

26. NUMBER functions can be...
Data
Has not changed the name
Nested
Location of the file

27. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
Export Data
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.
Fractional seconds
VALUES clause of an INSERT statement

28. You use the __________ keyword to change characteristics of a column.
LENGTH
ADD
Recursive
MODIFY

29. List the reasons that a subquery could not be unnested.
Default is 14; when you use the default - you repeat the heading every 14 rows.
X
Correlated
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

30. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
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.
Equijoin and correlated subquery
Scalar
Fractional seconds

31. Write the syntax to rename index just_two_more_weeks_i to finally_i
RENAME just_two_more_weeks TO finally_i
Equijoin and correlated subquery
ADD_MONTHS function
Fractional seconds

32. To equijoin a table with another table involves matching the...
2000
ADD_MONTHS function
Same
Common column value

33. You can pass parameters from the operating system when ___________ in SQl*Plus
Actual column
Running a script file
Same
Default is 14; when you use the default - you repeat the heading every 14 rows.

34. In a view you can give a column a different name than it has in the...
The indexed column is part of an expression. In this case - consider creating a function-based index instead
GROUP BY
Parent table
KEEP INDEX

35. What does SQL stand for?
Structured Query Language
Common column value
Equijoin and identical column names
NATURAL JOIN

36. The _______ operator determines common rows.
KEEP INDEX
Equijoin and correlated subquery
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

37. Self-joins are useful for performing comparisons and checking for ___________ in data.
Orphan
Inconsistencies
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
X

38. The __________ operator is inclusive of the two values specified.
Semicolon or forward slash
BETWEEN
Primary key/foreign key
VALUES

39. The query retrieves more than 5 to 15 percent of the rows
Export Data
MODIFY
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
The indexed column is part of an expression. In this case - consider creating a function-based index instead

40. The _______ function adds values together for a group of rows.
Another null
SUM
Equijoin and correlated subquery
V$

41. The ALTER SESSION statement can change the session's...
Time zone
Optional
SET clause of an UPDATE statement
Common column value

42. The ROWID pseudocolumn is not an ________- but it acts like one.
Fractional seconds
Static or dynamic
Actual column
When the table is small (unless your business rules require you too)

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

44. Comparison operators can compare multiple _________________.
STORAGE
Time zone
ANSI
Columns

45. Returns rows if the condition is true for all the values in the list
Asterisk
MINUS
All
Extra spaces

46. Users can only see changes made to a database once they have been...
ANSI
All
Testing functions
Committed

47. List 4 aggregate functions
Equijoin and identical column names
IN ANY ALL SOME
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
2000

48. 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.
Leading-edge
KEEP INDEX
Has not changed the name
Results

49. How many characters can you have using the VARCHAR data type?
Scalar
4000
KEEP INDEX
Asterisk

50. Which format mask displays December 31st - 1999?
X
A correct statement
Location of the file
Month ddth - yyyy