Interview Questions.

Question 1. What Is Tsql?

Answer :

TSQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several functions to the Structured Query Language (SQL) consisting of transaction manage, exception and error dealing with, row processing, and declared variables. Microsoft's SQL Server and Sybase's SQL server assist T-SQL statements.

Question 2. How To Use Sql Query To Copy Only Structure?

Answer :

select * into table2 from table1 where 1 = 2

PL/SQL Interview Questions
Question 3. How To Query A String Contains %?

Answer :

SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'.

Question 4. What Is Patindex?

Answer :

Returns the beginning position of the primary prevalence of a sample in a particular expression, or zeros if the sample isn't determined.
Syntax - PATINDEX ( '%sample%' , expression )
Eg: USE AdventureWorks;

USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%', DocumentSummary)
FROM Production.Document
WHERE DocumentID = three;
GO
SQL Database Tutorial
Question 5. How To Update 'sure' To 'no' And Viceversa In A Query?

Answer :

Update tablename set ColumnName1 = (case ColumnName1 while 'Yes' then 'No' else 'Yes' stop).

MSBI Interview Questions
Question 6. How To Create Temporary Table? How Do We Apply Noncluster Index? What Is Nolock? When And Where Is Nolock Applied Normally?

Answer :

Two ways of creating transient desk with non clusterindex implemented on it. Also instance suggests the way to apply "nolock". Nolock is usually carried out while querying on manufacturing servers. This might make the records being queried sharable at the desk. Ie, will not save you other queries from querying the same document parallely on same table. The danger may be nolock might return junk facts some instances because the choose question might be querying the table at the same time as some different insertion or updation commands are completed on the table.

CREATE TABLE #tmpTable
(
OfficeName varchar(50)
, officeid int
, CustID int
, AgentID int
, mlsid varchar(four)
, RequestMoreDetails int null
, Emails int null
)
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)
pick out
OfficeName
, officeid
, o.CustID
, AgentID -
, o.Mlsid
, PrintBrochure_Views = null
, RequestMoreDetails = null
, Emails = null
into #ForOffices from #Offices 
LEFT JOIN dbo.Planparts WITH (NOLOCK)
ON bppa.Officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)
Question 7. Difference Between Sql Server 2000 & Sql Server 2005 Features Or New Features Of 2005 Vs 2000?

Answer :

