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. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Default is 14; when you use the default - you repeat the heading every 14 rows.
VALUES clause of an INSERT statement
Null
999999.9999

2. The using clause of the ANSI join syntax always assumes an...
Nested
X
Equijoin and identical column names
DD-MON-YY

3. List the steps performed by a correlated subquery.
VALUES clause of an INSERT statement
Black
**** 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
All caps

4. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Script
ROLLBACK
12:00:00 A.M
Export Data

5. List 4 aggregate functions
KEEP INDEX
ON
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

6. Explain the steps needed to run a script in SQL Developer
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.
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
Export Data
DESC dict;

7. FEEDBACK
12:00:00 A.M
UNION operator
Any
Command returns the number of records returned by a query.

8. How many characters can be stored in the CHAR column/field?
2000
The way NULL values are treated
Month ddth - yyyy
SUM

9. The COUNT function uses the __________ wildcard.
Export Data
Asterisk
Mathematical equations
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.

10. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
Month ddth - yyyy
SELECT DISTINCT menu FROM restaurant
Static or dynamic
Surrogate key

11. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
ANY
Results
LENGTH
Has not changed the name

12. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
KEEP INDEX
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.
Inconsistencies
Equijoin

13. When column names on the tables are different - you use the ______ condition.
DICTIONARY
2000
ON
Non-matching rows

14. 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
STORAGE
X
Primary key/foreign key

15. The ________ operator cannot compare against a list of values.
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.
12:00:00 A.M
LIKE
FALSE

16. The purpose of the HAVING clause is to eliminate___________.
Time zone
12:00:00 A.M
Surrogate
Groups

17. Checks whether any value in the list makes the condition true.
Correlated
Any
SUM
IN ANY ALL SOME

18. Returns rows if the condition is true for all the values in the list
All
4000
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.
Any

19. SELECT * FROM instructor ORDER BY phone
X
Columns
A correct statement
Extra spaces

20. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
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
FALSE
Precision
STORAGE

21. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
DICTIONARY
BETWEEN
4
Implicit conversion

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

23. Which one of the following string functions tells you how many characters are in a string?
Asterisk
LENGTH
DECODE
Mathematical equations

24. When is it not a good idea to use an index?
The indexed column is part of an expression. In this case - consider creating a function-based index instead
X
When the table is small (unless your business rules require you too)
GROUP BY

25. The _____________ clause determines how rows are grouped.
Another null
GROUP BY
MODIFY
X

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. In SQL Developer field names and table names are shown in the color _______.
Black
Columns
INTERSECT
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

28. When you run a script in SQL*Plus you must give the...
MODIFY
Non-matching rows
Location of the file
BETWEEN

29. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
MINUS
GROUP BY
IN ANY ALL SOME
Fractional seconds

30. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
IN ANY ALL SOME
DD-MON-YY
999999.9999
VALUES clause of an INSERT statement

31. The ALTER SESSION statement can change the session's...
Crow's foot
Time zone
UNION
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.

32. The _______ function adds values together for a group of rows.
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.
BETWEEN
SUM
UNION

33. To equijoin a table with another table involves matching the...
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
MINUS
Common column value
UNION

34. The __________ operator is inclusive of the two values specified.
BETWEEN
Equijoin
Both MIN and MAX
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

35. 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.
ON
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Has not changed the name
Logical unit of work

36. What are the main components of a ROWID?
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
DESC dict;
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.
Extra spaces

37. What is the name of the key automatically generated from a sequence?
Both MIN and MAX
Surrogate key
ROLLBACK
Location of the file

38. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
GROUP BY
Results
Location of the file
STORAGE

39. Joins involving multiple columns must always follow the ____________ relationship path.
ADD_MONTHS function
Primary key/foreign key
MINUS
Inconsistencies

40. ____________ are useful for analyzing and exploring the relationships within your data
UTC
RUN SCRIPT
DROP INDEX need_to_pass_i
Self-joins

41. PAGESIZE
TO_CHAR function
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.
X

42. In a view you can give a column a different name than it has in the...
RENAME just_two_more_weeks TO finally_i
Parent table
Any
Equijoin

43. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
Columns
Running a script file
999999.9999
Scale

44. What is Oracle's most popular index storage structure?
Self-joins
Has not changed the name
B-Tree Index
RUN SCRIPT

45. You use the _________ keyword to add columns or constraints to a table.
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
The way NULL values are treated
Equijoin and identical column names
ADD

46. When you have issued a DELETE command - you can still ________ those rows and restore the information.
Retrieve
ADD_MONTHS function
DECODE
Nested

47. You can use the ________________ to subtract months from a given date.
Recursive
Structured Query Language
Inconsistencies
ADD_MONTHS function

48. Subqueries can return...
LIKE
STORAGE
Multiple rows
NATURAL JOIN

49. The TRUNC function on a date without a format model truncates the timestamp to...
12:00:00 A.M
Running a script file
SELECT DISTINCT menu FROM restaurant
SYSDATE

50. Checks whether any value in the list makes the condition true.
Any
CLOB
KEEP INDEX
X