All The Useful SQL Interview Questions & Answers for Experienced Professionals

Q 1. What is SQL injection?
SQL injection is a hacking technique which is widely used by black-hat hackers to steal data from your tables or databases. Let's say, if you go to a website and give in your user information and password, the hacker would add some malicious code over there such that, he can get the user information and password directly from the database. If your database contains any vital information, it is always better to keep it secure from SQL injection attacks.

Q 2. What is a trigger in SQL?
A trigger is a stored program in a database which automatically gives responses to an event of DML operations done by insert, update, or delete. In other words, is nothing but an auditor of events happening across all database tables.

Let's look at an example of a trigger:

CREATE TRIGGER bank_trans_hv_alert
    BEFORE UPDATE ON bank_account_transaction
    FOR EACH ROW
    begin
    if( abs(:new.transaction_amount)>999999)THEN
   RAISE_APPLICATION_ERROR(-20000, 'Account transaction exceeding the daily deposit on SAVINGS account.');
    end if;
    end;

Q 3. How to insert multiple rows in SQL?
To insert multiple rows in SQL we can follow the below syntax:

INSERT INTO table_name (column1, column2,column3...)
VALUES
   (value1, value2, value3…..),
   (value1, value2, value3….),
   ...
   (value1, value2, value3);
We start off by giving the keywords INSERT INTO then we give the name of the table into which we would want to insert the values. We will follow it up with the list of the columns, for which we would have to add the values. Then we will give in the VALUES keyword and finally, we will give the list of values.

Here is an example of the same:

INSERT INTO employees (
   name,
   age,
   salary)
VALUES
   (
       'Sam',
       21,
      75000
   ),
   (
       ' 'Matt',
       32,
      85000    ),

   (
       'Bob',
       26,
      90000
   );
In the above example, we are inserting multiple records into the table called employees.

Q 4. How to find the nth highest salary in SQL?
This is how we can find the nth highest salary in SQL SERVER using TOP keyword:

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM #Employee ORDER BY salary DESC ) AS temp ORDER BY salary

This is how we can find the nth highest salary in MYSQL using LIMIT keyword:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1

Q 5. How to copy table in SQL?
We can use the SELECT INTO statement to copy data from one table to another. Either we can copy all the data or only some specific columns.

This is how we can copy all the columns into a new table:

SELECT *
INTO newtable
FROM oldtable
WHERE condition;

If we want to copy only some specific columns, we can do it this way:

SELECT column1, column2, column3, ...
INTO newtable 
FROM oldtable
WHERE condition;

Q 6. How to add a new column in SQL?
We can add a new column in SQL with the help of alter command:

ALTER TABLE employees ADD COLUMN contact INT(10);

This command helps us to add a new column named as contact in the employees table.

Q 7. How to use LIKE in SQL?
The LIKE operator checks if an attribute value matches a given string pattern. Here is an example of LIKE operator

SELECT * FROM employees WHERE first_name like 'Steven'; 

With this command, we will be able to extract all the records where the first name is like 'Steven'.

Q 8. If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures?
Yes, SQL server drops all related objects, which exists inside a table like constraints, indexex, columns, defaults etc. But dropping a table will not drop views and sorted procedures as they exist outside the table. 

Q 9. Can we disable a trigger? If yes, How?
Yes, we can disable a single trigger on the database by using 'DISABLE TRIGGER triggerName ON<> We also have an option to disable all the trigger by using, 'DISABLE Trigger ALL ON ALL SERVER'

Q 10. What is a Live Lock?
A live lock is one where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keep interferring. A live lock also occurs when read transactions create a table or page. 

Q 11. How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers- To display even numbers-. Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0 To display odd numbers-. Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1

Q 12. Define COMMIT and give an example.
When a COMMIT is uded in a transaction all chnages made in the transaction are written into the database permanently. Example: BEGIN TRANSACTION; DELETE FROM HR.JobCandidate WHERE JobCandidateID = 20; COMMIT TRANSACTION; The above example deletes a job candidate in a SQL server.

Q 13. Can you join table by itself? 
A table can be joined to itself using self join, when you want to create a result set that joins records in a table with other records in the same table.

Q 14. Explain Equi join with example.
When two or more tables has been joined using equal to operator then this category is called as equi join. Just we need to concentrate on condition is equal to(=) between the columns in the table. Example: Select a.Employee_name,b.Department_name from Employee a,Employee b where a.Department_ID=b.Department_ID

Q 15. How do we avoid getting duplicate entries in a query?
The SELECT DISTINCT is used to get distinct data from tables using a query. The below SQL query selects only the DISTINCT values from the 'Country' column in the 'Customers' table: SELECT DISTINCT Country FROM Customers;

Q 16. How can you create an empty table from an existing table?
Lets take an example: Select * into studentcopy from student where 1=2 Here, we are copying student table to another table with the same structure with no rows copied.

Q 17. Write a Query to display odd records from student table.
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS RowID FROM student) WHERE row_id %2!=0

Q 18. Explain Non Equi Join with example.

1. When two or more tables are joining without equal to condition then that join is known as Non Equi Join. Any operator can be used here that is <>,!=,<,>,Between. Example: Select b.Department_ID,b.Department_name from Employee a,Department b where a.Department_id <> b.Department_ID;

Q 19. How can you delete duplicate records in a table with no primary key?
By using the SET ROWCOUNT command. It limits the number of records affected by a command. Let's take an example, if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0

Q 20. Difference between NVL and NVL2 functions?
Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null. With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1. With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

Q 21. What is the difference between clustered and non-clustered indexes?
1. Clustered indexes can be read rapidly rather than non-clustered indexes. 

2. Clustered indexes store data physically in the table or view whereas, non-clustered indexes do not store data in the table as it has separate structure from the data row.

Q 22. What does this query says? GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION];
The given syntax indicates that the user can grant access to another user too.

Q 23. Where is MyISAM table stored?
Each MyISAM table is stored on disk in three files. 

1. The '.frm' file stores the table definition. 
2. The data file has a '.MYD' (MYData) extension. 
3. The index file has a '.MYI' (MYIndex) extension. 

Q 24. What does myisamchk do?
It compresses the MyISAM tables, which reduces their disk or memory usage.

Q 25. What is ISAM?
ISAM is abbreviated as Indexed Sequential Access Method. It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

Q 26. What is Database White box testing?
White box testing includes: Database Consistency and ACID properties Database triggers and logical views Decision Coverage, Condition Coverage, and Statement Coverage Database Tables, Data Model, and Database Schema Referential integrity rules.

Q 27. What are the different types of SQL sandbox?
There are 3 different types of SQL sandbox: 

1. Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory as well as cannot create files.
2. External Access Sandbox: Users can access files without having the right to manipulate the memory allocation.
3. Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

Q 28. What is Database Black Box Testing?
This testing involves 1. Data Mapping 2. Data stored and retrieved 3. Use of Black Box testing techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA).

Q 29. Explain Right Outer Join with Example?
This join is usable, when user wants all the records from Right table (Second table) and only equal or matching records from First or left table. The unmatched records are considered as null records. Example: Select t1.col1,t2.col2….t 'n'col 'n.'. from table1 t1,table2 t2 where t1.col(+)=t2.col;

Q 30. What is a Subquery?
A SubQuery is a SQL query nested into a larger query. Example: SELECT employeeID, firstName, lastName FROM employees WHERE departmentID IN (SELECT departmentID FROM departments WHERE locationID = 2000) ORDER BY firstName, lastName;