ORACLE PL/SQL Chapter Wise Interview Questions – DDL-DML

ORACLE PL/SQL Chapter Wise Interview Questions – DDL-DML

Question 1:
What is DDL?
Data definition language (DDL) statements enable you to perform these tasks:

  1. Create, alter, and drop schema objects
  2. Grant and revoke privileges and roles
  3. Analyze information on a table, index, or cluster
  4. Establish auditing options
  5. Add comments to the data dictionary

Question 2:
What is DML?
Data Manipulation Language (DML) statements query and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction.
The following are the DML statements available in Oracle: INSERT, UPDATE, DELETE, and MERGE

Question 3:
How can I change my Oracle password?

Question 4:
What are the most important DDL statements in SQL?
The most important DDL statements in SQL are:

  1. create table-creates a new database table
  2. alter table-alters (changes) a database table
  3. drop table-deletes a database table
  4. create index-creates an index (search key)
  5. drop index-deletes an index

Question 5:
Is the query below allowed:


Question 6:
How many rows will the following SQL return:

9 rows

Question 7:
How many rows will the following SQL return:

No rows

Question 8:
What is the difference between unique key and primary key?
Unique key can be null.
Primary key cannot be null.

Question 9:
What are the different SELECT statements?
The SELECT statements are:

  1. SELECT column_name(s) FROM table_nameSELECT DISTINCT column_name(s) FROM table_name
  2. SELECT column FROM table WHERE column operator value
  3. SELECT column FROM table WHERE column LIKE pattern
  4. SELECT column,SUM(column) FROM table GROUP BY column
  5. SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

Question 10:
What are INSERT INTO Statements?
The INSERT INTO statements are:

  1. INSERT INTO table_name VALUES (value1, value2…. )
  2. INSERT INTO table_name (column 1, column2,…)
    VALUES (value1, value2,….)

Question 11:
Write the Update Statement?

Question 12:
How can I eliminate duplicates values in a table?

Question 13:
How we can sort the Rows?
There are mainly three types:

  1. SELECT column1, column2,.,.FROM table_name ORDER BY columnX, columnY,..
  2. SELECT column1, column2,…FROM table_name ORDER BY columnX DESC
  3. SELECT column1, column2,…FROM table_name ORDER BY columnX DESC, columnY ASC

Question 14:
What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.

Question 15:
What is Delete Statements in SQL?
Delete statement in SQL is used to delete partial/all data. Especially delete statement is useful in case of partial delete depending upon our criteria otherwise use TRUNCATE to delete whole data from table.
When delete command fires:

  1. Triggers will fire (If created on that table)
  2. These will not auto commit changes made so there is one chance to rollback.

Question 16:
Why use Truncate over Delete while deleting all rows?

  1. Truncate is efficient. Triggers are not fired.
  2. It deallocates space (Unless REUSE STORAGE is given).

Question 17:
Can you delete a column in table with indexes in Oracle?
We can delete a column with indexes. But if there is a Primary or foreign key present make sure, foreign keys are deleted first and then column with Primary key.

Question 18:
How can you compare a part of the name rather than the entire name?

would return a recordset with records consisting empname the sequence ‘ab’ in empname.

Question 19:
What is the INSERT statement?
The INSERT statement lets you insert information into a database.

Question 20:
How do you delete a record from a database?
Use the DELETE statement to remove records or any particular column values from a database.

Question 21:
How could I get distinct entries from a table?
The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table would of course depend on the various conditions that are specified in the SQL query.

Question 22:
How to get the results of a Query sorted in any order?
You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.

Question 23:
How can I find the total number of records in a table?
You could use the COUNT keyword,

Question 24:
What is GROUP BY?
The GROUP BY keywords has been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.

Question 25:
What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table?

  • Dropping: (Table structure + Data are deleted), Invalidates the dependent objects, Drops the indexes
  • Truncating: (Data alone deleted), Performs an automatic commit, faster than delete
  • Delete: (Data alone deleted), doesn’t perform automatic commit

Question 26:
Difference between a “where” clause and a “having” clause.
Having clause is used only with group functions whereas Where is not used with.


About the author


Leave a Comment