Oracle Concepts and Architecture Database Structures.

1. What are the components of Physical database structure of Oracle Database?.

ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one or
more Control files.

2. What are the components of Logical database structure of ORACLE database?

Tablespaces and the Database's Schema Objects.

3. What is a Tablespace?

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical
structures together.

4. What is SYSTEM tablespace and When is it Created?

Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is
created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

5. Explain the relationship among Database, Tablespace and Data file.

Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each
tablespace.

6. What is schema?

A schema is collection of database objects of a User.

7. What are Schema Objects ?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables,
views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages anddatabase links.

8. Can objects of the same Schema reside in different tablespaces.?
Yes.

9. Can a Tablespace hold objects from different Schemes ?
Yes.

10. what is Table ?
A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible
data. Table data is stored in rows and columns.

11. What is a View ?

A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the
columns and rows of the table(s) the view uses.)

12. Do View contain Data ?

Views do not contain or store data.

13. Can a View based on another View ?

Yes.

14. What are the advantages of Views ?

Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
Hide data complexity.
Simplify commands for the user.
Present the data in a different perpecetive from that of the base table.
Store complex queries.

15. What is a Sequence ?

A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

16. What is a Synonym ?

A synonym is an alias for a table, view, sequence or program unit.

17. What are the type of Synonyms?

There are two types of Synonyms Private and Public.

18. What is a Private Synonyms ?

A Private Synonyms can be accessed only by the owner.

19. What is a Public Synonyms ?

A Public synonyms can be accessed by any user on the database.

20. What are synonyms used for ?

Synonyms are used to : Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables,views or program units of a remote database.
Simplify the SQL statements for database users.


21. What is an Index ?

An Index is an optional structure associated with a table to have direct access to rows,which can be created to increase
the performance of data retrieval. Index can be created on one or more columns of a table.

22. How are Indexes Update ?

Indexes are automatically maintained and used by ORACLE. Changes to table data are automatically incorporated into all
relevant indexes.

23. What are Clusters ?

Clusters are groups of one or more tables physically stores together to share common columns and are often used
together.

24. What is cluster Key ?

The related columns of the tables in a cluster is called the Cluster Key.

25. What is Index Cluster ?

A Cluster with an index on the Cluster Key.

26. What is Hash Cluster ?

A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows
with the same hash key value are stores together on disk.

27. When can Hash Cluster used ?

Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster
key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

28. What is Database Link ?

A database link is a named object that describes a "path" from one database to another.

29. What are the types of Database Links ?

Private Database Link, Public Database Link & Network Database Link.

30. What is Private Database Link ?

Private database link is created on behalf of a specific user. A private database link can be used only when the owner
of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

31. What is Public Database Link ?

Public database link is created for the special user group PUBLIC. A public database link can be used when any user in
the associated database specifies a global object name in a SQL statement or object definition.

3
2. What is Network Database link ?

Network database link is created and managed by a network domain service. A network database link can be used when
any user of any database in the network specifies a global object name in a SQL statement or object definition.

33. What is Data Block ?

ORACLE database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical
database space on disk.

34. How to define Data Block size ?

A data block size is specified for each ORACLE database when the database is created. A database users and allocated
free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and cann't be changed latter.

35. What is Row Chaining ?

In Circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs ,
the data for the row is stored in a chain of data block (one or more) reserved for that segment.

36. What is an Extent ?

An Extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of
information.

37. What is a Segment ?

A segment is a set of extents allocated for a certain logical structure.

38. What are the different type of Segments ?

Data Segment, Index Segment, Rollback Segment and Temporary Segment.

39. What is a Data Segment ?

Each Non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each
cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

40. What is an Index Segment ?

Each Index has an Index segment that stores all of its data.

41. What is Rollback Segment ?

A Database contains one or more Rollback Segments to temporarily store "undo" information.

42. What are the uses of Rollback Segment ?

Rollback Segments are used :
To generate read-consistent database information during database recovery to rollback uncommitted transactions for users.

43. What is a Temporary Segment ?

Temporary segments are created by ORACLE when a SQL statement needs a temporary work area to complete
execution. When the statement finishes execution, the temporary segment extents are released to the system for future
use.

44. What is a Data File ?

Every ORACLE database has one or more physical data files. A database's data files contain all the database data. The
data of logical database structures such as tables and indexes is physically stored in the data files allocated for a
database.

45. What are the Characteristics of Data Files ?

