T-SQL Interview Questions

Before reading this article. .........................

This post is a continuation of my previous post

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_861.html

7. Fetch the employee based on EmpQualification
select a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID

To avoid duplicates we need to used the distinct keyword
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(5,'Santhi','Jayakantham','Dharmapuri',1000,'PM')

To list down all the rows from the left table with matching columns in the right table. This can be achieved using Left outer join.
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
left outer join EmployeeDetails b on a.EmpID=b.EmpID

Appropriate right tables column will be appended with Null value.
What about getting the all the right table row with corresponding matching column from the left table.

select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
right outer join EmployeeDetails b on a.EmpID=b.EmpID

Getting the matching data from two tables. Suppose I need to list the employees who is having qualification = 'BE'
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID where b.EmpQualification='BE'

8. List down all the managers and their employee ID
select b.ManagerID,a.EmpFirstName as ManagerName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down the employees who is having managers,
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down all the employees with managers details(If exists)
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
right outer join employee b on a.EmpID=b.ManagerID

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL server interview questions

Let see some basic T-SQL. On reading this article, you can straight forward attend an interview or you can take a SQL Server task.

CREATE TABLE EMPLOYEE(EmpID INT PRIMARY KEY , EmpFirstName VARCHAR(100),EmpLastName VARCHAR(100), City VARCHAR(10),ManagerID INT, Salary int ,Designation varchar(10))

CREATE TABLE EMPLOYEEDETAILS(EmpID INT , EmpQualification VARCHAR(100),EmpExtn INT)

1. Adding Constraint between two tables, to update a foreign key relation ship. You need to alter the child table.

ALTER TABLE EMPLOYEEDETAILS
ADD CONSTRAINT emp_for FOREIGN KEY (EmpID)
REFERENCES EMPLOYEE(EmpID)

2. Trying to insert into the child table.

INSERT INTO EMPLOYEEDETAILS VALUES(1,'BE',200)

You will get an error "The INSERT statement conflicted with the FOREIGN KEY constraint"
Its because, you need to populate data in the master table and corresponding column should be populated in the child table.

3. Trying to insert into the master table.

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(1,'Venkat','Prabu','Dharmapuri',1000,'PM')

Output:
(1 row(s) affected)

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary) VALUES(1,'Venkat','Prabu','Dharmapuri',1000)

On executing the above statement, we will get
Violation of PRIMARY KEY constraint 'PK__EMPLOYEE__3C69FB99'. Cannot insert duplicate key in object 'dbo.EMPLOYEE'.
Its because we are trying to insert duplicate key into the primary key column.
Let make a try with other insert statements,

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,ManagerID,Designation) VALUES(2,'Arun','Prabu','Dharmapuri',500,1,'Dev')
INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(3,'Karthi','Prabu','Salem',2000,'Lead')
INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,ManagerID,Designation) VALUES(4,'Lakshmi','Prabu','Chennai',100,3,'Dev')

select * from Employee

Lets make a try to insert a row in the child table.

INSERT INTO EMPLOYEEDETAILS VALUES(1,'BE',200)
INSERT INTO EMPLOYEEDETAILS VALUES(2,'MBA',100)
INSERT INTO EMPLOYEEDETAILS VALUES(3,'BA',10)
INSERT INTO EMPLOYEEDETAILS VALUES(4,'BE',50)
INSERT INTO EMPLOYEEDETAILS VALUES(4,'BE',500)

--delete from Employeedetails where EmpID=1
select * from Employeedetails

4. Employee earning maxiumum salary

select max(salary) from EMPLOYEE

I need the max salary on each designation

select max(salary),designation from Employee Group by designation

I need max salary based on employee city
select max(salary),city from Employee Group by city

I need to check the max salary of the employees who is having salary >=1000 based on city.
select max(salary),city from Employee where Salary >=1000
Group by city

Total salary given to employees by the company
select sum(salary) from Employee

Total salary given to employees grouped by designation
select sum(salary),designation from Employee Group by designation

Total employees available in the company
select count(*) from employee

5. Employee Search

I need an employee with First name as "Venkat"
select EmpID,EmpFirstName from employee where EmpFirstName='Venkat'

I need an employee with Firstname start with "V"
select EmpID,EmpFirstName from employee where EmpFirstName like 'V%'

