All The Useful SQL Interview Questions & Answers (Basics)

Q 1. What is SQL?
SQL stands for Structured Query Language. It is the primary language to interact with databases. With the help of SQL, we can extract data from a database, modify this data and also update it whenever there is a requirement. This query language is evergreen and is widely used across industries. For example, if a company has records of all the details of their employees in the database. With the help of SQL, all of this data can be queried to find out valuable insights in a short span of time.

Q 2. How to create a table in SQL?
The command to create a table in sql is extremely simple:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
  ....
);
We will start off by giving the keywords, CREATE TABLE, then we will give the name of the table. After that in braces, we will list out all the columns along with their datatypes.

For example, if we want to create a simple employee table:

CREATE TABLE employee (
    name varchar(25),
    age int,
    gender varchar(25),
  ....
);

Q 3. How to delete a table in SQL?
There are two ways to delete a table from sql: DROP and TRUNCATE. The DROP TABLE command is used to completely delete the table from the database. This is the command:

DROP TABLE table_name;

The above command will completely delete all the data present in the table along with the table itself.

But if we want to delete only the data present in the table but not the table itself, then we will use the truncate command:

DROP TABLE table_name ;

Q 4. How to change a table name in SQL?
This is the command to change a table name in SQL:

ALTER TABLE table_name

RENAME TO new_table_name;

We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.

For example, if we want to change the 'employee' table to 'employee_information', this will be the command:

ALTER TABLE employee

RENAME TO employee_information;

Q 5. How to delete a row in SQL?
We will be using the DELETE query to delete existing rows from the table:

DELETE FROM table_name

WHERE [condition];

We will start off by giving the keywords DELETE FROM, then we will give the name of the table, after that we will give the WHERE clause and give the condition on the basis of which we would want to delete a row.

For example, from the employee table, if we would like to delete all the rows, where the age of the employee is equal to 25, then this will the command:

DELETE FROM employee

WHERE [age=25];

Q 6. How to create a database in SQL?
A database is a repository in sql, which can comprise of multiple tables.

This will be the command to create a database in sql:

CREATE DATABASE database_name.

Q 7. What is Normalization in SQL?
Normalization is used to decompose a larger, complex table into simple and smaller ones. This helps us in removing all the redundant data.

Generally, in a table, we will have a lot of redundant information which is not required, so it is better to divide this complex table into multiple smaller tables which contains only unique information.

Let's look at the rules for a table to be in first normal form, second normal form and third normal form:

First normal form:

A relation schema is in 1NF, if and only if:

-  All attributes in the relation are atomic(indivisible value)
- And there are no repeating elements or group of elements.

Second normal form:

A relation is said to be in 2NF, if and only if:

It is in 1st Normal Form.

No partial dependency exists between non-key attributes and key attributes.

Third Normal form:

- A relation R is said to be in 3NF if and only if:

It is in 2NF.

No transitive dependency exists between non-key attributes and key attributes through another non-key attribute.

Q 8. What is join 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.

INNER JOIN:

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.

SYNTAX :

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
INNER JOIN table2
ON table1.commonfield = table2.commonfield;

LEFT JOIN:

The LEFT JOIN returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

SYNTAX :

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
LEFT JOIN table2
ON table1.commonfield = table2.commonfield;

RIGHT JOIN:

The RIGHT JOIN returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

SYNTAX :

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
RIGHT JOIN table2
ON table1.commonfield = table2.commonfield;

FULL OUTER JOIN:

The FULL OUTER JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SYNTAX :

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
Left JOIN table2
ON table1.commonfield = table2.commonfield;
Union
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
Right JOIN table2
ON table1.commonfield = table2.commonfield;

SELF JOIN:

The SELF JOIN joins a table to itself; temporarily renaming at least one table in the SQL statement.

SYNTAX:

SELECT a.col1, b.col2,..., a.coln
FROM table1 a, table1 b
WHERE a.commonfield = b.commonfield;

Q 9. What is SQL server?
To understand what exactly is SQL Server, we need to understand what is DBMS and RDBMS. DBMS stands for Database Management System. When we have a huge database with us, we would need a proper management system which would help us organise this database.  

There are 4 types of database management systems:

Hierarchical 
Network
Relational 
Object-Oriented.

