Top 50 SQL Interview Questions And Answers

SQL Interview Questions And Answers, Are you preparing for a service-based or product-based company?. Then the interviewer will always check your SQL Knowledge. That is why it is important to know what are the types of SQL Questions asked in the interview and thus prepare well to crack any sort of interview.

Below is the list of questions just prepare it well.

Most Important SQL Interview Questions

SQL Interview Questions And Answers

1. What is Database management System?

Ans: Database is the structured collection and organization of data in the physical storage. And Database management system is the system used for managing this organized data.

A DBMS allow user to interact, modify, and retrieve data and provide flexibility to perform various operations on it.

2. What is SQL?

Ans: Full form of SQL is Structured Query Language, SQL is a Standard language used to communicate with Database and used to perform various operation on Databases like insertion, deletion, updating, and retrieval of data from the database.

3. What are the different subsets of SQL?

Ans: Following are the different subsets of SQL:-

  • DDL- DDL stands for Data Definition Language, with the help of DDL end-user can perform operations on databases such as CREATE, ALTER, and DELETE data objects.
  • DML- DML stands for Data Manipulation Language as the name suggests it is to manipulate data, with the help of DML end-user can perform the operations on a database such as INSERT, UPDATE, DELETE.
  • DCL-DCL stands for Data Control Language, it allow user to have control access to the database. Example-Grant permission and Revoke permission.

4. What are Table, Fields, and Record in Database?

Ans: The data stored in the form of rows and columns in an organized fashion is called a table.The columns in the table are called fields and rows in the table are called records.

5. Explain Constraints in SQL?

Ans: Constraints are used to specify limits on database attributes. Constraints can be applied to single or multiple fields in the SQL table during creation or after the creation of the table.

ALTER TABLE command is to set the constraints of the table. The constraints are:

  • PRIMARY KEY.
  • FOREIGN KEY.
  • UNIQUE KEY.
  • NOT NULL
  • DEFAULT KEY.

6. Explain Primary Key?

  • The primary key is a field or the set of fields that distinctly specify a row.
  • The Primary key is the special type of Unique key.
  • Value of primary key Cannot Be Null.
  • For example- Aadhar card number can be a primary key.

7. Write SQL Query implement Primary key in while defining table?

Create table Student
(
Student_ID int not null,
Student_Name Varchar2,
Student_Last_Name Varchar2,
Primary key( Student_ID)
);

8. Write SQL Query to Implement Primary Key after defining table?

ALTER TABLE Student
ADD CONSTRAINT PK_Student Primary Key(Student_ID);

9. Explain Foreign Key?

Ans: Suppose if we want to combine or link together two tables foreign key is used, Foreign key is also called as Referencing key. It is a field or collection of fields in one table that refers to the PRIMARY KEY of another table.

Foreign key in a columns or set of columns must match to the value of Primary Key of the other table.

10. Write SQL Query to Implement Foreign key on table?

Table- Student

Create table Student
(
Student_ID int not null,
Student_Name Varchar2,
Registration_No int,
Student_Last_Name Varchar2,
Primary key( Student_ID)
FOREIGN KEY() REFERENCES School(Registration_No)
);

Table- Student_Details

Create table Student_Details
(
Student_ID int not null PRIMARY KEY,
Primary key( Student_ID),
Registration_No int FOREIGN KEY REFERENCES School(Registration_No),
);

11. What is Unique Key?

Ans: Use of Unique Key Constraint to Uniquely identify each records in the database. It ensures that all values in the columns are always different.

A PRIMARY KEY already has unique key constraints. It can accept only null values.

12. What is NOT NULL constraints?

In the table, Columns hold NULL values. With the help of NOT NULL constraints, Columns cannot accepts NULL values.

13. What is DEFAULT KEY?

If we want certain columns to have default values, We can achieve it by using DEFAULT KEY constraints.

The default values to added to all new records if no other values is specified.

14. Explain DEFAULT KEY with example?

Create table Student
(
Student_ID int not null,
Student_Name Varchar2,
Student_Last_Name Varchar2,
School_Name Varchar2 DEFAULT 'Delhi Public School'
);

15.What is the difference between Primary and Unique Key?

Primary KeyUnique Key
The primary key holds the Unique Values but the field of Primary Key cannot be NULL.The unique key holds the Unique Values but it can have a single NULL value field.
We can have only one Primary key in the Table.We can have more than one Unique key in the table.
By default it adds a clustered index.By default it adds a non-clustered index.

16. What is Join List types of Join ?

