--建表语句
CREATETABLEemp
(
enamevarchar(20)null,
hirdatedatenull,
salnumeric(8,2)null,
deptnoTINYINTnull
);
CREATETABLEdept
(
deptnoTINYINTnotnull,
deptnamevarchar(20)null
);
INSERTINTOemp(ename,hirdate,sal,deptno)
SELECT'zzx','2000-01-01','100',1UNIONALL
SELECT'lisa','2003-02-01','200',2UNIONALL
SELECT'bjguan','2004-04-02','100',1UNIONALL
SELECT'dony','2005-02-05','2000',4;
INSERTINTOdept(deptno,deptname)
SELECT1,'tech'UNIONALL
SELECT2,'sale'UNIONALL
SELECT5,'fin';
--更新语句:
UPDATEempa,deptb
SETa.sal=a.sal*b.deptno,b.deptname=a.ename
WHEREa.deptno=b.deptno;
最后结果集:
emp表:
enamehirdatesaldeptno
zzx2000-01-011001
lisa2003-02-014002
bjguan2004-04-021001
dony2005-02-0520004
dept表:
deptnodeptname
1zzx
2lisa
5fin
|