SQL Interview Questions - Set 2

This page consists of SQL interview questions and answers.

Q1: What is the difference between UNION and UNION ALL?

We use UNION to combine the content of two structurally compatible tables into one table.

The UNION omits any duplicate whereas UNION ALL keeps the duplicate records in the result.

Q2: Name the different types of JOIN

There are 4 types of JOIN in SQL.

• JOIN or INNER JOIN
• LEFT JOIN or LEFT OUTER JOIN
• RIGHT JOIN or RIGHT OUTER JOIN
• FULL JOIN or FULL OUTER JOIN

Q3: Explain INNER JOIN

JOIN or INNER JOIN is the most common join and it returns all the rows from the left table and right table where the join condition is satisfied.

Click here for more detail on INNER JOIN.

Q4: Explain LEFT JOIN

LEFT JOIN or LEFT OUTER JOIN is used to return all the rows from the left table and only the matching rows from the right table where the join condition is satisfied.

Click here for more detail on INNER JOIN.

Q5: Explain RIGHT JOIN

RIGHT JOIN or RIGHT OUTER JOIN is used to return all the rows from the right table and only the matching rows from the left table where the join condition is satisfied.

Click here for more detail on INNER JOIN.

Q6: Explain FULL JOIN

FULL JOIN or FULL OUTER JOIN returns all the rows from the left and the right table if there is a match in any one of the table.

Q7: What is a Primary Key?

A primary key is a column or collection of columns in a table that can uniquely identify a row.

Q8: What is a Foreign Key?

A foreign key is a column or collection of columns in a table that points at the primary key of some other table.

We use foreign key to link two tables.

Q9: How will you select all the rows where the firstname starts with X?

For this we will use LIKE.

Lets say we have a students table and it has a column firstname which holds the first name of the students.

To get all the students whose first name starts with X we will write the following query.

mysql> SELECT * FROM students
WHERE firstname LIKE 'X%';

mysql> denotes MySQL command line.

Q10: List all the students in the students table in ascending order based on their firstname

To list all the students in ascending order we can write the following query.

mysql> SELECT * FROM students
ORDER BY firstname ASC;

By default the result is returned in ascending order so, we can drop the ASC from the query and just use the following query.

mysql> SELECT * FROM students
ORDER BY firstname;