Question:- Explain Inner Join with an example.
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.
Question:- State the differences between views and tables.
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
Question:- What do you understand about a temporary table? Write a query to create a temporary table
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
Question:- Explain the difference between OLTP and OLAP.
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.
Question:- Explain OLAP.
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.
Question:- What is Hybrid OLAP?
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.
Question:- What is the difference between Union and Union All operators?
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.
Question:- What is a cursor? How to use a cursor?
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.
Question:- What is the use of the INTERSECT operator?
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.
Question:- What is the difference between BETWEEN and IN operators in SQL?
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.
Question:- What is the difference between HAVING and WHERE clauses?
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.
Question:- Explain database white box testing and black box testing.
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
Question:- How can you create empty tables with the same structure as another table?
Answer:- This can be achieved by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. In this way, SQL prepares the new table with a duplicate structure to accept the fetched records. However, there are no records that will get fetched due to the WHERE clause in action. Therefore, nothing is inserted into the new table, thus creating an empty table. SELECT * INTO Students_copy FROM Students WHERE 1 = 2;
Question:- What is Oracle?
Answer:- Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multi-user environment so that the users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. A standby database is a database replica created by taking a backup of a primary database.