exp 8 XIV, Sql Server

exp 8 XIV

create table emp(emp_no integer,emp_name varchar (30),salary integer);
insert into emp values(1,'Jay',75000),(2,'ashish',65000),(3,'shreyas',50000),(4,'priya',80000),(5,'mona',48000);

select * from emp;
create table dept(dept_no integer,emp_no integer ,deptname varchar(30),location varchar(30));
insert into dept values(11,1,'it','mumbai'),(10,3,'marketing','thane'),(15,4,'production','kalyan'),(10,2,'marketing','nashik');
insert into dept values(11,5,'it','mumbai');
select * from dept;
Select dept_no,sum(salary) from emp e,dept d where e.emp_no=d.emp_no group by dept_no;
Select dept_no,e.emp_no,min(salary) from emp e,dept d where e.emp_no=d.emp_no group by dept_no,e.emp_no;
Select deptname,count(d.emp_no)from emp e,dept d where e.emp_no=d.emp_no group by deptname;
Select * from emp order by salary;
Select dept_no,d.emp_no,salary from emp e,dept d where e.emp_no=d.emp_no  group by dept_no,d.emp_no,salary having salary>60000;
Select deptname,count(d.emp_no)from emp e,dept d where e.emp_no=d.emp_no group by deptname order by count(d.emp_no);
Select dept_no,count(d.emp_no)from emp e,dept d where e.emp_no=d.emp_no  group by dept_no,d.emp_no,salary having salary>45000;
Select d.emp_no,emp_name,salary from emp e,dept d where e.emp_no=d.emp_no  group by d.emp_no,emp_name,salary,deptname having deptname='it' and salary>(select avg(salary)from emp);


run \| edit \| history \| help	0