All The Useful SQL Joins Interview Questions & Answers

Q 1. How to change column name in SQL?
The command to change the name of a column is different in different RDBMS.

This is the command to change the name of a column in MYSQL:

ALTER TABLE Customer CHANGE Address Addr char(50);

IN MYSQL, we will start off by using the ALTER TABLE keywords, then we will give in the name of the table. After that, we will use the CHANGE keyword and give in the original name of the column, following which we will give the name to which we would want to rename our column.

This is the command to change the name of a column in ORACLE:

ALTER TABLE Customer RENAME COLUMN Address TO Addr;

In ORACLE, we will start off by using the ALTER TABLE keywords, then we will give in the name of the table. After that, we will use the RENAME COLUMN keywords and give in the original name of the column, following which we will give the TO keyword and finally give the name to which we would like to rename our column.

When it comes to SQL Server, it is not possible to rename the column with the help of ALTER TABLE command, we would have to use sp_rename.

Q 2. What is a view in SQL?
A view is a database object that is created using a Select Query with complex logic, so views are said to be a logical representation of the physical data, i.e Views behave like a physical table and users can use them as database objects in any part of SQL queries.

Let's look at the types of Views:

Simple View
Complex View
Inline View
Materialized View

Simple View:

Simple views are created with a select query written using a single table. Below is the command to create a simple view:

Create VIEW Simple_view as Select * from BANK_CUSTOMER ;

Complex View:
Create VIEW Complex_view as SELECT bc.customer_id , ba.bank_account From Bank_customer bc JOIN Bank_Account ba Where bc.customer_id = ba.customer_id And ba.balance > 300000

Inline View:
A subquery is also called as an inline view if and only if it is called in FROM clause of a SELECT query.

SELECT * FROM ( SELECT bc.customer_id , ba.bank_account From Bank_customer bc JOIN Bank_Account ba Where bc.customer_id = ba.customer_id And ba.balance > 300000)

Q 3. How to drop a column in SQL?
To drop a column is SQL, we will be using this command:

ALTER TABLE employees
DROP COLUMN gender;
We will start off by giving the keywords ALTER TABLE, then we will give the name of the table, following which we will give the keywords DROP COLUMN and finally give the name of the column which we would want to remove.

Q 4. How to remove duplicate rows in SQL?
There are a lot of ways to remove duplicate rows in SQL. Let's look at this example:

SELECT [Name],
   [Age],
   [Gender],
   COUNT(*) AS CNT
FROM [mydata].[dbo].[Employees]
GROUP BY [Name],
     [Age],
     [Gender]
HAVING COUNT(*) > 1;
In the above command, we are using group by and having to count the duplicate records.

Q 5. How to join two tables in SQL?
Joins are used to combine rows from two or more tables, based on a related column between them.

Types of Joins:

- INNER JOIN: Returns rows when there is a match in both tables.
- LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table.
- RIGHT JOIN: Returns all rows from the right table, even if there are no matches in the left table.
- FULL OUTER JOIN: Returns rows when there is a match in one of the tables.
- SELF JOIN:  Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
- CARTESIAN JOIN (CROSS JOIN): Returns the Cartesian product of the sets of records from the two or more joined tables.

Q 6. How to use BETWEEN in SQL?
The BETWEEN operator checks an attribute value within a range. Here is an example of BETWEEN operator:

SELECT * FROM employees WHERE salary between 10000 and 20000;

With this command, we will be able to extract all the records where the salary of the employee is between 10000 and 20000.