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 name of the key automatically generated from a sequence?
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.
Surrogate key
Surrogate
UTC

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

3. SQL*Plus is used more than SQL Developer when you need to...
Twice
Scalar
Run scripts
NATURAL JOIN

4. Subqueries can return...
UNION operator
VALUES
DD-MON-YY
Multiple rows

5. To place a line comment in your SQL statements - you first place a // at the beginning of the line.
FALSE
Month ddth - yyyy
Command returns the number of records returned by a query.
Parent table

6. What is the main difference between the NOT EXISTS and the NOT IN operators?
NATURAL JOIN
VALUES
Leading-edge
The way NULL values are treated

7. The ________________ is a type of statement whereby you retrieve data that exists in one table - but not in another.
Surrogate key
MINUS
BETWEEN
ADD

8. What is the command you would type into SQL*Plus if you needed to know the definition for each column of a table?
DICT_COLUMNS
Crow's foot
Extra spaces
DROP INDEX need_to_pass_i

9. To restart a sequence at a lower number - you can drop and ________ the sequence.
SUM
Re-create
Results
ADD

10. Write an SQL statement for SQL *Plus in which you want any unique menu items to be displayed from the restaurant table
12:00:00 A.M
Implicit conversion
UNION
SELECT DISTINCT menu FROM restaurant

11. In a view you can give a column a different name than it has in the...
USER_ - ALL_ - DBA_ - and V$
BETWEEN
Parent table
Equijoin and identical column names

12. Checks whether any value in the list makes the condition true.
Any
Hide data
Surrogate key
SELECT SYSDATE+1/96 - FROM dual

13. Joins involving multiple columns must always follow the ____________ relationship path.
Recursive
DD-MON-YY
Another null
Primary key/foreign key

14. SELECT * FROM instructor ORDER BY phone
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.
User-defined reports
A correct statement

15. Explain how Oracle treats locks when a database is being updated and needs to be viewed by other users.
@ or START
Self-joins
All
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

16. _________________ relationships are always optional relationships.
ROLLBACK
Recursive
BETWEEN
Command returns the number of records returned by a query.

17. The same function can be used ______ in a SELECT statement.
Equijoin
Static or dynamic
SUM
Twice

18. Write a statement for SQL*Plus that shows the dictionary.
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.
Month ddth - yyyy
DESC dict;
SELECT SYSDATE+1/96 - FROM dual

19. Which operator is most commonly used?
Control - Revoke - Define - TRUNCATE Manipulate - MERGE
UNION
DICT_COLUMNS
Multiple rows

20. The ______ operator compares a list of values for equivalency
ANY
KEEP INDEX
SYSDATE
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

21. NUMBER functions can be...
Self-joins
F12
Nested
Correlated

22. 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.
Equijoin
VALUES
Data
Common column value

23. Which format mask displays December 31st - 1999?
LENGTH
Groups
2000
Month ddth - yyyy

24. The COUNT function is useful for determining whether a table has...
SUM
SYSDATE
CLOB
Data

25. When you run a script in SQL*Plus you must give the...
Same
DD-MON-YY
Location of the file
Script

26. What is the first column of a composite index called?
Leading-edge
Scale
Non-matching rows
VALUES

27. The DUAL table can be used for...
SELECT SYSDATE+1/96 - FROM dual
Testing functions
Command returns the number of records returned by a query.
DD-MON-YY

28. The ___________ operator tests whether a matching row cannot be found.
RENAME - COPY - DROP - TRUNCATE - LOCK - & COMMENT
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.
X
RUN SCRIPT

29. The focus of the logical data model is _____________
Structured Query Language
NATURAL JOIN
Business problems
ON

30. List the steps performed by a correlated subquery.
FALSE
User-defined reports
X
**** 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

31. _________ rows are not allowed in the relational model.
Black
Orphan
RUN SCRIPT
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

32. When column names on the tables are different - you use the ______ condition.
ON
TO_CHAR function
RENAME just_two_more_weeks TO finally_i
Control - Revoke - Define - TRUNCATE Manipulate - MERGE

33. What is the tab in SQL Developer where data dictionary view information can be found?
2000
Data
Details
UNION operator

34. When returning multiple rows - a subquery must contain the _____ - _____ - _____ - or ______ operator - otherwise Oracle will return an error
IN ANY ALL SOME
Multiple rows
ALL_OBJECTS
Primary key/foreign key

35. 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.
Hide data
Another null
NATURAL JOIN
Has not changed the name

36. When using the data NUMBER(10 -4) there will be _______ digits following the decimal point.
4
ADD_MONTHS function
KEEP INDEX
SUM

37. List 4 aggregate functions
COUNT(registration_date) - AVG (capacity) - MIN (capacity) - MAX (capacity)
Self-joins
Script
Equijoin

38. A join across three tables can be expressed with the ________ join syntax.
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
BETWEEN
Crow's foot

39. To revert back to a state that happened before you ran your change statement - you issue a ____________ command.
Default is 14; when you use the default - you repeat the heading every 14 rows.
ROLLBACK
Export Data
ADD

40. A transaction is a...
Logical unit of work
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
GROUP BY
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.

41. Explicit data type conversion is preferable to Oracle's...
Has not changed the name
LENGTH
Implicit conversion
Mathematical equations

42. What does SQL stand for?
Multiple rows
Location of the file
Structured Query Language
Optional

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

44. What is the Oracle data dictionary called that contains a set of tables and views containing the data about the database?
ALL_OBJECTS
Combine
Columns
Black

45. The COUNT function uses the __________ wildcard.
99.99
COMMIT
Asterisk
Command determines the total number of characters SQL *Plus displays in on line before beginning a new line.

46. When is it not a good idea to use an index?
Any
Mathematical equations
When the table is small (unless your business rules require you too)
FALSE

47. What is the data type you can use if you need an unlimited number of textual characters?
Has not changed the name
CLOB
Asterisk
Re-create

48. Self-joins are useful for performing comparisons and checking for ___________ in data.
Inconsistencies
UNION
Surrogate key
Testing functions

49. Converting a text literal to DATE format requires the use of the...
Equijoin and correlated subquery
TO_CHAR function
RIGHT LEFT JOIN
Re-create

50. The time zone displacement value indicates the time difference from _______.
DICT_COLUMNS
Implicit conversion
UTC
Twice