SQL Query and Answers - Part1

Here I am sharing the set of SQL Queries with Answers which I have prepared. Working in OBIEE and in database It is good know about the basic SQL query. 

1. Display the dept information from department table.

  Select  *  from  dept;

2. Display the details of all employees

  Select * from emp;

3. Display the name and job for all employees

   Select ename ,job from emp;

4. Display name and salary for all employees.

  Select ename  , sal  from emp;

5. Display employee number and total salary  for each employee.

Select empno, sal+comm From emp;

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

  Select empno,empname,12*sal+nvl(comm,0) annualsal from emp;

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

  Select ename from emp where deptno=10;

8. Display the names of all employees working as  clerks and drawing a salary more than 3000

  Select ename from emp where job=’clerk’andsal>3000;

9. Display employee number and names for employees who earn commission

  Select empno,ename from emp where comm is not null and comm>0.

10. Display names of employees who do not earn any commission.

  Select empno ,ename  from emp where comm is  null and comm=0.

11. Display the names of employees who are working as clerk,salesman or anlyst and drawing a salary more than 3000.

  Select ename from emp where(job=’clerk’or job=’salesman’ or job= ‘Analyst’) and sal>3000;

 (Or)

  Select ename from emp wherejob in(‘clerk’,’slaesman’,’analyst’) and sal>3000;

12. Display the names of employees who are working in the company  for the past 5 years.

  Select ename from emp where sysdate-hiredate>5*365;

13. Display the list of employees who have joined the company before 
30^th  June 90 after 31^st dec 90.

  Select * from emp where hiredate between ’30-Jun-1990’ and  ’31-dec-1990’;

14. Display current date.

  Select sysdate from dual;

15. Display the list of users in your  database(using log table).

  Select * from dba_users;

16. Display the names of all tables from the current user.

Select * from tab;

17. Display the name of the Current user.

  Show user;

18. Display the names of employees working in department number 10 or 20 or 40 employees  working  as  clerks.salesman or analyst

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.

Select ename from emp where ename like ‘S%’;

20. Display the names of employees whose name ends with alphabet S.

Select ename from emp where ename like ‘%S’;

21. Display the names of employees whose name have second  alphabet A in their names.

Select ename from emp where ename like ‘_A%’;

22. Display the names of employees whose name is exactly five characters in length.

Select ename from emp where length(ename)=5;

(Or)

Select ename from emp where ename like ‘_____’;

23. Display the names of employees who are not working as managers.

 Select * from emp minus(Select * from emp where empno in(Select mgr from emp));

(Or)

 Select * from emp e where empno not in (Select mgr from emp where mgr is not null);

(Or)

Select * from emp e where empno not in (Select mgr from emp where e.empno=mgr);

24. Display the names of employees who are not working as SALESMAN or CLERK or ANALYST.

Select ename from emp  where job not in(‘clerks’,’salesman’,’Analyst’);

25. Display all rows from Emp table .The System should wait after every screen full of information.

 Set pause on;