Advanced Oracle Pl SQL Interview Questions 2018-Learn Now!

Q. What is SQL and also describe types of SQL statements?

SQL stands for Structured Query Language. SQL is a language used to communicate with the server to access, manipulate and control data.

There are 5 different types of SQL statements.

1.     Data Retrieval: SELECT

2.     Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE

3.     Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.

4.     Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT

5.     Data Control Language (DCL): GRANT, REVOKE

Q. What is an alias in SQL statements?

Alias is a user-defined alternative name given to the column or table. By default column alias headings appear in upper case. Enclose the alias in a double quotation marks (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.

For ex: Select empname AS name from employee; (Here AS is a keyword and “name” is an alias).

Q. What is a Literal? Give an example where it can be used?

A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks (‘ ‘), number literals need not.

For ex: Select last_name||’is a’||job_id As “emp details” from employee; (Here “is a” is a literal).

Q. What are SQL functions? Describe in brief different types of SQL functions?

SQL Functions are very powerful feature of SQL. SQL functions can take arguments but always return some value.
There are two distinct types of SQL functions:

1) Single-Row functions: These functions operate on a single row to give one result per row.

Types of Single-Row functions:

1.     Character

2.     Number

3.     Date

4.     Conversion

5.     General

2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:

1.     AVG

2.     COUNT

3.     MAX

4.     MIN

5.     SUM

6.     STDDEV

7.     VARIANCE

Q. Explain character, number and date function in detail?

Character functions: accept character input and return both character and number values. Types of character function are:

a) Case-Manipulation Functions: LOWER, UPPER, INITCAP

b) Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE

Number Functions: accept Numeric input and return numeric values. Number Functions are: ROUND, TRUNC and MOD

Date Functions: operates on values of the Date data type. (All date functions return a value of DATE data type except the MONTHS_BETWEEN Function, which returns a number. Date Functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC. 

Q. What is a Dual Table?

Dual table is owned by the user SYS and can be accessed by all users. It contains one columnDummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn or expression that is not derived from a table with user data.

If you're looking for Oracle PL SQL Interview Questions for Experienced or Freshers, you are at right place. Mindmajix offers Advanced Oracle PL SQL Interview Questions 2018 that helps you in cracking your interview & acquire dream career as Oracle PL SQL Developer.

Enroll now:  https://mindmajix.com/oracle-pl-sql-interview-questions