I need an employee with Firstname having second letter as "e"
select EmpID,EmpFirstName from employee where EmpFirstName like '_e%'

I need an employee with firstname ended with the letter "t"
select EmpID,EmpFirstName from employee where EmpFirstName like '%t'

I need an employee whose city is "Chennai"
select EmpID,EmpFirstName from employee where city ='Chennai'

6. List down the employees having more than one extension number
select empid from employeedetails group by empid having count( EmpExtn)>1

Happy Learning!!

Regards,

Venkatesan Prabu .J

SQL Interview Questions

Local and Global Cursor :
While declaring the cursors, we can specify a cursor as Local or Global cursor.

Local Cursor will exists for the particular object and it cannot be reference by an external object. By default, cursors are Local. If we need to make it as global and can be accessed by all the objects. We need to specify it as global.
If the cursor is declared as Global, it can be accessed by all the objects in the existing connection.

Subquery:
Subquery is an important concept in T-SQL. The outer query will provide the result based on the result given by the subquery.

Select id from table1 where id = (select max(id) from table1) -Red color is the subquery.

The outer table's output "id" depends on the subquery "id" value.
Here equal to (=) is used so that the subquery will retrieve only one id. If the subquery gives more than one value we have to use "IN" operator.
Select id from table1 where id in (select id from table1)
generally, the outer query is waiting for the subquery to finish its execution. So, it will hit the performance in a huge application with complex joins. So, we should avoid subqueries.

Correlated subquery:

These queries are little bit interesting one. The inner query depends on the outer queries input and there will be a mutal co-ordination occurs between these inner and outer query.

select name from venkat1 v

where id in (select id from venkat1 v1 where v.id=v1.id)
In the above query, the inner query depends on the outer queries output.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

SQL Interview questions

Below are some of the maximum capacity for SQL Server 2005: (Frequently asked interview questions in sql server)
Table Level:
Maximum Number of tables : No restrictions (Until your harddisk size you can create tables)
Maximum bytes per row in the table : 8060 bytes
Maximum tables in a select statment - 256
Maxiumu references per table - 253
Maximum rows in the table - Unlimited (Depends on the storage)
Maximum columns per base table : 1024
Maximum columns per insert statment : 1024
Maximum columns per Select statment : 1024 * 4 = 4096
Bytes per Group by or Order by = 8000

Index:
Maximum clustered index per table - 1
Maximum Non-clustered index per table - 249
Maximum Columns in an index (composite index) - 16

Keys:
Maximum Columns in a Primary key - 16
Maximum Columns in a Foreign key - 16

Objects:
Nested Stored procedure levels- 32
Nested triggers levels- 32
Parameters per stored procedure - 2100
Parameters per User defined functions - 2100
Identifier length - 16

Database Level Details:
Number of instances for a server - 50
Files per database - 32767
Filegroups per database - 32767
Databases per instance of SQL Server - 32767

More SQL interview questions,
http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_27.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_11.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-question-part-2.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions.html

Happy learning!!!
Regards,
Venkatesan Prabu .J

SQL Server Interview Questions

RDBMS Vs DBMS:
Database management system is considered as the system to store the data for easy retieval and the data were relatively stored as a group. Whereas, Relational database management system will include a relationship between tables (using constraints). The relationship will be based on their logical relations. Considering,
There are two table "employee" which will hold employee information like, employee id, employee dob, where he is working. Another table "employee details" which may hold his personal information like blood group, identification etc..,
For these two tables, there should be a key which will form a relation ship between these two tables. Its nothing but RDBMS. (Eg: SQL Server, Oracle)

SQL Server Vs MS access:
I used to see this question acess vs sql server,
MS access is a RDBMS and can be considered as client level database. It comes along with Microsoft office package. The database will be formed based on filesystem concept, so it wont support multiuser environment. Its preferred for very small databases. It developed on Jet engine.
SQL Server is a RDBMS and works as a Server level database. It will work for a large group of Users with 24/7 availability and its preferred for high end system with huge data and huge support. Its developed on sql server engine.
Normalization:
Normalization is a very important consideration in designing the databases. Normalization includes various steps like,
1. First normal form
2. Second normal form
3. third normal form
4. Backus naur form
5. Extended Backus naur form
6. Fifth normal form
7. Sixth normal form
The above normal forms are used to avoid redundancy of data. To achieve these, we have to segregate the columns to different tables. So that, duplication should not be there, partial dependency should be avoided etc..,

