Tuesday 17 June 2014

SQL/SSRS Interview questions I thought of blogging some SQL/SSRS interview questions.


Below are some.
I will add more, when I complete the compilation

1. What is OLTP(Online Transaction Processing)?

OLTP stands for Online Transaction Processing.
It is mainly used, when you have frequent inserts/deletes/updates.
OLTP will have normalized tables.

OLAP stands for Online Analytical processing.
OLAP is used, when we need historical data to be preserved and do not have frequent updates/deletes/inserts.
Tables are denormalized, and are used for multi-dimensional analysis/modelling.
OLAP forms the basis for data mining and knowledge discovery
2. What is normalization and what are the different forms of normalization?

Normalization is a way to arrange data in your database.
Primary goal being to reduce redundancy of data.
There are 6 normal forms (http://en.wikipedia.org/wiki/Database_normalization)
Generally most of the databases are normalised upto 3 NF (NF – normal form)
First Normal Form (1NF): No repeating columns or groups of columns
Second Normal Form (2NF): No partial dependencies on a concatenated key.
Third Normal Form (3NF): No dependencies on non-key attributes
Fourth normal Form (4NF) : Isolate independant multiple relationships
Fifth normal Form (5NF): Isolate semantically related multiple relationships
3.What are views?

A view is a kind of virtual table or a stored query.
The result set of the SELECT statement forms the virtual table returned by the view.

Example:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
4. What are indexed views?

A view with a unique clustered index is an indexed view.

Example:

CREATE TABLE sample_table(
A            INT PRIMARY KEY, 
B int
)
GO

CREATE VIEW sample_view WITH SCHEMABINDING AS
SELECT A, B
FROM dbo.sample_table
GO

CREATE UNIQUE CLUSTERED INDEX idx_MyView ON sample_view(B)


5. What are stored procedures?

Stored procedures are group of SQL statements that are created previously and stored.
Stored procedures can accept parameters, and also have output parameters.
Permisions can be modifed for stored procedures.

Example: 

CREATE PROCEDURE sp_Sample
@param varchar(10)
AS
SELECT A, B
FROM sometable
WHERE B = @param


EXECUTE sp_Sample ‘ABC’

6. Give an example for Stored Procedures with parameters and output parameters.
CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @managerID INT OUTPUT
AS
BEGIN
   SELECT @managerID = ManagerID 
   FROM HumanResources.Employee 
   WHERE EmployeeID = @employeeID
END
7.What is xml data type?
The xml data type lets you store XML documents and fragments in a SQL Server database.

Example:

8. Explain filestream with an Example.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) 

data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. 

Example: http://www.youtube.com/watch?v=ONYjKN21aIw
9.Explain stored procedure, function and the difference between them?
Stored procedures are group of SQL statements that are created previously and stored.
User defined function is a database object which has one or more sql statements , which can accept zero or more parameters and return 
either a value or table.

Difference:
1. Stored Procedure can have output parameters – UDF ‘s cannot.
2. Stored Procedures return integers – UDF’s can return scalar or tables.
3. Stored procedures can have Insert/Delete/update Statement – UDF’s cannot
4. Stored Procedures are compiled at compile time – UDF’s are compiled at runtime.
5. Stored procedure cannot be used in the select/where/having clause – UDF’s can be

Example: 
10. When do we use a having clause and when do we use a where clause?
Having Clause applies to groups – hence used with groupby clause.
Where is applied row by row

Example:
SELECT A, AVG(B)
FROM some_table
GROUP BY A
HAVING AVG(B) >10
11.Explain user defined functions and the difference forms of UDF
12. What are recursive stored procedures?
Stored Procedures calling itself in a repetative fashion are called Recursive stored procedures.
13.What are the different error handling techniques?
14. Write a query to find the second/third/nth largest Salary in a table: Emp(EmpID, EmpName, Salary)
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary 

15. What is a trigger? What are the different types of them?
Answer: A trigger is an event (set of DDL statements) that is automatically fired when some sort of an insert/delete/update occurs on a 
database.
Triggers can be nested.

Types of triggers:
DDL trigger 
DML trigger (instead of/after trigger)


Example:
CREATE TRIGGER T1
ON TABLE_NAME
AFTER INSERT
AS
BEGIN
ROLLBACK TRANSACTION
END
GO
16.Explain linked server with an example.
A Linked Server is a connection to an external data source. The remote data source can be DB2, Oracle, Access or any other data source that uses an OLE DB provider.

Example:

 sp_addlinkedserver [ @server = ] ‘server‘ 
    [ , [ @srvproduct = ] ‘product_name‘ ]
[ , [ @provider = ] ‘provider_name‘ ]
[ , [ @datasrc = ] ‘data_source‘ ]
[ , [ @location = ] ‘location‘ ]
[ , [ @provstr = ] ‘provider_string‘ ]
[ , [ @catalog = ] ‘catalog‘ ]
 17.Explain collation and collation sensitivity
Collation:  Collation controls the way string values are sorted. The default collation sorts alphabetically using the standard Latin character set. Other collations will sort in different orders Collation sensitivity:

Width sensitivity: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

Accent sensitivity:  If a and á, o and ó are treated in the same way, then it is accent-insensitive.

Case sensitivity:  If A and a, B and b, etc. are treated in the same way then it is case-insensitive. SQL distinguishes using ASCII values of A(65) and a(97)

Kana Sensitivity:  When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive
18.What is primary key/Unique Key and difference between them?

Primary key:  The key of a relational table that uniquely identifies each record in the table. Primary Key doesn’t allow NULLS
                            Clustered Indexes are created on Primary Keys.

Unique Key:   Unique Key enforces uniqueness of the column on which they are defined. Unique Key allows one NULL value
                     Non-Clustered indexes are created on Unique Keys.          
19.Explain 1:1, 1:m, m:m relationships1:1 -In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

1:many – In 1:many type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. 

many:many: In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. 20.Explain delete and truncate statement and the differenceDelete:  The DELETE Statement is used to delete rows from a table. 
DELETE is a logged operation on a per row basis
Example:
DELETE FROM table_name [WHERE condition]; 

Truncate:  The TRUNCATE command is used to delete all the rows from the table and free the space containing the table 

21.Explain the merge statement with an example.
Merge Statements perform insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. 
22.What is an index? What are the different types of indexes? what is the difference between them?

An index can be created in a table to find data more quickly and efficiently. it is a structure within SQL that is used to quickly locate specific rows within a table.

CLUSTERED INDEX: Clustered indexes define the logical order of the table. The leaf level of the clustered index has the actual data pages of the table. Because of this there can only be one clustered index per table. 
A table that does not have a clustered index is referred to as a heap

NON-CLUSTERED INDEX:  Here, the leaf level of a nonclustered index has a pointer as part of each index row. That pointer is either the clustered index key in the cases where the base table has a clustered index or the Row Identifier in the cases where the table is a heap 

23.Difference between char and nvarchar / char and varchar data-type?

char [ ( n ) ] : Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. 
varchar [ ( n | max ) ] : Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). 
nvarchar [ ( n | max ) ] : Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). 
24.What are joins? What are the different types of joins?
Joins are used to query data from more than 2 tables, based on a relationship between columns in these tables.

JOIN: Return rows when there is at least one match in both tables.
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

SQL/SSRS Interview questions I thought of blogging some SQL/SSRS interview questions.

Below are some. I will add more, when I complete the compilation 1. What is OLTP(Online Transaction Processing)? OLTP stands ...