About database

1.

Examine the structures of the PLAYER and TEAM tables:

PLAYER ------------- PLAYER_ID NUMBER(9) PK LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) TEAM_ID NUMBER MANAGER_ID NUMBER(9) 

TEAM ---------- TEAM_ID NUMBER PK TEAM_NAME VARCHAR2(30) 

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, 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);

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

PRODUCT ----------------------------------- PRODUCT_ID NUMBER PRODUCT_NAME VARCHAR2(25) SUPPLIER_ID NUMBER CATEGORY_ID NUMBER QTY_PER_UNIT NUMBER UNIT_PRICE NUMBER(7,2) QTY_IN_STOCK NUMBER QTY_ON_ORDER NUMBER REORDER_LEVEL NUMBER 

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 product_name, supplier_name FROM product NATURAL JOIN supplier ORDER BY 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 product_name, supplier_name FROM product JOIN supplier USING (supplier_id) ORDER BY product_name;