SQL Server Database Engineer: Build Robust Data Systems

Location: Germany

Technical Round Database Interview Questions

1) What are the different types of Trace flags which we used for detecting the Deadlock?
2) What are the different types of database backups?
3) How do you perform the Error handling in SQL Server?
4) What is the ACID property in SQL Server?
5) What are the best practices in Database programming?
6) What are the different isolation level in SQL Server
7) What is the default isolation level in SQL Server
8) What is dirty read in SQL Server
9) What are the Serializable isolation level in SQL Server
10)
What are the Rank functions in SQL server
11) What is Replication in SQL Server
12) What is the stuff function in SQL Server?
13) What is the Difference between Stuff command and Replace command in SQL Server
14) What is the merge command in SQL Server?
15) What is the Pivot/Unpivot table in SQL Server?
16) What is Derived table in SQL Server?
17) What is the Output clause in SQL Server?
18) What is the temporary tables in SQL Server?
19) What are the different types of database in SQL Server?
20) What is CTE in SQL Server?
21) What are the benefits of using CTE SQL Server
22) What are cursors in SQL Server?
23) What are Views in SQL Server?
24) What are Triggers in SQL Server?
25) What are magic tables in SQL Server?
26) What are the difference between Stored Procedure and SQL triggers in SQL Server?
27)
If no rows are affected by the SQL query, will after trigger will execute?
28) How can we improve the performance of an stored procedure?
29) What are the heap table in SQL Server?
30) Where we need to use table variable and where we should use temporary tables?
31) What is log shipping in SQL Server?
32) What is fragmentation in SQL Server?
33) What is the difference between logical and physical fragmentation?
34) In which database temporary tables are created?
35) What is the difference between Except command and Intersect command?
36) What is the BCNF in SQL Server?
37)
What is database collation?
38) Suppose there are two
tables A and B and we need to write 3 SQL queries which returns
therecord setas shown in the below figure.

39) Suppose we have to
design a database in which employees can work on multiple projects.

There
are many designation defined in the organization to which each employee
can belongs.

For
example, Software engineer, Team lead, Project Manager, Project lead,
QC engineer, QC Lead, QC manager etc. An employee can belong to one
designation
at a time. It means at a time an employee can either be Software
engineer or team lead or project manager but not software engineer as
well team lead at a same time.

Trending Importance of Best Practices in database programming

Also the projects can work on different technologies like .Net, SQL
Server, Oracle, HTML5 etc. Every project has a start data and end date. A
project can work can include many technologies like .net, SQL Server ,
Java script, HTML5 etc. Most of the time every project work include a
front end server technology like JAVA. Asp.net, uses Java script, HTML,
CSS as client side technologies and SQL Server, oracle as back end
database server. So In this ways a project work can include many
technologies as in above example, suppose a project can use Asp.net as
server end front end technology, java script, HTML, CSS, AJAX as client
side technology and SQL server as back end database server.

What should be the design the database schema (tables, their foreign keys, primary keys etc.?

40) Suppose we have a table named  have only one column say “charactername”.

It contains 10 rows. First 5 rows contains “Vivek Johari” and the next 5 rows contains “Abhinav Golwalkar”  ——————— Vivek Johari Vivek Johari Vivek Johari Vivek Johari Vivek Johari Abhinav Golwalkar Abhinav Golwalkar Abhinav Golwalkar Abhinav Golwalkar Abhinav Golwalkar Now we need a SQL Query which can return the result in the form Desired Result:

- Vivek Johari Abhinav Golwalkar Vivek Johari Abhinav Golwalkar Vivek Johari Abhinav Golwalkar Vivek Johari Abhinav Golwalkar Vivek Johari Abhinav Golwalkar

Query should work even if the table has 30 rows with first 15 contains “vivek johari” and next 15 contains “Abhinav Golwalkar” or 40 rowswith first 20 contains “vivek johari” and next 20…