A data file can be associated with only one database.Once created a data file can't change size.
One or more data files form a logical unit of database storage called a tablespace.

46. What is a Redo Log ?

The set of Redo Log files for a database is collectively known as the database's redo log.

47. What is the function of Redo Log ?

The Primary function of the redo log is to record all changes made to data.

48. What is the use of Redo Log Information ?

The Information in a redo log file is used only to recover the database from a system or media failure prevents database
data from being written to a database's data files.

49. What does a Control file Contain ?

A Control file records the physical structure of the database. It contains the following information.

Database Name
Names and locations of a database's files and redolog files.
Time stamp of database creation.

50. What is the use of Control File ?

When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files
that must be opened for database operation to proceed. It is also used in database recovery.

51. What is a Data Dictionary ?

The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the
database.
It stores information about both the logical and physical structure of the database, the valid users of an ORACLE
database, integrity constraints defined for tables in the database and space allocated for a schema object and how much of
it is being used.

52. What is an Integrity Constrains ?

An integrity constraint is a declarative way to define a business rule for a column of a table.

53. Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint ?
No.

54. Describe the different type of Integrity Constraints supported by ORACLE ?
NOT NULL Constraint - Disallows NULLs in a table's column.
UNIQUE Constraint - Disallows duplicate values in a column or set of columns.
PRIMARY KEY Constraint - Disallows duplicate values and NULLs in a column or set of columns.
FOREIGN KEY Constrain - Require each value in a column or set of columns match a value in a related table's UNIQUE or
PRIMARY KEY.
CHECK Constraint - Disallows values that do not satisfy the logical expression of the constraint.

55. What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
A column defined as UNIQUE can contain NULLs while a column defined as PRIMARY KEY can't contain Nulls.

56. Describe Referential Integrity ?

A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for
the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced
value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on
dependent data as a result of any action on referenced data.

57. What are the Referential actions supported by FOREIGN KEY integrity constraint ?

UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data.

DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

58. What is self-referential integrity constraint ?
If a foreign key reference a parent key of the same table is called self-referential integrity constraint.

59. What are the Limitations of a CHECK Constraint ?

The condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't
contain subqueries, sequence, the SYSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or
ROWNUM.

60. What is the maximum number of CHECK constraints that can be defined on a column ?
No Limit.

SYSTEM ARCHITECTURE :

61. What constitute an ORACLE Instance ?
SGA and ORACLE background processes constitute an ORACLE instance. (or) Combination of memory structure and
background process.

62. What is SGA ?
The System Global Area (SGA) is a shared memory region allocated by ORACLE that contains data and control information for
one ORACLE instance.

63. What are the components of SGA ?
Database buffers, Redo Log Buffer the Shared Pool and Cursors.

64. What do Database Buffers contain ?

Database buffers store the most recently used blocks of database data. It can also contain modified data that has not yet
been permanently written to disk.

65. What do Redo Log Buffers contain ?
Redo Log Buffer stores redo entries a log of changes made to the database.

66. What is Shared Pool ?
Shared Pool is a portion of the SGA that contains shared memory constructs such as shared SQL areas.

67. What is Shared SQL Area ?
A Shared SQL area is required to process every unique SQL statement submitted to a database and contains
information such as the parse tree and execution plan for the corresponding statement.

68. What is Cursor ?
A Cursor is a handle ( a name or pointer) for the memory associated with a specific statement.

69. What is PGA ?
Program Global Area (PGA) is a memory buffer that contains data and control information for a server process.

70. What is User Process ?
A user process is created and maintained to execute the software code of an application program. It is a shadow
process created automatically to facilitate communication between the user and the server process.

71. What is Server Process ?
Server Process handle requests from connected user process. A server process is in charge of communicating with the
user process and interacting with ORACLE carry out requests of the associated user process.

72. What are the two types of Server Configurations ?
Dedicated Server Configuration and Multi-threaded Server Configuration.

73. What is Dedicated Server Configuration ?
In a Dedicated Server Configuration a Server Process handles requests for a Single User Process.

74. What is a Multi-threaded Server Configuration ?
In a Multi-threaded Server Configuration many user processes share a group of server process.

75. What is a Parallel Server option in ORACLE ?
A configuration for loosely coupled systems where multiple instance share a single physical database is called Parallel Server.

76. Name the ORACLE Background Process ?
DBWR - Database Writer.
LGWR - Log Writer
CKPT - Check Point
SMON - System Monitor
PMON - Process Monitor
ARCH - Archiver
RECO - Recover
Dnnn - Dispatcher, and
LCKn - Lock
Snnn - Server.