In SQL join Clause is used to combine or join records of two or more tables. This combination of two or more tables occurs with the help of some common attributes or columns between tables. Joining to tables is not possible if tables is not having any common attributes.

In SQL there four different types of Joins:

  • Inner Join
  • Full Join
  • Right Join
  • Left Join

17. What is Inner Join?

Inner Join

INNER JOIN– Inner join fetch or retrieves the records that are common between both the tables.

Example:-

SELECT * FROM Table_A
JOIN Table_B;
SELECT * FROM Table_A
INNER JOIN Table_B;

18. What is Full Join?

Full Join

FULL JOIN – Full Join is also called Outer Join. It fetch or retrieves the all records when there is a match in left(Table A) or right (Table B) records

Note: FULL OUTER JOIN can have return very large result sets.

Example:-

SELECT *
FROM Table_A a1
FULL JOIN Table_B b1
ON a1.col= b1.col;

19. What is Right join?

Right Join

RIGHT (OUTER) JOIN- It retrieves all the records from the right and the matched record from the left table.

Example:- 

SELECT *
FROM Table A a1
RIGHT JOIN Table b1
ON a1.col=b1.col;

20. What is Left Join?

Left Join

LEFT (OUTER) JOIN- retrieves all the records from the left and the matched records from right table.

Example:-

SELECT *
FROM Table A a1
LEFT JOIN Table Bb1
ON a.col=b1.col;

21. What is Self Join?

self join

As the name suggest, In SELF JOIN table is the joined itself. That means each record of the table is joined to its and all the other records depending upon certain conditions.

Example:-

SELECT a.Roll_No, b.Name
From Student a, Student b
WHERE a.Roll_No<b.Roll_No;

22. What is Cross Join?

Cross Join

Cross Join is the Cartesian product of the two tables that are included in the join. After Cross Join Operation table contain same numbers of as in the cross product of rows in two tables.

SELECT Stu.name, sub.subject
FROM students AS Stu
CROSS JOIN subjects AS sub;

23. What is Data Integrity?

Data Integrity defines the accuracy(that is how accurate your data is) and the consistency(data stored must no change) of data stored in a database. That means our stored data in the database must be the same, accurate, and consistent.

24. Explain ACID property?

ACID is acronym for Atomicity, Consistency, Isolation, and Durability.

  • Atomicity– If Transaction T Takes place, either Transaction T must be executed fully or terminated.
  • Consistency-The database should be consistent before and after the transaction. That means there should be no change in the database before and after the transaction.
  • Isolation– This property ensures that when multiple transactions are taking place, No transaction should interfere in other transaction execution.
  • Durability– After the successful execution of the transaction on a database, the database must remain in the same state in which it was committed.

25. What is Normalization?

Normalization is the process of reducing or minimizing dependency and redundancy of table and thus improves data integrity.

26. Why Normalization is important?

Without Normalization in SQL we may face following issues:

  • Insertion Anomaly– It occurs when we cannot insert data into the table without the presence of another attribute.
  • Deletion Anomaly– It occurs when certain attributes are lost because of the deletion of other attributes.
  • Update Anomaly– The data inconsistency that causes data redundancy and partial update.

27. Enlist Normalization Types?

The types of Normalization are:

  • 1st Normal Form(1NF)
  • 2nd Normal Form(2NF)
  • 3rd Normal Form(3NF)

28. What is 1st Normal Form?

1st Normal Form should remove all the duplicates Attributes/columns from the table. That means it must maintain atomicity.

29. What is 2nd Normal Form?

Data should in 1st Normal form, In 2NF we place subsets of data in separate table and thus creating relationship between the tables using primary keys.

30. What is 3rd Normal Form?

Data should be in 2nd Normal Form In this we remove all the attributes columns that are not dependent on primary key constraints.

31.What is Index, Enlist its Types?

Just like the index page of our book in which all the contents in a proper format. DBMS also maintains Index which is a high tuning method that helps in faster retrieval of records from the table. An index creates an entry for each value added to the table hence this is how we achieve faster access data.

Types of Index:

  • Unique Index
  • Clustered Index
  • Non Clustered Index

32. Explain Unique Index?

In this type of Indexing, Index does not allow the fields to have duplicate values. That means here we are ensuring data integrity by ensuring that no two rows of the data in the table have identical key values.

For example: every chapter in our book are having different page number, this what unique index is.

Example:-

CREATE UNIQUE INDEX Index1
ON Employeed (Emp_no);

33. What is Clustered Index?

