SQL Interview Questions and Answers – 1

SQL Interview Questions and Answers

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.

Display all the records from employee table
SQL> select * from emp;
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      2975                20
7654     MARTIN        SALESMAN  7698    28/09/1981      1250    1400    30
7698     BLAKE          MANAGER   7839    01/05/1981      2850                30
7782     CLARK          MANAGER   7839    09/06/1981      2450                10
7788     SCOTT            ANALYST     7566    09/12/1982      3000                20
7839     KING  PRESIDENT              17/11/1981      5000                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      3000                20
7934     MILLER         CLERK           7782    23/01/1982      1300                10

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.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)