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 data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Same
Multiple rows
DD-MON-YY
Groups

2. The using clause of the ANSI join syntax always assumes an...
Self-joins
Semicolon or forward slash
DD-MON-YY
Equijoin and identical column names

3. 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.
Has not changed the name
Export Data
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
SELECT DISTINCT menu FROM restaurant

4. Another name for a data dictionary view is __________________ or _________________.
Logical unit of work
Static or dynamic
V$
DICT_COLUMNS

5. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
SELECT DISTINCT menu FROM restaurant
Equijoin and correlated subquery
RIGHT LEFT JOIN
B-Tree Index

6. When you have issued a DELETE command - you can still ________ those rows and restore the information.
Hide data
Fractional seconds
Retrieve
X

7. List 6 Table-Related Menu Choices
Semicolon or forward slash
Parent table
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
The indexed column is part of an expression. In this case - consider creating a function-based index instead

8. Write a solution that adds 20 minutes to a given date.
Month ddth - yyyy
Default is 14; when you use the default - you repeat the heading every 14 rows.
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
SELECT SYSDATE+1/96 - FROM dual

9. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
Black
Nested
MINUS
Fractional seconds

10. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Time zone
Semicolon or forward slash
Null
Mathematical equations

11. NUMBER functions can be...
ANSI
Nested
KEEP INDEX
999999.9999

12. A calculation with a null always yields...
Semicolon or forward slash
Precision
X
Another null

13. To join four tables - you must have at least _____ join conditions.
UTC
Retrieve
FALSE
Three

14. The key that most database designers prefer and is chosen by the system is the _____________ key.
Combine
Surrogate
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.
Scale

15. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Results
LENGTH
ANSI
RIGHT LEFT JOIN

16. The focus of the logical data model is _____________
Business problems
FALSE
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
Black

17. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
V$
+
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
Recursive

18. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
All
User-defined reports
IN ANY ALL SOME
Correlated

19. List 4 aggregate functions
4000
RIGHT LEFT JOIN
UNION
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)

20. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
IN ANY ALL SOME
Running a script file
NATURAL JOIN
DICT_COLUMNS

21. The ALTER SESSION statement can change the session's...
Equijoin and identical column names
Time zone
Another null
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

22. The DUAL table can be used for...
Precision
+
Correlated
Testing functions

23. What does SQL stand for?
Optional
Structured Query Language
Leading-edge
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

24. 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.
Re-create
CLOB
Running a script file
FALSE

25. You use the _________ keyword to add columns or constraints to a table.
Correlated
ADD
Asterisk
Any

26. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
Another null
VALUES clause of an INSERT statement
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
RENAME just_two_more_weeks TO finally_i

27. 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.
Mathematical equations
MODIFY
X

28. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Combine
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
2000

29. Checks whether any value in the list makes the condition true.
Any
SYSDATE
NATURAL JOIN
Extra spaces

30. What are the main components of a ROWID?
ADD_MONTHS function
Fractional seconds
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.
Same

31. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Recursive
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
Scalar
999999.9999

32. UNION ALL does not sort - therefore it is less time consuming to use than the...
@ or START
Inconsistencies
UNION operator
4

33. 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.
Details
MINUS
Structured Query Language

34. To equijoin a table with another table involves matching the...
Common column value
Command returns the number of records returned by a query.
ALL_OBJECTS
Another null

35. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
Implicit conversion
Non-matching rows
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
**** 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

36. The _______ function lets you perform if-then-else functionality with the SQL language.
Same
Month ddth - yyyy
DECODE
Equijoin and correlated subquery

37. A transaction is a...
Logical unit of work
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
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
SET clause of an UPDATE statement

38. List the different types of data dictionary views.
Retrieve
Surrogate key
USER_ - ALL_ - DBA_ - and V$
Same

39. List the steps performed by a correlated subquery.
**** 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
Equijoin
RIGHT LEFT JOIN
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

40. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
DICT_COLUMNS
Surrogate key
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
X

41. You can pass parameters from the operating system when ___________ in SQl*Plus
Scale
Running a script file
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.
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.

42. Write the syntax to rename index just_two_more_weeks_i to finally_i
B-Tree Index
RENAME just_two_more_weeks TO finally_i
4000
Has not changed the name

43. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Crow's foot
Optional
Hide data
Testing functions

44. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
Twice
Logical unit of work
TO_CHAR function
X

45. Comparison operators can compare multiple _________________.
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.
Columns
Optional
User-defined reports

46. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
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
NATURAL JOIN
Testing functions

47. Queries that are found in parentheses are done first - just as in the order of operations when doing...
User-defined reports
12:00:00 A.M
Mathematical equations
Business problems

48. FEEDBACK
Command returns the number of records returned by a query.
A correct statement
DD-MON-YY
Script

49. The same function can be used ______ in a SELECT statement.
DECODE
Equijoin and identical column names
Twice
INNER JOIN

50. The __________ operator is inclusive of the two values specified.
Scalar
X
Re-create
BETWEEN