SQL Interview Questions

What Is SQL?

SQL (pronounced as the letters S-Q-L or as sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database.

Which command displays the SQL command in the SQL buffer, and then executes it?

RUN

How can you call a PL/SQL procedure from SQL?

By use of the EXECUTE (short form EXEC) command.

Which is the subset of SQL commands used to manipulate Oracle Database structures?

Data Definition Language (DDL)

Write a query to select the second highest salary from a table.

SELECT max(salary) AS salary_2
FROM emp
WHERE salary < (SELECT max(salary) AS salary_1                               
                FROM emp)  

Write a query to select the 5th highest salary from a table.

SELECT min(salary) AS high5  
FROM employee  
WHERE salary IN(SELECT DISTINCT TOP 5 salary                               
                FROM employee                               
                ORDER BY salary DESC)  

How to find duplicate records with the number they are duplicated?

SELECT Id, count (*) as num_records
from table  
group by id  
having count (*) > 1

What command is used to get back the privileges offered by the GRANT command?

REVOKE

How do you execute a host operating system command from within SQL?

By use of the exclamation point “!” (in UNIX and some other OS) or the HOST (HO) command.

What command is used to create a table by copying the structure of another table?

CREATE TABLE .. AS SELECT command


What is the difference between Delete and Truncate command in SQL?

Delete command and truncate command both will delete the data, however the truncate command can not be rolled back as delete can be. TRUNCATE is a DDL command whereas DELETE is a DML command. The delete command can be used for selected records using the where clause but with the truncate command we have to loose data. DELETE statement is a logged operation and hence takes more time then truncate.

How can variables be passed to a SQL routine?

By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself:

“select * from dba_tables where owner=&owner_name;” .

Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.

What SQLPlus command is used to format output from a select?

This is best done with the COLUMN command.

Can Primary key is a Foreign Key on the same table?

Yes

You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?

Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:

select rowid from emp e
where e.rowid > (select min(x.rowid)
                 from emp x
                 where x.emp_no = e.emp_no);

What is the use of the DROP option in the ALTER TABLE command?

It is used to drop constraints specified on the table.

What is a Cartesian product?

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

How do you prevent output from coming to the screen?

The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.

What is the use of CASCADE CONSTRAINTS?

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

Which function is used to find the largest integer less than or equal to a specific value?

FLOOR

What is a PRIMARY KEY?

The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.

What is a FOREIGN KEY?

A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.

What is a UNIQUE KEY?

A UNIQUE KEY is one or more columns that must be unique for each row of the table.

What is the difference between UNIQUE and PRIMARY KEY?

The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as <null> value is also restricted.

Consider the below DEPT and EMPLOYEE table and answer the below queries.

DEPT
DEPTNO (NOT NULL , NUMBER(2)),
DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)

EMPLOYEE
EMPNO (NOT NULL , NUMBER(4)),
ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)),
MGR (NUMBER(4)),
HIREDATE (DATE),
SAL (NUMBER(7,2)),
COMM (NUMBER(7,2)),
DEPTNO (NUMBER(2))

MGR is the EMPno of the Employee whom the Employee reports to. DEPTNO is a foreign key.

List all the Employees who have at least one person reporting to them.

SELECT ENAME
FROM EMPLOYEE
WHERE EMPNO IN (SELECT MGR FROM EMPLOYEE);

List the highest salary paid for each job.

SELECT JOB, MAX(SAL)
FROM EMPLOYEE
GROUP BY JOB

In which year did most people join the company? Display the year and the number of Employees.

SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES"
FROM EMPLOYEE
GROUP BY TO_CHAR(HIREDATE,'YYYY')
HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO))
                      FROM EMPLOYEE
                      GROUP BY TO_CHAR(HIREDATE,'YYYY'));

Write a correlated sub-query to list out the Employees who earn more than the average salary of their department.

SELECT ENAME,SAL
FROM EMPLOYEE E
WHERE SAL > (SELECT AVG(SAL)
            FROM EMPLOYEE F
            WHERE E.DEPTNO = F.DEPTNO);

Find the nth maximum salary.

SELECT ENAME, SAL
FROM EMPLOYEE A
WHERE &N = (SELECT COUNT (DISTINCT(SAL))
               FROM EMPLOYEE B
               WHERE A.SAL<=B.SAL);   

Select the duplicate records (Records, which are inserted, that already exist) in the EMPLOYEE table.

SELECT * FROM EMPLOYEE A 
WHERE A.EMPNO IN (SELECT EMPNO                                          
                 FROM EMPLOYEE                                          
                 GROUP BY EMPNO                                           
                 HAVING COUNT(EMPNO)>1)
                 AND A.ROWID!=MIN (ROWID));

Write a query to list the length of service of the Employees (of the form n years and m months).

SELECT ENAME "EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))
||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN 
(SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE"
FROM EMPLOYEE;