Denormalization:
As the name indicates, it's opposite to normalisation. Introducing redundancy into the database is referred to as denormalization. The database becomes too complex and too many joins may occur to fetch the data. In that case, we used to prefer this denomalization concept. Narrow idea - Joining the divided or segregated tables.

MSDE vs SQL Server :
MSDE is the downgrade version of SQL Server, the same code with initial release of SQL Server was named as MSDE (Microsoft Desktop Enginer).

Extent Vs Page:
Pages are 8 KB size smallest unit to store the data. Inturn, 8 Pages will form the extent.

Delete VS Truncate:
Delete will delete row by row in the table. The syntax is,
delete from tablename
We can give where conditions for deleting the data.
Each delete will be logged in the log file.
Its a DML statement

Trucate will deallocate the reference in the pages instead of deleting the data.
Its DDL statement. The syntax is,
Truncate table tablename
We can provide truncate with tables having foreign key relationships. (Because it wont do any cascade delete on the table).
It wont log any information in the log file.
Delete or truncate the data at a stretch and wont do any row by row manipulation.

More SQL server Interview questions at,

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_11.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-question-part-2.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions.html

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Order by in view + sql server

I faced a small problem in using order by in views, Let me discuss the exact problem and hack to resolve this problem.

create table venkattable1(id int, val varchar(100))
insert into venkattable1(1,'Venkat')
insert into venkattable1(2,'Venkat')
insert into venkattable1(3,'Venkat')
create view venkatview
as
select * from venkattable1 order by id

I got the below error,
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

It shows that, we can't use order by clause on the view, I've tried with Top clause in the above statement,

create view venkatview
as
select top 10 * from venkattable1 order by id

It's working fine. Please check the bleow URL for more details related to this problem.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3908414&SiteID=1

Problem in loading excel data to SQL Server

While uploading the data from excel sometimes, data wont get populated into sql. Even, we have followed proper steps to upload the data, the data wont get populated. Why??

Reason:
Its because, sometimes the data in the column may be character or Alpha numeric and SSIS will design its package by considering the column as Integer. In this case, if there is any varchar value,

I have explained excels and SSIS basic characteristics in my another post,

http://venkattechnicalblog.blogspot.com/2008/09/excel-column-is-greater-than-255-in.html

The values wont get inserted into the table. In that case, There are two options to resolve this problem,

1. Option1 : Its tricky one, just copy the varchar related row to the top (may be as 1 row).Now try to create your package using import/export wizard.

2. Option 2: Append a Single quotes(') to the front of all the data and after migration, update the column using an update query like,

update tablename set column1=substring(column1,2,leng(column1))

Happy Learning!!!
Regards,
Venkatesan Prabu .J

excel column is greater than 255 in ssis

We used to face some problems like, while migrating data from excel to sql server error occurs due to lengthy data.

SSIS Excel Data Source: Error with output column “Comments” (5691) on output “Excel Source Output” (5596). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

Reason for this error:
1. The error is due to the lengthy data in the excel.
2. SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will written a logic to build the table's logic for the package. If your lengthy data in not in the 8 row then your ssis wont respond it.

Considering am having a column named "Name"
First 8 rows is of length <255
9th row is of length > 255.
While executing the package you will get the above error, its because your input value will be truncated and SSIS wont allow for that. In this case,
Two types of logics can be followed,

Option1 : Its tricky one, just copy the 9th row to the top (may be as 1 row).Now try to create your package using import/export wizard. Your ssis will create column with width nvarchar(Max) which will accept upto 2 GB.

Option2: Change the Preparation SQL task query, change the data type as varchar(Max) and modify the excel source ->advanced editor and change the output columns type and length and external column type and length.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Excel to SQL Server using SSIS

Sometimes, we need to need to fetch the data from excel spread sheet to sql server database. It can be easily achieved using import/export wizard or we can achieve by using SSIS tasks.

Let see how to utilise both options to achieve this great task in a simple manner. Open a new SSIS project in Business Intelligence studio.

Goto Project ->SSIS Import and Export Wizard

Clicking the option, we will get the Import/Export wizard. Browse your excel file and select your file.

Now, clicking next you will get the destination. you can specify your server and database name.

Clicking next, you will get the next screen.

You will be given two options,

either you can copy the entire table or you can write your customized queries to fetch the data from excel sheet. On clicking next you can view the spread sheets available. Here you need to select the spread sheet, from which you need to fetch the data.

On clicking "Preview" button you can view the spread sheet data. Click next button. You will get a successful message indicating your package is created. Now you need to execute the package.

You can view the above tasks in your monitor. Preparation SQL task will create a table and Data flow task is used to transfer the data from Excel source to SQL Server Destination.

Right click the package and execute the tasks.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

Convert Varchar to Datetime in sql server

Am having date, month and year as three columns and I need to append those data and convert into a date time. Let see how to achieve the same,
First,
We need to append those columns using Convert function in SQL Server.

I have tried to execute the below query,
select convert(datetime,
convert(varchar(10),convert(varchar(10),[month])+'/'+convert(varchar(10),[day])+'/'+convert(varchar(10),[year])))
from dbo.VenkatTable

I got this error,
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Reason: Its because of the date format. I am trying to insert the date value into the month but, it wont accept beyond 12. Am i right :-)

