Visual FoxPro数据库管理系统实验教程

实验8 Sql语言应用之一

2．使用rsgl数据库，写出满足下列要求和连接查询的SQL命令。

(1)select xm, csrq from zgqk where hf

(2)select xm, bmmc From zgqk, bm where zgqk.bmbh=bm.bmbh And ；

(bmmc="会计系" Or bmmc="经济系")

(3)select xm,xl from zgqk where xb="男"

(4)select * from zgqk,bm where zgqk.bmbh=bm.bmbh and bmmc="计算机系"；

And (zc="教授" or zc="副教授")

(5)select xm from zgqk where csrq>{^1975/01/01}

(6)select xm from zgqk,gz where zgqk.zgbh=gz.zgbh and jcgz<2500

(7)select xm ,bmmc,cgmc from zgqk, kyqk,bm where zgqk.zgbh=kyqk.zgbh and；

bm.bmbh=zgqk.bmbh

(8)select xm ,bmmc,zc,xw,jcgz,zwgz,zjgz from zgqk, gz,bm where ；

zgqk.zgbh=gz.zgbh and bm.bmbh=zgqk.bmbh

(9)select xm ,bmmc,zc,year(date())-year(gzrq) 工龄 from zgqk, bm where ；

bm.bmbh=zgqk.bmbh

3．使用rsgl数据库，写出使用子查询的SQL命令。

(1)select xm from zgqk where zgbh in (select zgbh from kyqk )

(2)select xm from zgqk where zgbh not in (select zgbh from kyqk )

(3)select bmmc from bm where bmbh not in (select bmbh from zgqk, kyqk；

where zgqk.zgbh=kyqk.zgbh )

(4)select bmmc from bm where bmbh in (select bmbh from zgqk where；

xw="博士")

(5)select xm from zgqk where zc="教授" and zgbh in (select zgbh from kyqk ) 4．使用rsgl数据库，写出分组和统计计算查询的SQL命令。

(1)select count(zgbh) 人数 from zgqk ,bm where zgqk.bmbh=bm.bmbh and ；

bmmc="财政系"

(2)select bmmc ,count(zgbh) 人数 from zgqk ,bm where zgqk.bmbh=bm.bmbh ；

group by bm.bmbh

(3)select bmmc ,count(zgbh) 教授人数 from zgqk ,bm where；

zgqk.bmbh=bm.bmbh and zc="教授" group by bm.bmbh order by；

教授人数 desc

(4)select sum (jcgz+zwgz+zjgz) 人数 from gz

(5)select bmbh , count(zgqk.zgbh) 人数 from zgqk , gz where；

zgqk.zgbh=gz.zgbh and jcgz>2500 group by bmbh

(6)select top 1 xm , count(cgbh) 数量 from zgqk,kyqk where；

zgqk.zgbh=kyqk.zgbh group by kyqk.zgbh order by 数量 desc

(7)select top 1 bmmc , count(cgbh) 数量 from zgqk, kyqk , bm where；

zgqk.zgbh=kyqk.zgbh and zgqk.bmbh=bm.bmbh group by bm.bmbh；

order by 数量 desc

(8)select zgqk.zgbh,xm,jcgz+zwgz+zjgz+fljj from zgqk,gz where；

zgqk.zgbh=gz.zgbh into table zggz

(9)select bmmc,sum(jcgz+zwgz+zjgz+fljj-sdf-mqf) from zgqk,gz,bm where；

zgqk.zgbh=gz.zgbh and bm.bmbh=zgqk.bmbh group by bm.bmbh；

into cursor sfgz

5．利用所建立的xsgl数据库，使用SQL Select完成下列查询。

(1) select * from student where dno=”d01” and sex=”男”

(2) select * from sc where cno=”c001” order by grade desc

(3) select sname from student where ( year(birthday)=1989 or ；

year(birth day)=1990) and sex=”女”

(4) select student.sno , sname ,grade from student, sc, course where；

student.sno=sc.sno and https://www.360docs.net/doc/b715064771.html,o=https://www.360docs.net/doc/b715064771.html,o and cname=”计算机基础”

(5) select sno from sc where allt(cno)="c001" and sno；

in (select sno from sc where allt(cno)="c002" )

(6) select sno from sc where allt(cno)="c001" and sno；

not in (select sno from sc where allt(cno)="c002" )

(7) select sno from sc where cno=”c001” union select sno from sc；

where cno=”c002”

或：select sno from sc where allt(cno)="c001" union select sno；

from sc where allt(cno)="c002"

(8) select count(*) from student where sex=”女”

(9) select count(*) from sc where grade<60 and；

cno=(select cno from course where cname=”计算机基础”)

或：select count(*) from sc where grade<60 and；

allt(cno) = (select cno from course where allt(cname)="计算机基础")

(10) select dno, count(*) from student group by dno

(11) select sno, sum(credit) 学分 from sc,course where；

https://www.360docs.net/doc/b715064771.html,o=https://www.360docs.net/doc/b715064771.html,o and grade>=60 group by sno

或：select sno, sum(credit) 学分 from sc,course where ；

allt(https://www.360docs.net/doc/b715064771.html,o)=allt(https://www.360docs.net/doc/b715064771.html,o) and grade>=60 group by sno

(12) select sno, sum(credit) 学分 from sc,course where https://www.360docs.net/doc/b715064771.html,o=https://www.360docs.net/doc/b715064771.html,o ；

and grade>=60 group by sno having学分>=30

或：select sno, sum(credit) 学分 from sc,course where ；

allt(https://www.360docs.net/doc/b715064771.html,o)=allt(https://www.360docs.net/doc/b715064771.html,o) and grade>=60 group by sno having学分>=30 (13) select cname from course where cno not in (select cno from sc) 或：select cname from course where allt(cno)；

not in (select allt(cno) from sc)

实验9 Sql语言应用之二

1．SQL数据修改命令。

(1)Insert Into zgqk (zgbh, xm, xb, xl, xw, zc)；

Values("199009", "和红", "女", "研究生","博士","讲师")

(2)Insert Into zgqk (zgbh) Values("A00009")

……

(3)delete from zgqk where left(zgbh,1)=”A”

(4)Update gz Set zwgz=zwgz+200 Where jcgz+zwgz<2000

(5) Update gz Set zwgz=zwgz*1.1 Where zgbh in (Select zgbh From；

zgqk where zc="教授" )

2．SQL数据定义命令练习一。

(1)Create Table student (sno C(6) , sname C(8), sex C(2) , birthday D , dno C(3)) Create Table sc ( sno C (6) , cno C(4) , grade n(3) )

Create Table dep ( dno C (3) , dname C(30) , tel C(8) )

Create Table course ( cno C (4) , sname C(30) , credit n(3,1), quota n(3) )

(2)Alter Table student alter sno C (6) Primary Key

(3)Alter Table student alter grade n(3) Check grade>=0 and grade<=100

或：Alter Table student alter grade set Check grade>=0 and grade<=100

(4)Alter Table dep alter dname c(35)

(5)Alter Table course drop quota

(6)Alter Table course add quota n(3)

(7)

(8)Select * from sc into table sc1

(9)Drop table sc1

(10) Create view age as select zgbh,xm,xb,zc,bmmc,year(date()-year(csrq)) nl； from zgqk,bm where zgqk.bmbh=bm.bmbh

(11) Drop view age