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. Oracle uses the outer join operator to indicate that nulls are shown for...
Script
Location of the file
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.
Non-matching rows

2. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
Leading-edge
Non-matching rows
SELECT DISTINCT menu FROM restaurant
Multiple rows

3. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
X
RUN SCRIPT
Month ddth - yyyy
Columns

4. The DUAL table can be used for...
Testing functions
Correlated
Parent table
Surrogate

5. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Non-matching rows
Same
X
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

6. What is Oracle's most popular index storage structure?
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
B-Tree Index
STORAGE
Multiple rows

7. Users can only see changes made to a database once they have been...
DD-MON-YY
Static or dynamic
Committed
Structured Query Language

8. What is the maximum value for a column defined as NUMBER(4 -2)?
99.99
Command returns the number of records returned by a query.
Data
MINUS

9. The TRUNC function on a date without a format model truncates the timestamp to...
12:00:00 A.M
Script
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.
X

10. Explain how Substitution Variables are used when writing scripts.
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
Scalar
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.

11. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
Command returns the number of records returned by a query.
@ or START
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.
Primary key/foreign key

12. Explain how the SPOOL command is used and what program it can be used in.
Twice
Testing functions
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
NATURAL JOIN

13. The query retrieves more than 5 to 15 percent of the rows
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Equijoin and identical column names

14. List the reasons that a subquery could not be unnested.
VALUES clause of an INSERT statement
Another null
X
DESC dict;

15. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
VALUES clause of an INSERT statement
B-Tree Index
Export Data
KEEP INDEX

16. What is the name of the key automatically generated from a sequence?
Actual column
Equijoin
Recursive
Surrogate key

17. What is the first column of a composite index called?
Time zone
Leading-edge
12:00:00 A.M
Orphan

18. Explicit data type conversion is preferable to Oracle's...
ROLLBACK
Implicit conversion
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
B-Tree Index

19. The ___________ operator tests whether a matching row cannot be found.
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
X
Location of the file
12:00:00 A.M

20. List 4 aggregate functions
GROUP BY
Twice
Leading-edge
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

21. Explain what is found in the All Objects report folder in SQL Developer
RUN SCRIPT
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Nested
Crow's foot

22. 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.
FALSE
Running a script file
Parent table
ANSI

23. What is the main difference between the NOT EXISTS and the NOT IN operators?
Committed
STORAGE
The way NULL values are treated
Correlated

24. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
KEEP INDEX
4
LENGTH
Surrogate

25. When you have issued a DELETE command - you can still ________ those rows and restore the information.
All
Data
Retrieve
User-defined reports

26. Write a solution that adds 20 minutes to a given date.
KEEP INDEX
SELECT SYSDATE+1/96 - FROM dual
GROUP BY
Has not changed the name

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

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

29. To equijoin a table with another table involves matching the...
INTERSECT
Common column value
Asterisk
Leading-edge

30. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
UNION operator
MINUS
ADD_MONTHS function
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.

31. You use the __________ keyword to change characteristics of a column.
RIGHT LEFT JOIN
MODIFY
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
Fractional seconds

32. The time zone displacement value indicates the time difference from _______.
Hide data
UTC
Extra spaces
MINUS

33. When you run a script in SQL*Plus you must give the...
Location of the file
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.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
MODIFY

34. _________________ relationships are always optional relationships.
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.
Twice
Recursive
Has not changed the name

35. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
UNION
ADD
ROLLBACK
2000

36. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Primary key/foreign key
SELECT SYSDATE+1/96 - FROM dual
USER_ - ALL_ - DBA_ - and V$
Equijoin and correlated subquery

37. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
KEEP INDEX
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
INNER JOIN
ALL_OBJECTS

38. Self-joins are useful for performing comparisons and checking for ___________ in data.
SELECT SYSDATE+1/96 - FROM dual
Location of the file
Inconsistencies
ROLLBACK

39. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
DICT_COLUMNS
FALSE
Time zone
IN ANY ALL SOME

40. Explain in detail the impact of INDEXES and how they are used by Oracle.
CLOB
Run scripts
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
Both MIN and MAX

41. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
ROLLBACK
Data
Correlated
Time zone

42. The focus of the logical data model is _____________
VALUES
Business problems
Both MIN and MAX
USER_ - ALL_ - DBA_ - and V$

43. The COUNT function uses the __________ wildcard.
Asterisk
Scalar
Retrieve
Static or dynamic

44. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Leading-edge
Any
DICT_COLUMNS

45. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
SYSDATE
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.
Semicolon or forward slash
Three

46. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
Business problems
Actual column
FALSE
IN ANY ALL SOME

47. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
SUM
+
999999.9999
User-defined reports

48. When you INSERT new information into a table - it is followed with a _______ keyword that contains the information that is going to be added to the table.
FALSE
RUN SCRIPT
Equijoin and correlated subquery
VALUES

49. What is the tab in SQL Developer where data dictionary view information can be found?
Details
Script
Static or dynamic
Retrieve

50. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
Logical unit of work
STORAGE
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
Equijoin and identical column names