SQL interview Questions

What do you mean by Fill Factor concept with respect to indexes?

Fill Factor can be defined as being that value which defines the percentage of left space on every leaf-level page that is to be packed with data. 100 is the default value of Fill Factor.

Define Denormalization.

Boosting up database performance, adding of redundant data which in turn helps rid of complex data is called denormalization.

ompare Non-clustered and clustered index

Both having B-tree structure, non-clustered index has data pointers enabling one table many non-clustered indexes while clustered index is distinct for every table.

Say True or False. Give explanation if False. A DDL statement or a DCL statement is automatically committed.

True.

Say True or False. Give explanation if False. A multiple row subquery returns more than one row from the inner SELECT statement.

True.

Say True or False. Give explanation if False. All group functions ignore null values.

True.

Say True or False. Give explanation if False. Attempting to delete a record with a value attached to an integrity constraint, returns an error.

True.

Say True or False. Give explanation if False. If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.

True.

Say True or False. Give explanation if False. Multiple column subqueries return more than one column from the inner SELECT statement.

True.

Say True or False. Give explanation if False. The DISTINCT keyword allows a function consider only non-duplicate values.

True.

Say True or False. Give explanation if False. While inserting new rows in a table you must list values in the default order of the columns.

True.

Say True or False. Give explanation if False. You can use a subquery in an INSERT statement.

True.

What is the difference between VARCHAR2 AND CHAR datatypes?

VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.

Say True or False. Give explanation if False. The DELETE statement is used to delete a table from the database.

False. The DELETE statement is used for removing existing rows from a table.

Define "correlated subqueries".

A 'correlated subquery' is a sort of sub query but correlated subquery is reliant on another query for a value that is returned. In case of execution, the sub query is executed first and then the correlated query.

Is a NULL value same as zero or a blank space? If not then what is the difference?

A NULL value is not same as zero or a blank space. A NULL value is a value which is 'unavailable, unassigned, unknown or not applicable'. Whereas, zero is a number and blank space is a character.

Define database model

A data model determining fundamentally how data can be stored, manipulated and organised and the structure of the database logically is called database model.

Define B-trees.

A data structure in the form of tree which stores sorted data and searches, insertions, sequential access and deletions are allowed in logarithmic time.

Define Database

A prearranged collection of figures known as data is called database.

Define sub-query.

A query contained by a query is called Sub-query.

What do you mean by Entity type?

A set of entries having similar attributes are entity types.

Define Atomicity and Aggregation.

Atomicity: It's an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS. Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.

What is a view? Why should you use a view?

A view is a logical snapshot based on a table or another view. It is used for − Restricting access to data; Making complex queries simple; Ensuring data independency; Providing different views of same data.

Enlist the advantages of normalizing database.

Advantages of normalizing database are: No duplicate entries Saves storage space Boasts the query performances.

What happens if you omit the WHERE clause in a delete statement?

All the rows in the table are deleted.

Define Entity Set.

Compilation of all entries of any particular type of entry in the database is called Entity Set

Define Object-oriented model.

Compilations of objects make up this model in which values are stored within instance variables which is inside the object. The object itself comprises bodies of object for its operation which are called methods. Objects containing same kind of variables and methods are called classes.

Define DDL Interpreter.

DDL statements are interpreted and recorded in tables called metadata.

Who proposed the relational model?

Edgar F. Codd proposed the relational model in 1970.

What are various DCL commands in SQL? Give brief description of their purposes.

Following are various DCL or Data Control Language commands in SQL − GRANT − it gives a privilege to user. REVOKE − it takes back privileges granted from user.

What are various DDL commands in SQL? Give brief description of their purposes.

Following are various DDL or Data Definition Language commands in SQL − CREATE − it creates a new table, a view of a table, or other object in database. ALTER − it modifies an existing database object, such as a table. DROP − it deletes an entire table, a view of a table or other object in the database.

What are various DML commands in SQL? Give brief description of their purposes.

