50+ Most Important SQL Interview Question And Answer

In this article we have collected and explained the Most important SQL  Interview Questions and Answers.

Let us start Preparing.

SQL interview questions

Q1). What is SQL and MySQL?

Ans:

SQL stands for Structured Query Language. This query language is used to maintain and manage a database. SQL comes with various syntax to perform database operations like retrieving, managing, adding, or manipulating data.

MySQL is a relational database management system developed in 1995 by MySQL AB. It uses SQL queries to perform operations on the database. MySQL
allows us to handle, store, modify and delete data and store data in an organized way.

Q2). What are the subset of SQL?

Ans:

There are three main subsets of the SQL language:

Q3). What is DDL?

Ans:

DDL stands for Data Definition Language and consists of some SQL commands that are used to define data structures such as to create, alter, or drop tables. The list of DDL Commands are CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME.

Q4). What is DML?

Ans:

DML Stands for Data Manipulation Language, Consists of the SQL commands that are used for data manipulation in a database. Some DML Statements are SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, LOCK TABLE.

Q5). What is DCL?

Ans:

DCL Stands for Data Control Language, Consists of the SQL commands that are used for permission, rights, and other control in the database. Some DCL commands are GRANT and REVOKE.

Q6) What is TCL?

Ans:

TCL stands for Transaction Control Language, Consists of the SQL commands that deal with transactions within the database. Some TCL commands are COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.

Q7). What is Table and Fields in SQL?

Ans :

A table is used to store data in an RDMS that contains rows and columns where data is stored.

In the Table, The rows are known as records and the columns are known as fields.

Q8) Tell some list of Constraints in SQL.

Ans:

SQL constraint is a rule on the data in a table that limit the data that can go into a table on a given condition.

Constraints can be applied to table labels and column labels.

Some List of SQL constraints are

NOT NULL – This constraint ensures no NuLL value can be added to the Column.

PRIMARY KEY – Primary Key Uniquely Identify each row in a table it can’t be duplicated. It is a combination of NOT NULL and UNIQUE.

UNIQUE – This constraint ensures a unique value in a given column. Unique keys can have a NULL value.

Q9) What is Primary Key in the table?

Ans:

Primary Key Uniquely Identify each row in a table it can’t be duplicated. It is a combination of NOT NULL and UNIQUE.

Syntax

CREATE TABLE EMPLOYEE(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   SALARY   DECIMAL (10, 2),   
   PRIMARY KEY (ID)
);

Q10) What is Foreign Key?

Ans:

A FOREIGN KEY is a field in a table that links to the Primary Key of another table. We can also say it is a constraint that is used to prevent actions that might destroy the links between tables.

The table has a primary key known as the parent table and a table with a foreign key known as the child table.

For example

To demonstrate the Foreign key we have to create two tables one will be Primary and the Second will be the child Table.

And there will be one Common Column in both tables.

CREATE TABLE Class (
    ClassID int NOT NULL, 
    StudentID int,
    PRIMARY KEY (ClassID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

Q11) What is a Unique Key?

Ans:

A UNIQUE Key Constraint in a column ensures all values are different.

It is similar to Primary Key only the difference is UNIQUE key constraints allow NULL value and a table can have multiple UNIQUE Key.

CREATE TABLE Student (
    ID int NOT NULL UNIQUE,
    Name varchar(255) NOT NULL,
    Age int
);

Q12) Tell the difference between Primary Key and Unique Key.

Ans:

  • Primary Key won’t allow NULL value But Unique Key allows NULL Value.
  • The table can have only one Primary Key But More than one unique Key.
  • Primary Key supports auto-increment whereas Unique Key won’t support auto increments.
  • The primary key creates a clustered index on selection but Unique Key creates a non-clustered index.
  • The primary Key value cannot be changed or modified but the Unique key value can be changed.

Q13). What is Join in SQL?

Ans:

Join is an operation to combine two or more tables to fetch the rows or data on the given conditions. Suppose we are storing data in multiple tables that are linked with Foreign Key.

Now when we have to fetch the data, we will have to join the table in order to fetch the required information from all the tables.

To Perform Join, A common field should be present in both tables.

Q14) Tell some different types of Join.

Ans:

There are different types of Join listed below

INNER JOIN: INNER Join will return information from both tables if the condition is matched.

LEFT JOIN: LEFT Join will return all information from the left table and only match data from the right table.

RIGHT JOIN: RIGHT Join will return all information from the right table and only match data from the left table.

FULL JOIN: Full Join will return all information either from left or right for matched conditions.

SELF JOIN :

