DB Development frequently Asked Questions in various DB Development job Interviews by interviewer. Get prepared for the DB Development job interview by Pritish Kumar Halder:

1. How to find the first two highest salaries in deptno in emp table?

if u want select the second highest salary from the emp
table then u have to write query as

select max(salary) from emp where
salary < ( select max(salary) from emp )

2. What is the difference between a primary key and a unique key?

The basic difference between the primary key and unique is that the primary key is used to define a table in other tables for preventing data from anomalies, and the unique key is used in a table to apply unifications among different values or columns of tables and only in that table obviously. There is no usage of a unique key outward a table but the primary key is often used to make relations between other tables.

3. What is the use of a foreign key?

the foreign key is used to have a relation between two tables, which
requires a primary key in the master table and that field can be used by the child table through the foreign key.

4. How to create an external table?

//1st of all create a directory to store ur external table
CREATE DIRECTORY EMP_DIR AS ‘/FLAT_FILES’ ;

// now write the following line of code to create an
external table at that directory

CREATE TABLE OLDEMP(ENO NUMBER,NAME CHAR(20), DOB DATE,)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EMP_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE ‘BAD_EMP’)
LOGFILE ‘LOG_EMP’
FIELDS TERMINATED BY ‘,’
(ENO CHAR, ENAME CHAR DOB CHAR DATE_FORMAT
DATE MASK “DD-MON-YYYY”
)
)
LOCATION (‘EMP1.TXT’)
)
PARALLEL 5
REJECT LIMIT 200;

5. What is the difference between the case version and the decode version?

Decode
1.It’s a function
2.can compare only discrete values
3.pl/sql standards
4.cannot process null

Case
1. It’s an Expression
2. can handle range values
3. ANSI Standards
4. Processing time is faster when compared to Decode
5. can process null

6. What are the family trees and connections by clause?

On its face, the relational database management system would appear to be a very poor tool for representing and manipulating trees. This chapter is designed to accomplish the following things:
show you that a row in an SQL database can be thought of as an object
show you that a pointer from one object to another can be represented by storing an integer key in a regular database column
demonstrate the Oracle tree extensions (CONNECT BY … PRIOR)
show you how to work around the limitations of CONNECT BY with PL/SQL
The canonical example of trees in Oracle is the org chart.
create table corporate_slaves (
slave_id integer primary key,
supervisor_id references corporate_slaves,
name varchar(100)
);
insert into corporate_slaves values (1, NULL, ‘Big Boss Man’);
insert into corporate_slaves values (2, 1, ‘VP Marketing’);
insert into corporate_slaves values (3, 1, ‘VP Sales’);
insert into corporate_slaves values (4, 3, ‘Joe Sales Guy’);
insert into corporate_slaves values (5, 4, ‘Bill Sales Assistant’);
insert into corporate_slaves values (6, 1, ‘VP Engineering’);
insert into corporate_slaves values (7, 6, ‘Jane Nerd’);
insert into corporate_slaves values (8, 6, ‘Bob Nerd’);

7. How to find the duplicate rows count from employees table in oracle?

select employee_id, count(*) from employees group by
employee_id having count(*) > 1

8. In which phase of mload can i use .FILLER?

When your data file has additional columns comparet to your
target table, you specify “FILLER” against the attribute
against the control file and the same will not get loaded

9. Write short notes on XSU and JAVA?

java :it is programming language .it is m/c independent.
it is best for web development. it remo ve all the
drawbacks of c and c++.

10. Explain the syntax of named procedure?

DECLARE
v_date DATE;
v_char VARCHAR2(10);
v_number NUMBER;
BEGIN
NULL;
END;

Reference: https://www.globalguideline.com/interview_questions/Answer.php?a=Explain_the_syntax_of_named_procedure