77. What Does DBWR do ?
Database writer writes modified blocks from the database buffer cache to the data files.

78.When Does DBWR write to the database ?
DBWR writes when more data needs to be read into the SGA and too few database buffers are free. The least recently
used data is written to the data files first. DBWR also writes when CheckPoint occurs.

79. What does LGWR do ?
Log Writer (LGWR) writes redo log entries generated in the redo log buffer of the SGA to on-line Redo Log File.

80. When does LGWR write to the database ?
LGWR writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.

81. What is the function of checkpoint(CKPT)?
The Checkpoint (CKPT) process is responsible for signaling DBWR at checkpoints and updating all the data files and
control files of the database.

82. What are the functions of SMON ?
System Monitor (SMON) performs instance recovery at instance start-up. In a multiple instance system (one that uses the
Parallel Server), SMON of one instance can also perform instance recovery for other instance that have failed SMON also
cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and
instance recovery because of file-read or off-line errors. These transactions are eventually recovered by SMON when the
tablespace or file is brought back on-line SMON also coalesces free extents within the database to make free space
contiguous and easier to allocate.

83. What are functions of PMON ?
Process Monitor (PMON) performs process recovery when a user process fails PMON is responsible for cleaning up the
cache and Freeing resources that the process was using PMON also checks on dispatcher and server processes and
restarts them if they have failed.

84. What is the function of ARCH ?
Archiver (ARCH) copies the on-line redo log files to archival storage when they are full. ARCH is active only when a
database's redo log is used in ARCHIVELOG mode.

85. What is function of RECO ?
RECOver (RECO) is used to resolve distributed transactions that are pending due to a network or system failure in a
distributed database. At timed intervals,the local RECO attempts to connect to remote databases and automatically
complete the commit or rollback of the local portion of any pending distributed transactions.

86. What is the function of Dispatcher (Dnnn) ?
Dispatcher (Dnnn) process is responsible for routing requests from connected user processes to available shared
server processes and returning the responses back to the appropriate user processes.

87. How many Dispatcher Processes are created ?
Atleast one Dispatcher process is created for every communication protocol in use.

88. What is the function of Lock (LCKn) Process ?
Lock (LCKn) are used for inter-instance locking when the ORACLE Parallel Server option is used.

89. What is the maximum number of Lock Processes used ?
Though a single LCK process is sufficient for most Parallel Server systems
upto Ten Locks (LCK0,....LCK9) are used for inter-instance locking.






DATA ACCESS

90. Define Transaction ?
A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.


91. When does a Transaction end ?
When it is committed or Rollbacked.

92. What does COMMIT do ?
COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL
statements of a transaction become visible to other user sessions transactions that start only after transaction is
committed.

93. What does ROLLBACK do ?
ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

94. What is SAVE POINT ?
For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which
can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed
from the current point in the transaction to a declared savepoint within the transaction.

95. What is Read-Only Transaction ?
A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to
the same point in time.

96. What is the function of Optimizer ?

The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

97. What is Execution Plan ?
The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.

98. What are the different approaches used by Optimizer in choosing an execution plan ?
Rule-based and Cost-based.

99. What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the
ALTER SESSION command hints in the statement.

100. What are the values that can be specified for OPTIMIZER MODE Parameter ?
COST and RULE.

101. Will the Optimizer always use COST-based approach if OPTIMIZER_MODE is set to "Cost'?

Presence of statistics in the data dictionary for atleast one of the tables accessed by the SQL statements is necessary
for the OPTIMIZER to use COST-based approach. Otherwise OPTIMIZER chooses RULE-based approach.

102. What is the effect of setting the value of OPTIMIZER_MODE to 'RULE' ?

This value causes the optimizer to choose the rule_based approach for all SQL statements issued to the instance
regardless of the presence of statistics.

103. What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command ?

CHOOSE,ALL_ROWS,FIRST_ROWS and RULE.

104. What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of
the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based
approach.

105. What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ?
This value causes the optimizer to the cost-based approach for all SQL statements in the session regardless of the
presence of statistics and to optimize with a goal of best throughput.

106. What is the effect of setting the value 'FIRST_ROWS' for OPTIMIZER_GOAL parameter of the ALTER SESSION
command ?
This value causes the optimizer to use the cost-based approach for all SQL statements in the session regardless of the
presence of statistics and to optimize with a goal of best response time.

