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 ROWID pseudocolumn is not an ________- but it acts like one.
Primary key/foreign key
Results
The way NULL values are treated
Actual column

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.
Has not changed the name
Script
Groups
The indexed column is part of an expression. In this case - consider creating a function-based index instead

3. List the reasons that a subquery could not be unnested.
Run scripts
Optional
INNER JOIN
X

4. When column names on the tables are different - you use the ______ condition.
ON
Correlated
Twice
Default is 14; when you use the default - you repeat the heading every 14 rows.

5. 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.
Black
SET clause of an UPDATE statement
UNION

6. Explain why constraints are used when building tables.

Warning: Invalid argument supplied for foreach() in /var/www/html/basicversity.com/show_quiz.php on line 183

7. What is the name of the key automatically generated from a sequence?
User-defined reports
4
Precision
Surrogate key

8. What do views do that makes them so useful for security?
Default is 14; when you use the default - you repeat the heading every 14 rows.
Any
Hide data
Asterisk

9. SQL*Plus is used more than SQL Developer when you need to...
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Run scripts
Three
2000

10. You can use the ________________ to subtract months from a given date.
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.
ADD_MONTHS function
SELECT DISTINCT menu FROM restaurant
FALSE

11. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
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
Details
Optional
Any

12. 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.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Inconsistencies
RIGHT LEFT JOIN
4000

13. Returns rows if the condition is true for all the values in the list
B-Tree Index
All
Nested
Primary key/foreign key

14. It is considered good form to put all SQL commands in...
The way NULL values are treated
SELECT SYSDATE+1/96 - FROM dual
Optional
All caps

15. 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.
Inconsistencies
A correct statement
FALSE
NATURAL JOIN

16. The COUNT function uses the __________ wildcard.
Month ddth - yyyy
Asterisk
4
SET clause of an UPDATE statement

17. The time zone displacement value indicates the time difference from _______.
@ or START
Rounded edges
F12
UTC

18. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
99.99
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
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
STORAGE

19. 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.
@ or START
Any
VALUES
Rounded edges

20. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
All caps
SET clause of an UPDATE statement
INNER JOIN
999999.9999

21. The query retrieves more than 5 to 15 percent of the rows
VALUES clause of an INSERT statement
NATURAL JOIN
The indexed column is part of an expression. In this case - consider creating a function-based index instead
V$

22. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
+
IN ANY ALL SOME
Default is 14; when you use the default - you repeat the heading every 14 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

23. SELECT LIST of a subquery in an INSERT statement
LENGTH
SET clause of an UPDATE statement
TO_CHAR function
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

24. In a view you can give a column a different name than it has in the...
Black
Any
Parent table
Equijoin

25. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Same
Black
Results
Actual column

26. Write the syntax to rename index just_two_more_weeks_i to finally_i
Common column value
GROUP BY
Null
RENAME just_two_more_weeks TO finally_i

27. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
ADD
Semicolon or forward slash
NATURAL JOIN
Equijoin

28. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
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.
Both MIN and MAX
Primary key/foreign key
UNION

29. When is it not a good idea to use an index?
When the table is small (unless your business rules require you too)
ON
4000
STORAGE

30. LINESIZE
DROP INDEX need_to_pass_i
Details
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
A correct statement

31. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Surrogate key
RIGHT LEFT JOIN
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Re-create

32. The ALTER SESSION statement can change the session's...
DROP INDEX need_to_pass_i
Time zone
Black
Details

33. The focus of the logical data model is _____________
Business problems
The indexed column is part of an expression. In this case - consider creating a function-based index instead
Correlated
Another null

34. Write a statement for SQL*Plus that shows the dictionary.
Run scripts
DESC dict;
All
UNION operator

35. The ____________ command makes changes permanent.
Same
COMMIT
Logical unit of work
Equijoin and correlated subquery

36. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
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.
Inconsistencies
SELECT DISTINCT menu FROM restaurant
RIGHT LEFT JOIN

37. To equijoin a table with another table involves matching the...
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
Extra spaces
Self-joins
Common column value

38. SELECT * FROM instructor ORDER BY phone
BETWEEN
A correct statement
Equijoin
ON

39. _________ rows are not allowed in the relational model.
Default is 14; when you use the default - you repeat the heading every 14 rows.
STORAGE
Orphan
The way NULL values are treated

40. Queries that are found in parentheses are done first - just as in the order of operations when doing...
Primary key/foreign key
FALSE
Mathematical equations
Structured Query Language

41. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
User-defined reports
SET clause of an UPDATE statement
BETWEEN
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

42. What is the data type you can use if you need an unlimited number of textual characters?
+
UNION operator
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
CLOB

43. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
Twice
4
USER_ - ALL_ - DBA_ - and V$
ANSI

44. UNION ALL does not sort - therefore it is less time consuming to use than the...
A correct statement
UNION operator
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
SUM

45. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
COMMIT
Correlated
Export Data
LIKE

46. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
DD-MON-YY
KEEP INDEX
Inconsistencies
Equijoin and correlated subquery

47. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
CLOB
Same
+
X

48. 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
Primary key/foreign key
LIKE
The indexed column is part of an expression. In this case - consider creating a function-based index instead

49. You can __________ DISTINCT and a GROUP BY clause in the same SELECT statement.
F12
Hide data
COMMIT
Combine

50. Checks whether any value in the list makes the condition true.
Any
2000
Re-create
Optional