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. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Static or dynamic
All
Semicolon or forward slash

2. 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.
Details
Has not changed the name
Recursive
When the table is small (unless your business rules require you too)

3. 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
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.
The way NULL values are treated
Committed

4. It is considered good form to put all SQL commands in...
DROP INDEX need_to_pass_i
Retrieve
All caps
Logical unit of work

5. Joins involving multiple columns must always follow the ____________ relationship path.
+
Primary key/foreign key
Common column value
When the table is small (unless your business rules require you too)

6. What does the following function return? SELECT LENGTH(NULL) - FROM dual
999999.9999
Optional
RIGHT LEFT JOIN
Null

7. The term ____________ ___________ is used to express a join that satisfies the join condition.
Black
INNER JOIN
A correct statement
Parent table

8. Which operator is most commonly used?
999999.9999
UNION
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Any

9. What is the tab in SQL Developer where data dictionary view information can be found?
Details
Location of the file
Export Data
Equijoin and correlated subquery

10. Explain what is found in the All Objects report folder in SQL Developer
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
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
VALUES
ANSI

11. Explain the steps needed to run a script in SQL Developer
MINUS
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
RIGHT LEFT JOIN
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.

12. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
NATURAL JOIN
Null
Surrogate
ROLLBACK

13. What is the maximum value for a column defined as NUMBER(4 -2)?
2000
RIGHT LEFT JOIN
99.99
Actual column

14. Which one of the following string functions tells you how many characters are in a string?
Same
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.
Equijoin
LENGTH

15. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Three
SELECT DISTINCT menu FROM restaurant
Data

16. The DUAL table can be used for...
Testing functions
ANSI
F12
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

17. Which format mask displays December 31st - 1999?
Mathematical equations
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Multiple rows
Month ddth - yyyy

18. The ___________ operator tests whether a matching row cannot be found.
Fractional seconds
RUN SCRIPT
Location of the file
X

19. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Orphan
Another null
All
Script

20. The RTRIM function is useful for eliminating ________ in a string.
Extra spaces
ALL_OBJECTS
Self-joins
The way NULL values are treated

21. The COUNT function uses the __________ wildcard.
Asterisk
DICTIONARY
Command returns the number of records returned by a query.
Multiple rows

22. You use the __________ keyword to change characteristics of a column.
Run scripts
Scalar
MODIFY
Surrogate key

23. A transaction is a...
Logical unit of work
DICT_COLUMNS
ANY
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

24. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
Time zone
SET clause of an UPDATE statement
X
MINUS

25. Oracle uses the outer join operator to indicate that nulls are shown for...
Non-matching 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
Has not changed the name
LENGTH

26. 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?
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.
SUM
Rounded edges
DICTIONARY

27. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
12:00:00 A.M
Month ddth - yyyy
STORAGE
Common column value

28. What do views do that makes them so useful for security?
MODIFY
Hide data
ADD
Command returns the number of records returned by a query.

29. The _____________ clause determines how rows are grouped.
GROUP BY
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Hide data
Any

30. Explain how the SPOOL command is used and what program it can be used in.
GROUP BY
Any
VALUES clause of an INSERT statement
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

31. Checks whether any value in the list makes the condition true.
Running a script file
Any
VALUES clause of an INSERT statement
Recursive

32. UNION ALL does not sort - therefore it is less time consuming to use than the...
UNION operator
Asterisk
Primary key/foreign key
INNER JOIN

33. FEEDBACK
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Command returns the number of records returned by a query.
Details
DICT_COLUMNS

34. List the reasons that a subquery could not be unnested.
X
All caps
Orphan
4

35. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Actual column
2000
Optional
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

36. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
X
USER_ - ALL_ - DBA_ - and V$
Correlated

37. The ALTER SESSION statement can change the session's...
F12
SUM
Time zone
Inconsistencies

38. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
User-defined reports
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Export Data
X

39. List the steps performed by a correlated subquery.
Retrieve
UNION operator
**** 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
A correct statement

40. Write the syntax to rename index just_two_more_weeks_i to finally_i
RENAME just_two_more_weeks TO finally_i
The way NULL values are treated
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
COMMIT

41. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Self-joins
Combine
Black
INTERSECT

42. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
A correct statement
Precision
Data
Script

43. List 6 Table-Related Menu Choices
DECODE
Crow's foot
DICT_COLUMNS
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

44. List the different types of data dictionary views.
Surrogate key
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.
Location of the file
USER_ - ALL_ - DBA_ - and V$

45. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
X
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Groups
Optional

46. When you run a script in SQL*Plus you must give the...
Location of the file
Re-create
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Run scripts

47. Write a statement for SQL*Plus that shows the dictionary.
Non-matching rows
DESC dict;
**** 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
CLOB

48. Returns rows if the condition is true for all the values in the list
All
X
Surrogate
UNION

49. When column names on the tables are different - you use the ______ condition.
ON
Retrieve
F12
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.

50. SQL*Plus is used more than SQL Developer when you need to...
Common column value
Run scripts
Black
Orphan