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. Explain how the SPOOL command is used and what program it can be used in.
Null
Twice
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
VALUES

2. All SQL statements contained within a script must end with a _______________ - even if they are done in SQL Developer.
All
Common column value
VALUES clause of an INSERT statement
Semicolon or forward slash

3. PAGESIZE
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
Default is 14; when you use the default - you repeat the heading every 14 rows.
Self-joins
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.

4. The _______ operator determines common rows.
B-Tree Index
USER_ - ALL_ - DBA_ - and V$
INTERSECT
A correct statement

5. List 4 aggregate functions
Fractional seconds
Equijoin and correlated subquery
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
SET clause of an UPDATE statement

6. Information that is returned after you run your statement are found in the __________ tab in SQL Developer.
Primary key/foreign key
Results
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
Common column value

7. ___________ subqueries allow you to reference columns from the outer query and execute the inner query repeatedly
Any
Surrogate key
DICT_COLUMNS
Correlated

8. Checks whether any value in the list makes the condition true.
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
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.
Both MIN and MAX

9. Write a solution that adds 20 minutes to a given date.
A correct statement
SELECT SYSDATE+1/96 - FROM dual
BETWEEN
X

10. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
CLOB
Any
BETWEEN
SELECT DISTINCT menu FROM restaurant

11. The data type definitions NUMBER(10) and NUMBER(10 -0) are the...
Equijoin
Inconsistencies
User-defined reports
Same

12. The ____________ command makes changes permanent.
DICT_COLUMNS
Recursive
COMMIT
MODIFY

13. The RTRIM function is useful for eliminating ________ in a string.
Location of the file
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
Extra spaces
When the table is small (unless your business rules require you too)

14. Write a statement for SQL*Plus that shows the dictionary.
+
SYSDATE
DESC dict;
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

15. It is considered good form to put all SQL commands in...
DICTIONARY
Committed
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.
All caps

16. Explain how Dynamic SQL is used and give an example of a Dynamic SQL statement.
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
Run scripts
Orphan
4000

17. How many characters can be stored in the CHAR column/field?
VALUES clause of an INSERT statement
2000
SUM
Common column value

18. The time zone displacement value indicates the time difference from _______.
UTC
DESC dict;
Actual column
DICTIONARY

19. Checks whether any value in the list makes the condition true.
ALL_OBJECTS
Any
Hide data
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.

20. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
DESC dict;
Same
12:00:00 A.M

21. The TIMESTAMP WITH LOCAL TIME ZONE data type allows...
99.99
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.
Fractional seconds
4

22. The _____________ clause determines how rows are grouped.
GROUP BY
LENGTH
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
Location of the file

23. Oracle uses the outer join operator to indicate that nulls are shown for...
FALSE
Non-matching rows
Hide data
UTC

24. To join four tables - you must have at least _____ join conditions.
Logical unit of work
SET clause of an UPDATE statement
Three
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

25. 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
UNION operator
B-Tree Index

26. Which operator is most commonly used?
RIGHT LEFT JOIN
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.
Groups
UNION

27. What is the data type you can use if you need an unlimited number of textual characters?
ON
CLOB
COMMIT
LENGTH

28. The key that most database designers prefer and is chosen by the system is the _____________ key.
Surrogate
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
Nested
Details

29. What is the syntax used to indicate that you are using the Oracle Outer Join operator?
X
Self-joins
4
+

30. An outer join includes the result rows returned by an __________ plus extra rows where no matches are found.
Business problems
ALL_OBJECTS
999999.9999
Equijoin

31. A _________ subquery returns a single column with a single row
Testing functions
Correlated
Scalar
ALL_OBJECTS

32. Explain the steps needed to run a script in SQL Developer
Default is 14; when you use the default - you repeat the heading every 14 rows.
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
MODIFY
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.

33. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
Committed
DICT_COLUMNS
Run scripts
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.

34. The _______ function adds values together for a group of rows.
SUM
Recursive
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.
Correlated

35. What is the first column of a composite index called?
DD-MON-YY
Combine
Leading-edge
Month ddth - yyyy

36. A join across three tables can be expressed with the ________ join syntax.
Leading-edge
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
NATURAL JOIN
ANSI

37. List the different types of data dictionary views.
Rounded edges
SELECT DISTINCT menu FROM restaurant
USER_ - ALL_ - DBA_ - and V$
Both MIN and MAX

38. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
4
RENAME just_two_more_weeks TO finally_i
UNION operator
Scalar

39. Which one of the following string functions tells you how many characters are in a string?
Scale
Null
LENGTH
Any

40. What is the most common DATE format found in SQL?
Export Data
IN ANY ALL SOME
DD-MON-YY
Both MIN and MAX

41. When column names on the tables are different - you use the ______ condition.
ON
ADD_MONTHS function
Script
SUM

42. The ____________ clause on a CREATE TABLE statement can specify how much space to allocate.
Same
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
STORAGE
INNER JOIN

43. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
Inconsistencies
Command returns the number of records returned by a query.
RUN SCRIPT
KEEP INDEX

44. NUMBER functions can be...
UTC
ADD_MONTHS function
Nested
Columns

45. In SQL Developer field names and table names are shown in the color _______.
Leading-edge
LIKE
Black
Same

46. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
Scalar
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
Any
NATURAL JOIN

47. The DUAL table can be used for...
Location of the file
ROLLBACK
Black
Testing functions

48. The TRUNC function on a date without a format model truncates the timestamp to...
Columns
INNER JOIN
12:00:00 A.M
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

49. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
Inconsistencies
ALL_OBJECTS
USER_ - ALL_ - DBA_ - and V$
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.

50. Which format mask displays December 31st - 1999?
Month ddth - yyyy
F12
TO_CHAR function
Nested