Ranking capabilities (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
Exception handling (TRY-CATCH block)
CTE (Common Table Expressions)
PIVOT, UNPOVIT
CUBE, ROLLUP & GROUPING SET
SYNONYMS.
MySQL Tutorial SQL Database Interview Questions
Question 8. What Tools Do You Use For Performance Tuning?

Answer :

SQL Server 2000 includes numerous gear you can discover useful while performance tuning your SQL Server packages. The consist of:

Query Analyzer
Profiler
Index Wizard
Performance Monitor.
Question nine. How Can You Execute An Sql Query From Command Prompt?

Answer :

By using OSQL & SQLCMD we are able to execute an square question from command set off.

MySQL Interview Questions
Question 10. Difference Between Delete & Truncate Statement? Which Statement Can Be Rollbacked?

Answer :

With DELETE we are able to provide conditional WHERE clause to dispose of/delete precise rows, which isn't always possible with TRUNCATE.
TRUNCATE is faster than DELETE as Delete continues log of each row it deletes in transaction logs, but truncate continues log of simplest de-allocated pages in transaction logs.
Both statements may be rolled subsidized if furnished in a transaction (BEGIN TRANS). If now not then none of them can be rollbacked.
DELETE is DML much like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP.
DB2 Using SQL Tutorial
Question eleven. How Can You Execute A Dos Command From Sql Or Through Sql Query By Using Xp_cmdshell?

Answer :

exec xp_cmdshell 'dir c:*.Exe'
xp_cmdshell 'command_string' [, no_output]
Arguments
'command_string'
Is the command string to execute on the running-device command shell. Command_string is varchar(8000) or nvarchar(4000), and not using a default. Command_string cannot contain more than one set of double citation marks. A unmarried pair of citation marks is essential if any areas are present within the document paths or software names referenced by way of command_string. If you have problem with embedded spaces, don't forget the use of FAT eight.Three file names as a workaround.
No_output
Is an non-obligatory parameter executing the given command_string, and does now not go back any output to the customer.

DB2 Using SQL Interview Questions
Question 12. Why You Should Not Use A Cursor? What Are Its Alternatives?

Answer :

If viable, keep away from using SQL Server cursors. They generally use loads of SQL Server sources and decrease the overall performance and scalability of your packages. If you need to carry out row-with the aid of-row operations, try to locate every other technique to perform the project. Here are a few options to using a cursor:

Use WHILE LOOPS
Use temp tables
Use derived tables
Use correlated sub-queries
Use the CASE announcement
Perform multiple queries.
PL/SQL Interview Questions
Question thirteen. What Are The Multiple Ways To Execute A Dynamic Query?

Answer :

EXEC sp_executesql, EXECUTE()
EXECUTE (or EXEC) vs sp_executesql
EXECUTE:
EXECUTE executes a command string or person string within a TSQL batch, or one of the following modules: device stored procedure, person-defined stored system, scalar-valued consumer-defined feature, or prolonged stored manner. The TSQL question can be an immediate string or a variable of char, varchar, nchar, or nvarchar statistics type.
Sp_executesql:
sp_executesql executes a TSQL declaration or batch that can be reused generally, or one that has been built dynamically. The TSQL assertion or batch can comprise embedded parameters. The SQL question is a Unicode string or a Unicode variable that contains a Transact-SQL statement or batch. Here the variable datatype is constrained to Unicode nchar or nvarchar most effective. If a Unicode steady (SQL string) is used then the it have to be prefixed with N.

DocumentDB SQL Tutorial
Question 14. Difference Between Coalesce() & Isnull()?

Answer :

ISNULL accepts best 2 parameters. The first parameter is checked for NULL fee, if it's miles NULL then the second parameter is lower back, in any other case it returns first parameter.
COALESCE accepts  or extra parameters. One can follow 2 or as many parameters, however it returns best the first non NULL parameter.
ISNULL does no longer implicitly converts the datatype if each parameters datatype are distinctive. On the opposite side COALESCE implicitly converts the parameters datatype so as of higher precedence.
Similar to above point ISNULL continually returns the price with datatype of first parameter. Contrary to this, COALESCE returns the datatype value in step with the priority and datatype compatibility.
Question 15. What Should Be The Ideal Combination With In & Union (all) In Terms Of Performance?

Answer :

SELECT *FROM
WHERE
IN (SELECT… UNION SELECT…)
OR
SELECT * FROM
WHERE
IN (SELECT… UNION ALL SELECT…).
SQL DBA Interview Questions
Question 16. What Are Cube & Rollup Sets?

Answer :

CUBE & ROLLUP are the grouping units used with GROUP BY clause and are very useful in developing reports.

Question 17. What New Indexes Are Introduced In Sql Server 2005 In Comparison To 2000?

Answer :

Spatial
XML.
SQL Interview Questions
Question 18. How Many Types Of Functions (udf) Are There In Sql Server?

Answer :

SQL Server supports especially three kinds of UDFs:

Scalar functions
Inline Table-valued features
Multi-statement Table-valued features.
MSBI Interview Questions
Question 19. How Will You Handle Exceptions In Sql Server Programming?

Answer :

By the usage of TRY-CATCH constructs, placing our SQL statements/scripts inside the TRY block and error handling in the CATCH block.

Question 20. What Are The Virtual Tables In Triggers?

Answer :

The virtual tables in Triggers are:

Inserted
Deleted.
PL/SQL and Informatica Interview Questions
Question 21. Can Stored-tactics Be Recursive? And Upto How Much Level?

Answer :

Yes, 32 levels.

Question 22. How Can You Load Large Data In Sql Server?

Answer :

BulkCopy is a tool used to replicate big quantity of statistics from tables. BULK INSERT command helps to Imports a information document into a database desk or view in a person-unique format.

Question 23. How Can You Copy Schema From One Sql Server To Another?

Answer :

DTS, import/export wizard.
Scripting out Database gadgets.
SSIS(SQL Server Integration Services) Interview Questions
Question 24. What Is A Table Called That Has ## Before Its Name, What Is Its Scope?

Answer :

Table with ## (double pound symptoms) is called Global Temp desk. Scope is outside the session but most effective until the authentic consultation lasts.

SQL Database Interview Questions
Question 25. What Is The Scope Of A Temporary Table?

Answer :

Scope is restricted to its session simplest.

Question 26. What Are The Various Isolation Levels?

Answer :

Read Uncommitted Isolation Level
Read Committed Isolation Level
Repeatable Read Isolation Level
Serializable Isolation Level
Snapshot Isolation Level
Read Committed Snapshot Isolation Level.
MYSQL DBA Interview Questions
Question 27. How Would You Know That If A Cursor Is Open Or Closed?

Answer :

  declare @mycursor cursor 
  claim @FirstName varchar(12) 
  select CURSOR_STATUS('variable','@mycursor') --// -2 (Not relevant) 
  set @mycursor = cursor for 
  pick out FirstName from Person.Contact
  pick CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed)
  open @mycursor 
  choose CURSOR_STATUS('variable','@mycursor') --// 1 (The end result set of the cursor has as a minimum one row) 
  fetch subsequent from @mycursor into @FirstName 
  select CURSOR_STATUS('variable','@mycursor') --// 1 (The end result set of the cursor has as a minimum one row) 
  close @mycursor 
  pick out CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed) 
  deallocate @mycursor 
  choose CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable) 
  select CURSOR_STATUS('variable','@nocursor') --// -3 (A cursor with the specified call does no longer exist) 
