[go: up one dir, main page]
Scribd
Upload
17 views2 pages

SQL Classes

Uploaded by

nitedi2544
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
17 views2 pages

SQL Classes

Uploaded by

nitedi2544
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

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

You might also like