SQL Server Interview jinzihao.info jinzihao.info and c# interview questions and Training visit jinzihao.info An article listing SQL Server Interview Questions. Shivprasad koirala, 10 Jan Interview Questions on Database Concepts. (Q) What is. MOST ASKED Sql Interview Questions Shivprasad Koirala Pdf. Read Sql Interview Questions Shivprasad Koirala Pdf for JOB INTERVIEW.
|Language:||English, Spanish, Japanese|
|ePub File Size:||24.60 MB|
|PDF File Size:||18.80 MB|
|Distribution:||Free* [*Regsitration Required]|
Download as PDF, TXT or read online from Scribd NET Interview questions -- SQL Server Interview questions -- Java Interview questions -- C# and ASP. Copyright: Attribution Non-Commercial (BY-NC). Download as PDF, TXT or read online from Scribd SQL Server Interview Questions From Prakash books. PDF's, how about a self owned hard copy. If you want to jinzihao.info uk/NET-Interview-Questions-Shivprasad- SQL Server Interview Questions.
Sign in Email. Keep similing and Happy job hunting: Because of inconsistent HR way of working you will see difference between companies. It brings huge weight. In SQL SERVER its stored in resource database which is stored as sys object , you can not access the sys object directly as in older version wewhere accessing master database. What does that mean fundamentally?. I Define transactions?
In order for the above example to satisfy the fourth normal form, both the many-to-many relationships should go in different tables. Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.
Dealers sell Product which can be manufactured by various Companies. Dealers in order to sell the Product should be registered with the Company. So these three entities have a mutual relationship within them. The above table shows some sample data.
If you observe closely, a single record is created using lot of small information. For instance: JM Associate can sell sweets under the following two conditions:. These two smaller bits of information form one record of the above given table. Below is the complete fifth normal form of the database. There is a huge similarity between Fourth and Fifth normal form, i.
Arrrrggghhh yes there exists a sixth normal form also. But note guys you can skip this statement. Just in case you want to impress the interviewer If you want a relational system in conjunction with time, you use sixth normal form. At this moment SQL Server does not support it directly. Extent is a basic unit of storage to provide space for tables. Every extent has a number of data pages. As new records are inserted new data, pages are allocated.
There are eight data pages in an extent. So as soon as the eight pages are consumed, it allocates a new extent with data pages. While extent is basic unit storage from a database point of view, page is a unit of allocation within extent. Data rows are where your actual row data is stored. For every data row, there is a row offset which points to that data row. Pages are contained in extent.
Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. Any SQL Server database is associated with two kinds of files: Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.
If a and A , o and O are treated in the same way, then it is accent-insensitive. When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. When a single-byte character half-width and the same character when represented as a double-byte character full-width are treated differently then it is width sensitive. Sign in Email.
Forgot your password? Search within: Articles Quick Answers Messages. Comments Tagged as SQL. Stats Licenced CPOL. Shivprasad koirala , 10 Jan Rate this: Please Sign up or sign in to vote. Introduction In this article we will go through the most basic and frequently asked interview questions on SQL Server. Happy job hunting Q What are E-R diagrams?
Q What is denormalization? Q What is Extent and Page? Q What are page splits? Chapter 1: What is the difference between a file and a database?
Can files qualify as a database? Database provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information. Secondly, the information has to be persistent, that means even after the application is closed the information should be persisted.
Finally, it should provide an independent way of accessing data and should not be dependent on the application to access the information. Figure 1. Non-Uniform Text File. Shivprasad koirala. Architect https: Do not forget to watch my Learn step by step video series.
Ben Greenberg 3-Jan Manish Chadha Aug Donsw Feb 7: Josh 2-Mar Ralph in Boise May Go to top. Rate this:. First Prev Next. Comment Member 2-Dec 0: Member Newer Post Older Post Home. Divya 2 November at Vikas 2 November at Vasundhara Reddy 2 November at Shagufta Ambar Shabeer 2 November at K Saikiran 4 November at Rohit Nikam 5 November at Tarak 7 November at Bhupat 15 November at Unknown 26 November at Anonymous 2 December at Anonymous 7 December at Anonymous 20 December at Jaikishan Joshi 22 December at Anonymous 24 December at Praveena Reddy 18 January at Naveen Harjani 30 January at Arun C Nair 30 January at Dhanashree Thakur 11 February at Anonymous 11 February at Santosh Ghutukade 17 February at Mogesh 22 February at Unknown 29 February at Maitreyee Pati 2 March at Vikram Chougule 2 March at Unknown 7 March at Pooja Grover 7 March at Kripansh Grover 7 March at Unknown 9 March at Unknown 10 March at Ankit Tiwari 15 March at Unknown 18 March at Anonymous 24 March at Himanshu Dhomse 24 March at Anonymous 4 April at Gourav Gupta 7 April at Xack Cloud 8 April at Anonymous 11 April at Anonymous 13 April at Kannan Ranganathan 26 April at Unknown 29 April at Tushar Baviskar 1 May at Kaushal Parik 3 May at Amit Kumar 27 May at Himasnhu Sharma 4 June at Anonymous 8 June at But again the other aspect, readers should know of what they are buying , the quality and is it really useful to buy this book.
So here are sample free questions which i am giving out free to the readers to see the worth of the book. We have recently started a career counselling drive absolutely free for new comers and experienced guys.
So i have enlisted the following guys on the panel. Thanks to all these guys to accept the panel job of consulting. Feel free to shoot them questions just put a title in the mail saying Question about Career. I have always turned up to them when i had some serious career decision to take. Shivprasad Koirala: Tapan Das: He can answer all your questions regarding how to groom your career as a project manager tapand vsnl. Kapil Siddharth: When it comes to role model as architect i rate this guy at the top.
You can contact him at kapilsiddharth hotmail. Please note there are no financial rewards as such but i am sure you will be proud of the work you are doing and whos knows what can come up. Lets make Software Industry a better place to work Happy Job Hunting and Best of Luck. The Table of contents is different from what is available in traditional books.
So rather than reading through the whole book just look at what questions you feel uncomfortable and revise that. Contents Introduction Database Concepts What are different type of normalization? NET Integration NET run-time? NET assemblies? In one of the projects following steps where done, will it work? NET framework 1. NET assembly? NET datatypes? NET architecture? What are basic methods of Dataadapter?
Notification Services Service Broker XML Integration How can we import and export using BCP utility? What are prime components in Integration Services? Reporting Services Database Optimization DB How can we remove the Fragmented spaces? Transaction and Locks Introduction Dedication This book is dedicated to my kid Sanjana, whose dads play time has been stolen and given to this book.
I am thankful to my wife for constantly encouraging me and also to BPB Publication to give new comer a platform to perform. Finally at the top of all thanks to two old eyes my mom and dad for always blessing me.
I am blessed to have Raju as my brother who always keeps my momentum moving on. I am grateful to Bhavnesh Asar who initially conceptualized the idea I believe concept thinking is more important than execution.
Tons of thanks to my reviewers whose feedback provided an essential tool to improve my writing capabilities. Just wanted to point out Miss Kadambari. Kadam took all the pain to review for the left outs with out which this book would have never seen the quality light. About the author Author works in a big multinational company and has over 8 years of experience in software industry.
He is working presently as project lead and in past has led projects in Banking, travel and financial sectors. But on the top of all , I am a simple developer like you all guys there doing an 8 hour job. Writing is something I do extra and I love doing it. No one is perfect and same holds true for me. Believe me guys your harsh words would be received with love and treated to the top most priority. Without all you guys I am not an author.
Writing an interview question book is really a great deal of responsibility. I have tried to cover maximum questions for the topic because I always think probably leaving one silly question will cost someones job there. But huge natural variations in an interview are something difficult to cover in this small book. Who knows probably that question can save some other guys job. Features of the book. This book goes in best combination with my previous book.
NET Interview questions. One takes care of your front end aspect and this one the back end which will make you really stand out during.
NET interviews. Other than core level interview question, DBA topics like database optimization and locking are also addressed. Replication section where most of the developer stumble, full chapter is dedicated to replication so that during interview you really look a champ.
NET integration which is one of the favorites of every interviewer is addressed with great care. This makes developer more comfortable during interview.
XML is one of the must to be answered questions during interview. All new XML features are covered with great elegance. Areas like data warehousing and data mining are handled in complete depth. Reporting and Analysis services which can really surprise developers during interviews are also dealt with great care. A complete chapter on ADO. NET makes it more stronger from a programmer aspect. In addition new ADO. Must for freshers who want to avoid some unnecessary pitfall during interview.
Every answer is precise and to the point rather than hitting around the bush. Some questions are answered to greater detail with practical implementation in mind. DB level question are mostly for guys who are looking for high profile DBA level jobs. Tips and tricks for interview, resume making and salary negotiation section takes this book to a greater height.
Introduction When my previous book ". Ok guys we have heard it louder and clearer, so heres my complete book on SQL Server: But theres a second stronger reason for writing this book which stands taller than the readers demand and that is SQL Server itself.
When it comes to. NET and especially operating system is windows. I treat this great relationship between. In my previous book we had only one chapter which was dedicated to SQL Server which is complete injustice to this beautiful product.
So why an interview question book on SQL Server? If you look at any. NET interview conducted in your premises both parties Employer and Candidate pay no attention to SQL Server even though when it is such an important part of development project. I am not saying these things are not important but if you see in development or maintenance majority time you will be either in your IDE or in SQL Server. Third very important thing IT is changing people expect more out of less.
That means they expect a programmer should be architect, coder, tester and yes and yes a DBA also. For mission critical data there will always be a separate position for a DBA. But now many interviewers expect programmers to also do a job of DBA, Data warehousing etc.
This is the major place where developers lack during facing these kinds of interview. So this book will make you walk through those surprising questions which can sprang from SQL Server aspect. I have tried to not go too deep as that will defeat the complete purpose of an Interview Question book. I think that an interview book should make you I hope this book takes you to a better height and gives you extra confidence boost during interviews. Best of Luck and Happy Job-Hunting How to read this book If you can read English, you can read this book In this book there are some legends which will make your reading more effective.
Every question has simple tags which mark the rating of the questions. These rating are given by Author and can vary according to companies and individuals. Compared to my previous book. While reading you can come across section marked as Note , which highlight special points of that section.
You will also come across tags like TWIST, which is nothing , but another way of asking the same question, for instance What is replication? But for DBA guys every question is important. For instance if you are going for a developer position and you flunk in simple ADO. NET question you know the result. Vice versa if you are going for a DBA position and you can not answer basic query optimization questions probably you will never reach the HR round.
So the best way to read this book is read the question and judge yourself do you think you will be asked these types of questions? For instance many times you know you will be only asked about data warehousing and rather than hitting the bush around you would like to target that section more.
And Many times you know your weakest area and you would only like to brush up those sections. You can say this book is not a book which has to be read from start to end you can start from a chapter or question and when you think you are ok close it. Software Company hierarchy. Its very important during interview to be clear about what position you are targeting. Depending on what positions you are targeting the interviewer shoots you questions.
But in big companies you can easily see the difference where DBA job are specifically done by specialist of SQL Server rather than developers. But now a days some big companies believe in a developer doing multitask jobs to remove dependencies on a resource. Above is a figure of a general hierarchy across most IT companies Well not always but I hope most of the time.
Because of inconsistent HR way of working you will see difference between companies. So why there is a need of hierarchy in an interview? Interview is a contract between the employer and candidate to achieve specific goals. So employer is looking for a suitable candidate and candidate for a better career. Normally in interviews the employer is very clear about what type of candidate he is looking for. How many times has it happened with you that you have given a whole interview and when you mentioned the position you are looking for So be clarified about the position right when you start the interview.
Following are the number of years of experience according to position. Junior engineers are especially fresher and work under software engineers.
Software engineers have around 1 to 2 years of experience. Interviewer expects software engineers to have know how of how to code ADO.
Senior Software Engineers have around 2 to 4 years of experience. Interviewer expect them to be technically very strong. Project leads should handle majority technical aspect of project and should have around 4 to 8 years of experience. They are also actively involved in to defining architect of the project. Interviewer expects them to be technically strong plus should have managerial skills.
But they are more interviewed from aspect of project management, client interaction, people management, proposal preparation etc. Pure DBAs do not come in hierarchy as such in pure development projects. They do report to the project managers or project leads but they are mainly across the hierarchy helping every one in a project.
In small companies software developers can also act as DBAs depending on companies policy. Pure DBAs have normally around 6 and above years of experience in that particular database product. That means one or two guys work fulfilling maintenance tickets. So now judge where you stand where you want to go Resume Preparation Guidelines First impression the last impression Before even the interviewer meets you he will first meet your resume.
I was always a bad guy when it comes to resume preparation. But when I looked at my friends resume they where gorgeous.
Now that I am writing series of book on interviews I thought this will be a good point to put in. You can happily skip it if you are confident about your resume. There is no hard and fast rule that you have to follow the same pattern but just see if these all check list are attended.
Use plain text when you are sending resumes through email. For instance you sent your resume using Microsoft word and what if the interviewer is using Linux he will never be able to read your resume. You can not be sure both wise , you sent your resume in Word and the guy has Word 97uuhhh.
Attach a covering letter it really impresses and makes you look traditionally formal. Yes even if you are sending your CV through email send a covering letter. Start with an objective or summary, for instance, Working as a Senior Database administrator for more than 4 years. Implemented quality web based application. Followed the industrys best practices and adhered and implemented processes, which enhanced the quality of technical delivery.
Pledged to deliver the best technical solutions to the industry. Specify your Core strengths at the start of the resume by which the interviewer can make a quick decision are you eligible for the position. For example: Looked after data mining and data warehousing department independently. Played a major role in query optimization. Worked extensively in database design and ER diagram implementation.
Well versed with CMMI process and followed it extensively in projects.
Looking forward to work on project manager or senior manager position. This is also a good position to specify your objective or position which makes it clear to the interviewer that should he call you for an interview. For instance if you are looking for senior position specify it explicitly looking for this job profile. Once you have specified briefly your goals and what you have done its time to specify what type of technology you have worked with. This will give an overview to the interviewer what type of companies you have associated your self.
Now its time to mention all your projects you have worked till now. Best is to start in descending order that is from your current project and go backwards. For every project try to put these things: Number of team members. Time span of the project. Brief summary of the project. Senior people who have huge experience will tend to increase there CV with putting in summary for all project. Best for them is to just put description of the first three projects in descending manner and rest they can say verbally during interview.
I have seen CV above 15 pages I doubt who can read it. Finally comes your education and personal details. Trying for onsite, do not forget to mention your passport number. Some guys tend to make there CV large and huge. I think an optimal size should be not more than 4 to 5 pages. Do not mention your salary in CV. You can talk about it during interview with HR or the interviewer. When you are writing your summary for project make it effective by using verbs like managed a team of 5 members, architected the project from start to finish etc.
It brings huge weight. This is essential very essential take 4 to 5 Xerox copies of your resume you will need it now and then. Just in case take at least 2 passport photos with you. You can escape it but many times you will need it.
Carry youre all current office documents specially your salary slips and joining letter. Salary Negotiation Ok thats what we all do it for money not every one right.
This is probably the weakest area for techno savvy guys. They are not good negotiators. So here are some points: Do a study of whats the salary trend? For instance have some kind of baseline. For example whats the salary trend on number of year of experience? Discuss this with your friends out. Do not mention your expected salary on the resume?
Let the employer first make the salary offer. Try to delay the salary discussion till the end. If they say what you expect? Remember never say negotiable on something which you have aimed, HR guys will always bring it down. The normal trend is that they look at your current salary and add a little it so that they can pull you in. Do your home work my salary is this much and I expect this much so whatever it is now I will not come below this.
Do not be harsh during salary negotiations. Its good to aim high. Some companies have those hidden cost attached in salary clarify that rather to be surprised at the first salary package. Many of the companies add extra performance compensation in your basic which can be surprising at times.
So have a detail break down. Best is to discuss on hand salary rather than NET. Talk with the employer in what frequency does the hike happen. Take everything in writing , go back to your house and have a look once with a cool head is the offer worth it of what your current employer is giving.
Do not forget once you have job in hand you can come back to your current employer for negotiation so keep that thing in mind. Remember the worst part is cribbing after joining the company that your colleague is getting this much. So be careful while interview negotiations or be sportive to be a good negotiator in the next interview. One very important thing the best negotiation ground is not the new company where you are going but the old company which you are leaving.
So once you have offer on hand get back to your old employee and show them the offer and then make your next move. Its my experience that negotiating with the old employer is easy than with the new one. Frankly if approached properly rarely any one will say no. Just do not be aggressive or egoistic that you have an offer on hand. Top of all some time some things are worth above money: I think its worth more than money. Points to remember One of the first questions asked during interview is Can you say something about yourself?
Can you describe about your self and what you have achieved till now? Why you want to leave the current company?
Where do you see yourself after three years? What are your positive and negative points? How much do you rate yourself in. Are you looking for onsite opportunities?
Be careful do not show your desperation of abroad journeys Why have you changed so many jobs? Prepare a decent answer do not blame companies and individuals for your frequent change. Never talk for more than 1 minute straight during interview. Have you worked with previous version of SQL Server?
Would you be interested in a full time Database administrator job? Do not mention client names in resume. If asked say that its confidential which brings ahead qualities like honesty When you make your resume keep your recent projects at the top.
Find out what the employer is looking for by asking him questions at the start of interview and best is before going to interview. Can you give brief about your family background?
As you are fresher do you think you can really do this job? Have you heard about our company? Say five points about our company? Just read at least once what company you are going for? Can you describe your best project you have worked with? Do you work on Saturday and Sunday?
Which is the biggest team size you have worked with? Can you describe your current project you have worked with? How much time will you need to join our organization? Whats notice period for your current company?
What certifications have you cleared? Do you have pass port size photos, last year mark sheet, previous companies employment letter, last months salary slip, pass port and other necessary documents. Whats the most important thing that motivates you?
Why you want to leave the previous organization? Which type of job gives you greatest satisfaction? What is the type of environment you are looking for? Do you have experience in project management? Do you like to work as a team or as individual? Describe your best project manager you have worked with? Why should I hire you? Have you been ever fired or forced to resign?
Can you explain some important points that you have learnt from your past project experiences? Have you gone through some unsuccessful projects, if yes can you explain why did the project fail? Will you be comfortable with location shift? If you have personal problems say no right at the first stage Do you work during late nights? Best answer if there is project deadline yes. Do not show that its your culture to work during nights. Any special achievements in your life till now Any plans of opening your own software company Beware do not start pouring your bill gates dream to him Can files qualify as a database?
But from freshers point of view it can be a difference between getting a job and to be jobless. Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information.
Secondly the information has to be persistent, that means even after the application is closed the information should be persisted. Finally it should provide an independent way of accessing data and should not be dependent on the application to access the information. Ok let me spend a few sentence more on explaining the third aspect. Below is a simple figure of a text file which has personal detail information.
The first column of the information is Name, Second address and finally the phone number. This is a simple text file which was designed by a programmer for a specific application.
It works fine in the boundary of the application. Now some years down the line a third party application has to be integrated with this file , so in order the third party application integrates properly it has the following options: Use interface of the original application.
Understand the complete detail of how the text file is organized, example the first column is Name, then address and finally phone number. After analyzing write a code which can read the file, parse it etc. Hmm lot of work right. Thats what the main difference between a simple file and database; database has independent way SQL of accessing information while simple files do not That answers my twisted question defined above. File meets the storing, managing and retrieving part of a database but not the independent way of accessing data.
But if you look at some old COBOL and C programs where file where the only means of storing data, you can see functionalities like locking, multi-user etc provided very efficiently. So its a matter of debate if some interviewers think this as a main difference between files and database accept it going in to debate is probably loosing a job.
Just a note for freshers multi-user capabilities means that at one moment of time more than one user should be able to add, update, view and delete data.
All DBMS provides this as in built functionalities but if you are storing information in files its up to the application to write a logic to achieve these functionalities. Ok as said before DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. If we break this referential integrity in DBMS and Files it will not complain, but RDBMS will not allow you to save this data if you have defined the relation integrity between person and addresses.
We will be answering this question in later section. In Dr. Rule 1: Information Rule. Rule 2: Guaranteed access Rule. NET we can access field information using table name and field names. Rule 3: Systematic treatment of null values. Rule 4: Dynamic on-line catalog based on the relational model. Rule 5: Comprehensive data sub-language Rule. However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items Data Definition View Definition Data Manipulation Interactive and by program.
Integrity Constraints Authorization. Transaction boundaries Begin , commit and rollback. Before 80s different You can see SQL syntaxes varying from vendor to vendor. Rule 6: View updating Rule "All views that are theoretically updatable are also updatable by the system. Rule 7: High-level insert, update and delete. Rule 8: Physical data independence. Rule 9: Logical data independence. Rule Integrity independence.
Distribution independence. Non-subversion Rule. NET, J etc But many people can contradict it as a large community of Microsoft professional thinks that access is not.
Following is the list of architecture differences between them: There are three types of architecture: Main frame architecture This is not related to the above explanation but just mentioned it as it can be useful during interview and also for comparing with other architectures File sharing architecture Followed by ACCESS. In Main Frame architecture all the processing happens on central host server. User interacts through dump terminals which only sends keystrokes and information to host.
All the main processing happens on the central host server. So advantage in such type of architecture is that you need least configuration clients.