Tricky SQL Server Interview Question and answers

Are you preparing for a SQL server interview? want some tricky questions and answers that will give you break through ? Then you are at the correct place. Here i have some bunch of tricky SQL Server interview questions and answers that will really help you to go through the interview

1.With is the Sixth normal form in SQL server? - Ohhh...You may think that, i have heard about first to forth only but what is this SIXTH? Friends, do not try to impress the interviewer just skip this question.
Sixth normal form does exist but use it when you want a relational system in conjunction with time. At this moment SQL Server does not support it directly.

2. In which Files does SQL Server Actually Store Data? - SQL server has 2 data files associated with it
1. MDF : Which is actual data file storage
2. LDF : (Log data files) which stores transaction log

3. Do you know How many locks are exist in SQL Server ? - We have these many locks exist here, see below
1. Intent
2. Shared
3. Update
4. Exclusive
5. Schema
6. Bulk Update

4.What is SQL Profiler?- It is a tool that allows administrator to monitor different events and transaction of SQL server instance, You can capture and save data about each event to a file or SQL Server table to analyze later
it is not available with EXPRESS edition

5. Can i insert into a table having just one IDENTITY column?- Yes you can, Use following Query

INSERT INTO TABLE1 DEFAULT VALUES;

6. How to drop primary key from a column using Query
- See below query to drop primary key constraint

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1_Col1

7. What is the difference between a DDL trigger and a DML trigger?- DDL commands are always deal with table structures, these command can update/modify table structure (CREATE, ALTER, DROP)
DMS commands are always deal with data/records not with table structure (INSERT, UPDATE, DELETE)

8. What the difference between UNION and UNIONALL?
- Union will remove the duplicate rows from the result set while UNIONALL does not remove them. So to avoid data redundancy you need to use UNION

9. What is the clustered and a non-clustered index?
- Clustered index is the physical index that reorders the way records in the table are physically stored, In non-clustered index logical order of the index does not match the physical stored order of the rows on disk

10. What's the difference between a primary key and a unique key?
- Both keys are not allow duplicate entries but primary key create a cluster index where as unique key create a non clustered index. Secondly primary key doesn't allow NULLs, but unique key allows one NULL only.
11. How to change the data type of a column- The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Here is the query to change column datataype
ALTER TABLE table_name ALTER COLUMN column_name data type

12. How to check the version of SQL server and operating system?
- With the help of the following query we can get it
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

13. What is CHECK Constraint?
- CHECK Constraint used to limit the values that can be placed in a column

14. How can you delete duplicate records in a table where there is no primary key?
- You can take Use of the SET ROWCOUNT command. Here if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.
15. What is NOT NULL Constraint?- This constraint force the column to not accept NULL values

16. How to copy data from one table to another table?
- With the help of 'INSERT INTO SELECT' or 'SELECT INTO' queries you can copy data from a datatable to another

17. What is PIVOT
- To automatically sort, count, and total the data stored in one table we use PIVOT. It will also rotate table as rows to columns and vice versa

18. What are DBCC commands?
- These are the Database Consistency Checker commands; They will Check disk allocation consistency, Display information about recent transactions.
19. Find the 3rd MAX salary in the emp table

Select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

20. What is the difference between Trigger and Stored Procedure?- Triggers cannot be called directly they need to associated with different quires

Finisher
Suggestion/Queries always welcome

Happy Interview
- Prasad

JQuery Interview Questions

Are you preparing a Jquery interview? Then read this blog to know more about JQuery, here I have given some frequently asked JQuery Interview Questions and answers, which will surely help you to crack your interview


1. What is JQuery?
- Most simple but frequently asked question, JQuery is a library designed by javascript, it is light weight, it is simple than JavaScript, it has many inbuilt methods and it can handle events, Html DOM and Ajax

2. What is $ in JQuery?
- Simple it is a function selector or a JQuery object, some says $ is a shortcut to the jQuery function

3. Can i use Javascript and JQuery on same page?
- Yes, we can use Javascript and JQuery on same page, for the use of JQuery we need to add .JS library reference to the page, where as for JavaScript for separate library reference is needed.

4. How to use JQuery?
 - JQuery can be easily called with the help of its referenced libraries, first we need to give JQuery library reference to page and then call it using '$' sign
see below snippet, we can write JQuery in HEAD section

<head>
//here is JQuery reference
//JQuery 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script>
$(document).ready(function(){
 alert("Message on on page load")
});
</script>
</head>

above snippet shows, we can write JQuery in HEAD section and above code will call on Page load
5. What are the features of JQuery?
- JQuery is highly intractable, it has following features

