60cj資料庫
『壹』 SQL資料庫編程
while 平均分<85
begin
update 表 set 平均分=平均分+ (case 變數 when 平均分<90 and 平均分>80 then 1
when 平均分<80 and 平均分>70 then 2
when 平均分<70 and 平均分>60 then 3
when 平均分<60 then 5)
end
『貳』 關於資料庫的題目，不會做！急求大師解答！
36、B 日期可以加天來數，結自果為N天後的日期。
37、A 字元連接，
38、C 指標到了表尾，表尾是在記錄後一條
39、D soft on 單詞錯誤，排序應用送sort on
40、a
41.b
42.c
43.B
44.b
45.d
46.a
47.d
48.c,
49.a
50.a
51.d
『叄』 資料庫SQL查詢操作
貌似學校的題目都是這種類型的
中文要切換輸入法，轉換下表，學生表裡面沒有姓名？
xs(xh,nl,xb,xim,xm)//最後面分別是 系名 和 姓名
kc(kh,km,xf,xs)
xk(xh,kh,cj)
select count(1),avg(cj) from kc,xk where kc.kh = xk.kh and km = "資料庫原理"
select xm from xs where xh in (select xh from xk group by xh having count(1) > 3)
select distinct xim from xs group by xim having count(1) > 1000
select xm from xs where not exists (select 1 from xk where xh = xs.xh and cj < 60)
『肆』 資料庫SQL語言
SQL 練習題
一 學生 – 課程資料庫
1 查詢 7號課程沒有考試成績的學生學號
select sno from sc where cno=』7』 and grade is null
2 查詢 7號課程成績在90分以上或60分以下的學生學號
select sno from sc where grade>90 or grade<60
3 查詢課程名以「數據」兩個字開頭的所有課程的課程號和課程名。
Select cno,cname from c where cname like 『數據%』
4 查詢每個學生所有課程的平均成績，輸出學生學號、平均成績
select sno,avg(grade) from sc group by sno
5 查詢每門課程的選修人數，輸出課程號、選修人數。
Select cno,count(*) from sc group by cno
6 查詢選修 7號課程的學生的學號、姓名、性別。
Select s.sno, sname,ssex from s , sc where s.sno=sc.sno and cno = 『7』
7 查詢選修7號課程學生的平均年齡。
Select avg(sage) from s , sc where s.sno=sc.sno and cno = 『7』
8 查詢由30名以上學生選修的課程號。
Select sno from sc group by cno having count(*)>30
9 查詢至今沒有考試不及格的學生學號
a: select sno from s where sno not in ( select sno from sc where grade<60 )
b: select sno from sc group by sno having min(grade)>=60
二
1 找出選修課程號為 C2 的學生學號與成績。
Select sno,grade from sc where cno=』C2』
2 找出選修課程號為C4 的學生學號與姓名。
Select s.sno , sname from s,sc where s.sno=sc.sno and cno=』C4』
3 找出選修課程名為 Maths 的學生學號與姓名。
Select s.sno ,sname from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno and cname = 『Maths』
4找出選修課程號為C2或C4 的學生學號。
Select distinct sno from sc where cno in (『C2』,』C4』)
或： Select distinct sno from sc where cno=』C2』 or cno =』C4』5找出選修課程號為C2和C4 的學生學號。
Select sno from sc where cno =』C2』 and sno in (
select sno from sc where cno = 『C4』 )6 找出不學C2課程的學生姓名和年齡
select sname , sage from s where sno not in ( select sno from sc where cno=』C2』 )
或：
select sname , sage from s where not exists ( select * from sc where sc.sno=s.sno and cno=』C2』 )
7 找出選修了資料庫課程的所有學生姓名。(與3同)
Select s.sno ,sname from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno and cname = 『資料庫』
8 找出資料庫課程不及格的女生姓名
嵌套：
select sname from s where ssex = 『女』 and sno in ( select sno from sc where grade<60 and cno in ( select cno from c where cname=』資料庫』) )
連接：
Select sname from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno and ssex=』女』 and cname = 『資料庫』 and grade<609 找出各門課程的平均成績，輸出課程名和平均成績
select cname , avg(grade) from sc , c where c.cno =sc.cno group by sc.cno10找出各個學生的平均成績，輸出學生姓名和平均成績
select sname , avg(grade) from s , sc where s.sno=sc.sno group by sc.sno
11 找出至少有30個學生選修的課程名
select cname from c where cno in ( select cno from sc group by cno having count(*)>=30 )
12 找出選修了不少於3門課程的學生姓名。
Select sname from s where sno in ( select sno from sc group by sno having count(*)>=3)
13 找出各門課程的成績均不低於90分的學生姓名。
Select sname from s where sno not in ( select sno from sc where grade<90)
14* 找出資料庫課程成績不低於該門課程平均分的學生姓名。
Select sname from s where sno in (
Select sno from sc , c where sc.cno=c.cno and cname=』資料庫』 and
Grade > (Select avg(grade) from sc , c where sc.cno=c.cno and cname=』資料庫』 ) )
15 找出各個系科男女學生的平均年齡和人數。
Select sdept,ssex , avg(sage) , count(*) from s
Group by sdept , ssex
16 找出計算機系(JSJ)課程平均分最高的學生學號和姓名。
Select sc.sno , sname from s, sc where s.sno=sc.sno and sdept=』JSJ』
Group by sc.sno Having avg(grade) =
( Select top 1 avg(grade) from sc, s where s.sno=sc.sno and sdept=』JSJ』
group by sc.sno order by avg(grade) DESC )三 客戶 – 商品資料庫中包括3按各表：KH,FP,YWY
1 查詢工資在 1000 到3000 元之間的男性業務員的姓名和辦公室編號。
Select Yname , Ono from YWY where salary between 1000 and 3000 and Ysex=』男』
2 查詢各個辦公室的業務員人數，輸出辦公室編號和對應的人數。
Select Ono , count(*) from YWY group by Ono
3 查詢每個客戶在2002年5月購買的總金額，輸出客戶號和相應的總金額。
Select Kno,sum(Fmoney) from FP where fdate between 『2002.5.1』 and 『2002.5.31』
Group by Kno
4 查詢2002年5月購買次數超過5次的所有客戶號，且按客戶號升序排序。
Select Kno from FP where fdate between 『2002.5.1』 and 『2002.5.31』
Group by Kno having count(*)>5
Order by Kno ASC
5 查詢各辦公室男性和女性業務員的平均工資。
Select Ono,Ysex ,avg(salary) from YWY group by Ono , Ysex
6 查詢2002年5月曾經在王海亮業務員手中購買過商品的客戶號、客戶姓名、聯系電話。
Select Kno,Kname,phone from KH where Kno in (
Select kno from FP where fdate between 『2002.5.1』 and 『2002.5.31』 and
Yno=(select Yno from YWY where Yname = 『王海亮』 )7 查詢所有工資比1538號業務員高的業務員的編號、姓名、工資。
Select yno ,Yname, salary from YWY where salary >
( Select salary from YWY where Yno=』1538』 )
8 查詢所有與1538號業務員在同一個辦公室的其他業務員的編號、姓名。
Select Yno , Yname from YWY where Yno<>』1538』 and Ono in (
Select Ono from YWY where Yno=』1538』 )
9 查詢銷售總金額最高的業務員的編號。
Select Yno from FP Group By Yno Having sum(Fmoney) =
(Select top 1 sum(Fmoney) from FP group by Yno ORDER BY sum(Fmoney) DESC)
10 查詢所有業務員的編號、姓名、工資以及工資比他高的其他業務員的平均工資。
利用自身連接
Select y1.Yno ,y1.Yname ,y1.salary , avg( y2. salary) from YWY y1 , YWY y2
Where y1.Yno<>y2.Yno and y1.salary < y2.salary
Group by y1.Yno
Sno salary sno salary
1 100 1 100
2 120 2 120
3 90 3 90
4 110 4 110四 某中學資料庫中由一張表：
學生選課表：由板及代碼、班內學號、姓名、科目、成績五個屬性組成，關系模式為
SC(BJDM,BNXH,XSXM,KM,CJ) ,其中(BJDM,BNXH)為主碼。
說明：每個學生每門科目存放一個記錄，科目有「語文」、「數學」、「外語」三門。1 找出每個班級的班級代碼、學生人數、平均成績。
Select BJDM,count(*) ,avg(CJ) from SC group by BJDM
2 找出每個學生的班級代碼、學生姓名、考試科目數、總成績。
Select BJDM,XSXM,count(*) , sum(CJ) from SC
Group by BNXH
3 輸出一張表格，每位學生對應一條記錄，包括：班級代碼、姓名、語文成績、數學成績、外語成績。
方法一：利用視圖
create view v1 (bjdm,xsxm, yw,sx,wy ) AS
select bjdm , xsxm , cj , 0,0 from sc where km=』語文』
union
select bjdm , xsxm , 0 , cj,0 from sc where km=』數學』
union
select bjdm , xsxm , 0,0,cj from sc where km=』外語』select bjdm, xsxm , sum(yw) as 語文, sum(sx) as 數學, sum(wy) as 外語 from v1 group by bjdm, xsxm
方法二:自身連接
select a.bjdm,a.xsxm , a.km,a.cj , b.km,b.cj , c.km,c.cj from sc a , sc b , sc c
where a.bjdm=b.bjdm and a.bnxh= b.bnxh and b.bjdm=c.bjdm and b.bnxh= c.bnxh
and a.km=』語文』 and b.km=』數學』 and c.km=』外語』方法三：利用存儲過程（略）
4 輸出一張表格：由成績低於60分的每位學生對應一條記錄，包括欄位：班級代碼、姓名、最低成績。Select bjdm,xsxm ,min(CJ) from sc where grade<60 group by bjdm,xsxm5輸出一張表格：由成績低於60分的每位學生對應一條記錄，包括欄位：班級代碼、姓名、最高成績、平均成績。
得到平均成績：create view V1 (bjdm,bnxh ,avg_cj) AS
select bjdm,bnxh ,avg(cj) from sc where bjdm , bnxhselect sc.bjdm,sc.xsxm ,max(cj) , avg_cj from sc , V1
where sc.bjdm=v1.bjdm and sc.bnxh=V1.bnxh and cj<60
group by sc.bjdm,sc.xsxm6輸出一張表格：所有成績不低於60分的每位學生對應一條記錄，包括欄位：班級代碼、姓名、平均成績。select bjdm, xsxm , avg(cj) from sc
where sno not in ( select sno from sc where grade<60)
group by bjdm, xsxm7輸出一張表格：每一位學生對應一條記錄，包括欄位：班級代碼、姓名、去掉一個最低分後的平均成績。
方法一：
得到每個學生的最低分：
create view V1 (bjdm,bnxh ,min_cj) as
select bjdm,bnxh,min(cj) from sc group by bjdm,bnxhselect sc.bjdm,sc.xsxm , avg(cj) from sc , v1
where sc.bjdm=v1.bjdm and sc.bnxh=v1.bnxh and sc.cj <> v1.min_cj
group by bjdm,bnxh方法二：
select sc.bjdm,sc.xsxm , ( sum(cj) – min(cj) ) / count(*) from sc
group by bjdm , bnxh8輸出一張表格：每門科目對應一條記錄，包括欄位：科目、去掉一個最低分後的平均成績。
方法一：
得到每門課的最低分：
create view V1 ( km, min_cj) as
select km,min(cj) from sc group by km
select sc.km , avg(cj) from sc , v1
where sc.km=v1.km and sc.cj <> v1.min_cj
group by sc.km
方法二：
select km , (sum( cj) – min(cj) )/count(*) from sc
group by km補充9：輸出表格：每門科目對應一條記錄，包括欄位：科目、去掉一個最低分和最高分後的平均成績。
select km , (sum( cj) – min(cj) – max(cj) )/count(*) from sc
group by km五 資料庫存放著某高校1990年以來英語四、六級的考試情況，且規定：
1 英語四、六級考試每年分別在6月和12月舉行二次；
2 四級沒有通過的學生不能報考六級；
3 某一級的考試只要沒有通過可以反復參加考試；
4 某一級的考試一旦通過就不能再報考同級的考試；
5 允許報了名但不參加考試。
該資料庫中有二張表，相應的關系模式如下：
學生表：S(Sno, Sname, Ssex, Sage, Sdept)，其中Sno為主碼。
考試表：E(Sno, Year, Month, Level, Grade)，學號、年、月、級別、成績。
其中(Sno, Year, Month)為主碼。1. 找出各次四級和六級考試的參考人數和平均成績（報了名但沒有參加考試的不作統計）
select year , month,level ,count(*) , avg(grade)
group by year,month , level2. 找出各次四級考試中平均分最高的系科（報了名但沒有參加考試的不作統計）。
A: Select sdept from s , e where s.sno=e.sno
Where level=4
Group by sdept
Having avg(grade)>=ALL(
Select avg(grade) from s , e where s.sno=e.sno where level=4 Group by sdept )B: Select top 1 sdept from s , e where s.sno=e.sno
Where level=4
Group by sdept
Order by (avg(grade) desc
3. 找出已經通過英語六級考試的學生的學號、姓名和性別（用連接方法做）
select s.sno,sname,ssex from s,e
where s.sno=e.sno and level=6 and grade>=60
4. 找出在同一年中四、六級考試都參加了的學生的學號
1) select sno from E
where (level=4 and grade>=60) or level=6
group by year having count(*)>=22) select sno from E X where level=4 and grade>=60 and exists (
select * from E Y where Y.sno=X.sno and year=X.year and level=6 )5. 找出只參加一次考試就通過了英語六級考試的學生的學號
select sno from E
where level=6
group by sno
having count(*)=1 錯，想想為何？1) select sno from E
where level=6
group by sno
having count(*)=1 and max(grade)>=602) select sno from E where level=6 and grade>=60 and sno in (
select sno from E where level=6 group by sno having count(*)=1)
6. 找出至今沒有通過英語四級考試的學生的學號（應包括至今還沒有參加過考試或者是參加了但還沒有通過兩種）
select sno from E where level=4
group by sno
having max(grade)<60
Union
Select sno from s where sno not in( select sno from E)
7. 找出英語六級考試中合格人數最少的考試年份和月份（有並列的都要列出，用一句SQL語句）。
Select year , month From E
Where level = 6 and grade>=60
Group by year , month
Having count(*) <=all
(Select count(*) from E where level=6 and grade>=60 group by year , month )
補充：查詢每門課程的及格率。
1 得到每門課的選修人數 2 得到每門課及格人數 3每門課的及格人數/每門課選修人數1create view v_all( cno , cnt) as select cno , count(*) from sc group by cno
2create view v_pass( cno , cnt_pass) as select cno , count(*) from sc where grade>=60
group by cno
3 select cno , cnt_pass*1.0 / cnt from v_all , v_pass where v_all.cno = v_pass.cno
『伍』 資料庫應用
1、insert JBQK values('0001'，'小王2'，『男』，19）
insert JBQK values('0002'，'小王1'，『男』，15)
insert JBQK values('0003'，'小王3'，『女』，17)
insert JBQK values('0004'，'小王4'，『男』，15)
insert JBQK values('0005'，'小王5'，『女』，16)
insert XXCJ values（『0001』，『小王2』，65，90，76）
insert XXCJ values（『0002』，『小王1』，60，89，76）
insert XXCJ values（『0003』，『小王3』，60，45，79）
insert XXCJ values（『0004』，『小王4』，67，90，76）
insert XXCJ values（『0005』，『小王5』，60，90，67）
insertJTQK values（『小王2』，『A』，『F'）
insert JTQK values（『小王1』，『B』，『E）
insert JTQK values（『小王3』，『A』，『F』）
insert JTQK values（『小王4』，『C』，『H』）
insert JTQK values（『小王5』，『D』，『J』）
2、ALTER TABLE JBQK drop column 性別
ALTER TABLE JBQK add 性別 varchar（2） default '女'
3、alter table XXCJ add 總分 as 語文+數學+英語
4、select top 3 * from XXCJ oder by 總分 desc
5、alter table XXCJ add constraint _1 check (語文>=0 and 語文<=100)
alter table XXCJ add constraint _2 check （數學>=0 and 數學<=100）
alter table XXCJ add constraint _3 check （英語>=0 and 英語<=100）
6、 select a.學號，a.姓名，b.家庭住址 from JBQK a，JTQK b where a.姓名=b.姓名
7、檢索JBQK表中年齡最大學生的姓名以及學生的平均年齡。
select 姓名 from JBQK where 年齡=（select max(年齡) from JBQK ）
select avg(年齡) from JBQK
8、編寫程序，當某學生退學刪除JBQK表裡該生記錄時，能自動刪除該生的XXCJ表和JTQK表中的相應記錄
create trigger ondelete
on JBQK
before delete
as
begin
declare @ 姓名 varchar（20）
select @姓名=姓名 from deleted
delete from XXCJ where 姓名=@姓名
delete from JTQK where 姓名=@姓名
end
『陸』 資料庫 SQL應用題
10.題答案
select 課程號,總分,平均分,最高分,最低分(
select 課程號,
總分=sum(成績),
平均分=sum(成績)/count(*),
最高分=max(成績),
最低分=min(成績)
from 成績
group by 課程號) as kc order by 平均分 desc
11.題答案
select 課程號,
平均分=sum(成績)/count(*)
from 成績
where 課程號='1001' or 課程號='1002'
group by 課程號
12.題答案
select 姓名,xs.學號,kc.平均分
from 學生 as xs
left join (select 學號,
平均分=sum(成績)/count(*)
from 成績
group by 學號) as kc on kc.學號=xs.學號
where kc.平均分>80
『柒』 VF6.0 資料庫編程填空
1.將D1表中工齡最短得人得記錄顯示出來
SET TALK OFF
USE D:\D1
MIN=GL
J=RECCOUNT()
FOR I=1 TO J
IF _MIN>GL
MIN=GL
R=RECNO()
ENDIF
SKIP
ENDFOR
LIST FOR RECNO()=R
USE
SET TALK
CANCEL
2.輸入學生姓名，顯示該學生得記錄
SET TALK OFF
USE D:\D1
ACCE "請輸入學生得姓名：" TO XMM
LOCA FOR XM=XMM
IF FOUND()
DISP
ELSE
?"查無此人！"
ENDIF
USE
SET TALK ON
RETURN
3.顯示所有零件單價在20元以上得記錄
SET TALK OFF
USE D:\D1
DO WHILE .NOT. EOF()
IF LJDJ>=20
DISPLAY
ENDIF
SKIP
ENDDO
USE
SET TALK ON
RETURN
4.將D2中商品單價在30元以下（包括30元）得商品數量增加20
SET TALK OFF
USE D:\D2
DO WHILE .NOT. EOF()
IF SPDJ>30
SKIP
LOOP
ENDIF
REPLACE SPSL WITH SPSL+20
SKIP
ENDDO
LIST
USE
SET TALK ON
5.刪除所有副教授得記錄
SET TALK OFF
USE D:\D1
DO WHILE .NOT. EOF()
IF ZC="副教授"
DELETE
ENDIF
SKIP
ENDDO
PACK
USE
SETTALK
RETURN
6.根據輸入決定顯示及格或不及格學生得記錄（60或60分以上為及格，其餘為不及格）
SET TALK OFF
USE D:\D1
?"輸入P————顯示及格學生得記錄"
?"輸入F————顯示不及格學生記錄"
WAIT "請選擇：" TO K
IF K="P"
DISP FOR CJ>=60
ELSE
IF K="F"
DISP FOR CJ<60
ELSE
?"輸入錯誤"
ENDIF
ENDIF
USE
SET TALK ON
RETURN
7.將水果為山竹商品單價上漲10%
SET TALK OFF
USE D:\D1
DO WHILE NOT EOF()
IF SGMC="山竹"
REPLACE SGDJ WITH SGDJ*1.1
?"更新成功!"
ENDIF
SKIP
ENDDO
USE
SET TALK ON
RETURN
8.按照出生日期得先後次序建立索引，顯示索引後得全部數據，以及出生日期最大（年齡最小）的記錄
SET TALK OFF
SET HEANDING OFF
CLEAR
USE D:\D1
INDEX ON CSRQ TO I_CSRQ
LIST
GO BOTTOM
DISPLAY
SET HEARDING ON
USE
SET TALK ON
RETURN
『捌』 資料庫高手幫忙
不及格的：select 學號，姓名，專業，班級 from xsqk where 學號 in (select 學號 from xscj where cj<60 group by 學號 having count(*)>=1)
a：select * from xscj
b:select 姓名，性別，班級，學號 from xsqk
c:select year(getdate())-year（出生年月） as 年齡 from xsqk
d:select 學號，課程號，成績 as 考試成績 from xscj
e:select * from xscj where cj>=60
f:select 姓名，學號，班級 from xsqk where 總學分>=10
g:select 學分，學時 from xskc where 課程名=『數據結構』
h:select 姓名，班級 from xsqk where 姓名 like '張%'
i:select * from xsqk where 出生年月>'19800101' and 系別=計算機
『玖』 求做資料庫觸發器設計題
createorreplacetriggertri_cj
afterinsertonXSCJ
foreachrow
begin
if:new.cj>=60then
Dbms_output.put_line('成績合格，已完成插入操作');
else
Dbms_output.put_line('成績不合格，不能計入成績');
endif;
end;
編譯完成後，執行下圖的插入語句：
註：把代碼中的cj換成XSCJ表中對應的成績欄位。