New SQL Commands in Oracle

1. New SQL Commands in Oracle

2. INNER JOINs NATURAL JOIN • Perform JOIN based on like columns in two tables. The Like columns must be of the same name and data type. • ON clause is not required • Natural Join SELECT * FROM Cust NATURAL JOIN Emp;

3. USING • Perform JOIN based on specified columns in two tables. • The specified columns must have the same name and data type. • With “Using” SELECT * FROM Cust JOIN Emp USING (city, age); // This join is also called COLUMN name join in that it uses // only column names specified in USING clause • Without “Using” SELECT * FROM Cust, Emp WHERE Cust.city = Emp.City AND Cust.age= Emp.Age;

4. ON • Perform JOIN based on specified columns in two tables. • Can be used for columns having different names • With “ON” SELECT * FROM Cust JOIN Emp ON Cust.cust_city=Emp.emp_city; // This join is called CONDITION JOIN SELECT * FROM Cust JOIN Emp ON (Cust.city = Emp.city AND Cust.age > Emp.age);

5. Without “ON” SELECT * FROM Cust, Emp WHERE Cust.cust_city=Emp.emp_city; SELECT * FROM Cust, Emp WHERE (Cust.city = Emp.city AND Cust.age > Emp.age);

6. CROSS JOIN • Produces cross product of two tables, resulting in a Cartesian join • // With CROSS JOIN (On and Using clauses are not allowed) SELECT * FROM Cust CROSS JOIN Emp; // Without CROSS JOIN SELECT * FROM Cust, Emp;

7. OUTER JOIN All the above joins are called INNER JOINs. (a) LEFT OUTER JOIN (all tuples in the left table are kept) SELECT * FROM Cust LEFT OUTER JOIN Emp Using (city); SELECT * FROM Cust LEFT OUTER JOIN Emp ON (Cust.city = Emp.City); (b) RIGHT OUTER JOIN (all tuples in right table are kept) SELECT * FROM Cust RIGHT OUTER JOIN Emp Using (city); (c) FULL OUTER JOIN SELECT * FROM Cust FULL OUTER JOIN Emp Using (city);

8. CASE Statement Simple CASE Statements SELECT Fname, Lname, (CASE DNO WHEN 1 THEN ‘Headquarters’ WHEN 4 THEN ‘Administration’ WHEN 5 THEN ‘Research’ ELSE ‘No department’ END) AS Department FROM Employee; Fname, LnameDepartment John Smith Research Franklin Wong Research Alica Zelaya Administration

9. Searched CASE Statements SELECT Fname, Lname, Salary (CASE Salary WHEN Salary <= 25000 THEN 1500 WHEN Salary > 25000 AND Salary < 50000 THEN 1000 WHEN Salary > 50000 AND Salary < 100000 THEN 500 ELSE 0 END) “Bonus” FROM Employee; Fname, LnameSalaryBonus John Smith 30000 1000 Franklin Wong 40000 1000 Alica Zelaya 25000 1500