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
In
the wizard's third page select the additional connection
parameter. If you are not sure what they should be just leave
them to default and click Finish to connect to directory server. 
Appropriate right tables column will be appended with Null value.
