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. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
ANSI
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
12:00:00 A.M

2. Write the syntax to rename index just_two_more_weeks_i to finally_i
Primary key/foreign key
RENAME just_two_more_weeks TO finally_i
When the table is small (unless your business rules require you too)
Any

3. The _______ function adds values together for a group of rows.
Surrogate key
INTERSECT
SUM
2000

4. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
INTERSECT
FALSE
Precision
Rounded edges

5. SQL Developer provides an extensive list of useful reports and offers the capability to create your own...
Run scripts
User-defined reports
FALSE
Optional

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

7. Explain why constraints are used when building tables.

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

8. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
VALUES clause of an INSERT statement
12:00:00 A.M
MINUS
Same

9. In SQL Developer field names and table names are shown in the color _______.
ADD
Black
The way NULL values are treated
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

10. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
MINUS
X
Leading-edge

11. Which one of the following string functions tells you how many characters are in a string?
ANY
ON
Logical unit of work
LENGTH

12. Checks whether any value in the list makes the condition true.
UNION
Any
@ or START
Re-create

13. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
NATURAL JOIN
999999.9999
Non-matching rows
Results

14. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Equijoin
FALSE
Null
DICTIONARY

15. Subqueries can return...
Command returns the number of records returned by a query.
Testing functions
Script
Multiple rows

16. The COUNT function is useful for determining whether a table has...
Has not changed the name
Data
X
Inconsistencies

17. To join four tables - you must have at least _____ join conditions.
Three
Common column value
Null
Precision

18. Explain how Substitution Variables are used when writing scripts.
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.
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.
Inconsistencies
4

19. Joins involving multiple columns must always follow the ____________ relationship path.
Primary key/foreign key
Committed
Run scripts
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

20. You can pass parameters from the operating system when ___________ in SQl*Plus
Running a script file
Export Data
999999.9999
Details

21. What is Oracle's most popular index storage structure?
Details
FALSE
B-Tree Index
Testing functions

22. Comparison operators can compare multiple _________________.
Orphan
Columns
CLOB
VALUES clause of an INSERT statement

23. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Business problems
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
Non-matching rows
Combine

24. A join across three tables can be expressed with the ________ join syntax.
Precision
ANSI
When the table is small (unless your business rules require you too)
Nested

25. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
SELECT SYSDATE+1/96 - FROM dual
STORAGE
Scale
Implicit conversion

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

27. UNION ALL does not sort - therefore it is less time consuming to use than the...
UNION operator
Precision
F12
Time zone

28. Self-joins are useful for performing comparisons and checking for ___________ in data.
Default is 14; when you use the default - you repeat the heading every 14 rows.
MODIFY
V$
Inconsistencies

29. The key that most database designers prefer and is chosen by the system is the _____________ key.
CLOB
Surrogate
Nested
Fractional seconds

30. Which operator is most commonly used?
UNION
RIGHT LEFT JOIN
INNER JOIN
Scalar

31. Explain how the SPOOL command is used and what program it can be used in.
Equijoin and identical column names
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
@ or START
GROUP BY

32. What do views do that makes them so useful for security?
Hide data
Nested
Month ddth - yyyy
Committed

33. It is considered good form to put all SQL commands in...
COMMIT
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.
BETWEEN
All caps

34. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Same
LIKE
Mathematical equations
Run scripts

35. The ALTER SESSION statement can change the session's...
ROLLBACK
Time zone
Running a script file
All

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

37. The _____________ clause determines how rows are grouped.
User-defined reports
GROUP BY
FALSE
Same

38. List the reasons that a subquery could not be unnested.
Location of the file
GROUP BY
Any
X

39. ____________ are useful for analyzing and exploring the relationships within your data
LENGTH
SET clause of an UPDATE statement
Self-joins
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

40. The same function can be used ______ in a SELECT statement.
Twice
Retrieve
Time zone
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.

41. You use the __________ keyword to change characteristics of a column.
MODIFY
MINUS
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.
Retrieve

42. When you have issued a DELETE command - you can still ________ those rows and restore the information.
TO_CHAR function
Retrieve
Re-create
Same

43. Explain what is found in the All Objects report folder in SQL Developer
All
X
VALUES
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

44. Another name for a data dictionary view is __________________ or _________________.
Default is 14; when you use the default - you repeat the heading every 14 rows.
Static or dynamic
Asterisk
ANSI

45. What is the most common DATE format found in SQL?
Logical unit of work
COMMIT
UNION
DD-MON-YY

46. 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.
X
Running a script file
FALSE
All

47. Returns rows if the condition is true for all the values in the list
Logical unit of work
All
**** 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
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.

48. Dynamic views begin with a ___________.
999999.9999
Three
Same
V$

49. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
ALL_OBJECTS
Nested
Leading-edge
SELECT DISTINCT menu FROM restaurant

50. Oracle uses the outer join operator to indicate that nulls are shown for...
Surrogate key
NATURAL JOIN
MINUS
Non-matching rows