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. What is the first column of a composite index called?
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
DROP INDEX need_to_pass_i
Leading-edge
Rounded edges

2. The purpose of the HAVING clause is to eliminate___________.
Hide data
V$
Groups
Equijoin

3. The focus of the logical data model is _____________
LENGTH
Nested
Business problems
COMMIT

4. The ______ depicts the M of a 1:M relationship.

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

5. 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?
DICTIONARY
Optional
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Null

6. List 6 Table-Related Menu Choices
Running a script file
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
ON

7. NUMBER functions can be...
Nested
Export Data
Common column value
4000

8. What is the tab in SQL Developer where data dictionary view information can be found?
MODIFY
F12
4000
Details

9. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
999999.9999
Twice
ADD_MONTHS function
X

10. Oracle uses the outer join operator to indicate that nulls are shown for...
Black
DECODE
RIGHT LEFT JOIN
Non-matching rows

11. List the reasons that a subquery could not be unnested.
X
Equijoin and identical column names
ANSI
Both MIN and MAX

12. The ________ operator cannot compare against a list of values.
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.
F12
LIKE
SELECT SYSDATE+1/96 - FROM dual

13. Dynamic views begin with a ___________.
Data
Another null
V$
Structured Query Language

14. When column names on the tables are different - you use the ______ condition.
RUN SCRIPT
ON
ANY
Mathematical equations

15. ____________ are useful for analyzing and exploring the relationships within your data
DICTIONARY
B-Tree Index
Columns
Self-joins

16. The _____________ clause determines how rows are grouped.
Committed
GROUP BY
X
All caps

17. What is the most common DATE format found in SQL?
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
Twice
DD-MON-YY
ALL_OBJECTS

18. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
4
Scalar
Surrogate key
Committed

19. How many characters can be stored in the CHAR column/field?
X
Script
2000
UTC

20. What is the data type you can use if you need an unlimited number of textual characters?
X
CLOB
X
Nested

21. The _______ function adds values together for a group of rows.
SUM
F12
Running a script file
Static or dynamic

22. Queries that are found in parentheses are done first - just as in the order of operations when doing...
+
LENGTH
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Mathematical equations

23. The using clause of the ANSI join syntax always assumes an...
Location of the file
COMMIT
SYSDATE
Equijoin and identical column names

24. UNION ALL does not sort - therefore it is less time consuming to use than the...
UNION operator
4000
Testing functions
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

25. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
Hide data
Retrieve
ALL_OBJECTS

26. Explain how you can optimize your queries with subqueries and the main advantage of doing so.
Location of the file
@ or START
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
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

27. The Nonequijoin can use the ________________ operator to construct joins that are not based on equality of values.
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.
Re-create
B-Tree Index
BETWEEN

28. 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
Scale
Optional
Asterisk

29. What do views do that makes them so useful for security?
Groups
COMMIT
B-Tree Index
Hide data

30. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Non-matching rows
BETWEEN
RUN SCRIPT
NATURAL JOIN

31. The RTRIM function is useful for eliminating ________ in a string.
Re-create
UNION
RENAME just_two_more_weeks TO finally_i
Extra spaces

32. The key that most database designers prefer and is chosen by the system is the _____________ key.
Black
UTC
Surrogate
Parent table

33. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
2000
Scalar
User-defined reports
DICT_COLUMNS

34. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
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
Same
ON

35. If a graphical representation of a table's box has __________ it means that the relationship is identifying.
Equijoin and identical column names
DECODE
Equijoin and correlated subquery
Rounded edges

36. When you want to run a script in either SQL*Plus or SQL Developer - you can use a ______ or the _________ command.
TO_CHAR function
ADD
Non-matching rows
@ or START

37. When using the following data: NUMBER(10 -4) - the # 4 is called the __________.
GROUP BY
Scale
SELECT DISTINCT menu FROM restaurant
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

38. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
Semicolon or forward slash
VALUES clause of an INSERT statement
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.
INTERSECT

39. To join four tables - you must have at least _____ join conditions.
Three
Surrogate key
Month ddth - yyyy
X

40. _________ and __________ functions are opposites of each other and provide a value in a group of rows.
Surrogate key
Both MIN and MAX
X
UNION

41. Joins involving multiple columns must always follow the ____________ relationship path.
Primary key/foreign key
Common column value
Twice
Scale

42. __________________ inserts the current date and time.
SELECT DISTINCT menu FROM restaurant
12:00:00 A.M
Groups
SYSDATE

43. The same function can be used ______ in a SELECT statement.
ANY
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
Twice
All

44. To restart a sequence at a lower number - you can drop and ________ the sequence.
F12
Re-create
DESC dict;
UNION operator

45. A join across three tables can be expressed with the ________ join syntax.
Run scripts
Export Data
ANSI
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.

46. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
The way NULL values are treated
MODIFY
RIGHT LEFT JOIN
Fractional seconds

47. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
STORAGE
BETWEEN
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
ADD_MONTHS function

48. When is it not a good idea to use an index?
ANY
ADD
Twice
When the table is small (unless your business rules require you too)

49. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
The way NULL values are treated
MINUS
Actual column
KEEP INDEX

50. What is the main difference between the NOT EXISTS and the NOT IN operators?
The way NULL values are treated
Any
FALSE
DESC dict;