Oracle__9. Oracle 1Z0-051 Exam - Joins Flashcards Preview

Oracle > Oracle__9. Oracle 1Z0-051 Exam - Joins > Flashcards

Flashcards in Oracle__9. Oracle 1Z0-051 Exam - Joins Deck (44)
Loading flashcards...
1
Q

List the 2 main types of Joins?

A
  1. INNER2. OUTER
2
Q

What is another name for simple join?

A

INNER

3
Q

List the 3 types of OUTER joins?

A
  1. LEFT OUTER JOIN2. RIGHT OUTER JOIN3. FULL OUTER JOIN
4
Q

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

A

INNER

5
Q

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

A

Yes.This is an implicit Inner Join with aliases.

6
Q

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

A

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

7
Q

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

A

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

8
Q

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

A

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

9
Q

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

A

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

10
Q

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?

A

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

11
Q

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?

A

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

12
Q

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

A

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

13
Q

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

A
  1. SELECT2. FROM3. WHERE
14
Q

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?

A

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

15
Q

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?

A

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

16
Q

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?

A

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

17
Q

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

A

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

18
Q

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

A

SELECT id, codeFROM table1NATURAL JOIN table2

19
Q

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’

A

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

20
Q

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

A

Left to right, unless parenthesis are involved

21
Q

List 3 main categories of Joins?

A
  1. Natural or Inner2. Outer3. Cross
22
Q

List 3 main clauses for the Natural Joins.

A
  1. NATURAL JOIN2. USING3. JOIN ON
23
Q

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

A

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

24
Q

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?

A

SELECT*FROM tablename1JOIN tablename2USING (fieldname1, fieldname2)

25
Q

Equijoin are also called?

A

INNERalso Simple Joins

26
Q

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

A

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

27
Q

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

A

No

28
Q

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

A

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

29
Q

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

A

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

30
Q

A CROSS JOIN is also called a?

A

Cartesian Join

31
Q

What happens in a CROSS JOIN?

A

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.

32
Q

What happen in an Inner Join?

A

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

33
Q

What is an LEFT OUTER JOIN?

A

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

34
Q

What is an RIGHT OUTER JOIN?

A

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

35
Q

What is a FULL OUTER JOIN?

A

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.

36
Q

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

A

2 records will be returned

37
Q

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

A

10 records will be returned

38
Q

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

A

12 records will be returned

39
Q

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

A

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

40
Q

Can a NATURAL JOIN also be an OUTER JOIN?

A

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

41
Q

A self join query has to have what?

A

a subquery of the same table

42
Q

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;

A

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

43
Q

What is a materialized view?

A

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.

44
Q

What would be a reason to use a Cartesian Join?

A

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.