Msc IT Last Year Paper Fully Solved

M. Sc. (Sem. I) (IT) Examination

January/February 2009

P-I03 DBMS & Database Administration

TIME: 3 Hours MAX. MARKS: 70

Q.1	Fill in the blanks		5
	(1)	Privileges are removed from a user with REVOKE command.
	(2)	ALERT.LOG file will gives the oracle instance status information.
	(3)	Control File and Redo Log file can be mirrored by oracle.
	(4)	SYS DBA role gives complete authority in oracle database.
	(5)	ROWID is an internal physical address for every row in every nonclustered table in the database.
Q.2	Answer briefly :		15
	(1)	Name three types of files that make up the oracle database.
	Ans	Control File, Redo Log File and Database File
	(2).	What is auditing used for ?
	Ans	Auditing is monitoring of selected user database actions. It is used to Investigate suspicious database activity. Gather Information about specific database activity.
	(3).	What is oracle SID?
	Ans	The Oracle System Identifier (SID) identifies the Oracle instance on the machine. It is usually set up as an ORACLE_SID symbol is used to name of the Oracle background processes and to identify the SGA area in memory.
	(4).	Name four types of segments.
	Ans	Table (Data Segment), Index, Cluster, Rollback Segment, Temporary Segment, Index Organized Table, Table Partition, Nested Table.
	(5).	What is mirrored-online redo log?
	Ans.	In an attempt to preserve the data within the online redo logs in much the same way as control files, Oracle introduces redo log groups. They enable redo logs to be mirrored across multiple disks. SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; Instead of having individual redo logs, each of which contains a distinct series of transactions, the redo logs are broken down into groups and members.
	(6).	Name different kinds of tuning at database level.
	Ans	Application Tuning. Database Tuning. I/O Tuning Memory Tuning Contention Tuning Operating System Tuning
	(7).	Which command is issued to force the checkpoint ?
	Ans	SQL> ALTER DATABASE SWITCH LOGFILE; SQL> ALTER DATABASE CHECKPOINT;
	(8).	What parameter will format the name of archived redo logs?
	Ans	SHOW PARAMETER LOG_ARCHIVE_START SQL> ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE;
	(9).	List various shutdown methods.
	Ans	Shutdown Normal (Default) Shutdown Transaction Shutdown Immediate Shutdown Abort
	(10).	What is log switch ?
	à	LGWR writes new copy of changed data from Redo Log buffer to Online Redo Log files. When Online Redo log File is Full LGWR begins writing into next redo log file. Moving from One Redo log file to next Redo log file for Writing is called Log Switch. It is Automatically occurs when Online Redo log file is Full. You can do it Force fully using following Command : SQL> Alter database switch logfile;
	(11).	What is UTLBSTAT and UTLESTAT?
	Ans.	Oracle provides tools that enable you to examine in detail what the Oracle RDBMS was doing during a specific period of time. They are the begin statistics utility (utlbstat) and the end statistics utility (utlestat). These scripts enable you to take a snapshot of how the instance was performing during an interval of time. They use the Oracle dynamic performance (V$) tables to gather information.
	(12).	What is tablespace?
	Ans	A tablespace is the name given to a group of one or more database files. When objects are created, you can specify in which tablespace they will occupy storage. This gives you control over where and how much storage is used. You can specify the amount of storage that users are allowed to use in each tablespace in the database. To Create a Tablespace : SQL> CREATE TABLESPACE T1 DATAFILE F:\DB1.DBS’ SIZE 10M;
	(13).	What is an Oracle Instance ?
	Ans	Oracle Instance is Combination of Memory Structures (SGA) and Background Processes. SGA : Shared Pool, Redo Log Buffer, Database Buffer Cache, Large Pool, Java Pool Background Processes : DBWn, LGWR, PMON,SMON, ARCHn, Checkpoint.
	(14).	Name the Three stage of Oracle Instance Startup?
	Ans.	STARTUP Nomount, Mount, Open, Force, Recover, Restrict
	(15).	What is profile ?
	Ans	The database profile is Oracle's attempt to enable the DBA to exercise some method of resource management upon the database. A profile is "a named set of resource limits." To Create a Profile SQL > CREATE PROFILE BOSS LIMIT ( FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2 IDLE_TIME 600 CONNECT_TIME 500 SESSIONS_PER_USER 5 );