107. What is the effect of setting the 'RULE' for OPTIMIER_GOAL parameter of the ALTER SESSION Command ?
This value causes the optimizer to choose the rule-based approach for all SQL statements in a session regardless of the
presence of statistics.

108. What is RULE-based approach to optimization ?
Choosing an executing planbased on the access paths available and the ranks of these access paths.

109. What is COST-based approach to optimization ?
Considering available access paths and determining the most efficient execution plan based on statistics in the data
dictionary for the tables accessed by the statement and their associated clusters and indexes.

ORACLE Interview Questions

1.What is an oracle instance?
2.What is a view?
3.What is referential integrity?
4.Name the data dictionary that stores user-defined
constraints?
5.What is a collection of privileges?
6.What is a snapshot?
7.What is a synonym?
8.What is a cursor?
9.What is a sequence?
10.What is a trigger?
11.What is an exception?
12.What is a partition of table?
13.What are pseudo-columns in SQL? Give examples?
14.What are the Data Control statements?
15.What is a schema?
16.What is a type?
17.What is a data model?
18.What is a relation?
19.Advantages of redo log files?
20.What is an Archiver?
21.What is a database buffer cache?
22.What are the 5 background processes in Oracle?
23.%type and %rowtype are attributes for?
24.What are the 2 steps in a two-phase commit?
25.What is a union, intersect, minus?
26.What is a join, explain the types of joins?
27.What is a co-related sub-query?
28.ODBC stands for?
29.Data-type used to work with integers is?
30.Describe data models?
31.Describe the Normalization principles?
32.What are the types of Normalization?
33.What is de-normalization?
SQL SERVER:-
34.What is normalization?
Ans: Well a relational database is basically composed
of tables that contain related data. So the Process of
organizing this data into tables is actually referred
to as normalization.
35.What is a Stored Procedure?
Ans: Its nothing but a set of T-SQL statements
combined to perform a single task of several tasks.
Its basically like a Macro so when you invoke the
Stored procedure, you actually run a set of
statements.
36.Can you give an example of Stored Procedure?
Ans: sp_helpdb , sp_who2, sp_renamedb are a set of
system defined stored procedures. We can also have
user defined stored procedures which can be called in
similar wayWhat is a trigger?
Ans: Triggers are basically used to implement business
rules. Triggers is also similar to stored procedures.
The difference is that it can be activated when data
is added or edited or deleted from a table in a
database.
37.What is a view?
Ans: If we have several tables in a db and we want to
view only specific columns from specific tables we can
go for views. It would also suffice the needs of
security some times allowing specfic users to see only
specific columns based on the permission that we can
configure on the view. Views also reduce the effort
that is required for writing queries to access
specific columns every time.
38.What is an Index?
Ans: When queries are run against a db, an index on
that db basically helps in the way the data is sorted
to process the query for faster and data retrievals
are much faster when we have an index.
39.What are the types of indexes available with SQL
Server?
Ans: There are basically two types of indexes that we
use with the SQL Server. Clustered and the
Non-Clustered.
40.What is the basic difference between clustered and
a non-clustered index?
Ans: The difference is that, Clustered index is unique
for any given table and we can have only one clustered
index on a table. The leaf level of a clustered index
is the actual data and the data is resorted in case of
clustered index. Whereas in case of non-clustered
index the leaf level is actually a pointer to the data
in rows so we can have as many non-clustered indexes
as we can on the db.
41.What are cursors?
Ans: Well cursors help us to do an operation on a set
of data that we retreive by commands such as Select
columns from table. For example : If we have duplicate
records in a table we can remove it by declaring a
cursor which would check the records during retreival
one by one and remove rows which have duplicate
values.
42.When do we use the UPDATE_STATISTICS command?
Ans: This command is basically used when we do a large
processing of data. If we do a large amount of
deletions any modification or Bulk Copy into the
tables, we need to basically update the indexes to
take these changes into account. UPDATE_STATISTICS
updates the indexes on these tables accordingly.
43.SQL Server runs on which TCP/IP port?
Ans: SQL Server runs on port 1433 but we can also
change it for better security.
44.From where can you change the default port?
Ans: From the Network Utility TCP/IP properties -->
Port number.both on client and the server.
45.Can you tell me the difference between DELETE &
TRUNCATE commands?
Ans: Delete command removes the rows from a table
based on the condition that we provide with a WHERE
clause. Truncate will actually remove all the rows
from a table and there will be no data in the table
after we run the truncate command.
46.Can we use Truncate command on a table which is
referenced by FOREIGN KEY?
Ans: No. We cannot use Truncate command on a table
with Foreign Key because of referential integrity.
47.What is the use of DBCC commands?
Ans: DBCC stands for database consistency checker. We
use these commands to check the consistency of the
databases, i.e., maintenance, validation task and
status checks.
48.Can you give me some DBCC command options?(Database
consistency check)
Ans: DBCC CHECKDB - Ensures that tables in the db and
the indexes are correctly linked.and DBCC CHECKALLOC -
To check that all pages in a db are correctly
allocated. DBCC SQLPERF - It gives report on current
usage of transaction log in percentage. DBCC
CHECKFILEGROUP - Checks all tables file group for any
damage.
49.What command do we use to rename a db?
Ans: sp_renamedb 'oldname' , 'newname'
50.Well sometimes sp_reanmedb may not work you know
because if some one is using the db it will not accept
this command so what do you think you can do in such
cases?
Ans: In such cases we can first bring to db to single
user using sp_dboptions and then we can rename that db
and then we can rerun the sp_dboptions command to
remove the single user mode.
51.What is the difference between a HAVING CLAUSE and
a WHERE CLAUSE?
Ans: Having Clause is basically used only with the
GROUP BY function in a query. WHERE Clause is applied
to each row before they are part of the GROUP BY
function in a query.
52.What do you mean by COLLATION?
Ans: Collation is basically the sort order. There are
three types of sort order Dictionary case sensitive,
Dictonary - case insensitive and Binary.
53.What is a Join in SQL Server?
Ans: Join actually puts data from two or more tables
into a single result set.
54.Can you explain the types of Joins that we can have
with Sql Server?
Ans: There are three types of joins: Inner Join, Outer
Join, Cross Join
55.When do you use SQL Profiler?
Ans: SQL Profiler utility allows us to basically track
connections to the SQL Server and also determine
activities such as which SQL Scripts are running,
failed jobs etc..
56.What is a Linked Server?
Ans: Linked Servers is a concept in SQL Server by
which we can add other SQL Server to a Group and query
both the SQL Server dbs using T-SQL Statements.
57.Can you link only other SQL Servers or any database
servers such as Oracle etc..
Ans: We can link any server provided we have the
OLE-DB provider from Microsoft to allow a link. For
Oracle we have a OLE-DB provider for oracle that
microsoft provides to add it as a linked server to the
sql server group.
58.Which stored procedure will you be running to add a
linked server?
Ans: sp_addlinkedserver, sp_addlinkedsrvlogin
59.What are the OS services that the SQL Server
installation adds?
Ans: MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC
(Distribution transac co-ordinator)
60.Can you explain the role of each service?
Ans: SQL SERVER - is for running the databases
SQL AGENT - is for automation such as Jobs, DB
Maintanance, Backups
DTC - Is for linking and connecting to other SQL
Servers
61.How do you troubleshoot SQL Server if its running
very slow?
Ans: First check the processor and memory usage to see
that processor is not above 80% utilization and memory
not above 40-45% utilization then check the disk
utilization using Performance Monitor, Secondly, use
SQL Profiler to check for the users and current SQL
activities and jobs running which might be a problem.
Third would be to run UPDATE_STATISTICS command to
update the indexes
62.Lets say due to N/W or Security issues client is
not able to connect to server or vice versa. How do
you troubleshoot?
Ans: First I will look to ensure that port settings
are proper on server and client Network utility for
connections. ODBC is properly configured at client end
for connection ------Makepipe & readpipe are utilities
to check for connection. Makepipe is run on Server and
readpipe on client to check for any connection issues.
63.What are the authentication modes in SQL Server?
Ans: Windows mode and mixed mode (SQL & Windows).
64.Where do you think the users names and passwords
will be stored in sql server?
Ans: They get stored in master db in the sysxlogins
table.
65.What is log shipping? Can we do logshipping with
SQL Server 7.0
Ans: Logshipping is a new feature of SQL Server 2000.
We should have two SQL Server - Enterprise Editions.
From Enterprise Manager we can configure the
logshipping. In logshipping the transactional log file
from one server is automatically updated into the
backup database on the other server. If one server
fails, the other server will have the same db and we
can use this as the DR (disaster recovery) plan.
66.Let us say the SQL Server crashed and you are
rebuilding the databases including the master database
what procedure to you follow?
Ans: For restoring the master db we have to stop the
SQL Server first and then from command line we can
type SQLSERVER –m which will basically bring it into
the maintenance mode after which we can restore the
master db.
67.Let us say master db itself has no backup. Now you
have to rebuild the db so what kind of action do you
take?
Ans: (I am not sure- but I think we have a command to
do it).
68.What is BCP? When do we use it?
Ans:BulkCopy is a tool used to copy huge amount of
data from tables and views. But it won't copy the
structures of the same.
69.What should we do to copy the tables, schema and
views from one SQL Server to another?
Ans:We have to write some DTS packages for it.

