Oracle SQL developer Interview questions

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

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

What is the difference between Truncate & Drop?

Explain SQL Having?

Difference between SQL Having & Where?

Difference between SQL IN/SQL Exists?

Difference between SQL NOT IN/SQL NOT Exists?

Difference between SQL UNION/SQL UNION ALL?

Explain SQL TOP.

How to delete duplicate records in a table?

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 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 CANDIDATE 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 value is also restricted.

Difference between Unique Key and Primary Key.

Difference between Cast & Convert.

Explain SQL Group by and give examples.

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 Nth highest salary from a table.

Write a query to select the 2nd highest salary from a table.

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)

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

Give examples of Create Table?

Answer

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

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