• Rich Inbuilt methods that easily interact with controls
• Events handling
• Special widgets
• Animation and effect supports
• Good alternative to flash
• Strong AJAX support
• HTML DOM Manipulation
• Can Bind Multiple Event Handlers in single method

6. What is JQuery UI?
- JQuery UI is also a JavaScript library, as name suggest it is a user interface portal that is built using JavaScript and can be easily added in new and existing applications
it contains interactions, widgets and effects, we can build UI rich and highly interactive applications using it.

7. Which are the performance responsive selector?
- The elements which are selected using ID are the performance responsive selector as the ID is unique throughout the rendered page

8. JQuery or JavaScript which is quicker in execution?
- Native JavaScript is always faster in operation than JQuery cause JQuery is a JavaScript library,
e.g. Through we call '$(#EmpName)' in JQuery, it internally convert the syntax to javascript's 'getElementById("EmpName")' method and then execute in browser

9. What is has() in JQuery? - has() is selection filter, it checks Check if an Element 'Has Something', means if i want to traverse my HTML file and check if DIV has list tag (OL) then we can check with has(), see below snippet

JQuery('div').has('ol');

above code will select all element with 'ol'
10. Can JQuery parse XML?
- No, currently JQuery supports only HTML DOM parser

11. Can JQuery Run on MAC or Linux instead of Windows?
- JQuery support cross browser and cross platform compatibility hence it fluidly runs on Windows/MAC/Linux with all major browser compatibility (IE/Chrome/FF/NN/Safari)

12. When to use JQuery?
- Basically JQuery can be use more than validation part, Alone JavaScript can be able to handle validation part, you can us JQuery when you want rich user interface with client side events, when you want to remove flash plug inns and put some animations

13. Which effects has inbuilt supported by JQuery?
- Hide, Show, Toggle, SlideDown, SlideToggle, SlideUp, FadeIn, FadeOut, Animate, FadeTo, FadeToggle, has inbuilt support by JQuery

14. Can we replace any character or variable instead of $ in JQuery?
- Yes,
we can replace $ by using no Conflict () method, see below snippet to replace $

var szDoll = $.noConflict()

15. Tell me something about JQuery debugging? JQuery can be debugged easily using following ways

1. using debugger; keyword : Add debugger; keyword just before the line where you want to debug
2. using Breakpoint : If you have visual studio then you can place breakpoint on line where you want to start debugging
3. Placing a Alert box : It is traditional way, if you don't have visual studio, you need to place a alert boxes in lines from where you want start debugging

16. What is Method chaining in JQuery?
 - As name suggest it is the chain of methods executed in sequence, it is executed in single statement, see following snippet

$("#div1").text("DIV 1");
$("#div1").css("color", "red");
$("#div1").removeClass("cls");

//instead we can write
$("#div1").text("DIV 1");
          .css("color", "red");
          .removeClass("cls");

17. How to redirect a page using JQuery?
- There is no specific JQuery syntax to redirect a page, you can use Javascript syntax instead, see below snippet

window.location.replace("http://test/default.aspx");
OR
window.location.href = http://test/default.aspx;

18. How to refresh a page using JQuery?
- you can use location.reload() method, to refresh page, see below snippet

$('#div1').click(function() 
{
location.reload();
});

19. How to check if element is hidden or visible using JQuery?
- You can use 'is()' method for 'display' or 'hidden' property, it returns boolean variable, see below snippet

$("#txtEmpName").is(":visible"); 

20. What is difference in return false and preventDefault in JQuery?
- when we call return false, it first call e.preventDefault and then call e.stopPropagation which stop execution, where as e.preventDefault will prevent event to be bubbling up.

21. What is difference between "#" and "." selector in JQuery? - Basically JQuery uses following notation as selector

1. $("div") : It is global selector which Selects all DIV element in page
2. $("#btnSubmit") : # is a specific ID selector which select element having ID as 'btnSubmit'
3. $(".classBold") : . is a specific Class selector which select element having class as 'classBold'

22. Which are the most common JQuery evens?

• click () : Raise when any element is clicked
• dblclick() : Raise when any element is double clicked
• mouseenter() : Raise when mouse pointer enter in element
• mouseleave() : Raise when mouse pointer leave an element
• mousedown() : Raise when mouse pointer clicked down on element
• mouseup() : Raise when mouse pointer clicked and release on element
• focus() : Raise when element got focus

Rome was not built in a day, still more to come, i will cover some more complex Q&A in Next version of this article, till then you can enjoy this most commonly asked question

Suggestion and queries are most welcome

Thanking you
-Prasad