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

2. 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 2^15-1

Ans; TTFF (m/d/y) FF (2³¹)

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

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

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

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

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

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

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

10. Find the Top album of each company

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