SQL interview Question Answer Part -1

SQL QUERIES -Part 1

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;

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 * from all_users;

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

SQL>select * 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 alaphabet S.

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

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

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