SQL Challenge

Here is a challenge that takes you away from those repetitive boring type of queries that you write over and over again, several times a day. All of us, the database people, are familiar with thinking in set based manner as well as row by row style. Here is something that is very interesting where you might need to process records in a 'three-line-at-a-time' fashion.

For the purpose of this challenge, imagine that you are working for a bank which just decided to scan all the banking documents. Assume that they have an old fashioned scanner which scans the documents and produces a text file with the customer number. So far so good. Well, not really! Unfortunately the scanner produces a graphical representation of the customer number using three lines of symbols: space, unerscores and pipe characters.

Here is an example of the output produced by the scanner.

Here are the rules to keep in mind while reading and recognizing the output generated by the scanner.

• Each digit is represented using 9 cells (3x3)
• Only spaces, underscores and pipe characters are used
• The number of digits in each account number may vary.
• The Scanner is not 100% reliable and it might produce some digits that are invalid

The Challenge

Your job is to read the output produced by the scanner and identify the the customer number represented by each image. Remember that the scanner is not very reliable and it might produce invalid digit representations. For each digit that is not valid, set the value to 'X'

Sample Data

Here is the sample data for this challenge. Please take care with spaces, tabs and carriage returns as each digit is represented by three lines of text and if a space, tab or carriage return is misplaced, the whole image will be distorted.

Id          ScanNumber

----------- ---------------------------

1            _  _  _  _  _  _  _  _  _  

            | || || || || |  || ||_ |_|  

            |_||_||_||_||_|  ||_| _| _| 

2               _  _  _  _  _  _     _ 

            |_||_|| || ||_   |  |  ||_ 

              | _||_||_||_|  |  |  | _|

3            _  _  _     _  _  _  _  _  

            |_ |_|| || ||_ |_| _|  ||_| 

            |_||_||_||_||_||_||_   | _| 

4               _  _  _  _  _  _     _ 

            |_||_|| ||_||_   |  |  ||_ 

              | _||_||_||_|  |  |  ||_|

5               _  _  _  _  _  _     _ 

            | ||_|| ||_||_   |  |  ||_ 

              | _||_||_||_|  |  |  ||_|

6            _     _  _     _  _  _  _ 

            | |  | _| _||_||_ |_   ||_|

            |_|  ||_  _|  | _||_|  ||_|

Expected Results

Based on the sample input and the rules discussed earlier, here is the expected output.

Id          Value

----------- ---------

1           000007059

2           490067715

3           680X68279

4           490867716

5           X90867716

6           012345678

Sample Scripts

Use the following script to generate the sample data for this challenge.

DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))

INSERT INTO @t

SELECT  1,--> 000 007 059

'_  _  _  _  _  _  _  _  _ 

| || || || || |  || ||_ |_|

|_||_||_||_||_|  ||_| _| _|

' UNION 

SELECT 2,-->  490 067 715

'   _  _  _  _  _  _     _ 

|_||_|| || ||_   |  |  ||_ 

  | _||_||_||_|  |  |  | _|

' UNION

SELECT  3, --> 680 X68 279

'_  _  _     _  _  _  _  _ 

|_ |_|| || ||_ |_| _|  ||_|

|_||_||_||_||_||_||_   | _|

' UNION

SELECT  4,--> 490 867 716

'   _  _  _  _  _  _     _ 

|_||_|| ||_||_   |  |  ||_ 

  | _||_||_||_|  |  |  ||_|

'  UNION

SELECT  5,--> X90 867 716

'   _  _  _  _  _  _     _ 

| ||_|| ||_||_   |  |  ||_ 

  | _||_||_||_|  |  |  ||_|

' 

UNION 

SELECT 6,--> 012 345 678

'_     _  _     _  _  _  _ 

| |  | _| _||_||_ |_   ||_|

|_|  ||_  _|  | _||_|  ||_|

Notes

1. Each record may have more than three lines of data (each line is separated by a CR and LF). Your code should consider only the first three lines.
2. The length of the first three lines of each recrd will always be the same and will be divisible by three.
3. There may be 3x3 blocks of spaces in the string. In such a case, you should generate an empty space in the output. If a 3x3 block does not create a valid digit (except for the case of a 3x3 block of spaces), you should generate an "X".
4. The number of 3x3 blocks in each record may vary

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.