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

2. To equijoin a table with another table involves matching the...
SET clause of an UPDATE statement
Surrogate
Has not changed the name
Common column value

3. The _____________ clause determines how rows are grouped.
Mathematical equations
GROUP BY
VALUES clause of an INSERT statement
X

4. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
UNION operator
ANY
Equijoin and correlated subquery
Export Data

5. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Static or dynamic
Rounded edges
Null
Correlated

6. List the steps performed by a correlated subquery.
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
Any
Self-joins

7. A calculation with a null always yields...
Another null
Equijoin and correlated subquery
Asterisk
Semicolon or forward slash

8. The query retrieves more than 5 to 15 percent of the rows
4
ADD
Another null
The indexed column is part of an expression. In this case - consider creating a function-based index instead

9. Match each of the SQL commands on the left with a verb from the list.
X
Actual column
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
INNER JOIN

10. Joins involving multiple columns must always follow the ____________ relationship path.
999999.9999
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
Primary key/foreign key
V$

11. What is the data type you can use if you need an unlimited number of textual characters?
CLOB
Null
Any
Scale

12. List the reasons that a subquery could not be unnested.
Multiple rows
UNION
Crow's foot
X

13. You can pass parameters from the operating system when ___________ in SQl*Plus
Self-joins
Running a script file
Parent table
X

14. The _______ function adds values together for a group of rows.
SUM
Run scripts
Structured Query Language
FALSE

15. Checks whether any value in the list makes the condition true.
Any
COMMIT
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
ANSI

16. SELECT LIST of a subquery in an INSERT statement
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
DECODE
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.
SET clause of an UPDATE statement

17. A _________ subquery returns a single column with a single row
Scalar
Command returns the number of records returned by a query.
DROP INDEX need_to_pass_i
Committed

18. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
Primary key/foreign key
MINUS
DICTIONARY
Structured Query Language

19. Which one of the following string functions tells you how many characters are in a string?
LENGTH
RUN SCRIPT
RIGHT LEFT JOIN
SYSDATE

20. When is it not a good idea to use an index?
Asterisk
Export Data
Month ddth - yyyy
When the table is small (unless your business rules require you too)

21. NUMBER functions can be...
All
SELECT SYSDATE+1/96 - FROM dual
UTC
Nested

22. 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.
FALSE
Retrieve
+
Correlated

23. To join four tables - you must have at least _____ join conditions.
When the table is small (unless your business rules require you too)
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
RENAME just_two_more_weeks TO finally_i
Three

24. The ROWID pseudocolumn is not an ________- but it acts like one.
Self-joins
SET clause of an UPDATE statement
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Actual column

25. What is Oracle's most popular index storage structure?
VALUES
B-Tree Index
Actual column
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

26. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
ROLLBACK
Groups
Logical unit of work
Results

27. The _______ function lets you perform if-then-else functionality with the SQL language.
The way NULL values are treated
DECODE
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
Results

28. The TRUNC function on a date without a format model truncates the timestamp to...
Script
USER_ - ALL_ - DBA_ - and V$
Run scripts
12:00:00 A.M

29. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
Equijoin and identical column names
BETWEEN
Re-create
MODIFY

30. UNION ALL does not sort - therefore it is less time consuming to use than the...
Black
Surrogate key
UNION operator
Optional

31. Users can only see changes made to a database once they have been...
Crow's foot
Equijoin and correlated subquery
RUN SCRIPT
Committed

32. The COUNT function uses the __________ wildcard.
Scalar
NATURAL JOIN
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Asterisk

33. ____________ are useful for analyzing and exploring the relationships within your data
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Self-joins
Optional
Data

34. Instead of using the Oracle Outer Join Operator or the ANSI syntax - you can achieve the same result with two SQL statements: ____________ and a ____________
Static or dynamic
F12
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 correlated subquery

35. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
RUN SCRIPT
F12
ADD
All

36. When you have issued a DELETE command - you can still ________ those rows and restore the information.
SELECT SYSDATE+1/96 - FROM dual
Retrieve
Common column value
Semicolon or forward slash

37. Explain in detail the impact of INDEXES and how they are used by Oracle.
Run scripts
When the table is small (unless your business rules require you too)
INNER JOIN
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

38. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
UNION operator
SELECT DISTINCT menu FROM restaurant
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

39. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
TO_CHAR function
SYSDATE
Three
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

40. The key that most database designers prefer and is chosen by the system is the _____________ key.
Surrogate
BETWEEN
Precision
Location of the file

41. Which operator is most commonly used?
UTC
UNION
When the table is small (unless your business rules require you too)
SUM

42. The ________ operator cannot compare against a list of 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
NATURAL JOIN
Export Data
LIKE

43. Dynamic views begin with a ___________.
V$
SET clause of an UPDATE statement
Actual column
SUM

44. The using clause of the ANSI join syntax always assumes an...
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Scale
Equijoin and identical column names
Running a script file

45. The focus of the logical data model is _____________
Rounded edges
Hide data
Business problems
Combine

46. What is the tab in SQL Developer where data dictionary view information can be found?
99.99
Leading-edge
Details
Business problems

47. You can simplify the writing of SQL statements by writing SQL _________ that generate and execute other SQL statements.
Rounded edges
Script
Optional
RUN SCRIPT

48. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
+
NATURAL JOIN
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
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.

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

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