Oracle__9. Oracle 1Z0-051 Exam - Joins

Question 1

List the 2 main types of Joins?

Answer 1

1. INNER2. OUTER

Question 2

What is another name for simple join?

Answer 2

INNER

Question 3

List the 3 types of OUTER joins?

Answer 3

1. LEFT OUTER JOIN2. RIGHT OUTER JOIN3. FULL OUTER JOIN

Question 4

What type of join returns all rows from multiple tables where the join condition is met?

Answer 4

INNER

Question 5

Will this statement execute without errors? SELECT a.name, b.name, a.id FROM table1 a, table2 b WHERE a.id = b.id

Answer 5

Yes.This is an implicit Inner Join with aliases.

Question 6

Rewrite the statement below using an INNER JOIN? SELECT a.name, b.name, a.id FROM table1 a, table2 b WHERE a.id = b.id

Answer 6

SELECT a.name, b.name, a.idFROM table1 aINNER JOIN table2 bON a.id = b.id

Question 7

Rewrite the statement below using an INNER JOIN but add another join column named lastname in which is in both tables. Also add lastname to the select clause. SELECT a.name, b.name, a.id FROM table1 a, table2 b WHERE a.id = b.id

Answer 7

SELECT a.name, b.name, a.id, a.lastnameFROM table1 aINNER JOIN table2 bON a.id = b.idAND a.lastname = b.lastname

Question 8

Rewrite the statement below using an INNER JOIN adding another table3 which has both an id and a lastname column. SELECT a.name, b.name, a.id, a.lastname FROM table1 a INNER JOIN table2 b ON a.id = b.id AND a.lastname = b.lastname

Answer 8

SELECT a.name, b.name, a.id, a.lastnameFROM table1 aINNER JOIN table2 bON a.id = b.idAND a.lastname = b.lastnameINNER JOIN table3 cON a.id = c.idAND a.lastname = c.lastnameNote: in this case the order of tables does not matter and table3 could be joined to table2 instead

Question 9

Will this statement execute with errors? SELECt a.name, b.name, a.id FROM table1 a JOIN table2 b ON a.id = b.id

Answer 9

No[INNER] JOINThe keyword INNER is not necessary in Oracle.Also case letters do not matter in the keyword SELECt

Question 10

What type of join is needed if you want all rows returned from the left table an only those rows that match the Join from the right table?

Answer 10

LEFT [OUTER] JOINThe keyword OUTER is not necessary in Oracle.

Question 11

What type of join is needed if you want all rows returned from the right table an only those rows that match the Join from the left table?

Answer 11

RIGHT [OUTER] JOINThe keyword OUTER is not necessary in Oracle

Question 12

What type of join will return all rows from both tables with nulls where the join condition is not met?

Answer 12

FULL [OUTER] JOINThe keyword OUTER is not necessary in Oracle

Question 13

What 3 clauses of a SQL statement can have a subquery?

Answer 13

1. SELECT2. FROM3. WHERE

Question 14

If JOINING 2 tables would you use the ON clause or USING clause if you are joining fields with different names, but the same data types?

Answer 14

ON clause joins columns with different names, but the same data types.

Question 15

If columns are joined with the ON clause and have the same name between the tables how are the columns differentiate in other clauses of the SQL statement?

Answer 15

Either a table prefix or a alias prefix will differentiate the columns that are the same names between different tables.Example using table prefix to differentiate:SELECT table1.id, table2.id, table1.code, table2.codeFROM table1JOIN table2ON table1.id = table2.idAND table1.code = table2.codeExample using aliases prefix to differentiate:SELECT a.id, b.id, a.code, b.codeFROM table1 aJOIN table2 bON a.id = b.idAND a.code = b.code

Question 16

If columns are joined with the USING clause and have the same name between the tables how are the columns differentiate in other clauses of the SQL statement?

Answer 16

The columns defined in the USING at not prefixed.Example:SELECT id, codeFROM table1JOIN table2USING (id, code)

Question 17

Inner Joins can use the JOIN..ON clause or the WHERE clause. What is the syntax of this statement using the WHERE clause to join. SELECT table1.id, table2.id, table1.code, table2.code FROM table1 JOIN table2 ON table1.id = table2.id AND table1.code = table2.code

Answer 17

SELECT table1.id, table2.id, table1.code, table2.codeFROM table1, table2WHERE table1.id = table2.idAND table1.code = table2.code

Question 18

What is the syntax if the following code was changed to a NATURAL JOIN? SELECT table1.id, table2.id, table1.code, table2.code FROM table1 JOIN table2 ON table1.id = table2.id AND table1.code = table2.code

Answer 18

SELECT id, codeFROM table1NATURAL JOIN table2

Question 19

What are results from this SQL Statement? SELECT promo_name, cust_name FROM promotions p JOIN sales s ON (time_id BETWEEN begin_date AND end_date) JOIN customer c ON (s.cust_id = c.cust_id) AND time_id < ‘30-oct-2007’

Answer 19

It will execute without errorsstrange how the time_id is reference between tables and also allowing the BETWEEN clause?

Question 20

If a SQL statement has many join what order are they executed?

Answer 20