CARTESIAN JOIN: CARTESIAN JOIN also known as CROSS JOIN, is used to combine each row of the first table with each row of the second table.

Q15). What is Cross-Join?

Ans: CROSS JOIN also known as CARTESIAN JOIN, is used to combine each row of the first table with each row of the second table.

Syntax of CROSS-JOIN

SELECT column_name(s), column_name(p) 
FROM table1 CROSS JOIN table2;

Q16). What is Normalization?

Ans:

Normalization is a process to reduce the data duplication in a large table by breaking it into smaller tables and linking them using relationships.
Normalizations also eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.

Types of Normal forms are

1). 1 NF

2). 2NF

3). 3NF

4). BCNF

5). 4NF

6). 5NF

Q17). What is denormalization?

Ans:

Denormalization is a process in which we add some redundant data to the table to get rid of the complex join operation. In normalization operation, we break the large table into the smaller table. This increase the execution time to get the data after performing multiple joins on the table. To get rid of this we do denormalization to increase the execution time.

Q18). What are the different types of SQL operators?

Ans: 

SQL has some special keywords to perform some specific operations known as SQL Operators. Mostly these operators are used in the WHERE clause of SQL commands.

Below are the list of  SQL operators :

Arithmetic Operators to perform mathematical operations

  • addition (+)
  • subtraction (-)
  • multiplication (*)
  • division (/)
  • remainder/modulus (%)

Comparison Operators to perform comparisons operations

  • equal to (=)
  • not equal to (!= or <>)
  • less than (<), 
  • greater than (>)
  • less than or equal to (<=)
  • greater than or equal to (>=)
  • not less than (!<)
  • not greater than (!>)

Logical Operators to evaluate the expressions

  • ALL
  • AND
  • ANY
  • ISNULL
  • EXISTS
  • BETWEEN
  • IN 
  • LIKE
  • NOT
  • OR 
  • UNIQUE

Bitwise Operators to perform operations on bits

  • AND (& symbol)
  • OR (|, ^)
  • NOT (~)

Q19). Tell some clauses used with Select Query.

Ans:

List of clauses used with Select Query are

  • SELECT
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • OFFSET
  • FETCH FIRST
  • UNION
  • INTERSECT
  • EXCEPT
  • WITH

Q20). What are SET Operators? Explain UNION, UNION ALL, Minus, and Intersect commands.

Ans:

UNION: UNION Command is used to combine the result of two select queries and make it one. To Perform the UNION Operation, the Select query should have a similar field, and also the data type of the all field should be similar. Here Duplicate rows will be eliminated

Syntax:

SELECT (coloumn) from table1 [WHERE condition] UNION SELECT (coloumn) from table2 [WHERE condition];

UNION ALL: UNION ALL is similar to UNION the only difference is, UNION ALL can’t eliminate the duplicate value.

Syntax:

SELECT (coloumn) from table1 [WHERE condition] UNION ALL SELECT (coloumn) from table2 [WHERE condition];

MINUS: MINUS Command returns the result only from the first select query which is not available in the second select query. In MINUS Operation, the Select query should have a similar field, and also the data type of the all field should be similar.

SELECT (coloumn) from table1 [WHERE condition] MINUS SELECT (coloumn) from table2 [WHERE condition];

INTERSECT: INTERSECT Command is used to return only common data as a result of two select queries. Intersection operation always returns the distinct rows.

SELECT (coloumn) from table1[WHERE condition] INTERSECT SELECT (coloumn) from table2 [WHERE condition];

Q21). What is Cursor in SQL?

Ans:

A Cursor in SQL is a Temporary Memory or temporary work area which is used when a DML operation is performed on the table. 

There are two types of Cursor

1. Implicit Cursor

2. Explicit Cursor

Q22). What is Trigger in SQL?

Ans: A trigger is a set of SQL statements that is called automatically whenever a database event occurs. Triggers are associated with tables. The trigger can be called when any row is inserted into the table or we updated any column value. The trigger is useful in a scenario to suppose in Student Report Database student’s marks are recorded. Now we want to suppose whenever a new record is the total and average of specified marks should automatically insert. Trigger Syntax 

CREATE TRIGGER schema.trigger_name  
ON table_name  
AFTER  {INSERT, UPDATE, DELETE}  
NOT FOR REPLICATION]  
AS  
{SQL_Statements}

Q23). What is Alias in SQL?

Ans:

Alias is a temporary name given to a table or a column in a table. With the help of AS keywords alias is created and it only exists for the duration of that query.

Syntax of alias

SELECT column_name AS alias_name FROM table_name;

Q24). What is View in SQL?

