DBMS Interview Questions – Set 05

by

What are indexes? What are B-Trees

Index makes your search faster. So defining indexes to your database will make your search faster.Most of the indexing fundamentals use “B-Tree” or “Balanced-Tree” principle. It’s not a principle that is something is created by SQL Server or ORACLE but is a mathematical derived fundamental.In order that “B-tree” fundamental work properly both of the sides should be balanced.

What is a Sub-Query

A query nested inside a SELECT statement is known as a subquery and is an alternative to complex join statements. A subquery combines data from multiple tables and returns results that are inserted into the WHERE condition of the main query. A subquery is always enclosed within parentheses and returns a column. A subquery can also be referred to as an inner query and the main query as an outer query. JOIN gives better performance than a subquery when you have to check for the existence of records.

For example, to retrieve all EmployeeID and CustomerID records from the ORDERS table that have the EmployeeID greater than the average of the EmployeeID field, you can create a nested query, as shown:

SELECT DISTINCT EmployeeID, CustomerID FROM ORDERS WHERE EmployeeID > (SELECT AVG(EmployeeID) FROM ORDERS)

As you can see the numbers indicate the relationship between each node. All left values greater than 6 and right values less than 11 are descendants of 6-11 (i.e Id: 3 Amanda).Now If you want to extract out the 2-6 sub-tree for Amanda. What SQL query you will write

SELECT * FROM employee WHERE left_val BETWEEN 6 and 11 ORDER BY left_val ASC;

How do we select distinct values from a table

DISTINCT keyword is used to return only distinct values.

Below is syntax:- Column age and Table pcdsEmp

SELECT DISTINCT age FROM pcdsEmp

Which will return Amanda, Ralph, and Jeanne. If you want to get ancestors to a given node say 7-8 Ralph. What SQL query you will write

SELECT * FROM employee WHERE left_val < 7 and right_val > 8 WHERE ORDER BY left_val ASC;

We have an employee salary table how do we find the secondhighest from it

Below Sql Query find the second highest salary

SELECT * FROM pcdsEmployeeSalary a WHERE (2=(SELECT COUNT(DISTINCT(b.salary)) FROM pcdsEmployeeSalary b WHERE b.salary>=a.salary))

What are Fact tables and Dimension Tables ? What is Dimensional Modeling and Star Schema Design

When we design transactional database we always think in terms of normalizing design to its least form. But when it comes to designing for Data warehouse we think more in terms of denormalizing the database. Data warehousing databases are designed using Dimensional Modeling. Dimensional Modeling uses the existing relational database structure and builds on that.

There are two basic tables in dimensional modeling:-

  • Fact Tables.
  • Dimension Tables.

Fact tables are central tables in data warehousing. Fact tables have the actual aggregate values which will be needed in a business process. While dimension tables revolve around fact tables. They describe the attributes of the fact tables

What’s the difference between DELETE and TRUNCATE

Following are difference between them:

  • DELETE TABLE syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.
  • DELETE table can have criteria while TRUNCATE can not.
  • TRUNCATE table can not have triggers.

I have a table which has lot of inserts, is it a good database designto create indexes on that table

Insert’s are slower on tables which have indexes, justify it?or Why do page splitting happen?

All indexing fundamentals in database use “B-tree” fundamental. Now whenever there is new data inserted or deleted the tree tries to become unbalance.

Creates a new page to balance the tree.Shuffle and move the data to pages.

So if your table is having heavy inserts that means it’s transactional, then you can visualize the amount of splits it will be doing. This will not only increase insert time but will also upset the end-user who is sitting on the screen. So when you forecast that a table has lot of inserts it’s not a good idea to create indexes.

What are Aggregate and Scalar Functions

Aggregate and Scalar functions are in built function for counting and calculations.

Aggregate functions operate against a group of values but returns only one value.

  • AVG(column) :- Returns the average value of a column
  • COUNT(column) :- Returns the number of rows (without a NULL value) of a column
  • COUNT(*) :- Returns the number of selected rows
  • MAX(column) :- Returns the highest value of a column
  • MIN(column) :- Returns the lowest value of a column

Scalar functions operate against a single value and return value on basis of the single value.

  • UCASE(c) :- Converts a field to upper case
  • LCASE(c) :- Converts a field to lower case
  • MID(c,start[,end]) :- Extract characters from a text field
  • LEN(c) :- Returns the length of a text