Left to right, unless parenthesis are involved

Question 21

List 3 main categories of Joins?

Answer 21

1. Natural or Inner2. Outer3. Cross

Question 22

List 3 main clauses for the Natural Joins.

Answer 22

1. NATURAL JOIN2. USING3. JOIN ON

Question 23

If 2 tables are joined using a NATURAL JOIN what must exist between the 2 tables?

Answer 23

At least one column name is the same in each table which also need to be the same data type.

Question 24

If 2 tables are to be joined that have several columns with matching names, what can be used to limit the number of column being join through a natural join?

Answer 24

SELECT*FROM tablename1JOIN tablename2USING (fieldname1, fieldname2)

Question 25

Equijoin are also called?

Answer 25

INNERalso Simple Joins

Question 26

What can you not used on the column name that is joins using the USING clause?

Answer 26

an AliasSince the same field is in both tables, the column name does not (cannot) have an Alias.

Question 27

If a table name has an alias can you still use the table name in other clauses of the SQL statement?

Answer 27

No

Question 28

What is the syntax of a inner join when the column names between 2 tables that are to be join are named differently?

Answer 28

SELECT *FROM tablename1JOIN tablename2ON (ColumnameA = ColumnNameB)NOTE: In this example Aliases are not needed since the column names are differentAlso Note: The Parenthesis are optional

Question 29

How can you modify the following statement to add a WHERE clause without modifying the results? SELECT * FROM tablename1 a JOIN tablename2 b ON (a.columnName1 = b.ColumnName1) AND a.number = 1

Answer 29

SELECT *FROM tablename1 aJOIN tablename2 bON (a.columnName1 = b.ColumnName1)WHERE a.number = 1

Question 30

A CROSS JOIN is also called a?

Answer 30

Cartesian Join

Question 31

What happens in a CROSS JOIN?

Answer 31

Each record from one table is matched to a record from the 2nd table.So if Table1 has 10 records and Table2 has 10 records the results are 100 records.

Question 32

What happen in an Inner Join?

Answer 32

The return consists of record that match between the two tables based on the joined fields.

Question 33

What is an LEFT OUTER JOIN?

Answer 33

The return consists of All records from the left table and only matching records from the right table?SELECT *FROM table1 aLEFT OUTER JOIN Table2 bON a.ID = b.IDAll records from table1 will appear

Question 34

What is an RIGHT OUTER JOIN?

Answer 34

The return consists of All records from the right table and only matching records from the left table?SELECT *FROM table1 aRIGHT OUTER JOIN Table2 bON a.ID = b.IDAll records from table2 will appear

Question 35

What is a FULL OUTER JOIN?

Answer 35

The return consists of all matching record and then all unmatched records from the Left table and then all unmatched records from the Right table.SELECT *FROM table1 aRIGHT OUTER JOIN Table2 bON a.ID = b.IDAll records from each table will appear but the matching records will appear only once.

Question 36

In the following select statement how many records will be returned? SELECT * FROM table1 a INNER JOIN Table2 b on a.ID = b.ID Table1 has 10 records Table2 has 12 records 2 records IDs match between the tables

Answer 36

2 records will be returned

Question 37

In the following select statement how many records will be returned? SELECT * FROM table1 a LEFT OUTER JOIN Table2 b ON a.ID = b.ID Table1 has 10 records Table2 has 12 records 2 records IDs match between the tables

Answer 37

10 records will be returned

Question 38

In the following select statement how many records will be returned? SELECT * FROM table1 a RIGHT OUTER JOIN Table2 b ON a.ID = b.ID Table1 has 10 records Table2 has 12 records 2 records IDs match between the tables

Answer 38

12 records will be returned

Question 39

In the following select statement how many records will be returned? SELECT * FROM table1 a FULL OUTER JOIN Table2 b ON a.ID = b.ID Table1 has 10 records Table2 has 12 records 2 records IDs match between the tables

Answer 39

20 records will be returned2 matched records8 unmatched records from table110 unmatched records from table2

Question 40

Can a NATURAL JOIN also be an OUTER JOIN?

Answer 40

Yes.Syntax: NATURAL LEFT OUTER JOIN NATURAL RIGHT OUTER JOIN NATURAL FULL OUTER JOIN

Question 41

A self join query has to have what?

Answer 41

a subquery of the same table

Question 42

Is this an example of a Left Outer Join or a Right Outer Join? SELECT a.cust_first_name, a.cust_last_name, b.order_date, b.order_mode, b.order_status, b.order_total FROM customers a, orders b WHERE a.customer_id=b.customer_id (+) ORDER BY a.customer_id;

Answer 42

Left outer join.The (+) in the WHERE clause indicates an outer join on the ORDERS table, giving the list of customers without orders.

Question 43

What is a materialized view?

Answer 43

The following are characteristics of materialized views: 1. They are refreshed when the data gets changed. 2. They are used to replicate data from another database. 3. They are used in data-warehouse environments where data needs to be aggregated and stored so that queries and reports run faster.

Question 44

What would be a reason to use a Cartesian Join?

Answer 44

A Cartesian product can be helpful for testing purposes when there is a need to generate a large number of rows to simulate a reasonable amount of data.