Database Interview Questions
1. What are the different type of SQL’s statements ?
1. DDL – Data Definition Language
DDL is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.
2. DML – Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. The Select statement is considered as a limited version of the DML, since it can’t change the data in the database. But it can perform operations on data retrieved from the DBMS, before the results are returned to the calling function.
3. DCL – Data Control Language
DCL is used to control the visibility of data like granting database access and set privileges to create tables, etc. Example – Grant, Revoke access permission to the user to access data in the database.
2. What are properties of a transaction?
Expect this SQL Interview Questions as a part of an any interview, irrespective of your experience. Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
2. Consistency
The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.
3. Isolation
Every transaction should operate as if it is the only transaction in the system.
4. Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.
3. What is difference between row_number, rank and dense_rank in SQL Server, Oracle.
rank()
The rank() function will assign the same rank to same values i.e. which are not distinguishable by ORDER BY. Also, the next different rank will not start from immediately next number but there will be gap i.e. if 4th and 5th employee have the same salary then they will have same rank 4, and 6th employee which has different salary will have new rank 6.
Here is the example to clarify the point:
select e.*, rank() over (order by salary desc) rank from #Employee e
Result:
name salary rank
Jackob 7000 1
Peter 5000 2
John 4000 3
Shane 3000 4
Rick 3000 4
Sid 1000 6
You can see that both Shane and Rick has got the same rank 4th, but the Sid got the rank 6th, instead of 5 because it keep original ordering.
dense_rank()
The dense_rank function is similar to rank() window function i.e. same values will be assigned the same rank, but the next different value will have rank which is just one more than the previous rank, i.e. if 4th and 5th employee has the same salary then they will have same rank but 6th employee, which has different salary will have rank 5, unlike rank 6 as is the case with rank()function. There will be no gap on ranking in case of dense_rank() as shown in the following example:
select e.*, dense_rank() over (order by salary desc) dense_rank from #Employee e
Result:
name salary dense_rank
Jackob 7000 1
Peter 5000 2
John 4000 3
Shane 3000 4
Rick 3000 4
Sid 1000 5
You can see that both Shane and Rick has same ranking 4th, but Sid now has 5th rank which is differnt than 6th in earlier example when we used the rank() function
The row_number gives continuous numbers, while rank and dense_rank give the same rank for duplicates, but the next number in rank is as per continuous order so you will see a jump but in dense_rank doesn’t have any gap in rankings.
4. What is difference between rownum and row_number()?
ROWNUM will not sort the column based on ROWNUM if we add order by clause in select where in row_number() will give appropriate row numbers to the records.
ROWNUM is a “pseudocolumn” that assigns a number to each row returned by a query:
select rownum, ename, deptno from emp;
Result:
ROWNUM ENAME DEPTNO
———- ———- ———-
1 SMITH 99
2 ALLEN 30
3 WARD 30
4 JONES 20
5 MARTIN 30
6 BLAKE 30
7 CLARK 10
8 SCOTT 20
9 KING 10
10 TURNER 30
11 FORD 20
12 MILLER 10
ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows:
SQL> select ename, deptno, row_number() over (partition by deptno order by ename) rn from emp;
ENAME DEPTNO RN
———- ———- ———-
CLARK 10 1
KING 10 2
MILLER 10 3
FORD 20 1
JONES 20 2
SCOTT 20 3
ALLEN 30 1
BLAKE 30 2
MARTIN 30 3
TURNER 30 4
WARD 30 5
SMITH 99 1
5. Difference in between where clause and having?
1) Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query. For example following query, which involve WHERE clause will work but other which uses HAVING clause will not work :
update DEPARTMENT set DEPT_NAME=”NewSales” WHERE DEPT_ID=1 ; // works fine
update DEPARTMENT set DEPT_NAME=”NewSales” HAVING DEPT_ID=1 ; // error
Incorrect syntax near the keyword ‘HAVING’.: update DEPARTMENT set DEPT_NAME=’NewSales’ HAVING DEPT_ID=1
2) WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
3) One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.
4) When WHERE and HAVING clause are used together in a SELECT query with aggregate function, WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified.
6. Write a query to find the Nth largest salary
SELECT * FROM
(select e.*,row_number() over (order by e.salary desc) row_number1 from sampledb e)
where row_number1>3;
we can use desk_rank() or rank() depending on the requirement.
7. Write a query to find employees with same salary
SELECT Name FROM table1 WHERE Salary IN
( SELECT Salary FROM table1 GROUP BY Salary HAVING COUNT(*) > 1 )
8. Write a query to delete employees with same salary
DELETE FROM EmployeeSalary WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary GROUP BY Project, Salary HAVING COUNT(*) > 1));
9. Drop vs Truncate vs delete
DROP
The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DROP
The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
Since TRUNCATE is a DDL operation, it’s automatically get committed, on the other hand, DELETE is not auto commit.
10. What are different Join types available in database?
1. INNER JOIN
The Oracle INNER JOIN would return the records where table1 and table2 intersect
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Old Sytax –
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
2. LEFT OUTER JOIN
The Oracle LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
3. RIGHT OUTER JOIN
The Oracle Right OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table1.
The Oracle Right OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table1.
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
4. FULL OUTER JOIN
The Oracle FULL OUTER JOIN would return the all records from table1 and table2.
The Oracle FULL OUTER JOIN would return the all records from table1 and table2.
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
11. What are Set Operators?
1. UNION
The following statement combines the results with the UNION operator, which eliminates duplicate selected rows.
select empno,ename,sal from emp
UNION
select empno,ename,salary from oldemp
6. UNION ALL
The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:
select empno,ename from emp
union all
select empno,ename from oldemp;
7. INTERSECT
The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:
SELECT empno FROM emp
INTERSECT
SELECT empno FROM oldemp;
8. MINUS
The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:
SELECT empno FROM emp
MINUS
SELECT empno FROM oldemp;
12. Difference between View vs Materialized View in database?
Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :
1) The first difference between View and materialized view is that In Views query result is not stored in the disk or database but Materialized view allow to store the query result in disk or table.
2) Another difference between View vs materialized view is that, when we create a view using any table, rowid of view is same as the original table but in case of Materialized view rowid is different. See Oracle PL/SQL Programming: Covers Versions Through Oracle Database 12c for more details on materialized view in Oracle.
3) One more difference between View and materialized view in the database is that In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) Performance of View is less than Materialized view.
5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table
6) Last difference between View vs Materialized View is that In case of Materialized view we need an extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in the database.
13. Write a syntax for Function and Procedure?
create or replace function add_numbers(firstNum IN number, secondNum In number)
return number
is
sumNum number;
begin
sumNum := firstNum + secondNum;
DBMS_OUTPUT.put_line(‘Sum is : ‘ || sumNum);
return sumNum;
end add_numbers;
Ways to execute functions
1. From dual table
1. From dual table
SELECT add_numbers(5, 2) FROM DUAL;
2. PL SQL block
SET serveroutput ON
DECLARE
firstNum number := 10;
secondNum number := 20;
BEGIN
DBMS_OUTPUT.put_line(‘In: ‘ || firstNum);
retval := add_numbers(firstNum, secondNum);
DBMS_OUTPUT.put_line(‘Out: ‘ || secondNum);
DBMS_OUTPUT.put_line(‘Return: ‘ || retval);
END;
Procedure
create or replace procedure add_numbersProc(firstNum IN number, secondNum In number)
is
sumNum number;
begin
sumNum := firstNum + secondNum;
DBMS_OUTPUT.put_line(‘Sum is : ‘ || sumNum);
end add_numbersProc;
Ways to execute procedures
SELECT add_ numbersProc (5, 2) FROM DUAL; // compilation error for procedure
SET serveroutput ON
execute add_numbersProc(5, 2) ; // we can use execute for procedure
14. What is the difference between a function and a procedure?
1. A Function must return a value but in Stored Procedures it is optional: a procedure can return 0 or n values.
2. Functions can have only input parameters for it, whereas procedures can have input/output parameters.
3. For a Function it is mandatory to take one input parameter, but a Stored Procedure may take 0 to n input parameters.
4. Functions can be called from a Procedure whereas Procedures cannot be called from a Function.
5. Exceptions can be handled by try-catch blocks in a Procedure, whereas a try-catch block cannot be used in a Function.
6. We can go for Transaction Management in a Procedure, whereas in a Function we can’t.
1. A Function must return a value but in Stored Procedures it is optional: a procedure can return 0 or n values.
2. Functions can have only input parameters for it, whereas procedures can have input/output parameters.
3. For a Function it is mandatory to take one input parameter, but a Stored Procedure may take 0 to n input parameters.
4. Functions can be called from a Procedure whereas Procedures cannot be called from a Function.
5. Exceptions can be handled by try-catch blocks in a Procedure, whereas a try-catch block cannot be used in a Function.
6. We can go for Transaction Management in a Procedure, whereas in a Function we can’t.
15. What is difference in OUT and IN OUT Params?
create or replace function add_numbers(firstNum IN number, secondNum In out number, result out)
return number
is
sumNum number;
begin
sumNum := firstNum + secondNum;
DBMS_OUTPUT.put_line(‘Sum is : ‘ || sumNum);
return sumNum;
end add_numbers;
out parameter OUT param will only return the result
in out parameter will take an input update that variable and return the result.
in out parameter will take an input update that variable and return the result.
Let’s create a procedure which gets the name of the employee when the employee id is passed.
CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT NUMBER)
IS
BEGIN
SELECT first_name INTO emp_name
FROM emp_tbl WHERE empID = id;
END;
We can call the procedure ‘emp_name’ in this way from a PL/SQL Block.
DECLARE
empName varchar(20);
CURSOR id_cur SELECT id FROM emp_ids;
BEGIN
FOR emp_rec in id_cur
LOOP
emp_name(emp_rec.id, empName);
dbms_output.putline(‘The employee ‘ || empName || ‘ has id ‘ || emp-rec.id);
END LOOP;
END;
16. What is difference in clustered and non clustered Index?
1) One of the main difference between clustered and non clustered index in SQL Server is that, one table can only have one clustered Index but It can have many non clustered index, approximately 250.
2) One more difference between them is that, clustered index contains data i..e rows in there leaf node, as Index is represented as BST, while nonclustered index contains pointer to data (address or rows) in there leaf node, which means one more extra step to get the data.
3) By the way there is a misconception that we can only define clustered index with one column, which is not true. You can create clustered index with multiple columns, known as composite index.
