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 cityI 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
Appropriate right tables column will be appended with Null value.
You will be given two options,
Click apply, now you can achieve your tasks.
