Basic SQL Interview Questions

Table of Contents

16. What is a Materialized View?

As we have seen above, views are executed when we fire query on it. So, it does not speed up the execution of the query that is having views. But it simplifies the query. But when we write a query it is always expected it to run quickly. So what we can do is, run the frequently used query and store it under some name! This is similar to view but here we are executing the query and storing the results of query under name. This type of view is called materialized view. Using materialized view in complex query reduces the execution time for running repeated queries. It involves the time for running ‘SELECT * FROM  …’. Unlike views, in this type of views, copies of table records are created and stored. Materialized views are not virtual tables. Hence there will be a storage space allocated to it.

CREATE MATERIALIZED VIEW mv_Design_Emp AS
 SELECT e.EMP_ID, e.EMP_FIRST_NAME, e.EMP_LAST_NAME, d.DEPT_ID, d.DEPT_NAME
 FROM EMPLOYEE e, DEPARTMENT d – two tables are used to create a view
 WHERE e.DEPT_ID = d.DEPT_ID -- Join condition
  AND d.DEPT_NAME = ‘DESIGN’; -- Filtering condition

Here unlike views, materialized views mv_Design_Emp and mv_Test_Emp are two separate copies of tables and stored in the different data block of memory.

We can have SELECT query on materialized views as we do with any other tables. Since this view is created and stored in advance, it might not have latest records from the table. That means if any records are inserted into the table after a materialized view is created and that inserted record is a valid candidate to be in materialized view, then it will not be shown in the materialized view. Materialized view needs to be refreshed to get the latest record.

17. What is Normalization? Why it is used and which stage of the project development?

Normalization is a set of rules/guidelines/technique that is used while designing a database.  These rules help to remove all the anomalies and distribute the data among different related tables and query them efficiently and effectively. It removes all the duplication issues (data redundancy) and incorrect data issues (data inconsistency), helping to have a well-designed database.
Any tables in the database should be normalized while designing them itself. It is the stage in project development where requirements from its clients are gathered and analyzed to have tables. But while designing tables, normalization is applied to get saturated tables. This will avoid data inconsistency and data redundancy.

18. What is ER diagram? Why it is used? Which stage of the project development?

An ER diagram is an Entity-Relationship diagram designed to understand the requirement clearly to the user / developer. It is very much useful to the developers, as it denotes how many entities are present, what are its relationships, cardinality or selectivity etc. it is designed soon after the requirement gathering.
ER Data Model is based on the real world objects and their relationship. In other words, each and everything, either living or non-living things in this world forms the object in database world. We identify all the required objects for our database requirement and give the shape of database objects. Before putting them into database, it is very much essential to understand the requirement properly and design them efficiently. It is like a foundation of the building.  For this purpose, we use ER diagrams where we plan the database pictorially. ER diagram basically breaks requirement into entities, attributes and relationship. Let us see them in detail.

19. What is the difference between Procedure and Package?

A procedure is a named PL/SQL block which executes one or more related task.
Package is an object which groups procedures, functions, variables, constants, exception etc. under one particular name. It has 2 parts – specification and body. The specification part has all the declarations about the objects inside it. Body part has detailed tasks about the objects.

20. What is a Package? Where a global variable is declared in a package?

We have set of different procedures, functions, variables and other database objects which are performing various tasks on STUDENT table. All the database objects are named properly, so that we can identify them easily. But all are scattered in the database. In order to group all of them under one single name, DBMS provides a feature called package. It can be considered as a Folder, which contains different types of file – .txt, .doc, .xls, .pdf etc.

Package is an object which groups procedures, functions, variables, constants, exception etc. under one particular name. It has 2 parts – specification and body. The specification part has all the declarations about the objects inside it. Body part has detailed tasks about the objects.

Below is one simple example of a package.

-- Package Specification
CREATE PACKAGE package_name AS
   FUNCTION function_name (p_arg IN NUMBER) RETURN NUMBER; -- Declares the function in the package
   PROCEDURE procedure_name (p_parameter IN NUMBER) RETURN VARCHAR2; -- Declares the procedure in the package
END package_name;
/

-- Package Body
CREATE PACKAGE BODY package_name AS
 -- Detailed set of transactions inside the package function
   FUNCTION function_name (p_arg IN NUMBER) RETURN NUMBER IS
   BEGIN
      RETURN p_arg*10; -- Returns the input parameter multiplied by 10
   END function_name;

-- Detailed set of transactions inside the package procedure
   PROCEDURE procedure_name (p_parameter IN NUMBER) IS
	ld_date DATE;
   BEGIN
	-- Calculate current date and displays 
       	SELECT SYSDATE into ld_date FROM DUAL; 
	dbms_output.put_line ('Today’s Date is: ’|| ld_date);
   END procedure_name;
END package_name; -- End of the package body
/

Creating the package specification alone in the database creates a package in the database. But we will not be able to call the objects inside the package. We can call the procedures and functions inside the package similar to the way we call simple procedure or function. But we need to append package name to the procedure or function.

— Below are the methods to call package function

  • lv_emp_name:= package_name.function_name (103);
  • SELECT package_name.function_name (100) FROM DUAL;
  • dbms_output.put_line (package_name.function_name (100));

— Below are the methods to call package procedure

  • EXECUTE [or EXEC] package_name.procedure_name;
  • package_name.procedure_name;

In order to remove the package from the database, we can fire below query.

DROP package package_name; -- removes both specification and body from DB
DROP package BODY package_name; -- removes only package body from DB

A global variable in a package is declared in a package specification. The variables declared in package specifications are available for all the procedures, functions and blocks in package body. We need not re-declare them in package body.

Pages: 1 2 3 4 5 6 7

Translate »