MySQL Interview Questions
Question 28. How Many Non-clustered Indexes Can You Have In A Table?

Answer :

Upto 249 non-clustered indexes may be created in a table.

Question 29. What All Indexes Can You Have In A Table?

Answer :

One Clustered Index, one or a couple of non-clustered index, precise index, filtered, spatial, xml, and so on.

PL/I Interview Questions
Question 30. Difference Between Varchar & Varchar2?

Answer :

VARCHAR2 is precise to Oracle. MS SQL Server has VARCHAR & VARCHAR(MAX) statistics kinds.

Question 31. What Is De-normalization?

Answer :

De-normalization is the process of trying to optimize the performance of a database via including redundant statistics. It is every so often important due to the fact contemporary DBMS put into effect the relational version poorly. A authentic relational DBMS would permit for a totally normalized database on the logical stage, whilst presenting bodily garage of statistics that is tuned for high overall performance. De-normalization is a technique to move from better to lower everyday forms of database modeling in an effort to accelerate database get admission to.

Question 32. Types Of Replication?

Answer :

Microsoft SQL Server 2005 presents the following sorts of replication for use in allotted applications:

Transactional Replication
Merge Replication
Snapshot Replication.
DB2 SQL Programming Interview Questions
Question 33. What Can You Do With Colasce Function?

Answer :

Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...N ] )
expression
Is an expression of any type.

DB2 Using SQL Interview Questions
Question 34. What Are Integrity Constraints?

Answer :

Integrity constraints are used to ensure accuracy and consistency of facts in a relational database. Data integrity lets in to define certain information exceptional necessities that the information inside the database desires to fulfill. If a person tries to insert facts that doesn’t meet those requirements, the RDBMS will now not allow so.
A constraint is a property assigned to a column or the set of columns in a desk that forestalls positive sorts of inconsistent statistics values from being located within the column(s). Constraints are used to enforce the information integrity. This ensures the accuracy and reliability of the statistics in the database.

