Database Interview questions for freshers as well 1 to 3 year experienced on advance and basic DBMS , RDBMS, Database with examples that cover the essentials of SQL and Database means all type of databse questions covered under this page and also a form is given at the end of this page for those user who want more Interview questions and answer on Database just need to fill the form and send us we will send all the answers to them and it for both freshers and experienced condidate and also chat option is given in right hand side for directly chat with for answers
|Questions : 1||What is database or database management systems (DBMS)? and - What’s the difference between file and database? Can files qualify as a database?|
|Answers : 1||
Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information.
Secondly the information has to be persistent, that means even after the application is closed the information should be persisted.
Finally it should provide an independent way of accessing data and should not be dependent on the application to access the information.
Main difference between a simple file and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and retrieving part of a database but not the independent way of accessing data. Many experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we look at some old COBOL and C programs where file where the only means of storing data, we can see functionalities like locking, multi-user etc provided very efficiently. So it’s a matter of debate if some interviewers think this as a main difference between files and database accept it… going in to debate is probably loosing a job.
|Questions : 2||What is SQL ?|
|Answers : 2||
SQL stands for Structured Query Language.SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.
|Questions : 3||What’s difference between DBMS and RDBMS ?|
|Answers : 3||
DBMS provides a systematic and organized way of storing, managing
and retrieving from collection of logically related information. RDBMS also provides
what DBMS provides but above that it provides relationship integrity. So in short we can
|Questions : 4||What are CODD rules?
|Answers : 4||
In 1969 Dr. E. F. Codd laid down some 12 rules which a DBMS should adhere in order to get the logo of a true RDBMS.
Rule 1: Information Rule.
|Questions : 5||What are E-R diagrams?|
|Answers : 5||
E-R diagram also termed as Entity-Relationship diagram shows relationship between various tables in the database. .
|Questions : 6||How many types of relationship exist in database designing?|
|Answers : 6||
There are three major relationship models:-
|Questions : 7||7.What is normalization? What are different type of normalization?|
|Answers : 7||
There is set of rules that has been established to aid in the design of tables that are meant to
be connected through relationships. This set of rules is known as Normalization.
|Questions : 8||What is denormalization ?|
|Answers : 8||
Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance.The sacrifice to performance is that you increase redundancy in database.
|Questions : 9||Can you explain Fourth Normal Form and Fifth Normal Form ?|
|Answers : 9||
In fourth normal form it should not contain two or more independent multi-v
about an entity and it should satisfy “Third Normal form”.
|Questions : 10||Have you heard about sixth normal form?|
|Answers : 10||
If we want relational system in conjunction with time we use sixth normal form. At this moment SQL Server does not supports it directly.
|Questions : 11||What are DML and DDL statements?|
|Answers : 11||
DML stands for Data Manipulation Statements. They update data values in table. Below
are the most important DDL statements:-
|Questions : 12||How do we select distinct values from a table?|
|Answers : 12||
DISTINCT keyword is used to return only distinct values. Below is syntax:- Column age and Table pcdsEmp
|Questions : 13||What is Like operator for and what are wild cards?|
|Answers : 13||
LIKE operator is used to match patterns. A "%" sign is used to define the pattern.
|Questions : 14||Can you explain Insert, Update and Delete query?|
|Answers : 14||
Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :-INSERT INTO pcdsEmployee SET name='rohit',age='24';
UPDATE pcdsEmployee SET age='25' where name='rohit';
DELETE FROM pcdsEmployee WHERE name = 'sonia';
|Questions : 15||What is order by clause?|
|Answers : 15||
ORDER BY clause helps to sort the data in either ascending order to descending order.
|Questions : 16||What is the SQL " IN " clause?|
|Answers : 16||
SQL IN operator is used to see if the value exists in a group of values. For instance the below SQL checks if the Name is either 'rohit' or 'Anuradha' SELECT * FROM pcdsEmployee WHERE name IN ('Rohit','Anuradha') Also you can specify a not clause with the same. SELECT * FROM pcdsEmployee WHERE age NOT IN (17,16)
|Questions : 17||Can you explain the between clause?|
|Answers : 17||Below SQL selects employees born between '01/01/1975' AND '01/01/1978' as per mysql SELECT * FROM pcdsEmployee WHERE DOB BETWEEN '1975-01-01' AND '2011-09-28'|
|Questions : 18||we have an employee salary table how do we find the second highest from it?|
|Answers : 18||
below Sql Query find the second highest salary
|Questions : 19||What are different types of joins in SQL?|
|Answers : 19||
|Questions : 20||What is “CROSS JOIN”? or What is Cartesian product?|
|Answers : 20||
“CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table. In real life scenario I can not imagine where we will want to use a Cartesian product. But there are scenarios where we would like permutation and combination probably Cartesian would be the easiest way to achieve it.
|Questions : 21||How to select the first record in a given set of rows?|
|Answers : 21||
Select top 1 * from sales.salesperson
|Questions : 22||What is the default “-SORT ” order for a SQL?|
|Answers : 22||
|Questions : 23||What is a self-join?|
|Answers : 23||
If we want to join two instances of the same table we can use self-join.
|Questions : 24||What’s the difference between DELETE and TRUNCATE ?|
|Answers : 24||
Following are difference between them:
|Questions : 25||What’s the difference between “UNION” and “UNION ALL” ?|
|Answers : 25||
UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.
|Questions : 26||What are cursors and what are the situations you will use them?|
|Answers : 26||
SQL statements are good for set at a time operation. So it is good at handling set of data. But there are scenarios where we want to update row depending on certain criteria. we will loop through all rows and update data accordingly. There’s where cursors come in to picture.
|Questions : 27||What is " Group by " clause?|
|Answers : 27||
“Group by” clause group similar data so that aggregate values can be derived.
|Questions : 28||What is the difference between “HAVING” and “WHERE” clause?|
|Answers : 28||
“HAVING” clause is used to specify filtering criteria for “GROUP BY”, while “WHERE” clause applies on normal SQL.
|Questions : 29||What is a Sub-Query?|
|Answers : 29||
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.
|Questions : 30||What are Aggregate and Scalar Functions?|
|Answers : 30||
Aggregate and Scalar functions are in built function for counting and calculations.
|Questions : 31||Can you explain the SELECT INTO Statement?|
|Answers : 31||
SELECT INTO statement is used mostly to create backups. The below SQL backsup the Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of pcdsEmployeeBackup and pcdsEmployee table should be same. SELECT * INTO pcdsEmployeeBackup FROM pcdsEmployee
|Questions : 32||What is a View?|
|Answers : 32||
View is a virtual table which is created on the basis of the result set returned by the select
|Questions : 33||What is SQl injection ?|
|Answers : 33||
It is a Form of attack on a database-driven Web site in which the attacker executes
unauthorized SQL commands by taking advantage of insecure code on a system connected
to the Internet, bypassing the firewall. SQL injection attacks are used to steal information
from a database from which the data would normally not be available and/or to gain
access to an organization’s host computers through the computer that is hosting the
|Questions : 34||What is Data Warehousing ?|
|Answers : 34||
Data Warehousing is a process in which the data is stored and accessed from central location and is meant to support some strategic decisions. Data Warehousing is not a requirement for Data mining. But just makes your Data mining process more efficient.
Data warehouse is a collection of integrated, subject-oriented databases designed to support the decision-support functions (DSF), where each unit of data is relevant to some moment in time.
|Questions : 35||What are Data Marts?|
|Answers : 35||
Data Marts are smaller section of Data Warehouses. They help data warehouses collect data. For example your company has lot of branches which are spanned across the globe. Head-office of the company decides to collect data from all these branches for anticipating market. So to achieve this IT department can setup data mart in all branch offices and a central data warehouse where all data will finally reside.
|Questions : 36||What are Fact tables and Dimension Tables ? What is Dimensional Modeling and Star Schema Design|
|Answers : 36||
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.
|Questions : 37||What is Snow Flake Schema design in database? What’s the difference between Star and Snow flake schema?|
|Answers : 37||
Star schema is good when you do not have big tables in data warehousing. But when tables start becoming really huge it is better to denormalize. When you denormalize star schema it is nothing but snow flake design. For instance below customeraddress table is been normalized and is a child table of Customer table. Same holds true for Salesperson table.
|Questions : 38||What is ETL process in Data warehousing? What are the different stages in “Data warehousing”?|
|Answers : 38||
ETL (Extraction, Transformation and Loading) are different stages in Data warehousing.
Like when we do software development we follow different stages like requirement
gathering, designing, coding and testing. In the similar fashion we have for data warehousing.
|Questions : 39||What is Data mining ?|
|Answers : 39||
Data mining is a concept by which we can analyze the current data from different
perspectives and summarize the information in more useful manner. It’s mostly used
either to derive some valuable information from the existing data or to predict sales to
increase customer market.
|Questions : 40||Compare Data mining and Data Warehousing ?|
|Answers : 40||
“Data Warehousing” is technical process where we are making our data centralized while “Data mining” is more of business activity which will analyze how good your business is doing or predict how it will do in the future coming times using the current data. As said before “Data Warehousing” is not a need for “Data mining”. It’s good if you are doing “Data mining” on a “Data Warehouse” rather than on an actual production database. “Data Warehousing” is essential when we want to consolidate data from different sources, so it’s like a cleaner and matured data which sits in between the various data sources and brings then in to one format. “Data Warehouses” are normally physical entities which are meant to improve accuracy of “Data mining” process. For example you have 10 companies sending data in different format, so you create one physical database for consolidating all the data from different company sources, while “Data mining” can be a physical model or logical model. You can create a database in “Data mining” which gives you reports of net sales for this year for all companies. This need not be a physical database as such but a simple query.
|Questions : 41||What are indexes? What are B-Trees?|
|Answers : 41||
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.
|Questions : 42||I have a table which has lot of inserts, is it a good database design to
create indexes on that table?
Insert’s are slower on tables which have indexes, justify it?or Why do page splitting happen?
|Answers : 42||
All indexing fundamentals in database use “B-tree”
fundamental. Now whenever there is new data inserted or deleted the tree tries to become
|Questions : 43||What are the two types of indexes and explain them in detail? or What’s the difference between clustered and non-clustered indexes?|
|Answers : 43||
There are basically two types of indexes:-