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. The ________ operator cannot compare against a list of values.
SET clause of an UPDATE statement
BETWEEN
LIKE
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

2. UNION ALL does not sort - therefore it is less time consuming to use than the...
+
DECODE
UNION operator
MODIFY

3. Write the syntax to rename index just_two_more_weeks_i to finally_i
VALUES clause of an INSERT statement
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
RENAME just_two_more_weeks TO finally_i
@ or START

4. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
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
Has not changed the name
DESC dict;
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

5. You can use the ________________ to subtract months from a given date.
Results
Run scripts
ADD_MONTHS function
12:00:00 A.M

6. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
Asterisk
ADD_MONTHS function
**** 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
VALUES clause of an INSERT statement

7. ____________ are useful for analyzing and exploring the relationships within your data
RIGHT LEFT JOIN
F12
Self-joins
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

8. The term ____________ ___________ is used to express a join that satisfies the join condition.
Precision
INNER JOIN
999999.9999
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

9. Converting a text literal to DATE format requires the use of the...
F12
TO_CHAR function
Mathematical equations
RENAME just_two_more_weeks TO finally_i

10. The ALTER SESSION statement can change the session's...
INNER JOIN
F12
ADD_MONTHS function
Time zone

11. SELECT LIST of a subquery in an INSERT statement
Orphan
Three
Structured Query Language
SET clause of an UPDATE statement

12. How many characters can you have using the VARCHAR data type?
2000
Business problems
4000
A correct statement

13. SELECT * FROM instructor ORDER BY phone
COMMIT
IN ANY ALL SOME
2000
A correct statement

14. The _______ operator determines common rows.
INTERSECT
Business problems
ANY
DICT_COLUMNS

15. A join across three tables can be expressed with the ________ join syntax.
ANSI
Actual column
ANY
FALSE

16. What is the keyboard shortcut for the ROLLBACK command?
F12
DROP INDEX need_to_pass_i
KEEP INDEX
CLOB

17. Explain the steps needed to run a script in SQL Developer
DROP INDEX need_to_pass_i
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.
Nested
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

18. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
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
Logical unit of work
KEEP INDEX
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.

19. The ______ operator compares a list of values for equivalency
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.
ANY
+
Asterisk

20. When column names on the tables are different - you use the ______ condition.
Business problems
Groups
Data
ON

21. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Script
Results
FALSE
NATURAL JOIN

22. When you run a script in SQL*Plus you must give the...
Has not changed the name
The way NULL values are treated
CLOB
Location of the file

23. 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.
DECODE
Precision
Black
FALSE

24. To join four tables - you must have at least _____ join conditions.
Extra spaces
Three
ON
Non-matching rows

25. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Actual column
Re-create
DICT_COLUMNS
NATURAL JOIN

26. Explain what is found in the All Objects report folder in SQL Developer
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
B-Tree Index
Mathematical equations
MINUS

27. Joins involving multiple columns must always follow the ____________ relationship path.
Primary key/foreign key
ANSI
Multiple rows
BETWEEN

28. The ___________ operator tests whether a matching row cannot be found.
ROLLBACK
X
@ or START
Another null

29. Checks whether any value in the list makes the condition true.
Any
Default is 14; when you use the default - you repeat the heading every 14 rows.
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
Groups

30. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Rounded edges
@ or START
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
DECODE

31. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
DECODE
Primary key/foreign key
SELECT DISTINCT menu FROM restaurant
UNION

32. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Equijoin
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
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
UNION operator

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

34. When is it not a good idea to use an index?
FALSE
COMMIT
LENGTH
When the table is small (unless your business rules require you too)

35. The using clause of the ANSI join syntax always assumes an...
Re-create
Combine
Equijoin and identical column names
DESC dict;

36. The TRUNC function on a date without a format model truncates the timestamp to...
Scale
12:00:00 A.M
Scalar
F12

37. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
All
DICTIONARY
STORAGE
Logical unit of work

38. The same function can be used ______ in a SELECT statement.
Export Data
Twice
Logical unit of work
Time zone

39. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
4
Leading-edge
INTERSECT
BETWEEN

40. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
Command returns the number of records returned by a query.
TO_CHAR function
SYSDATE

41. 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.
Semicolon or forward slash
Orphan
SET clause of an UPDATE statement
RIGHT LEFT JOIN

42. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Surrogate
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Export Data
Results

43. 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
12:00:00 A.M
X
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

44. List the different types of data dictionary views.
Asterisk
4
USER_ - ALL_ - DBA_ - and V$
ADD

45. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
SELECT SYSDATE+1/96 - FROM dual
Fractional seconds
Any
Twice

46. Write a statement that drops the index need_to_pass_i
DROP INDEX need_to_pass_i
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
X
Any

47. Comparison operators can compare multiple _________________.
Columns
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
Logical unit of work
UNION operator

48. _________ rows are not allowed in the relational model.
Actual column
Orphan
Command returns the number of records returned by a query.
Mathematical equations

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

50. What does SQL stand for?
KEEP INDEX
Structured Query Language
X
Default is 14; when you use the default - you repeat the heading every 14 rows.