Following are various DML or Data Manipulation Language commands in SQL − SELECT − it retrieves certain records from one or more tables. INSERT − it creates a record. UPDATE − it modifies records. DELETE − it deletes records.

Define Fragmentation.

Fragmentation can be defined as a database feature of server that promotes control on data which is stored at table level by the user.

What do you mean by Index hunting?

Indexes help in improving the speed as well as the query performance of database. The procedure of boosting the collection of indexes is named as Index hunting.

Define Entity.

It can be defined as being a 'thing' with an independent existence in the real world.

What's wrong in the following query? SELECT subject_code, count(name) FROM students;

It doesn't have a GROUP BY clause. The subject_code should be in the GROUP BY clause. SELECT subject_code, count(name) FROM students GROUP BY subject_code;

Differentiate Table Scan from Index Scan.

Iterating over all the table rows is called Table Scan while iterating over all the index items is defined as Index Scan.

How do you insert null values in a column while inserting data?

Null values can be inserted into a table by one of the following ways − Implicitly by omitting the column from the column list. Explicitly by specifying the NULL keyword in the VALUES clause.

What are the specific uses of SQL functions?

SQL functions have the following uses − Performing calculations on data Modifying individual data items Manipulating the output Formatting dates and numbers Converting data types

Define Scalar functions.

Scalar function is depended on the argument given and returns sole value.

Explain the importance of partitioning.

Splitting of one table which is large into smaller database entities logically is called database partitioning. Its benefits are: To improve query performance in situations dramatically when mostly rows which are heavily accessed are in one partition. Accessing large parts of a single partition Slower and cheaper storage media can be used for data which is seldom used.

What is SQL?

Structured Query Language (SQL) being ANSI standard language updates database and commands for accessing.

What are the various kinds of interactions catered by DBMS?

The various kind of interactions catered by DBMS are: Data definition Update Retrieval Administration

Enlist the various transaction phases.

The various transaction phases are: Analysis Phase. Redo Phase Undo Phase

Enlist ways to efficiently code transactions.

Ways to efficiently code transactions: User input should not be allowed while transactions. While browsing, transactions must not be opened of data. Transactions must be kept as small as possible. Lower transaction segregation levels. Least information of data must be accessed while transacting.

Can you remove rows from a table based on values from another table? Explain.

Yes, subqueries can be used to remove rows from a table based on values from another table.

Can you sort a column using a column alias?

Yes. A column alias could be used in the ORDER BY clause.

Leaf level pages

