Sql oracle interview questions and answers pdf

Oracle applies the aggregate functions to each group of rows and returns a single result row for each group. Aggregating Data Using Group Functions. Answer: A GROUP BY clause can be used in the select statements where it will collect data across multiple records and group the results by one or more columns.

What is the difference between rename and alias? Answer: Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but a replacement of a name and Alias is an alternate name of the table or column.

If all values in the list are null, then the coalesce function will return NULL. How can you fetch the first 5 characters of the column in the table? Optimizer is the engine in the Oracle database which determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. The output from the optimizer is a plan that describes an optimum method of execution.

In general, use the cost-based approach. What are hints in Oracle? Answer Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the specific criteria. The row source tree is the core of the execution plan. Which two statements about views are true? Choose two. A view can be created as read-only. A view can be created as a join on two or more tables. Answer: A, BA view can be created as a read-only object.

However, it is possible to change data in the underlying table s with some restrictions. A view also can be created as a join on two or more tables. This type of view is called a complex view. Complex views provide complicated data models where many base tables are drawn together into one virtual table.

Which data dictionary table should you query to view the object privileges granted to the user on specific columns? For which two constraints does the Oracle Server implicitly create a unique index? Indexes are created automatically by Oracle to support integrity constraints that enforce uniqueness.

Which two are true about aggregate functions? You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. You can pass column names, expressions, constants, or functions as parameters to an aggregate function. You can use aggregate functions on a table, only by grouping the whole table as one single group.

You cannot group the rows of a table by more than one column while using aggregate functions. Answer: C, D. It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

Also, it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function. Both tables have NULL values. You want all unmatched data from one table. You want all matched data from both tables. You want all unmatched data from both tables. One of the tables has more data than the other.

You want all matched and unmatched data from only one table. Answer: D. What does this allow you to do? SQL is a declarative and data-oriented language. It is mainly used for the manipulation of data. It is used for creating an application.

It provides interaction with the database server. It does not provide interaction with the database server. To check the reliability of the transactions, ACID properties are used. Group functions operate on a series of rows and return a single result for each group. The trigger can be defined as an automatic process that happens when an event occurs in the database server.

It helps to maintain the integrity of the table. The trigger is activated when the commands like insert, update, and delete are given. Normalization is used in reducing data redundancy and dependency by organizing fields and tables in databases. It involves constructing tables and setting up relationships between those tables according to certain rules. The redundancy and inconsistent dependency can be removed using these rules to make it more flexible.

Denormalization is contrary to normalization. In this, we basically add redundant data to speed up complex queries involving multiple tables to join. Here, we attempt to optimize the read performance of a database by adding redundant data or by grouping the data.

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. The WHERE clause defines the parameters that would be 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. This 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.

A function is an SQL Server database object. It is basically a set of SQL statements that allow input parameters, perform processing, and return results only. The function can only return a single value or table.

The ability to insert, update, and delete records in database tables is not available. The stuff function deletes a part of the string and then inserts another part into the string starting at a specified position. Here, String1 is the one that would 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 would overwrite String1.

Views are virtual tables used to limit the tables that we want to display, and these are nothing but the result of a SQL statement that has a name associated with it. Since views are not physically present, they take less space to store. We can create a view-only table for the female employees from the entire employee table.

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. This also supports faster execution when compared to executing multiple queries.

The Join clause is used to combine rows from two or more tables based on a related column between them. There are various types of Joins that can be used to retrieve data, and it depends upon the relationship between tables. In SQL Server, usernames and passwords are stored in the main database in the sysxlogins table.

Relationships are developed by interlinking the column of one table with the column of another table. There are three different types of relationships which are as follows:. Let us suppose, 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. Output :. After Inner Join, we have only those records where the departments match in both tables. As we can see, the matched departments are Support, Analytics, and Sales.

Views Tables It is a virtual table that is extracted from a database. A table is structured with a set number of columns and a boundless number of rows. Views do not hold data themselves. A table contains data and stores the data in databases. A view is also utilized to query certain information contained in a few distinct tables. A table holds fundamental client information and the cases of a characterized object.

In a view, we will get frequently queried information. In a table, changing the information in the database changes the information that appears in the view. A temporary table helps us store and process intermediate results. These temporary tables are created and can be automatically deleted when they are no longer used. They are very useful in places where we need to store temporary data. OLTP: It stands for Online Transaction Processing, and we can consider it to be a category of software applications that is 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.

Also, queries involved in such databases are generally simple, need fast response time, and in comparison, return only a few records. So, the number of transactions per second acts as an effective measure for those systems.

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 multi-dimensional schemas. Self Join in SQL is used for joining a table with itself. How packaged procedures and functions are called from the following? Stored procedure or anonymous block b. A function can not be called. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package. A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures. What are two parts of package? Package Specification contains declarations that are global to the packages and local to the schema.

Package Body contains actual procedures and local declaration of the procedures and cursor declarations. What is Overloading of procedures?