About database 1. Examine the structures of the PLAYER and TEAM tables: For this

Question

About database

1.

Examine the structures of the PLAYER and TEAM tables:

For this example, team managers are also players, and the MANAGER_ID column references the PLAYER_ID column. For players who are managers, MANAGER_ID is NULL.

Which SELECT statement will provide a list of all players, including the player's name, the team name, and the player's manager's name?

SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p

LEFT OUTER JOIN player m ON (p.manager_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p JOIN player m ON (p.manager_id = m.player_id) RIGHT OUTER JOIN team t ON (p.team_id = t.team_id);

SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p  LEFT OUTER JOIN player m ON (p.player_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

2.Examine the structures of the PRODUCT and SUPPLIER tables:

SUPPLIER ------------------------------------ SUPPLIER_ID NUMBER
SUPPLIER_NAME VARCHAR2(25)
ADDRESS VARCHAR2(30)
CITY VARCHAR2(25)
REGION VARCHAR2(10)
POSTAL_CODE VARCHAR2(11)

You want to create a query that will return an alphabetical list of products including the name of each product's supplier. Only products in the PRODUCT table that have a supplier assigned should be included in your report.

Which two queries could you use? (Choose two. Each correct answer is a separate solution.)

SELECT p.product_name, s.supplier_name FROM product p
LEFT OUTER JOIN supplier s
ON p.supplier_id = s.supplier_id

ORDER BY p.product_name;

SELECT p.product_name, s.supplier_name FROM product p
JOIN supplier s
ON (supplier_id)

ORDER BY p.product_name;

SELECT p.product_name, s.supplier_name FROM product p
JOIN supplier s
USING (p.supplier_id)

ORDER BY p.product_name;

  SELECT p.last_name, p.first_name, p.manager_id, t.team_name FROM player p NATURAL JOIN team t;  

  SELECT p.last_name, p.first_name, p.manager_id, t.team_name FROM player p JOIN team t USING (team_id);  

SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p

LEFT OUTER JOIN player m ON (p.manager_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p JOIN player m ON (p.manager_id = m.player_id) RIGHT OUTER JOIN team t ON (p.team_id = t.team_id);

SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p  LEFT OUTER JOIN player m ON (p.player_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

Explanation / Answer

1.

SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p

LEFT OUTER JOIN player m ON (p.manager_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

here self join of table player is done and left outer join is used because for managers , the manager_id is NULL

2.

a.

SELECT product_name, supplier_name FROM product NATURAL JOIN supplier
ORDER BY product_name;
b.

the supplier id should be matching in both tables so leftjoin cannot be used and table alias is also invalid

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.