I have changed the query. Now, its working fine.

select convert(datetime,convert(varchar(10),convert(varchar(10),[day])+'/'+convert(varchar(10),[month])+'/'+convert(varchar(10),[year])))from dbo.VenkatTable
Happy Learning!!!
Regards,

Venkatesan Prabu .J

SQL Server error

While trying to write my data in to an external file (Like logging the sql output into an external text file). I found this error,

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Reason:
Its because, by default sql server will disable xp_cmdshell property and we need to enable it.
Goto Surface Area Configuration manager and enableit.

Click apply, now you can achieve your tasks.

You can achieve it using T-SQL too.

Clearly you'll need sa privileges on your SQL instance to run this:
sp_configure 'show advanced options',

RECONFIGURE

GO

sp_configure 'xp_cmdshell',

GO

RECONFIGURE

GO

Regards,

Venkatesan Prabu .J

List out all tables and row count in sql server

How to list down all the tables and its row count in SQL Server:

SQL Server will hold all the tables and its row count in a special table named sysindexes.
Below is the query to fetch all the information,

select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows from sysindexes a inner join sysobjects b on a.id = b.id

To list out the information for a particular database, you can use the below query.

select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows
from sysindexes a inner join sysobjects b on a.id = b.id
inner join INFORMATION_SCHEMA.TABLES c on c.[TABLE_NAME]=convert(varchar(30),object_name(a.id))
where c.Table_catalog='Database Name'
Happy Learning!!!
Regards,
Venkatesan Prabu .J

Ltrim / Rtrim in SQL Server

Trim function is a very important function in database part.
Sometimes, our front end application will populate some spaces with out actual data.
While validating the data ( "X" is not equal to " X") we will get improper data. To avoid this, SQL Server provides an option of trimming those white spaces.

