DataBase Tranning 1

من طرف **SiteAdmin** الخميس يوليو 08, 2010 11:43 am

Database Tanning 1

1- write SQL Statement to :

1-1- Create table Staff .

1-2- create table Registration .

1-3- insert second record in Private owner .

1-4- insert 4^th record into Client .

1-5- delete table Viewing .

1-6- create table PropertyForRent .

1-7- delete table registration .

1-8- insert 4^th row in Viewing table .

2- write the result of the following SQL :

2-1- insert into brach

Values(B008,Wabour elnour,sama,6166A)

2-2- insert into brach(BranNo,streest,city,PosCod)

Values (B010,schools st,minia,1512A)

2-3- insert into client

Values(CR70,ehab,ragab,Vila,950)

2-4-insert into registration

Values(CR60,B007,SL21,775209)

2-5-drop Staff

2-6- insert into client

Values(CR70,ehab,ragab,,Vila,950)

2-7- create table temp

(Name CHR not null

Street CHR not null

City CHR not null

Tel INT

CardNo INT

Sex bolean mot null

Primary Key(CardNo))

2-8- insert into brach

Values (B003,Refeia st,samalout,AA6166).

3- write SQL Clauses to create the following reports and write the result :

3-1-staff No and staff full name .

3-2- all Female Staff data .

3-3- staff who has salary less than 20000 .

3-4- all data about maximize max rent .

3-5- average max rent .

3-6- all data about property has minimum rooms .

3-7- all data about property has no commits .

3-8- all data about houses for rent .

3-9- all client data who registered by StafNo SG37 .

3-10- PropertyForRent privateخاصة بـ for staff manger .

3-11- staff No and First name to all staff burned before 1-1-60

3-12- all data for PropertyForRent private for Owner Carol .

4- write the result of the following SQL :

4-1- select *

From Clint

Where maxrent 600

4-2- select * from Viewing

Where ProNo=(select ProNo

From PropertyForRent

Where type=’house’)

4-3- select ProNo,rent

From PropertyForRent

Where rooms between (4,5)

4-4- select ProNo,rent

From PropertyForRent

Where rooms >= 4 Or Rooms <=5

4-5- select StfNo,Fname,(Salary/12)AS Mon

From staff

Where sex=’M’

4-6- select FName, LName , Salary

From Staff

Where StfNo= ‘SL50’

4-7- select *

From Client

Where maxrent > (1000/2)

4-8- select ProNo, Comment AS Notes

From Viewing

Where Comments <>’’