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. 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.
RIGHT LEFT JOIN
DECODE
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Scale

2. You use the _________ keyword to add columns or constraints to a table.
USER_ - ALL_ - DBA_ - and V$
ON
Scale
ADD

3. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Same
Correlated
INNER JOIN
Black

4. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
VALUES
@ or START
BETWEEN
SELECT DISTINCT menu FROM restaurant

5. Explain what a static data dictionary view is and how it is used
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.
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
Mathematical equations

6. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
User-defined reports
Any
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.

7. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Orphan
Rounded edges
ADD_MONTHS function
Has not changed the name

8. Write a statement for SQL*Plus that shows the dictionary.
Committed
Crow's foot
DESC dict;
SET clause of an UPDATE statement

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

10. A _________ subquery returns a single column with a single row
LIKE
MODIFY
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Scalar

11. UNION ALL does not sort - therefore it is less time consuming to use than the...
Any
Actual column
TO_CHAR function
UNION operator

12. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Both MIN and MAX
+
Equijoin and correlated subquery
4000

13. The query retrieves more than 5 to 15 percent of the rows
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Leading-edge
DESC dict;
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

14. The COUNT function uses the __________ wildcard.
ROLLBACK
Asterisk
F12
SET clause of an UPDATE statement

15. The ________ operator cannot compare against a list of values.
Semicolon or forward slash
IN ANY ALL SOME
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
LIKE

16. What does SQL stand for?
Export Data
Structured Query Language
A correct statement
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

17. What is the maximum value for a column defined as NUMBER(4 -2)?
Nested
99.99
Data
DICTIONARY

18. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
Equijoin and correlated subquery
4
VALUES clause of an INSERT statement
Multiple rows

19. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
**** 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
Inconsistencies
RENAME just_two_more_weeks TO finally_i

20. How many characters can you have using the VARCHAR data type?
B-Tree Index
Time zone
CLOB
4000

21. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
Re-create
Export Data
UTC
ON

22. Converting a text literal to DATE format requires the use of the...
Implicit conversion
TO_CHAR function
SELECT DISTINCT menu FROM restaurant
Same

23. You use the __________ keyword to change characteristics of a column.
Primary key/foreign key
Retrieve
Time zone
MODIFY

24. Explain what is found in the All Objects report folder in SQL Developer
Primary key/foreign key
A correct statement
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Surrogate

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

26. Write the syntax to rename index just_two_more_weeks_i to finally_i
Actual column
RENAME just_two_more_weeks TO finally_i
FALSE
BETWEEN

27. When is it not a good idea to use an index?
SET clause of an UPDATE statement
Inconsistencies
When the table is small (unless your business rules require you too)
Committed

28. Checks whether any value in the list makes the condition true.
Optional
Any
When the table is small (unless your business rules require you too)
Rounded edges

29. SELECT LIST of a subquery in an INSERT statement
Multiple rows
SET clause of an UPDATE statement
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.
Parent table

30. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Mathematical equations
Results
Orphan

31. What is the tab in SQL Developer where data dictionary view information can be found?
FALSE
Details
Implicit conversion
BETWEEN

32. What is the data type you can use if you need an unlimited number of textual characters?
Surrogate key
CLOB
ANSI
Combine

33. When you run a script in SQL*Plus you must give the...
12:00:00 A.M
Semicolon or forward slash
Black
Location of the file

34. How many characters can be stored in the CHAR column/field?
The way NULL values are treated
Leading-edge
F12
2000

35. Subqueries can return...
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Multiple rows
@ or START
LIKE

36. Explain in detail the impact of INDEXES and how they are used by Oracle.
F12
Business problems
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
Static or dynamic

37. The key that most database designers prefer and is chosen by the system is the _____________ key.
Combine
The way NULL values are treated
A correct statement
Surrogate

38. PAGESIZE
Script
Run scripts
Default is 14; when you use the default - you repeat the heading every 14 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

39. The ______ operator compares a list of values for equivalency
Columns
SELECT DISTINCT menu FROM restaurant
ANY
+

40. The DUAL table can be used for...
VALUES clause of an INSERT statement
Non-matching rows
Parent table
Testing functions

41. A join across three tables can be expressed with the ________ join syntax.
Groups
Black
ANSI
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

42. What is the name of the key automatically generated from a sequence?
Surrogate key
Actual column
Equijoin and correlated subquery
ANY

43. List the reasons that a subquery could not be unnested.
X
Results
DD-MON-YY
RENAME just_two_more_weeks TO finally_i

44. List 4 aggregate functions
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
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Location of the file
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

45. You can use the ________________ to subtract months from a given date.
Export Data
When the table is small (unless your business rules require you too)
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
ADD_MONTHS function

46. Explicit data type conversion is preferable to Oracle's...
Run scripts
X
Implicit conversion
All caps

47. A transaction is a...
Logical unit of work
SELECT SYSDATE+1/96 - FROM dual
Null
COMMIT

48. Explain the steps needed to run a script in SQL Developer
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Retrieve
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.
Surrogate key

49. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
IN ANY ALL SOME
DESC dict;
INTERSECT
USER_ - ALL_ - DBA_ - and V$

50. What is the most common DATE format found in SQL?
Black
DD-MON-YY
CLOB
2000