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

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

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

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

Question 2:
What is DML?
Answer:
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?
Answer:

From SQI*Plus type: ALTER USER username IDENTIFIED BY new_password

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

create table-creates a new database table
alter table-alters (changes) a database table
drop table-deletes a database table
create index-creates an index (search key)
drop index-deletes an index

Question 5:
Is the query below allowed:

Select sal, ename Into x From emp Where ename = 'KING'
(Where x is a record of Number(4) and Char(15))

Answer:
Yes

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

Select *from emp Where rownum < 10;

Answer:
9 rows

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

Select *from emp Where rownum = 10;

Answer:
No rows

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

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

SELECT column_name(s) FROM table_nameSELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

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

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

Question 11:
Write the Update Statement?
Answer:

UPDATE table_name SET column_name = new_value 
WHERE column_name = some_value

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

SQL> DELETE FROM table_name A WHERE ROWED > 
(SELECT min(round) FROM table_name B WHERE 
A.key_values = B.key_values);

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

SELECT column1, column2,.,.FROM table_name ORDER BY columnX, columnY,..
SELECT column1, column2,…FROM table_name ORDER BY columnX DESC
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?
Answer:
It is used to drop constraints specified on the table.

Question 15:
What is Delete Statements in SQL?
Answer:
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:

Triggers will fire (If created on that table)
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?
Answer:

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

Question 17:
Can you delete a column in table with indexes in Oracle?
Answer:
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?
Answer:

SELECT * FROM people WHERE empname LIKE '%ab%'

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

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

Question 20:
How do you delete a record from a database?
Answer:
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?
Answer:
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.

Example: SELECT DISTINCT empname FROM emptable

Question 22:
How to get the results of a Query sorted in any order?
Answer:
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.

SELECT empname, age, city FROM emptable ORDER BY empname

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

E.g.: SELECT COUNTY*) FROM emp WHERE age>40

Question 24:
What is GROUP BY?
Answer:
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?
Answer:

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.
Answer:
Having clause is used only with group functions whereas Where is not used with.

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

Related