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. Users can only see changes made to a database once they have been...
Committed
12:00:00 A.M
Any
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

2. Converting a text literal to DATE format requires the use of the...
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
Run scripts
+
TO_CHAR function

3. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
BETWEEN
STORAGE
Non-matching rows
COMMIT

4. Match each of the SQL commands on the left with a verb from the list.
2000
Null
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
UNION operator

5. When you INSERT new information into a table - it is followed with a _______ keyword that contains the information that is going to be added to the table.
STORAGE
VALUES
Surrogate
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.

6. When you have issued a DELETE command - you can still ________ those rows and restore the information.
Optional
Retrieve
RUN SCRIPT
Has not changed the name

7. Explain what a static data dictionary view is and how it is used
@ or START
DESC dict;
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.
RIGHT LEFT JOIN

8. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
SYSDATE
BETWEEN
Correlated
4

9. The COUNT function is useful for determining whether a table has...
Data
Surrogate key
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.
Equijoin and correlated subquery

10. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
The way NULL values are treated
SELECT SYSDATE+1/96 - FROM dual
Fractional seconds
MODIFY

11. The _______ function lets you perform if-then-else functionality with the SQL language.
KEEP INDEX
DD-MON-YY
Testing functions
DECODE

12. Which format mask displays December 31st - 1999?
MODIFY
Fractional seconds
Month ddth - yyyy
Optional

13. When you run a script in SQL*Plus you must give the...
Location of the file
IN ANY ALL SOME
X
A correct statement

14. A join across three tables can be expressed with the ________ join syntax.
ANSI
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
99.99
Equijoin

15. The TRUNC function on a date without a format model truncates the timestamp to...
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.
12:00:00 A.M
Recursive
SELECT SYSDATE+1/96 - FROM dual

16. The _______ operator determines common rows.
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
INTERSECT
V$
Asterisk

17. List 6 Table-Related Menu Choices
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Precision
SELECT SYSDATE+1/96 - FROM dual
2000

18. A _____ is a query definition stored in the database that looks just like a table - but does not have any physical rows
X
Mathematical equations
@ or START
INTERSECT

19. How many characters can be stored in the CHAR column/field?
USER_ - ALL_ - DBA_ - and V$
2000
X
Primary key/foreign key

20. LINESIZE
VALUES
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
X
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

21. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
Rounded edges
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
RUN SCRIPT
Scale

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

23. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
Multiple rows
RENAME just_two_more_weeks TO finally_i
Orphan
KEEP INDEX

24. Explain how Substitution Variables are used when writing scripts.
Leading-edge
SYSDATE
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.
F12

25. What is the name of the key automatically generated from a sequence?
2000
ADD_MONTHS function
IN ANY ALL SOME
Surrogate key

26. It is considered good form to put all SQL commands in...
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.
Precision
All caps
Crow's foot

27. You can use the ________________ to subtract months from a given date.
ADD_MONTHS function
ON
DROP INDEX need_to_pass_i
UNION

28. The ROWID pseudocolumn is not an ________- but it acts like one.
ROLLBACK
Leading-edge
Mathematical equations
Actual column

29. List the reasons that a subquery could not be unnested.
Equijoin
DD-MON-YY
X
DICTIONARY

30. Write a statement for SQL*Plus that shows the dictionary.
DESC dict;
VALUES clause of an INSERT statement
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Structured Query Language

31. The purpose of the HAVING clause is to eliminate___________.
Committed
X
Groups
DICTIONARY

32. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
FALSE
RENAME just_two_more_weeks TO finally_i
**** 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
BETWEEN

33. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
MINUS
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Scale

34. What do views do that makes them so useful for security?
Hide data
999999.9999
Has not changed the name
Non-matching rows

35. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
A correct statement
DROP INDEX need_to_pass_i
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
ALL_OBJECTS

36. The ________ operator cannot compare against a list of values.
LIKE
Another null
USER_ - ALL_ - DBA_ - and V$
RIGHT LEFT JOIN

37. In SQL Developer field names and table names are shown in the color _______.
DESC dict;
Black
LENGTH
Hide data

38. You use the _________ keyword to add columns or constraints to a table.
Correlated
ADD
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
MINUS

39. PAGESIZE
Committed
99.99
GROUP BY
Default is 14; when you use the default - you repeat the heading every 14 rows.

40. The using clause of the ANSI join syntax always assumes an...
Equijoin and identical column names
+
X
Parent table

41. Self-joins are useful for performing comparisons and checking for ___________ in data.
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
UTC
All caps
Inconsistencies

42. To restart a sequence at a lower number - you can drop and ________ the sequence.
Hide data
Re-create
Self-joins
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.

43. Joins involving multiple columns must always follow the ____________ relationship path.
Crow's foot
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Primary key/foreign key
Surrogate

44. What are the main components of a ROWID?
INNER JOIN
Running a script file
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.
X

45. A _________ subquery returns a single column with a single row
Scalar
IN ANY ALL SOME
LENGTH
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.

46. What does SQL stand for?
Structured Query Language
Running a script file
Export Data
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.

47. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
User-defined reports
All
@ or START
MINUS

48. Write a statement that drops the index need_to_pass_i
V$
ADD_MONTHS function
DROP INDEX need_to_pass_i
ROLLBACK

49. The ___________ operator tests whether a matching row cannot be found.
X
Any
Run scripts
VALUES clause of an INSERT statement

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