SQL Interview questions

What is Self Join?
This is a particular case when one table joins to itself, with one or two
aliases to avoid confusion. A self join can be of any type, as long as the
joined tables are the same. A self join is rather unique in that it involves a
relationship with only one table. The common example is when company have a
hierarchal reporting structure whereby one member of staff reports to another.

What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product
of the tables involved in the join. The size of a Cartesian product result set
is the number of rows in the first table multiplied by the number of rows in
the second table. The common example is when company wants to combine each
product with a pricing table to analyze each product at each price.

Which virtual table does a trigger use?
Inserted and Deleted.

List few advantages of Stored Procedure.

• Stored procedure can reduced
  network traffic and latency, boosting application performance.
  
• Stored procedure execution
  plans can be reused, staying cached in SQL Server’s memory, reducing
  server overhead.
  
• Stored procedures help
  promote code reuse.
  
• Stored procedures can
  encapsulate logic. You can change stored procedure code without affecting
  clients.
  
• Stored procedures provide
  better security to your data.
  

What is DataWarehousing?

• Subject-oriented,
  meaning that the data in the database is organized so that all the data
  elements relating to the same real-world event or object are linked
  together;
  
• Time-variant,
  meaning that the changes to the data in the database are tracked and
  recorded so that reports can be produced showing changes over time;
  
• Non-volatile,
  meaning that data in the database is never over-written or deleted, once
  committed, the data is static, read-only, but retained for future
  reporting;
  
• Integrated,
  meaning that the database contains data from most or all of an
  organization’s operational applications, and that this data is made
  consistent.
  

What is OLTP(OnLine Transaction Processing)?
In OLTP - online transaction processing systems relational database design use
the discipline of data modeling and generally follow the Codd rules of data
normalization in order to ensure absolute data integrity. Using these rules
complex information is broken down into its most simple structures (a table)
where all of the individual atomic level elements relate to each other and
satisfy the normalization rules.

How do SQL server 2000 and XML linked? Can XML be used to access data?
FOR XML (ROW, AUTO, EXPLICIT)
You can execute SQL queries against existing relational databases to return
results as XML rather than standard rowsets. These queries can be executed
directly or from within stored procedures. To retrieve XML results, use the FOR
XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or
EXPLICIT.

OPENXML
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over
an in-memory XML document. OPENXML is a rowset provider similar to a table or a
view. OPENXML provides a way to access XML data within the Transact-SQL context
by transferring data from an XML document into the relational tables. Thus,
OPENXML allows you to manage an XML document and its interaction with the
relational environment.

What is an execution plan? When would you use it? How would you view the
execution plan?
An execution plan is basically a road map that graphically or textually shows
the data retrieval methods chosen by the SQL Server query optimizer for a
stored procedure or ad-hoc query and is a very useful tool for a developer to
understand the performance characteristics of a query or stored procedure since
the plan is the one that SQL Server will place in its cache and use to execute
the stored procedure or query. From within Query Analyzer is an option called
“Show Execution Plan” (located on the Query drop-down menu). If this option is
turned on it will display query execution plan in separate window when query is
ran again.