Chinmoy Mukherjee - Cracking the Coding Interview: 70 Database Questions and Answers

DatabaseInterview Question-2. When to not useindex? Answer: Index should not be used forfollowing cases: If number of records are small If records need to beupdated/inserted/deleted on a daily basis If column value can be null veryoften DatabaseInterview Question-3. What is tempdb? Answer: tempdb is temporary databasewhich can be used by all users/processes to maintain temporary tables, etc.Content of tempdb is erased when database server is restarted.

DatabaseInterview Question-4. How will you findout all the stored procedures present in a database? Answer: SELECT object_name,object_type FROM user_objects WHERE object_type = 'PROCEDURE' DatabaseInterview Question-5. What are thedifferences between TRUNCATE and DELETE? Answer: TRUNCATE removes all rowsfrom a table. The operation cannot be rolled back, also triggers are notexecuted for TRUNCATE DatabaseInterview Question-6. What is the differencebetween IN and EXISTS ? Answer: EXISTS checks if any suchrecord exists. e.g.

SELECT * FROM Orders o WHERE EXISTS( SELECT * FROM Products p wherep.ProductNumber = o.ProductNumber) IN is used to find presence of arecord among multiple records. e.g. SELECT * FROM Orders WHEREProductNumber IN (1, 10, 100) DatabaseInterview Question-7. What databaseparameters can be tuned for performance?" Answer: Buffer pool, Log level, etc. DatabaseInterview Question-8. What is bcp? Answer: bcp in Sybase helps to copytable into a flat file and vice versa.

DatabaseInterview Question-9. Suppose you arelogged onto a database DB1. What query will you run in DB1 to retrieve datafrom the table table1 present in another database DB2? Answer: We can use either select *from DB2..table1 or use DB2;select * from table1 DatabaseInterview Question-10. What are main Opensource databases? Answer: PostgreSQL, MySQL, etc. DatabaseInterview Question-11. What is a View? Answer: A view is the representationof a SQL statement that is stored in memory so that it can easily be re-used asexample CREATE VIEW [MyProductList] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No SELECT * FROM [MyProductList] DatabaseInterview Question-12.

There is a tableEmployee having two columns Emp_Name and Office_Locations. Write a querythat will display all office locations and the number of employees in each ofthese locations. Answer: Select Office_Locations,COUNT(Emp_Name) FROM Employee GROUP BY Office_Locations; DatabaseInterview Question-13. Write a query toget the employees, who are also managers Answer: Select name from employeeswhere id in (select manager from employees) Or Select e1.name from employees e1 innerjoin employees e2 ON e1.id = e2.manager DatabaseInterview Question-14. How to get nameof database? Answer: select db_name(); DatabaseInterview Question-15. How to getdatabase version? Answer: select @@version DatabaseInterview Question-16.

If table istruncated, can it be roll backed? Answer: No DatabaseInterview Question-17. What is clusteredindex? How many Clustered indexes can you have on a table. Answer: A clustered index physicallysorts the records on the disk based on the index. Hence only one clusteredindex per table is feasible DatabaseInterview Question-18. What is MINUSused for? Answer: MINUS returns only thoseresults from first result set which are not present in second result set DatabaseInterview Question-19. Write a query toget all the employees who are managers Answer: select distinct e.NAME asEmployee, m.NAME as Manager from EMPLOYEE e inner join EMPLOYEE m on e.ID =m.MANAGER; DatabaseInterview Question-20.

Write a query toget all the employees who do not have managers Answer: Select ID, NAME fromemployee where MANAGER is NULL; DatabaseInterview Question-21. Suppose a tableX has 3 duplicate rows and you want to delete 2 redundant rows

ID	Name	Level
	Jack
	Jack
	Jack

Answer: BEGIN; set rowcount 2;delete from X; END; DatabaseInterview Question-22. There is a tableEmployee having two columns Emp_Name and Office_Locations. Write a querythat will display all office locations having more than 100 employees. Answer: select Office_Locations from(select Office_Locations, COUNT(Emp_name) as count1 from employee_table GROUPBY Office_Locations) HAVING count1>=100; DatabaseInterview Question-23. Can a table spanmultiple partitions? Answer: Yes DatabaseInterview Question-24.

What is foreignkey? Answer: Foreign key is a column or acombination of columns that is used to establish and enforce a link between twotable DatabaseInterview Question-25. How to removeduplicates from a table? SELECT DISTINCT * INTO NODUPLICATESFROM DUPLICATES; DROP TABLE DUPLICATES; RENAME TABLE NODUPLICATES TO DUPLICATES DatabaseInterview Question-26. How do youmeasure performance of a stored proc? Answer: There are three ways tomeasure performance of stored procedure. First way: DECLARE @start datetime, @stopdatetime SET @start = GETDATE() EXEC mystoredprocedure SET @stop = GETDATE() Second way: SET STATISTICS TIME ON EXEC mystoredprocedure Third way: SET STATISTICS IO ON EXEC mystoredprocedure DatabaseInterview Question-27. How do youdefine constraints? Answer: CREATE TABLE myTable ( column_name1 data_type(size)constraint_name, column_name2 data_type(size)constraint_name, column_name3 data_type(size)constraint_name, .... ); As example, we have NOT NULL andprimary key constraints in below table CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_ID PRIMARY KEY (ID) ) DatabaseInterview Question-28.

What allconstraints are supported by MYSQL? Answer: We have the followingconstraints: A. NOT NULL The constraint ensuresthat a column cannot store NULL value B. UNIQUE - The constraint ensures thatcolumn cannot have duplicate value C. PRIMARY KEY This constraint is combinationof UNIQUE and NOT NULL D. FOREIGN KEY This constraint ensuresthat referential integrity among multiple table is maintained E. CHECK - This constraint ensures thatthe value in a column meets a specific condition F.

DEFAULT - This constraint specifies adefault value for a column DatabaseInterview Question-29. What are thedifferences between WHERE clause and HAVING clause? Answer: WHERE clause cannot be usedwith aggregates, but the HAVING clause can be used. WHERE is less expensive than that ofHAVING DatabaseInterview Question-30. Write a simpleStored Procedure. Answer: Here is a sample storedprocedure CREATE PROCEDUREGetEmployeenameEmailInOutputVariable ( @ID INT, --Inputparameter @NAME VARCHAR (200) OUT, --Output parameter to collect name @EMAIL VARCHAR (200)OUT --Output Parameter to collectemail ) AS BEGIN SELECT @NAME= Firstname+''+Lastname, @EMAIL=EMAIL FROM EMPLOYEE WHEREID=@ID END DatabaseInterview Question-31. What are thedisadvantages of normalization? Answer: More one normalizesdatabase, more tables, more joins are required and takes lot of time toretrieve required records DatabaseInterview Question-32.