sql queries for practise

I have arranged some basic sql queries that will help you to sort out basic query related problems and build your concept also.

Here emp means employee, sal means salary  i hope you will understand accordingly.

-----------------------------------------------------------------------------------------

1) Display the details of all employees

    SQL>Select * from emp;

2) Display the depart information from department table

    SQL>select * from dept;

3) Display the name and job for all the employees

    SQL>select ename,job from emp;

4) Display the name and salary  for all the employees

    SQL>select ename,sal from emp;

5) Display the employee no and totalsalary  for all the employees

    SQL>select empno,ename,sal,comm, sal+nvl(comm,0) as"total  salary" from

    emp

6) Display the employee name and annual salary for all employees.

    SQL>select ename, 12*(sal+nvl(comm,0)) as "annual Sal" from emp

7) Display the names of all the employees who are working in depart number 10.

    SQL>select emame from emp where deptno=10;

8) Display the names of all the employees who are working as clerks and

   drawing a salary more than 3000.

   SQL>select ename from emp where job='CLERK' and sal>3000;

9) Display the employee number and name  who are earning comm.

   SQL>select empno,ename from emp where comm is not null;

s

10) Display the employee number and name  who do not earn any comm.

SQL>select empno,ename from emp where comm is null;

11) Display the names of employees who are working as clerks,salesman or

analyst and drawing a salary more than 3000.

SQL>select ename  from emp where job='CLERK' OR JOB='SALESMAN'

          OR JOB='ANALYST' AND SAL>3000;

12) Display the names of the employees who are working in the company for

the past 5 years;

SQL>select ename  from emp where to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;

13) Display the list of employees who have joined the company before

30-JUN-90 or after 31-DEC-90.

a)select ename from emp where hiredate < '30-JUN-1990' or hiredate >

'31-DEC-90';

14) Display current Date.

SQL>select sysdate from dual;

15) Display the list of all users in your database(use catalog table).

SQL>select username from all_users;

16) Display the names of all tables from current user;

SQL>select tname from tab;

17) Display the name of the current user.

SQL>show user

18) Display the names of employees working in depart number 10 or 20 or 40

or employees working as

CLERKS, SALESMAN or ANALYST.

SQL>select ename from emp where deptno in (10, 20, 40) or job

in ('CLERKS','SALESMAN','ANALYST');

19) Display the names of employees whose name starts with alphabet S.

SQL>select ename from emp where ename like 'S%';

20) Display the Employee names for employees whose name ends with alphabet S.

SQL>select ename from emp where ename like '%S';

21) Display the names of employees whose names have second alphabet A in

their names.

SQL>select ename from emp where ename like '_A%';

22) select the names of the employee whose names is exactly five characters

in length.

SQL>select ename from emp where length (ename) =5;

23) Display the names of the employee who ar e not working as MANAGERS.

SQL>select ename from emp where job not in ('MANAGER');

24) Display the names of the employee who are not work ing as SALESMAN OR

CLERK OR ANALYST.

SQL>select ename from EMP where job not

In ('SALESMAN','CLERK','ANALYST');

25) Display all rows from EMP table. The system should wait after every

Screen full of information.

SQL>set pause on

26) Display the total number of employee working in the company.

SQL>select count (*) from emp;

27) Display the total salary beiging paid to all employees.

SQL>select sum (Sal) from emp;

28) Display the maximum salary from emp table.

SQL>select max (Sal) from emp;

29) Display the minimum salary from emp table.

K SQL>select min (Sal) from emp;

30) Display the average salary from emp table.

SQL>select avg(sal) from emp;

31) Display the maximum salary being paid to CLERK.

SQL>select max(sal) from emp where job='CLERK';

32) Display the maximum salary being paid to depart number 20.

SQL>select max(sal) from emp where deptno=20;

33) Display the minimum salary being paid to any SALESMAN.

SQL>select min(sal) from emp where job='SALESMAN';

34) Display the average salary drawn by MANAGERS.

SQL>select avg(sal) from emp where job='MANAGER';

35) Display the total salary drawn by ANALYST working in depart number 40.

SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;

36) Display the names of the employee in order of salary i.e the name of

the employee earning lowest salary    should salary appear first.

SQL>select ename from emp order by sal;

37) Display the names of the employee in descending order of salary.

a)select ename from emp order by sal desc;

38) Display the names of the employee in order of employee name.

a)select ename from emp order by ename;

39) Display empno,ename,deptno,sal sort the output first base on name and

within name by deptno and with in deptno by sal.

SQL>select empno,ename,deptno,sal from emp order by

40) Display the name of the employee along with their annual salary(sal*12).The name of the employee earning highest annual salary should apper first.

SQL>select ename,sal*12 from emp order by sal desc;

41) Display name,salary,hra,pf,da,total salary for each employee. The

output should be in the order of total salary,hra 15% of salary,da 10% of salary,pf 5%

salary,total salary will be(salary+hra+da)-pf.

SQL>select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as

da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;

42) Display depart numbers and total number of employees working in each

department.

SQL>select deptno,count(deptno)from emp group by deptno;

43) Display the various jobs and total number of employees within each job

group.

SQL>select job,count(job)from emp group by job;

44) Display the depart numbers and total salary for each department.

SQL>select deptno,sum(sal) from emp group by deptno;

45) Display the depart numbers and max salary for each department.

SQL>select deptno,max(sal) from emp group by deptno;

46) Display the various jobs and total salary for each job

SQL>select job,sum(sal) from emp group by job;

48) Display the depart numbers with more than three employees in each dept.

SQL>select deptno,count(deptno) from emp group by deptno having

count(*)>3;

49) Display the various jobs along with total salary for each of the jobs

where total salary is greater than 40000.

SQL>select job,sum(sal) from emp group by job having sum(sal)>40000;

50) Display the various jobs along with total number of employees in each

job.The output should contain only those  jobs with more than three employees.

SQL>select job,count(empno) from emp group by job having count(job)>3

51) Display the name of the empployee who earns highest salary.

SQL>select ename from emp where sal=(select max(sal) from emp);

52) Display the employee number and name for employee working as clerk and

earning highest salary among clerks.

SQL>select empno,ename from emp where where job='CLERK'

           and sal=(select max(sal) from emp  where job='CLERK');