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

  1. 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

  1. 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

  1. 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

  1. Find the Top album of each company

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

No comments: