Oracle Basic JOIN Quiz Exercises

1. The _____ statement can be used to combine rows from two queries, returning only the rows that appear in both sets. (Points: 3)
UNION
UNION ALL
INTERSECT

2. The most common type of subquery uses an inner SELECT subquery on the right side of a(n) __ comparison expression that is part of a preceding SELECT statement. (Points: 3)
WHERE
JOIN
UNION
EXISTS

3. Assume you are using the INTERSECT operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. None of the customers appears in both the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the INTERSECT operator? (Points: 3)
10
7
17
0

4. A subquery that executes repeatedly, once for each value of a candidate row selected by the main query is a(n) __ subquery. (Points: 3)
nested
correlated
inline
grouped

5. When using a(n) __ join, only rows that are in common are returned. (Points: 3)
full
inner
outer
set

6. f you wish to perform an inner join based on a single column two tables have in common, you might use the _____ clause. (Points: 3)
OF
USING
HAS
JOIN ON

7. What constitutes an updatable view and how it might be used?

Oracle DBA Interview Questions, Exercises and Quizes

1. Multidimensional data analysis refers to the processing of data such that data are viewed as part of the __ structure. (Points: 4)
semidimensional
multidimensional
one-dimensional
two-dimensional

2. During the data __ phase of data mining, common characteristics or patterns are identified. (Points: 4)
analysis
preparation
extraction
acquisition

3. The components of a data warehouse are __. (Points: 4)
integrated
volatile
object-oriented
decentralized

4. In contrast to the traditional DSS tools, data mining is __. (Points: 4)
proactive
protective
automatic
costly

5. A view that can pass data changes from itself back to the master table is called ____. (Points: 4)
updatable view
read-only view
materialized view
database link

6. Which security level is not offered by Oracle? (Points: 4)
account security
object security
access security
system security

7. During _____ audits, select, insert, update, and delete operations are logged. (Points: 4)
login
object
action
system

8. A full table scan occurs when a SELECT statement does not contain a ________ clause. (Points: 4)
HAVING
IN
ON
WHERE

9. Which of the following tuning tools have been introduced in Oracle 10g? (Points: 4)
STATSPACK
TKPROF
AWR
OEM

10. An index that is made of two columns will be used ____. (Points: 4)
when its first column is included in the WHERE clause of the query
when its second column is included in the WHERE clause of the query
in every query
in every query that involves full table scan

11. A __ join returns rows with matching values as well as unmatched rows from both tables. (Points: 4)
natural
right outer
full outer
left outer

12. In subquery terminology, the first query in the SQL statement is known as the __ query. (Points: 4)
outer
left
inner
base

13. The ____ statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second. (Points: 4)
UNION
UNION ALL
INTERSECT
MINUS

14. Assume you are using the UNION operator to combine the results from two tables with identical structure: CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. All other customers are different in both tables. How many records are returned when using the UNION operator? (Points: 4)
7
10
15
17

15. _____ databases are often stripped-down versions of their server-based counterparts. (Points: 4)
Mobile
Multimedia
Internet
Genome

16. : __ transparency ensures that the system will continue to operate in the event of a node failure. (Points: 4)
Transaction
Distribution
Failure
Performance

17. Which of the following is a disadvantage of DDBMS? (Points: 4)
single point of failure
growth facilitation
improved security
lack of standards

18. What feature is a DDBMS advantage? (Points: 4)
Greater difficulty in managing the data environment
Less danger of a single-point failure
Increased training cost
Complexity of management and control

19. __ transparency allows a transaction to update data at several network sites. (Points: 4)
Transaction
Distribution
Failure
Performance

20. Which of the following does NOT describe an abstract data type? (Points: 4)
The operations are user-defined.
It does not allow direct access to its internal data representation.
It encapsulates its definition.
It allows direct access to its internal data representation.

21. Which of the following must be supported by every DBMS? (Points: 4)
distributed databases
object-oriented types
data queries
data analysis tools

22. Abstract data types are implemented with ___ command. (Points: 4)
CREATE TYPE
CREATE TABLE
CREATE TABLESPACE
CREATE CONSTRAINT

23. ______ treats multiple disks as a single volume. (Points: 4)
RAM
ROM
RAID
DVR

24. Data in global temporary tables _______. (Points: 4)
can be shared between different users
can be shared between different sessions of the same user
can be shared within one session of the same user
is good only for the duration of one operation

25. ______ improve performance of accessing data in large databases. (Points: 4)
Indexes
Rollback segments
Temporary tables
Redo log files

26. __ data types are implemented through classes. (Points: 4)
Dynamic
Abstract
Simple
Complex

27. The ability to hide the object's internal details is known as __. (Points: 4)
encapsulation
interrogation
instantiation
inheritance

28. __ means that the user may send the same message to different objects that belong to different classes and yet always generate the correct response. (Points: 4)
Abstract data type
Encapsulation
Polymorphism
Inheritance

29. The use of __ concepts will enable future DBMSs to handle more complex problems with both normalized and nonnormalized data. (Points: 4)
application
OO
procedural
database

30. Which of the following is a new tool introduced in Oracle 10g to automatically generate reports from AWR? (Points: 4)
DBMS
OEM
ADDM
STATSPACK

31. Two techniques are often used in the design of distributed databases: data fragmentation and data replication. Explain the similarities and differences between these two data storage techniques. Evaluate risks and benefits associated with each regarding performance of data access.

32. summarize database solutions to contemporary problems.
You have been assigned the responsibility of formulating a data warehouse and business intelligence strategy. Describe your plan and evaluate its potential for success

33. Discuss ways in which database design can extend relational tables and normalization. When is it justified to use these techniques.

Oracle JOIN Exercise Quiz

1. (TCO 1) The _______ statement can be used to combine rows from two queries, returning only the rows that appear in both sets.  (Points: 3)
        UNION 
        UNION ALL 
        INTERSECT 
        MINUS 


2. (TCO 1) Assume you are using the UNION ALL operator to combine the results from two identical tables, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the UNION ALL operator? (Points: 3)
        7 
        10 
        15 
        17 


3. (TCO 1) In a subquery, which query is executed first? (Points: 3)
        Leftmost 
        Rightmost 
        Innermost 
        Base 


4. (TCO 1) Which operator(s) are used with subqueries that use inequality operators on a list of values? (Points: 3)
        ANY 
        ANY and ALL 
        ON 
        ONLY 


5. (TCO 1) A(n) ____ join will select only the rows with common values in the common attribute(s) (Points: 3)
        natural 
        cross 
        full 
        outer 


6. (TCO 1) What is the syntax for a left outer join? (Points: 3)
        SELECT column-list
  FROM table1 OUTER JOIN table2 LEFT
  WHERE join-condition 
        SELECT column-list
  FROM table1 LEFT [OUTER] JOIN table2 LEFT
  ON join-condition 
        SELECT column-list
   WHERE LEFT table1=table2 
        SELECT column-list
  FROM table1 LEFT table2 [JOIN]
  WHERE join-condition 


7. (TCO 1) Explain the different types of join operators. What are they and how do they work?(Points: 12)