Structured Query Language(SQL)Interview Questions & Answer
1. What is SQL?
SQL stands for Structured Query Language. It is a programming language used for interaction with relational database management systems (RDBMS). This includes fetching, updating, inserting, and removing data from tables. It is a case-insensitive language.
2.What is MySQL?
MySQL is a DBMS developed by Oracle that implements SQL. A Database Management System (DBMS) allows users to manage and alter data. MySQL is a software that helps us create, read, update, and delete data.
3. In how many languages is SQL divided?
SQL is divided into five languages. They are as follows:
- Data Query Language(DDL)
- Data Definition Language(DDL)
- Data Manipulation Language(DML)
- Transaction Control Language(TCL)
- Data Control Language(DCL)
4. What is Data Query Language(DDL)?
It has a SELECT command used to fetch the data by columns, rows, and between a minimum of 2 tables.
- Projection– It is used to fetch the columns. Projection statements do not have conditions.
Projection Syntax
Select CN(column name)
From TN(table name);
2. Selection– It is used to fetch the rows. Selection statements have a condition.
Selection Syntax
Select CN(column name)
From TN(table name);
Where (condition);
3. Joins– Fetching the result between a minimum of 2 tables.
5. What is Data Definition Language(DDL)?
It is a language for describing data and its relationships in a database. Data Definition Language has five commands
1. Create – It is used to create our table.
Create Syntax
Create table TN
(Cn1 datatype constraints,
Cn2 datatype constraints,
Cn3 datatype constraints);
2. Alter– It helps in modification, we can add an extra column, remove a column, or rename the column.
Alter to add a column Syntax
Alter Table TN
Add column column name;
Alter to remove/delete a column Syntax
Alter Table TN
Drop column name;
Alter to rename a column Syntax
Alter Table TN
Rename column old-column_name to new_column-name
3. Drop– it will completely delete the table including the data and structure of the table.
Drop Syntax
Drop Table Table_Name;
4. Truncate– it will delete the complete data of the table, not the structure.
Truncate Syntax
Truncate Table Table_Name;
5. Rename– it will rename the Table.
Rename Syntax
Rename Table old_column_name to new_column-name
Foreign Key Syntax
Foreign Key(Table name ID) references (column name ID)
6. What is Data Manipulation Language(DML)?
It allows to add, delete, and modify data in a database.
Data Manipulation Language has three commands
1.Insert– it will insert any new column with its value.
Insert Syntax
Insert into table_name(col1,col2,col3)
values(v1,v2,v3);
Column =values
2. Update– it will update any column with its value.
Update Syntax
Update Table_name set Column_name=value
Condition <if required>;
3. Delete-it will delete row and column whichever we will select.
Delete Syntax for rows
Delete from table_name
condition<if required>;
Delete Syntax for column
Alter table table_name
Drop Column_name;
7. What is Transaction Control Language(TCL)?
It is used to handle database transactions. Transaction Control Language has three commands that will make DML temporary changes permanent. It has three commands commit, rollback, and savepoint. DML commands are temporary until they are committed.
1. Commit– It will used to make DML commands permanent. Commit means it is fixed.
Commit Syntax
Insert with commit
INSERT INTO TN(C1,C2,….)
VALUES( Value1, Value 2,…)
COMMIT;
Update with commit
UPDATE TN
Set C1=Value 1, C2= Value 2,…
WHERE Condition(optional)
COMMIT;
Delete with commit
DELETE FROM TN WHERE Condition(optional)
COMMIT;
2.Rollback– It is used to undo those transactions that are not saved yet in the database.
Rollback Syntax
First Syntax Of SavePoint and RollBack :
Insert: Insert into
table(Column1,Column2……)
values(Value1,Value2…)
SavePoint a;
Update : update Table Name set Column Name= value Condition(optional)
SavePoint b;
Delete: delete from Table Name
Condition(optional)
SavePoint c;
RollBack to a;(will be written after savepoint a if delete update not needed)
Second Syntax Of SavePoint and RollBack :
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…)
SavePoint a;
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…)
SavePoint b;
Rollback to b;
Syntax Of SavePoint and Commit:
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…) SavePoint a;
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…) SavePoint b;
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…) SavePoint c;
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…) SavePoint d;
Syntax Of SavePoint and Commit:
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…) SavePoint a;
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…) SavePoint b;
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…) SavePoint c;
Insert : Insert into
table(Column1,Column2……)
values(Value1,Value2…) SavePoint d;
Commit;
Syntax Of Commit ,SavePoint and RollBack :
Insert : Insert into table(Column1,Column2……)
values(Value1,Value2…)
SavePoint a;
Insert : Insert into table(Column1,Column2……)
values(Value1,Value2…)
SavePoint b;
Update : update Table Name set Column Name = value
Condition(optional)
SavePoint c;
Update : update Table Name set Column Name = value
Condition(optional)
SavePoint d;
Delete: delete from Table Name Condition(optional)
SavePoint e;
RollBack to d;(will be written after savepoint d)
Commit;
8. What is Data Control Language(DCL)?
It allows for the management of database access and security.
Data Control Language has two commands
1. Grant– to give access to someone’s Database.
Grant Syntax
Grant command_name on Table_name to username;
2. Revoke– to take back given access to someone’s Database.
Revoke Syntax
revoke command_name on Table_name to username;
9. What is Data integrity?
Data Integrity is used to restrict the invalid data into the database at the column level. Data Base Administrators(DBA) will create a table by making use of Data Integrity. In SQL data integrity is applicable for Data definition language(DDL) create command.
10. What is the Primary key?
The primary key is a constraint at a column level that needs to be unique and not null. The primary key will always be one. The primary key will always be present on the master table. There are two types of primary keys-
1.Candidate Key– Candidate Key are those columns eligible to become the primary key.
2.Alternate Key– Alternate Key are those columns not selected as primary key.
Primary key= Candidate Key-Alternate Key.
11. What is the Foreign key?
The foreign key is a constraint at a column level that will be used to create the relationship between the two tables. The foreign key can be duplicate or null. The foreign key is also called a Referential Integrity Constraint.
12. How to decide which table has primary and foreign keys?
The columns between the two tables should be the same.
The primary key will always be present on the master table.
The foreign key will always be present on the child’s table.
In the below two tables, the Employee Table and the Department Table.
The primary key is a Department Table as the dept_no column has no duplicate values and the Foreign key is the Employee Table as the dept_no column has duplicate values. The child table can be many however master table will always be one.
Employee Table
Department Table
13. What is the formula to calculate the datatype range?
Formula to calculate the range of values is –2^(n-1) to (2^(n-1)-1)
byte- (-128 to 128)
short-(-32768 to 32767)
int- (-2,147,483,648 to 2,147,483,647)
long- (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
14. What is Normalization?
Normalization is the database design technique that organizes the table to reduce the data’s redundancy and dependency.
15. What is Not Null constraint?
Not Null is a constraint at a column level that cannot be left blank. It is a mandatory field to update.
16. Which aggregate functions in SQL are used to find the maximum number?
Max used to find the maximum number like the maximum salary.
Syntax: select max(CN) from TN;
17. Aggregate functions should not be used in the ‘where’ clause we can use them in the ‘have’ clause.
True aggregate functions should not be used in the ‘where’ clause we can use them in the ‘have’ clause.
18. Types of aggregate functions and their syntaxes
| Max | Min | Avg | Sum | Count |
| It is used to find the maximum number like the maximum salary. | It is used to find the minimum number like the minimum salary. | It is used to find the average of values like the average salary. | It is used to find the sum of values like the total salary. | It is used to find the count of values like the count number of rows in any table. |
| Syntax: select max(CN) from TN; |
Syntax: select min(CN) from TN; |
Syntax select avg(CN) from TN; |
Syntax select sum(CN) from TN; |
Syntax select count(CN) from TN; |
19. Which concept of SQL is the command ‘order by’ used?
Sorting is a concept of SQL where the command ‘order by’ is used.
20. What is the Syntax for Sorting in ascending order?
Select CN(column name)
From TN(table name)
Order by CN;
21. Sorting in ascending order is done explicitly or implicitly.
Sorting in ascending order is done implicitly.
22. What is the Syntax for Sorting in descending order?
Select CN(column name)
From TN(table name)
Order by CN desc;
23. Sorting in descending order is done explicitly or implicitly.
Sorting in descending order is done Explicitly.
24. Display those dept_no who have more than one clerk in it.
Employee Table
Department Table
Query:
select dept_no
from employee
where job=’clerk’
group by dept_no
having count(*) >1;
Output Screen:
25. What in SQL allows us to fetch data in the group?
Grouping in SQL allows us to fetch data in the group.
26. How to generate Unique results?
We can get unique results in SQL by
Select distinct( ColumnName)
from Table Name;
27. Can we concat in SQL?
Yes, we can do concatenation in SQL by using the Concat command.
Select Concat (Column1, Column2…)
From Table Name;
28. Differences between normalization and denormalization
| Normalization | Denormalization |
| Normalization is the database design technique that organizes the table to reduce the data’s redundancy and dependency. | Denormalization redundancy is added instead of reduction or elimination of redundancy. |
| Data integrity is maintained in normalization. | Data integrity is not maintained in denormalization. |
| In normalization table should not have a transitive functional dependency, meaning it should not have multiple things dependent on each other in a particular table. There must be different tables to store data accordingly to create a pattern. | The denormalization table has a transitive functional dependency, meaning it has multiple things dependent on each other in a particular table. There are no different tables to store data accordingly to create a pattern. |
| The number of tables in normalization is increased. | The number of tables in denormalization is decreased. |
29. What is the first rule of normalization form?
- Each record should be unique as rows are unique.
- Each cell should consist of a single value.
- There should not be two values in a single cell as rows need to be unique
30. What is inner join?
Inner Join In SQL will fetch common data between the two tables or multiple tables. Values come in pairs and no null values exist in the inner join.
31. What is the outer join?
Outer joins are joins that return matched values and unmatched values from either or both tables.
32. Difference between right outer join, left outer join, and full outer join.
| Right Outer Join | Left Outer Join | Full Outer Join |
| Right Outer Join In SQL will fetch data that is common between the two tables and everything from the right table. Values come in pairs and there are null values in the right outer join. | Left Outer Join In SQL will fetch common data between the two tables and everything from the left table. Values come in pairs and there are null values in the left outer join. | Full Outer Join In SQL will fetch matched and unmatched records between the two tables. |
| Syntax Select t1.CN1,t2.CN2 From t1 right outer join t2 On t1.cc = t2.cc; |
Syntax Select t1.CN1,t2.CN2 From t1 left outer join t2 On t1.cc = t2.cc; |
Syntax Select t1.CN1,t2.CN2 From t1 full outer join t2 On t1.cc = t2.cc; |
33. Find out the ename and manager’s name.
Employee Table
Department Table
Query:
select A.ename, B.ename
from EMPLOYEE A, EMPLOYEE B
where A.MGR =B.EMP_NO;
Output Screen:
34. What are the three syntaxes for Grouping?
| First Syntax for Grouping | Second Syntax for Grouping | Third Syntax for Grouping |
| Select CN(column name) From TN(table name) Where condition Group by CN(column name) Having condition1; |
Select CN(column name) From TN(table name) Group by CN(column name); |
Select CN(column name) From TN(table name) Group by CN(column name) Having condition1; |
35. Explain ways of Data Integrity.
Data Integrity can be achieved in two ways
1.Constraints
There are five types of constraints we need to consider to achieve data integrity
a) Not Null– Not Null is a constraint at a column level that cannot be felt blank. It is a mandatory field to update. In the Employee table below, you can see the Ename column needs to be filled it cannot be null.
b) Data Types– Data types are a constraint at a column level that needs to be unique. It will not accept duplicate values. In the Employee table below, you can see the column name needs to be unique.
c) Primary key– The primary key is a constraint at a column level that needs to be unique and not null. The primary key will always be one. The primary key will always be present on the master table.
d) Foreign key- is a constraint at a column level that will be used to create the relationship between the two tables. The foreign key can be duplicate or null. The foreign key is also called a Referential Integrity Constraint.
e) Check Key– The check key is a constraint at a column level that will check any kind of condition written by the developer and the database administrator.
2.Datatypes
Datatypes in SQL will decide at the column level what kind of data column will have it can be name, address, number, store alphabets, True or false. In SQL every data have a datatype.
36. Display the average, minimum, and maximum salary of each dept_no.
Employee Table
Department Table
Query:
select max(salary) as highest_salary,min(salary) as minimum_salary,avg(salary) as average_salary
from employee;
Output Screen:
37. What is an aggregate function?
The aggregate function in SQL performs a calculation on multiple values and returns a single value. Aggregate functions should not be used in the ‘where’ clause we can use them in the ‘have’ clause. Aggregate function is used in the ‘select’ clause.
38. Types of aggregate functions and their syntaxes.
| Max | Min | Avg | Sum | Count |
| It is used to find the maximum number like the maximum salary. | It is used to find the minimum number like the minimum salary. | It is used to find the average of values like the average salary. | It is used to find the sum of values like the total salary. | It is used to find the count of values like the count number of rows in any table. |
| Syntax: select max(CN) from TN; |
Syntax: select min(CN) from TN; |
Syntax select avg(CN) from TN; |
Syntax select sum(CN) from TN; |
Syntax select count(CN) from TN; |
For more information, on aggregate functions, you can also read blog on :
39. Different types of operators in SQL.
| Arithmetic Operators |
Conditional Operators |
Logical Operators |
Special Operators |
| Arithmetic Operators are used for addition(+),subtraction(-), multiplication(*), division(/). |
Conditional Operators are greater than(>),smaller than(<),greater than or equals to(>=),smaller than or equals to(<=),assisgnment(=), comparison(==),not equals to(<> or !=) |
Logical operators are ‘And’, ‘Or’ |
Special operators are ‘Like’,; is’, ‘in’, and ‘between’. |
For more information, on operators, you can also read blog on :
40. What is a subquery?
A subquery can be defined as a query within another query. In other words, we can say that a Subquery is a query embedded in another SQL query’s WHERE clause of another SQL query.
41. Find details of all employees who are earning more than the least paid in dept20.
Employee Table
Department Table
Query:
select *
from EMPLOYEE
where salary >( select min(salary)
from EMPLOYEE
where dept_no=20);
Output Screen:
Query:
select employee.ename,dept.dname,dept.loc
from EMPLOYEE inner join deptc
on EMPLOYEE.dept_no=dept.DEPT_NO
where dept.loc like ‘c%’;
Output Screen:
43. How are Delete, Drop and Truncate commands different from each other?
| Delete | Drop | Tuncate | |
| Definition | It deletes the row | It deletes the table including the structure of the table. | It deletes the table but not the structure of the table. |
| Type | Data Manipulation Language | Data Definition Language | Data Control Language |
| Structure | No impact on structure | yes there is impact on structure | No impact on structure |
| Permanent/Temporary | Temporary Change | Permanent Change | Permanent Change |
| Transaction Control Language | Yes | No | No |
| Syntax | delete from TableName Condition if any; |
drop table TN; | truncate table TN; |
44. What is Alias name?
Alias name give a table,or a column in a table a temporary name.It is often used to make column names more readable. An alias only exists for the duration of that query.
45. Find out the salary and monthly salary of employees.
We can multiply columns and get the result. As in the below example we can find the yearly salary by using literals any value we are using in the query can be done by using ‘ *’ and If we want to modify something in the column name as they vanish, that’s the reason the new column is given as yearly_salary which is known as Alias name.
Employee Table
Department Table
Query:
select salary, salary*12 as monthy_salary
from employee;
Output Screen:
45. What are special operators?
Special operators are ‘Like’,; is’, ‘in’, and ‘between’.
1. like operator
The ‘like’ operator searches single characters or multicharacter searches. It is also known as wild card search operator.
a)Multicharacter search- We search by using ‘%’.
Problem Statement:
Display those employees whose names start with ‘m’.
Employee Table
Department Table
Query:
select ename
from employee
Where ename like ‘m%’;
Output Screen:
b) Single character search- We search by using ‘_’.
Problem Statement:
Display those employees whose names have exactly four characters in their name.
Employee Table
Department Table
Query:
select ename
from employee
Where ename like ‘____’;
Output Screen:
2. ls operator
The ‘Is’ operator is used to compare null values.
Employee Table
Department Table
Problem Statement:
Display those employees whose commission(comm) is null.
Query:
select ename, comm
from employee
Where comm is null;
Output Screen:
3. In operator
The ‘In’ operator displays two values of the same column name together.
Employee Table
Department Table
Problem Statement:
Display those employees who belong to dept 10 and 20.
Query:
select ename,dept_no
from employee
Where dept_no in(10,20);
Output Screen:
4. between operator
The ‘between’ operator displays a range of two values between the same column name.
Problem Statement:
Display those employees whose salary is between 1000 and 2000.
Employee Table
Department Table
Query:
select ename,salary
from employee
Where salary between 1000 and 2000;
Output Screen:
Also Read Blogs On SQL:
Popular Courses
Previous
Next
Upskill Yourself
Consult Us
Consult Us