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 _____________ clause determines how rows are grouped.
GROUP BY
Non-matching rows
BETWEEN
Parent table

2. Write a solution that adds 20 minutes to a given date.
SELECT SYSDATE+1/96 - FROM dual
Command returns the number of records returned by a query.
Time zone
Precision

3. The ____________ icon allows you to execute multiple statements and emulates SQL *Plus as much as possible.
DICTIONARY
RUN SCRIPT
VALUES
Non-matching rows

4. When column names on the tables are different - you use the ______ condition.
Precision
ON
Black
Running a script file

5. 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.
Equijoin and identical column names
F12
RIGHT LEFT JOIN
When the table is small (unless your business rules require you too)

6. List 6 Table-Related Menu Choices
VALUES clause of an INSERT statement
Crow's foot
Hide data
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT

7. _________ rows are not allowed in the relational model.
Primary key/foreign key
Combine
Export Data
Orphan

8. SQL Developer's ____________________ option allows you to selectively export certain rows and columns.
IN ANY ALL SOME
Export Data
SYSDATE
Details

9. A join across three tables can be expressed with the ________ join syntax.
Correlated
DESC dict;
RIGHT LEFT JOIN
ANSI

10. NUMBER functions can be...
Columns
Nested
Scalar
All

11. Converting a text literal to DATE format requires the use of the...
Structured Query Language
ANY
TO_CHAR function
**** 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

12. You can use the ________________ to subtract months from a given date.
Export Data
All
ADD_MONTHS function
12:00:00 A.M

13. The ______ operator compares a list of values for equivalency
ANY
Re-create
Equijoin and correlated subquery
Columns

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

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.
FALSE
Common column value
Non-matching rows
Running a script file

16. What is the maximum value for a column defined as NUMBER(4 -2)?
99.99
X
Self-joins
Results

17. Explain the steps needed to run a script in SQL Developer
Non-matching rows
All caps
Dynamic SQL is used to be continuously updated by the background processes in the Oracle instance but never by the user.
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.

18. What does SQL stand for?
Scale
Structured Query Language
The way NULL values are treated
Script

19. What does the following function return? SELECT LENGTH(NULL) - FROM dual
Null
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
DICTIONARY
Testing functions

20. To restart a sequence at a lower number - you can drop and ________ the sequence.
Has not changed the name
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
Re-create
INTERSECT

21. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
ANSI
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
Leading-edge
B-Tree Index

22. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
USER_ - ALL_ - DBA_ - and V$
NATURAL JOIN
4
SELECT SYSDATE+1/96 - FROM dual

23. The ____________ keywords and the USING clause of the ANSI join syntax are mutually exclusive.
RIGHT LEFT JOIN
CLOB
X
NATURAL JOIN

24. Explain what is found in the All Objects report folder in SQL Developer
Surrogate
Columns
User-defined reports
Lists all objects accessible for the chosen database connection. Also lists a helpful dependencies report for accessing change impact.

25. In a view you can give a column a different name than it has in the...
DECODE
Primary key/foreign key
**** 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
Parent table

26. The COUNT function is useful for determining whether a table has...
USER_ - ALL_ - DBA_ - and V$
Equijoin and identical column names
Data
MODIFY

27. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
X
RUN SCRIPT
B-Tree Index

28. SELECT LIST of a subquery in an INSERT statement
Three
SET clause of an UPDATE statement
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.
TO_CHAR function

29. What is the keyboard shortcut for the ROLLBACK command?
F12
Implicit conversion
DICTIONARY
When the table is small (unless your business rules require you too)

30. A transaction is a...
Semicolon or forward slash
Equijoin and correlated subquery
2000
Logical unit of work

31. You use the _________ keyword to add columns or constraints to a table.
Correlated
Columns
Another null
ADD

32. When writing the syntax for finding information on three or more tables - the parenthesis around the ON clause is...
Optional
Mathematical equations
Static or dynamic
Nested

33. LINESIZE
FALSE
FALSE
X
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

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

35. To equijoin a table with another table involves matching the...
Common column value
999999.9999
Both MIN and MAX
Twice

36. What is the tab in SQL Developer where data dictionary view information can be found?
Has not changed the name
Primary key/foreign key
All
Details

37. The __________ operator is inclusive of the two values specified.
ADD_MONTHS function
Fractional seconds
BETWEEN
GROUP BY

38. A _________ subquery returns a single column with a single row
Time zone
Scalar
B-Tree Index
CLOB

39. The ________ operator cannot compare against a list of values.
LIKE
12:00:00 A.M
IN ANY ALL SOME
Time zone

40. When disabling a unique or primary key - you can keep the index if you specify the __________________ clause in the ALTER TABLE statement.
ANSI
Equijoin
KEEP INDEX
Surrogate

41. The term ____________ ___________ is used to express a join that satisfies the join condition.
Has not changed the name
Equijoin and identical column names
Actual column
INNER JOIN

42. When using the following data: NUMBER(10 -4) - the lowest number you can possibly have is __________.
Command returns the number of records returned by a query.
999999.9999
Default is 14; when you use the default - you repeat the heading every 14 rows.
Orphan

43. 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
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.
The way NULL values are treated
Business problems

44. The RTRIM function is useful for eliminating ________ in a string.
Re-create
ADD
BETWEEN
Extra spaces

45. Oracle uses the outer join operator to indicate that nulls are shown for...
Script
Non-matching rows
Data
UTC

46. How many characters can be stored in the CHAR column/field?
X
ADD
2000
Semicolon or forward slash

47. 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.
VALUES
Three
Default is 14; when you use the default - you repeat the heading every 14 rows.
DESC dict;

48. Joins involving multiple columns must always follow the ____________ relationship path.
Primary key/foreign key
Precision
Groups
User-defined reports

49. Returns rows if the condition is true for all the values in the list
MINUS
UNION
SELECT SYSDATE+1/96 - FROM dual
All

50. What is the main difference between the NOT EXISTS and the NOT IN operators?
TO_CHAR function
The way NULL values are treated
Extra spaces
Actual column