For trimming the white space before the data, LTRIM function is used.
For trimming the white space after the data, RTRIM function is used.
For trimming both sides, we will use both function like LTRIM(RTRIM(ColumnName)

DECLARE @VenkatTable TABLE (id int, val varchar(10))
INSERT INTO @VenkatTable VALUES(1,' Venkat')
INSERT INTO @VenkatTable VALUES(1,' Ve nkat')
INSERT INTO @VenkatTable VALUES(1,' Venkat ')
INSERT INTO @VenkatTable VALUES(1,'Venkat ')
SELECT * FROM @VenkatTable
SELECT LTRIM(RTRIM(val)) as val FROM @VenkatTable


Happy Learning!!!
Regards,
Venkatesan Prabu .J

Server restart data in SQL Server

An interesting topic in SQL Server,

Is it possible to find the last restarted datatime of a server?

When my server is started is the question?

OOPS... where this data is stored. Frankly speaking even I dont know. But, I found a work around to achieve the same.

SQL Server is holding a database named 'tempdb' which gets flushed off during server down or server stop and created once sql server is restarted. So, we can fetch the relative data from tempdb's created date.

Below is the query to fetch this information,

SELECT create_date FROM sys.databases WHERE [name]='tempdb'

Happy Learning!!!

Regards,

Venkatesan Prabu .J

SQL Server interview questions

Some more SQL Server interview questions:
What are the authentication modes available in SQL Server?

Three types of authentication available,
1. Windows authentication - SQL Server authenticates based on the login windows user.
2. SQL Server authentication - A specific user leve authentication will be available to login into SQL Server.
3. Mixed mode authentication - It's a combination of windows and SQL Server authentication.


What are indexes, types and uses:
Indexes are nothing but a short cuts or high level idea about the table. SQL Server will have a high level idea about the data located. Its used to retrieve the data faster.
Two types of indexes available,
1. Clustered Indexes. - Only one index is available and it will physically sort the table.
This will hold the data in the leaf level and if a primary key is created. It will automatically create a clustered index. Clustered index is too heavy and took more memory.
2. Non- clustered indexes - 249 index can be created for a table.
This will hold the reference in the leaf level. Comparatively non clustered index will occupy less memory.

Rebuild index Vs Reorganize index:
Its a very important question and we need to know it for freshing up our knowledge.

1. Rebuilding index is nothing but, scapping all the indexes and create fresh index so that the pages will be compressed and all the datas will be formatted (Storage wise) Where as reorganizing the index will do a high level compaction and it wont delete the existing index.
2. We should not rebuild the index duing production time and it can be done during system downtime where as reorganising can be done during run time.

What do you mean by acid property?

Atomicity : The value should be atomic.

Consistency : The data in the database should be consistent. It indicates, if we are maintaining data in two different places. Those data should resemble. If it differs, then it will create confusions. Am I right?

Isolation : Transaction should be isolated and it should not affect the other transactions. If the transactions occured on the same data. Appropriate locks should be posted before starting the transactions.

Durability: The data should be stable.

Regards,

Venkatesan Prabu .J

ORDER BY CASE error in SQL Server

Considering am having two tables,

create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10000,1)
insert into employees values(2,'bb','pm',10000,1)
insert into employees values(3,'cc','pl',500,2)
SELECT * FROM employees

create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails

Now I need to fetch the data from the table in the order based on the id value.
If the ID has the value as 1 then the order should be based on the first column "ID" else, the order should be based on the second column "empName" Lets see, how to achieve the same,

select empid,empname from employees
union all
select empid,'a' as empname from employeedetails
order by
case
when empid=1 then 0
else 1
end
You will get an error,
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
In this case, we should do a work around to achieve the result, Let me try with derived tables concept,
select * from
(select empid,empname from employees
union all
select empid,'a' as empname from employeedetails )
t
order by
case
when t.empid=0 then 0
else 1
end
Now, we will get the exact output.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

TSQL Interview Questions - Part2

T-SQL Interview Questions: (SQL Server Interview questions)
Considering am having two tables,

Considering am having two tables,
create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10000,1)
insert into employees values(2,'bb','pm',10000,1)
insert into employees values(3,'cc','pl',500,2)
SELECT * FROM employees
SELECT * FROM employees
create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails

The table structure should resemble as below,

Employees (empid, empname, Designation, salary, mgrid )
EmployeeDetails (empid, City, Telephone)

5. Group the employees based on the designation. Need the number of employees in each designation
SELECT Designation,COUNT(Designation) FROM employees GROUP BY Designation

6. Retrieve the number of employees having more than one phone number
SELECT distinct * FROM employeedetails A
INNER JOIN
(select empid,telephone,count(*) as cnt from employeedetails group by empid,telephone)t
ON A.empid = t.empid
WHERE t.cnt>1
7. Select the details of 3 max salaried employees from employee table.
SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC

8. Update 5 percent salary increment for all employees

UPDATE employees SET Salary = (Salary *105)/100
9. Display all managers from the table. (manager id is same as emp id)

select * from employees where empid IN (SELECT DISTINCT mgrid FROM employees)

10. Listing all the employees with Managers available
select a.empid,a.empname,b.mgrid,b.empname from employees a
inner join employees b
on a. mgrid =b.empid

Regards,
Venkatesan Prabu .J

T-SQL Interview Questions - Part 1

