1] Distinct Querry :-
without distinct-
select empname, salary, joindate, deptno
from employee
group by empname, salary, joindate, deptno;
with distinct-
SELECT DISTINCT empname, salary, joindate
FROM employee;
2] Update Querry :-
UPDATE employee
SET empname = 'chirag', salary = 350000
WHERE deptno = 103;
3] Create Table :-
create table employee(empid int primary key, empname varchar(20), salary
decimal(10,2), joindate date, deptno int);
4] To Insert value :-
insert into employee(empid, empname, salary, joindate, deptno) values
(1, 'dipesh', 100000.00, '2024-10-16', 101),
(2, 'videeka', 200000.00, '2024-10-17', 102),
(3, 'chirag', 300000.00, '2024-06-18', 103),
(4, 'mayank', 400000.00, '2023-12-24',104),
(5, 'riya', 500000.00, '2023-12-24', 105),
(6, 'ishraj', 16000.00, '2016-10-16', 106),
(7, 'ishraj', 16000.00, '2016-10-16', 106);
5] To find maximum salary :-
step1) select max(salary) from employee;
step2) select * from employee where salary = (select max(salary) from
employee);
6] Display maxm salary in each deptartment :-
SELECT deptno, MAX(salary) FROM employee GROUP BY deptno;
7] Display minim salary in each deptartment :-
SELECT deptno, MIN(salary) FROM employee GROUP BY deptno;
8] Display dept which are having more than 2 employess :-
select deptno from employee
group by deptno having count (*) >=2;
9] Display dept and their sum of salary whose total salary is reater than 1.2lac :-
SELECT deptno, SUM(salary) AS total_salary
FROM employee
GROUP BY deptno
HAVING SUM(salary) > 120000;
10] Display details of employee based on asc order of dept and in each dept salary
should be furtheer aranged in highest to lowest :-
select * from employee
order by deptno asc, salary desc;
11] Display name and salary of emp working in deptno. dept in desc order of their
salary :-
SELECT empname, salary
FROM employee
ORDER BY salary DESC;
12] Display 2nd highest salary :-
select max(salary) from employee
where salary<(select max(salary) from employee);
13] Display 3rd or nth highest salary :-
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY; -- For the 3rd highest salary using
order by (offset-fetch is use to skip 1st two highest slaries and fetch
the next one)
14] Display Details of 5 Highest Salary :-
SELECT TOP 5 salary
FROM employee
ORDER BY salary DESC;
15] People join in specific year and month :-
select * from employee where year(joindate)='2023';
select * from employee where month(joindate)='10';
select * from employee where day(joindate)='16';
EXTRA] to create view
create view emplo AS
select * from employee
