Requetes de TP INFORMIX 3eme anneé

Merci Farah

DATABASE Societe
main

# Requete b)
# ---------
{
DEFINE P_Employe RECORD LIKE Employe.*
DECLARE C_Employe CURSOR FOR SELECT * FROM Employe ORDER BY nume
OPEN C_Employe
WHILE (true and status=0)
FETCH C_Employe INTO P_Employe.*
IF STATUS=100 THEN EXIT WHILE
ELSE DISPLAY P_Employe.*
SLEEP 0
END IF
END WHILE
Sleep 2
CLOSE C_Employe
}


# Requete c)
# ---------
{DEFINE P_chef LIKE Employe.nome
SELECT nome INTO P_chef FROM Employe WHERE nume=
(SELECT chef FROM Employe WHERE nome='Boutayeb')
DISPLAY P_chef AT 12,12
SLEEP 2
}

# Requete d)
# ---------
{ DEFINE V_nome LIKE Employe.nome
DEFINE V_fonct LIKE Employe.fonct
DEFINE V_sal LIKE Employe.sal
declare C_Employe cursor for select nome,fonct,sal into V_nome,V_fonct,V_sal
FROM Employe
WHERE fonct=(select fonct from Employe where nome='Nasri')
OR sal>( SELECT sal FROM employe where nome='Boutayeb')
#ORDER BY sal
open C_Employe
while true
FETCH C_Employe into V_nome, V_fonct, V_sal
IF status=100 THEN EXIT WHILE
ELSE display V_nome, V_fonct, V_sal
display " "
SLEEP 0
End if
end while
sleep 5
CLOSE C_Employe
}

# Requete e)
# ---------
{define employe Record like employe.*
declare C_Employe cursor for Select * from employe A, employe B
where A.chef=B.nume and A.nums<>B.nums

open C_Employe

while true
FETCH C_Employe into employe.*
IF status=100 THEN EXIT WHILE
ELSE display employe.*
SLEEP 2
End if
end while

CLOSE C_Employe
}

# Requete f)
# ---------
{WHENEVER ERROR CONTINUE
INSERT INTO Employe (nume) VALUES (7782)
IF STATUS=-239 THEN
DISPLAY "tuple existant" At 12, 30
END IF
sleep 2
}

# Requete g)
# ---------
{define sal_moy decimal(7,2)
define com_moy decimal(7,2)
select avg(sal) x from employe
where nums=(select nums from service where loc='Meknes')
into temp tabsal
select avg(comm) y from employe
where nums=(select nums from service where loc='Meknes')
into temp tabcom
select x into sal_moy from tabsal
select y into com_moy from tabcom
update employe
set sal= 1.1 * sal_moy,
comm = 1.2 * com_moy,
nums=(select nums from service where loc='Casa')
where nums=(select nums from service where loc ='Meknes')

if status =0 then display 'succes'
else display 'echec'
end if
sleep 2
}
# Requete h)
# ---------
{
define V1 like employe.fonct
define V2 smallint
define V3 like employe.sal
declare C_Employe cursor for select count(*),fonct, AVG(sal) from employe
group by fonct
having count(*)>=3
open C_Employe

while true
FETCH C_Employe into V2, V1, V3
IF status=100 THEN EXIT WHILE
ELSE display V1, V2, V3
SLEEP 0
End if
end while

CLOSE C_Employe
}

# Requete e')
# ---------
{define V1 record like employe.*
define V2 like enfant.nomf

declare C cursor for select A.*, B.nomf
from employe A,outer enfant B
where A.nume=B.nume
open C
while true
FETCH C into V1.*, V2
IF status=100 THEN EXIT WHILE
ELSE display V1.*
display " "
display V2
display " "
sleep 0
End if
end while
sleep 2
close C

}

# Requete e'')
# ---------
{define V2 like employe.fonct
define V1 like employe.sal

declare C cursor for select SUM(sal), fonct from employe
where nums=10
and fonct in (select fonct from Employe A , service B
where A.nums=B.nums
and B.noms='vente')
group by fonct
open C
while true
FETCH C into V1, V2
IF status=100 THEN EXIT WHILE
ELSE display V2, V1
End if
end while
sleep 2
CLOSE C
}

# Requete g')
# ---------
{define V char(18 )
declare C cursor for select tabname
from systables T, syscolumns C
where T.tabid=C.tabid
and colname='nums'
open C
while true
FETCH C into V
IF status=100 THEN EXIT WHILE
ELSE display V
End if
end while
sleep 2
CLOSE C
}
# Requete g'')
# ---------
{define V smallint
select coltype into V from Syscolumns
where tabid=(select tabid from systables
where tabname='Employe')
and colname='fonct'
display "Le type de donn‚es de 'fonct' est: ",V at 12, 12
sleep 2
}
END MAIN


Vive INSEA

Salam,

Pour avoir les questions dont les réponses sont celles d'en haut cliquer sur ce lien

salam,
zeeen..
merci chers laureats..

svp si qlq pourra m' aider , je voudrai un cours sur informix