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)