9 medium level interview Questions on Stored Procedures.

1. Explain aggregate function?

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. They basically summarize the results of a particular column of selected data. We are covering these here since they are required by the next topic, "GROUP BY". Although they are required for the "GROUP BY" clause, these functions can be used without the "GROUP BY" clause. For example:

SELECT AVG(salary)
FROM employee;

This statement will return a single result which contains the average value of everything returned in the salary column from the employee table. Another example:

SELECT AVG(salary)
FROM employee

WHERE title = 'Programmer';

This statement will return the average salary for all employee whose title is equal to 'Programmer' Example:

SELECT Count(*)

FROM employee;

This particular statement is slightly different from the other aggregate functions since there isn't a column supplied to the count function. This statement will return the number of rows in the employees table. Source: https://docs.oracle.com

2. Difference between UNION and UNION ALL?

Though both UNION and UNION ALL is used to combine results of two SELECT queries, the main difference between them is that UNION doesn't include duplicate record but UNION ALL does. Another difference between them is that UNION ALL is faster than UNION but may look slow because it returns more data which takes more time to travel via the network. The difference between UNION and UNION ALL is one of the tricky SQL questions, especially for developers, who has not used this useful keyword ever. Since UNION clause is not as common as a SELECT clause in SQL, it's usually asked in a telephonic round of programming interviews to check whether the candidate is comfortable with SQL or not. It's in the same league of questions like clustered vs non-clustered index or primary vs unique key. UNION is very different than other SQL commands because it operates on data rather than columns.

Anyway, answer of this question is simple, though both UNION and UNION ALL are used to combine result of two separate SQL queries on same or different table, UNION does not keep duplicate record (a row is considered duplicate if value of all columns are same), while UNION ALL does.

Source: http://www.java67.com/

3. Explain Declaration Generator (dclgen)?

The declarations generator (DCLGEN) produces an SQL DECLARE TABLE statement and a COBOL, PL/I, or C data declaration for a table or a view named in the catalog.

Environment

Authorization

• Ownership of the table or view
• SELECT privilege on the table or view
• DBADM authority on the database containing the table
• SYSADM authority
• SYSCTRL authority

4. What does SPUFI stands for?

SPUFI stands for SQL processing using file input. It is the DB2 interactive menu-driven tool used by developers to create database objects.

5. Define a storage group (STOGROUP)?

A storage group is a named set of storage paths where data can be stored. Storage groups are configured to represent different classes of storage available to your database system. You can assign table spaces to the storage group that best suits the data. Only automatic storage table spaces use storage groups.

A table space can be associated with only one storage group, but a storage group can have multiple table space associations. To manage storage group objects you can use the CREATE STOGROUP, ALTER STOGROUP, RENAME STOGROUP, DROP and COMMENT statements.

With the table partitioning feature, you can place table data in multiple table spaces. Using this feature, storage groups can store a subset of table data on fast storage while the remainder of the data is on one or more layers of slower storage. Use storage groups to support multi-temperature storage which prioritizes data based on classes of storage. For example, you can create storage groups that map to the different tiers of storage in your database system. Then the defined table spaces are associated with these storage groups.

• Frequency of access
• Acceptable access time
• Volatility of the data
• Application requirements

Souce: https://www.ibm.com

6. Explain SQLCA?

An SQLCA is a collection of variables that is updated at the end of the execution of every SQL statement.

A program that contains executable SQL statements and is precompiled with option LANGLEVEL SAA1 (the default) or MIA must provide exactly one SQLCA, though more than one SQLCA is possible by having one SQLCA per thread in a multi-threaded application.

When a program is precompiled with option LANGLEVEL SQL92E, an SQLCODE or SQLSTATE variable may be declared in the SQL declare section or an SQLCODE variable can be declared somewhere in the program.

An SQLCA should not be provided when using LANGLEVEL SQL92E. The SQL INCLUDE statement can be used to provide the declaration of the SQLCA in all languages but REXX. The SQLCA is automatically provided in REXX.

To display the SQLCA after each command executed through the command line processor, issue the command db2 -a. The SQLCA is then provided as part of the output for subsequent commands.

Source: ttps://www.ibm.com

7. When will you choose to run RUNSTATS? Explain

Updates statistics in the system catalog about the characteristics of a table, associated indexes, or statistical views. These characteristics include number of records, number of pages, and average record length. The optimizer uses these statistics to determine access paths to the data.

For a table, call the RUNSTATS command when the table had many updates, or after the table is reorganized. For a statistical view, call the RUNSTATS command when changes to underlying tables substantially affected the rows that are returned by the view. The view must be previously enabled for use in query optimization by using the ALTER VIEW statement.

Scope

You can issue the RUNSTATS command from any database partition in the db2nodes.cfg file. You can use the command to update the catalogs on the catalog database partition.

