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. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
Export Data
Null
Orphan
Month ddth - yyyy

2. You use the _________ keyword to add columns or constraints to a table.
TO_CHAR function
X
ADD
Inconsistencies

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.
Actual column
Has not changed the name
BETWEEN
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.

4. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Business problems
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
MODIFY
MINUS

5. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
X
NATURAL JOIN
Non-matching rows
SET clause of an UPDATE statement

6. 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.
Same
RIGHT LEFT JOIN
Committed
Retrieve

7. Self-joins are useful for performing comparisons and checking for ___________ in data.
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Inconsistencies
Primary key/foreign key
ANSI

8. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
GROUP BY
IN ANY ALL SOME
LIKE
BETWEEN

9. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
INNER JOIN
KEEP INDEX
DICT_COLUMNS
X

10. The COUNT function uses the __________ wildcard.
Asterisk
Three
SET clause of an UPDATE statement
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

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

12. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
User-defined reports
MINUS
**** 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
Static or dynamic

13. When you run a script in SQL*Plus you must give the...
SUM
Business problems
Location of the file
Primary key/foreign key

14. What are the main components of a ROWID?
Running a script file
UNION
RENAME just_two_more_weeks TO finally_i
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.

15. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
UTC
SELECT DISTINCT menu FROM restaurant
Same
MODIFY

16. To join four tables - you must have at least _____ join conditions.
Scale
Three
Results
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

17. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
Fractional seconds
Command returns the number of records returned by a query.
UNION
ROLLBACK

18. UNION ALL does not sort - therefore it is less time consuming to use than the...
BETWEEN
UNION operator
ROLLBACK
SELECT DISTINCT menu FROM restaurant

19. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Non-matching rows
Has not changed the name
UNION
999999.9999

20. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
LENGTH
SYSDATE
STORAGE

21. Checks whether any value in the list makes the condition true.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Any
99.99
F12

22. Dynamic views begin with a ___________.
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
V$
Export Data
Correlated

23. List the different types of data dictionary views.
Logical unit of work
12:00:00 A.M
USER_ - ALL_ - DBA_ - and V$
Re-create

24. A join across three tables can be expressed with the ________ join syntax.
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
Running a script file
Non-matching rows
ANSI

25. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
MODIFY
+
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
Black

26. Converting a text literal to DATE format requires the use of the...
TO_CHAR function
IN ANY ALL SOME
X
Semicolon or forward slash

27. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Both MIN and MAX
VALUES clause of an INSERT statement
Location of the file
INTERSECT

28. The _______ operator determines common rows.
Retrieve
Null
12:00:00 A.M
INTERSECT

29. _________ rows are not allowed in the relational model.
Orphan
Columns
Primary key/foreign key
SELECT DISTINCT menu FROM restaurant

30. The term ____________ ___________ is used to express a join that satisfies the join condition.
Results
Fractional seconds
B-Tree Index
INNER JOIN

31. A _________ subquery returns a single column with a single row
F12
Scalar
The way NULL values are treated
Fractional seconds

32. The _______ function adds values together for a group of rows.
SUM
Multiple rows
Another null
Non-matching rows

33. List 4 aggregate functions
B-Tree Index
Extra spaces
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
F12

34. Comparison operators can compare multiple _________________.
Columns
Inconsistencies
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
All

35. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Crow's foot
DICTIONARY
Semicolon or forward slash
Null

36. The ________ operator cannot compare against a list of values.
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.
Leading-edge
DICT_COLUMNS
LIKE

37. The ____________ command makes changes permanent.
COMMIT
Same
BETWEEN
The indexed column is part of an expression. In this case - consider creating a function-based index instead

38. You can pass parameters from the operating system when ___________ in SQl*Plus
Surrogate key
Running a script file
Groups
All caps

39. It is considered good form to put all SQL commands in...
SUM
Black
All caps
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

40. The purpose of the HAVING clause is to eliminate___________.
DESC dict;
Groups
Recursive
DD-MON-YY

41. Users can only see changes made to a database once they have been...
Correlated
VALUES clause of an INSERT statement
Committed
SELECT DISTINCT menu FROM restaurant

42. The NEXTVAL and CURRVAL pseudocolumns can be used in which of the following SQL constructs? Select all that apply.
Semicolon or forward slash
Optional
Twice
VALUES clause of an INSERT statement

43. Explain Flashback queries and how they are used. Give an example of a FLASHBACK_TRANSACTION query and explain what it does row by row.

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

44. The ______ operator compares a list of values for equivalency
ANY
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
BETWEEN

45. How many characters can be stored in the CHAR column/field?
@ or START
X
2000
UTC

46. Which one of the following string functions tells you how many characters are in a string?
Both MIN and MAX
Results
LENGTH
The indexed column is part of an expression. In this case - consider creating a function-based index instead

47. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
SUM
User-defined reports
SELECT DISTINCT menu FROM restaurant
DESC dict;

48. What is the keyboard shortcut for the ROLLBACK command?
F12
X
STORAGE
Script

49. Checks whether any value in the list makes the condition true.
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
ADD
2000
Any

50. Explain how Substitution Variables are used when writing scripts.
GROUP BY
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.
When the table is small (unless your business rules require you too)
INNER JOIN