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. Match each of the SQL commands on the left with a verb from the list.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
SUM
ON
Asterisk

2. A join across three tables can be expressed with the ________ join syntax.
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.
Non-matching rows
VALUES
ANSI

3. The _______ function lets you perform if-then-else functionality with the SQL language.
DECODE
INNER JOIN
Any
Location of the file

4. 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.
VALUES
Another null
DROP INDEX need_to_pass_i
NATURAL JOIN

5. In a view you can give a column a different name than it has in the...
DICTIONARY
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
UTC
Parent table

6. The purpose of the HAVING clause is to eliminate___________.
Equijoin and correlated subquery
Default is 14; when you use the default - you repeat the heading every 14 rows.
Leading-edge
Groups

7. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Any
Extra spaces
Same
Any

8. A transaction is a...
Logical unit of work
VALUES clause of an INSERT statement
ON
MODIFY

9. The _______ function adds values together for a group of rows.
Groups
ADD_MONTHS function
SUM
Actual column

10. List 4 aggregate functions
Both MIN and MAX
ANSI
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
X

11. A _________ subquery returns a single column with a single row
Orphan
DROP INDEX need_to_pass_i
Time zone
Scalar

12. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
NATURAL JOIN
Surrogate key
RIGHT LEFT JOIN
ON

13. Explain how Substitution Variables are used when writing scripts.
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
Primary key/foreign key
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

14. LINESIZE
Results
X
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
DICTIONARY

15. FEEDBACK
Command returns the number of records returned by a query.
Multiple rows
Inconsistencies
DICTIONARY

16. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
DICT_COLUMNS
Any
Rounded edges
SELECT SYSDATE+1/96 - FROM dual

17. What does the following function return? SELECT LENGTH(NULL) - FROM dual
LENGTH
LIKE
BETWEEN
Null

18. When column names on the tables are different - you use the ______ condition.
ON
GROUP BY
F12
LENGTH

19. What is the data type you can use if you need an unlimited number of textual characters?
Static or dynamic
CLOB
+
4000

20. What are the main components of a ROWID?
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
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.
Implicit conversion
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.

21. Returns rows if the condition is true for all the values in the list
Retrieve
Scalar
All
Another null

22. What is the most common DATE format found in SQL?
DD-MON-YY
Groups
Data
ADD

23. What is the first column of a composite index called?
Equijoin and identical column names
TO_CHAR function
Leading-edge
ANSI

24. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
Details
GROUP BY
VALUES clause of an INSERT statement
Null

25. Explain how the SPOOL command is used and what program it can be used in.
UNION
LENGTH
NATURAL JOIN
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

26. Write a statement for SQL*Plus that shows the dictionary.
UTC
SELECT SYSDATE+1/96 - FROM dual
ADD_MONTHS function
DESC dict;

27. 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?
Committed
All caps
DICTIONARY
Both MIN and MAX

28. When you have issued a DELETE command - you can still ________ those rows and restore the information.
INNER JOIN
DESC dict;
@ or START
Retrieve

29. How many characters can you have using the VARCHAR data type?
Command returns the number of records returned by a query.
Common column value
4000
RUN SCRIPT

30. The ROWID pseudocolumn is not an ________- but it acts like one.
Actual column
Scalar
X
KEEP INDEX

31. The RTRIM function is useful for eliminating ________ in a string.
Groups
Extra spaces
Static or dynamic
LENGTH

32. Users can only see changes made to a database once they have been...
Rounded edges
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.
Committed
Optional

33. _________ rows are not allowed in the relational model.
Orphan
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.
Columns
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

34. Checks whether any value in the list makes the condition true.
Any
X
RIGHT LEFT JOIN
Multiple rows

35. Explain in detail the impact of INDEXES and how they are used by Oracle.
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
Another null
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
B-Tree Index

36. The time zone displacement value indicates the time difference from _______.
X
Three
UTC
USER_ - ALL_ - DBA_ - and V$

37. Which format mask displays December 31st - 1999?
Month ddth - yyyy
Running a script file
MINUS
B-Tree Index

38. The query retrieves more than 5 to 15 percent of the rows
Implicit conversion
Equijoin
The indexed column is part of an expression. In this case - consider creating a function-based index instead
ROLLBACK

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

40. How many characters can be stored in the CHAR column/field?
SET clause of an UPDATE statement
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
ALL_OBJECTS
2000

41. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Recursive
Mathematical equations
Combine
Surrogate

42. 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.
SYSDATE
Any
Has not changed the name
Script

43. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Equijoin and correlated subquery
Rounded edges
COMMIT
Any

44. 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.
When the table is small (unless your business rules require you too)
ANY
NATURAL JOIN
FALSE

45. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
FALSE
LENGTH
Nested
User-defined reports

46. ____________ are useful for analyzing and exploring the relationships within your data
99.99
Results
Self-joins
VALUES clause of an INSERT statement

47. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Logical unit of work
BETWEEN
X
Has not changed the name

48. List the different types of data dictionary views.
SYSDATE
USER_ - ALL_ - DBA_ - and V$
Implicit conversion
Both MIN and MAX

49. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Semicolon or forward slash
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Month ddth - yyyy
SYSDATE

50. What is the name of the key automatically generated from a sequence?
Surrogate key
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
Fractional seconds
Data