Out of these database management systems, SQL Server comes under the category of Relational database management system.  A relational database refers to a database that stores data in a structured format, using rows and columns. This makes it easier to locate and access specific values within the database. It is 'relational' because the values within each table are related to each other. The relational structure makes it possible to run queries across multiple tables at once.

Q 10. How to insert date in SQL?
If the RDBMS is MYSQL, this is how we can insert date:

'INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2020-9-10')';

Q 11. What is Primary Key in SQL?
Primary Key is a constraint in SQL. So, before understanding what exactly is a primary key, let's understand what exactly is a constraint in SQL. Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. Constraints can either be column level or table level. 

Let's look at the different types of constraints which are present in SQL:

Constraint     Description
NOT NULL    Ensures that a column cannot have a NULL value.
DEFAULT    Provides a default value for a column when none is specified.
UNIQUE    Ensures that all the values in a column are different
PRIMARY    Uniquely identifies each row/record in a database table
FOREIGN    Uniquely identifies a row/record in any another database table
CHECK    The CHECK constraint ensures that all values in a column satisfy certain    conditions.
INDEX    Used to create and retrieve data from the database very quickly.

You can consider Primary Key constraint to be a combination of UNIQUE and NOT NULL constraint. This means that if a column is set as a primary key, then this particular column cannot have any null values present in it and also all the values present in this column must be unique.

Q 12. How do I view tables in SQL?
To view tables in SQL, all you need to do is give this command:

Show tables;

Q 13. What is PL SQL?
PL SQL stands for Procedural language constructs for Structured Query Language. PL SQL was introduced by Oracle to overcome the limitations of plain sql. So, pl sql adds in procedural language approach to the plain vanilla sql.

One thing to be noted over here is that pl sql is only for oracle databases. If you don't have an Oracle database, then you cant work with PL SQL.

While, with the help of sql, we were able to DDL and DML queries, with the help of PL SQL, we will be able to create functions, triggers and other procedural constructs.

Q 14. What is MYSQL?
To understand exactly what is MYSQL, we need to understand what is DBMS and RDBMS. DBMS stands for Database Management System. When we have a huge database with us, we would need a proper management system which would help us organise this database.  There are 4 types of database management systems:

Hierarchical 
Network
Relational 
Object - Oriented.
Out of these database management systems, MYSQL comes under the category of Relational database management system.  A relational database refers to a database that stores data in a structured format, using rows and columns. This makes it easier to locate and access specific values within the database. It is 'relational' because the values within each table are related to each other. The relational structure makes it possible to run queries across multiple tables at once.

Q 15. How can I see all tables in SQL?
Different database management systems have different queries to see all the tables.

To see all the tables in MYSQL, we would have to use this query:

show tables;

This is how we can see all tables in ORACLE:

SELECT 
   table_name
FROM
   User_tables;
This is how we can extract all tables in SQL Server:

SELECT 
   *
FROM
   Information_schema.tables;

Q 16. What is ETL in SQL?
ETL stands for Extract, Transform and Load. It is a three step process, where we would have to start off by extracting the data from sources. Once we collate the data from different sources, what we have is raw data. This raw data has to be transformed into tidy format, which will come in the second phase. Finally, we would have to load this tidy data into tools which would help us to find insights.

Q 17. How to install SQL?
SQL stands for Structured Query Language and it is not something you can install. To implement sql queries, you would need a relational database management system. There are different varieties of relational database management systems such as:

ORACLE
MYSQL
SQL Server
Hence, to implement sql queries, we would need to install any of these Relational Database Management Systems.

Q 18. What is the update command in SQL?
The update command comes under the DML(Data Manipulation Langauge) part of sql and is used to update the existing data in the table.

UPDATE employees

SET last_name='Cohen'

WHERE employee_id=101;

With this update command, I am changing the last name of the employee.

Q 19. How to rename column name in SQL Server?
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 20. What are the types of SQL Queries?
We have four types of SQL Queries:

DDL (Data Definition Language): the creation of objects
DML (Data Manipulation Language): manipulation of data
DCL (Data Control Language): assignment and removal of permissions
TCL (Transaction Control Language): saving and restoring changes to a database

Let's look at the different commands under DDL:

