21. What is the difference between view and materialized view?
22. Can you issue a DDL command inside a procedure, function or a package? Why and how this functionality can be achieved within procedure, function or a package?
We cannot execute any DDLs within any procedures, functions or packages. They are used only to perform series of SQL transactions. They will use only queries like SELECT, INSERT, DELETE and UPDATE. Any DDL commands – which alter the structure of the tables or views, cannot be executed by them.
Procedures and functions are mainly used for compile it and execute it any number of times. Suppose we have a DDL statements like CREATE, DROP or TRUNCATE within a procedure and we execute it a number of times. What will happen? It will execute without any issue for the first time. What will happen to the second execution? It will throw an error. Why? We are creating a structure or dropping the structure which is already created or dropped. This will be an error and we are not checking it again here. In addition, use of having DDLs within procedure is very limited.
Still there will be some requirement, where we would need to modify the objects in the database or we might have to create some temporary table for maintaining temporary data for some calculations. This can be achieved by issuing dynamic SQL using ‘EXECUTE IMMEDIATE’ command. But before using dynamic SQL, the owner of the procedure/function/ package should have the privilege to execute the DDL or create the user or the permission to give grants. That means, the user/ owner of the procedure should have permission to execute the action that we are going to perform within the procedure.
CREATE OR REPLACE PROCEDURE sp_Example IS BEGIN EXECUTE IMMEDIATE ‘CREATE TABLE tbl_Example (ID NUMBER(8))’; EXECUTE IMMEDIATE ‘GRANT SELECT tbl_Example TO Joseph’; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (‘Error while executing the procedure’); END sp_Example;
In order to execute above procedure, the owner of the procedure should have permission to create the table and provide grant to other users. Then above procedure will execute successfully.
23. What are the components of Oracle Database?
Components of a database include
- User : Users are the one who really uses the database. Users can be administrator, developer or the end users.
- Data or Database : As we discussed already, data is one of the important factor of database. A very huge amount of data will be stored in the database and it forms the main source for all other components to interact with each other. There are two types of data. One is user data. It contains the data which is responsible for the database, i.e.; based on the requirement, the data will be stored in the various tables of the database in the form of rows and columns. Another data is Metadata. It is known as ‘data about data’, i.e.; it stores the information like how many tables, their names, how many columns and their names, primary keys, foreign keys etc. basically these metadata will have information about each tables and their constraints in the database.
- DBMS : This is the software helps the user to interact with the database. It allows the users to insert, delete, update or retrieve the data. All these operations are handled by query languages like MySQL, Oracle etc.
- Database Application : It the application program which helps the users to interact with the database by means of query languages. Database application will not have any idea about the underlying DBMS.
All these components when put together form an efficient database.
24. How do you convert COL1, COL2 and COL3 into ROW 1, ROW2 and ROW3?
For this, we can use UNION or UNION ALL clause to get the rows from columns. We need to select each column in SELECT statement three times to get three rows.
Consider below query from DUAL table, which has three columns.
SELECT * FROM (SELECT 'FIRST COLUMN' AS COL1, 'SECOND COLUMN' AS COL2, 'THIRD COLUMN' AS COL3 FROM DUAL) tbl;
If we need these three columns as three distinct rows, then we need to select each columns separately in a query. In order to combine these three queries into one and get three rows, we need to use UNION clause.
SELECT COL1 AS TBL_ROWS FROM (SELECT 'FIRST COLUMN' AS COL1, 'SECOND COLUMN' AS COL2, 'THIRD COLUMN' AS COL3 FROM DUAL) tbl UNION SELECT COL2 AS TBL_ROWS FROM (SELECT 'FIRST COLUMN' AS COL1, 'SECOND COLUMN' AS COL2, 'THIRD COLUMN' AS COL3 FROM DUAL) tbl UNION SELECT COL3 AS TBL_ROWS FROM (SELECT 'FIRST COLUMN' AS COL1, 'SECOND COLUMN' AS COL2, 'THIRD COLUMN' AS COL3 FROM DUAL) tbl;
This is how we get rows from columns. This will work and feasible if the number of columns to be converted is less.
25.How do you convert ROW 1, ROW2 and ROW3 into COL1, COL2 and COL3?
This can be done by using PIVOT function. Suppose we have three rows with value ‘First Row’, ‘Second Row’ and ‘Third Row’. In order to convert it into columns, we can use pivot like below
SELECT * FROM (SELECT ‘First Row’ AS Rows FROM DUAL UNION SELECT ‘Second Row’ AS Rows FROM DUAL UNION SELECT ‘Third Row’ AS Rows FROM DUAL) AS tbl PIVOT (ROWS) AS COLS FOR (ROWS);