There are three types of locks:
* shared
* exclusive
* update
* shared
These locks are requested and used by readers of information. More than one connection can hold a shared lock on a data page. This allows for multiple readers.
exclusive -The SQL Server uses exclusive locks when data is to be modified. Only oneconnection may have an exclusive lock on a given data page. If a tableis large enough and the data is spread sufficiently, more than oneconnection may update different data pages of a given tablesimultaneously.
update
A update lock is placed during adelete or an update while the SQL Server is hunting for the pages to bealtered. While an update lock is in place,there can be shared locksthus allowing for higher throughput.
The update lock(s) are promoted to exclusive locks once the SQL Server is ready to perform the delete/update.
Table LocksThere are three types of table locks:
* intent
* shared
* exclusive
intent
Intent locks indicate the intention to acquire a shared or exclusive lock on a data page. Intent locks are used to prevent other transactions from acquiring
shared or exclusive locks on the given page.
shared
This is similar to a page level shared lock but it affects the entire table.This lock is typically applied during the creation of a non-clustered index.
exclusive
This is similar to a page level exclusive lock but it affects the entire table.If an update or delete affects the entire table, an exclusive table lock is generated. Also, during the creation of a clustered index an exclusive lock is generated.
Demand Locks
A demand lock prevents further shared locks from being set. The SQL Server sets a demand lock to indicate that a transaction is next to lock a table or a page.
This avoids indefinite postponement if there was a flurry of readers when a writer wished to make a change.
2.Demand Locks
Demand locks prevent any more shared locks from being set. SQL Server sets a demand lock to indicate that a transaction is next in line to lock a table or page. This avoids situations in which read transactions acquire overlapping shared locks, monopolizing a table or page, so that a write transaction waits indefinitely for its exclusive lock.
After waiting on several different read transactions, SQL Server gives a demand lock to the write transaction. As soon as the existing read transactions finish, the write transaction is allowed to proceed. Any new read transactions must then wait for the write transaction to finish, when its exclusive lock is released.
3.What are the Isolation levels in Transaction
The SQL92 standard defines four levels of isolation for transactions. Each isolation level specifies the kinds of actions that are not permitted while concurrent transactions are executing. Higher levels include the restrictions imposed by the lower levels:
Level 0 - ensures that data written by one transaction represents the actual data. It prevents other transactions from changing data that has already been modified (through an insert, delete, update, and so on) by an uncommitted transaction. The other transactions are blocked from modifying that data until the transaction commits. However, other transactions can still read the uncommitted data, which results in dirty reads.
Level 1 - prevents dirty reads. Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid. This is the default isolation level supported by Adaptive Server.
Level 2 - prevents nonrepeatable reads. Such reads occur when one transaction reads a row and a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield different results than the original read.
Adaptive Server supports this level for data-only-locked tables. It is not supported for allpages-locked tables.
Level 3 - ensures that data read by one transaction is valid until the end of that transaction, hence preventing phantom rows. Adaptive Server supports this level through the holdlock keyword of the select statement, which applies a read-lock on the specified data. Phantom rows occur when one transaction reads a set of rows that satisfy a search condition, and then a second transaction modifies the data (through an insert, delete, update, and so on). If the first transaction repeats the read with the same search conditions, it obtains a different set of rows.
You can set the isolation level for your session by using the transaction isolation level option of the set command. You can enforce the isolation level for just a query as opposed to using the at isolation clause of the select statement. For example:
set transaction isolation level 0
Default isolation levels for Adaptive Server and SQL92
By default, the Adaptive Server transaction isolation level is 1. The SQL92 standard requires that level 3 be the default isolation for all transactions. This prevents dirty reads, nonrepeatable reads, and phantom rows. To enforce this default level of isolation, Transact-SQL provides the transaction isolation level 3 option of the set statement. This option instructs Adaptive Server to apply a holdlock to all select operations in a transaction. For example:
set transaction isolation level 3
Applications that use transaction isolation level 3 should set that isolation level at the beginning of each session. However, setting transaction isolation level 3 causes Adaptive Server to hold any read locks for the duration of the transaction. If you also use the chained transaction mode, that isolation level remains in effect for any data retrieval or modification statement that implicitly begins a transaction. In both cases, this can lead to concurrency problems for some applications, since more locks may be held for longer periods of time.
To return your session to the Adaptive Server default isolation level:
set transaction isolation level 1
0 comments
Sybase Questions Set V
1. Explain about Exists,Join,Distinct?
2. Write a query to find the duplicate rows from a table?
Select name, count(*) from tablename group by name having count(*)>1
3. You have created a view as Vi_emp and writing the query like select * from vi_emp order by name, Is the query have any Issue?
4.There are two tables master is Employees and the detail is Attendance. The Employee_ID field is common to both tables. How do you find the name of the employees those have missed more than 5 days of work in last 30 days
Select name from Employees a where a.id in (select b.id from attendance b where a.id=b.id and date between (lastmonth+1 ,lastmonth) presence=0 group by id having count(presence)>4)
5.State True or False
a)We can run Truncate command inside the Trigger F
b)Trigger can be recursive and stored procedure in reentrant
c)In the transaction rollback Trigger reset the @@trancount value to previous command value. T
d)In chain mode we have to end the transaction with commit or rollback Tran Statement.
6.Give me the Global variable names for the description given below
Error number reported for last SQL statement ( @@error)
Current transaction mode (chained or unchained)(@@tranchained)
Status of prevoius fetch statement in a cursor(@@sqlstatus)
Transaction nesting level(@@trancount)
Current process ID(@@spid)
The database scheme consists of four relations:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.
1.Find the makers producing at least three distinct models of PCs. Result set: maker, number of models.
Select maker,count(*) from product where type='pc' group by maker having count(*)>2
2.Find the model number of the product (PC, laptop) with the highest speed. Result set: model.
Select model from (select model,speed from pc where speed=(select max(speed) from pc) union select model,speed from laptop where speed=(select max(speed) from laptop)) a where speed=(select max(speed) from (select speed from pc union select speed from laptop) b)
3.Define the minimum price of color printer produced by each maker. Result set: maker, minimum price
select product.maker,min(pc.price) from product,pc where pc.price in (select min(price) from pc) and type='printer' and color='y'
4.Find the ram that are equal among two or more PCs. Result set: ram.
Select ram from pc where ram in (select ram from pc group by ram having count(*)>=2)
0 comments
Sybase Question set IV
I) 1. What is the difference between Where and Having clause?
2. What are the difference types of Joins in Sybase?
3. What are the difference types of Sub Query?
4. Explain Union with an example?
5. State the following true or false
a) ‘Select into’ will enter into the log
b) Exists allow the duplicates in the result set
c) Distinct remove the duplicates in the result set
d) You can change the data types of a column
II) The database scheme consists of four relations:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.
1. Find the pc model which have the maximum price
2. Delete from PC table the computers having minimal hdd size
3. Maker A has passed manufacture of printers to maker Z. Perform necessary changes
4. Find the maker who produces matrix printer with minimum price
0 comments
Sybase Question set III
1. State whether the following statements are true or false
1) Wildcards can be contained in values passed to stored procedure-T(like)
2) Rules, defaults and column properties do not apply to parameters defined with user defined datatypes -T
3) Parameter name should be 20 characters in length-F(29)
4) In order to make changes in the stored procedure, we can alter the stored procedure-F(drop)
5) The stored procedure return 0 on success and 1 for errors-F (0 , -1 to -99)
2. What is isolation level, list different isolation levels in Sybase and what is default?
To avoid the manual overriding of locking, we have transaction isolation level which are tied with transaction.
List of different isolation levels are isolation level 0,1,2,3.
Isolation level 1- this allow read operation can only read pages. No dirty reads are allowed.
Isolation level 0-This allows reading pages that currently are being modified. It allows dirty read
Isolation level 2-Allows a single page to be read many times within same transaction and guarantees that same value is read each time. This prevent other users to read
Isolation level 3- preventing another transaction from updating, deleting or inserting rows for pages previously read within transaction
Isolation level 1 is the default
3. 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
4. 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 [ ] + ‘%’
5. What’s the difference between DELETE TABLE and TRUNCATE TABLE
commands?
Ans: DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.
TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster.
Of course, TRUNCATE TABLE can be rolled back.
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.
The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view
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. Find the title and artist who recorded the song 'Alison'
Ans: select title,artist from album,track where track.song="Alison"
2. 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"
3. 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
4. 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
5. 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
0 comments
Sybase Questions Set II
(I)
1. What are the difference types of Joins in Sybase?
2. What is the difference between a sub-query and a correlated sub-query?
Ans: A subquery is a query that SQL Server must evaluate before it can process the main query. It doesn’t depend on a value in the outer subquery.
A correlated subquery is the one that depends on a value in a join clause in the outer subquery
3. 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
4. Explain the following.
a) count()
b) count(*)
c) distinct
d) union
5. What is the difference between delete and truncate?
II) The database scheme consists of four relations:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.
1. Find the pc model which have the maximum price
2. Delete from PC table the computers having minimal hdd size
3. Maker A has passed manufacture of printers to maker Z. Perform necessary changes
4. Find the maker who produces matrix printer with minimum price
0 comments
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
2. 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
3. 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
4. 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
5. Find the Top album of each company
select title ,label ,min(rank) from album group by label
0 comments
Session 5
1. Give some notes on Indexes.
2. Expalin the steps involved during the first execution and subsequent executions of a stored procedure.
3. State whether the following statements are true or false
1) Wildcards can be contained in values passed to stored procedure-T(like)
2) Rules, defaults and column properties do not apply to parameters defined with user defiend datatypes -T
3) Parameter name should be 20 characters in length-F(29)
4) In order to make changes in the stored procedure, we can alter the stored procedure-F(drop)
5) The stored procedure return 0 on success and 1 for errors-F (0 , -1 to -99)
4. Explain the set commands given below
1) set rowcount (Instruct the server to rturn only the first n rows of data)
2) set statistics io on (Asks the server for the no. of logical and physical page requests)
3) set nocount on ( stops reporting the number of rows returned)
4) 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)
5) set statistics time on (requests the execution time)
5. What is isolation level, list different isolation levels in Sybase and what is default?
To avoid the manual overriding of locking, we have transaction isolation level which are tied with transaction.
List of different isolation levels are isolation level 0,1,2,3.
Isolation level 1- this allow read operation can only read pages. No dirty reads are allowed.
Isolation level 0-This allows reading pages that currently are being modified. It allows dirty read
Isolation level 2-Allows a single page to be read many times within same transaction and guarantees that same value is read each time. This prevent other users to read
Isolation level 3- preventing another transaction from updating, deleting or inserting rows for pages previously read within transaction
Isolation level 1 is the default.
Movie Database
This database features two entities (movies and actors) in a many-to-many relation. Each entity has its own table. A third table, casting , is used to link them. The relationship is many-to-many because each film features many actors and each actor has appeared in many films.
movie
Field name
Type
Notes
id
INTEGER
An arbitrary unique identifier
title
CHAR(70)
The name of the film - usually in the language of the first release.
yr
DECIMAL(4)
Year of first release.
score
FLOAT
Average of all the votes cast for the film. (Internet users can vote for films on a scale of 1-10)
votes
INTEGER
The number of votes cast for this film.
actor
Field name
Type
Notes
id
INTEGER
An arbitrary unique identifier
name
CHAR(36)
The name of the actor (the term actor is used to refer to both male and female thesps.)
casting
Field name
Type
Notes
movieid
INTEGER
A reference to the movie table.
actorid
INTEGER
A reference to the actor table.
ord
INTEGER
The ordinal position of the actor in the cast list. The star of the movie will have ord value 1 the co-star will have value 2, ...
6. Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.
SELECT yr, COUNT(movie.id)
FROM movie, casting, actor
WHERE name='John Travolta'
AND movieid=movie.id
AND actorid=actor.id
GROUP BY yr
7. List the film title and the leading actor for all of 'Julie Andrews' films.
SELECT title, name
FROM movie, casting, actor
WHERE movieid=movie.id
AND actorid=actor.id
AND ord=1
AND movieid IN
(SELECT movieid FROM casting, actor
WHERE actorid=actor.id
AND name='Julie Andrews')
8. Obtain a list of actors in who have had at least 10 starring roles.
SELECT name
FROM casting ,actor where
actorid = actor.id
WHERE ord=1
GROUP BY name
HAVING COUNT(movieid)>=10
9. List the 1978 films by order of cast list size.
SELECT title, COUNT(actorid)
FROM casting, movie
WHERE yr=1978
AND movieid=movie.id
GROUP BY title
ORDER BY 2 DESC
10. List all the people who have worked with 'Art Garfunkel'.
select name from actor,casting where name !="Art Garfunkel" and actorid=actor.id and movieid in (select movieid from actor,casting where name = "Art Garfunkel" and id=actorid)
0 comments
Session 4
1. State whether the following is true or false
1) The object names of the SQL server can be upto 50 characters in length and are case-sensitive
2) The name of the tables can be same in the same database
3) The default property for the identity column is null
4) Identity columns must use the numeric datatype and have a scale of 0
Ans: F-30, T, F, T
2. Answer the following
1)A numeric column allows to store upto _38__ decimal places
2)The scale of the numeric datatype for identity column is_0___
3)Views can have __16__levels of nesting
4)Maximum number of parameters the stored procedure can accept is _255__
(set identity_insert table_name on)
3. Explain the views with check option
4. Give the function name for the description given
Largest integer less than or equal to the specified value-floor(num_expr)
Current active roles for user-show_role()
Returns a specified part of date_expr value as a string- datename(datepart,dateexpr)
Replace expr1, if null with expr2- isnull(expr1, expr2)
Generates string of int_expr spaces- space(int_exp)
Returns length of expr in bytes-datalength(expr)
5. What are the different kinds of locks in sybase?
Locking: The process of restricting access to resources in a multi-user environment to
maintain security and prevent concurrent access problems. SQL Server
automatically applies locks to tables or pages.
Shared locks—SQL server applies shared lock for read operations. All the processes can read, but no process can write. (Eg- select statement.)
Exclusive lock--- SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.
Update lock –allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed
Movie Database
This database features two entities (movies and actors) in a many-to-many relation. Each entity has its own table. A third table, casting , is used to link them. The relationship is many-to-many because each film features many actors and each actor has appeared in many films.
movie
Field name
Type
Notes
id
INTEGER
An arbitrary unique identifier
title
CHAR(70)
The name of the film - usually in the language of the first release.
yr
DECIMAL(4)
Year of first release.
score
FLOAT
Average of all the votes cast for the film. (Internet users can vote for films on a scale of 1-10)
votes
INTEGER
The number of votes cast for this film.
actor
Field name
Type
Notes
id
INTEGER
An arbitrary unique identifier
name
CHAR(36)
The name of the actor (the term actor is used to refer to both male and female thesps.)
casting
Field name
Type
Notes
movieid
INTEGER
A reference to the movie table.
actorid
INTEGER
A reference to the actor table.
ord
INTEGER
The ordinal position of the actor in the cast list. The star of the movie will have ord value 1 the co-star will have value 2, ...
6. Obtain the cast list for the film 'Alien'
Ans: SELECT name
FROM movie, casting, actor
WHERE title='Alien'
AND movieid=movie.id
AND actorid=actor.id
7. List the films where 'Harrison Ford' has appeared - but not in the star role
Ans: select title from movie,actor,casting where actor.name='Harrison Ford' and casting.movieid=movie.id and actor.id=casting.actorid and casting.ord>1
8. What are the titles of the films with id 1, 2, 3?
Ans: SELECT title FROM movie WHERE id IN (1,2,3)
9. List the 1978 films by order of cast list size.
Ans: SELECT title, COUNT(actorid)
FROM casting, movie
WHERE yr=1978
AND movieid=movie.id
GROUP BY title
ORDER BY 2 DESC
10. List the films together with their stars for all 1962 films
Ans: select title,name from movie,actor,casting where yr=1962 and movieid=movie.id and actorid=actor.id having ord=1
0 comments
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 215-1
Ans; TTFF (m/d/y) FF (231)
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
0 comments
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
0 comments
Session 1
1. What is the difference between a sub-query and a correlated sub-query?
Ans: A subquery is a query that SQL Server must evaluate before it can process the main query. It doesn’t depend on a value in the outer subquery.
A correlated subquery is the one that depends on a value in a join clause in the outer subquery
2. What is the advantage of stored procedure?
Ans: Faster Execution: after first exec it become memory resident and do not need to be reparsed, recompiled
Reduced network traffic
Modular programming
Reduced operator error-less info to pass
3. What command do we use to rename a database?
Ans: sp_renamedb ‘oldname’ , ‘newname’
Well sometimes sp_renamedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? - In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.
4. What is default? Is there a column to which a default can’t be bound?
Ans: A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them. See CREATE DEFAULT in books online
5. What’s the difference between DELETE TABLE and TRUNCATE TABLE
commands?
Ans: DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.
TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster.
Of course, TRUNCATE TABLE can be rolled back.
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.
The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view
6. Write a query to find the duplicate rows from a table?
Select name, count(*) from tablename group by name having count(*)>1
7. Give me the Global variable names for the description given below
1. Error number reported for last SQL statement ( @@error)
2. Current transaction mode (chained or unchained)(@@tranchained)
3. Status of prevoius fetch statement in a cursor(@@sqlstatus)
4. Transaction nesting level(@@trancount)
5. Current process ID(@@spid)
8. There are two tables master is Employees and the detail is Attendance. The Employee_ID field is common to both tables. How do you find the name of the employees those have missed more than 5 days of work in last 30 days
Select name from Employees a where a.id in (select b.id from attendance b where a.id=b.id and date between (lastmonth+1 ,lastmonth) presence=0 group by id having count(presence)>4)
9. Write a query to access a particular number of rows in a table?
set rowcount 10
go
set rowcount 0
0 comments