Sybase Question set III

1. State whether the following statements are true or false

1) Wildcards can be contained in values passed to stored procedure-T(like)

2) Rules, defaults and column properties do not apply to parameters defined with user defined datatypes -T

3) Parameter name should be 20 characters in length-F(29)

4) In order to make changes in the stored procedure, we can alter the stored procedure-F(drop)

5) The stored procedure return 0 on success and 1 for errors-F (0 , -1 to -99)

2. What is isolation level, list different isolation levels in Sybase and what is default?

To avoid the manual overriding of locking, we have transaction isolation level which are tied with transaction.

List of different isolation levels are isolation level 0,1,2,3.

Isolation level 1- this allow read operation can only read pages. No dirty reads are allowed.

Isolation level 0-This allows reading pages that currently are being modified. It allows dirty read

Isolation level 2-Allows a single page to be read many times within same transaction and guarantees that same value is read each time. This prevent other users to read

Isolation level 3- preventing another transaction from updating, deleting or inserting rows for pages previously read within transaction

Isolation level 1 is the default

3. Give me the correct function or command name for the following description

1. The function used to change data from one type to another when SQL server cannot implicitly understand a conversion- convert
2. The function gives the user’s SQL server login name-suser_name([server_user_id])
3. The command used to choose date formats- set dateformat
4. The function removes the leading space of a string-ltrim
5. The function returns the specified part of a date expression as a string.-datepart(datepart,Date_exp)
6. The keyword marks the point in the transaction for potential rollback –save point

4. Look at the column of a table below.

Name

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

Raja Ram Mohan

RaviKishore

Abdul Rahman Mohd.

Ramesh Kumar

M S R

Raja Kumar Siva

A B J Abdul Kalam

Aksai Kumar

Muralitharan

Write a query to retrive the name contains 3 and more words

Ans: select name from table where name like ‘%’ + space(1) or [ ] + ‘%’ + space (1)or [ ] + ‘%’

5. What’s the difference between DELETE TABLE and TRUNCATE TABLE

commands?

Ans: DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster.

Of course, TRUNCATE TABLE can be rolled back.

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.

But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.

The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE TABLE may not be used on tables participating in an indexed view

Construct the Query using the Tables album and track of music database

album
Asin	title	artist	price	release	label	Rank
B00000255F	Born to Run	Bruce Springsteen	13.98	1975-08-25	Sony	521
B000002UAU	Sgt. Pepper's Lonely Hearts Club Band	The Beatles	13.49	1967-06-01	Capitol	218
...

Track
Album	dsk	posn	Song
B00000255F	1	1	Thunder Road
B00000255F	1	2	Tenth Avenue Freeze-Out
B00000255F	1	3	Night
B00000255F	1	4	Backstreets
B00000255F	1	5	Born to Run
...
B000002UAU	1	1	Sgt. Pepper's Lonely Hearts Club Band
B000002UAU	1	2	With a Little Help from My Friends
B000002UAU	1	3	Lucy in the Sky With Diamonds
B000002UAU	1	4	Getting Better
B000002UAU	1	5	She's Leaving Home
...

1. Find the title and artist who recorded the song 'Alison'

Ans: select title,artist from album,track where track.song="Alison"

2. Show the song for each track on the album 'Blur'

Ans: select track.song from track,album where track.album=album.asin and album.title="Blur"

3. For each album show the title and the total number of track

Ans: select a.title,count(*) from album a,track t where a.asin=t.album group by a.title

4. For each album show the title and the total number of tracks containing the word 'Heart' (albums with no such tracks need not be shown)

Ans: select a.title, count(*) from album a, track b where a.asin=b.album and b.song like '%Heart%' group by a.title

5. An "eponymous" album is one where the title is the same as the artist (for example the album 'Blur' by the band 'Blur'). Show the eponymous albums

Ans: select title eponymous from album where artist=title