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