ANSI-standard SQL specifies five types of JOIN clauses as follows:

• INNER JOIN (a.k.a. “simple join”): Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOINcombines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.
• CROSS JOIN: Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).

What is the difference between inner and outer join? Explain with example.

Inner Join

Inner join returns rows when there is at least one match in both tables

If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id

Outer Join

Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).

Outer Join can be full outer or single outer

Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)

The (+) sign on the emp side of the predicate indicates that emp is the outer table here. The above SQL can be alternatively written as below (will yield the same result as above):

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept LEFT OUTER JOIN EMPLOYEE emp
ON dept.id = emp.dept_id  

What is the difference between JOIN and UNION?

SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.

UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.

SELECT * FROM EMP1
UNION
SELECT * FROM EMP2;

What is the difference between UNION and UNION ALL?

UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.

In the following example, I am choosing exactly the same employee from the emp table and performing UNION and UNION ALL. Check the difference in the result.

SELECT * FROM EMPLOYEE WHERE ID = 5
UNION ALL
SELECT * FROM EMPLOYEE WHERE ID = 5

SELECT * FROM EMPLOYEE WHERE ID = 5
UNION 
SELECT * FROM EMPLOYEE WHERE ID = 5

What is the difference between WHERE clause and HAVING clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

To understand this, consider this example.
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

SELECT * FROM DEPT WHERE ID > 3

Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
GROUP BY dept.name
HAVING AVG(emp.sal) > 80

As you see above, there is only one department (Engineering) where average salary of employees is greater than 80.

What is the difference among UNION, MINUS and INTERSECT?

UNION combines the results from 2 tables and eliminates duplicate records from the result set.

MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.

INTERSECT operator returns us only the matching or common rows between 2 result sets.

To understand these operators, let’s see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.

UNION

SELECT * FROM EMPLOYEE WHERE ID = 5
UNION 
SELECT * FROM EMPLOYEE WHERE ID = 6

MINUS

SELECT * FROM EMPLOYEE
MINUS
SELECT * FROM EMPLOYEE WHERE ID > 2

INTERSECT

SELECT * FROM EMPLOYEE WHERE ID IN (2, 3, 5)
INTERSECT
SELECT * FROM EMPLOYEE WHERE ID IN (1, 2, 4, 5)

What is Self Join and why is it required?

Self Join is the act of joining one table with itself.

Self Join is often very useful to convert a hierarchical structure into a flat structure

In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. This is an example of how a hierarchy (in this case employee-manager hierarchy) is stored in the RDBMS table. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:

SELECT e.name EMPLOYEE, m.name MANAGER
FROM EMPLOYEE e, EMPLOYEE m
WHERE e.mgr_id = m.id (+)

The only reason we have performed a left outer join here (instead of INNER JOIN) is we have one employee in this table without a manager (employee ID = 1). If we perform inner join, this employee will not show-up.

How can we transpose a table using SQL (changing rows to column or vice-versa) ?

The usual way to do it in SQL is to use CASE statement or DECODE statement.

How to generate row number in SQL Without ROWNUM

Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method  This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:

SELECT name, sal, (SELECT COUNT(*) FROM EMPLOYEE i WHERE o.name >= i.name) row_num
FROM EMPLOYEE o
order by row_num

SALROW_NUM

801

602

803

1004

505

606

707

508

1009

7010

. For example, in Oracle database, every SQL result set contains a hidden column called ROWNUM. We can just explicitly select ROWNUM to get sequence numbers.

How to select first 5 records from a table?

In Oracle,

SELECT * 
FROM EMP
WHERE ROWNUM <= 5;

In SQL Server,

SELECT TOP 5 * FROM EMP;

Generic solution,

I believe a generic solution can be devised for this problem if and only if there exists at least one distinct column in the table. For example, in our EMP table ID is distinct. We can use that distinct column in the below way to come up with a generic solution of this question that does not require database specific functions such as ROWNUM, TOP etc.

SELECT  name 
FROM EMPLOYEE o
WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5

I have taken “name” column in the above example since “name” is happened to be unique in this table. I could very well take ID column as well.

What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work.

ROW_NUMBER() is an analytical function which is used in conjunction to OVER() clause wherein we can specify ORDER BY and also PARTITION BY columns.

Suppose if you want to generate the row numbers in the order of ascending employee salaries for example, ROWNUM will not work. But you may use ROW_NUMBER() OVER() like shown below:

SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal
FROM EMPLOYEE o

What are the differences among ROWNUM, RANK and DENSE_RANK?

ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set.

RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no record will be assigned the 3rd rank as no one came in third, according to RANK. See below:

SELECT name, sal, rank() over(order by sal desc) rank_by_sal
FROM EMPLOYEE o

DENSE_RANK, like RANK, does not assign unique numbers, but it does assign contiguous numbers. Even though two records tied for second place, there is a third-place record. See below:

SELECT name, sal, dense_rank() over(order by sal desc) dense_rank_by_sal
FROM EMPLOYEE o

sql> select Count(*) as Total from Orders;
+-------+
| Total |
+-------+
|  100  |
+-------+

sql> select Count(*) as Cust123Total from Orders where customer_id = '123'
+--------------+
| Cust123Total |
+--------------+
|      15      |
+--------------+

sql> select Count(*) from Orders where customer_id <> '123'

select case when null = null then 'Yup' else 'Nope' end as Result;

select case when null is null then 'Yup' else 'Nope' end as Result;

sql> SELECT * FROM runners;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Doe     |
|  2 | Jane Doe     |
|  3 | Alice Jones  |
|  4 | Bobby Louis  |
|  5 | Lisa Romero  |
+----+--------------+

sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event          | winner_id |
+----+----------------+-----------+
|  1 | 100 meter dash |  2        |
|  2 | 500 meter dash |  3        |
|  3 | cross-country  |  2        |
|  4 | triathalon     |  NULL     |
+----+----------------+-----------+

select * from runners where id not in (select winner_id from races)

create table dbo.envelope(id int, user_id int)
create table dbo.docs(idnum int,pageseq int,doctext varchar(100))

Insert into dbo.envelope values(1,1),(2,2),(3,3)

insert into dbo.docs(idnum,pageseq) values(1,5),(2,6),(null,0)

idnum	pageseq	doctext
1	5	5
2	6	6
NULL	0	NULL

SELECT Id, YEAR(BillingDate) AS BillingYear 
FROM Invoices
WHERE BillingYear >= 2010;

SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE YEAR(BillingDate) >= 2010;

Id	Name			ReferredBy
1	John Doe		NULL
2	Jane Smith		NULL
3	Anne Jenkins		2
4	Eric Branford		NULL
5	Pat Richards		1
6	Alice Barnes		2

SELECT Name FROM Customers WHERE ReferredBy <> 2;

SELECT Name FROM Customers WHERE ReferredBy IS NULL OR ReferredBy <> 2

SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2

SELECT i.Id, i.BillingDate, c.Name, r.Name AS ReferredByName
FROM Invoices i
 JOIN Customers c ON i.CustomerId = c.Id
 LEFT JOIN Customers r ON c.ReferredBy = r.Id
ORDER BY i.BillingDate;

Select * From Emp, Dept

Id	Name Sex	Salary
1     A   m      2500
2     B   f      1500
3     C   m      5500
4     D   f       500

UPDATE SALARIES SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END

. Get all employee details from the employee table

 Select * from employee

. Get First_Name,Last_Name from employee table

 Select first_name, Last_Name from employee

. Get First_Name from employee table using alias name “Employee Name”

 Select first_name Employee Name from employee

. Get First_Name from employee table in upper case

 Select upper(FIRST_NAME) from EMPLOYEE

. Get First_Name from employee table in lower case

Select lower(FIRST_NAME) from EMPLOYEE

. Get unique DEPARTMENT from employee table

select distinct DEPARTMENT from EMPLOYEE

. Select first 3 characters of FIRST_NAME from EMPLOYEE

Oracle Equivalent of SQL Server SUBSTRING is SUBSTR,

Query : select substr(FIRST_NAME,0,3) from employee

SQL Server Equivalent of Oracle SUBSTR is SUBSTRING,

Query : select substring(FIRST_NAME,0,3) from employee

MySQL Server Equivalent of Oracle SUBSTR is SUBSTRING. In MySQL start position is 1, Query : select substring(FIRST_NAME,1,3) from employee

Get position of ‘o’ in name ‘John’ from employee table

Oracle Equivalent of SQL Server CHARINDEX is INSTR,

Query : Select instr(FIRST_NAME,’o’) from employee where first_name=’John’

SQL Server Equivalent of Oracle INSTR is CHARINDEX,

Query: Select CHARINDEX(‘o’,FIRST_NAME,0) from employee where first_name=’John’

MySQL Server Equivalent of Oracle INSTR is LOCATE,

Query: Select LOCATE(‘o’,FIRST_NAME) from employee where first_name=’John’

Get FIRST_NAME from employee table after removing white spaces from right side

select RTRIM(FIRST_NAME) from employee

. Get FIRST_NAME from employee table after removing white spaces from left side

select LTRIM(FIRST_NAME) from employee

. Get length of FIRST_NAME from employee table

. Get First_Name from employee table after replacing ‘o’ with ‘$’

select REPLACE(FIRST_NAME,'o','$') from employee

. Get First_Name and Last_Name as single column from employee table separated by a ‘_’

. Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table

SQL Queries in Oracle,

Select FIRST_NAME, to_char(joining_date,’YYYY’) JoinYear , to_char(joining_date,’Mon’), to_char(joining_date,’dd’) from EMPLOYEE

SQL Queries in SQL Server,

select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING (convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE

SQL Queries in MySQL,

select year(joining_date),month(joining_date), DAY(joining_date) from EMPLOYEE

. Get all employee details from the employee table order by First_Name Ascending

Select * from employee order by FIRST_NAME asc

. Get all employee details from the employee table order by First_Name descending

Select * from employee order by FIRST_NAME desc

. Get all employee details from the employee table order by First_Name Ascending and Salary descending

Select * from employee order by FIRST_NAME asc,SALARY desc

Select * from EMPLOYEE where FIRST_NAME='John'

Select * from EMPLOYEE where FIRST_NAME in('John','Roy')

Select * from EMPLOYEE where FIRST_NAME not in ('John','Roy')

Select * from EMPLOYEE where FIRST_NAME like 'J%'

Select * from EMPLOYEE where FIRST_NAME like '%o%'

Select * from EMPLOYEE where FIRST_NAME like '%n'

Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)

Select * from EMPLOYEE where FIRST_NAME like 'J___' (Underscores)

Select * from EMPLOYEE where Salary >600000

Select * from EMPLOYEE where Salary <800000

Select * from EMPLOYEE where Salary between 500000 and 800000

Select * from EMPLOYEE where FIRST_NAME in ('John','Michael')

select * from EMPLOYEE where exists (select * from INCENTIVES)

select EMPLOYEE_ID from EMPLOYEE
MINUS
select EMPLOYEE_REF_ID from INCENTIVES

Tip - Use another single quote before special character
Insert into employee (LAST_NAME) values ('Test''')

Select * from EMPLOYEE where lower(LAST_NAME)=upper(LAST_NAME)

select FIRST_NAME from EMPLOYEE union select LAST_NAME from EMPLOYEE

DROP table employee;

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)

Alter TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;

ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK;