-- To fetch the alternate records from a table --Even row
select * from employee where empno%2=0
--To fetch the alternate records from a table -- Odd row
select * from employee where empno%2=1
-- find third max salary from the table
Select salary from employee order by salary desc
Select distinct salary from employee order by salary desc
Select distinct top 3 salary from employee order by salary asc
Select top 1 salary from (Select distinct top 5 salary from employee order by salary desc) salarynew order by salary asc
-- find third min salary from the table
select top 1 salary from (Select distinct top 3 salary from employee order by salary asc) salarynew order by salary desc
-- find Select First n records from a table
Select top 5 * from employee order by empno asc
-- find select last n records from a table
select top 5 * from employee order by empno desc
--List dept no., Dept name for all the departments in which there are no employees in the department.
select * from employee where deptno in (select deptno from department)
Select * from department where deptno not in(Select deptno from employee)
--how to get 3 max salaries
select distinct top 3 salary from employee order by salary desc
--how to get 3 min salaries
Select distinct top 3 salary from employee order by salary asc
-- how to get n th max salaries
--here n is sample example 6
select distinct top 6 salary from employee order by salary desc
--- select distinct records from emp table
select distinct * from employee
--how to delete duplicate rows in a table
Select MAX(deptno) from employee group by empno,deptno
CREATE TABLE TestTable(COL1 INT, COL2 INT, COL3 VARCHAR(50))
INSERT INTO TestTable VALUES (1, 1, 'ONE')
INSERT INTO TestTable VALUES (1, 2, 'TWO')
INSERT INTO TestTable VALUES (1, 2, 'TWO')
INSERT INTO TestTable VALUES (1, 3, 'THREE')
INSERT INTO TestTable VALUES (1, 3, 'THREE')
INSERT INTO TestTable VALUES (1, 3, 'THREE')
--> to view removing depulicate records
Select Distinct * from TestTable
Select * from TestTable
--step1:
Select *,ROW_NUMBER() over(partition by col1,col2,col3 order by col1) as rownumer from TestTable
--step2:
with deleteduplicaterows as
(
Select *,ROW_NUMBER() over(partition by col1,col2,col3 order by col1) as rownumer from TestTable
)
delete from deleteduplicaterows where rownumer >1
--Count of number of employees in department wise
Select * from employee
select * from department
select Count(*),dept.deptno,dept.deptname from employee emp inner join department dept on emp.deptno=dept.deptno group by dept.deptno,dept.deptname
--Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
Select empname,salary/12 as monthlysalary from employee -- monthly salary
select empname,salary*12 from employee--annual salary
--Select all record from emp table where deptno =10 or 40.
Select * from employee
select * from employee where deptno=1 or deptno=4
--Select all record from emp table where deptno=30 and sal>1500.
Select * from employee where deptno=2 and salary>26000
--Select all record from emp where job not in SALESMAN or CLERK.
Select * from employee
Select * from employee where job not in ('IT' , 'Ac')
--Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
Select * from employee where empname in ('myil','Anu')
--Select all records where ename starts with ‘S’ and its lenth is 6 char.
Select * from employee where empname like 'R_____'
--Select all records where ename may be any no of character but it should end with ‘R’.
Select * from employee where empname like '%R'
--Count MGR and their salary in emp table.
Select distinct Count(Job),Count(salary) from employee
Select Count(salary) from employee where job='IT'
--emp table add comm+sal as total sal .
Select empname,(salary + bonus) from employee
--Select any salary <3000 from emp table.
Select salary from employee where salary < 27000
Select * from employee
Select salary from employee where salary < 36000
Select * from employee where salary > any(Select salary from employee where salary < 36000)
--Select All salary <3000 from emp table.
Select * from employee
Select salary from employee where salary < 30000
Select * from employee where salary > all(Select salary from employee where salary < 30000)
--Select all the employee group by deptno and sal in descending order.
Select empname,deptno,salary from employee group by deptno,empno,empname,salary order by salary desc
--How can I create an empty table emp1 with same structure as emp?
create table employee1 as
select * into employee1 from employee
select * from employee1
Select * into employee2 from employee where 1=2
Select * from employee2
--How to retrive record where sal between 1000 to 2000?
Select * from employee
select * from employee where salary<=40000 and salary>=25000
select * from employee where salary between 25000 and 40000
--Select all records where dept no of both emp and dept table matches.
Select * from employee emp join department dept on emp.deptno=dept.deptno
select * from employee emp where exists(select * from department dept where emp.deptno=dept.deptno)
--If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
Select * from employee union select * from employee1
Select * from employee intersect select * from employee1
--error coding
--Select * from employee Minus select * from employee1
--Count the totalsal deptno wise where more than 2 employees exist.
Select count(empno) from employee group by deptno order by deptno
Select emp.deptno,SUM(emp.salary) as totalsalary from employee emp group by emp.deptno having Count(emp.empno)>2
insert into employee1 ()
select * from department
Select * from employee order by deptno
Select emp.deptno,dept.deptname,SUM(salary) from employee emp join department dept on emp.deptno=dept.deptno group by emp.deptno,dept.deptname order by deptno
Select * from department
declare @name int
set @name =(Select MAX(salary) from employee)
Select @name
Select deptno,SUM(salary) as sal from employee group by deptno having deptno >2
Select * from employee where deptno =(select deptno from department where deptno=2)
Select * from employee where deptno =2
Select salary from employee
Select MAX(salary) from employee
Select MAX(salary) from employee
Select salary from employee group by salary having MAX(salary) < (Select MAX(salary) from employee)
Select * from employee where deptno not in (select deptno from department)
Select deptno from department d where EXISTS(Select * from employee where d.deptno = employee.deptno )
Select * from employee1
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
alter trigger tr_persons on employee1
after insert,delete,update
as
PRINT 'TRIGGER OUTPUT' + CONVERT(VARCHAR(5),@@ROWCOUNT)
+ 'ROW UPDATED'
Go
update employee1 set job='NonIT' where job='' or job=null
Select * from employee1
Select * from employee
insert into employee1(empname,empphone) values ('kamal','9638521254')
update employee1 set salary='20000' where empno=13
Create table TestEmp
(
empno int not null primary key,
empname varchar(50),
job varchar(20),
mgr numeric(4,0),
hiredate datetime,
sal numeric(10,2),
comm numeric(10,2),
deptno int foreign key references dept(depto)
)
Create table dept(
depto int not null primary key identity(1,1),
deptname varchar(20),
Location varchar(20)
)
Select * from dept
Create table Jobhist(
empno int not null,
startdate datetime not null,
enddate datetime,
job varchar(20),
comm numeric(10,2),
depto int,
chgdesc varchar(100),
constraint jobhisk_pk primary key (empno,startdate),
constraint jobhist_ref_emp_fk Foreign key (empno) references Testemp(empno),
constraint jobhist_ref_dept_fk Foreign key (depto) references dept(depto),
constraint jobhist_ref_date_ck Check (startdate <= enddate)
)
INSERT INTO dept VALUES ('ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES ('RESEARCH','DALLAS');
INSERT INTO dept VALUES ('SALES','CHICAGO');
INSERT INTO dept VALUES ('OPERATIONS','BOSTON');
INSERT INTO TestEmp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,2);
INSERT INTO TestEmp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,3);
INSERT INTO TestEmp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,3);
INSERT INTO TestEmp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,2);
INSERT INTO TestEmp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,3);
INSERT INTO TestEmp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,3);
INSERT INTO TestEmp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,1);
INSERT INTO TestEmp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,2);
INSERT INTO TestEmp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,1);
INSERT INTO TestEmp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,3);
INSERT INTO TestEmp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,2);
INSERT INTO TestEmp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,3);
INSERT INTO TestEmp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,2);
INSERT INTO TestEmp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,1);
Select * from Jobhist
INSERT INTO jobhist VALUES (7369,'17-DEC-80',NULL,'CLERK',800,2,'New Hire');
INSERT INTO jobhist VALUES (7499,'20-FEB-81',NULL,'SALESMAN',1600,3,'New Hire');
INSERT INTO jobhist VALUES (7521,'22-FEB-81',NULL,'SALESMAN',1250,3,'New Hire');
INSERT INTO jobhist VALUES (7566,'02-APR-81',NULL,'MANAGER',2975,2,'New Hire');
INSERT INTO jobhist VALUES (7654,'28-SEP-81',NULL,'SALESMAN',1250,3,'New Hire');
INSERT INTO jobhist VALUES (7698,'01-MAY-81',NULL,'MANAGER',2850,3,'New Hire');
INSERT INTO jobhist VALUES (7782,'09-JUN-81',NULL,'MANAGER',2450,2,'New Hire');
INSERT INTO jobhist VALUES (7788,'19-APR-87','12-APR-88','CLERK',1000,2,'New Hire');
INSERT INTO jobhist VALUES (7788,'13-APR-88','04-MAY-89','CLERK',1040,2,'Raise');
INSERT INTO jobhist VALUES (7788,'05-MAY-90',NULL,'ANALYST',3000,2,'Promoted to Analyst');
INSERT INTO jobhist VALUES (7839,'17-NOV-81',NULL,'PRESIDENT',5000,1,'New Hire');
INSERT INTO jobhist VALUES (7844,'08-SEP-81',NULL,'SALESMAN',1500,3,'New Hire');
INSERT INTO jobhist VALUES (7876,'23-MAY-87',NULL,'CLERK',1100,2,'New Hire');
INSERT INTO jobhist VALUES (7900,'03-DEC-81','14-JAN-83','CLERK',950,1,'New Hire');
INSERT INTO jobhist VALUES (7900,'15-JAN-83',NULL,'CLERK',950,3,'Changed to Dept 30');
INSERT INTO jobhist VALUES (7902,'03-DEC-81',NULL,'ANALYST',3000,2,'New Hire');
INSERT INTO jobhist VALUES (7934,'23-JAN-82',NULL,'CLERK',1300,1,'New Hire');
Select * from dept
select * from TestEmp
