Zend Preparation | PHP Quiz (Learning Mode)

Database and SQL Learning Mode

Qusetions Index 31/75

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

You have created a table based on the following data:

EmpID NUMBER (5) PRIMARY KEY
EmpName VARCHAR2 (35) NOT NULL
Salary NUMBER (9, 2) NOT NULL
Commission NUMBER (4, 2)
ManagerName VARCHAR2 (25) 
ManagerID NUMBER (5)

Now, you want to display the names of employees who are managers, using a self join. Which of the following SQL statements can you use to accomplish this?

SELECT e.EmpName FROM Employees e SELF JOIN Employees m ON e.EmpID = m.ManagerID;

SELECT e.EmpName FROM Employees e INNER JOIN Employees m ON e.EmpID = m.ManagerID;

SELECT e.EmpName FROM Employees e, Employees m WHERE e.EmpID = m.ManagerID;

SELECT e.EmpName FROM Employees e LEFT OUTER JOIN Employees m ON e.EmpID = m.ManagerID;

Explanation:
Answer options B and C are correct.

Self-join is used to join a table to it. Each row of the table is joined to itself and to every other row of the table. The table name appears twice in the FROM clause, with two different aliases. The two aliases are treated as two different tables, and are joined to each other.

When performing self-join in ANSI syntax, the

JOIN .... ON

syntax should be used. The INNER keyword is optional.

Answer option D is incorrect. LEFT OUTER JOIN specifies a join between two tables. It returns all the rows from the left table in the join expression, and only those rows that meet the join condition from the right table. The syntax of LEFT OUTER JOIN is as follows:

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Consider the following example, EMPLOYEE table and ORDER table:

P_ID	LastName	FirstName	Address
1	Harry	David	Storgata
2	Steve	Joseph	California
3	Peter	Kari	Toronto

O_ID	Order	P_ID
1	1023	1
2	2034	2

To join the EMPLOYEE table with the ORDER table containing EMPLOYEE if any, from the tables above, run the following command:

SELECT EMPLOYEE.LastName, ORDER.Order
FROM EMPLOYEE
LEFT JOIN ORDER
ON EMPLOYEE.P_Id=ORDER.O_Id
ORDER BY EMPLOYEE.LastName

The output of the command will appear as:

LastName	Order
Harry	1023
Steve	2034
Peter

Note: In some database, RIGHT JOIN is also called as RIGHT OUTER JOIN.

Answer option A is incorrect. There is no clause such as SELF JOIN in SQL.

Reference: http://www.liamdelahunty.com/tips/mysql_self_join.php