Session 3

  1. You have a table with many rows -- say, 10 million. Let's assume about 9.5 million rows, identified by certain criteria, must be deleted from this table. What's the fastest way to do this?

Ans: Transfer 0.5 million rows to a temp table and truncate the main table and transfer data from temp to main table

  1. State whether the following statement is true or false

    1. Columns defined as char store trailing blanks to fill out a fixed number of characters.
    2. Columns defined as varchar truncate trailing blanks to save space.
    3. The column defined with null will not use any extra overhead in each row to keep track of the actual length
    4. The server treats the date format as year/month/day in default
    5. The column defined with bit datatype can defined with null value
    6. The maximum value of int datatype is 215-1

Ans; TTFF (m/d/y) FF (231)

  1. What is rules and different methods to create a rule and some of the limitations?

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

  1. 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 [ ] + ‘%’

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: