Sybase Questions set 1

1. Explain the steps involved during the first execution and subsequent executions of a

stored procedure.

2. Suppose I have a table with following column

E_Id

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

BLL12

BRR

BDGT8

NW23L

KO9G0

B___B

___BN

BSNMA

___12

BNG

What will be the output of the following query?

“select E_Id from table where E_Id like ‘___’”

Ans: BRR

% -0 or more

_ - any single chr

[]- any single chr listed in the bracket

3. How many rows may be retrieved by the select statement in the following SQL

code samples?

               create table t1  (k int unique)

               insert t1 values (rand()*1000)

               insert t1 values (rand()*1000)

               insert t1 values (rand()*1000)

               [...1000 additional inserts...]

               select * from t1 where k = rand()*1000

Ans: 0 or 1

4. Explain timestamp datatype?

5. Explain the set commands given below

1) set rowcount (Instruct the server to rturn only the first n rows of data)

2) set nocount on ( stops reporting the number of rows returned)

3) set noexec on ( parse and optimize, but don’t exec the query (used with show plan for looking at the plan without running a query)) showplan gives the final optimization plan for a query)

4) set statistics time on (requests the execution time)

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. A "title track" is where the song is the same as the title. Find the title

Tracks

select song "title tracks" from album,track where album.asin=track.album and album.title=track.song

2. Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up

select song, count (album) from album, track where album.asin=track.album group by song having count(song)>2

3. A "good value" album is one where the price per track is less than 50 pence. Find the good value album - show the title, the price and the number of tracks

select title,price,count(song) from album,track where album.asin=track.album group by title having (price/count(song))<0.5

4. Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby clocks up 101 tracks

List albums so that the album with the most tracks is first. Show the title and the number of tracks

select title, count(song) from album,track where album.asin=track.album group by title order by count(song) desc

5. Find the Top album of each company

select title ,label ,min(rank) from album group by label