Clustered Index are the indexes whose ordering of rows corresponds to the rows in the index. That means only we can have only one Clustered Index on a given table.

For example- the book contains chapters and every chapter has its subtopics, This chapter can be assumed as a clustered index.

Suppose if we index book again Chapter wise, Now we can accessing topics become more faster.

34. What is Non-Clustered Index?

This type of Index does not alter the physical order of the table and it maintains logical order of the data. At most each table can have 999 Non Clustered Indexes.

35. What is the difference between clustered and non clustered Index?

Clustered IndexNon-Clustered Index
Clustered index modifies the the way in which records are stored in database based on indexed column.Non-Clustered Index creates a separate entry in the table that references the original table.
They are more fast than Non-clustered Index.They are comparatively slow.
In SQL a single table can have only one Clustered Index.Whereas SQL can have up to 999 Non-Clustered Index in single table.

36. What is a View?

View In SQL

A View in SQL is the Virtual Table based on our requirement and the result set of SQL Statements.

Suppose if there are two tables lets say Table A and Table B and we want some data of Table A and some data of Table B should be stored and we do not want end-user to see our actual Table A and Table B in such cases we can make just a view of these two tables and show it to end-user instead of showing them entire data.

37. What is Cursor in SQL?

A database Cursor is control aspects that allow us to traverse the row or the records of the table. This can be viewed as pointer in the rows or row sets. Cursor is very useful because we can retrieve, add, and remove records from the database.

38. What are Triggers?

A Database trigger is a code or a programs that is automatically executed with response to some event on the table or view. It is used to maintain Data Integrity.

Consider the example of mouse trap, As soon as mouse touch the trap it gets lock in it. same is with triggers whenever we want something happen we call triggers.

39. What is Stored Procedure?

Stored Procedure is a function consists of many SQL statements to access the database System. SQL statements are written into a stored procedure and we can execute these stored procedures whenever and wherever required.

40. What is a relationship and what are its types?

We define database relationship as the connection between the tables of the database. the various types of relationships are:

  • One to One relationship.
  • One to Many relationship.
  • Many to One relationship.
  • Many to Many relationship.

41. What is Query in SQL?

A Query is the request data or information from database table or combination of tables. the most important and common functions of queries is to retrieve the data stored in respective databases.

SELECT * FROM emp;

42. What is Sub Query in SQL?

A Sub Query sis define as Query inside another Query. Subqueries are always executed first and their result are always passed to the outer query or main query. We write it under SELECT UPDATE or within another query.

43. What are the different types of the Sub Queries?

There are two types of Sub Queries namely Correlated and Non-Correlated.

Correlated Query-These are the Queries which select the data from a table referenced in the outer query. It is not an independent query as it refers to another table and refers to the columns in a table.

Non- Correlated Query– This Query is an independent query where the output of sub query is given to the main query.

44. What are the different types of operators available in SQL?

There are namely three types of operators available in SQL:-

  • Arithmetic Operators.
  • Logical Operators.
  • Comparison Operators.

45. What is Clause in SQL?

SQL clause helps to limit the result set of query after execution by implementing certain conditions. It help to filter the rows from the entire set of records.

For Example- WHERE, HAVING clause.

46. What is difference between Having clause and where clause?

Having Clause Where Clause
Having clause can be used only with SELECT Statement.Where clause is used when we want to implement some conditions in our query.
It is usually used with a group by clause.whenever Group by clause is not used HAVING clause behave likes WHERE clause.

47. what is the difference between “BETWEEN” and “IN” condition operators?

BETWEEN operator is used for displaying rows, based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.

Example of BETWEEN:

SELECT * FROM STUDENTS WHERE ROLL_NO BETWEEN 10 AND 50;

Example of IN:

SELECT * FROM STUDENTS WHERE ROLL_NO IN (8,15,25);

48. What is the use of SQL functions ?

Uses of SQL functions are:

  • To perform some calculations on the data.
  • Modify individual data items.
  • Manipulate the output.
  • Convert the data types.

49. How we can insert NULL values in a columns while inserting data?

We can insert NULL values in the following ways:

  1. Implicitly by omitting column from column list.
  2. Explicitly by specifying NULL keyword in the Values clause.

50. What is Auto Increment in SQL?

Auto Increment keyword allows the users to create a unique number to get whenever we add a new record into the table.

This keyword is usually required when Primary Key is used.

Latest Post

Conclusion:

If you wants me to add more question, you can simply mail us at :

admin@noteshacker.com or you can also comment questions in comment section

Leave a Comment