In this article I have posted SQL interview questions and answers for my visitors to help them in learning basic SQL.
How to connect with Oracle Database
C:>sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 16 16:22:56 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn sys/sys@test as sysdba
Connected.
How to create a user in Oracle Database
SQL> create user test identified by test;
User created.
How to give dba privileges to test user
SQL> grant dba to test;
Grant succeeded.
How to create a table
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL,ENAME VARCHAR2(20),JOB VARCHAR2(9),MGR NUMBER(5),HIREDATE DATE,SAL NUMBER(8, 2),COMM NUMBER(8, 2),DEPTNO NUMBER(3));
Table created.
CREATE TABLE DEPT(DEPTNO NUMBER(3),DNAME VARCHAR2(18),LOC VARCHAR2(18) );
Table created.
Insert rows into a table
INSERT INTO EMP VALUES (7369 , ‘SMITH’, ‘CLERK’, 7902 ,TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES(7499 , ‘ALLEN’, ‘SALESMAN’, 7698 ,TO_DATE(’20-FEB-981′, ‘DD-MON-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES(7521 , ‘WARD’, ‘SALESMAN’, 7698 ,TO_DATE(’22-FEB-1981′, ‘DD-MON-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES(7566 , ‘JONES’, ‘MANAGER’, 7839 ,TO_DATE(‘2-APR-1981’, ‘DD-MON-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES(7654 , ‘MARTIN’, ‘SALESMAN’, 7698 ,TO_DATE(’28-SEP-1981′, ‘DD-MON-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698 , ‘BLAKE’, ‘MANAGER’, 7839 ,TO_DATE(‘1-MAY-1981’, ‘DD-MON-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782 , ‘CLARK’, ‘MANAGER’, 7839 ,TO_DATE(‘9-JUN-1981’, ‘DD-MON-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788 , ‘SCOTT’, ‘ANALYST’, 7566 ,TO_DATE(’09-DEC-1982′, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839 , ‘KING’, ‘PRESIDENT’, NULL ,TO_DATE(’17-NOV-1981′, ‘DD-MON-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844 , ‘TURNER’, ‘SALESMAN’, 7698 ,TO_DATE(‘8-SEP-1981’, ‘DD-MON-YYYY’), 1500, 0, 30);
INSERT INTO EMP VALUES(7876 , ‘ADAMS’, ‘CLERK’, 7788 ,TO_DATE(’12-JAN-1983′, ‘DD-MON-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900 , ‘JAMES’, ‘CLERK’, 7698, TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES(7902 , ‘FORD’, ‘ANALYST’, 7566 ,TO_DATE(‘3-DEC-1981’, ‘DD-MON-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES(7934 , ‘MILLER’, ‘CLERK’, 7782, TO_DATE(’23-JAN-1982′, ‘DD-MON-YYYY’), 1300, NULL, 10);
INSERT INTO DEPT VALUES (10 , ‘ACCOUNTING’, ‘NEW YORK’ );
INSERT INTO DEPT VALUES (20 , ‘RESEARCH’, ‘DALLAS’);
INSERT INTO DEPT VALUES (30 , ‘SALES’, ‘CHICAGO’);
INSERT INTO DEPT VALUES (40 , ‘OPERATIONS’, ‘BOSTON’);
Commit is used to make it permanent into the database
SQL> COMMIT;
Commit complete.
Select names of all the employees who are working in department 10
SQL> select ename from emp where deptno=10;
ENAME
CLARK
KING
MILLER
How can we add row number with selected records?
select rownum, ename from emp;
Select the employee number and name who are earning comm. from their salary.
SQL> select empno,ename from emp where comm is not null;
EMPNO ENAME
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
Select the names of the employees who are working in the company from last 5 years;
SQL> select ename from emp where hiredate < add_months(sysdate,-60);
ENAME
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
Select the names of employees who are working in department number 10 or 20 or 40 and working as CLERKS,SALESMAN or ANALYST.
SQL> select ename from emp where deptno in(10,20,40) and job in(‘CLERKS’,’SALESMAN’,’ANALYST’);
ENAME
SCOTT
FORD
Select commission from emp table and put NA in place of no commission.
SQL> select nvl(to_char(comm.),’NA’) as commission from emp;
commission
NA
300
NA
1400
NA
Select the Employee names whose name ends with an alaphabet S.
SQL> select ename from emp where ename like ‘%S’;
ENAME
JONES
ADAMS
JAMES
Select the maximum salary being paid to CLERK.
SQL> select max(sal) as Max_Sal from emp where job=’CLERK’;
Max_Sal
1300
Select the total salary drawn by an ANALYST working in department 20.
SQL> select sum(sal) as sum_sal from emp where job=’ANALYST’ and deptno=20;
sum_sal
6000
Select the department numbers with more than three employees in each dept.
SQL> select deptno,count(deptno) from emp group by deptno having count(*)>3;
DEPTNO COUNT(DEPTNO)
30 6
20 5
Select the name of the employee who is earning highest salary.
SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
KING
Select the employee number and name for employee working as clerk and earning highest salary among clerks.
SQL> select empno,ename from emp where job=’CLERK’ and sal=(select max(sal) from emp where job=’CLERK’);
EMPNO ENAME
7934 MILLER
Select the names of the employees who is earning highest salary in their respective departments.
SQL> select ename,sal,deptno from emp e where sal in(select max(sal) from emp m where m.deptno = e.deptno);
ENAME SAL DEPTNO
BLAKE 2850 30
SCOTT 3000 20
KING 5000 10
FORD 3000 20
How to display the number value in Words
SQL> select sal, (to_char(to_date(sal,’j’), ‘jsp’)) as Number_to_Word from emp;
Number_to_Word
1600 one thousand six hundred
1250 one thousand two hundred fifty
Show the current ’15-Aug-2012′ as 15th wednesday August twenty twelve.
SQL> select to_char(to_date(’15-Aug-2012′),’ddth day Month year’) as PROPERNAME
from dual;
PROPERNAME
15th wednesday August twenty twelve
How to display odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
How to display even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
Select the Employee name and Managers names.
SQL> select e.ename employee, m.ename manager from emp e, emp m where m.empno=e.mgr;
EMPLOYEEMANAGER
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
Select top 5 earners of company.
SQL> select * from (select * from emp order by sal desc) where rownum <= 5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17/11/1981 5000 10
7788 SCOTT ANALYST 7566 09/12/1982 3000 20
7902 FORDANALYST 7566 03/12/1981 3000 20
7566 JONES MANAGER 7839 02/04/1981 2975 20
7698 BLAKE MANAGER 7839 01/05/1981 2850 30
Select the department name and total number of employees in each department.
SQL> select dname, count(ename) as total from emp, dept where emp.deptno=dept.deptno group by dname;
DNAME TOTAL
ACCOUNTING 3
RESEARCH 5
SALES 6
Increase salary of all managers by 10%.
SQL> update emp set sal=sal*1.1 where empno in (select mgr from emp);
6 rows updated.
How to find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Delete duplicate department from dept table.
SQL> delete from dept where rowid not in (select max(rowid) from dept group by dname);
0 rows deleted
How to eliminate the duplicate rows from a table?
delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
or
delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
Create a table emp_tnp from existing table emp.
SQL> create table emp_tmp as select * from emp;
Table created
Show all the records of emp_tmp table.
select * from emp_tmp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17/12/1980 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 3272.5 20
7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 3135 30
7782 CLARK MANAGER 7839 09/06/1981 2695 10
7788 SCOTT ANALYST 7566 09/12/1982 3300 20
7839 KING PRESIDENT 17/11/1981 5500 10
7844 TURNER SALESMAN 7698 08/09/1981 1500 0 30
7876 ADAMS CLERK 7788 12/01/1983 1100 20
7900 JAMES CLERK 7698 03/12/1981 950 30
7902 FORDANALYST 7566 03/12/1981 3300 20
7934 MILLER CLERK 7782 23/01/1982 1300 10
Show installed Oracle version information
SQL> select banner from v$version;
I hope you learned some basics of SQL.