leaf level pages are the end of the road for the data search through the B-tree structure of an index. They contain the data defined in the index definition (or in the case of a clustered index, all of the table's data) and a row locator to the data row (in the case of a nonclustered index).

What are the features of Database language?

A database language may also incorporate features like: DBMS-specific Configuration and management of storage engine Computations to modification of query results by computations, like summing, counting, averaging, grouping, sorting and cross-referencing Constraint enforcement Application Programming Interface

Define cursor.

A database object which helps in manipulating data row by row representing a result set is called cursor.

If a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?

A query result displays all rows including the duplicate rows. To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause.

What do you understand by a subquery? When is it used?

A subquery is a SELECT statement embedded in a clause of another SELECT statement. It is used when the inner query, or the subquery returns a value that is used by the outer query. It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table.

What do database languages do?

As special-purpose languages, they have: Data definition language Data manipulation language Query language

What happens if you omit the WHERE clause in the UPDATE statement?

All the rows in the table are modified.

Say True or False. Give explanation if False. By default the group functions consider only distinct values in the set.

By default, group functions consider all values including the duplicate values.

Define Database system.

DBMS along with database is called Database system.

What is DBMS?

Database Management Systems (DBMS) are applications designed especially which enable user interaction with other applications.

What is Database partitioning?

Division of logical database into independent complete units for improving its management, availability and performance is called Database partitioning.

What is Executive Plan?

Executive plan can be defined as: SQL Server caches collected procedure or the plan of query execution and used thereafter by subsequent calls. An important feature in relation to performance enhancement. Data execution plan can be viewed textually or graphically.

Say True or False. Give explanation if False. A DROP TABLE statement can be rolled back.

False. A DROP TABLE statement cannot be rolled back.

Say True or False. Give explanation if False. A single row subquery returns only one row from the outer SELECT statement

False. A single row subquery returns only one row from the inner SELECT statement.

Say True or False. Give explanation if False. COUNT(*) returns the number of columns in a table.

False. COUNT(*) returns the number of rows in a table.

Say True or False. Give explanation if False. Group functions cannot be nested.

False. Group functions can be nested to a depth of two.

Say True or False. Give explanation if False. INSERT statement does not allow copying rows from one table to another.

False. INSERT statement allows to add rows to a table copying rows from an existing table.

Define Union All operator and Union.

Full recordings of two tables is Union All operator. A distinct recording of two tables is Union.

Define Aggregate functions.

Functions which operate against a collection of values and returning single value is called aggregate functions

What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

Why is group-clause used?

Group-clause uses aggregate values to be derived by collecting similar data.

What's wrong in the following query? SELECT student_code, name FROM students WHERE marks = (SELECT MAX(marks) FROM students GROUP BY subject_code);

Here a single row operator = is used with a multiple row subquery.

B-Tree data structure

In computer science, a B-tree is a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. The B-tree is a generalization of a binary search tree in that a node can have more than two children

What are the various multiple row comparison operators in SQL?

IN, ANY, ALL.

database Views

In database theory, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.

How do you search for a value in a database table when you don't have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called 'wildcard' search.

How does Index hunting help in improving query performance?

Index hunting helps in improving the speed as well as the query performance of database. The followed measures are achieved to do that: The query optimizer is used to coordinate the study of queries with the workload and the best use of queries suggested based on this. Index, query distribution along with their performance is observed to check the effect. Tuning databases to a small collection of problem queries is also recommended.

Define Join and enlist its types.

Joins help in explaining the relation between different tables. They also enable you to select data with relation to data in another table. The various types are: INNER JOINs: Blank rows are left in the middle while more than equal to two tables are joined. OUTER JOINs: Divided into Left Outer Join and Right Outer Join. Blank rows are left at the specified side by joining tables in other side. Other joins are CROSS JOINs, NATURAL JOINs, EQUI JOIN and NON-EQUI JOIN.

What are the case manipulation functions of SQL?

LOWER, UPPER, INITCAP

Define DDL and DML.

Managing properties and attributes of database is called Data Definition Language(DDL). Manipulating data in a database such as inserting, updating, deleting is defined as Data Manipulation Language. (DML)

Differentiate Nested Loop, Hash Join and Merge Join.

Nested loop (loop over loop) An outer loop within an inner loop is formed consisting of fewer entries and then for individual entry, inner loop is individually processed. E.g. Select col1.*, col2.* from coll, col2 where coll.col1=col2.col2; It's processing takes place in this way: For i in (select * from col1) loop For j in (select * from col2 where col2=i.col1) loop Results are displayed; End of the loop; End of the loop; The Steps of nested loop are: Identify outer (driving) table Assign inner (driven) table to outer table. For every row of outer table, access the rows of inner table. Nested Loops is executed from the inner to the outer as: outer_loop inner_loop Hash join While joining large tables, the use of Hash Join is preferred. Algorithm of Hash Join is divided into: Build: It is a hash table having in-memory which is present on the smaller table. Probe: this hash value of the hash table is applicable for each second row element. Sort merge join Two independent sources of data are joined in sort merge join. They performance is better as compared to nested loop when the data volume is big enough but it is not good as hash joins generally. The full operation can be divided into parts of two: Sort join operation : Get first row R1 from input1 Get first row R2 from input2. Merge join operation: 'while' is not present at either loop's end. if R1 joins with R2 next row is got R2 from the input 2 return (R1, R2) else if R1 < style=""> next row is got from R1 from input 1 else next row is got from R2 from input 2 end of the loop

Define Normalization.

Organized data void of inconsistent dependency and redundancy within a database is called normalization

What is the difference between SQL and PL/SQL?

PL/SQL is a dialect of SQL that adds procedural features of programming languages in SQL. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.

What do you mean by Query Evaluation Engine?

Query Evaluation Engine executes the low-level instructions that are generated by the compiler.

What restrictions can you apply when you are creating views?

Restrictions that are applied are: Only the current database can have views. You are not liable to change any computed value in any particular view. Integrity constants decide the functionality of INSERT and DELETE. Full-text index definitions cannot be applied. Temporary views cannot be created. Temporary tables cannot contain views. No association with DEFAULT definitions. Triggers such as INSTEAD OF is associated with views.

What is the difference between SQL and MySQL or SQL Server?

SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.

Define Data Warehousing.

Storage and access of data from the central location in order to take some strategic decision is called Data Warehousing. Enterprise management is used for managing the information whose framework is known as Data Warehousing.

Which SQL statement is used to add, modify or drop columns in a database table?

The ALTER TABLE statement.

What is the purpose of the condition operators BETWEEN and IN?

The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.

Discuss the syntax and use of the COALESCE function?

The COALESCE function has the expression COALESCE(exp1, exp2, .... expn) It returns the first non-null expression given in the parameter list.

What is the purpose of DML statements in SQL?

The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.

Which statement is used to add a new row in a database table?

The INSERT INTO statement.

How do you copy rows from one table to another?

The INSERT statement can be used to add rows to a table by copying from another table. In this case, a subquery is used in the place of the VALUES clause.

What is the purpose of the MERGE statement in SQL?

The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exists, or an INSERT if the row does not exist.

Which function returns the remainder in a division operation?

The MOD function returns the remainder in a division operation.

What is the use of the NULLIF function?

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.

What is the purpose of the NVL function?

The NVL function converts a NULL value to an actual value.

What is the difference between the NVL and the NVL2 functions?

The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1. The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.

What's wrong in the following query? SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;

The WHERE clause cannot be used to restrict groups. The HAVING clause should be used. SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;

What is the difference between cross joins and natural joins?

The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.

What is the default ordering of data using the ORDER BY clause? How could it be changed?

The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.

Segregate database technology's development.

The development of database technology is divided into: Structure or data model Navigational model SQL/ relational model

Enlist the disadvantages of query.

The disadvantages of query are: No indexes Stored procedures are excessively compiled. Triggers and procedures are without SET NOCOUNT ON. Complicated joins making up inadequately written query. Cursors and temporary tables showcase a bad presentation.

You want to display a result query from joining two tables with 20 and 10 rows respectively. Erroneously you forget to write the WHERE clause. What would be the result?

The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.

Enlist the various relationships of database.

The various relationships of database are: One-to-one: Single table having drawn relationship with another table having similar kind of columns. One-to-many: Two tables having primary and foreign key relation. Many-to-many: Junction table having many tables related to many tables.

Which expressions or functions allow you to implement conditional processing in a SQL statement?

There are two ways to implement conditional processing or IF-THEN-ELSE logic in a SQL statement. Using CASE expression Using the DECODE function

Enlist some commands of DDL.

They are: CREATE: Create is used in the CREATE TABLE statement. Syntax is: CREATE TABLE [column name] ( [column definitions] ) [ table parameters] ALTER: It helps in modification of an existing object of database. Its syntax is: ALTER objecttype objectname parameters. DROP: It destroys an existing database, index, table or view. Its syntax is: DROP objecttype objectname.

Enlist the cursor types.

They are: Dynamic: it reflects changes while scrolling. Static: doesn't reflect changes while scrolling and works on recording of snapshot. Keyset: data modification without reflection of new data is seen.

Enlist the types of cursor.

They types of cursor are: Implicit cursor: Declared automatically as soon as the execution of SQL takes place without the awareness of the user. Explicit cursor: Defined by PL/ SQL which handles query in more than one row.

Say True or False. Give explanation if False. A view doesn't have data of its own.

True

Can you modify the rows in a table based on values from another table? Explain.

Yes. Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.