Top 50 Sql Database Interview Questions You Must Prepare 19.Mar.2024

The Schema refers to the overall structure of the database with all related information like users, access privileges, interaction information between the different tables etc. The CREATE SCHEMA is the command that can be used to create the schema in the SQL Server. This when done can be used to re deploy the same database in another system for demonstrative or test purposes. This holds intact the underlying framework over which the database has been built.

Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

  • Mask the real name and owner of an object.
  • Provide public access to an object
  • Provide location transparency for tables, views or program units of a remote database.
  • Simplify the SQL statements for database users.

Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.

Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc.,) and boilerplate objects (boxes, lines, images etc.,) that operators interact with us they run your form . Each canvas views displayed in a window.

Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.

When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.

  • Text Parameters
  • Data Parameters

COMMIT makes permanent changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.

Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence.

Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;

The active set of the cursor is stored can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for select and function cursors but not insert or update statements.

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

  1. If there is significant change in the key values in the index
  2. If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
  3. Database is upgraded from a previous version

Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats.

The RAID stands for Redundant Array of Independent Disks. With its own RAID controllers, the RAID implements a fail-safe storage mechanism with its own backup mechanisms. There are different configurations of the RAID that all give us the ACID properties of storage along with other such facilities. This kind of storage will make the SQL Server database to be failsafe and stable. This can sometimes mean that the backup mechanisms and other such reliability measures can be taken off from the SQL Server level of operations.

  • Shut the database
  • Backup the database
  • Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file.
  • Start up the database.

A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

Tempfiles, unlike normal datafiles, are not listed in v$datafile or dba_data_files. Instead query v$tempfile or dba_temp_files:

  SELECT * FROM v$tempfile;  SELECT * FROM dba_temp_files;

Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

Only one window in a form can display the console, and you cannot change the console assignment at runtime.

When-image-activated fires, when the operators double clicks on an image item, when-image-pressed fires, when an operator clicks or double clicks on an image item.

Because, in a where clause the condition is applied during data retrieval then after retrieving the data.

A Read-Only transaction ensures that the results of each query executed in the transaction are consistent with respect to the same point in time.

By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.

Lexical reference is place_holder for text that can be embedded in a sql statements. A lexical reference can be created using & before the column or parameter name.

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

An integrity constraint is a declarative way to define a business rule for a column of a table.

Using open_form built-in setting the session option Ex. Open_form('Stocks ',active,session). when invoke the mulitiple forms with open form and call_form in the same application, state whether the following are true/False

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.

The term file which key is correspond to which oracle report functions.

COMPLETE - Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.
FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.
FORCE - Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library, you can attach it to any other form, menu, or library modules. When you can call library program units from triggers menu items commands and user named routine, you write in the modules to which you have attach the library. When a library attaches another library, program units in the first library can reference program units in the attached library. Library support dynamic loading-that is library program units are loaded into an application only when needed. This can significantly reduce the run-time memory requirements of applications.

Statistics represent the uniqueness for the indexes that are being used for selecting the records. This can make the query execution pretty efficient. The tables that we are dealing with if truncated and repopulated, there is a good chance that the indexes and statistics are out of sync and this is when we have to update the statistics. There are also other situations like when the table has been modified and lot of rows have been added recently or like when a server has been updated with different version of software. These also give us the reason to use the UPDATE_STATISTICS, DBCC SHOW_STATISTICS etc to update it accordingly.

The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line

Time the references to occur when master tables are not in use. Peform the reference manually immediately locking the master tables. We can join tables in snapshots by creating a complex snapshots that will be based on the master tables.

A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

Stacked canvas view is displayed in a window on top of, or "stacked" on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options.
SQL*Loader supports various load formats, selective loading, and multi-table loads.

Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name.

Issues dynamic Sql statements at run time, including server side pl/SQl and DDL

Oracle Precompiler exits, Oracle call interface, NonOracle user exits.

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
CREATE INDEX myIndex ON myTable(myColumn)