RSS Feed

SQL Interview Questions


SQL Interview Questions
(few questions are repeated with small differences in their answers)

SQL Interview QuestionsWhat 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.

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

RUN

SQL Interview QuestionsWhat is the difference between Truncate & Drop?

Answer

SQL Interview QuestionsExplain SQL Having?

Answer

SQL Interview QuestionsDifference between SQL Having & Where?

Answer

SQL Interview QuestionsDifference between SQL IN/SQL Exists?

Answer

SQL Interview QuestionsDifference between SQL NOT IN/SQL NOT Exists?

Answer

SQL Interview QuestionsDifference between SQL UNION/SQL UNION ALL?

Answer

SQL Interview QuestionsExplain SQL TOP.

Answer

SQL Interview QuestionsHow to delete duplicate records in a table?

Answer

SQL Interview QuestionsHow to find duplicate records with the number they are duplicated?

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

SQL Interview QuestionsWhat is a PRIMARY KEY?

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

SQL Interview QuestionsWhat is a FOREIGN KEY?

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

SQL Interview QuestionsWhat is a UNIQUE KEY?

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

SQL Interview QuestionsWhat 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.

SQL Interview QuestionsDifference between Unique Key and Primary Key.

Answer

SQL Interview QuestionsDifference between Cast & Convert.

Answer

SQL Interview QuestionsExplain SQL Group by and give examples.

Answer 1

Answer 2

SQL Interview QuestionsHow can you call a PL/SQL procedure from SQL?

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

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

Data Definition Language (DDL)

SQL Interview QuestionsWrite a query to select the Nth highest salary from a table.

Answer

SQL Interview QuestionsWrite a query to select the 2nd highest salary from a table.

Answer

SQL Interview QuestionsWrite 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) 
 
SQL Interview QuestionsWrite 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) 
 
SQL Interview QuestionsWhat command is used to get back the privileges offered by the GRANT command?

Revoke


SQL Interview QuestionsHow 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.

SQL Interview QuestionsWhat command is used to create a table by copying the structure of another table?

CREATE TABLE .. AS SELECT command

SQL Interview QuestionsGive examples of Create Table?

Answer  

SQL Interview QuestionsWhat is the difference between Delete and Truncate command in SQL?

Answer  

SQL Interview QuestionsWhat 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.  

SQL Interview QuestionsHow 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.  

SQL Interview QuestionsWhat SQLPlus command is used to format output from a select?

This is best done with the COLUMN command.  

SQL Interview QuestionsCan Primary key is a Foreign Key on the same table?

Yes  

SQL Interview QuestionsYou 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); 
 
SQL Interview QuestionsWhat is the use of the DROP option in the ALTER TABLE command?

It is used to drop constraints specified on the table.  

SQL Interview QuestionsWhat 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.  

SQL Interview QuestionsHow 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.  

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

FLOOR  

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.

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

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

2. List the highest salary paid for each job.

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

3. 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'));

4. 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);

5. 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);   

6. 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));

7. 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;