ORACLE PL/SQL Chapter Wise Interview Questions – DDL-DML
What is DDL?
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
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
How can I change my Oracle password?
From SQI*Plus type: ALTER USER username IDENTIFIED BY new_password
What are the most important DDL statements in SQL?
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
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))
How many rows will the following SQL return:
Select *from emp Where rownum < 10;
How many rows will the following SQL return:
Select *from emp Where rownum = 10;
What is the difference between unique key and primary key?
Unique key can be null.
Primary key cannot be null.
What are the different SELECT statements?
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
What are INSERT INTO Statements?
The INSERT INTO statements are:
- INSERT INTO table_name VALUES (value1, value2…. )
- INSERT INTO table_name (column 1, column2,…)
VALUES (value1, value2,….)
Write the Update Statement?
UPDATE table_name SET column_name<em> = </em>new_value
WHERE column_name = some_value
How can I eliminate duplicates values in a table?
SQL> DELETE FROM table_name A WHERE ROWED >
(SELECT min(round) FROM table_name B WHERE
A.key_values = B.key_values);
How we can sort the Rows?
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
What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
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:
- Triggers will fire (If created on that table)
- These will not auto commit changes made so there is one chance to rollback.
Why use Truncate over Delete while deleting all rows?
- Truncate is efficient. Triggers are not fired.
- It deallocates space (Unless REUSE STORAGE is given).
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.
How can you compare a part of the name rather than the entire name?
SELECT * FROM people WHERE empname LIKE '%ab%'
would return a recordset with records consisting empname the sequence ‘ab’ in empname.
What is the INSERT statement?
The INSERT statement lets you insert information into a database.
How do you delete a record from a database?
Use the DELETE statement to remove records or any particular column values from a database.
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.
Example: SELECT DISTINCT empname FROM emptable
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.
SELECT empname, age, city FROM emptable ORDER BY empname
How can I find the total number of records in a table?
You could use the COUNT keyword,
E.g.: SELECT COUNTY*) FROM emp WHERE age>40
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.
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
Difference between a “where” clause and a “having” clause.
Having clause is used only with group functions whereas Where is not used with.