Q1. Difference between DBMS & RDBMS
Answer.
| DBMS | RDBMS |
| Data is stored in form of files. | Data is stored in form of tables. |
| Normalization is not applied. | Normalization is applied. |
| Relations between different data is not set as data is stored in form of files. | Relationship between tables can be set as data is stored in form of tables. |
| Doesn’t support distributed database system. | Distributed database system is supported. |
| Cannot handle multiple users & huge data efficiently. Supports single user only. | Handles multiple users & huge data efficiently. |
| Integrity constraints cannot be applied. | Integrity constraints can be applied. |
| Low Software & Hardware needs. | High-end software & high-end hardware is required. |
| Satisfies less than seven of Dr. E.F. Codd Rules. | Satisfies 8 to 10 of Dr. E.F. Codd Rules. |
| Doesn’t support client-server architecture. | Supports client-server architecture. |
| Flat Files, Excel Files, XML, etc. | ORACLE, MSSQL, DB2, MySQL, etc. |
Q2. What are joins and Types of join?
Answer. When we need to retrieve data from two or more tables to make our result complete, we need to perform a join. Following are the types of joins:
- INNER JOIN: This join returns rows when there is at least one match in both the tables.
- SELF JOIN: When a table joins to itself then it is called self-join.
- OUTER JOIN: There are three different Outer Join methods.
- LEFT OUTER JOIN: This join returns all the rows from the left table with the matching rows from the right table. If there are no field matching in the right table then it returns NULL values
- RIGHT OUTER JOIN: Right outer join returns all the rows from the right table with the matching rows from the left table. If there are no field matching in the left table then it returns NULL values
- FULL OUTER JOIN: Full outer join merge left outer join and right outer join. this returns row from either table when the conditions are met and returns null value when there is no match
- CROSS JOIN: Cross join does not necessarily require any condition to join. The output result contains records that are multiplication of record from both the tables.
Q3. For which DB objects Synonym can be created?
Answer. Table, View, Materialized View, Sequence, Procedure, Function, Package, User-defined type, Java Class Schema Object & Synonym
Q4. Explain Commit, Rollback and Savepoint.
Answer.
For a COMMIT statement, the following is true:
• Other users can see the data changes made by the transaction.
• The locks acquired by the transaction are released.
• The work done by the transaction becomes permanent.
A ROLLBACK statement gets issued when the transaction ends,
and the following is true.
• The work done in a transition is undone.
• All locks acquired by transaction are released.
• It undoes all the work done by the user in a transaction.
With SAVEPOINT, only part of transaction can be done or undone.
Q5. What is the difference between Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL) & Transaction Control Language (TCL) statements?
Answer.
DDL statements are used for defining data.
E.g.: Create, Alter, Drop, Truncate, Rename.
DML statements are used for manipulating data.
E.g.: Insert, Update, Delete, Merge.
DCL statements are used for to control the access of data.
E.g.: Grant, Revoke.
TCL statements are used for data saving.
E.g.: Commit, Rollback, Savepoint.
Q6. What are the properties of the transaction?
Answer. Properties of the transaction are known as ACID properties, such as
- Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully, if not then transaction is aborted at the failure point and it is rolled back to its initial state.
- Consistency: Ensures that all changes made through successful transaction are reflected properly in the database.
- Isolation: Ensures that all transactions are performed independently, and changes made by one transaction are not reflected on other transactions.
- Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure.
Q7. What is the significance of the & and && operators in PL/SQL?
Answer. The & operator means that the PL/SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.
Q8. Which is faster – IN or EXISTS?
Answer. EXISTS is faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
Q9. What is the difference between the following two statements?
- Drop Table Tablename;
- Drop Table Tablename Purge;
Answer. A new feature was introduced in Oracle Database 10g for dropping tables. When table is dropped, the database does not immediately release the space associated with the table. Rather, it renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACK TABLE statement if one feels that the table was dropped by mistake.
If the user wants to immediately release the space associated with the table at the time the DROP TABLE statement is issued, then include the PURGE clause must follow the DROP TABLE statement. If PURGE is specified, then the database does not place the table and its dependent objects into the recycle bin.
Q10. Can we code DDL statements in stored procedure? If yes, give example. Or What is Dynamic SQL?
Answer. Yes, we code DDL statements in stored procedure using Dynamic SQL.
Declare
SQLStr varchar2(2000);
Begin
SQLStr = ‘Drop Table tab’;
Execute immediate SQLStr;
End;
All DDL, DML, DCL commands can be executed using Dynamic SQL. Q11. Which symbol precedes the path to the table in the remote database?
Answer. @
Q12. What are %TYPE and %ROWTYPE? What are the advantages of using these over data types?
Answer. %TYPE provides the data type of a variable or a database column to that variable. %ROWTYPE provides the record type that represents an entire row of a table or view or columns selected in the cursor.
The advantages are:
• need not know about variable’s data type
• If the database definition of a column in a table changes, the data type of a variable changes accordingly.
Q13. What is difference between %ROWTYPE and TYPE RECORD?
Answer.
• %ROWTYPE is to be used when an entire row of a table or a view or a cursor is to be stored in a variable.
• TYPE RECORD is used when data from columns of different tables or views or cursor or variables are to be stored in a variable.
Q14. Give Comparison of RowNum & RowId.
Answer.
| RowID | RowNum |
| ROWID uniquely identifies where a row resides on disk. It contains the information of the disk number, the cylinder number, the block number and the offset number into the block. | ROWNUM is a “pseudo-column”, a placeholder that one can reference in SQL. |
| ROWID is permanent. | ROWNUM is temporary. |
| ROWID can be used to fetch a row. | ROWNUM is a way of referencing rows within a fetched result set. |
Q15. Difference between DDL and DML statement?
Answer.
| Difference | DDL | DML |
| Examples | Create, Alter, Drop, Truncate | Insert, Update, Delete |
| Purpose | Maintenance of DB objects | Manipulation of data in DB Objects |
| Commit | Automatic | Explicit |
| PL/SQL | Using Dynamic SQL “Execute Immediate” | Allowed to use directly |
Q16. Difference between DELETE & TRUNCATE statement
Answer.
| Delete | Truncate |
| DML | DDL |
| where clause available | where clause not available |
| Activates trigger | Does not activate trigger |
| Logs created & Rollback possible, hence slower | Logs not created & Rollback not possible, hence faster |
| Does not reset identity of table | Resets identity of table. |
| No impact on DB Replication | Not advisable to use it if DB Replication is active |
Q17. Differentiate between Decode & Case.
Answer.
| Case | Decode |
| 1. Statement | 1. Function |
| 2. All logical operators can be used. | 2. No logical operators can be used. Only equality checks performed. |
| 3. Can be used in PL/SQL & SQL. | 3. Can be used only in SQL. |
| 4. Can replace If Statements. | 4. Cannot do so as limited to SQL only. |
| 5. Can replace Decode Function. | 5. Cannot replace Case Statement. |
Q18. What is difference between SUBSTR and INSTR?
Answer.
- SUBSTR returns a specified portion of a string. e.g., SUBSTR(‘BCDEF’, 4) output is BCDE.
- INSTR provides character position in which a pattern is found in a string. e.g., INSTR(‘ABC-DC-F’, ‘-‘, 2) output is 7 (2nd occurrence of ‘-‘).
Q19. What is difference between Rename and Alias?
Answer. Rename is a permanent name given to a table or column.
Alias is a temporary name given to a table or column, which does not exist once the SQL statement is executed.
Q20. What is UTL_File?
Answer. UTL_File is Oracle provided Utility Package to work with File I/O. It is mainly used for import & export of the data to & from Oracle Database.
Q21. Can we perform DDL & DML operations using functions?
Answer. Yes, however you can’t call such function using SQL statement.
Q22. Why do we require “Replace” keyword in the following commands?
Create or replace Procedure …
Create or replace Function …
Create or replace Package …
Create or replace Trigger …
Answer. To preserve grants given to the users on those DB objects, if any.
Q23. Can rows from two different tables have the same ROWID?
Answer. Possible, if they are in a Cluster.
Q24. What is a pseudo column? Give some examples.
Answer. It is a column that is not an actual column in the table, e.g., UID, ROWNUM, ROWID, LEVEL, CONNECT_BY_ISCYCLE.
Q25. What is Grant & Revoke?
Answer. GRANT & REVOKE both are used to manage the privileges of both users and roles throughout the database.
Grant is to award the privileges e.g., Grant Privileges on Object to User;
Revoke is to withdraw the awarded privileges e.g.,Revoke Privileges on Object to User;
Q26. Can we create & drop packages, procedures, functions dynamically?
Answer. Yes, it can be done using dynamic SQL, i.e., Execute Immediate;
Q27. What are the data types available in PL/SQL?
Answer.
- Scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
- Composite data types such as RECORD, Collections & TABLE.
Q28. When is HAVING clause used?
Answer. HAVING clause is like WHERE clause. It is used to apply filters on the data returned by GROUP BY query.
SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 25000 And dept_no <> 9; Q29. What is returning Into clause in DML statements?
Answer. The returning into clause specifies the values returned from DELETE, INSERT, UPDATE & EXECUTE IMMEDIATE statements. Column values can be retrieved into individual variables or into collections. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined.
DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE,
salary employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
v_employee_id employees.employee_id%TYPE;
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = emp_id
RETURNING last_name, salary
INTO emp_info;
DBMS_OUTPUT.PUT_LINE('Just gave a raise to ' || emp_info.last_name ||
', who now makes ' || emp_info.salary);
commit;
Delete employees
where employee_last_name = emp_info.last_name
and salary = emp_info.salary
returning employee_id
into v_employee_id;
DBMS_OUTPUT.PUT_LINE('Just deleted employee with ID ' ||
v_employee_id);
commit;
END;
DECLARE
TYPE NameList IS TABLE OF VARCHAR2(15);
enames NameList;
bonus_amt NUMBER := 50;
sql_stmt VARCHAR(200);
BEGIN
sql_stmt := 'UPDATE employees
SET salary = salary + :1
RETURNING last_name INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING bonus_amt
RETURNING BULK COLLECT
INTO enames;
END; Q30. What is bulk collect?
Answer. Bulk collect is used to improve the performance while loading huge number of records from queries.
CREATE TABLE bulk_test AS
SELECT owner, object_name, object_id
FROM all_objects;
SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_test_tab IS TABLE OF bulk_test%ROWTYPE;
l_tab t_bulk_test_tab := t_bulk_test_tab();
l_start NUMBER;
BEGIN
--Time regular population.
l_start := DBMS_UTILITY.get_time;
FOR cur_rec IN (SELECT *
FROM bulk_test)
LOOP
l_tab.extend;
l_tab(l_tab.last) := cur_rec;
END LOOP;
DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' ||
(DBMS_UTILITY.get_time - l_start));
--Time bulk population.
l_start := DBMS_UTILITY.get_time;
SELECT *
BULK COLLECT
INTO l_tab
FROM bulk_test;
DBMS_OUTPUT.put_line('Bulk (' || l_tab.count || ' rows): ' ||
(DBMS_UTILITY.get_time - l_start));
END;
Regular (42578 rows): 66
Bulk (42578 rows): 4
Bulk collect is to be done using Limit clause as shown below,
otherwise it may cause considerable performance problem.
DECLARE
TYPE t_bulk_test_tab IS TABLE OF bulk_test%ROWTYPE;
l_tab t_bulk_test_tab;
CURSOR c_data IS
SELECT *
FROM bulk_test;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10000;
EXIT WHEN l_tab.count = 0;
--Process contents of collection here.
DBMS_OUTPUT.put_line(l_tab.count || ' rows');
END LOOP;
CLOSE c_data;
END;
10000 rows
10000 rows
10000 rows
10000 rows
2578 rows Q31. Explain COALESCE function.
Answer. COALESCE function returns the first not null expression from the expression list. At least two expressions are to be specified. If all expressions evaluate to null, then the function returns null.
COALESCE (expr1, expr2); is same as
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE expr2 END;
Q32. What is the purpose of “Host” & “!”?
Answer. “Host” is a SQLPlus command, equivalent to “!” command in UNIX/Linux. It is used in Windows to shell out of SQLPlus without exiting SQL*Plus. Once shelled out, you may fire any Windows DOS commands. Host can be used in PL/SQL too.
Example:
Windows ==> Host notepad myfile.sql
UNIX/Linux ==> !vi myfile.sql
PL/SQL ==> declare
cmd varchar2(100):='C:\Docs\runme.sql';
begin
host(cmd);
end; Q33. Difference between TRANSLATE and REPLACE
Answer.
TRANSLATE function replaces a sequence of characters in a string with another set of characters. It replaces a single character at a time.
TRANSLATE(‘Aptech23’, ‘A123’, ‘Z456’); ==> Result: ‘Ztech56’
REPLACE function replaces a sequence of characters in a string with another set of characters.
REPLACE(‘Hi Here & Hi There’, ‘Hi’); ==> Result: ‘Here & There’
REPLACE(‘9912399’, ‘9’); ==> Result: ‘123’
REPLACE(‘0000123’, ‘0’, ‘9’); ==> Result: ‘9999123’
Q34. Select for update
Answer. Adding FOR UPDATE clause to a SELECT statement lets you lock the selected rows, if there are no prevailing locks on it. Other users cannot lock or update those rows until you end your transaction using Commit or Rollback. It cannot be used with Distinct Operator, Set Operators, Group by & Aggregate Functions.
Select EmpNo, EName, Job From Emp
Where EmpNO = 7900 For update of EName;
Q35. What is the use of “@” & “@@”?
Answer. “@” & “@@” are used to run a script. These commands are almost identical to the START command. Suppose that you have the following script named PRINTRPT:
SELECT DEPARTMENT_ID, CITY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
@EMPRPT.SQL
@@WKRPT.SQLWhen you START PRINTRPT and it reaches the @ command, it looks for the script named EMPRPT.SQL in the current working directory and runs it. When PRINTRPT reaches the @@ command, it looks for the script named WKRPT.SQL in the same path as PRINTRPT and runs it. These commands can be disabled in the Product user profile.
Q36. What are comments?
Answer. Comments are the means by which the developer of the program can insert program details in simple English in the programs developed. Oracle supports 2 types of comments, i.e., single line comments using — and multiline comments using /* */ as demonstrated below.
SELECT last_name, salary + NVL(commission_pct, 0),
job_id, e.department_id
/*Select all employees whose compensation is
greater than that of Pitamber*/
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND salary + NVL(commission_pct,0) > --Subquery follows below
(SELECT salary + NVL(commission_pct,0)
FROM employees
WHERE last_name = 'Pitamber');Q37. Explain Import & Export
Answer. Export is a command line utility that provides a simple way to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. Export extracts all or selected db objects along with data and writes it to an Export file also known as Export dump file.
Import is a command line utility that reads the db object definitions and the data from an Export dump file & restores them into an Oracle database.
Q38. How many Aggregate Functions are available there in SQL?
Answer. SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value. There are 6 aggregate functions as listed below:
| AVG() | Returns the average value from specified columns |
| COUNT() | Returns number of table rows |
| MAX() | Returns largest value among the records |
| MIN() | Returns smallest value among the records |
| SUM() | Returns the sum of specified column values |
| Median() | Returns the middle value |
Q39. Explain Soundex.
Answer. SOUNDEX function facilitates searching character strings containing similar phonetic representations. This function lets you compare words that are spelled differently, but sound alike in English.
SELECT last_name, first_name
FROM employees
WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE')
ORDER BY last_name, first_name; | LAST_NAME | FIRST_NAME |
| Smith | Lindsey |
| Smith | William |
In case of any discrepancies or doubts do contact me.
Best of Luck Friends.
TechOraFAQ 
Good initiative
LikeLike
Thanks a lot for sharing these questions and answers.
LikeLike
Good work. Very helpful. Appreciate the efforts of the author.
LikeLike