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. A _________ subquery returns a single column with a single row
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Scalar
Running a script file
Surrogate key

2. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
BETWEEN
Rounded edges
USER_ - ALL_ - DBA_ - and V$
Committed

3. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Retrieve
Correlated
X
Precision

4. The ____________ command makes changes permanent.
COMMIT
X
X
ANY

5. The __________ operator is inclusive of the two values specified.
VALUES
Month ddth - yyyy
BETWEEN
Orphan

6. Write a solution that adds 20 minutes to a given date.
INNER JOIN
ANSI
SELECT SYSDATE+1/96 - FROM dual
Parent table

7. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
Time zone
Business problems
@ or START
A correct statement

8. The TRUNC function on a date without a format model truncates the timestamp to...
UTC
12:00:00 A.M
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.
UNION operator

9. Checks whether any value in the list makes the condition true.
RUN SCRIPT
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.
SELECT DISTINCT menu FROM restaurant
Any

10. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Another null
VALUES
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
Equijoin and identical column names

11. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
Results
Combine
IN ANY ALL SOME
SELECT SYSDATE+1/96 - FROM dual

12. The term ____________ ___________ is used to express a join that satisfies the join condition.
Rounded edges
Logical unit of work
INNER JOIN
UTC

13. 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.
X
Structured Query Language
Equijoin
RIGHT LEFT JOIN

14. What is the tab in SQL Developer where data dictionary view information can be found?
Rounded edges
KEEP INDEX
Details
Retrieve

15. Which operator is most commonly used?
ALL_OBJECTS
ROLLBACK
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
UNION

16. Users can only see changes made to a database once they have been...
UNION operator
Self-joins
Scale
Committed

17. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Both MIN and MAX
INTERSECT
RUN SCRIPT

18. NUMBER functions can be...
B-Tree Index
Any
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Nested

19. Explain the differences in how an ANSI Full Outer Join and a Full Outer Join using the UNION Operator work
Three
INNER JOIN
X
Orphan

20. The COUNT function uses the __________ wildcard.
CLOB
IN ANY ALL SOME
Asterisk
Testing functions

21. The DUAL table can be used for...
ON
999999.9999
Testing functions
2000

22. Which format mask displays December 31st - 1999?
Equijoin and identical column names
Month ddth - yyyy
Actual column
Parent table

23. Explain the steps needed to run a script in SQL Developer
Actual column
CLOB
DICTIONARY
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.

24. When using the following data: NUMBER(10 -4) - the # 10 is called the __________.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Null
Precision
KEEP INDEX

25. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
MINUS
IN ANY ALL SOME
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.
Location of the file

26. Comparison operators can compare multiple _________________.
FALSE
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Columns
Self-joins

27. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
BETWEEN
V$
X

28. 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
Hide data
All
Multiple rows

29. SELECT * FROM instructor ORDER BY phone
A correct statement
DESC dict;
Same
Scale

30. What does SQL stand for?
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
Surrogate key
Structured Query Language
DD-MON-YY

31. The key that most database designers prefer and is chosen by the system is the _____________ key.
Mathematical equations
ADD_MONTHS function
Surrogate
SUM

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

33. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Asterisk
Equijoin
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.

34. SELECT LIST of a subquery in an INSERT statement
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Any
SET clause of an UPDATE statement
Scalar

35. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
Asterisk
Any
TO_CHAR function
Export Data

36. 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?
X
Extra spaces
DICTIONARY
ALL_OBJECTS

37. Self-joins are useful for performing comparisons and checking for ___________ in data.
Inconsistencies
Null
Black
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

38. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Mathematical equations
B-Tree Index
Semicolon or forward slash
Scale

39. Oracle uses the outer join operator to indicate that nulls are shown for...
Same
CLOB
+
Non-matching rows

40. When column names on the tables are different - you use the ______ condition.
@ or START
Export Data
ON
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

41. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
4
SYSDATE
Optional
User-defined reports

42. The COUNT function is useful for determining whether a table has...
USER_ - ALL_ - DBA_ - and V$
Data
Hide data
SET clause of an UPDATE statement

43. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
4000
Optional
Multiple rows

44. What does the following function return? SELECT LENGTH(NULL) - FROM dual
FALSE
SET clause of an UPDATE statement
Time zone
Null

45. In SQL Developer field names and table names are shown in the color _______.
99.99
Another null
ANSI
Black

46. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
Script
Static or dynamic
KEEP INDEX
USER_ - ALL_ - DBA_ - and V$

47. Explain what a static data dictionary view is and how it is used
Script
ALL_OBJECTS
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.
Re-create

48. When you run a script in SQL*Plus you must give the...
DESC dict;
Location of the file
FALSE
Testing functions

49. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Actual column
User-defined reports
Results
LENGTH

50. __________________ inserts the current date and time.
4
F12
Testing functions
SYSDATE