About database 1. Examine the structures of the PLAYER and TEAM tables: For this
ID: 3726835 • Letter: A
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