1. What is DBMS?
Database
management system is a collection of programs that enables user to store,
retrieve, update and delete information from a database.
2. What is RDBMS?
Relational
Database Management system (RDBMS) is a database management system (DBMS) that
is based on the relational model. Data from relational database can be accessed
or reassembled in many different ways without having to reorganize the database
tables . Data from relational database can be accessed using a API , Structured
Query Language (SQL)
3. What is SQL?
Structured Query Language(SQL) is
a language designed specifically for communicating with databases. SQL is an
ANSI (American National Standards Institute) standard.
4. What
is the different type of Sql's?
Frequently asked SQL Interview
Questions
1. DDL – Data Definition
Language
DDL is used to define the
structure that holds the data. For example: table
2. DML – Data Manipulation
Language
DML is used for manipulation of
the data itself. Typical operations are Insert, Delete, Update and retrieving
the data from the table
3. DCL – Data Control
Language
DCL is used to control the
visibility of data like granting database access and set privileges to create
table etc.
5. What are the Advantages of SQL?
1. SQL is not a proprietary
language used by specific database vendors. Almost every major DBMS supports
SQL, so learning this one language will enable programmer to interact with any
database like ORACLE, SQL, MYSQL etc.
2. SQL is easy to learn. The
statements are all made up of descriptive English words, and there aren't that
many of them.
3. SQL is actually a very
powerful language and by using its language elements you can perform very
complex and sophisticated database operations.
6. What is a field in a database?
A field is an area within a
record reserved for a specific piece of data. Examples: Employee Name, Employee
ID etc
7. What is a Record in a database?
A record is the collection of
values / fields of a specific entity: i.e. a Employee , Salary etc
8. What is a Table in a database?
A table
is a collection of records of a specific type. For example, employee table,
salary table etc.
SQL Interview Questions and answers on
Database Transactions
9. What is a database transaction?
Frequently
asked SQL Interview Questions,
Database
transaction take DB from one consistent state to another. At the end of the
transaction the system must be in the prior state if transaction fails or the
status of the system should reflect the successful completion if the
transaction goes through.
10. What are properties of a transaction?
Frequently
asked SQL Interview Questions
Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
In this, a transaction consists
of many steps. When all the steps in the transaction go completed it get
reflected in DB or if any step fails, all the transactions are rolled back.
2. Consistency
The database will move from one
consistent state to another if the transaction succeeds and remain in the
original state if the transaction fails.
3. Isolation
Every transaction should operate
as if it is the only transaction in the system
4. Durability
Once a transaction has completed
successfully, the updated rows/records must be available for all other
transactions on a permanent basis
11. What is a Database Lock?
Database lock tell a transaction
if the data item in questions is currently being used by other transactions.
12. What are the type of locks?
1. Shared Lock
When a shared lock is applied on
data item, other transactions can only read the item, but can’t write into it.
2. Exclusive Lock
When a exclusive lock is applied
on data item, other transactions can’t read or write into the data item.
SQL Interview Questions and answers on
Database Normalization
13. What are the different type of normalization?
Frequently asked SQL Interview
Questions
In database design , we start
with one single table, with all possible columns. Lot of redundant data would
be present since it’s a single table. The process of removing the redundant
data, by splitting up the table in a well defined fashion is called
normalization.
1. First Normal Form (1NF)
A relation is said to be in first
normal form if and only if all underlying domains contain atomic values only.
After 1NF, we can still have redundant data
2. Second Normal Form (2NF)
A relation is said to be in 2NF
if and only if it is in 1NF and every non key attribute is fully dependent on
the primary key. After 2NF, we can still have redundant data
3. Third Normal Form (3NF)
A relation is said to be in 3NF
if and only if it is in 2NF and every non key attribute is non-transitively
dependent on the primary key
SQL Interview Questions and answers on
Database Keys and Constraints
14. What is a primary key?
Frequently asked SQL Interview
Questions
A primary key is a column whose
values uniquely identify every row in a table. Primary key values can never be
reused. If a row is deleted from the table, its primary key may not be assigned
to any new rows in the future. In the examples given below “Employee_ID” field
is the primary key.
1. To define a field as primary
key, following conditions had to be met:
2. No two rows can have the same
primary key value.
3. Every row must have a primary
key value
4. Primary key field cannot be
null
5. Values in primary key columns
can never be modified or updated
15. What is a SQL Composite Primary Key
A
Composite primary key is a set of columns whose values uniquely identify every
row in a table. For example, in the table given above , if
"Employee_ID" and "Employee Name" together uniquely
identifies a row it’s called a Composite Primary Key . In this case, both the
columns will be represented as primary key.
16. What is a Composite Primary Key ?
A
Composite primary key is a set of columns whose values uniquely identify every
row in a table. For example , in the table given above , if
"Employee_ID" and "Employee Name" together uniquely
identifies a row its called a Composite Primary Key . In this case , both the
columns will be represented as primary key .
17. What is a Foreign Key?
Frequently asked SQL Interview
Questions
When a "one" table's
primary key field is added to a related "many" table in order to
create the common field which relates the two tables, it is called a foreign
key in the "many" table. In the example given below, salary of an employee
is stored in salary table. Relation is established via foreign key column
“Employee_ID_Ref” which refers “Employee_ID” field in Employee table.
18. What is a Unique Key?
Unique key is same as primary
with difference being existence of null. Unique key field allows one value as
NULL value
SQL Interview Questions and answers on
SQL Commands
19. Define SQL Insert Statement?
Frequently asked SQL Interview
Questions
SQL INSERT statement is used to
add rows to a table. For a full row insert , SQL Query should start with
“insert into “ statement followed by table name and values command followed by
the values that need to be inserted into the table. Insert can be used in
several ways:
1. To insert a single complete
row
2. To insert a single partial row
20. Define SQL Update Statement?
Frequently asked SQL Interview
Questions
SQL Update is used to update data
in a row or set of rows specified in the filter condition.
The basic format of an SQL UPDATE
statement is, Update command followed by table to be updated and SET command
followed by column names and their new values followed by filter condition that
determines which rows should be updated
21. Define SQL Delete Statement?
SQL Delete is used to delete a
row or set of rows specified in the filter condition .The basic format of an
SQL DELETE statement is,DELETE FROM command followed by table name followed by
filter condition that determines which rows should be updated
22. What are wild cards used in database for Pattern Matching?
Frequently
asked SQL Interview Questions
SQL
'Like' search takes more time to process. So before using like operator
following tips should be considered.
Don't overuse wild cards. If another search operator will do, use it instead.
When you do use wild cards, try to not use them at the beginning of the search
pattern unless absolutely necessary. Search patterns that begin with wild cards
are the slowest to process.
Pay careful attention to the placement of the wild card symbols. If they are
misplaced, you might not return the data you intended
SQL Interview Questions and answers on
SQL Joins
23. Define Join and explain different type of joins?
Frequently asked SQL Interview
Questions
In order to avoid data
duplication, data is stored in related tables. Join keyword is used to fetch
data from related table. Join return rows when there is at least one match in
both tables. Type of joins are
Right Join
Return all rows from the right
table, even if there are no matches in the left table.
Outer Join
Left Join
Return all rows from the left
table, even if there are no matches in the right table .
Full Join
Return rows when there is a match
in one of the tables.
24. What is Self-Join?
Self-join is query used to join a
table to itself. Aliases should be used for the same table comparison.
25. What is Cross Join?
Cross Join will return all
records where each row from the first table is combined with each row from the
second table.
SQL Interview Questions and answers on
Database Views
26. What is a view?
Frequently asked SQL Interview
Questions
Views are virtual tables. Unlike
tables that contain data, views simply contain queries that dynamically
retrieve data when used.
27. What is a materialized view?
Materialized views are also a
view but are disk based. Materialized views get updated on specific duration,
base upon the interval specified in the query definition. We can index
materialized view.
28. What are the advantages and disadvantages of views in a database?
Advantages:
1. Views doesn't store data in a
physical location.
2. View can be use to hide some
of the columns from the table
3. Views can provide Access Restriction,
since data insertion, update and deletion is not possible on the view.
Disadvantages:
1. When a table is dropped,
associated view become irrelevant.
2. Since view are created when a
query requesting data from view is triggered, its bit slow
3. When views are created for
large tables, it occupies more memory.
SQL Interview Questions and
answers on Stored Procedures and Triggers
29. What is a stored procedure?
Frequently
asked SQL Interview Questions
Stored
Procedure is a function which contain collection of SQL Queries. Procedure can
take inputs, process them and send back output.
30. What are the advantages a stored procedure?
Stored
Procedures are precompiled and stored in database. This enable the database to
execute the queries much faster. Since many queries can be included in a stored
procedure, round trip time to execute multiple queries from source code to
database and back is avoided.
31. What is a trigger?
Database are set of commands that
get executed when an event(Before Insert, AfterInsert, OnUpdate, On delete of a
row) occurs on a table, views.
32. Explain the difference between DELETE, TRUNCATE and DROP commands?
Once delete operation is
performed Commit and Rollback can be performed to retrieve data. But after
truncate statement, Commit and Rollback statement can’t be performed. Where
condition can be used along with delete statement but it can’t be used with
truncate statement. Drop command is used to drop the table or keys like
primary, foreign from a table.
33. What is the difference between Cluster and Non cluster Index?
A clustered index reorders the way
records in the table are physically stored. There can be only one clustered
index per table. It make data retrieval faster. A non clustered index does not
alter the way it was stored but creates a complete separate object within the
table. As a result insert and update command will be faster.
34. What is Union, minus and INTERSECT commands?
MINUS operator is used to return
rows from the first query but not from the second query. INTERSECT operator is
used to return rows returned by both the queries.
What are DMVs?
Dynamic
management views (DMVs) and functions return server state information that can
be used to monitor the health of a server instance, diagnose problems, and tune
performance; that is, they let you see what is going on inside SQL Server. They
were introduced in SQL Server 2005 as an alternative to system tables. One
example is viewing operating system wait statistics via this query:
SELECT * FROM
sys.dm_os_wait_stats;
Another
example is examining current sessions, much like the sp_who2 command:
SELECT * FROM
sys.dm_exec_sessions;
What are temp tables? What is the difference
between global and local temp tables?
Temporary
tables are temporary storage structures. You may use temporary tables as
buckets to store data that you will manipulate before arriving at a final
format. The hash (#) character is used to declare a temporary table as it is
prepended to the table name. A single hash (#) specifies a local temporary
table.
CREATE TABLE #tempLocal(
nameidint, fnamevarchar(50), lnamevarchar(50) )
Local
temporary tables are available to the current connection for the user, so they
disappear when the user disconnects.
Global
temporary tables may be created with double hashes (##). These are available to
all users via all connections, and they are deleted only when all connections
are closed.
CREATE TABLE ##tempGlobal(
nameidint, fnamevarchar(50), lnamevarchar(50) )
Once
created, these tables are used just like permanent tables; they should be
deleted when you are finished with them. Within SQL Server, temporary tables
are stored in the Temporary Tables folder of the tempdb database.
How are transactions used?
Transactions
allow you to group SQL commands into a single unit. The transaction begins with
a certain task and ends when all tasks within it are complete. The transaction
completes successfully only if all commands within it complete successfully.
The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK
TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions.
A group of tasks starts with the begin statement. If any problems occur, the
rollback command is executed to abort. If everything goes well, all commands
are permanently executed via the commit statement.
What is the difference between a clustered and a
nonclustered index?
A
clustered index affects the way the rows of data in a table are stored on disk.
When a clustered index is used, rows are stored in sequential order according
to the index column value; for this reason, a table can contain only one
clustered index, which is usually used on the primary index value.
A
nonclustered index does not affect the way data is physically stored; it
creates a new object for the index and stores the column(s) designated for
indexing with a pointer back to the row containing the indexed values.
You can
think of a clustered index as a dictionary in alphabetical order, and a
nonclustered index as a book's index.
What are DBCC commands?
Basically,
the Database Consistency Checker (DBCC) provides a set of commands (many of
which are undocumented) to maintain databases -- maintenance, validation, and
status checks. The syntax is DBCC followed by the command name. Here are three
examples:
DBCC
CHECKALLOC -- Check disk allocation consistency.
DBCC
OPENTRAN -- Display information about recent transactions.
DBCC HELP
-- Display Help for DBCC commands.
What is the difference between truncate and delete?
Truncate
is a quick way to empty a table. It removes everything without logging each
row. Truncate will fail if there are foreign key relationships on the table.
Conversely, the delete command removes rows from a table, while logging each
deletion and triggering any delete triggers that may be present.
What does the NOLOCK query hint do?
Table
hints allow you to override the default behaviour of the query optimizer for
statements. They are specified in the FROM clause of the statement. While
overriding the query optimizer is not always suggested, it can be useful when
many users or processes are touching data. The NOLOCK query hint is a good
example because it allows you to read data regardless of who else is working
with the data; that is, it allows a dirty read of data -- you read data no
matter if other users are manipulating it. A hint like NOLOCK increases
concurrency with large data stores.
SELECT * FROM table_name (NOLOCK)
What is a CTE?
A common
table expression (CTE) is a temporary named result set that can be used within
other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as
an object and its lifetime is limited to the query. It is defined using the
WITH statement as the following example shows:
WITH ExampleCTE (id, fname,
lname)
AS
(
SELECT id, firstname, lastname
FROM table
)
SELECT * FROM ExampleCTE
A CTE can
be used in place of a view in some instances.
What is a view? What is the WITH CHECK OPTION
clause for a view?
A view is
a virtual table that consists of fields from one or more real tables. Views are
often used to join multiple tables or to control access to the underlying
tables.
The WITH
CHECK OPTION for a view prevents data modifications (to the data) that do not
confirm to the WHERE clause of the view definition. This allows data to be
updated via the view, but only if it belongs in the view.
What is a query execution plan?
SQL
Server has an optimizer that usually does a great job of optimizing code for
the most effective execution. A query execution plan is the breakdown of how
the optimizer will run (or ran) a query. There are several ways to view a query
execution plan. This includes using the Show Execution Plan option within Query
Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use
the SET SHOWPLAN_TEXT ON command before running a query and capturing the
execution plan event in a SQL Server Profiler trace.
What does the SQL Server Agent Windows service do?
SQL
Server Agent is a Windows service that handles scheduled tasks within the SQL
Server environment (aka jobs). The jobs are stored/defined within SQL Server,
and they contain one or more steps that define what happens when the job runs.
These jobs may run on demand, as well as via a trigger or predefined schedule.
This service is very important when determining why a certain job did not run
as planned -- often it is as simple as the SQL Server Agent service not
running.
What is the default port number for SQL Server?
If
enabled, the default instance of Microsoft SQL Server listens on TCP port 1433.
Named instances are configured for dynamic ports, so an available port is
chosen when SQL Server starts. When connecting to a named instance through a
firewall, configure the Database Engine to listen on a specific port, so that
the appropriate port can be opened in the firewall.
|
1.What is a "trigger"?
|
|
Microsoft
SQL Server includes support for a special type of stored procedure called a
trigger. A trigger is a stored procedure that executes whenever an update,
delete or insert statement is executed against a table or a view. Triggers
are created in order to enforce integrity rules in a database. In other words,
you can associate a trigger with a table in such a way that it fires whenever
a change is made to the contents of the table. Basically, trigger is a set of
SQL statements that execute in response to a data modification/retrieval
event on a table. A trigger is a solution to the restrictions of a
constraint. For instance :
·
A database column cannot carry PSEUDO columns as
criteria where a trigger can.
·
A database constraint cannot refer old and new
values for a row where a trigger can.
Also,
other than table triggers, there are also schema and database triggers. These
can be made to fire when new objects are created, when a user logs in, when
the database shutdown etc. Table level triggers can be classified into row
and statement level triggers and those can be further broken down into before
and after triggers.
·
In SQL Server 6.5 you could define only 3
triggers per table, one for INSERT, one for UPDATE and one for DELETE.
·
From SQL Server 7.0 onwards, this restriction is
gone, and you could create multiple triggers per each action. But in 7.0
there’s no way to control the order in which the triggers fire.
·
In SQL Server 2000 you could specify which
trigger fires first or fires last using sp_settriggerorder. Triggers can’t be
invoked on demand. They get triggered only when an associated action (INSERT,
UPDATE, and DELETE) happens on the table on which they are defined.
Triggers
are generally used to implement business rules, auditing. Triggers can also
be used to extend the referential integrity checks, but wherever possible,
use constraints for this purpose, instead of triggers, as constraints are
much faster. Till SQL Server 7.0, triggers fire only after the data
modification operation happens. So in a way, they are called post triggers.
But in SQL Server 2000 you could create pre triggers also.
|
|
What is "index covering" of a query?
|
|
A
non-clustered index that includes (or covers) all columns used in a query is
called a covering index. When SQL server can use a non-clustered index to
resolve the query, it will prefer to scan the index rather than the table,
which typically takes fewer data pages. If your query uses only columns
included in the index, then SQL server may scan this index to produce the desired
output.
|
|
What types of join algorithms can you have?
|
|
You can
dynamically relate different tables by applying what's known as a join.
Technically, a join is the operation used when selecting data to create these
relationships at retrieval time. What that means to you is that a join
determines which records are selected and which aren't. In this article,
we'll introduce you to several types of joins supported by SQL and show you
how to correctly apply them to get the data you need.
What that
means is that a join is conditional—similar to a WHERE clause or criteria
expression—in that the join specifies which records (rows) are selected in
both tables. Certainly, the type of join significantly impacts which records
are retrieved or acted upon. For the most part, a join can be specified in a
FROM or WHERE clause, so be careful where you specify them. Because the data
engine executes the clauses in a specific sequence, placement can affect the
results.
Generally,
the data engine fulfills the FROM clause before applying any other clauses.
Adding the join here eliminates records before they're evaluated by any other
clauses. As a rule, you'll find this is the recommended method.
Joins are
used in queries to explain how different tables are related. Joins also let
you select data from a table depending upon data from another table. Types of
joins: INNER JOIN, OUTER JOIN, and CROSS JOIN. OUTER JOIN’s are further
classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
|
|
What is a SQL View?
|
|
A view can be thought of as either a virtual
table or a stored query. The data accessible through a view is not stored in
the database as a distinct object. What is stored in the database is a SELECT
statement. The result set of the SELECT statement forms the virtual table
returned by the view. A user can use this virtual table by referencing the
view name in Transact-SQL statements the same way a table is referenced. A
view is used to do any or all of these functions:
·
Restrict a user to specific rows in a table. For
example, allow an employee to see only the rows recording his or her work in
a labor-tracking table.
·
Restrict a user to specific columns. For example,
allow employees who do not work in payroll to see the name, office, work
phone, and department columns in an employee table, but do not allow them to
see any columns with salary information or personal information.
·
Join columns from multiple tables so that they
look like a single table.
·
Aggregate information instead of supplying
details. For example, present the sum of a column, or the maximum or minimum
value from a column.
Views are created by defining the SELECT
statement that retrieves the data to be presented by the view. The data
tables referenced by the SELECT statement are known as the base tables for
the view. In this example, titleview in the pubs database is a
view that selects data from three base tables to present a virtual table of
commonly needed data:
CREATE VIEW titleview
AS
SELECT title, au_ord,
au_lname, price, ytd_sales, pub_id
FROM authors AS a
JOIN
titleauthor AS ta ON (a.au_id = ta.au_id)
JOIN titles AS t ON (t.title_id =
ta.title_id)
You can then reference titleview in
statements in the same way you would reference a table:
SELECT *
FROM titleview
A view can reference another view. For example, titleview
presents information that is useful for managers, but a company typically
discloses year-to-date figures only in quarterly or annual financial
statements. A view can be built that selects all the titleview columns
except au_ord and ytd_sales. This new view can be used by
customers to get lists of available books without seeing the financial
information:
CREATE VIEW Cust_titleview
AS
SELECT title, au_lname, price,
pub_id
FROM titleview
Views in all versions of SQL Server are updatable
(can be the target of UPDATE, DELETE, or INSERT statements), as long as the
modification affects only one of the base tables referenced by the view, for
example:
-- Increase the prices for
publisher '0736' by 10%.
UPDATE titleview
SET price = price * 1.10
WHERE pub_id = '0736'
GO
SQL Server 2000 supports more complex types of
INSERT, UPDATE, and DELETE statements that reference views. INSTEAD OF
triggers can be defined on a view to specify the individual updates that must
be performed against the base tables to support the INSERT, UPDATE, or DELETE
statement. Also, partitioned views support INSERT, UPDATE, and DELETE
statements that modify multiple member tables referenced by the view.
Indexed views are a SQL Server 2000 feature that
greatly improves the performance of complex views of the type usually found
in data warehouses or other decision support systems.
Views are
called virtual tables because the result set of a view is not usually saved
in the database. The result set for a view is dynamically incorporated into
the logic of the statement and the result set is built dynamically at run
time.
|
|
What is a Primary Key?
|
|
Primary
Key is a unique column in the table that identifies each row. There cannot be
more than 1 row in the table with the same primary key. The primary key
contains a unique identifier to maintain each record's unique identity.
Primary keys field can include an employee ID, part number, or customer
number. Typically, you specify which column contains the primary key when you
create a database table.
|
|
Define candidate key, alternate key and composite key.
|
|
A
candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table. If the table
has more than one candidate key, one of them will become the primary key, and
the rest are called alternate keys. A key formed by combining at least two or
more columns is called composite key.
|
|
What’s the difference between a primary key and a unique key?
|
|
Both
primary key and unique key enforce uniqueness of the column on which they are
defined. But by default primary key creates a clustered index on the column,
whereas unique key creates a non-clustered index by default. Another
difference is that, primary key doesn’t allow NULL, but unique key allows one
NULL only.
|
|
What is a "constraint"?
|
|
A
constraint allows you to apply simple referential integrity checks to a
table. There are four primary types of constraints that are currently
supported by SQL Server:
·
PRIMARY/UNIQUE - enforces uniqueness of a
particular table column.
·
DEFAULT - specifies a default value for a column
in case an insert operation does not provide one.
·
FOREIGN KEY - validates that every value in a
column exists in a column of another table.
·
CHECK - checks that every value stored in a
column is in some specified list
Each type
of constraint performs a specific type of action. There are five kinds of
constraints in all, Primary key, Foreign key, Unique, Check and Default.
|
|
What action do you have to perform before retrieving data from the
next result set of a
stored procedure?
|
|
Move the
cursor down one row from its current position. A Result Set cursor is
initially positioned before the first row. Before you can get to the first
row, you would need to Move the cursor down by one row.
With
certain database systems, a stored procedure can return multiple result sets,
multiple update counts, or some combination of both. Also, if you are
providing a user with the ability to enter any SQL statement, you don't know
if you are going to get a ResultSet or an update count back from each
statement, without analyzing the contents. The Statement.execute() method
helps in these cases. Method Statement.execute() returns a Boolean to tell
you the type of response:
·
true indicates next result is a ResultSet. Use
Statement.getResultSet to get the ResultSet
·
false indicates next result is an update count.
Use Statement.getUpdateCount to get the update count
·
false also indicates no more results. Update
count is -1 when no more results (usually 0 or positive)
After
processing each response, you use Statement.getMoreResults to check for more
results, again returning a Boolean. The following demonstrates the processing
of multiple result sets:
boolean result = stmt.execute(" ... ");
intupdateCount = stmt.getUpdateCount();
while (result || (updateCount != -1)) {
if(result) {
ResultSet r = stmt.getResultSet();
// process result set
} else if(updateCount != -1) {
// process update count
}
result = stmt.getMoreResults();
updateCount = stmt.getUpdateCount(); }
|
|
Briefly explain about Database.
|
|
A
collection of information organized in such a way that a computer program can
quickly select desired pieces of data. You can think of a database as an
electronic filing system. Traditional databases are organized by fields,
records, and files. A field is a single piece of information; a
record is one complete set of fields; and a file is a collection of records.
For example, a telephone book is analogous to a file. It contains a list of
records, each of which consists of three fields: name, address, and telephone
number.
An
alternative concept in database design is known as Hypertext. In a
Hypertext database, any object, whether it be a piece of text, a picture, or
a film, can be linked to any other object. Hypertext databases are
particularly useful for organizing large amounts of disparate information,
but they are not designed for numerical analysis.
To access
information from a database, you need a database management system (DBMS).
This is a collection of programs that enables you to enter, organize, and
select data in a database.
|
|
Briefly explain about DBMS.
|
|
A
collection of programs that enables you to store, modify, and extract
information from a database. There are many different types of DBMSs, ranging
from small systems that run on personal computers to huge systems that run on
mainframes. The following are examples of database applications:
·
computerized library systems
·
automated teller machines
·
flight reservation systems
·
computerized parts inventory systems
From a
technical standpoint, DBMSs can differ widely. The terms relational, network,
flat, and hierarchical all refer to the way a DBMS organizes
information internally. The internal organization can affect how quickly and
flexibly you can extract information.
Requests
for information from a database are made in the form of a query, which
is a stylized question. For example, the query
SELECT ALL WHERE NAME = "SMITH"
AND AGE > 35
requests
all records in which the NAME field is SMITH and the AGE field is greater
than 35. The set of rules for constructing queries is known as a query
language. Different DBMSs support different query languages, although
there is a semi-standardized query language called SQL(structured query
language). Sophisticated languages for managing database systems are
called fourth-generation languages, or 4GLs for short.
The
information from a database can be presented in a variety of formats. Most
DBMSs include a report writerprogram that enables you to output data
in the form of a report. Many DBMSs also include a graphics component that
enables you to output information in the form of graphs and charts.
|
|
Briefly explain about RDBMS.
|
|
Short for relational
database management system and pronounced as
separate letters, a type of database management system (DBMS) that stores
data in the form of related tables. Relational databases are powerful because
they require few assumptions about how data is related or how it will be
extracted from the database. As a result, the same database can be viewed in
many different ways.
An
important feature of relational systems is that a single database can be
spread across several tables. This differs from flat-file databases, in which
each database is self-contained in a single table. Almost all full-scale
database systems are RDBMS's. Small database systems, however, use other
designs that provide less flexibility in posing queries.
|
|
What is normalization? Explain different levels of normalization?
|
|
In a
relational database design, the process of organizing data to minimize
redundancy is termed as Normalization. Normalization usually involves
dividing a database into two or more tables and defining relationships
between the tables. The objective is to isolate data so that additions,
deletions, and modifications of a field can be made in just one table and
then propagated through the rest of the database via the defined
relationships.
Normalization
can be viewed as a series of steps (i.e., levels) designed, one after
another, to deal with ways in which tables can be "too complicated for
their own good". The purpose of normalization is to reduce the chances
for anomalies to occur in a database. The definitions of the various levels
of normalization illustrate complications to be eliminated in order to reduce
the chances of anomalies. At all levels and in every case of a table with a
complication, the resolution of the problem turns out to be the establishment
of two or more simpler tables which, as a group, contain the same information
as the original table but which, because of their simpler individual
structures, lack the complication.
There are
three main normal forms, each with increasing levels of normalization:
·
First Normal Form (1NF): We must eliminate repeating groups. That means any subgroups of
data that appear within the record should be spilt into separate tables. Each
field in a table contains different information. For example, in an employee
list, each table would contain only one birthdate field.
·
Second Normal Form (2NF): By eliminating partial dependencies. Partial dependencies means
that data does not depend on the primary key of the table to uniquely
identify it. Each field in a table that is not a determiner of the contents
of another field must itself be a function of the other fields in the table.
·
Third Normal Form (3NF): Eliminate all transitive (i.e. hidden) dependencies. In other
words every column that isn’t part of the key must depend on the key for its
information al value. No duplicate information is permitted. So, for example,
if two tables both require a birthdate field, the birthdate information would
be separated into a separate table, and the two other tables would then
access the birthdate information via an index field in the birthdate table.
Any change to a birthdate would automatically be reflecting in all tables
that link to the birthdate table.
There are
additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth
normal form (4NF) and fifth normal form (5NF). While normalization
makes databases more efficient to maintain, they can also make them more
complex because data is separated into so many different tables.
·
Boyce-Codd Normal Form (BCNF): A table is in BCNF if it is in 3NF and if every determinant is a
candidate key.
·
4th Normal Form (4NF): A table is in 4NF if it is in BCNF and if it has no multi-valued
dependencies.
·
5th Normal Form (5NF): A table is in 5NF, also called "Projection-Join Normal Form"
(PJNF), if it is in 4NF and if every join dependency in the table is a
consequence of the candidate keys of the table.
·
Domain-Key Normal Form (DKNF): A table is in DKNF if every constraint on the table is a logical
consequence of the definition of keys and domains.
|
|
What is denormalization and when would you go for it?
|
|
As the
name indicates, denormalization is the reverse process of normalization. It's
the controlled introduction of redundancy in to the database design. It helps
improve the query performance as the number of joins could be reduced.
|
|
What is an index? What are the types of indexes? How many clustered
indexes can be
created on a table? I created a separate index on each column of a table.
What are the advantages and disadvantages of this approach?
|
|
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 create a 11index 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 its 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 time, 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.
|
|
What is RAID and what are different types of RAID configurations?
|
|
Note: RAID is available only on Microsoft Windows NT 4.0 and
Microsoft Windows 2000.
RAID
(redundant array of independent disks) is a disk system that comprises
multiple disk drives (an array) to provide higher performance, reliability,
storage capacity, and lower cost. Fault-tolerant arrays are categorized in
six RAID levels, 0 through 5. Each level uses a different algorithm to
implement fault tolerance.
Although
RAID is not a part of Microsoft® SQL Server™ 2000, its implementation can
directly affect the way SQL Server performs. RAID levels 0, 1, and 5 are
typically used with SQL Server.
A hardware
disk array improves I/O performance because I/O functions, such as striping
and mirroring, are handled efficiently in firmware. Conversely, an operating
system–based RAID offers lower cost but consumes processor cycles. When cost
is a consideration and redundancy and high performance are required,
Microsoft Windows® NT® stripe sets with parity or Windows 2000 RAID-5 volumes
are a good solution.
Data
striping (RAID 0) is the RAID configuration with the highest performance, but
if one disk fails, all the data on the stripe set becomes inaccessible. A
common installation technique for relational database management systems is
to configure the database on a RAID 0 drive and then place the transaction
log on a mirrored drive (RAID 1). You can get the best disk I/O performance
for the database and maintain data recoverability (assuming you perform
regular database backups) through a mirrored transaction log.
If data
must be quickly recoverable, consider mirroring the transaction log and
placing the database on a RAID 5 disk. RAID 5 provides redundancy of all data
on the array, allowing a single disk to fail and be replaced in most cases
without system downtime. RAID 5 offers lower performance than RAID 0 or RAID
1 but higher reliability and faster recovery.
|
|
What are the steps you will take to improve performance of a poor
performing query?
|
|
There
could be a lot of reasons behind the poor performance of a query.
But some
general issues to talk about would be:
No
indexes, table scans, missing or out of date statistics, blocking, excess
recompilations of stored procedures, procedures and triggers without SET
NOCOUNT ON, poorly written query with unnecessarily complicated joins, too
much normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems
are:
SET
SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server
Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in
Query Analyzer.
|
|
Write down the general syntax for a SELECT statement covering all the
options.
|
|
SELECT select_list
[INTO
new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]
|
|
What is a self join? Explain it with an example.
|
|
Self-joins
are useful in SQL select statements and are often used to identify duplicate
entries or entries linked in other ways such as customers who share the same
telephone number or patients who share the same address. A self-join is an
internal table (or view) join. The self-join is between fields in a
table and fields within a virtual copy of the table.
Self join
is just like any other join, except that two instances of the same table will
be joined in the query. Here is an example: Employees table which contains
rows for normal employees as well as managers. So, to find out the managers
of all the employees, you need a self join.
CREATE TABLE emp
(
empidint,
mgridint,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
|
|
What is the difference between GROUP BY and DISTINCT?
|
|
GROUP
BY divides a table into groups. Groups can consist
of column names or results or computed columns. GROUP BY was added to SQL
because aggregate functions (like SUM) return the aggregate of all column
values every time they are called, and without the GROUP BY function it was
impossible to find the sum for each individual group of column values.
This
"Sales" Table:
|
Company
|
Amount
|
|
W3Schools
|
5500
|
|
IBM
|
4500
|
|
W3Schools
|
7100
|
And This
SQL: SELECT Company, SUM (Amount) FROM Sales
Returns
this result:
|
Company
|
SUM(Amount)
|
|
W3Schools
|
17100
|
|
IBM
|
17100
|
|
W3Schools
|
17100
|
The above
code is invalid because the column returned is not part of an aggregate. A
GROUP BY clause will solve this problem:
SELECT Company, SUM (Amount) FROM Sales
GROUP BY Company
Returns
this result:
|
Company
|
SUM(Amount)
|
|
W3Schools
|
12600
|
|
IBM
|
4500
|
DISTINCT: The SELECT
keyword allows us to grab all information from a column (or columns) on a
table. This, of course, necessarily means that there will be redundancies.
What if we only want to select each DISTINCT element? This is easy to
accomplish in SQL. All we need to do is to add DISTINCT after SELECT.
Example:
Select Companies from Order Table:Simple Table of Purchase Orders:
|
Company
|
OrderNumber
|
|
Sega
|
3412
|
|
DeveloperIQ
|
2312
|
|
Trio
|
4678
|
|
DeveloperIQ
|
6798
|
This SQL
statement: SELECT Company FROM Orders
Will return
this result:
|
Company
|
|
Sega
|
|
DeveloperIQ
|
|
Trio
|
|
DeveloperIQ
|
Note that
the company DeveloperIQ is listed twice in the result. Sometimes we don't
want that.
Example:
Select Distinct Companies from Orders
This SQL
statement: SELECT DISTINCT Company FROM
Orders
Will
return this result:
|
Company
|
|
Sega
|
|
DeveloperIQ
|
|
Trio
|
|
|
In SQL query, what is @@ERROR used for?
|
|
It is used
to track the error in Stored Procedure and Triggers. For example if @@ERROR =
NULL, it means there is no error in Stored Procedure and Triggers.
When an
error is encountered within a stored procedure, the best you can do (assuming
it’s a non-fatal error) is halt the sequential processing of the code and
either branch to another code segment in the procedure or return processing
to the calling application. Notice that the previous sentence is specific to
non-fatal errors. There are two types of errors in SQL Server: fatal and
non-fatal. Fatal errors cause a procedure to abort processing and
terminate the connection with the client application. Non-fatal errors
do not abort processing a procedure or affect the connection with the client
application. When a non-fatal error occurs within a procedure, processing
continues on the line of code that follows the one that caused the error.
You might
be wondering what actions cause fatal errors. Unfortunately, the actions that
cause a fatal error are not well documented. Each error has an associated
severity level that is a value between 0–25. The errors with a severity level
of 20 or above are all fatal, but once you get below this value there is no
well-defined rule as to which errors are fatal. In truth, though, worrying
about which errors are fatal is a bit useless because there is no code you
can implement that will allow you to handle them gracefully. Of course, you
can use pro-active coding to make sure fatal-errors do not occur. For
example, if your application allows users to type in the name of the table on
which a query is based you can verify its existence before referencing it
with dynamic SQL.
@@ERROR
The
@@ERROR system function is used to implement error handling code. It contains
the error ID produced by the last SQL statement executed during a client’s
connection. When a statement executes successfully, @@ERROR contains 0. To
determine if a statement executes successfully, an IF statement is used to
check the value of the function immediately after the target statement
executes. It is imperative that @@ERROR be checked immediately after the
target statement, because its value is reset when the next statement executes
successfully.
RAISERROR
The
RAISERROR statement is used to produce an ad hoc error message or to retrieve
a custom message that is stored in the sysmessages table. You can use this
statement with the error handling code presented in the previous section to
implement custom error messages in your applications.
Adding a
Permanent Custom Message
If you
have a message that is going to be used frequently, it is more efficient to
add it to the sysmessages table and reference it by its unique ID. The system
stored procedure sp_addmessages adds an error message to sysmessages.
|
1. What
are the basic functions for master, msdb, model, tempdb and resource databases?
- The master
database holds information for all databases located on the SQL Server
instance and is theglue that holds the engine together. Because SQL Server
cannot start without a functioning masterdatabase, you must administer
this database with care.
- The msdb
database stores information regarding database backups, SQL Agent
information, DTS packages, SQL Server jobs, and some replication
information such as for log shipping.
- The tempdb
holds temporary objects such as global and local temporary tables and
stored procedures.
- The model
is essentially a template database used in the creation of any new user
database created in the instance.
- The resoure
Database is a read-only database that contains all the system objects that
are included with SQL Server. SQL Server system objects, such as
sys.objects, are physically persisted in the Resource database, but they
logically appear in the sys schema of every database. The Resource
database does not contain user data or user metadata.
2. What
is Service Broker?
Service
Broker is a message-queuing technology in SQL Server that allows developers to
integrate SQL Server fully into distributed applications. Service Broker is
feature which provides facility to SQL Server to send an asynchronous,
transactional message. it allows a database to send a message to another
database without waiting for the response, so the application will continue to
function if the remote database is temporarily unavailable.
3. Where
SQL server user names and passwords are stored in SQL server?
They get
stored in System Catalog Views sys.server_principals and sys.sql_logins.
4. What
is Policy Management?
Policy
Management in SQL SERVER 2008 allows you to define and enforce policies for
configuring and managing SQL Server across the enterprise. Policy-Based
Management is configured in SQL Server Management Studio (SSMS). Navigate to
the Object Explorer and expand the Management node and the Policy Management
node; you will see the Policies, Conditions, and Facets nodes.
5. What
is Replication and Database Mirroring?
Database
mirroring can be used with replication to provide availability for the
publication database. Database mirroring involves two copies of a single
database that typically reside on different computers. At any given time, only
one copy of the database is currently available to clients which are known as
the principal database. Updates made by clients to the principal database are
applied on the other copy of the database, known as the mirror database.
Mirroring involves applying the transaction log from every insertion, update,
or deletion made on the principal database onto the mirror database.
6. What
are Sparse Columns?
A sparse
column is another tool used to reduce the amount of physical storage used in a
database. They are the ordinary columns that have an optimized storage for null
values. Sparse columns reduce the space requirements for null values at the
cost of more overhead to retrieve nonnull values.
7. What
does TOP Operator Do?
The TOP
operator is used to specify the number of rows to be returned by a query. The
TOP operator has new addition in SQL SERVER 2008 that it accepts variables as
well as literal values and can be used with INSERT, UPDATE, and DELETES
statements.
8. What
is CTE?
CTE is an
abbreviation Common Table Expression. A Common Table Expression (CTE) is an
expression that can be thought of as a temporary result set which is defined
within the execution of a single SQL statement. A CTE is similar to a derived
table in that it is not stored as an object and lasts only for the duration of
the query.
9. What
is MERGE Statement?
MERGE is
a new feature that provides an efficient way to perform multiple DML operations.
In previous versions of SQL Server, we had to write separate statements to
INSERT, UPDATE, or DELETE data based on certain conditions, but now, using
MERGE statement we can include the logic of such data modifications in one
statement that even checks when the data is matched then just update it and
when unmatched then insert it. One of the most important advantages of MERGE
statement is all the data is read and processed only once.
10. What
is Filtered Index?
Filtered
Index is used to index a portion of rows in a table that means it applies
filter on INDEX which improves query performance, reduce index maintenance
costs, and reduce index storage costs compared with full-table indexes. When we
see an Index created with some where clause then that is actually a FILTERED
INDEX.
11. Which
are new data types introduced in SQL SERVER 2008?
- The
GEOMETRY Type: The GEOMETRY data type is a system .NET
common language runtime (CLR) data type in SQL Server. This type
represents data in a two-dimensional Euclidean coordinate system.
- The
GEOGRAPHY Type: The GEOGRAPHY datatype’s functions are the
same as with GEOMETRY. The difference between the two is that when you
specify GEOGRAPHY, you are usually specifying points in terms of latitude
and longitude.
- New
Date and Time Datatypes: SQL Server 2008 introduces four new datatypes
related to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2.
- DATE:
The new DATE type just stores the date itself. It is based on the
Gregorian calendar and handles years from 1 to 9999.
- TIME:
The new TIME (n) type stores time with a range of 00:00:00.0000000
through 23:59:59.9999999. The precision is allowed with this type. TIME
supports seconds down to 100 nanoseconds. The n in TIME (n) defines this
level of fractional second precision, from 0 to 7 digits of precision.
- The
DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zone-aware
version of a datetimedatatype. The name will appear less odd when you
consider what it really is: a date + a time + a time-zone offset. The
offset is based on how far behind or ahead you are from Coordinated
Universal Time (UTC) time.
- The
DATETIME2 Type: It is an extension of the datetime type in
earlier versions of SQL Server. This new datatype has a date range
covering dates from January 1 of year 1 through December 31 of year 9999.
This is a definite improvement over the 1753 lower boundary of the
datetimedatatype. DATETIME2 not only includes the larger date range, but
also has a timestamp and the same fractional precision that TIME type
provides
12. What
are the Advantages of using CTE?
- Using
CTE improves the readability and makes maintenance of complex queries
easy.
- The
query can be divided into separate, simple, logical building blocks which
can be then used to build more complex CTEs until final result set is
generated.
- CTE
can be defined in functions, stored procedures, triggers or even views.
- After
a CTE is defined, it can be used as a Table or a View and can SELECT,
INSERT, UPDATE or DELETE Data.
13. What
is CLR?
In SQL
Server 2008, SQL Server objects such as user-defined functions can be created
using such CLR languages. This CLR language support extends not only to
user-defined functions, but also to stored procedures and triggers. You can
develop such CLR add-ons to SQL Server using Visual Studio 2008.
14. What
are synonyms?
Synonyms
give you the ability to provide alternate names for database objects. You can
alias object names; for example, using the Employee table as Emp. You can also
shorten names. This is especially useful when dealing with three and four part
names; for example, shortening server.database.owner.object to object.
15. What
is LINQ?
Language
Integrated Query (LINQ) adds the ability to query objects using .NET languages.
The LINQ to SQL object/relational mapping (O/RM) framework provides the following
basic features:
- Tools
to create classes (usually called entities) mapped to database tables
- Compatibility
with LINQ's standard query operations
- The
DataContext class, with features such as entity record monitoring,
automatic SQL statement generation, record concurrency detection, and much
more
16. What
is Isolation Levels?
Transactions
specify an isolation level that defines the degree to which one transaction
must be isolated from resource or data modifications made by other
transactions. Isolation levels are described in terms of which concurrency
side-effects, such as dirty reads or phantom reads, are allowed.
Transaction
isolation levels control:
- Whether
locks are taken when data is read, and what type of locks are requested.
- How
long the read locks are held.
- Whether
a read operation referencing rows modified by another transaction:
- Blocks
until the exclusive lock on the row is freed.
- Retrieves
the committed version of the row that existed at the time the statement
or transaction started.
- Reads
the uncommitted data modification.
17. What
is use of EXCEPT Clause?
EXCEPT
clause is similar to MINUS operation in Oracle. The EXCEPT query and MINUS
query returns all rows in the first query that are not returned in the second
query. Each SQL statement within the EXCEPT query and MINUS query must have the
same number of fields in the result sets with similar data types.
18. How
would you handle error in SQL SERVER 2008?
SQL
Server now supports the use of TRY...CATCH con handling. TRY...CATCH lets us
build error handling at the level we need, in the way w to, by setting a region
where if any error occurs, it will break out of the region and head to an error
handler.
The basic structure is as follows:
BEGIN TRY
stmts..
END TRY
BEGIN CATCH
stmts..
END CATCH
19. What
is RAISEERROR?
RaiseError
generates an error message and initiates error processing for the session.
RAISERROR can either reference a user-defined message stored in the
sys.messagescatalog view or build a message dynamically. The message is
returned as a server error message to the calling application or to an
associated CATCH block of a TRY | CATCH construct.
20. How
to rebuild Master Database?
Master
database is system database and it contains information about running server's
configuration. When SQL Server 2005 is installed it usually creates master,
model, msdb, tempdb resource and distribution system database by default. Only
Master database is th one which is absolutely must have database. Without
Master database SQL Server cannot be started. This is the reason it is
extremely important to backup Master database.
To
rebuild the Master database, Run Setup.exe, verify, and repair a SQL Server
instance, and rebuild the system databases. This procedure is most often used
to rebuild the master database for a corrupted installation of SQL
Server.
21. What
is XML Datatype?
The xml
data type lets you store XML documents and fragments in a SQL Server database.
An XML fragment is an XML instance that is missing a single top-level element.
You can create columns and variables of the xml type and store XML instances in
them. The xml data type and associated methods help integrate XML into the
relational framework of S Server.
22. What
is Data Compression?
In SQL
SERVE 2008 Data Compression comes in two flavors:
- Row
Compression: Row compression changes the format of
physical storage of data. It minimize the metadata (column information,
length, offsets etc) associated with each record. Numeric data types and
fixed length strings are stored in variable-length storage format, just like
Varchar.
- Page
Compression: Page compression allows common data to be
shared between rows for a given page. Its uses the following techniques to
compress data:
- Row
compression.
- Prefix
Compression. For every column in a page duplicate prefixes are identified.
These prefixes are saved in compression information headers (CI) which
resides after page header. A reference number is assigned to these
prefixes and that reference number is replaced where ever those prefixes
are being used.
- Dictionary
Compression: Dictionary compression searches for duplicate
values throughout the page and stores them in CI. The main difference
between prefix and dictionary compression is that prefix is only
restricted to one column while dictionary is applicable to the complete page.
23. What
is Catalog Views?
Catalog
views return information that is used by the SQL Server Database Engine.
Catalog Views are the most general interface to the catalog metadata and
provide the most efficient way to obtain, transform, and present customized
forms of this information. All user- available catalog metadata is exposed
through catalog views.
24. What
is PIVOT and UNPIVOT?
A Pivot
Table can automatically sort, count, and total the data stored in one table
or spreadsheet and create a second table displaying the summarized data. The
PIVOT operator turns the values of a specified column into column names,
effectively rotating a table.
UNPIVOT table is reverse of PIVOT Table.
25. What
is Dirty Read ?
A dirty
read occurs when two operations say, read and write occurs together giving the
incorrect or unedited data. Suppose, A has changed a row, but has not committed
the changes. B reads the uncommitted data but his view of the data may be wrong
so that is Dirty Read.
26. What
is Aggregate Functions?
Aggregate
functions perform a calculation on a set of values and return a single value.
Aggregate functions ignore NULL values except COUNT function. HAVING clause is
used, along with GROUP BY, for filtering query using aggregate values.
Following functions are aggregate functions.
AVG, MIN CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR,
MAX. VARP
27. What
do you mean by Table Sample?
TABLESAMPLE
allows you to extract a sampling of rows from a table in the FROM clause. The
rows retrieved are random and they are not in any order. This sampling can be
based on a percentage of number of rows. You can use TABLESAMPLE when only a
sampling of rows is necessary for the application instead of a full result set.
28. What
is the difference between UNION and UNION ALL?
- UNIONThe
UNION command is used to select related information from two tables, much
like the JOIN command. However, when using the UNION command all selected
columns need to be of the same data type. With UNION, only distinct values
are selected.
- UNION
ALL The
UNION ALL command is equal to the UNION command, except that UNION ALL
selects all values.
The
difference between Union and Union all is that Union all will not eliminate
duplicate rows, instead it just pulls all rows from all tables fitting your
query specifics and combines them into a table.
29. What
is B-Tree?
The
database server uses a B-tree structure to organize index information. B-Tree
generally has following types of index pages or nodes:
- root
node: A
root node contains node pointers to branch nodes which can be only one.
- branch
node: A
branch node contains pointers to leaf nodes or other branch nodes which
can be two or more.
- leaf
nodes: A leaf node contains index items and
orizantal pointers to other leaf nodes which can be many.
1. When is the UPDATE_STATISTICS command used?
- When the processing of large data is done, this command
is used.
- Whenever large number of deletions, modification or copy takes place into the
tables, the indexes need to be updated to take care of these changes.
UPDATE_STATISTICS performs this job.
2. Differentiate between a HAVING CLAUSE and a WHERE
CLAUSE.
HAVING CLAUSE
- HAVING CLAUSE is used only with the SELECT statement.
- It is generally used in a GROUP BY clause in a query.
- If GROUP BY is not used, HAVING works like a WHERE clause.
WHERE Clause
- It is applied to each row before they become a part of
the GROUP BY function in a query.
Download SQL Server interview questions and answers
pdf
3. What do you understand by a view? What does the WITH
CHECK OPTION clause for a view do?
- A view is a virtual table that consists of fields from
one or more real tables.
- It is usually used to join multiple tables and get the data.
- The WITH CHECK OPTION for a view prevents any modification to the data that
does not confirm to the WHERE clause of the view definition.
- This allows the data belonging to the view to be updated through the view.
4. Explain query execution plan?
- The optimizer available in SQL Server optimizes the code
to be effectively executed.
- A query execution plan shows how this optimizer would run the query.
- Query execution plan can be viewed by :
- Using the Show Execution Plan option available in Query Analyzer,
- Displaying Estimated Execution Plan on the query dropdown menu,
- Use the SET SHOWPLAN_TEXT ON command before running a query and capturing the
execution plan event in a SQL Server Profiler trace.
5. What is the function of SQL Server Agent Windows
service?
- It is a Windows service which handles the tasks scheduled
within the SQL Server environment. These tasks are also called as job and are
stored with in SQL server. The jobs may run through a trigger, a predefined
schedule or on demand.
- This service is very useful in determining why a particular job did not run
as intended.
6. Comment on Transactions.
- Using transactions we can group all SQL commands into a
single unit.
- The transaction begins with some task and finishes only when all tasks within
it are over.
- The transaction gets over successfully only when all commands in it are
successfully over. Even if one command fails, the whole transaction fails.
- The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION
statements are used to work with transactions.
- A group of tasks starts with the begin statement.
- In case of any problem, the rollback command is executed to abort the
transaction.
- If all the tasks run successfully, all commands are executed through commit
statement.
7. Differentiate between a primary key and a unique key.
- By default, clustered index on the column are created by
the primary key whereas nonclustered index are created by unique key.
- Primary key doesn't allow NULLs, but unique key allows one NULL.
8. What is recursion? Is it possible for a stored
procedure to call itself or recursive stored procedure? How many
levels of SP nesting is possible?
Recursion is method of problem solving where the solution
is arrived at by repetitively applying the logic and solution to the subsets of
the problem.
Transact-SQL supports recursion. So, yes it is possible for
a stored procedure to call itself.
Stored procedures and managed code references can be nested
up to 32 levels.
9. What are the advantages of using Stored Procedures?
- They help in reducing the network traffic and latency
which in turn boosts application performance.
- They help in promoting code reuse.
- They provide better security to data.
- It is possible to encapsulate the logic using stored procedures. This allows
to change stored procedure code without affecting clients.
- It is possible to reuse stored procedure execution plans, which are cached in
SQL Server's memory. This reduces server overhead.
10. a.) What do you mean by an execution plan? Why is
it used? How would you view it?
a.) An execution plan can be called as a road map that
graphically or textually shows the data retrieval methods which have been
chosen by the SQL
Server query optimizer, for a stored procedure or ad- hoc query.
b.) It is used because it is a very useful tool for a developer to understand
the performance characteristics of a query or stored procedure.
c.) There exists an option called "Show Execution Plan" in Query
Analyzer. If this option is turned on, it will display query execution plan in
separate window when the query is run again.
11. You want to implement the following relationships
while designing tables. How would you do it?
a.) One-to-one
b.) One-to-many
c.) Many-to-many
a.) One-to-One relationship - can be implemented as a
single table and rarely as two tables with primary and foreign key
relationships.
b.) One-to-Many relationships - by splitting the data into two tables with
primary key and foreign key relationships.
c.) Many-to-Many - by using a junction table with the keys from both the tables
forming the composite primary key of the junction table.
12. Differentiate between DELETE and TRUNCATE.
- Truncate can not be rolled back while Delete can be.
- Truncate keeps the lock on table while Delete keeps the lock on each row.
- Truncate resets the counter of the Identity column while Delete doesn't do
so.
- Trigger is not fired in Truncate while it happens in Delete.
13. What are the properties of the Relational tables?
Relational tables have six properties:
1. Values are atomic.
2. Column values are of the same kind.
3. Each row is unique.
4. The sequence of columns is insignificant.
5. The sequence of rows is insignificant.
6. Each column must have a unique name.
14. Explain the following.
a.) COLLATION.
Collation is a type of sort order. There are mainly three types of sort orders,
namely:
i.) Dictionary case sensitive
ii.)Dictionary - case insensitive
iii.)Binary.
b.) Stored Procedure
- It is a set of T-SQL statements combined together to perform a single task
formed by combining many small tasks.
- When you actually run a Stored procedure, a set of statements is run.
15. What do you mean by ACID?
- ACID (Atomicity Consistency Isolation Durability) is a
quality sought after in a reliable database. Here's the relevance of each
quality:
- Atomicity is an all-or-none proposition.
- Consistency - it guarantees that your database is never left by a transaction
in a half-finished state.
- Isolation - it keeps transactions separated from each other until they’re
finished.
- Durability - it ensures that the database keeps a track of pending changes in
a way that the server can recover from an abnormal termination.
16. Explain the following:
a.) Dirty pages.
These are the buffer pages that contain modifications which have not been
written to disk.
b.) ETL - Extraction, Transformation, and Loading.
- It is the process of copying and cleaning data from heterogeneous sources.
- It is an important part of development projects for data warehousing and
business intelligence.
17. Differentiate between a Local and a Global temporary
table?
- A local temporary table exists only for the duration of a
connection or, if defined inside a compound statement, for the duration of the
compound statement.
- Global temporary tables (created with a double “##”) are visible to all
sessions.
- Global temporary tables are dropped when the session that created it ends,
and all other sessions have stopped referencing it.
18. Explain different types of Locks in SQL Server.
There are 3 kinds of locks in SQL Server
i.) Shared locks - they are used for operations which do not allow any change
or update of data. For e.g. SELECT.
ii.) Update locks - they are used when SQL Server wants to modify a page. The
update page lock is then promoted to an exclusive page lock before actually
making the changes.
iii.) Exclusive locks - they are used for the data modification operations. For
e.g. UPDATE, INSERT, or DELETE.
What are DMVs?
Dynamic management views (DMVs) and functions return server
state information that can be used to monitor the health of a server instance,
diagnose problems, and tune performance; that is, they let you see what is
going on inside SQL Server. They were introduced in SQL Server 2005 as an
alternative to system tables. One example is viewing operating system wait
statistics via this query:
SELECT * FROM sys.dm_os_wait_stats;
Another example is examining current sessions, much like
the sp_who2 command:
SELECT * FROM sys.dm_exec_sessions;
What are temp tables?
What is the difference between global and local temp tables?
Temporary tables are temporary storage structures. You may use
temporary tables as buckets to store data that you will manipulate before
arriving at a final format. The hash (#) character is used to declare a
temporary table as it is prepended to the table name. A single hash (#)
specifies a local temporary table.
CREATE TABLE #tempLocal( nameidint, fnamevarchar(50), lnamevarchar(50) )
Local temporary tables are available to the current
connection for the user, so they disappear when the user disconnects.
Global temporary tables may be created with double hashes
(##). These are available to all users via all connections, and they are
deleted only when all connections are closed.
CREATE TABLE ##tempGlobal( nameidint, fnamevarchar(50), lnamevarchar(50) )
Once created, these tables are used just like permanent
tables; they should be deleted when you are finished with them. Within SQL
Server, temporary tables are stored in the Temporary Tables folder of the
tempdb database.
How are transactions
used?
Transactions allow you to group SQL commands into a single
unit. The transaction begins with a certain task and ends when all tasks within
it are complete. The transaction completes successfully only if all commands
within it complete successfully. The whole thing fails if one command fails.
The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements
are used to work with transactions. A group of tasks starts with the begin
statement. If any problems occur, the rollback command is executed to abort. If
everything goes well, all commands are permanently executed via the commit
statement.
What is the difference
between a clustered and a nonclustered index?
A clustered index affects the way the rows of data in a
table are stored on disk. When a clustered index is used, rows are stored in
sequential order according to the index column value; for this reason, a table
can contain only one clustered index, which is usually used on the primary
index value.
A nonclustered index does not affect the way data is
physically stored; it creates a new object for the index and stores the
column(s) designated for indexing with a pointer back to the row containing the
indexed values.
You can think of a clustered index as a dictionary in
alphabetical order, and a nonclustered index as a book's index.
What are DBCC commands?
Basically, the Database Consistency Checker (DBCC) provides
a set of commands (many of which are undocumented) to maintain databases --
maintenance, validation, and status checks. The syntax is DBCC followed by the
command name. Here are three examples:
DBCC CHECKALLOC -- Check disk allocation consistency.
DBCC OPENTRAN -- Display information about recent
transactions.
DBCC HELP -- Display Help for DBCC commands.
What is the difference
between truncate and delete?
Truncate is a quick way to empty a table. It removes
everything without logging each row. Truncate will fail if there are foreign
key relationships on the table. Conversely, the delete command removes rows
from a table, while logging each deletion and triggering any delete triggers
that may be present.
What does the NOLOCK
query hint do?
Table hints allow you to override the default behavior of
the query optimizer for statements. They are specified in the FROM clause of
the statement. While overriding the query optimizer is not always suggested, it
can be useful when many users or processes are touching data. The NOLOCK query
hint is a good example because it allows you to read data regardless of who
else is working with the data; that is, it allows a dirty read of data -- you
read data no matter if other users are manipulating it. A hint like NOLOCK
increases concurrency with large data stores.
SELECT * FROM table_name (NOLOCK)
Microsoft advises against using NOLOCK, as it is being
replaced by the READUNCOMMITTED query hint. There
are lots more query hints with plenty of information online.
What is a CTE?
A common table expression (CTE) is a temporary named result
set that can be used within other statements like SELECT, INSERT, UPDATE, and
DELETE. It is not stored as an object and its lifetime is limited to the query.
It is defined using the WITH statement as the following example shows:
WITH ExampleCTE (id, fname, lname)
AS
(
SELECT id,
firstname, lastname FROM table
)
SELECT * FROM ExampleCTE
A CTE can be used in place of a view in some instances.
What is a view? What is
the WITH CHECK OPTION clause for a view?
A view is a virtual table that consists of
fields from one or more real tables. Views are often used to join multiple
tables or to control access to the underlying tables.
The WITH CHECK OPTION for a view prevents data
modifications (to the data) that do not confirm to the WHERE clause of the view
definition. This allows data to be updated via the view, but only if it belongs
in the view.
What is a query execution plan?
SQL Server has an optimizer that usually does a great job
of optimizing code for the most effective execution. A query execution plan is
the breakdown of how the optimizer will run (or ran) a query. There are several
ways to view a query execution plan. This includes using the Show Execution
Plan option within Query Analyzer; Display Estimated Execution Plan on the
query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a
query and capturing the execution plan event in a SQL Server Profiler trace.
What does the SQL Server Agent Windows service do?
SQL Server Agent is a Windows service that handles
scheduled tasks within the SQL Server environment (aka jobs). The jobs are
stored/defined within SQL Server, and they contain one or more steps that
define what happens when the job runs. These jobs may run on demand, as well as
via a trigger or predefined schedule. This service is very important when
determining why a certain job did not run as planned -- often it is as simple
as the SQL Server Agent service not running.
What is the default port number for SQL Server?
If enabled, the default instance of Microsoft SQL Server
listens on TCP port 1433. Named instances are configured for dynamic ports, so
an available port is chosen when SQL Server starts. When connecting to a named
instance through a firewall, configure the Database Engine to listen on a
specific port, so that the appropriate port can be opened in the firewall.
The list of possible questions is endless. I am sure these
questions will spawn debate and discussion.
What are triggers? How many triggers you can have on a
table? How to invoke a trigger on demand?
Triggers
are constructs in PL/SQL that need to be just created and associated with a
table. Once they are created, when the table associated with it gets updated
due to an UPDATE, INSERT or a DELETE, the triggers get implicitly fired
depending upon the instructions passed to them.
A
table can have up to 12 triggers defined on it.
Triggers
can't be invoked on demand. They get triggered when the associated INSERT,
DELETE or UPDATE is performed.
Triggers -
October 24, 2008 at 18:10 pm by RajmeetGhai
Describe triggers features and limitations.
Trigger
features:-
1.
Can execute a batch of SQL code for an insert, update or delete command is
executed
2. Business rules can be enforced on modification of data
Trigger
Limitations:-
1. Does not accept arguments or parameters
2. Cannot perform commit or rollback
3. Can cause table errors if poorly written
Syntax for viewing, dropping and disabling triggers
View
trigger:
A
trigger can be viewed by using sp_helptrigger syntax. This returns all the
triggers defined in a table.
Sp_helptriggertable_name
Drop
a trigger
Syntax: DROP TRIGGER Trigger_name
Disable
a trigger:-
Syntax: DISABLE TRIGGER [schema name] trigger name
ON [object, database or ALL server ]
Determine how to use the inserted and deleted pseudo
tables.
Inserted
and deleted pseudo tables contain the New and Old values of the data that
initiating the Trigger. These tables can be used for database maintenance and
dynamic modification to data. These tables can be examined by the trigger
itself. The tables themselves cannot be altered.
Explain how to apply cascading referential integrity in
place of triggers.
Cascading
referential integrity constraints are automatic updates and deletes on
dependant objects. They define a set of actions that SQL server may need to
take. The References clause of the CREATE TABLE and ALTER TABLE statements
support ON DELETE and ON UPDATE clauses:
·
[ON DELETE {NO ACTION} ]: If
an attempt to delete a row is made, with a key referenced by foreign keys in
existing rows in other tables, an error is raised and DELETE is rolled back.
·
[ON UPDATE {NO ACTION } ]:
If an attempt to update a row is made, with a key referenced by foreign keys in
existing rows in other tables, an error is raised and UPDATE is rolled back.
·
[ ON DELETE { CASCADE } ]:
If an attempt to delete a row is made, with a key referenced by foreign keys in
existing rows in other tables, all rows containing those foreign keys are also
deleted
·
[ ON UPDATE { CASCADE } ]:
If an attempt to update a row is made, with a key referenced by foreign keys in
existing rows in other tables, all rows containing those foreign keys are also
Updated.
NO
ACTION is default.
Explain trigger classes i.e. instead of and after
trigger.
Answer
INSTEAD OF: Cause the trigger to fire instead of executing the
triggering event or action. It prevents unnecessary changes to be made.
Example: Causes the trigger to fire instead of the update (action)
CREATE
TRIGGER Employee_update ON Employee INSTEAD OF UPDATE AS { TRIGGER Definition }
AFTER:
execute following the triggering action, such as an insert, update, or delete.
These triggers are fired a little late in the process.
Example: Causes the trigger to fire instead of the update (action)
CREATE
TRIGGER Employee_update ON Employee AFTER UPDATE AS { TRIGGER Definition }
What are the instances when triggers are appropriate?
Answer
·
When security is the top
most priority. i.e. to allow unauthorized access
·
When backups are essential
·
When Maintenance is desired.
Triggers can be fired when any error message is logged
·
Keeping the database
consistent.
·
Table Name : Employee
|
EMPLOYEE_ID
|
FIRST_NAME
|
LAST_NAME
|
SALARY
|
JOINING_DATE
|
DEPARTMENT
|
|
1
|
John
|
Abraham
|
1000000
|
01-JAN-13 12.00.00 AM
|
Banking
|
|
2
|
Michael
|
Clarke
|
800000
|
01-JAN-13 12.00.00 AM
|
Insurance
|
|
3
|
Roy
|
Thomas
|
700000
|
01-FEB-13 12.00.00 AM
|
Banking
|
|
4
|
Tom
|
Jose
|
600000
|
01-FEB-13 12.00.00 AM
|
Insurance
|
|
5
|
Jerry
|
Pinto
|
650000
|
01-FEB-13 12.00.00 AM
|
Insurance
|
|
6
|
Philip
|
Mathew
|
750000
|
01-JAN-13 12.00.00 AM
|
Services
|
|
7
|
TestName1
|
123
|
650000
|
01-JAN-13 12.00.00 AM
|
Services
|
|
8
|
TestName2
|
Lname%
|
600000
|
01-FEB-13 12.00.00 AM
|
Insurance
|
·
Table Name : Incentives
·
|
EMPLOYEE_REF_ID
|
INCENTIVE_DATE
|
INCENTIVE_AMOUNT
|
|
1
|
01-FEB-13
|
5000
|
|
2
|
01-FEB-13
|
3000
|
|
3
|
01-FEB-13
|
4000
|
|
1
|
01-JAN-13
|
4500
|
|
2
|
01-JAN-13
|
3500
|
·
·
SQL Queries Interview Questions and Answers on
"SQL Select" - Examples
·
1. Get all employee
details from the employee table
· Select * from employee
·
2. Get
First_Name,Last_Name from employee table
· Select first_name, Last_Name from employee
·
3. Get First_Name from
employee table using alias name “Employee Name”
· Select first_name Employee Name from employee
·
4. Get First_Name from
employee table in upper case
· Select upper(FIRST_NAME) from EMPLOYEE
·
5. Get First_Name from
employee table in lower case
· Select lower(FIRST_NAME) from EMPLOYEE
·
6. Get unique
DEPARTMENT from employee table
· select distinct DEPARTMENT from EMPLOYEE
·
7. Select first 3
characters of FIRST_NAME from EMPLOYEE
· Oracle Equivalent of SQL Server SUBSTRING is SUBSTR, Query : select substr(FIRST_NAME,0,3) from employee
· SQL Server Equivalent of Oracle SUBSTR is SUBSTRING, Query : select substring(FIRST_NAME,0,3) from employee
· MySQL Server Equivalent of Oracle SUBSTR is SUBSTRING. In MySQL start position is 1, Query : select substring(FIRST_NAME,1,3) from employee
·
8. Get position of 'o'
in name 'John' from employee table
· Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query : Select instr(FIRST_NAME,'o') from employee where first_name = 'John'
· SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query: Select CHARINDEX('o',FIRST_NAME,0) from employee where first_name = 'John'
· MySQL Server Equivalent of Oracle INSTR is LOCATE, Query: Select LOCATE('o',FIRST_NAME) from employee where first_name = 'John'
·
9. Get FIRST_NAME from
employee table after removing white spaces from right side
· select RTRIM(FIRST_NAME) from employee
·
10. Get FIRST_NAME from
employee table after removing white spaces from left side
· select LTRIM(FIRST_NAME) from employee
·
11. Get length of
FIRST_NAME from employee table
· Oracle,MYSQL Equivalent of SQL Server Len is Length , Query :select length(FIRST_NAME) from employee
· SQL Server Equivalent of Oracle,MYSQL Length is Len, Query :select len(FIRST_NAME) from employee
·
12. Get First_Name from
employee table after replacing 'o' with '$'
· select REPLACE(FIRST_NAME,'o','$') from employee
·
13. Get First_Name and
Last_Name as single column from employee table separated by a '_'
· Oracle Equivalent of MySQL concat is '||', Query : Select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE
· SQL Server Equivalent of MySQL concat is '+', Query : Select FIRST_NAME + '_' +LAST_NAME from EMPLOYEE
· MySQL Equivalent of Oracle '||' is concat, Query : Select concat(FIRST_NAME,'_',LAST_NAME) from EMPLOYEE
·
14. Get FIRST_NAME
,Joining year,Joining Month and Joining Date from employee table
· SQL Queries in Oracle, Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear , to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEE
· SQL Queries in SQL Server, select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING (convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE
· SQL Queries in MySQL, select year(joining_date),month(joining_date), DAY(joining_date) from EMPLOYEE
·
·
Database SQL Queries Interview Questions and answers on
"SQL Order By"
·
·
15. Get all employee
details from the employee table order by First_Name Ascending
·
·
Select * from employee
order by FIRST_NAME asc
·
16. Get all employee
details from the employee table order by First_Name descending
·
·
Select * from employee
order by FIRST_NAME desc
·
17. Get all employee
details from the employee table order by First_Name Ascending and Salary
descending
· Select * from employee order by FIRST_NAME asc,SALARYdesc
·
SQL Queries Interview Questions and Answers on
"SQL Where Condition" - Examples
·
18. Get employee
details from employee table whose employee name is “John”
· Select * from EMPLOYEE where FIRST_NAME = 'John'
·
19. Get employee
details from employee table whose employee name are “John” and “Roy”
· Select * from EMPLOYEE where FIRST_NAME in ('John','Roy')
·
20. Get employee
details from employee table whose employee name are not “John” and “Roy”
· Select * from EMPLOYEE where FIRST_NAME not in ('John','Roy')
·
SQL Queries Interview Questions and Answers on
"SQL Wild Card Search" - Examples
·
21. Get employee
details from employee table whose first name starts with 'J'
· Select * from EMPLOYEE where FIRST_NAME like 'J%'
·
22. Get employee
details from employee table whose first name contains 'o'
· Select * from EMPLOYEE where FIRST_NAME like '%o%'
·
23. Get employee
details from employee table whose first name ends with 'n'
· Select * from EMPLOYEE where FIRST_NAME like '%n'
·
SQL Queries Interview Questions and Answers on "SQL
Pattern Matching" - Examples
·
24. Get employee
details from employee table whose first name ends with 'n' and name contains 4
letters
· Select * from EMPLOYEE where FIRST_NAME like '___n' (Underscores)
·
25. Get employee
details from employee table whose first name starts with 'J' and name contains
4 letters
· Select * from EMPLOYEE where FIRST_NAME like 'J___' (Underscores)
·
26. Get employee
details from employee table whose Salary greater than 600000
· Select * from EMPLOYEE where Salary > 600000
·
27. Get employee
details from employee table whose Salary less than 800000
· Select * from EMPLOYEE where Salary < 800000
·
28. Get employee
details from employee table whose Salary between 500000 and 800000
· Select * from EMPLOYEE where Salary between 500000 and 800000
·
29. Get employee
details from employee table whose name is 'John' and 'Michael'
· Select * from EMPLOYEE where FIRST_NAME in ('John','Michael')
·
·
SQL Queries Interview Questions and Answers on
"SQL DATE Functions" - Examples
·
·
30. Get employee
details from employee table whose joining year is “2013”
·
·
SQL
Queries in Oracle, Select * from EMPLOYEE
where to_char(joining_date,'YYYY') = '2013'
SQL Queries in SQL Server,
Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103),7,4) =
'2013'
SQL Queries in MySQL, Select *
from EMPLOYEE where year(joining_date) = '2013'
·
31. Get employee
details from employee table whose joining month is “January”
· SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'MM') = '01' or Select * from EMPLOYEE where to_char(joining_date,'Mon') = 'Jan'
·
· SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,100),1,3) = 'Jan'
·
· SQL Queries in MySQL, Select * from EMPLOYEE where month(joining_date) = '01'
·
32. Get employee
details from employee table who joined before January 1st 2013
· SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE <to_date('01/01/2013','dd/mm/yyyy')
· SQL Queries in SQL Server (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date< '01/01/2013'
· SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date< '2013-01-01'
·
33. Get employee
details from employee table who joined after January 31st
· SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE >to_date('31/01/2013','dd/mm/yyyy')
· SQL Queries in SQL Server and MySQL (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date>'01/31/2013'
· SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date> '2013-01-31'
·
35. Get Joining Date
and Time from employee table
· SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyyhh:mi:ss') from EMPLOYEE
· SQL Queries in SQL Server, Select convert(varchar(19),joining_date,121) from EMPLOYEE
· SQL Queries in MySQL, Select CONVERT(DATE_FORMAT(joining_date,'%Y-%m-%d-%H:%i:00'),DATETIME) from EMPLOYEE
·
36. Get Joining
Date,Time including milliseconds from employee table
· SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyyHH:mi:ss.ff') from EMPLOYEE . Column Data Type should be “TimeStamp”
· SQL Queries in SQL Server, select convert(varchar,joining_date,121) from EMPLOYEE
· SQL Queries in MySQL, Select MICROSECOND(joining_date) from EMPLOYEE
·
37. Get difference
between JOINING_DATE and INCENTIVE_DATE from employee and incentives table
· Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
·
38. Get database date
· SQL Queries in Oracle, select sysdate from dual
· SQL Queries in SQL Server, select getdate()
· SQL Query in MySQL, select now()
·
·
SQL Queries Interview Questions and Answers on
"SQL Escape Characters" - Examples
·
·
39. Get names of
employees from employee table who has '%' in Last_Name. Tip : Escape character
for special characters in a query.
·
·
SQLQueries in Oracle,
Select FIRST_NAME from employee where Last_Name like '%?%%'
·
SQLQueries in SQL
Server, Select FIRST_NAME from employee where Last_Name like '%[%]%'
·
SQLQueries in
MySQL,Select FIRST_NAME from employee where Last_Name like '%\%%'
·
40. Get Last Name from
employee table after replacing special character with white space
· SQL Queries in Oracle, Select translate(LAST_NAME,'%',' ') from employee
·
· SQL Queries in SQL Server and MySQL, Select REPLACE(LAST_NAME,'%',' ') from employee
·
SQL Queries Interview Questions and Answers on
"SQL Group By Functions" - Examples
·
41. Get
department,total salary with respect to a department from employee table.
· Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by department
·
42. Get department,total
salary with respect to a department from employee table order by total salary
descending
· Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending
·
SQL Queries Interview Questions and Answers on "SQL
Mathematical Operations using Group By" - Examples
·
43. Get department,no
of employees in a department,total salary with respect to a department from
employee table order by total salary descending
· Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending
·
44. Get department wise
average salary from employee table order by salary ascending
· select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalaryasc
·
45. Get department wise
maximum salary from employee table order by salary ascending
· select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalaryasc
·
46. Get department wise
minimum salary from employee table order by salary ascending
· select DEPARTMENT,min(SALARY) MinSalary from employee group by DEPARTMENT order by MinSalaryasc
·
47. Select no of
employees joined with respect to year and month from employee table
· SQL Queries in Oracle, select to_char (JOINING_DATE,'YYYY') Join_Year,to_char (JOINING_DATE,'MM') Join_Month,count(*) Total_Emp from employee group by to_char (JOINING_DATE,'YYYY'),to_char(JOINING_DATE,'MM')
· SQL Queries in SQL Server, select datepart (YYYY,JOINING_DATE) Join_Year,datepart (MM,JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by datepart(YYYY,JOINING_DATE), datepart(MM,JOINING_DATE)
· SQL Queries in MySQL, select year (JOINING_DATE) Join_Year,month (JOINING_DATE) Join_Month,count(*) Total_Emp from employee group by year(JOINING_DATE), month(JOINING_DATE)
·
48. Select
department,total salary with respect to a department from employee table where
total salary greater than 800000 order by Total_Salary descending
· Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) > 800000 order by Total_Salarydesc
·
·
SQL Queries Interview Questions and Answers on "SQL
Joins" - Examples
·
·
49. Select first_name,
incentive amount from employee and incentives table for those employees who
have incentives
·
·
Select FIRST_NAME,INCENTIVE_AMOUNT
from employee a inner join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
·
50. Select first_name,
incentive amount from employee and incentives table for those employees who
have incentives and incentive amount greater than 3000
· Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT > 3000
·
51. Select first_name,
incentive amount from employee and incentives table for all employes even if
they didn't get incentives
· Select FIRST_NAME,INCENTIVE_AMOUNT from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
·
52. Select first_name,
incentive amount from employee and incentives table for all employees even if
they didn't get incentives and set incentive amount as 0 for those employees
who didn't get incentives.
· SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
· SQL Queries in SQL Server, Select FIRST_NAME, ISNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
· SQL Queries in MySQL, Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
·
53. Select first_name,
incentive amount from employee and incentives table for all employees who got
incentives using left join
· SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
· SQL Queries in SQL Server, Select FIRST_NAME, isnull(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
· SQL Queries in MySQL, Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
·
54. Select max
incentive with respect to employee from employee and incentives table using sub
query
· SQL Queries in Oracle, select DEPARTMENT,(select nvl(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID = EMPLOYEE_ID) Max_incentive from EMPLOYEE
· SQL Queries in SQL Server, select DEPARTMENT,(select ISNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID = EMPLOYEE_ID) Max_incentive from EMPLOYEE
· SQL Queries in SQL Server, select DEPARTMENT,(select IFNULL (max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID = EMPLOYEE_ID) Max_incentive from EMPLOYEE
·
Advanced SQL Queries Interview Questions and Answers on
"Top N Salary" - Examples
·
55. Select TOP 2 salary
from employee table
· SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum< 3
· SQL Queries in SQL Server, select top 2 * from employee order by salary desc
· SQL Queries in MySQL, select * from employee order by salary desc limit 2
·
56. Select TOP N salary
from employee table
· SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum< N + 1
· SQL Queries in SQL Server, select top N * from employee
· SQL Queries in MySQL, select * from employee order by salary desc limit N
·
57. Select 2nd Highest
salary from employee table
· SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum< 3)
· SQL Queries in SQL Server, select min(SALARY) from (select top 2 * from employee) a
· SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit 2) a
·
58. Select Nth Highest
salary from employee table
· SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum< N + 1)
· SQL Queries in SQL Server, select min(SALARY) from (select top N * from employee) a
· SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit N) a
·
SQL Queries Interview Questions and Answers on
"SQL Union" - Examples
·
59. Select
First_Name,LAST_NAME from employee table as separate rows
· select FIRST_NAME from EMPLOYEE union select LAST_NAME from EMPLOYEE
·
60. What is the
difference between UNION and UNION ALL ?
· Both UNION and UNION ALL is used to select information from structurally similar tables. That means corresponding columns specified in the union should have same data type. For example, in the above query, if FIRST_NAME is DOUBLE and LAST_NAME is STRING above query wont work. Since the data type of both the columns are VARCHAR, union is made possible. Difference between UNION and UNION ALL is that , UNION query return only distinct values.
·
·
"Advanced SQL Queries
Interview Questions and Answers"
·
·
61. Select employee details
from employee table if data exists in incentive table ?
·
·
select * from EMPLOYEE where
exists (select * from INCENTIVES)
·
Explanation : Here exists statement helps us to do the job of If statement.
Main query will get executed if the sub query returns at least one row. So we
can consider the sub query as "If condition" and the main query as
"code block" inside the If condition. We can use any SQL commands
(Joins, Group By , having etc) in sub query. This command will be useful in
queries which need to detect an event and do some activity.
·
62. How to fetch data
that are common in two query results ?
· select * from EMPLOYEE where EMPLOYEE_ID INTERSECT select * from EMPLOYEE where EMPLOYEE_ID < 4
· Explanation : Here INTERSECT command is used to fetch data that are common in 2 queries. In this example, we had taken EMPLOYEE table in both the queries.We can apply INTERSECT command on different tables. The result of the above query will return employee details of "ROY" because, employee id of ROY is 3, and both query results have the information about ROY.
·
63. Get Employee ID's
of those employees who didn't receive incentives without using sub
query ?
· select EMPLOYEE_ID from EMPLOYEE
· MINUS
· select EMPLOYEE_REF_ID from INCENTIVES
·
· Explanation : To filter out certain information we use MINUS command. What MINUS Command odes is that, it returns all the results from the first query, that are not part of the second query. In our example, first three employees received the incentives. So query will return employee id's 4 to 8.
·
64. Select 20 % of
salary from John , 10% of Salary for Roy and for other 15 % of salary from
employee table
· SELECT FIRST_NAME, CASE FIRST_NAME WHEN 'John' THEN SALARY * .2 WHEN 'Roy' THEN SALARY * .10 ELSE SALARY * .15 END "Deduced_Amount" FROM EMPLOYEE
·
· Explanation : Here we are using SQL CASE statement to achieve the desired results. After case statement, we had to specify the column on which filtering is applied. In our case it is "FIRST_NAME". And in then condition, specify the name of filter like John, Roy etc. To handle conditions outside our filter, use else block where every one other than John and Roy enters.
·
65. Select Banking as
'Bank Dept', Insurance as 'Insurance Dept' and Services as 'Services Dept' from
employee table
· SQL Queries in Oracle, SELECT distinct DECODE (DEPARTMENT, 'Banking', 'Bank Dept', 'Insurance', 'Insurance Dept', 'Services', 'Services Dept') FROM EMPLOYEE
· SQL Queries in SQL Server and MySQL, SELECT case DEPARTMENT when 'Banking' then 'Bank Dept' when 'Insurance' then 'Insurance Dept' when 'Services' then 'Services Dept' end FROM EMPLOYEE
Explanation : Here DECODE keyword is used to specify the alias name. In oracle we had specify, Column Name followed by Actual Name and Alias Name as arguments. In SQL Server and MySQL, we can use the earlier switch case statements for alias names.
·
66. Delete employee
data from employee table who got incentives in incentive table
· delete from EMPLOYEE where EMPLOYEE_ID in (select EMPLOYEE_REF_ID from INCENTIVES)
Explanation : Trick about this question is that we can't delete data from a table based on some condition in another table by joining them. Here to delete multiple entries from EMPLOYEE table, we need to use Subquery. Entries will get deleted based on the result of Subquery.
·
67. Insert into
employee table Last Name with " ' " (Single Quote - Special
Character)
· Tip - Use another single quote before special character
· Insert into employee (LAST_NAME) values ('Test''')
·
68. Select Last Name
from employee table which contain only numbers
· Select * from EMPLOYEE where lower(LAST_NAME) = upper(LAST_NAME)
·
Explanation : Here in order to achieve the desired result, we use ASCII property of the database. If we get results for a column using Lower and Upper commands, ASCII of both results will be same for numbers. If there is any alphabets in the column, results will differ.
·
69. Write a query
to rank employees based on their incentives for a month
· select FIRST_NAME,INCENTIVE_AMOUNT,DENSE_RANK() OVER (PARTITION BY INCENTIVE_DATE ORDER BY INCENTIVE_AMOUNT DESC) AS Rank from EMPLOYEE a, INCENTIVES b where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID
·
Explanation : Here in order to rank employees based on their rank for a month, DENSE_RANK keyword is used. Here partition by keyword helps us to sort the column with which filtering is done. Rank is provided to the column specified in the order by statement. The above query ranks employees with respect to their incentives for a given month.
·
·
70. Update incentive table
where employee name is 'John'
·
·
Explanation : Here we need to join Employee and Incentive Table for updating
the incentive amount. But for update statement joining query wont work. We need
to use sub query to update the data in the incentive table. SQL Query is as
shown below.
·
update INCENTIVES set
INCENTIVE_AMOUNT = '9000' where EMPLOYEE_REF_ID =(select EMPLOYEE_ID from
EMPLOYEE where FIRST_NAME = 'John' )
·
SQL Queries Interview Questions and Answers on "SQL
Table Scripts" - Examples
·
71. Write create table
syntax for employee table
· Oracle -
·
· CREATE TABLE EMPLOYEE (
· EMPLOYEE_ID NUMBER,
· FIRST_NAME VARCHAR2(20 BYTE),
· LAST_NAME VARCHAR2(20 BYTE),
· SALARY FLOAT(126),
· JOINING_DATE TIMESTAMP (6) DEFAULT sysdate,
· DEPARTMENT VARCHAR2(30 BYTE) )
·
· SQL Server -
·
· CREATE TABLE EMPLOYEE(
· EMPLOYEE_ID int NOT NULL,
· FIRST_NAME varchar(50) NULL,
· LAST_NAME varchar(50) NULL,
· SALARY decimal(18, 0) NULL,
· JOINING_DATE datetime2(7) default getdate(),
· DEPARTMENT varchar(50) NULL)
·
72. Write syntax to
delete table employee
· DROP table employee;
·
73. Write syntax to set
EMPLOYEE_ID as primary key in employee table
· ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)
·
74. Write syntax to set
2 fields(EMPLOYEE_ID,FIRST_NAME) as primary key in employee table
· ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID,FIRST_NAME)
·
75. Write syntax to
drop primary key on employee table
· Alter TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;
·
76. Write Sql Syntax to
create EMPLOYEE_REF_ID in INCENTIVES table as foreign key with respect to
EMPLOYEE_ID in employee table
· ALTER TABLE INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (EMPLOYEE_REF_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
·
77. Write SQL to drop
foreign key on employee table
· ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK;
·
78. Write SQL to create
Orcale Sequence
· CREATE SEQUENCE EMPLOYEE_ID_SEQ START WITH 0 NOMAXVALUE MINVALUE 0 NOCYCLE NOCACHE NOORDER;
·
79. Write Sql syntax to
create Oracle Trigger before insert of each row in employee table
· CREATE OR REPLACE TRIGGER EMPLOYEE_ROW_ID_TRIGGER
· BEFORE INSERT ON EMPLOYEE FOR EACH ROW
· DECLARE
· seq_no number(12);
· BEGIN
· select EMPLOYEE_ID_SEQ.nextval into seq_no from dual ;
· :new EMPLOYEE_ID := seq_no;
· END;
· SHOW ERRORS;
·
80. Oracle Procedure81.
Oracle View
· An example oracle view script is given below
· create view Employee_Incentive as select FIRST_NAME,max(INCENTIVE_AMOUNT) INCENTIVE_AMOUNT from EMPLOYEE a, INCENTIVES b where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID group by FIRST_NAME
·
82. Oracle materialized
view - Daily Auto Refresh
· CREATE MATERIALIZED VIEW Employee_Incentive
· REFRESH COMPLETE
· START WITH SYSDATE
· NEXT SYSDATE + 1 AS
· select FIRST_NAME,INCENTIVE_DATE,INCENTIVE_AMOUNT from EMPLOYEE a, INCENTIVES b
· where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID
·
83. Oracle materialized
view - Fast Refresh on Commit
· Create materialized view log for fast refresh. Following materialized view script wont get executed if materialized view log doesn't exists
· CREATE MATERIALIZED VIEW MAT_Employee_Incentive_Refresh
· BUILD IMMEDIATE
· REFRESH FAST ON COMMIT AS
· select FIRST_NAME,max(INCENTIVE_AMOUNT) from EMPLOYEE a, INCENTIVES b
· where a.EMPLOYEE_ID = b.EMPLOYEE_REF_ID group by FIRST_NAME
·
84. What is SQL
Injection ?
· SQL Injection is one of the the techniques uses by hackers to hack a website by injecting SQL commands in data fields.
Q: When I delete any data from a table, does SQL
Server reduce the size of that table?
A: When data are deleted from any table, the SQL
Server does not reduce the size of the table right away, but marks those pages
as free pages, showing that they belong to the table. When new data are
inserted, they are put into those pages first. Once those pages are filled up,
SQL Server will allocate new pages. If you wait for sometime background process
de-allocates the pages and finally reducing the page size. Follow the
example below.
Q: When I delete any data from a table, does SQL
Server reduce the size of the B-Tree or change the level of the B-Tree since
there are lesser data?
A: No. It does very different behavior. Follow the
example.
USE tempdb
GO
-- Create Table FragTable
CREATE
TABLE FragTable(ID CHAR(800),
FirstNameCHAR(2000),
LastNameCHAR(3000),
City CHAR(2200))
GO
-- Create Clustered Index
CREATE
CLUSTERED INDEX [IX_FragTable_ID] ON FragTable
(
[ID] ASC
)
ON
[PRIMARY]
GO
-- Insert one Million Records
INSERT
INTO FragTable(ID,FirstName,LastName,City)
SELECT
TOP 100 ROW_NUMBER() OVER
(ORDER BY a.name) RowID,
'Bob',
CASE WHEN
ROW_NUMBER() OVER
(ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE
'Brown'
END,
CASE WHEN
ROW_NUMBER() OVER
(ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN
ROW_NUMBER() OVER
(ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN
ROW_NUMBER() OVER
(ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE
'Houston'
END
FROM sys.all_objects a
CROSS
JOIN
sys.all_objects b
GO
-- Check the spaces
sp_spaceused'FragTable'
GO
-- Check the percentages
SELECT
avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM
sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Delete all from table
DELETE
FROM FragTable
GO
-- Check the spaces
sp_spaceused'FragTable'
GO
-- Check the percentages
SELECT
avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM
sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Clean up
DROP
TABLE FragTable
GO
Now let us check the details. Here, we check the
index details before and after the delete statement.
Click on
Image to See Larger Image
It is very clear from the example that after
deleting data, the size of the table is not reduced; same goes with the
levels of the indexes- they are not resized by SQL Server. What really changes
is the leaf level pages where data are stored.
However if you wait for some time, and run once
again fragmentation script you will noticed that levels are still the same but
the page_count has been reduced to 1. This is clearly explained by Hrvojein
his comment correcting the blog post over . The
part of technical explanation is reproduced here.
“The behavior differs for Heaps and tables with a
clustered index. (The following applies to SQL Server 2008 SP1 and it might
differ on other versions)
Heaps:
BOL: “When rows are deleted from a heap the Database Engine may use row or page
locking for the operation. As a result, the pages made empty by the delete
operation remain allocated to the heap. When empty pages are not deallocated,
the associated space cannot be reused by other objects in the database.”
While it may appear that the pages are not deallocated due to you example set
size, try increasing the number of inserted rows and you will see that the
table size gets reduced to around 25MB. The number of index pages reduces
accordingly. (Levels do not apply to heaps)
If on the other hand you put a TABLOCK hint in the delete statement all pages
will get deallocated.
Tables with a Clustered Index:
Deleting from a table with a clustered index will deallocate deleted pages
regardless of the TABLOCK hint. The difference is that without the hint it will
happen by a background clean up process and therefore might not be visible
immediately after the delete. Try waiting for a few seconds and check space
used. TABLOCK will make this operation synchronous.
Number index levels are kept the same, BUT the size of ALL levels gets reduced
and not just the leaf levels.
Reducing the size of the heap or the levels of
indexes in the B-Tree can be achieved in several ways: if the table is empty
(and other constraints satisfied) TRUNCATE TABLE will do the work. Other
methods are rebuilding the table/clustered index.” Read original comment.
