Session 2

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

2. State whether the following statements are True or False

1. count() Returns the number of rows found

2. having keyword should always follow the group by

3. A result set based on a query with a proper join condition is called a cartesian product

4. distinct requires an additional sorting step using a worktable

5. subquery always return single column of data

6. once you create a column and declare its datatype, you cannot change that datatype without dropping and recreating the table.

Ans: FTFTF(F)

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. State whether the following statement is True

1. The maximum number of parameters passed to stored procedure is 16

2. The maximum number of tables used for join is 24

3. The maximum length of a character column is 255 characters

4. The storage size of smalldatetime datatype is 8 bytes

5. The maximum number of index on the table is 250

Ans: F(255)F(16)TF(4)T

5. Explain timestamp datatype?

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

...

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

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

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

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

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

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

No comments: