Answer:- A magic table can be defined as a provisional logical table that is developed by an SQL Server for tasks such as insert, delete, or update (DML) operations. The operations recently performed on the rows are automatically stored in magic tables. Magic tables are not physical tables; they are just temporary internal tables.

Answer:- There are many SELECT statement clauses in SQL. Some of the most commonly used clauses with SELECT queries are: • FROM The FROM clause defines the tables and views from which data can be interpreted. The tables and views listed must exist at the time the question is given. • WHERE The WHERE clause defines the parameters that are used to limit the contents of the results table. You can test for basic relationships or for relationships between a column and a series of columns using subselects. • GROUP BY The GROUP BY clause is commonly used for aggregate functions to produce a single outcome row for each set of unique values in a set of columns or expressions. • ORDER BY The ORDER BY clause helps in choosing the columns on which the table’s result should be sorted. • HAVING The HAVING clause filters the results of the GROUP BY clause by using an aggregate function.

Answer:- SELECT gender, AVG(age) FROM employee WHERE AVG(age)>30 GROUP BY gender When this command is executed, it gives the following error: Msg 147, Level 16, State 1, Line 1 Aggregation may not appear in the WHERE clause unless it is in a subquery contained in the HAVING clause or a select list; the column being aggregated is an outer reference. Msg 147, Level 16, State 1, Line 1 Invalid column name ‘gender’. This basically means that whenever we are working with aggregate functions and are using the GROUP BY clause, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we should use the HAVING clause. When we are using the HAVING clause, the GROUP BY clause should come first, followed by the HAVING clause. select e_gender, avg(e_age) from employee group by e_gender having avg(e_age)>30

Answer:- he stuff() function deletes a part of the string and then inserts another part into the string, starting at a specified position. Syntax: STUFF(String1, Position, Length, String2) Here, String1 is the one that will be overwritten. Position indicates the starting location for overwriting the string. Length is the length of the substitute string, and String2 is the string that will overwrite String1. Example: select stuff(‘SQL Tutorial’,1,3,’Python’) This will change ‘SQL Tutorial’ to ‘Python Tutorial’ Output: Python Tutorial

Answer:- Views are virtual tables used to limit the tables that we want to display. Views are nothing but the result of an SQL statement that has a name associated with it. Since views are not physically present, they take less space to store. Let us consider an example. In the following employee table, say we want to perform multiple operations on the records with gender “Female”. We can create a view-only table for the female employees from the entire employee table. Now, let us implement it on SQL Server. This is the employee table: select * from employee Now, we will write the syntax for the view. Syntax: create view female_employee as select * from employee where e_gender=’Female’ select * from female_employee

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.