DBMS LAB : Cycle-IV



Lab Cycle - IV Queries



1.Create a view, which contain employee names and their manager names working in sales department.
 --->create view v1 as (select e.ename,n.ename as managername from emp e,emp n,dept d where e.mgr=n.empno and e.deptno=d.deptno and d.dname='sales');

View created.

=============================================================================

2. Update the employee salary by 25%, whose experience is greater than 10 years.
---> update emp set sal=sal+(sal*0.25) where round(months_between(sysdate,hiredate)/12)>10;

14 rows updated.

=============================================================================

3. Delete the employees, who completed 32 years of service.

---> delete from emp where round(months_between(sysdate,hiredate)/12)>32;

0 rows deleted.

=============================================================================

4. Determine the minimum salary of an employee and his details, who join on the same date.

--->select * from emp where sal in(select min(sal) from emp group by hiredate having count(hiredate)>=2);

Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7900 james clerk 7698 03-DEC-81 1187.5 30


=============================================================================

5. Determine the count of employees, who are taking commission and not taking commission.

---> select count(comm) nottakingcommission,count(empno)-count(comm) as takingcommission from emp;

Output:

NOT TAKING COMMISSION TAKING COMMISSION
4 10


=============================================================================

6. Determine the department does not contain any employees.

---> select deptno,dname from dept where deptno not in (select deptno from emp);

Output:

DEPTNO DNAME
40 operations


=============================================================================

7. Find out the details of top 5 earners of company. (Note: Employee Salaries should not be duplicate like 5k, 4k, 4k, 3k, 2k)

---> select * from (select * from emp order by sal desc) where rownum <= 5;

Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 king president 17-NOV-81 6250 10
7902 ford analyst 7566 03-DEC-81 3750 20
7788 scott analyst 7566 09-DEC-82 3750 20
7566 jones manager 7839 02-APR-81 3718.75 20
7698 blake manager 7839 01-MAY-81 3562.5 30


=============================================================================

8. Display those managers name whose salary is more than an average salary of his employees.

--->select m.ename,m.sal,avg(e.sal) from emp e,emp m where e.mgr=m.empno group by m.ename, m.sal having m.sal>avg(e.sal);


Output:

ENAME Sal AVG(E.Sal)
scott 3750 1375
clark 3062.5 1625
king 6250 3447.91667
blake 3562.5 1637.5
ford 3750 1000


=============================================================================

9. Display the names of the managers who is having maximum number of employees working under him?
--->select ename from emp where empno in (select mgr from emp group by mgr having count(*)=(select max(count(*)) from emp group by mgr));

Output:

ENAME
blake

=============================================================================

10. In which year did most people join the company? Display the year and number of employees.

---> select to_char(hiredate,'yyyy') "YEAR",count(EMPNO) "NO. OF Employees" from emp group by to_char(hiredate,'yyyy') having count(empno)=(select max(count(empno)) from emp group by to_char(hiredate,'yyyy'));
Output

YEAR NO. OF Employees
1981 10

=============================================================================
Note : If you find any errors, please make a comment here...

Comments

Post a Comment

Popular posts from this blog

DBMS LAB : PL/SQL Programs

1. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN NUMBER IS STRONG OR NOT. SQL> Declare n number:=&n; n1 number:=n; fact number; sum1 number; r number; i number; Begin sum1:=0; while(n>0) loop r:=mod(n,10); n:=trunc(n/10); i:=1; fact:=1; while(i<=r)loop fact:=fact*i; i:=i+1; end loop; sum1:=sum1+fact; end loop; if(sum1=n1) then dbms_output.put_line(n1||' is strong number'); else dbms_output.put_line(n1||' is not a strong number'); end if; end; / Output: Enter value for n: 145 old 9: n:=&n; new 9: n:=145; 145 is strong number PL/SQL procedure successfully completed. 2. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN STRING IS PALINDROME OR NOT. SQL> Declare len number; s1 varchar2(20) := '&s1'; s2 varchar2(20); Begin len := length(s1); for i...
Read more

COMPUTER NETWORKS PROGRAMS

1. WRITE A PROGRAM TO MANIPULATE THE IP ADDRESS OF A SYSTEM. import java.net.*; public class inetaddrs { public static void main(String args[]) throws UnknownHostException { InetAddress local=InetAddress.getLocalHost(); System.out.println("Name :"+local.getHostName()); System.out.println("Ip Address :"+local.getHostAddress()); } } 2. WRITE A PROGRAM TO OBTAIN THE INFORMATION ABOUT THE HOST, PORTNO. PROTOCOL, FILE AND PATH. import java.lang.*; import java.io.*; import java.net.*; class url { public static void main(String args[]) throws Exception { URL obj=new URL("http://www.dreamtechpress.com:80/index.html"); System.out.println("Host Name is:"+obj.getHost()); System.out.println("Port NO is:"+obj.getPort()); System.out.println("Protocol is:"+obj.getProtocol()); System.out.println("File used is:"+obj.getFile()); System.out.println("Path used is:"+obj.getPat...
Read more

DBMS LAB : Cycle-II

Lab Cycle - II Queries 1. Get Suppliers Names for Suppliers who supply at least one red part. -->select sname from supplier where sno in(select sno from sp where pno in(select pno from parts where color='Red')); Output: SNAME Blake Clark Adams Smith ============================================================================= 2.Get Suppliers Names for Suppliers who do not supply part ‘P2’ --> select sname from supplier where sno in(select sno from sp where pno in(select pno from parts where pno!='P2')); Output: SNAME Blake Clark Adams Jones Smith ============================================================================= 3. Using Group by with Having Clause, Get the part numbers for all the parts supplied by more than one supplier. -->select pno,count(sno) from sp group by pno having count(sno)>1; Output: PNO COUNT(SNO) P4 2 P1 2 P3 3 P6 2 P5 2 =====================================...
Read more