For tables, this command collects statistics for a table on the database partition from which it is invoked. If the table does not exist on that database partition, the first database partition in the database partition group is selected.

For views, this command collects statistics by using data from tables on all participating database partitions.

Authorization

For a table, you require one of the following authorities:

• SYSADM
• SYSCTRL
• SYSMAINT
• DBADM
• SQLADM
• CONTROL privilege on the table
• LOAD authority

You do not need any explicit privilege to use this command on any declared temporary table that exists within its connection. For statistical views, one of the following authorities:

• SYSADM
• SYSCTRL
• SYSMAINT
• DBADM
• SQLADM
• CONTROL privilege on the statistical view
• LOAD authority

Required connection

Database

Source: https://www.ibm.com

8. Why ORACLE database is the best?

When it comes to database management software, Oracle database is the top pick for most of the organizations. It has some advanced features and capabilities which makes it popular in DBMS world. Oracle was designed towards business and corporations. Within the software market, it has the leading role as the perfect database solution. In other words, Oracle database has some unique features that made it the best DBMS solution. Let’s see, why Oracle is the best among all other database management systems.

• Functionality
• Portability
• Quick recovery
• Speed and Performance
• Multiple Database Support
• Reliability

Source: Further Source

9. Difference between oracle and mysql database?

Following are major differences of MySQL and Oracle Databases.

Price Factor Comparison Oracle v/s MySQL:

In price lies the most notable difference between the two databases. Oracle has something called Oracle Express Edition, and this edition is completely free of cost. Unfortunately, the edition has too much limitation with the features. It will cost you tens of thousands of dollars, if you go on to buy full featured standard edition or Enterprise edition. MySQL on the other hand is an open source database, and is completely free. The most famous social networking site called Facebook runs on MySQL.

Purpose compare MySQL v/s Oracle:

Oracle is targeted towards large organizations with the high cost it comes with. Saying so doesn't mean the MySQL cannot work for large organizations (as mentioned that largest social networking site Facebook runs on MySQL), but still this free, open source database lacks on add-on possibilities and robust features of Oracle.

Database Security Compare Oracle and MySQL:

While MySQL uses three parameters to authenticate a user namely user name, password and Location, Oracle uses so many security features like username, password, profiles, local authentication, external authentication, advance security enhancements etc.

SQL Syntax Compare MySQL v/s Oracle:

Oracle SQL syntax differs somehow from MySQL considerably. Oracle provides more flexibility for programming language called PL/SQL. SQL*Plus tool of Oracle offers more command than MySQL for generating report output and variable define.

Storage Compare Oracle and MySQL:

Compare to Oracle, MySQL doesn't have Tablespace, Role management, snapshots, synonym and packages and automatic storage management.

Object Names Comparison Oracle v/s MySQL:

Though some of the schema objects names are case-insensitive both in Oracle and MySQL, for example column, stored procedure, indexes etc, but in some cases case-sensitivity differs between the two databases; Oracle is case-insensitive to all object names, however, some of the MySQL object names like databases and tables, are case-sensitive (depending on the underlying Operating System), unlike Oracle.

Running Programs & External program support:

Oracle database supports several programming languages to be written, compiled and executed from inside the database. Also, for transferring data, Oracle databases use XML. MySQL, on the other hand, does not support other languages being executed from within the system, and neither does it supports XML.

Character Data Types Comparison MySQL and Oracle:

There are some differences with the character types supported in the two databases. MySQL has CHAR and VARCHAR for character types with maximum length allowed to be 65,535 Bytes (CHAR can be maximum 255 Bytes and VARCHAR 65.535 Bytes). Oracle, on the other hand, supports fours character types namely CHAR, NCHAR, VARCHAR2, and NVARCHAR2; all of the four character types need to minimum of 1 byte long; CHAR and NCHAR can be 2000 bytes maximum and the maximum limit for NVARCHAR2 and VARCHAR2 is 4000 bytes. May be in latest version it will be expanding.

Extra Features Compare MySQL v/s Oracle:

MySQL database does not support any feature like Audit Vault on its server. Oracle, on the other hand, supports several extensions and programs on its database server for instance, Active Data Guard, Audit vault, Partitioning, and Data Mining etc.

Temporary Tables Comparison Oracle v/s MySQL:

Oracle and MySQL handles temporary tables differently. In MySQL, temporary tables are visible only to the current user session, and as soon as the session ends, these tables are dropped automatically. In Oracle however, these tables should be dropped explicitly and are visible to all sessions; it is only the data inside which would be visibly only to the current user session.

Source: https://www.dbametrix.com 

Thanks for reading and good luck on your interview! Check Stored Procedures. more Interview Questions & Answers on https://www.onlineinterviewquestions.com

Sponsor Ads

• Ad Space Available - Rent it Now!

Report this Page

Created on Nov 1st 2018 06:15. Viewed 611 times.

Share on APSense

Comments