SQL is considered to be the most important database management language in today’s time. Whether you want to work in an IT company or are choosing any career related to data, knowledge of SQL is very important for you. Keeping this in mind, we have prepared Top SQL Interview Questions and Answers for you. In this article, you will find questions related to SQL which are often asked in interviews and knowing the correct answers to them will be very beneficial for you.
By reading these questions and answers, not only will your understanding of SQL be strengthened but you will also be able to answer with confidence in the interview. The answer to every question is given in simple language and in detail so that you can easily understand and remember it. The demand for SQL is increasing in every sector today, so its preparation can give you good job prospects.
This article is specially designed for those candidates who are preparing for SQL job interview. So come, strengthen your preparation by reading these questions and answers and move towards career success.
1: What is SQL?
SQL means Structured Query Language. It is a basic language for relational database management system. And it is especially useful in handling organized data, in which the relationship is established between different data tables.
2: What is DBMS?
DBMS means Database management system. It is a system software, which is used to create a database, store data in it, use, change, etc.
DBMS works as an interface between the database and its end-users or application software, so that our data remains well organized, and at the same time we can easily access that data.
3: What is RDBMS?
RDBMS means Relational database management system. The important difference here compared to DBMS is that, RDBMS stores data in the form of tables, and establishes a relation in those tables with the help of common columns present in those tables.
Most modern database management systems used today such as Microsoft SQL Server, Oracle, MySQL, IBM DB2 and Amazon Redshift, etc. are based on RDBMS.
4: What is a database?
A database is an organized collection of data, which is stored or retrieved digitally in a remote or local computer system. A database can be quite large and complex, and such databases are developed using good design and modeling.
5: What is the syntax for creating a database in SQL?
The syntax for creating a new database in SQL is –
CREATE DATABASE DATABASE_NAME;
6: What is the difference between SQL and MySQL?
The SQL language is used to retrieve and manipulate data from a ready database. But in contrast, MySQL is also a relational database management system like Oracle, IBM DB2, SQL server, etc., which is used to manage SQL databases.
7: What are tables and fields?
Tables are an organized collection of data, which is stored in the form of rows and columns. And columns can be classified in any form, vertical or horizontal. Also, these columns present in a database table are also called fields, and rows are called records.
8: What is the syntax for creating a table in SQL?
The syntax for creating a new table in SQL is –
CREATE TABLE TABLE_NAME(
COLUMN NAME_1 type,
COLUMN NAME_2 type,
…………………………..
COLUMN NAME_n type,
COLUMN NAMEn type ) ;
9: What are Constraints in SQL?
Constraints are used to show the rules related to the data present in a database. And it can be applied to any data field during the creation of the SQL table or later by using the ALTER TABLE command.
10: What are some examples of Constraints?
Some examples of Constraints are –
NOT NULL – It prevents any NULL value from being inserted in the column.
DEFAULT – If no value is given for the field, then it automatically puts a default value in it.
UNIQUE – It ensures a unique value in every field.
CHECK – It ensures whether all the values entered in a field meet the specified conditions or not.
INDEX – It indexes the field providing faster search of a record. etc.
11: What is Primary Key?
It is a constraint, which gives a unique identity to every row present in a table. The data in each row should be different from each other, and they should not be NOT NULL or empty. A table can have only one primary key, which can include one or more fields or columns.
12: What is a Foreign Key?
The foreign key in a table is the primary key in another table, which forms a relation between these two tables, which helps in joining these two tables. It can be formed from one or more fields, but a foreign key can be NOT NULL or empty.
The table in which the foreign key is present is called the child table, and the table in which the primary key related to it is present is called the parent table.
13: What is Join?
Join is a clause in SQL, which works to join two or more tables with the help of related or common columns present in them.
14: What is the syntax of Join in SQL?
The syntax of join in SQL is –
SELECT COLUMNS
FROM TABLE 1 JOIN TABLE 2
ON TABLE1.column name = TABLE2.column name ;
15: How many types of join are there?
There are four types of join in SQL, which are –
INNER JOIN – It takes the common values present in both the tables.
LEFT (OUTER) JOIN – It takes all the data present in the left table and only the common matching data present in the right table.
RIGHT (OUTER) JOIN – It takes all the data present in the right table and only the common matching data present in the left table.
FULL (OUTER) JOIN – It takes all the data present in both the tables, whether there is a match or not.
16: What is an index and what is its syntax?
Index is a type of data structure in a database, which helps in quickly searching and viewing the data present in the columns of the table. It also greatly increases the speed of retrieving data from a database. The syntax for writing an index is –
CREATE INDEX index_name
ON table_name (column_1, column_2);
17: What is a query in SQL?
A query is a rule, with the help of which useful data is extracted from a large database. Queries can be of two types, such as select query or action query.
(Select query)
SELECT name, class
FROM myDb.students
WHERE student_id = 1;
(Action query)
UPDATE myDb.students
SET name = ‘sam’, class = ‘5’
WHERE student_id = 1;
18 : What are some important commands of SQL?
What are some important commands of SQL –
Select – It is used to retrieve data from the database.
Create database – It is used to create a new database.
Update – It is used to change the data present in the database.
Alter database – It is used to change the structure of a database.
Create table – It is used to create a new table in the database.
Drop table – It is used to delete a table from the database.
Delete – It is used to delete the data present in the database.
Insert into – It is used to insert new data into a table. etc.
19 : What is order by in SQL?
It is used to get the data present in a database in ascending or decreasing order, its syntax is –
SELECT COLUMN1, COLUMN2, COLUMN3, ……COLUMNn
FROM TABLE_name
ORDER BY column1, column2…..
ASC/ DESC ;
Generally, when order by is used, it gets the data in ascending order. But if we want to get it in descending order, then we have to use (DESC) keyword at the end.
20: What is group by in SQL?
Group by statement is used to join together those rows which have the same values, such as all the customers living in a country. And mostly it is used with functions like (max, min, ave, sum). Its syntax is –
SELECT COLUMN1, COLUMN2, COLUMN3, ……COLUMNn
FROM TABLE_name
WHERE condition
GROUP BY column1, column2…..
ORDER BY column1, column2….. ;
21: What is UNION and UNION ALL command?
Union is used to join two or more “select” commands together. It has some important rules, such as –
The number of columns in all the SELECT commands used in it should be the same.
Along with this, the type of its columns i.e. datatype should also be the same.
And the order of the columns in all the select statements should also be the same.
The syntax of Union command is –
SELECT column(names) FROM table_1
UNION
SELECT column(names) FROM table_2 ;
UNION takes only distinct values, for all values including duplicate values, UNION ALL command is used. Its syntax is –
SELECT column(names) FROM table_1
UNION ALL
SELECT column(names) FROM table_2 ;
22 : What is Alias in SQL?
Alias is used to give a permanent or temporary name to a database table or column. Generally, it is used to make the names of columns in a database table more clear. Its syntax is –
SELECT Column_name AS alias_name
FROM
Table_name AS alias_name ;
For example –
SELECT emp_name AS “Employee”,
Emp_add AS “address”
FROM employee_details AS Employees
23 : What is Normalization in SQL? (What is normalization in sql in hindi)
Normalization represents the way to organize and manage data efficiently in a database. It includes creating database tables, establishing relationships between them and defining rules for those relationships. And based on these rules, the database is protected from inconsistency and redundancy, so that the database can be made flexible.
24: What is Denormalization in SQL? (What is denormalization in sql in hindi)
This is the exact opposite of normalization, where a normalized schema is converted into a common schema, in which unnecessary information can also be kept.
25: How many types of Normalization are there?
There are six types of Normalization, which are –
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3 NF)
Boyce Codd Normal Form or Fourth Normal Form (BCNF or 4NF)
Fifth Normal Form (5NF)
Sixth Normal Form (6NF)
26: What is the function of Min(), Max(), and Ave() function?
Min() – It is used to get the smallest value present in the column of a database table.
Max() – It is used to get the largest value present in the column of a database table.
Ave() – It is used to find the average of all the values present in the column of a database table.
27: What is the IN operator in SQL?
The IN operator is used to use two or more conditions in the Where clause. For example –
Select *
From customers_table
Where country IN (‘Japan’ , ‘Taiwan’) ;
28: What is the function of Between operator in SQL?
The Between operator is used to get the data present in a specified range. And this data can be of any type like text, number, date, etc. For example –
SELECT *
FROM Products
WHERE Price BETWEEN 10 AND 20 ;
29: What are AND, OR and NOT operators in SQL?
AND – It takes data when all the conditions given in it are true.
OR – It takes data when any one of the conditions given in it is true.
NO – It takes data when none of the conditions given in it is true.
30: How can AND and OR operators be used together?
We can also use AND and OR operators together in a single query, for example –
SELECT *
FROM Customers
WHERE country = ‘India’
AND (city = ‘Delhi’ OR ‘Kolkata’) ;
The above query will take every data present in the Customers table, in which the country is ‘India’ and the city is either ‘Delhi’i or ‘Kolkata’.
31: What is SELECT statement?
SELECT statement is used to get a data set present in the database. The data obtained is stored in the form of a table, which is also called result-set. Its syntax is –
SELECT Column_names
From database_name
Where condition ;
32: How to get all the data of a table at once with the help of SELECT?
To get all the data of a table at once, the (*) symbol is used, for example –
SELECT *
FROM myDB.students;
With the above query, all the data present in the “myDB.students” table will be selected at once and displayed on our screen.
33: What are some common clauses used with SELECT?
Some common clauses used with SELECT are –
WHERE – It is used to filter the data according to our requirement.
ORDER BY – It is used to show the obtained data set in ascending or descending order.
GROUP BY – It is used to group similar data together.
HAVING – It is used to filter the data along with “Group By”. This is different from the “Where” clause, because “Where” cannot be used with aggregate functions.
34: What are Entities and Relationships?
An entity can be any object in the real world, which can be easily identified. For example, in a college database, students, professors, workers, departments and projects can be considered as an entity. And each entity has some associated attributes, which give it an identity.
On the other hand, a relationship shows the relationship between these entities. For example – the employee data table in a company database can be linked to the salary table in the same database.
35: How many types of relationships are there in SQL?
There are four types of relationships in SQL, which are –
One-to-One – It shows the relationship between two tables, where each record in one table is linked to at most one record in the other table.
One-to-Many & Many-to-One – This is the most commonly used relationship where one record in one table is linked to multiple records in another table.
Many-to-Many – This is used in cases when multiple instances of both the parties are required to define a relationship.
Self-Referencing Relationships – This is used when a table needs to define a relationship with itself.
36: What is OLTP?
It stands for “Online Transaction Processing” and it is a class of software applications that support programs related to transactions.
Maintaining concurrency is a feature of OLTP systems.
These systems are usually designed for a large number of users who frequently perform small transactions.
In this, database queries are kept simple, so that the response time can be kept fast.
37: What is OLAP?
It stands for “Online Analytical Processing” and it is a class of software programs that are used for low frequency online transactions.
In this, the queries are usually quite complex, which include a lot of aggregations.
The effectiveness of an OLAP system depends highly on its response time.
And such systems are widely used for data mining, maintaining historical data, and multi-dimensional schemas.
38: What is Collation?
Collation is a set of rules that determine how data will be stored and compared. These rules also tell in which sequence the characters will be placed and sorted. And it also determines case sensitivity. There are some types of collation sensitivity –
Case sensitivity – ‘A’ and ‘a’ are considered different.
Accent sensitivity – ‘a’ and ‘á’ are also considered different.
Kana sensitivity – Japanese Kana characters Hiragana and Katakana are treated differently.
Width sensitivity – The same character represented in single-byte and double-byte is also considered different.
39: What is the difference between DELETE and TRUNCATE?
The DELETE command is used to delete a row present in the database table according to the condition given. And if no condition is given in the query, then it will delete all the rows present in the table at once. But it does not delete the space used by the table. Its syntax is –
DELETE
From table_name
Where condition ;
The same, TRUNCATE command is used to delete all the rows of the table as well as to delete the space used by the table. Its syntax is –
TRUNCATE TABLE table_name;
40: What is STORED PROCEDURE?
This is a type of SQL code, which we can save after writing, which can be used again and again later. Apart from this, we can also pass parameters in “stored procedures”, so that the procedure works according to our given parameters. Its syntax is –
CREATE PROCEDURE Procedure_name
AS
SQL statement
GO ;
And to execute this procedure, we have to use the query given below, which is –
EXEC Procedure_name c;
41: Give an example of STORED PROCEDURE?
CREATE PROCEDURE selectAllCustomer
AS
Select * from customer_table
GO;
And to execute it –
EXEC selectAllCustomer;
With the above procedure, we can get all the data present in customer_table, and for this we will not need to write select query again and again.
42 : Give an example of STORED PROCEDURE with parameters?
CREATE PROCEDURE selectAllCustomer @age varchar(30)
AS
Select * from customer_table where age >= @age
GO ;
With the above procedure, we can get all the data present in customer_table, whose age is more than 30 years.
43 : What is ACID property?
It means Atomicity, Consistency, Isolation, and Durability. These are the transaction properties of a database, which are used to guarantee data validity in case of an error or failure.
Atomicity – It ensures that the transaction is either completed completely or it is not completed. It eliminates the possibility of a transaction getting stuck in the middle.
Consistency – It ensures that all updates made in the database are valid and are done following rules and restrictions.
Isolation – It ensures the integrity of a transaction, which is visible to all other transactions.
Durability – It ensures that a committed transaction is permanently stored in the database.
44: How to create a table with the same structure as an existing table?
We can easily create the same table structure as any existing table in the database without using the “Create” command. For this, we have to use “INTO” and “Where” clause, such as –
SELECT * INTO Shop_copy
FROM Shop
WHERE 1 = 2;
In the above query, the “where” clause will be false for all conditions, due to which the structure of the table named “Shop_copy” will be created, but there will be no data in it.
45: What is NULL value in SQL?
Null means no value. That is, the field in which no value is present is called Null. However, we cannot find out Null values using any comparison operator like (=, >, <, <=, >=). For this purpose, we have to use “IS NULL” and “IS NOT NULL” operators. For example –
Select name, contact_no
From Customers
Where Address IS NULL ;
The above query will get all those records present in the “Customers” table, in which the “Address” column is Null i.e. empty.
Select name, contact_no
From Customers
Where Address IS NOT NULL ;
The above query will get all those records present in the “Customers” table, in which the “Address” column is NOT Null i.e. empty.