T-SQL Interview Questions: (SQL Interview questions)
Considering am having two tables,

create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10,1)
insert into employees values(2,'aa','pm',10,1)
SELECT * FROM employees
create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails

The structure of the table resembles,

Employees (empid, empname, Designation, salary, mgrid )
EmployeeDetails (empid, City, Telephone)

1. Select all employee with Salary>2000
Select empid, empname, Designation, salary, mgrid FROM employees
WHERE Salary >2000

2. Select all employee with Designation "Project Manager"
Select empid, empname, Designation, salary, mgrid FROM employees
where Designation='Project Manager'

3. Select all employee who doen'thave Telephone
SELECT DISTINCT A.empid, A.empname, A.Designation, A.salary, A.mgrid, B.city, B.Telephone FROM
employees A INNER JOIN EmployeeDetails B
ON A.empid=B.empid WHERE B.Telephone IS NOT NULL AND B.Telephone <> ''
(OR)
SELECT empid, empname, Designation, salary, mgrid
FROM employee
WHERE (empid IN
(SELECT empid
FROM EmployeeDetails where Telephone IS NOT NULL AND Telephone <> ''
GROUP BY empid

4. Select all employee belong to the city 'Sydney'
SELECT DISTINCT A.empid, A.empname, A.Designation, A.salary, A.mgrid, B.city, B.Telephone FROM
employees A INNER JOIN EmployeeDetails B
ON A.empid=B.empid WHERE B.City='Sydney'

Regards,
Venkatesan Prabu .J

Stored procedure output into table in sql server

Stored procedure output into the table:
I have seen lots of lots people are querying about, projecting stored procedure output
into a table.
I will take an extended procedure to explain this concept,

exec xp_fixeddrives
The above stored procedure is used to fetch the amount of space available in our hard disk.
My scenario is used to fetch the output of this SP and populate the same in my table.
We used to try the below query to achieve the same,

SELECT * INTO temp_table
exec xp_fixeddrives
OOps, am getting an error,
An object or column name is missing or empty.
For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names.
Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

Whats the reason???. Its because, as per the syntax we have to specify the table and we can't specify the stored procedure or execute stored procedure in this context.
Lets see some work around for this problem,

In this case, we need to create a temp table or table and afterwards we have to do an insert statement.

CREATE TABLE temp_table (drive varchar(15),MBfree varchar(500))
INSERT INTO temp_table exec xp_fixeddrives
SELECT * FROM temp_table

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Select * Vs Select column Name in SQL Server

In general, we used to put a select statement like,

Select * from TableName
Its one of the bad coding standard to retrieve the data from a particular table.
In turn, we have to specify the column name for the select statement.
The format should be,

Select ColName1, ColName2 etc.., from TableName
It will surely increase the performance of your query. Because, we are filtering the wanted column in the table + we specifying the exact column in the table.

Regards,
Venkatesan Prabu .J

Page Vs Extent in sql server

Extent Vs Page?
Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.
The size of the page is 8KB.
Eight consecutive pages will form an extent 8 * 8KB = 64KB. Thus I/O level operation will be happening at pages level.
The pages will hold a template information at the start of each page (header of the page). They are,
1. page number,
2. page type,
3. the amount of free space on the page,
4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents
2. Mixed extents

Uniform Extents:
It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.
Mixed Extents:
Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :
Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.
Herecomes, our fragmentation and reindexing concepts.
Regards,
Venkatesan Prabu .J

Copy table structure in SQL Server

Copy only structure with out data from SQL Server table:
Am creating a table name Table1,

CREATE TABLE Table1(id int primary key,nam varchar(10))
I am creating the second table Table2.
CREATE TABLE Table2(id1 int primary key, nam varchar(10))
Now, am trying to link these two tables using a column named id in first table with id in the second table.

ALTER TABLE Table2
ADD FOREIGN KEY (ID1) REFERENCES
Table1(ID)
Way to copy the structure of the table:
Using below query, we can copy the structure of the table without any data.

SELECT * INTO NewTable FROM Table1 WHERE 1=2
SQL Server will first create the table and insert the data. Since, our where clause fails due to the condition,
only structure will be created without any data.

SELECT count(*) FROM NewTable
The output will be 0
Happy Learning!!!
Regards,
Venkatesan Prabu .J

Copy table data in SQL Server

Copy table in sql server:
In our database world, we used to face this situation frequently. You will be given a table and requested
to copy those data into another table. Let's see how to achieve this scenario,
Am creating a table name Table1,

CREATE TABLE Table1(id int primary key,nam varchar(10))
I am creating the second table Table2.

CREATE TABLE Table2(id1 int primary key, nam varchar(10))
Now, am trying to link these two tables using a column named id in first table with id in the second table.

ALTER TABLE Table2
ADD FOREIGN KEY (ID1) REFERENCES
Table1(ID)
First way of copying source table data:
In this case, a new table can be created similar to the structure of the source table
and after wards, an insert statement can be fired

CREATE TABLE NewTable1(id int primary key,nam varchar(10))
INSERT INTO NewTable1(id,nam) SELECT id,nam FROM Table1
Second way to copy data from a table:
In this case, a table of similar structure will be created and data will be inserted into the table.
The new table structure resembles the old table structure.

SELECT * INTO NewTable2 FROM Table1
Oops!!! In the second case, I am doubting about the constraints. Whether the constraints is copied in the new table. No, its not the case.

Only the structure and the data will get copied to the new table and we need to create our constraints on the new table.
Lets check it by executing the below query,
SP_HELP Table1-- [sp_help will provide the details about the system object]

Let's check with the new table created.

SP_HELP NewTable2

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Foreign key in SQL Server

Am creating a table name Table1,

CREATE TABLE Table1(id int primary key,nam varchar(10))
I am creating the second table Table2.

CREATE TABLE Table2(id1 int primary key, nam varchar(10))
Now, am trying to link these two tables using a column named id in first table with id in the second table.

ALTER TABLE Table2
ADD FOREIGN KEY (ID1) REFERENCES
Table1(ID)
Foreign key: It forms a relation ship between the Master table and Child table.
Here Table1 is the master table where as, Table2 is the child table.
It inturns define a basic rule, "Master table should have a record before inserting into child table".
Let's try to insert some data in the child table without inserting appropriate records in the master table.

INSERT INTO Table2 VALUES(2,'VP')
We will get an error indicating, foreign key constraint wont allow for this.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Table2__id1__2898D86D".
The conflict occurred in database "master", table "dbo.Table1", column 'id'.

Let me try to insert some value in master table.

INSERT INTO Table1 VALUES(2,'VP')
Output is,
(1 row(s) affected)
Let's make a try with Table2.

INSERT INTO Table2 VALUES(2,'VP')
Output is,
(1 row(s) affected)

Hope its clear!!!
Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Server Interview question - Part 2

What is normalization?
Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.

What is a stored procedure:

Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.

What is the difference between UNION ALL Statement and UNION ?

The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?

They are three kinds of stored procedures,
1.System stored procedure – Start with sp_
2. User defined stored procedure – SP created by the user.
3. Extended stored procedure – SP used to invoke a process in the external systems.
Example for system stored procedure
sp_helpdb - Database and its properties
sp_who2 – Gives details about the current user connected to your system.
sp_renamedb – Enable you to rename your database

What is a trigger?
Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.

What is a view?

If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

What is an Index?

When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

What are the types of indexes available with SQL Server?
There are basically two types of indexes that we use with the SQL Server
Clustered - 1. It will format the entire table, inturn physically sort the table.
2. Only one clustered index can be created for a table.
3. Data will be located in the leaf level.
4. By default, primary key will create clustered index on the table.
Non-Clustered Index – 1. It wont touch the structure of the table.
2. It forms an index table as reference to the exact data.
3. A reference to the data will be located in the leaf level.
4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Server Interview questions

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.
We should avoid permitting null values because
Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.

The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.
What is the difference between SQL and Pl/Sql ?
Straight forward. SQL is a single statement to finish up our work.
Considering, I need some data from a particular table.
“Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Programming Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.
We should avoid permitting null values because
Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.
Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

SQL Injection

SQL Injection:
Internet Hackers introduces the concept called SQL injection. It'snothing but injecting some malicious code to hack your system or to break with website. Since, they are using the concepts of sql server to break the security of the server. It's referred to as SQL Injection.
Considering am creating a table which contains username and password and based on the username and password. The user will be allowed to enter into the system.
CREATE TABLE VenkatSQLInjection(id INT,USERNAME VARCHAR(100),password VARCHAR(10))
INSERT INTO VenkatSQLInjection VALUES(1,'Venkat','Venkat')
INSERT INTO VenkatSQLInjection VALUES(2,'SUBA','SUBA')
SELECT * FROM VenkatSQLInjection
Below is teh procedure to validate the user authentication,
CREATE PROCEDURE SQLInjectionProc
(
@USR VARCHAR(10),
@PWD VARCHAR(10),
@VAL INT OUTPUT
)
AS
BEGIN
SELECT @VAL =COUNT(*) FROM VenkatSQLInjection WHERE USERNAME = @USR AND password = @PWD
RETURN @VAL
END

IF the user knows the username by using cookies. They will try to hack the system with out password. One possibility is, Considering you will be having a GUI which ask for User name and password. If the user give the existing username. For example, 'SUBA''--' and password as 'ABC'
In SQL Server, the operation resembles,

SELECT COUNT(*) FROM VenkatSQLInjection WHERE USERNAME = 'SUBA'-- AND password = 'ABC'

Obviously, the system will recognize the person as valid user and Its the basic foudation for hacking.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

Views in sql server

Views in sql server:
View is a very good concept in sql server. View is nothing but a reference for a table. It inturn refer the table itself.
Its like a layer placed on the table. So that, we will be providing an extra layer over the tables. Instead of a using complex join, we
can views which will hold the entire logic and hope it's supporting encapsulation logic :-).
Creating a sample table:

CREATE TABLE venkat1(id int, name varchar(100))

INSERT INTO venkat VALUES(1,'aa;bb;cc;dd;')

INSERT INTO venkat VALUES(2,'EE;FF;GG;HH')

SELECT * FROM venkat

Creating view on the table:

CREATE VIEW VENKATVIEW AS

SELECT * FROM venkat

Selecting data from the view:

SELECT * FROM VENKATVIEW
DML operations on the view:
(Insert statement on views)

INSERT INTO VENKATVIEW VALUES(2,'VE')

SELECT * FROM VENKAT

(Delete statement on views)

DELETE FROM VENKATVIEW WHERE ID=1
(Update statement on views)

UPDATE VENKATVIEW SET NAME='AA' WHERE ID=2

Happy Learning!!!

Regards,

Venkatesan Prabu .J

SSIS deployment error

While executing our dtsx package, Sometimes we will get the below errors.
"The product level is insufficient for component "Source"


The product level is insufficient for component "Destination..
Goto Run and type services.msc.... check for the sql related services installed in your machine.

The error is due to the SQL installables in your machine. SQL Server will hold two types of installables,

1. Only work station component will be installed.

2. Entire package with ssis,ssas,ssrs can be installed (Options will be there to install specific services)
The error is due to the first option installed in your machine. SSIS services wont be available in your machine. In that case, you can execute the package using Business intelligence. But standalone.. dtsx package wont execute.

Check this blog for more reference,

http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx

Happy learning!!!
Regards,
Venkatesan Prabu .J

Server property in SQL Server

Below Query will fetch all the information about SQL Server installed in our machine.

Code Snippet

SELECT SERVERPROPERTY('Collation') Collation, SERVERPROPERTY('Edition') Edition, SERVERPROPERTY('Engine Edition') EngineEdition, SERVERPROPERTY('InstanceName') InstanceName, SERVERPROPERTY('IsClustered') IsClustered, SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled, SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly, SERVERPROPERTY('IsSingleUser') IsSingleUser, SERVERPROPERTY('IsSyncWithBackup') IsSyncWithBackup, SERVERPROPERTY('LicenseType') LicenseType, SERVERPROPERTY('MachineName') MachineName, SERVERPROPERTY('NumLicenses') NumLicenses, SERVERPROPERTY('ProcessID') ProcessID, SERVERPROPERTY('ProductVersion') ProductVersion, SERVERPROPERTY('ProductLevel') ProductLevel, SERVERPROPERTY('ServerName') ServerName

The output resembles,

select SERVERPROPERTY('Edition') Edition

is an important property used in fetching the edition of our sql server installed in our machine.

Happy Learning!!!

Regards,

Venkatesan Prabu .J