70.What are the different types of joins and what dies
each do?
71.What are the four main query statements?
72.What is a sub-query? When would you use one?
73.What is a NOLOCK?
74.What are three SQL keywords used to change or set
someone’s permissions?
75.What is the difference between HAVING clause and
the WHERE clause?
76.What is referential integrity? What are the
advantages of it?
77.What is database normalization?
78.Which command using Query Analyzer will give you
the version of SQL server and operating system?
79.Using query analyzer, name 3 ways you can get an
accurate count of the number of records in a table?
80.What is the purpose of using COLLATE in a query?
81.What is a trigger?
82.What is one of the first things you would do to
increase performance of a query? Example “ a query ran
yesterday took 30 seconds, today it takes 6 minutes”
83.What is an execution plan? When would you use it?
How would you view the execution plan?
84.What is the STUFF function and how does it differ
from the REPLACE function?
85.What does it mean to have quoted_identifier on?
What are the implications of having it off?
86.What are the different types of replication? How
are they used?
87.What is the difference between a local and a global
variable?
88.What is the difference between a Local temporary
table and a Global temporary table? How is each one
used?
89.What are cursors? Name four types of cursors and
when each one would be applied?
90.What is the purpose of UPDATE STATISTICS?
91.How do you use DBCC statements to monitor various
aspects of a SQL server installation?
92.How do you load large data to the SQL server
database?
93.How do you check the performance of a query and how
do you optimize it?
94.How do SQL server 2000 and XML linked? Can XML be
used to access data?
95.What is SQL server agent?
96.What is referential integrity and how is it
achieved?
97.What is indexing?
98.What is normalization and what are the different
forms of normalizations?
99.Difference between server.transfer and
server.execute method?
100.What id de-normalization and when do you do it?
101.What is better- 2nd Normal form or 3rd normal
form? Why?
102.Can we rewrite subqueries into simple select
statements or with joins? Example?
103.What is a function? Give some example?
104.What is a stored procedure?
105.Difference between Function and Procedure-in
general?
106.Difference between Function and Stored Procedure?
107.Can a stored procedure call another stored
procedure. If yes what level and can it be controlled?
108.Can a stored procedure call itself(recursive). If
yes what level and can it be controlled.?
109.How do you find the number of rows in a table?
110.Difference between Cluster and Non-cluster index?
111.What is a table called, if it does not have
neither Cluster nor Non-cluster Index?
112.Explain DBMS, RDBMS?
113.Explain basic SQL queries with SELECT from where
Order By, Group By-Having?
114.Explain the basic concepts of SQL server
architecture?
115.Explain couple pf features of SQL server
2000(Scalability, Availability, Integration with
internet, etc.)?
116.Explain fundamentals of Data ware housing & OLAP?
117.Explain the new features of SQL server 2000?
118.How do we upgrade from SQL Server 6.5 to 7.0 and
7.0 to 2000?
119.What is data integrity? Explain constraints?
120.Explain some DBCC commands?
121.Explain sp_configure commands, set commands?
122.Explain what are db_options used for?
123.What is the basic functions for master, msdb,
tempdb databases?
124.What is a job?
125.What are tasks?
126.What are primary keys and foreign keys?
127.How would you Update the rows which are divisible
by 10, given a set of numbers in column?
128.If a stored procedure is taking a table data type,
how it looks?
129.How m-m relationships are implemented?
130.How do you know which index a table is using?
131.How will oyu test the stored procedure taking two
parameters namely first name and last name returning
full name?
132.How do you find the error, how can you know the
number of rows effected by last SQL statement?
133.How can you get @@error and @@rowcount at the same
time?
134.What are sub-queries? Give example? In which case
sub-queries are not feasible?
135.What are the type of joins? When do we use Outer
and Self joins?
136.Which virtual table does a trigger use?
137.How do you measure the performance of a stored
procedure?
138.Questions regarding Raiseerror?
139.Questions on identity?
140.If there is failure during updation of certain
rows, what will be the state?