SQL Queries on Joins

// Get All deparments where corresponding employee first name is blank

SELECT dep.department_name,
  emp.first_name
FROM DEPARTMENTS dep
LEFT OUTER JOIN EMPLOYEES emp
ON dep.department_id  =emp.department_id where emp.first_name is null


// To get first column associated value in Oracle. Ir rownum =2, then returns first two values of first_name
select first_name from employees where rownum <=1


// Connect to database using sql command prompt

conn hr/hr;

// Below query substring the city column values from 1 to 4 characters and returns using MID() function
SELECT MID(City,1,4) AS ShortCity
FROM Customers;

// Returns length of each first_name column using LEN() function
SELECT LEN(first_name) FROM employees;

// Returns current system date using NOW() function
SELECT first_name,  Now() AS PerDate

FROM employees;

Different SQL JOINs

Before we continue with examples, we will list the types the different SQL JOINs you can use:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables