Database/SQL Interview Questions

How will you search for a word in a very large database ?
1. First the group of related files are stored in different bucket using hashing . Then in particular bucket we sort the files using some sorting method if the files are not in alphabetical order .
2. Now comes the searching part . Using hash function we can reach to bucket in O(1) time.
3. As we have already sorted the files in particular bucket so we will apply binary search to  reach to exact file. Binary Search will take O(logn) time .
Then in the file we can apply different searching algorithm to reach to the word .

Explain the database to your 5 year old child in three sentence.
1. It is like shelf in which you put your toys with number of drawer for different kind of toys. for eg for green toys you put them in drawer no 1 and to the red toys you put them in drawer no .2.
2. Every drawer has a name slip on it like green ,yellow  etc so that you can easily search different color toy.
3.Your self has  a  lock and you are having key of that lock such that without your permission no can open your shelf and  see your toys .

Design a database schema for college elections and how will you make sure at database end that one user can vote for one person standing for one post only.
There will be four table as follows :->
1. Candidate(name ,c_id(primary_key) ,phone_no., Address )
2. Voter(name , v_id(primary_key), phone_no,address,no_of_votes(auto_increment and check constraint <=1)
3. Voting(v_id,c_id)
4.Post(id,name,c_id(unique))

Here comes the important question where did we make sure that one user can vote for one person standing for one post only.

If you will see carefully in the database schema you will notice that i have made no_of_votes attribute in table Voter auto_incremented i.e when a voter will vote for a particular candidate .His
no_of_votes will be incremented automatically and next time if comes he comes to vote again then check constraint will show that it has already been incremented hence cant vote anymore.

Now for  a candidate can apply for one post only :- we have made c_id in the post table unique hence he can't apply for more than one post.


What is the difference between having and where clause?

HAVING is used to specify a condition for a group or an aggregate function used in select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions.


Question : Consider the two relation given below:


Given that A is the primary key of the RTable and D is the primary Key of S and there is referential integrity between Rtable.A and STable.A . Discuss all the integrity constraints that are violated.   

Before solving this question, i would suggest to read this article and refresh your concept of key and integrity rules.

1.Primary key of the RTable  contains the null values and the value A1 is duplicated. hence it violated both the properties of the primary key and Entity Integrity rule i.e. the primary key can not contain NULl values and primary key value must be unique
2. In Primary key  of  STable value d1 is duplicated .
3. The foreign S.Table contains the value 'A2' which is not available in the parent key hence violating the referential integrity constraints in the relation STable.


What is the difference between primary key and unique constraints?

Both primary key and unique key enforces uniqueness of the column on which they are defined .
1)But by default , a primary key creates a clustered indexes on the column , whereas unique creates a non clustered index by default .
2) Also, primary key does not allow NULL values , but unique key allows NULL values .
3) There can be more than one unique key for a table but there can not be more than one primary key for a table .

Can a table have multiple unique, foreign, and/or primary keys?

A table can have multiple unique and foreign keys. However, a table can have only one primary key.

Can a unique key have NULL values? Can a primary key have NULL values?

Unique key columns are allowed to hold NULL values. The values in a primary key column, however, can never be NULL.

Can a foreign key reference a non-primary key?

Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference a unique key.

Can a foreign key contain null values?

Yes, a foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well.

What is a Trigger?

A Trigger is a stored procedure that is associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.
Trigger can not be invoked on demand . Trigger can be used for referential integrity checks also , but wherever possible, constraints should be used for this purpose , instead of triggers , as constraints are much faster.

What is a Stored Procedure?

A stored procedure is a set of pre-compiled T-SQL statemenets, which can be executed whenever required .They are similar to procedure in any programming language and hence can accept input parameters , return output parameters and even status value .
Advantages of using Stored Procedure :
1) Increased modularized programming .
2) Faster execution as they are pre-compiled.

What is the difference between Trigger and Stored Procedure?

Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with queries whereas stored procedure can be called as per user requirement .

Example of automatic execution of Trigger on the compilation of DML statement.

We have a database in which we have store the information about product. The price of a product changes constantly . It is important to maintain the history of the prices of the product . So we will need trigger to update the history of prices of product . Lets see how it works :

Suppose we have the following tables in our database.

product_name

product_id

supplier_name

product_price

                         (Product)

product_name

product_id

supplier_name

product_price

                         (Product_Price_History)

Now we will create the price_history_trigger and execute it .

CREATE or REPLACE TRIGGER price_history_trigger
BEFORE UPDATE Of product_price
ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
( : old.product_name,
:old_product_id.
:old.supplier_name,
:old.product_price);
END; 

Lets update the product table :

UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100

Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.

Example of on demand execution of Stored Procedure 

Below we are creating the procedure named employer_detail which will give the informtiion about employee.

CREATE OR REPLACE PROCEDURE employer_details
IS
CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur%rowtype;
BEGIN
FOR emp_rec in sales_cur
LOOP
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
|| ' ' ||emp_cur.salary);
END LOOP;
END;

Now we will call the procedure . There are two ways :
1) From the SQL prompt.
    EXECUTE [or EXEC] procedure_name;
2) Within another procedure – simply use the procedure name.
    procedure_name;


Explain the different kind of joins in DBMS .

Refer this article to get the complete details of joins in DBMS.


How does the database indexing works ?

Please refer to database indexing for getting more details.

Explain the ACID in database.

Atomic -  As described above, atomicity makes sure that either all operations within a transaction must be successful or none of them.
Consistent- This property makes sure that data should be in consistent state once the transaction is completed.
Isolated-  this property allows multiple users to access the same set of data and each user’s processing should be isolated from others.

Durable – Result of the transaction should be permanent once the transaction is completed to avoid any loss of data.

Write a query to find 2nd highest salary of an employee?

SELECT max(salary) FROM EMPLOYEES WHERE salary IN
(SELECT salary FROM EMPLOYEEs MINUS SELECT max(salary)
FROM EMPLOYEES);

OR

SELECT max(salary) FROM EMPLOYEES WHERE 
salary NOT IN (SELECT max(salary) FROM EMPLOYEES);

There is table where only one row is fully repeated. Write a Query to find the Repeated row

Name	Section
abc	CS1
bcd	CS2
abc	CS1

SELECT name, section FROM tbl
GROUP BY name, section
HAVING COUNT(*) > 1

How would you approach to optimization of a slow query?

Here are some of the things to think about when a query is slow.

• All the databases provide a tool to examine the execution plans of a query., we can use such to tool to see what a database is doing to optimize our query. Is it using an index or it is doing a table scan.
• We can look if there is looping in the query. Can we remove the loop and make it into one query
• Retrieving only required columns. I recently had to optimize a query that was retrieving a row that had a serialized object as one of the data column. But that serialized object was never used. And hibernate was very slow retrieving data from this table. By removing that column from the select statement, i could reduce the query execution time considerably.
• Indexes can make slow performing queries very fast. If the column on which the filter needs to be indexed. Execution plan tools can help here by showing which index was used for the query. Indexing is not always a solution and can lead to slower queries sometimes.
• Caching can be an option. If nothing helps then a cache in the application can reduce the calls to the database.

This covers the database important concepts and database interview  questions which are being asked , if you want to add more question or you have any doubt then please comment below.