SQL SERVER Questions And Answers
1.
What is an
Entity?
The basic data item stored in database is called entity. An
entity can be any object, item, place, person, concept, or activity about which
data is stored.
2.
What is an
attribute?
An attribute is a property of an entity. It describes a
part of an entity. Entity could have one or more attributes.
3.
What is ER
diagram?
An Entity Relationship Diagram is diagrammatic
representation of the logical structure of a database system.
4.
Describe the
concept of keys.
Candidate
key
Foreign
key
5.
What are
joins?
Sometimes, data from multiple tables is to be displayed
using select statement. For this purpose, the tables in use must have a column
that is equated. This is termed as simple join or multiple join.
Sometimes, you might want to display all records from one
table and some from another. This type of join is called an outer join. An
outer join is only possible between two tables.
There are two types of outer join, namely left and right.
In a left outer join, all the rows of the first table named in the FROM clause
are displayed. In a right outer join all the rows from the second table
mentioned in the FROM clause are displayed. In either case, all the matching
rows from other table are displayed.
6.
What is a sub
query?
Sometimes the results of one query are dependant on the
results of another query. For this purpose one query is nested inside another
query, this is called as sub query.
7.
What are the
types of constraints?
You
can enforce data integrity by using constraints. Constraints are divided in to
five categories.
Primary
key constraint
A
primary key constraint is defined on a column are a set of columns whose values
uniquely identify the rows in a table. It cannot contain null values.
Unique
constraint
Unique
constraints are used to enforce uniqueness on non-primary key columns. It
allows null values but only one row can have a null value.
Foreign
Key constraint
You
can use the foreign key constraint to remove the inconsistency in two tables
when the data in the one table is dependant on the other table.
It
enforces domain integrity by restricting the values to be inserted in a column.
It is possible to define multiple check constraints on a single column. These
are evaluated in the order in which they are defined.
A
default constraint can be use to assign a constant value to a column and the
user need not insert values in to that column.
8.
What
is a rule?
The
required integrity can be enforced by specifying a check constraint or by
defining a rule. But check constraint modifies the table structure. The
constraint can there fore be implemented using rules with out changing the
table structure. This rule is applied before an insert or update statement .
9.
What are
indexes?
To speed up data retrieval indexes are used. Indexes also
enforce the uniqueness of rows.
Advantages
1.
Improves the speed of execution.
2.
Enforces uniqueness of data.
3.
Speeds up joins between tables.
Disadvantages
1.Takes disk space to store.
2.Data modification takes longer.
3.Takes time to create index.
Types
of indexes
Clustered
index
1.The data is physically sorted
2.One clustered index can be created per table, so you
should build it on attributes that have a high percentage of unique values and
that are not modified often.
Nonclustered
index
1.The physical order of the rows is not the same as the
index order.
2.There can be as many as 249 nonclustered indexes per
column.
10.
What are
views?
A view is a virtual table, which gives access to a subset
of columns from one or more tables. It is a query stored as an object in the
database. Hence a view is an object that derives it data from one or more
tables.
Advantages
1.A view serves as a security mechanism.
2.A view simplifies the usage of complex queries.
11.
What
are store procedures and its advantages?
12.
Explain
about BCP and DTS.
BCP
The transfer data from an external source to SQL Server is
performed using Bulk Copy Program utility. The external source is a flat file.
Data transfer from external source to SQL Server in BCP IN. The transfer of
data from SQL Server to external source is BCP OUT.
DTS
Data transformation services can be used to import and
export data between heterogeneous data sources and SQL Server. The external
data sources include Visual FoxPro, MS Excel, Paradox, MS Access, Dbase, and
text files.
13.
What are
transactions and their properties?
A transaction is a sequence of operations performed
together as a single unit of logical work. It has four properties.
Atomicity – it states that either all the data
modifications are performed or none are performed.
Consistency - it states that all the data is in a
consistent state after a successful completion of transaction.
Isolation – it states any data modification made my
concurrent transactions must be isolated from the modifications made by other
concurrent transaction.
Durability – it
states that ay change made by a completed transaction remains permanently in
the system.
14.
Locking
mechanism.
SQL
Server uses the concept of locking to ensure transactional integrity and
database consistency. Locking, by functionally prevents users from accessing
information being changed by other users. In a multi-user environment, Locking
prevents users from changing the same data at the same time. In SQL Server
locking is implemented automatically.
SQL
Server implements multi-granular locking, which allows transactions to lock
different types of resources at different levels. SQL Server can lock the
following resources.
RID
– is a row identifier that individually locks a row in a table.
It
is used for operations that do not change or update the data. This allows
concurrent transactions to read a resource and no other transaction can modify
the data on that resource.
This
lock is implemented when a transaction modify a row. Only one update lock is
allowed on a resource at a time.
Exclusive
locks
Intent
locks
Schema
Locks
15.
What is
deadlock?
A
dead lock is a situation in which two users (or transactions) have locks on
separate objects, and each user is waiting for a lock on the other’s object. It
usually occurs in a multi-user environment.
16.
What are
triggers?
A
trigger is a block of code that constitutes with a set of T-SQL
statements that are activated in response to certain actions. A trigger can
also be interpreted as a special kind of stored procedure that is executed
whenever an action, such as data modification, takes place.
·
It
can be associated with tables.
There
are three types of triggers
17.
What is
Normalization? Explain three normal forms?
Normalization is a scientific method of breaking down
complex table structures in to simple table structures by using certain rules.
Hence reduce redundancy in a table and eliminate inconsistency problems and
disk space usage.
First Normal Form
A table is said to be in 1NF when each cell of the table
contains precisely one value.
Functional dependency
If you have two attribute A and B, A is said to be
functionally dependant on B, if for each value of B, there is exactly one value
of A.
Second Normal Form
Identify the functionally dependent keys and place them in
a different table.
Third Normal Form
A relation is said to be 3NF when every non-key attribute
is functionally dependant only on the primary key.
18.
What are
Cursors and types of cursors?
A cursor is a work area called private SQL area, which
executes SQL statements and stores the results.
Cursor Types
So you can specify the four-cursor types
for Transact-SQL cursors. These cursors vary in their ability to detect changes
to the result set and in the resources, such as memory and space in tempdb,
they consume. The four API server cursor types supported by SQL Server are:
- Static cursors
- Dynamic cursors
- Forward-only cursors
- Key set-driven cursors
Static cursors detect few or no changes but
consume relatively few resources while scrolling, although they store the
entire cursor in tempdb.
25. What are
the advantages of SQL Server 7.0 over SQL Server 6.5?
·
Trigger Enhancements
·
Recursive
triggers
·
Data Transformation Services
·
Web Assistant Wizard
The Web Assistant Wizard has been
enhanced in SQL Server 7.0. In addition to exporting SQL Server data out to an
HTML file, it can also import tabular data from an HTML file into SQL Server,
and post to and read from HTTP and FTP locations.
·
SQL Server 7.0 supports
applications that span a broad range of platforms.
·
Gigabytes of memory, and a
terabyte or more of disk storage.
·
Stored Procedures
The stored procedure model has been
enhanced in SQL Server 7.0 to provide improved performance and increased
application flexibility. When a stored procedure is compiled and placed in the
procedure cache, all users of the stored procedure share that one copy of the
compiled plan.
·
The most notable feature is
update replication. Using update
replication, data replicated by SQL Server 7.0 can be modified at
multiple sites.
SQL-Server
Truncate & Delete
1.Truncate a table means data will be
lost, but structure of the table will be present in the database.
Syntax: Truncate table <table
name>;
2.If you drop a table then the structure
of the table also goes along with the data.
3.You can’t delete a table; you can
delete the data in a record wise manner.
Syntax:
delete from <table name> where <condition>
4.The difference between the truncate and
delete is you can delete the data at a stretch buy using the truncate, by using
the delete you can delete only one record at a time.
5.When you have truncated the table then
all the records will lose. Then immediately if you add a record to that table
the row number will be ‘1’ only.
Index:
- If
you have a clustered index on a column, then also you can assign a primary
key to that column?
- You
are having rowid already then why you are going for the index?
- How
many non-clustered indexes can be allowed in a table?
Syntax:
On
<Table name (<col1>,
<col2>, -------)>;
Syntax:
Rules
9.Rules will maintain the domain
integrity.
10.Is it possible to create a rule at
the time of creating a table?
11.Is it possible to drop a rule?
12.What is the difference between the
rule and check constraint?
If a rule is applied on a table it will
not apply to the already existing data in the table. The rule will be imposed
from now on the updation and insertion of data.
Syntax
Create rule <rule name> as <condition>
Keys
13.How many types of keys are there?
What is the importance of those keys?
15.What is the primary key and candidate
key and what are there advantages?
16.Is it possible to hide the database?
17. What is the maximum size of a row?
18.What is the maximum number of rows
can a table allowed? Then how many columns are allowed in a table?
19.What is normalization?
20.What is the redundancy?
21.What are the normalization forms?
22.What is the difference between the
DBMS/RDBMS?
23.What is replication?
Replication:
Definition:
“Replication is an important and
powerful technology for distributing data and the execution of stored
procedures across an enterprise. “
The replication technology allows you to
make duplicate copies of your data, move those copies to different locations,
and synchronize the data automatically so that all copies have the same data
values. Replication can be implemented between databases on the same server or
different servers connected by LANs, WANs, or the Internet.
24.What are the types of replication?
Types of the replications:
- Snapshot Replication
Option: Snapshot, with
immediate-updating Subscribers
- Transactional
Replication
Option: Transactional, with
immediate-updating Subscribers
- Merge Replication
Which
type of replication you choose for your application depends on your
requirements for data consistency, site autonomy, and network resources.
26.What is the sql using by the SQL-Server
and oracle?
SQL-Server: Transact-SQL
28.Is it possible to create a view with
out a base table?
Actually the answer is NO.
29.Index is the only second object,
which holds data in the database.
30. “MODIFY” is a keyword only it is not
a clause.
An error is given stating that null
values are found.
32.I am doing a transaction, I am having
four activities in those four fourth activity got some problem, then will be
the transaction committed or roll backed?
Automatically it will rollback, because
a transaction is said to be committed when all the activities of that
transaction is perfectly executed.
Joins
33.What is a join?
34.How many types of joins are there?
35.What is the difference between the
outer join and left outer join?
36.How
to make a copy of a table (if you have a table with some name X, is it possible
to create a table with a name Y by using the table X?
Explanation:
SQL server
Select
<columns> into <new table> from <old table>
Oracle
Write a query to display the details of
employees whose salary is more than the ‘BLAKE’ salary.
Sql> select B. * from EMP A, EMP B
38.What is schema?
Schema:
A
description of a database generated by the data definition language (DDL) of
the database management system (DBMS). In OLAP Services, a schema is a
description of multidimensional objects such as cubes, dimensions, and so
forth.
NO.
40.Is it possible to create a single
stored procedure on multiple tables?
YES.
What is SQL
and where does it come from?
Structured
Query Language (SQL) is a language that provides an interface to relational
database systems. IBM developed SQL in the 1970s for use in System R. SQL is a
de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced
SEQUEL.
In common
usage SQL also encompasses DML (Data Manipulation Language), for INSERTs,
UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and
modifying tables and other database structures.
The
development of SQL is governed by standards. A major revision to the SQL
standard was completed in 1992, called SQL2. SQL3 support object extensions and
will be (partially?) Implemented in Oracle8.
How can I
eliminate duplicates values in a table?
Choose one of
the following queries to identify or remove duplicate rows from a table:
Method 1:
SQL> DELETE FROM table_name A WHERE ROWID
> (
Method 2:
SQL> create table table_name2 as select
distinct * from table_name1;
Method 3:
SQL> Delete from where rowid not in(
Method 4:
SQL> delete from mytable t1
Note: If you
create an index on the joined fields in the inner loop, you for all intensive
purposes eliminate N^2 operations (no need to loop through the entire table on
each pass by a record).
How can I
generate primary key values for my table?
Create your
table with a NOT NULL column (say SEQNO). This column can now be populated with
unique values:
SQL>
UPDATE table_name SET seqno = ROWNUM;
SQL>
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
SQL>
UPDATE table_name SET seqno = sequence_name.NEXTVAL;
How can I get
the time difference between two date columns
select
floor((date1-date2)*24*60*60)/3600)
How does one
count different data values in a column?
select dept, sum( decode(sex,'M',1,0)) MALE,
How does one
select every n th row from a table?
Method 1:
Using a subquery
How does one
select the top n rows from a table?
SELECT *
How to
implement if-then-else in a select statement?
The Oracle decode
function acts like a procedural statement inside an SQL statement to return
different values or columns based on the values of other columns in the select
statement.
Example:
Note: The
decode function is not ANSI SQL and are rarely implemented in other RDBMS
offerings. It is one of the good things about Oracle, but use it sparingly if
portability is required.
How can one
dump/ examine the exact content of a database column?
SELECT DUMP(col1)
DUMP(COL1)
For this
example the type is 96, indicating CHAR, and the last byte in the column is 32,
which is the ASCII code for a space. This tells us that this column is
blank-padded.
Can one drop
a column from a table?
Oracle does
not provide a way to DROP a column.
1. update t1
set column_to_drop = NULL;
Can one
rename a column in a table?
No
How can I
change my Oracle password?
Issue the
following SQL command: ALTER USER <username> IDENTIFIED BY
<new_password>
Workaround
for snapshots on tables with LONG columns
You can use
the SQL*Plus COPY command instead of snapshots if you need to copy LONG and
LONG RAW variables from one location to another. Eg:
COPY TO
SCOTT/TIGER@REMOTE -
Note: If you
run Oracle8, convert your LONGs to LOBs, as it can be replicated.
1.
What is an
Entity?
The basic data item stored in database is called entity. An
entity can be any object, item, place, person, concept, or activity about which
data is stored.
2.
What is an
attribute?
An attribute is a property of an entity. It describes a
part of an entity. Entity could have one or more attributes.
3.
What is ER
diagram?
An Entity Relationship Diagram is diagrammatic
representation of the logical structure of a database system.
4.
Describe the
concept of keys.
Candidate
key
An
attribute that uniquely identifies a row is called candidate key. It is also
called das surrogate key.
Primary
key
A
candidate key that you choose to identify rows uniquely is called a primary
key.
Alternate
key
If
there are multiple candidate keys in a table, the candidate keys that are
chosen as primary key are called the alternate keys.
Composite
key
When
the key that uniquely identifies the rows of a table is made up of more than
one attribute, it is called as a composite key.
Foreign
key
Two
tables can be related using a common attribute. When a primary key of one table
is also available as an attribute in another related table it is called a
foreign key.
5.
What are
joins?
Sometimes, data from multiple tables is to be displayed
using select statement. For this purpose, the tables in use must have a column
that is equated. This is termed as simple join or multiple join.
Sometimes, you might want to display all records from one
table and some from another. This type of join is called an outer join. An
outer join is only possible between two tables.
There are two types of outer join, namely left and right.
In a left outer join, all the rows of the first table named in the FROM clause
are displayed. In a right outer join all the rows from the second table
mentioned in the FROM clause are displayed. In either case, all the matching
rows from other table are displayed.
6.
What is a sub
query?
Sometimes the results of one query are dependant on the
results of another query. For this purpose one query is nested inside another
query, this is called as sub query.
7.
What are the
types of constraints?
You
can enforce data integrity by using constraints. Constraints are divided in to
five categories.
Primary
key constraint
A
primary key constraint is defined on a column are a set of columns whose values
uniquely identify the rows in a table. It cannot contain null values.
Unique
constraint
Unique
constraints are used to enforce uniqueness on non-primary key columns. It
allows null values but only one row can have a null value.
Multiple
unique constraints can be created on a table.
Foreign
Key constraint
You
can use the foreign key constraint to remove the inconsistency in two tables
when the data in the one table is dependant on the other table.
Check
constraint
It
enforces domain integrity by restricting the values to be inserted in a column.
It is possible to define multiple check constraints on a single column. These
are evaluated in the order in which they are defined.
Default
Constraint
A
default constraint can be use to assign a constant value to a column and the
user need not insert values in to that column.
8.
What
is a rule?
The
required integrity can be enforced by specifying a check constraint or by
defining a rule. But check constraint modifies the table structure. The
constraint can there fore be implemented using rules with out changing the
table structure. This rule is applied before an insert or update statement .
A
rule must be bound to a column or a user-define d data type. This is done using
stored procedure sp_bindrule. Rules do not apply to data that has already been
inserted in the table. The existing values in tables do not have to meet the
criteria specified by the rule.
9.
What are
indexes?
To speed up data retrieval indexes are used. Indexes also
enforce the uniqueness of rows.
Advantages
1.
Improves the speed of execution.
2.
Enforces uniqueness of data.
3.
Speeds up joins between tables.
Disadvantages
1.Takes disk space to store.
2.Data modification takes longer.
3.Takes time to create index.
Types
of indexes
Clustered
index
1.The data is physically sorted
2.One clustered index can be created per table, so you
should build it on attributes that have a high percentage of unique values and
that are not modified often.
Nonclustered
index
1.The physical order of the rows is not the same as the
index order.
2.There can be as many as 249 nonclustered indexes per
column.
10.
What are
views?
A view is a virtual table, which gives access to a subset
of columns from one or more tables. It is a query stored as an object in the
database. Hence a view is an object that derives it data from one or more
tables.
Advantages
1.A view serves as a security mechanism.
2.A view simplifies the usage of complex queries.
11.
What
are store procedures and its advantages?
A
stored procedure is collection or batch of Transact-SQL statements and control
flow language that is stored under one name, and executed as single unit. It
helps in improving the performance of a query. It is a precompiled object. As
it is ready to execute no time is needed for parsing and compiling the
procedure.
Advantages
Improved
performance – Applications do not have to compile the procedure over and over
again.
Reduction
in network congestion – applications need not submit multiple SQL statements to
server for the purpose of processing.
Enhanced
accuracy – SQL statements included in a procedure are designed by experienced
programmers and are therefore more efficient, error free, and tested.
Better
security mechanism – users can be granted permission to execute a stored
procedure even if they do not own it.
Types
of stored procedures
1.User-defined
2.System
defined
These
are prefixed with sp_. These are for administrative purpose and are stored in
the database and are accessible to all users.
3.Temporary
These
are prefixed with #, stored in tempdb and are automatically dropped when
connection terminates.
4.Remote
These
are created and stored in remote servers and can accessed by users with
appropriate permissions.
5.Extended
These
are dlls that are executed outside SQL Server. They are prefixed by xp_.
12.
Explain
about BCP and DTS.
BCP
The transfer data from an external source to SQL Server is
performed using Bulk Copy Program utility. The external source is a flat file.
Data transfer from external source to SQL Server in BCP IN. The transfer of
data from SQL Server to external source is BCP OUT.
DTS
Data transformation services can be used to import and
export data between heterogeneous data sources and SQL Server. The external
data sources include Visual FoxPro, MS Excel, Paradox, MS Access, Dbase, and
text files.
13.
What are
transactions and their properties?
A transaction is a sequence of operations performed
together as a single unit of logical work. It has four properties.
Atomicity – it states that either all the data
modifications are performed or none are performed.
Consistency - it states that all the data is in a
consistent state after a successful completion of transaction.
Isolation – it states any data modification made my
concurrent transactions must be isolated from the modifications made by other
concurrent transaction.
Durability – it
states that ay change made by a completed transaction remains permanently in
the system.
14.
Locking
mechanism.
SQL
Server uses the concept of locking to ensure transactional integrity and
database consistency. Locking, by functionally prevents users from accessing
information being changed by other users. In a multi-user environment, Locking
prevents users from changing the same data at the same time. In SQL Server
locking is implemented automatically.
SQL
Server implements multi-granular locking, which allows transactions to lock
different types of resources at different levels. SQL Server can lock the
following resources.
RID
– is a row identifier that individually locks a row in a table.
Key
– is a rowlock with in an index.
Page
– is an 8k page or an index page.
Extent
– is a contiguous group of 8k data pages or index pages.
Table
– is the entire table, including all data and indexes.
Database
– is the complete database.
Shared
Locks
It
is used for operations that do not change or update the data. This allows
concurrent transactions to read a resource and no other transaction can modify
the data on that resource.
Update
Locks
This
lock is implemented when a transaction modify a row. Only one update lock is
allowed on a resource at a time.
Exclusive
locks
Intent
locks
Schema
Locks
15.
What is
deadlock?
A
dead lock is a situation in which two users (or transactions) have locks on
separate objects, and each user is waiting for a lock on the other’s object. It
usually occurs in a multi-user environment.
16.
What are
triggers?
A
trigger is a block of code that constitutes with a set of T-SQL
statements that are activated in response to certain actions. A trigger can
also be interpreted as a special kind of stored procedure that is executed
whenever an action, such as data modification, takes place.
A
trigger is always defined on a table, and is said to have fired whenever the
data in the underlying table is affected by any of the Data Manipulation
Language (DML) statements-INSERT, UPDATE, or DELETE. A trigger fires in
response to an event like insertion, updation, and deletion of data.
Triggers
help in maintaining consistent, reliable, and correct data in tables. They
enable the performance of complex actions and cascade these actions to other
dependant tables.
Characteristics
of a trigger:
·
It
can be associated with tables.
·
It
cannot be defined on temporary tables or views. However, it can reference
temporary tables and views.
·
Whenever
any data modification statement is issued then SQL Server fires it
automatically.
·
It
cannot be explicitly invoked or executed, as in the case of stored procedures.
·
Triggers
can be nested up to 16 levels. The nesting of triggers occurs when a trigger
performs an action that initiates another trigger.
·
It
prevents incorrect, unauthorized, and inconsistent changes in data.
·
It
cannot return data to the user.
There
are three types of triggers
1)
INSERT
2)
DELETE
3)
UPDATE
17.
What is
Normalization? Explain three normal forms?
Normalization is a scientific method of breaking down
complex table structures in to simple table structures by using certain rules.
Hence reduce redundancy in a table and eliminate inconsistency problems and
disk space usage.
First Normal Form
A table is said to be in 1NF when each cell of the table
contains precisely one value.
Functional dependency
If you have two attribute A and B, A is said to be
functionally dependant on B, if for each value of B, there is exactly one value
of A.
Second Normal Form
Identify the functionally dependent keys and place them in
a different table.
Third Normal Form
A relation is said to be 3NF when every non-key attribute
is functionally dependant only on the primary key.
18.
What are
Cursors and types of cursors?
A cursor is a work area called private SQL area, which
executes SQL statements and stores the results.
Cursor Types
So you can specify the four-cursor types
for Transact-SQL cursors. These cursors vary in their ability to detect changes
to the result set and in the resources, such as memory and space in tempdb,
they consume. The four API server cursor types supported by SQL Server are:
- Static cursors
- Dynamic cursors
- Forward-only cursors
- Key set-driven cursors
Static cursors detect few or no changes but
consume relatively few resources while scrolling, although they store the
entire cursor in tempdb.
Dynamic cursors detect all changes but
consume more resources while scrolling, although they make the lightest use of
tempdb.
Key set-driven cursors lie in between,
detecting most changes but at less expense than dynamic cursors.
Although the database API cursor models
consider a forward-only cursor to be a distinct type of cursor, SQL Server does
not. SQL Server considers both Forward only and scroll to be options that can
be applied to static, key set-driven, and dynamic cursors.
19.
What is Encryption option in SQL Server?
Encryption is a method for keeping
sensitive information confidential by changing data into an unreadable form.
Encryption ensures that data remains secure by keeping the information hidden
from everyone, even if the encrypted data is viewed directly. Decryption is the
process of changing encrypted data back into its original form and so
authorized users can view it.
20.
What are time-stamped data types?
It is a database-wide unique number. The
storage size is 8 bytes. A table can have only one timestamp column. The
value in the timestamp column is updated every time a row containing a
timestamp column is inserted or updated. This property makes a timestamp
column a poor candidate for keys, especially primary keys. Any update made to
the row changes the timestamp value, thereby changing the key value. If the
column is in a primary key, the old key value is no longer valid, and foreign
keys referencing the old value are no longer valid. If the table is referenced
in a dynamic cursor, all updates change the position of the rows in the cursor.
If the column is in an index key, all updates to the data row also generate
updates of the index.
21.
Correlated
queries.
In
correlated queries a sub query is executed for each row the parent query is
executed.
Ex:
“Select
e. * from EMP e where e.sal >(select Avg (Sal) from emp where e.deptno =
emp.deptno “
22.
What is OLAP and how it works with SQL
Server?
OLAP
Services is a new middle-tier server for online analytical processing (OLAP).
OLAP Services provides wizards, editors, and information to make OLAP
technology easier to use. OLAP Services supports various data and storage
models to help you create and maintain an OLAP system that meets your
organization’s needs.
25. What are
the advantages of SQL Server 7.0 over SQL Server 6.5?
·
Trigger Enhancements
·
Recursive
triggers
·
Multiple
triggers per INSERT, UPDATE, or DELETE statement
·
Data Transformation Services
·
Web Assistant Wizard
The Web Assistant Wizard has been
enhanced in SQL Server 7.0. In addition to exporting SQL Server data out to an
HTML file, it can also import tabular data from an HTML file into SQL Server,
and post to and read from HTTP and FTP locations.
·
Row-level
locking
SQL Server 7.0 supports complete
row-level locking on both data pages
and index pages.
·
SQL Server 7.0 supports
applications that span a broad range of platforms.
·
Gigabytes of memory, and a
terabyte or more of disk storage.
·
Stored Procedures
The stored procedure model has been
enhanced in SQL Server 7.0 to provide improved performance and increased
application flexibility. When a stored procedure is compiled and placed in the
procedure cache, all users of the stored procedure share that one copy of the
compiled plan.
·
The most notable feature is
update replication. Using update
replication, data replicated by SQL Server 7.0 can be modified at
multiple sites.
SQL-Server
Truncate & Delete
1.Truncate a table means data will be
lost, but structure of the table will be present in the database.
Syntax: Truncate table <table
name>;
2.If you drop a table then the structure
of the table also goes along with the data.
Syntax: drop table <table name>;
3.You can’t delete a table; you can
delete the data in a record wise manner.
Syntax:
delete from <table name> where <condition>
4.The difference between the truncate and
delete is you can delete the data at a stretch buy using the truncate, by using
the delete you can delete only one record at a time.
5.When you have truncated the table then
all the records will lose. Then immediately if you add a record to that table
the row number will be ‘1’ only.
Index:
- If
you have a clustered index on a column, then also you can assign a primary
key to that column?
- You
are having rowid already then why you are going for the index?
- How
many non-clustered indexes can be allowed in a table?
Syntax:
Create index <index name>
On
<Table name (<col1>,
<col2>, -------)>;
Syntax:
Drop index <index name>
Rules
9.Rules will maintain the domain
integrity.
10.Is it possible to create a rule at
the time of creating a table?
No.
11.Is it possible to drop a rule?
Yes.
12.What is the difference between the
rule and check constraint?
If a check constraint is applied to a
table it will enforce the constraint to previously existing data also. If that
data violates the constraint then constraint statement gives an error message.
If a rule is applied on a table it will
not apply to the already existing data in the table. The rule will be imposed
from now on the updation and insertion of data.
Syntax
Create rule <rule name> as <condition>
Sp_bindrule <rule name>, <object
name>
Drop rule <rule name>
Keys
13.How many types of keys are there?
What is the importance of those keys?
14.What
is the difference between the primary key and unique?
15.What is the primary key and candidate
key and what are there advantages?
16.Is it possible to hide the database?
17. What is the maximum size of a row?
18.What is the maximum number of rows
can a table allowed? Then how many columns are allowed in a table?
19.What is normalization?
20.What is the redundancy?
21.What are the normalization forms?
22.What is the difference between the
DBMS/RDBMS?
23.What is replication?
Replication:
Definition:
“Replication is an important and
powerful technology for distributing data and the execution of stored
procedures across an enterprise. “
The replication technology allows you to
make duplicate copies of your data, move those copies to different locations,
and synchronize the data automatically so that all copies have the same data
values. Replication can be implemented between databases on the same server or
different servers connected by LANs, WANs, or the Internet.
24.What are the types of replication?
Types of the replications:
- Snapshot Replication
Option: Snapshot, with
immediate-updating Subscribers
- Transactional
Replication
Option: Transactional, with
immediate-updating Subscribers
- Merge Replication
Which
type of replication you choose for your application depends on your
requirements for data consistency, site autonomy, and network resources.
25.Differences between the SQL-server
2000 and SQL-Server 7.0.
26.What is the sql using by the SQL-Server
and oracle?
SQL-Server: Transact-SQL
Oracle: ANSI-SQL
28.Is it possible to create a view with
out a base table?
Actually the answer is NO.
But this is possible by using the
“FORCED VIEWS”
Syntax:
Create force view XYZ
As
Select * from ZZZ;
29.Index is the only second object,
which holds data in the database.
30. “MODIFY” is a keyword only it is not
a clause.
31.What will happen if you add a not
null constraint on to a column if that column is already having the null values
and what is the status of those null values, which are already present?
An error is given stating that null
values are found.
32.I am doing a transaction, I am having
four activities in those four fourth activity got some problem, then will be
the transaction committed or roll backed?
Automatically it will rollback, because
a transaction is said to be committed when all the activities of that
transaction is perfectly executed.
Joins
33.What is a join?
34.How many types of joins are there?
35.What is the difference between the
outer join and left outer join?
36.How
to make a copy of a table (if you have a table with some name X, is it possible
to create a table with a name Y by using the table X?
Explanation:
SQL server
Select
<columns> into <new table> from <old table>
Oracle
Create table <table name> as
select * from <table name>
Write a query to display the details of
employees whose salary is more than the ‘BLAKE’ salary.
Sql> select B. * from EMP A, EMP B
Where A.ename=’BLAKE’
A. Sal < B.sal;
It is also called aliasing.
It is a self-joining concept.
38.What is schema?
Schema:
A
description of a database generated by the data definition language (DDL) of
the database management system (DBMS). In OLAP Services, a schema is a
description of multidimensional objects such as cubes, dimensions, and so
forth.
39.Is it possible to create a trigger on
multiple tables?
NO.
40.Is it possible to create a single
stored procedure on multiple tables?
YES.
What is SQL
and where does it come from?
Structured
Query Language (SQL) is a language that provides an interface to relational
database systems. IBM developed SQL in the 1970s for use in System R. SQL is a
de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced
SEQUEL.
In common
usage SQL also encompasses DML (Data Manipulation Language), for INSERTs,
UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and
modifying tables and other database structures.
The
development of SQL is governed by standards. A major revision to the SQL
standard was completed in 1992, called SQL2. SQL3 support object extensions and
will be (partially?) Implemented in Oracle8.
How can I
eliminate duplicates values in a table?
Choose one of
the following queries to identify or remove duplicate rows from a table:
Method 1:
SQL> DELETE FROM table_name A WHERE ROWID
> (
2
SELECT min(rowid) FROM table_name B
3
WHERE A.key_values = B.key_values);
Method 2:
SQL> create table table_name2 as select
distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
Method 3:
SQL> Delete from where rowid not in(
SQL>
select max(rowid) from
SQL>
group by );
Method 4:
SQL> delete from mytable t1
SQL> where exists (select 'x' from mytable t2
SQL> where t2.key_value1 =
t1.key_value1
SQL> and t2.key_value2 =
t1.key_value2
SQL> and t2.rowid != t1.rowid);
Note: If you
create an index on the joined fields in the inner loop, you for all intensive
purposes eliminate N^2 operations (no need to loop through the entire table on
each pass by a record).
How can I
generate primary key values for my table?
Create your
table with a NOT NULL column (say SEQNO). This column can now be populated with
unique values:
SQL>
UPDATE table_name SET seqno = ROWNUM;
or use a
sequences generator:
SQL>
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
SQL>
UPDATE table_name SET seqno = sequence_name.NEXTVAL;
Finally,
create a unique index on this column.
How can I get
the time difference between two date columns
select
floor((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference from ...
How does one
count different data values in a column?
select dept, sum( decode(sex,'M',1,0)) MALE,
sum( decode(sex,'F',1,0)) FEMALE,
count(decode(sex,'M',1,'F',1)) TOTAL
from
my_emp_table
group
by dept;
How does one
select every n th row from a table?
Method 1:
Using a subquery
SELECT *
FROM
emp
WHERE
(ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);
Method 2: Use
dynamic views (available from Oracle7.2):
SELECT *
FROM
( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE
MOD(temp.ROWNUM,4) = 0;
How does one
select the top n rows from a table?
SELECT *
FROM TableX a
WHERE 10 >= (SELECT COUNT(DISTINCT
maxcol)
FROM tableX b
WHERE b.maxcol >=
a.maxcol)
ORDER BY maxcol DESC;
How to
implement if-then-else in a select statement?
The Oracle decode
function acts like a procedural statement inside an SQL statement to return
different values or columns based on the values of other columns in the select
statement.
Example:
select decode(sex, 'M', 'Male',
'F', 'Female',
'Unknown'
)
from
employees;
Note: The
decode function is not ANSI SQL and are rarely implemented in other RDBMS
offerings. It is one of the good things about Oracle, but use it sparingly if
portability is required.
How can one
dump/ examine the exact content of a database column?
SELECT DUMP(col1)
FROM tab1
WHERE cond1 = val1;
DUMP(COL1)
-------------------------------------
Typ=96 Len=4: 65,66,67,32
For this
example the type is 96, indicating CHAR, and the last byte in the column is 32,
which is the ASCII code for a space. This tells us that this column is
blank-padded.
Can one drop
a column from a table?
Oracle does
not provide a way to DROP a column.
1. update t1
set column_to_drop = NULL;
rename t1 to t1_base;
create view t1 as select <specific
columns> from t1_base;
2. create
table t2 as select <specific columns> from t1;
drop table t1;
rename t2 to t1;
Can one
rename a column in a table?
No
1. rename t1
to t1_base;
create view t1 <column list with new
name> as select * from t1_base;
2. create
table t2 <column list with new name> as select * from t1;
drop table t1;
rename t2 to t1;
How can I
change my Oracle password?
Issue the
following SQL command: ALTER USER <username> IDENTIFIED BY
<new_password>
/
From Oracle8
you can just type "password" from SQL*Plus, or if you need to change
another user's password, type "password user_name".
Workaround
for snapshots on tables with LONG columns
You can use
the SQL*Plus COPY command instead of snapshots if you need to copy LONG and
LONG RAW variables from one location to another. Eg:
COPY TO
SCOTT/TIGER@REMOTE -
CREATE
IMAGE_TABLE USING -
SELECT IMAGE_NO, IMAGE -
FROM
IMAGES;
Note: If you
run Oracle8, convert your LONGs to LOBs, as it can be replicated.
0 comments: