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 the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
X
SELECT DISTINCT menu FROM restaurant
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.
IN ANY ALL SOME

2. The time zone displacement value indicates the time difference from _______.
UNION
UTC
Combine
X

3. The query retrieves more than 5 to 15 percent of the rows
@ or START
The indexed column is part of an expression. In this case - consider creating a function-based index instead
VALUES
Script

4. The COUNT function uses the __________ wildcard.
IN ANY ALL SOME
Asterisk
Equijoin
NATURAL JOIN

5. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Multiple rows
@ or START
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Equijoin and correlated subquery

6. NUMBER functions can be...
Nested
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
Primary key/foreign key
Time zone

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

8. You can use the ________________ to subtract months from a given date.
UTC
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
4
ADD_MONTHS function

9. The _______ operator determines common rows.
INTERSECT
DROP INDEX need_to_pass_i
Same
Correlated

10. Checks whether any value in the list makes the condition true.
Any
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
RIGHT LEFT JOIN
Surrogate key

11. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
Common column value
Fractional seconds
Correlated
FALSE

12. Which operator is most commonly used?
UNION
When the table is small (unless your business rules require you too)
NATURAL JOIN
DECODE

13. _________________ relationships are always optional relationships.
LIKE
DECODE
Recursive
ANY

14. A join across three tables can be expressed with the ________ join syntax.
ANSI
DESC dict;
VALUES
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

15. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
99.99
Precision
Correlated
Running a script file

16. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Data
Semicolon or forward slash
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
X

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

18. The key that most database designers prefer and is chosen by the system is the _____________ key.
Surrogate
ADD
DD-MON-YY
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

19. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
DICT_COLUMNS
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.
STORAGE
X

20. What is the most common DATE format found in SQL?
Optional
DD-MON-YY
Hide data
LIKE

21. What do views do that makes them so useful for security?
Multiple rows
Month ddth - yyyy
VALUES
Hide data

22. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Has not changed the name
Same
All caps
Optional

23. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
DROP INDEX need_to_pass_i
@ or START
Optional
BETWEEN

24. It is considered good form to put all SQL commands in...
Results
Combine
All caps
DD-MON-YY

25. Queries that are found in parentheses are done first - just as in the order of operations when doing...
RUN SCRIPT
Mathematical equations
Has not changed the name
Scale

26. UNION ALL does not sort - therefore it is less time consuming to use than the...
UNION operator
ROLLBACK
A correct statement
ON

27. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
Re-create
ALL_OBJECTS
ON
Groups

28. The ____________ command makes changes permanent.
DD-MON-YY
Retrieve
COMMIT
+

29. 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.
SELECT DISTINCT menu FROM restaurant
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Default is 14; when you use the default - you repeat the heading every 14 rows.

30. What does SQL stand for?
UNION operator
Structured Query Language
Time zone
Same

31. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
Equijoin and identical column names
RUN SCRIPT
STORAGE
IN ANY ALL SOME

32. You use the _________ keyword to add columns or constraints to a table.
ADD
Parent table
Hide data
Three

33. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
Implicit conversion
Orphan
VALUES clause of an INSERT statement
999999.9999

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

35. List the different types of data dictionary views.
USER_ - ALL_ - DBA_ - and V$
+
Optional
ROLLBACK

36. Another name for a data dictionary view is __________________ or _________________.
Results
Business problems
Hide data
Static or dynamic

37. How many characters can be stored in the CHAR column/field?
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.
2000
Equijoin
All caps

38. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Equijoin and identical column names
Precision
SET clause of an UPDATE statement
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

39. Returns rows if the condition is true for all the values in the list
All
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Testing functions
Re-create

40. 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.
The way NULL values are treated
RIGHT LEFT JOIN
Rounded edges
The indexed column is part of an expression. In this case - consider creating a function-based index instead

41. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
NATURAL JOIN
99.99
Self-joins
Fractional seconds

42. The _______ function adds values together for a group of rows.
Testing functions
Logical unit of work
Equijoin and correlated subquery
SUM

43. A transaction is a...
2000
The way NULL values are treated
Scalar
Logical unit of work

44. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
UNION
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.
Both MIN and MAX
Structured Query Language

45. The TRUNC function on a date without a format model truncates the timestamp to...
X
Location of the file
SUM
12:00:00 A.M

46. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
DICTIONARY
KEEP INDEX
Retrieve
MODIFY

47. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Running a script file
Optional
Self-joins

48. Write a statement for SQL*Plus that shows the dictionary.
MINUS
MODIFY
DESC dict;
Surrogate

49. Write a statement that drops the index need_to_pass_i
INNER JOIN
DROP INDEX need_to_pass_i
Precision
SELECT SYSDATE+1/96 - FROM dual

50. Write a solution that adds 20 minutes to a given date.
TO_CHAR function
SELECT SYSDATE+1/96 - FROM dual
BETWEEN
Black