Command         Description
CREATE    Create objects in the database
ALTER    Alters the structure of the database object
DROP    Delete objects from the database
TRUNCATE    Remove all records from a table permanently
COMMENT    Add comments to the data dictionary
RENAME    Rename an object

Q 21.  Write a Query to display the number of employees working in each region.
SELECT region, COUNT(gender) FROM employee GROUP BY region;

Q 22. What are Nested Triggers?
Triggers may implement DML by using INSERT, UPDATE, and DELETE statements. These triggers that contain DML and find other triggers for data modification are called Nested Triggers.

Q 23. Write SQL query to fetch employee names having a salary greater than or equal to 20000 and less than or equal 10000.
By using BETWEEN in the where clause, we can retrieve the Employee Ids of employees with salary >= 20000and <=10000. SELECT FullName FROM EmployeeDetails WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary WHERE Salary BETWEEN 5000 AND 10000)

Q 24. Given a table Employee having columns empName and empId, what will be the result of thisSQL query below.

 select empName from Employee order by 2 asc;

'Order by 2' is valid when there are at least 2 columns used in SELECT statement. Here this query will throw error because only one column is used in the SELECT statement. 

Q 25. What is OLTP?
OLTP stands for Online Transaction Processing. And is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency. 

Q 26. What is Data Integrity?
Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

Q 27. What is OLAP?
OLAP stands for Online Analytical Processing. And a class of software programs which are characterized by relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations. 

Q 28. Find the Constraint information from the table.
There are so many times where user needs to find out the specific constraint information of the table. following queries are useful, SELECT * From User_Constraints; SELECT * FROM User_Cons_Columns;

Q 29. Can you get the list of employees with same salary? 
Select distinct e.empid,e.empname,e.salary from employee e, employee e1 where e.salary =e1.salary and e.empid != e1.empid 

Q 30. What is an alternative for TOP clause in SQL?

1. ROWCOUNT function 
2. Set rowcount 3
3. Select * from employee order by empid desc Set rowcount 0 

Q 31. Will following statement give error or 0 as output? SELECT AVG (NULL)
Error. Operand data type NULL is invalid for Avg operator. 

Q 32. What is the Cartesian product of the table?
The output of Cross Join is called a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 rows.

Q 33. What is a schema in SQL?
Our database comprises of a lot of different entities such as tables, stored procedures, functions, database owners and so on. To make sense of how all these different entities interact, we would need the help of schema. So, you can consider schema to be the logical relationship between all the different entities which are present in the database.

Once we have a clear understanding of the schema, this helps in a lot of ways:

We can decide which user has access to which tables in the database.
We can modify or add new relationships between different entities in the database.
Overall, you can consider a schema to be a blueprint for the database, which will give you the complete picture of how different objects interact with each other and which users have access to different entities.

Q 34. What is the WHERE clause in SQL?
The 'Where' clause is used to extract elements from the table on the basis of a condition.

Let's say, we have a table like this:

Now, if you want to extract all the records 'where' the value of 'Sepal.Length' is greater than 6, then you can use a query like this:

select * from iris where Sepal.Length>6 

As, you see, we have extracted all the records, where the value of 'Sepal.Length' is greater than 6.

Similarly, if you want to extract all records where 'Species' is Virginia, this will be the query:

2. select * from iris where Species='virginica' 

Q 35. How to delete a column in SQL?
To delete a column in SQL we will be using DROP COLUMN method:

ALTER TABLE employees

DROP COLUMN age;

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 36. What is a unique key in SQL?
Unique Key is a constraint in SQL. So, before understanding what exactly is a primary key, let's understand what exactly is a constraint in SQL. Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. Constraints can either be column level or table level. 

Unique Key: 

Whenever we give the constraint of unique key to a column, this would mean that the column cannot have any duplicate values present in it. In other words, all the records which are present in this column have to be unique.

Q 37. How to implement multiple conditions using WHERE clause?
We can implement multiple conditions using AND, OR operators:

SELECT * FROM employees WHERE first_name = 'Steven' AND salary <=10000;

In the above command, we are giving two conditions. The condition ensures that we extract only those records where the first name of the employee is 'Steven' and the second condition ensures that the salary of the employee is less than $10,000. In other words, we are extracting only those records, where the employee's first name is 'Steven' and this person's salary should be less than $10,000.