Data base Tranning 1 Answer

من طرف **SiteAdmin** الخميس يوليو 08, 2010 12:03 pm

Database Tanning 1-Answer

1-1- create table staff

(StfNo CHR not null

FName CHR not null

LName CHR not null

Position CHR not null

Sex Bolean not null

DOB Date not null

Salary INT not null

BranNo CHR not null

Primary key(StfNo))

1-4- insert into Client

Values(CR62,Mary,Treg,01224-169,Flat,600)

1-5- Drop Table Viewing

2-3- error because we will interring NUMERIC data in CHR field in column PrefType and Null Value in MaxRent .

2-5- This SQL is Error It must Be Drop table Staff Result Is : deleting table Staff from database file .

2-6-

CR70

ehab

ragab

Vila

950

2-7-error Primary key CardNo Cannot be Null.

2-8- error Primary key BranNo already have value B003 we cannot duplicate values in PK.

3-1-select Stfno,FName,LName

From staff

Result :

StfNo	FName	LName
SL21 SG37 SG14 SA9 SG5 SL41	John Ann David Mary Susan Julie	White Beech Ford Howe Brand Lee

3-2- select * from staff

Where Sex = ‘F’

Result : draw table contain two rows and copy data for SL21 and SG14 .

3-3- select * From Staff

Where Salary < 20000

Result : …………………….

3-4- Select max(Maxrent) from Client

Result :

Max(MaxRent)

750

3-5-select AVG(Maxrent) from Client

Result :

Avg(MaxRent)
531.25	Sum(maxrent)/count

3-7- select * from viewing

Where Comment <>’’

Result : …………………………………..

3-8- Select * from PropertyForRent

Where type=’Houses’

3-9-select * from Client

Where ClintNo=(select ClintNo

From registration

Where StfNo=’SG37’)

3-10- select * from PropertyForRent

Where StfNo=(select StfNo from Staff

Where Position=’manager’)

3-11- select StfNo,FName from Staff

Where DOB < 1-Jan-60

3-12-select * from PropertyForRent

Where OwNo = ( select OwNo

from PrivateOwner

where Fname=’Carol’ )

4-1- Error condition must have comparing parameter (=,>,<,<=,>=,like,between…etc)

4-2- nested select result is PA14 and PG21

Result :

ClintNo	ProNo	ViewDa	Comment
CR56 CR62	PA14 PA14	24-may-01 14-May-01	Too small No dinning room

4-5-

StfNo	Fname	Mon
SL21	John	2500	30000/12
SG14	Ford	1500	18000/12

4-6- Empty table ( SL50 not found)

4-7- true Answer it !

4-8-

ProNo	Notes
CR56
CR56