SQL Plus Interview Questions

Q – 1 Explain What is meant by Scrollable cursor?

Ans- A scrollable cursor, however, can move forward and backward, and can seek any desired record in the cursor. Such operations are common in applications that present results sets in scrolling windows. With a scrollable cursor, application developers do not need to create and manage their own buffer for the records.

Q – 2 Explain What is the output of SIGN function?

Ans- SIGN (a): Returns 1 if a is positive or if a is 0, and -1 if a is less than 0.

Q – 3 How to use SQL to build SQL, what is this called and give an example?

Ans- This is called dynamic SQL. An example would be: set lines 90 pages 0 termout off feedback off verify off spool drop_all.sql
select ?drop user ?||username||? cascade;? from dba_users where username not in (“SYS?,?SYSTEM?); spool off Essentially you are looking to see that they know to include a command (in this case DROP USER…CASCADE;) and that you need to concatenate using the ?||? the values selected from the database.

Q – 4 Explain What is the maximum number of triggers, can apply to a single table?

Ans- Insert/Update/Delete :- 3
Before/After:- 2
Row Level/Statement Level:-2

Hence 3*2*2

Q – 5 Explain What is meant by SORTING and GROUPING?

Ans- For sorting we use order by clause in select statement. This is used to sort data in ascending order or descending order.

To group data based on perticulr column we use groupby clause.
Both are used to get distinct values.

Q – 6 Explain How do we eliminate the duplicate rows?

Ans- Use the DISTINCT keyword right after SELECT…

i.e. SELECT DISTINCT customername FROM customer

select * from emp e where rownum=
(select max(rownum) from emp ee
where e.empno=ee.empno)

Q – 7 Explain What is an Integrity Constraint?

Ans- Integrity constraint is a rule that restricts values to a column in a table.

Q – 8 Explain What is a Cartesian product?

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

Q – 9 Explain some PL/SQL Exceptions?

Ans- TOO_MANY_ROWS

NO_DATA_FOUND

INVALID_CURSORS

CURSOR_ALREADY_OPEN

DUP_VAL_ON_INDEX

Q – 10 Explain What are the different types of SQL?

Ans- There are 5 types

1.data definition type

2.data manipulation

3.data control

4.transaction control

5.data query

SQL is Structured Query Language is a database computer language designed for managing data in relational database management systems (RDBMS).

PostgreSQL is an object-relational database management system (ORDBMS).It is released under a BSD-style license and is thus free software. As with many other open-source programs, PostgreSQL is not controlled by any single company, but has a global community of developers and companies to develop it.

SQLite is an ACID-compliant embedded relational database management system contained in a relatively small (~225 KB) C programming library. The source code for SQLite is in the public domain.

MySQL is a relational database management system (RDBMS) which has more than 6 million installations. MySQL stands for “My Structured Query Language”. The program runs as a server providing multi-user access to a number of databases.

Q – 11 Explain Can a view be updated/inserted/deleted?

Ans- if a view is update, deleted , or inserted wil the changes be refelected on the base table

Q – 12 Explain How do we replace a comma (,) with a blank in a select statement?

Ans- select empno||’ ‘||ename from emp;

select replace (column name,’,’,”) from table name.

Q – 13 Explain What are the data types allowed in a table?

Ans- CHAR,
VARCHAR2,
NUMBER,
DATE,
RAW,
LONG and
LONG RAW.

Q – 14 What is a join in Oracle? Explain the different types of joins?

Ans- Join is a query which retrieves related columns or rows from multiple tables.Self Join – Joining the table with itself.Equi Join – Joining two tables by equating two common columns.Non-Equi Join – Joining two tables by equating two common columns.Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

JOIN: Return rows when there is at least one match in both tables

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

FULL JOIN: Return rows when there is a match in one of the tables

Q – 15 Explain How to drop the index?

Ans- Drop Index Indexname

Q – 16 Explain What is a corelated subquery?

Ans- Correlated subquery is the subquery, where the subquery has been executed for the every row processed by the parent statement.

Q – 17 Explain What is a database link?

Ans- Database Link is a named path through which a remote database can be accessed

Q – 18 Explain Explicit Cursor attributes?

Ans- %IS OPEN,%FOUND,%NOT FOUND,%ROW COUNT

Q – 19 Explain ROWID?

Ans- ROWID is a pseudo column generated at the run time(during inserting value). Its a hexadecimal value which is unique identification for each record

Q – 20 Please Explain Connect by Prior?

Ans- Retrieves rows in hierarchical order.e.g. select empno, ename from emp where.

“connect by prior” is clause which is used in hierarchical queries.Example select ename,empno,mgr,job from emp start with job=’PRESIDENT’
connect by prior empno=mgr;