Answer:- In SQL, the views are classified into four types. They are: Simple View: A view that is based on a single table and does not have a GROUP BY clause or other features. Complex View: A view that is built from several tables and includes a GROUP BY clause as well as functions. Inline View: A view that is built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query. Materialized View: A view that saves both the definition and the details. It builds data replicas by physically preserving them.

Answer:- A stored procedure is a prepared SQL code that can be saved and reused. In other words, we can consider a stored procedure to be a function consisting of many SQL statements to access the database system. We can consolidate several SQL statements into a stored procedure and execute them whenever and wherever required. A stored procedure can be used as a means of modular programming, i.e., we can create a stored procedure once, store it, and call it multiple times as required. This also supports faster execution when compared to executing multiple queries. Syntax: CREATE PROCEDURE procedure_name AS Sql_statement GO; To execute we will use this: EXEC procedure_name Example: We are going to create a stored procedure that will help us extract the age of the employees. create procedure employee_age as select e_age from employee go Now, we will execute it. exec employee_age

Answer:- Inner Join basically gives us those records that have matching values in two tables. Let us suppose that we have two tables, Table A and Table B. When we apply Inner Join on these two tables, we will get only those records that are common to both Table A and Table B.

Answer:- --- Views • A view is a virtual table that is extracted from a database • A view does not hold data itself • A view is utilized to query certain information contained in a few distinct tables • In a view, we will get frequently queried information --- Tables • A table is structured with a set number of columns and a boundless number of rows • A table contains data and stores it in databases • A table holds fundamental client information and cases of a characterized object • In a table, changing the information in the database changes the information that appears in the view

Answer:- A temporary table helps us store and process intermediate results. Temporary tables are created and can be automatically deleted when they are no longer used. They are very useful in places where temporary data needs to be stored. Syntax: CREATE TABLE #table_name(); The below query will create a temporary table: create table #book(b_id int, b_cost int) Now, we will insert the records. insert into #book values(1,100) insert into #book values(2,232) select * from #book

Answer:- OLTP: It stands for online transaction processing, and we can consider it to be a category of software applications that are efficient for supporting transaction-oriented programs. One of the important attributes of the OLTP system is its potential to keep up the consistency. The OLTP system often follows decentralized planning to keep away from single points of failure. This system is generally designed for a large audience of end users to perform short transactions. The queries involved in such databases are generally simple, need fast response time, and, in comparison, return in only a few records. So, the number of transactions per second acts as an effective measure for those systems.

Answer:- OLAP: It stands for online analytical processing, and it is a category of software programs that are identified by a comparatively lower frequency of online transactions. For OLAP systems, the efficiency of computing depends highly on the response time. Hence, such systems are generally used for data mining or maintaining aggregated historical data, and they are usually used in multidimensional schemas.

Answer:- Hybrid OLAP (HOLAP) uses a combination of multidimensional data structures and relational database tables to store multidimensional data. The aggregations for a HOLAP partition are stored by analysis services in a multidimensional structure. The facts are stored in a relational database.

Answer:- The Union operator is used to combine the result set of two or more select statements. For example, the first select statement returns the fish shown in Image A, and the second statement returns the fish shown in Image B. The Union operator will then return the result of the two select statements as shown in Image A U B. If there is a record present in both tables, then we will get only one of them in the final result. The Union All operator gives all the records from both tables including the duplicates.

Answer:- A database cursor is a control that allows you to navigate around a table’s rows or documents. It can be referred to as a pointer for a row in a set of rows. Cursors are extremely useful for database traversal operations such as extraction, insertion, and elimination. • After any variable declaration, DECLARE a cursor. A SELECT statement must always be aligned with the cursor declaration. • To initialize the result set, OPEN statements must be called before fetching the rows from the result table. • To grab and switch to the next row in the result set, use the FETCH statement. • To deactivate the cursor, use the CLOSE expression. • Finally, use the DEALLOCATE clause to uninstall the cursor description and clear all the resources associated with it.

Answer:- The INTERSECT operator helps combine two select statements and returns only those records that are common to both the select statements. So, after we get Table A and Table B over here, and if we apply the INTERSECT operator on these two tables, then we will get only those records that are common to the result of the select statements of these two tables.

Answer:- The BETWEEN operator is used to represent rows based on a set of values. The values may be numbers, text, or dates. The BETWEEN operator returns the total number of values that exist between two specified ranges. The IN condition operator is used to search for values within a given range of values. If we have more than one value to choose from, then we use the IN operator.

Answer:- The distinction between HAVING and WHERE clauses in SQL is that while the WHERE clause cannot be used with aggregates, the HAVING clause is used with the aggregated data. The WHERE clause works on the data from a row and not with the aggregated data.

Answer:- The white box testing method mainly deals with the internal structure of a particular database, where users hide specification details. The white box testing method involves the following: • As the coding error can be detected by testing the white box, it can eliminate internal errors. • To check for the consistency of the database, it selects the default table values. • This method verifies the referential integrity rule. • It helps perform the module testing of database functions, triggers, views, and SQL queries. The black box testing method generally involves interface testing, followed by database integration. The black box testing method involves the following: • Mapping details • Verification of incoming data • Verification of outgoing data from the other query functions