Handling the database is one of the top most requirements in recent times, which includes the task like accessing, reading, updating and deleting the information. And because of these there seems to have excessive call for the professionals having SQL competencies. Referring to the requirement of the same, few SQL interview questions and answers are provided in this article which will help a job aspirant to prepare for the interview for various positions related to Database Management. These SQL interview questions will help such candidates to increase personal confidence and also provide a boost in the performance at the time of interview.

1. What is the difference between DELETE and TRUNCATE commands?

DELETE is a Data Manipulation Language (DML) command which is used to remove row(s) from a relation. WHERE clause can be used with DELETE for providing condition(s). If a condition is not provided, it will remove all records from a relation.

TRUNCATE command is a Data Definition Language (DDL) command and it is used to remove all rows from a relation. WHERE clause is not used with TRUNCATE.

2. What is the importance of “ON DELETE CASCADE” in SQL?

ON DELETE CASCADE automatically deletes matching rows from the child table when any record is removed from the parent table.

Example:

Consider following two tables:

CREATE TABLE Student (

  rollno int(10) NOT NULL primary key,

  name varchar(40) NOT NULL

);

CREATE TABLE Marks (

  rollno int(10) NOT NULL,

  percentage int(4) NOT NULL,

FOREIGN KEY (rollno) REFERENCES Student (rollno) ON DELETE CASCADE

);

Student:

rollno	name
1	Alice
2	Bob

Marks:

rollno	percentage
1	63
2	75

If we execute following query:

DELETE FROM Student where rollno=1;

Then record related to rollno 1 will be deleted from both Student as well as from Marks tables

3. Which are the wildcard characters that can be used with like keyword? Give examples.

% and _ wildcard characters are used with LIKE keyword in SQL.

% is used to match one or more characters and _ is used to match a single character.

Example:

Following query will fetch all students’ names starting with “s”

select name from student where name like ‘s%’

Following query will display names of all people whose age starts with digit 2 and is of 2 digits length.

select name from movies where age like `2_`

4. Sort the following keywords in descending order of the execution of an SQL query (having, order by, group by, where)

order by, having, group by, where

5. What are the conditions for a table to be in 2NF? Convert the following table into 2NF.

Student ID	Name	Phone Number
1	Alice	3456788291, 7891245672
2	Bob	3456798291, 6789876789
3	Carol	3456798678

For any table to be in 2nd normal form, following conditions should be satisfied:

1. The table must be in 1st Normal form
2. There should not be any partial dependency

To convert the given table into 2NF, we will first need to convert it into 1NF as it contains multivalued attributes.

1NF:

Student_Details

Student ID	Name	Phone Number
1	Alice	3456788291
1	Alice	7891245672
2	Bob	3456798291
2	Bob	6789876789
3	Carol	3456798678

2NF:

Student

Student ID	Name
1	Alice
2	Bob
3	Carol

Student_Phone

Student ID	Phone Number
1	3456788291
1	7891245672
2	3456798291
2	6789876789
3	3456798678

6. What happens with a view when its parent table is dropped?

If the parent table is dropped, the view will no longer remain active.

7. What is the benefit of using “default” constraint in SQL?

Default constraint is used to apply a default value to a column for which the user has not specified any value.

Example:

CREATE TABLE Person (

    ID int NOT NULL,

    Name varchar(75) NOT NULL,

    Age int,

    Country varchar(50) DEFAULT ‘INDIA‘

);

If a table is created using above columns and at the time of inserting data, if the user doesn’t insert any value in the Country field then by default ‘INDIA’ will be inserted for that record.

8. Differentiate between BETWEEN and IN operators

Between operator is used to display the rows as per the specified range while IN will display the rows based on a specific set of values.

Example:

1. SELECT * FROM employees where eid BETWEEN 1 AND 10;
2. SELECT * FROM employees where eid IN (1,7,19);

Here, the first statement will display all the details of employees with eid 1 to 10 while the second will display all the details of employees with eid 1, 7 and 19.

9. What is the use of COMMIT?

Commit command is used to save all changes made by a transaction since the last commit or rollback command

Conclusion

We’ve reached the end of the SQL interview questions. SQL’s popularity has evolved to the point that almost every recruiter prefers that not only the database manager but the programmers should also have the knowledge to incorporate storage functionality in their application. So keep learning.