Ans: In SQL, View is a virtual table that also contains rows and columns. To create View, we use CREATE VIEW statement. The view can be created from one or many tables which depends on the written SQL query to create a view. 

CREATE VIEW view_name ASSELECT column1, column2.....FROM table_nameWHERE [condition];

Types of View are

  • Simple View
  • Complex View
  • Inline View
  • Materialized View

Q25). Differences between views and tables

Ans:

  • A View is a virtual table extracted from a database whereas a table is real with rows and columns.
  • View depends on the table but a Table is an independent object.
  • View is used to extract data from a table whereas Table is used to store data.
  • View Don’t allow to add, update or delete any data from the view But In the table, we can perform this operation.
  • View Doesn’t occupy space in the system but the Table occupies the space in System.

Q26). What is IN operator?

Ans:

The IN operator allows us to use multiple values in a WHERE clause. Instead of using multiple OR we can use IN Operator.

Syntax

SELECT column(s)
FROM table
WHERE column IN (value1, value2, ...);

Suppose we need the Details of Students that have Roll Numbers 1, 3, 4, and 7, Our Query will be

SELECT SId, FirstName, LastName, Marks FROM Student WHERE SId IN (1, 3, 4, 7)

Q27). What is Between Operator?

Ans:

BETWEEN operator is used to define the range to select the output. This range can apply on numbers, text, or dates.

Syntax

SELECT column(s)
FROM table
WHERE column BETWEEN value1 AND value2;

Query Example

SELECT * FROM Student
WHERE RollNo BETWEEN 1 AND 3;

Q28). What are Aggregate functions?

Ans: 

In SQL, Aggregate functions are used to perform an operation on multiple values to get a result in a single value. For example the sum of marks obtained by all students in a table. Here we will use the aggregate function.

Some Aggregate Functions are

  • Count()
  • Sum()
  • Avg()
  • Min()
  • Max()
  • FIRST()
  • LAST()

29). What is the difference between DELETE and TRUNCATE statements in SQL?

Ans:

  • DELETE command is used to delete specific rows whereas Truncate Command is used to delete all rows from a table.
  • With DELETE Command we can use the WHERE clause But With TRUNCATE we can’t use the WHERE Clause.
  • DELETE Command Lock the table row before deleting Whereas TRUNCATE Command Lock the entire table.
  • DELETE Command is slower as compared to TRUNCATE Command.
  • DELETE Command can be rollback But Truncate can’t roll back

30). What is the difference between DROP and TRUNCATE statements in SQL?

Ans:

  • DROP command removes the table definition and its contents But the TRUNCATE command only removes the content, not the table definition.
  • DROP command frees the memory after deletion But the TRUNCATE command does not free the memory.
  • DROP Command removes integrity constraints from the table But the TRUNCATE command Won’t.
  • DROP Command also deletes the table structure But TRUNCATE Command Keeps the Table Structure safe.

31). What is the DISTINCT Keyword?

Ans:

DISTINCT keyword is used with the SELECT statement to fetch only unique records. It eliminates the duplicate values if any are available in Colum.

Suppose the Name of students may be common in the Student table. But if we want only the unique name then we can use DISTINCT Keyword with select Query.

Syntax

SELECT DISTINCT column1, column2
FROM table;

Example

SELECT DISTINCT Name FROM Student;

32). What is Order By clause?

Ans:

ORDER By Clause in SQL is used to sort the result either in ascending order or descending order.

ORDER BY ASC
This is used to sort the output in ascending order.
Syntax

SELECT *
FROM Customers
ORDER BY age ASC;

ORDER BY DESC

This is used to sort the output in descending order.


Syntax

SELECT *
FROM Customers
ORDER BY age DESC;

33). Tell the difference between WHERE & HAVING Clauses.

Ans:

  • WHERE Clause is used to filter the rows from the table on given conditions whereas the HAVING clause is used to filter groups instead of one row at a time.
  • We can use the WHERE Clause without GROUP BY Clause but the Having clause can’t be used without GROUP BY clause.
  • Where Clause can be used with Select, update, and delete statements but Having Clause can be used with Select only.
  • WHERE Clause can be used before GROUP BY Clause But Having Clause can be used after GROUP BY Clause.

34). What is the FLOOR function used in SQL?

Ans:

The FLOOR() function finds the largest integer value to a given number, which can be an equal or lesser number.
Example
SELECT FLOOR(21.55) AS FloorValue;
Output: 21

35). What is the COALESCE function?

Ans:

The COALESCE function returns the first non-null value from the input.

Syntax

SELECT COALESCE(val1, val2, val3,……, nth );

Example

SELECT COALESCE(NULL, 1, 2, ‘sql’);