Sql Interview Questions and Answer

A SUPER KEY is a set of one or more attributes that allows us to identify uniquely an entity in the entity set.

The PRIMARY KEY is the column(s) used to uniquely identify each row of a table. A table can have only one primary key.

A null signifies that a value is undefined or not known. This is not the same as a zero, a blank, or a default value. Instead, it indicates that a data value is absent.

A UNIQUE KEY is one or more columns that must be unique for each row of the table

A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.
CANDIDATE KEY is a minimal superkey. A candidate key for a relation schema is a minimal set of attributes whose values uniquely identify tuples in the corresponding relation.

Metadata - are data about data but not the actual data

An entity - is an object with a physical existence.

A view - is a virtual table that is created when the view is invoked (by calling its name). The table doesn't actually exist, only the SQL statement that defines the table.

A set function - is a type of function that processes or calculates data and returns the appropriate values.

Data dictionary - is a repository of information describing the data in the database, that is the metadata. Data dictionary is a file that contains Metadata or data about data.

An entity type - defines a collection of entities that have same attribute Entity Set.

Entity set - is the collection of a particular entity type that are grouped into an Entity Set.

The REVOKE- statement is used to remove database privileges


What are the ways tablespaces can be managed and how do they differ?

Objects can be assigned to a table space. The related objects can be then grouped together. Table space can also be managed using extents. Extents consist of a specific number of contiguous data blocks. For the required extent, the free extent closest in size is determined.

Explain what are Oracle Catalog and Oracle Archive log?

Oracle catalog contains tables and views to get information about the database. It helps user to understand the available tables, attributes, constraints etc.

Oracle Archive log mode of a database in Oracle, ensures, the online redo logs are not overwritten before they are archived. This ensures that recovery is possible.

What are PCT Free and PCT Used? What is PCT increase parameter in segment?

PCTFREE is a parameter used to find how much space should be left in a database block for future updates. This means that if the PCTFREE = 20, new rows will be added in the block until it is 80% full.

PCTUSED is a parameter helps Oracle to find when it should consider a database block to be empty enough to be added to the freelist. This means that if the PCTFREE = 50, new rows will be not be added in the block until sufficient rows are deleted from the block so that it falls below 40% empty.

PCTINCREASE parameter is used to find how much will the each subsequent segment will grow. This value is in %.

What is dump destination? What are bdump, cdump and udump?

Trace files for Oracle processes are stored in dump destination.

Bdump- Oracle writes to the trace log and creates trace files for background processes in background dump destination. If this directory becomes full and more files cannot be written, debugging becomes difficult.

Cdump- Oracle writes core files and background processes in Core dump destination directory. If this directory becomes full and more files cannot be written, debugging becomes difficult.

Udump – Oracle creates trace files for the user processes in the User Dump directory if this directory becomes full and more files cannot be written, debugging becomes difficult.

How do you increase the performance of %LIKE operator?

LIKE% works the fastest because it uses the index to search on the column provided an index is specified on the column. Using % after LIKE, results in faster results.

Why use materialized view instead of a table

Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.

Why and how the deadlock situation arises

A deadlock situation arises when two or more users wait for the same resource locked by one anther or two or more processes wait to update rows which are locked by other processes. Oracle if detects a deadlock, rolls back the session chosen by the deadlock victim.

What are standby databases? Difference between Physical and logical standby databases

A standby database is a replica of the original database. In order to keep both the database synchronized, archived redo logs can be used. It is mainly used in disaster protection. It can also be opened in read only mode which allows it to be used independently for reporting.

A logical standby database allows new database objects like tables, indexes to be added to the database. On the other hand, the physical standby database is a physical or structural copy of primary database. They can be opened in read only for disaster recovery.

 What is Cache Fusion Technology?

In Cache fusion, multiple buffers join to act as one. It eliminates disk i/o operaions by making use of a scalable shared cache. It treats multiple buffer caches as one thereby resolving data consistency issues. Cash fusion technology can provide more resources and increases concurrency of users.

What is the difference between Cloning and Standby databases?

The clone database is a copy of the database which can be opened in read write mode. It is treated as a separate copy of the database that is functionally completely separate. The standby database is a copy of the production database used for disaster protection. In order to update the standby database; archived redo logs from the production database can be used. If the primary database is destroyed or its data becomes corrupted, one can perform a failover to the standby database, in which case the standby database becomes the new primary database.

What is dump destination? What are bdump, cdump and udump?  

Trace files for Oracle processes are stored in dump destination.

Bdump- Oracle writes to the trace log and creates trace files for background processes in background dump destination. If this directory becomes full and more files cannot be written, debugging becomes difficult.

Cdump- Oracle writes core files and background processes in Core dump destination directory. If this directory becomes full and more files cannot be written, debugging becomes difficult.

Udump – Oracle creates trace files for the user processes in the User Dump directory if this directory becomes full and more files cannot be written, debugging becomes difficult.

What are PCT Free and PCT Used? What is PCT increase parameter in segment? 

PCTFREE is a parameter used to find how much space should be left in a database block for future updates. This means that if the PCTFREE = 20, new rows will be added in the block until it is 80% full.

PCTUSED is a parameter helps Oracle to find when it should consider a database block to be empty enough to be added to the freelist. This means that if the PCTFREE = 50, new rows will be not be added in the block until sufficient rows are deleted from the block so that it falls below 40% empty.

PCTINCREASE parameter is used to find how much will the each subsequent segment will grow. This value is in %

 Explain what are Oracle Catalog and Oracle Archive log? 

Oracle catalog contains tables and views to get information about the database. It helps user to understand the available tables, attributes, constraints etc.

Oracle Archive log mode of a database in Oracle, ensures, the online redo logs are not overwritten before they are archived. This ensures that recovery is possible

 Transactional control commands?

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTIONS

Drawbacks of file processing system?

Duplication of data, which leads to wastage of storage space and data inconsistency.

Types of data manipulation languages?

• Procedural DMLs
• Declarative DMLs

Physical and Logical data independence

In logical data independence, the conceptual schema can be changed without changing the external schema. 

In physical data independence, the internal schema can be changed without changing the conceptual schema.



What is the difference between Delete and Truncate command in SQL
Delete command and truncate command both will delete the data, however the truncate command can not be rolled back as delete can be.
The delete command can be used for selected records using the where clause but with the truncate command we have to loose data. DELETE statement is a logged operation and hence takes more time then truncate.

What is the difference between a "where" clause and a "having" clause? - 
"Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.

What is the basic form of a SQL statement to read data out of a table? 

The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called basic form because of WHERE clause.

What is a "join"? - 

‘join’ used to connect two or more tables logically with or without common field.

What structure can you implement for the database to speed up table reads?- 

Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)

What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.