Q 10

I) 1.What is substring()?

2.Explain the following functions:

a)stuff

b)reverse

c)ltrim

d)rtrim

3.What is patindex() and charindex()

4.Explain the following functions

a)ceiling

b)floor

c)round

d)pi

5.Explain the following functions

a)datename

b) datediff

c)dateadd

e)convert

II) Under consideration is the database of naval ships that took part in World War II. The database has the following relations:

Classes(class,type,country,numGuns,bore,displacement)
Ships(name,class,launched)
Battles(name,date)
Outcomes(ship,battle,result)

Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, class name does not coincide with any ship name in the database).
The relation Classes includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The relation Ships includes the ship's name, its class name, and launch year. The relation Battles covers the name and date of the battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the relation Outcomes. Note: the relation Outcomes may include the ships not included in the relation Ships.

1 Point out the names and countries of the ship classes the gun caliber of which is not less than 8 in.

ans: select class,country from classes where bore >=8

2. Define the ships sunken at the battles in the North Atlantic.Result set: ship.

Ans: select ship from Outcomes where battle='North Atlantic' and result='sunk'

3. Find the class, name and country for all the ships having not less than 10 guns.

Ans: select c.class,s.name,c.country from ships s,classes c where s.class=c.class and c.numguns>=10

4. Find the names of ships sunked in battles and the names of corresponding battles.

Ans: select ship,battle from outcomes where result='sunk'