Question 35. What Is The Sequence For Logical Query Processing, What Is The Order?

Answer :

FROM, [JOIN CONDITION, JOIN TABLE ...], ON, OUTER, WHERE, GROUP BY, CUBE/ROLLUP/GROUPING SETS, HAVING, SELECT, DISTINCT, ORDER BY, TOP.

Question 36. How Will You Rename A Table?

Answer :

By using sp_rename stored method.

SQL DBA Interview Questions
Question 37. What Are Acid Properties, Define Them?

Answer :

A – Atomicity (Transaction is atomic, if one component fails, then the entire transaction fails).
C – Consistency (Any transaction the database plays will take it from one consistent state to every other, most effective legitimate statistics will be written to the database).
I – Isolation (Other operations cannot get right of entry to data that has been modified at some stage in a transaction that has no longer yet completed).
D – Durability (On a transaction’s success the transaction will no longer be lost, the transaction’s data modifications will continue to exist system failure, and that every one integrity constraints were glad).

Question 38. What Is The Highest, Lowest & Default Isolation Level?

Answer :

Highest: SERIALIZABLE
Lowest: READ UNCOMMITTED
Default: READ COMMITTED

Question 39. How Will You Know Index Usage On Tables?

Answer :

Execution plan
SET STATISTICS PROFILE ON.
Question forty. What Are The Various Ssis Logging Mechanisms?

Answer :

Text document
SQL Server Profiler
SQL Server
Windows Event Log
XML File.
SQL Interview Questions
Question forty one. What Is Collation?

Answer :

Each SQL Server collation specifies three homes:

The sort order to apply for Unicode data sorts (nchar, nvarchar, and ntext). A kind order defines the sequence in which characters are looked after, and the way characters are evaluated in comparison operations.
The type order to use for non-Unicode person records kinds (char, varchar, and textual content).
The code page used to keep non-Unicode individual data.
Question 42. What Output Will "pick out 1/2" Statement Give?

Answer :

It will supply 0.

PL/SQL and Informatica Interview Questions
Question 43. What Are The Steps Involved In Database Partitioning?

Answer :

This entails 4 steps:

Create Database with exclusive file companies
Create Partition Function
Create Partition Scheme
Create Partitioned Table or Index.
Question 44. How Many Types Of Recovery Models Are Available For A Database?

Answer :

Simple
Bulk logged
Full.
Question 45. How Many Types Of Temporary Tables Are There In Sql Server?

Answer :

Local Temp tables (#)
Global temp tables (##)
Table variables (@).
Question 46. What Are The Required/obligatory Parameters That Have To Be Passed With Raiseerror Statement?

Answer :

message _id or message_str
severity, and
state.
Question forty seven. What Are Row Constructors?

Answer :

New manner to insert more than one statistics in a table with INSERT declaration. Also called Table Value Constructor.

Question forty eight. How Many Types Of Internal Joins Are There In Sql Server?

Answer :

Nested loop join
Merge be part of
Hash be a part of.
Question forty nine. What Are Pages In Sql Server? How Many Types Of Pages Are There?

Answer :

A page in SQL Server is an 8 KB data garage place.
There are eight varieties of pages:

Data
Index
Text/Image
Global Allocation Map, Shared Global Allocation Map
Page Free Space
Index Allocation Map
Bulk Changed Map
Differential Changed Map.
Question 50. How Many Data Sources Are Available In Ssis?

Answer :

DataReader Source in 2005 & ADO NET Source in 2008 & above.
Excel Source
Flat File Source
OLE DB Source
Raw File Source
Script Component
XML Source.
Question fifty one. How Will You Deploy An Ssis Package In Testing, Staging & Production Environments?

Answer :

The dtutil is the command spark off software that is used to manage SSIS programs.
This software can reproduction, flow, delete or verify the lifestyles of a package.