Q.4	Answer in one word or one sentence :		10.
	(1).	Which tool is commonly used to create queries and execute them against SQL Server databases?
		Query Analyzer
	(2)	What are the three types of Transact-SQL statements that SQL Server supports?
		DDL, DCL, and DML
	(3).	What is Transact-SQL?
		Transact-SQL is a language that contains the commands used to administer instances of SQL Server; to create and manage all objects in an instance of SQL Server; and to insert, retrieve, modify, and delete data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by ISO and ANSI.
	(4).	What methods can you use to create a SQL Server database object?
		SQL Server provides several methods that you can use to create a database: the Transact-SQL CREATE DATABASE statement, the console tree in Enterprise Manager, and the Create Database wizard (which you can access through Enterprise Manager).
	(5).	Which statement should you use to delete all rows in a table without having the action logged?
		The TRUNCATE TABLE statement
	(6).	Which three types of cursor implementations does SQL Server support?
		Transact-SQL server cursors, API server cursors, and client cursors
	(7)	Name three common database tasks accomplished with triggers
		Maintaining running totals and other computed values; creating audit records; invoking external actions; and implementing complex data integrity
	(8)	What is the default sort order for an index key?
		An index key is sorted in ascending order unless you specify descending order (the DESC keyword).
	(9).	Name a SQL Server tool you can use to monitor current SQL Server activity.
		The Current Activity node of Enterprise Manager and SQL Profiler are two SQL Server tools that monitor current activity.
	(10).	What are several keywords that you can use in a select list?
		DISTINCT, TOP n, and AS

Implementing Triggers (Single Marks questions)

[1]. You have applied constraints, an INSTEAD OF trigger, and three AFTER triggers to a table. A colleague tells you that there is no way to control trigger order for the table. Is he correct? Why or why not?

He is incorrect. INSTEAD OF triggers always fire before constraints are processed. Following constraint processing, the AFTER triggers fire. Because there are three AFTER triggers, you can be sure about their execution order by using sp_settriggerorder to define the first and last trigger to execute.

[2]. You need to make sure that when a primary key is updated in one table, all foreign key references to it are also updated. How should you accomplish this task?

Configure cascading referential integrity to the foreign key constraints so that updates to the primary key are propagated to the other tables.

[3]. Name four instances when triggers are appropriate.

Triggers are appropriate in the following instances:

• If using declarative data integrity methods does not meet the functional needs of the application
• If changes must cascade through related tables in the database
• If the database is denormalized and requires an automated way to update redundant data contained in multiple tables
• If a value in one table must be validated against a non-identical value in another table
• If customized messages and complex error handling are required

[4]. When a trigger fires, how does it track the changes that have been made to the modified table?

An INSERT or UPDATE trigger creates the Inserted (pseudo) table in memory. The Inserted table contains any inserted or updated data. The UPDATE trigger also creates the Deleted (pseudo) table, which contains the original data. A DELETE trigger also creates a Deleted (pseudo) table in memory. The Deleted table contains any deleted data. The transaction isn't committed until the trigger completes. Thus, the trigger can roll back the transaction.

[5]. Name a table deletion event that does not fire a DELETE trigger.

TRUNCATE TABLE does not fire a DELETE trigger because the transaction isn't logged. Logging the transaction is critical for trigger functions, because without it, there is no way for the trigger to track changes and roll back the transaction if necessary.

[6]. Name a system stored procedure and a function used to view the properties of a trigger.

The sp_helptrigger system stored procedure shows the properties of one or all triggers applied to a table or view. The OBJECTPROPERTY function is used to determine the properties of database objects (such as triggers). For example, the following code returns 1 if a trigger named Trigger01 is an INSTEAD OF trigger:

SELECT OBJECTPROPERTY (OBJECT_ID(`trigger01'), `ExecIsInsteadOfTrigger')

[7]. Using Transact-SQL language, what are two methods to stop a trigger from running?

You can use the ALTER TABLE statement to disable a trigger. For example, to disable a trigger named Trigger01 that is applied to a table named Table01, type the following:

ALTER TABLE table01 DISABLE TRIGGER trigger01.

A second option is to delete the trigger from the table by using the DROP TRIGGER statement.

[8]. Write a (COLUMNS_UPDATED()) clause that detects whether columns 10 and 11 are updated.

IF ((SUBSTRING(COLUMNS_UPDATED(),2,1)=6))

 PRINT 'Both columns 10 and 11 were updated.'

[9]. Name three common database tasks accomplished with triggers.

Maintaining running totals and other computed values; creating audit records; invoking external actions; and implementing complex data integrity

[10]. What command can you use to prevent a trigger from displaying row count information to a calling application?

In the trigger, type the following:

SET NOCOUNT ON

There is no need to include SET NOCOUNT OFF before exiting the trigger, because system settings configured in a trigger are only in effect while the trigger is running.

[11]. What type of event creates both an Inserted and Deleted logical table?

An UPDATE event is the only type of event that creates both pseudo tables. The Inserted table contains the new value specified in the update, and the Deleted table contains the original value before the UPDATE runs.

[12]. Is it possible to instruct a trigger to display result sets and print messages?

Yes, it is possible to display result sets by using the SELECT statement and print messages to the screen by using the PRINT command. You shouldn't use SELECT and PRINT to return a result, however, unless you know that all applications that will modify tables in the database can handle the returned data.

Single Marks Questions of University Exam Msc(IT)-1st

[1]. What are the four primary properties that most SELECT statements describe in a result set?

Most SELECT statements describe the following four primary properties of a result set:

• The columns to be included in the result set
• The tables from which the result set data is retrieved
• The conditions that the rows in the source table must meet to qualify for the result set
• The ordering sequence of the rows in the result set

[2]. What are the main clauses of a SELECT statement?

The main clauses of a SELECT statement can be summarized as follows:

SELECT select_list

[INTO new_table_name]

FROM table_list

[WHERE search_conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list [ASC | DESC] ]

[3]. What are several keywords that you can use in a select list?

DISTINCT, TOP n, and AS

[4]. What type of objects can you specify in the FROM clause of a SELECT statement?

Tables, views, joins, and derived tables

[5]. What purpose does a join provide when used in a SELECT statement?

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table.

[6]. What are the differences between inner joins and outer joins?

Inner joins return rows only when there is at least one row from both tables that matches the join condition, eliminating the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause (as long as these rows meet any WHERE or HAVING search conditions).

[7]. What is a subquery?

A subquery is a SELECT statement that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery can be used anywhere an expression is allowed. A subquery is also called an inner query or inner select, while the statement containing a subquery is called an outer query or outer select.

[8]. What are the differences between a CUBE operator and a ROLLUP operator?

The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. The differences between CUBE and ROLLUP are as follows:

• CUBE generates a result set showing aggregates for all combinations of values in the selected columns.
• ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.

[9]. For what types of columns can you not specify values in an INSERT statement?

Columns with an IDENTITY property, columns with a DEFAULT definition that uses the NEWID() function, and computed columns

[10]. What methods can you use to modify data in a SQL Server database?

The UPDATE statement, database APIs and cursors, and the UPDATETEXT statement

[11]. What are the major clauses contained in an UPDATE statement?

SET, WHERE, and FROM

[12]. Which statement should you use to delete all rows in a table without having the action logged?

The TRUNCATE TABLE statement

Using Transact-SQL on a SQL Server Database (Single Marks Question)

Using Transact-SQL on a SQL Server Database

[1]. In which window in Query Analyzer can you enter and execute Transact-SQL statements?

The Editor pane of the Query window

[2]. How do you execute Transact-SQL statements and scripts in Query Analyzer?

You can execute a complete script or an individual Transact-SQL statement by creating or opening the script in the Editor pane and then pressing F5. To perform this task, no other statements can be entered into the Editor pane. If there are other statements, you must highlight the script or statements that you want to execute, then press F5.

[3]. What type of information is displayed on the Execution Plan tab, the Trace tab, and the Statistics tab?

The Execution Plan tab displays a graphical representation of the execution plan that is used to execute the current query. The Trace tab, like the Execution Plan tab, can assist you with analyzing your queries. The Trace tab displays server trace information about the event class, subclass, integer data, text data, database ID, duration, start time, reads and writes, and CPU usage. The Statistics tab provides detailed information about client-side statistics for execution of the query.

[4]. Which tool in Query Analyzer enables you to control and monitor the execution of stored procedures?

Transact-SQL debugger

[5]. What is Transact-SQL?

Transact-SQL is a language that contains the commands used to administer instances of SQL Server; to create and manage all objects in an instance of SQL Server; and to insert, retrieve, modify, and delete data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by ISO and ANSI.

[6]. What are the three types of Transact-SQL statements that SQL Server supports?

DDL, DCL, and DML

[7]. What type of Transact-SQL statement is the CREATE TABLE statement?

DDL

[8]. What Transact-SQL element is an object in batches and scripts that can hold a data value?

Variable

[9]. Which Transact-SQL statements do you use to create, modify, and delete a user-defined function?

CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION

[10]. What are control-of-flow language elements?

Control-of-flow language elements control the flow of execution of Transact-SQL statements, statement blocks, and stored procedures. These words can be used in Transact-SQL statements, batches, and stored procedures. Without control-of-flow language, separate Transact-SQL statements are performed sequentially, as they occur. Control-of-flow language elements permit statements to be connected, related to each other, and made interdependent by using programming-like constructs.

Control-of-flow keywords are useful when you need to direct Transact-SQL to take some kind of action. For example, use a BEGIN...END pair of statements when including more than one Transact-SQL statement in a logical block. Use an IF...ELSE pair of statements when a certain statement or block of statements needs to be executed IF some condition is met, and another statement or block of statements should be executed if that condition is not met (the ELSE condition).

[11]. What are some of the methods that SQL Server 2000 supports for executing Transact-SQL statements?

You can execute single statements, or you can execute the statements as a batch (a group of one or more Transact-SQL statements). You can also execute Transact-SQL statements through stored procedures and triggers. In addition, you can use scripts to execute Transact-SQL statements.

[12]. What are the differences among batches, stored procedures, and triggers?

A batch is a group of one or more Transact-SQL statements sent at one time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time. A stored procedure is a group of Transact-SQL statements that is compiled one time and can then be executed many times. A trigger is a special type of stored procedure that a user does not call directly. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

Microsoft SQL Server 2000 (Single Marks questions of Msc(IT)-1)

[1]. What is SQL Server 2000?

SQL Server 2000 is an RDBMS that uses Transact-SQL to send requests between a client computer and a SQL Server 2000 computer. An RDBMS includes databases, the database engine, and the applications necessary to manage the data and the components of the RDBMS. The RDBMS organizes data into related rows and columns within the database.

[2]. What language is commonly used to work with data in a database?

SQL

[3]. What is XML?

XML is a standard format for data on the Internet. XML consists of tags within a text document that define the structure of the document. XML documents can be easily processed through HTML. Although most SQL statements return their results in a relational (tabular) result set, the SQL Server 2000 database component supports a FOR XML clause that causes the results to be returned as an XML document. SQL Server 2000 also supports XPath queries from Internet and intranet applications.

[4]. Which edition of SQL Server 2000 includes the complete SQL Server offering?

SQL Server 2000 Enterprise Edition

[5]. What is the purpose of the SQL Server 2000 relational database engine?

The SQL Server 2000 relational database engine is a modern, highly scalable engine for storing data. The database engine stores data in tables. Applications submit SQL statements to the database engine, which returns the results to the application in the form of a tabular result set. Internet applications submit either SQL statements or XPath queries to the database engine, which returns the results in the form of an XML document. The relational database engine provides support for common Microsoft data access interfaces, such as ADOs, OLE DB, and ODBC.

[6]. What SQL Server 2000 technology helps you build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources?

DTS

[7]. What are at least four administrative tasks that you can use the Enterprise Manager to perform?

Any four of the following tasks:

Defining groups of servers running SQL Server

Registering individual servers in a group

Configuring all SQL Server options for each registered server

Creating and administering all SQL Server databases, objects, logins, users, and permissions in each registered server

Defining and executing all SQL Server administrative tasks on each registered server

Designing and testing SQL statements, batches, and scripts interactively by invoking Query Analyzer

Invoking the various wizards defined for SQL Server

[8]. Which tool is commonly used to create queries and execute them against SQL Server databases?

Query Analyzer

[9]. What are at least five objects that can be included in a logical database?

Table,Data type,View,Stored procedure,Function,Index,Constraint,Rule,Default,Trigger

[10]. What are the major components involved in processing a SQL statement received from a SQL Server client?

The client, the tabular data stream, the server Net-Library, and SQL Server (the relational database engine)