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