数库应课程实验报告
实验名称: Visual FoxPro 60根操作
实验类型: 验证设计
实验学时: 8学时
学 号:
姓 名:
班 级:
指导教师: 罗贺
实验点: 理学院号楼四楼机房
实验时间: 2021年11月19日26日
二〇二年十二月十日
添加30条数
〔3〕求门课均成绩结果存入average表
解:USE h\数库实验作业二\实验二\score表dbf EXCLUSIVE
sele score表Cnoavg(score表score)as 均分
from score表
into table average
group by score表Cno
Browse
截图:
〔4〕学生马丽鹃〞出生日期改〞
解:USE h\数库实验作业二\实验二\student表dbf EXCLUSIVE
edit for sname'马丽娟'
截图:
〔5〕学生zipcode属性列值填补
解:repl Zipcode with 230006
repl all Zipcode with 230006
USE h\studentdbf EXCLUSIVE
modify structure
repl all Zipcode with 230006
截图:
〔6〕average表中课程均成绩置零
解:USE h\数库实验作业二\实验二\averagedbf EXCLUSIVE
repl all 均分 with 0
截图:
〔7〕删average表中课程号c007’均成绩记录
解:USE h\数库实验作业二\实验二\averagedbf EXCLUSIVE
delete for Cno'C007'
browse
pack
Browse
截图
〔8〕删average表中均成绩记录
解:delete all
pack
browse
截图:
(9) 建立时学生信息表〔tstudent〕删该表中学号前六位001011’
学生记录
解:Select*
from student
into cursor tstudent
where substr(Sno16)'001011'
browse
截图:
〔10〕查询全体学生学号姓名
解:Select SnoSname
From student
截图:
〔11〕查询全体学生学号姓名属班级
解:Select SnoSnameSclass
From student
截图:
〔12〕查询全体学生详细记录
解:Select *
From student
截图:
〔13〕查询全体学生姓名年龄
解:Select Snameyear(date())year(Sbirthday) as age
From student
截图:
〔14〕查询全体学生姓名出生年份
解:Select SnameSbirthday
From student
截图
〔15〕查询修课学生学号
解:Select studentSno
From score student
Where scoreSnostudentSno
截图:
〔16〕查询信系0101〞班全体学生名单
解:Select Sname
From student
Where Sclass'信系0101'
截图:
〔17〕查询查询年龄27岁学生姓名年龄
解:Select Snameyear(date())year(Sbirthday) as age
From student
Where year(date())year(Sbirthday)<27
截图:
〔18〕查询考试成绩格学生学号
解:Select Sno
From score
Where Score<60
截图:
〔19〕查询年龄1528岁间学生姓名班级年龄
解:Select SnameSclass year(date())year(Sbirthday) as age
From student
Where year(date())year(Sbirthday)>15
and year(date())year(Sbirthday) <28
截图:
〔20〕查询年龄1528岁间学生姓名班级年龄
解:Select SnameSclass year(date())year(Sbirthday) as age
From student
Where year(date())year(Sbirthday)<15
or year(date())year(Sbirthday)>28
截图:
〔21〕查询信系0101〞电商系0102〞班学生姓名班级信息
解:Select SnameSclass
From student
Where Sclass'信系0101'or Sclass'电商系0102'
截图:
〔22〕查询信系0101〞电商系0102〞班学生姓名班级信息
解:Select SnameSclass
From student
Where Sclass '信系0101'and Sclass'电商系0102'
截图:
〔23〕查询学号011113104〞学生详细情况
解:Select *
From student
Where Sno'011113104'
截图:
〔24〕查询学号0111〞头学生信息
解:Select *
From student
Where substr(Sno14)'0111'
截图:
〔25〕查询姓张〞学生学号姓名性年龄
解:Select SnoSnameyear(date())year(Sbirthday) as age
From student
Where Sname like '张'
截图:
〔26〕查询名字中第二字海〞字学生学号姓名性年龄
解:Select SnoSname year(date())year(Sbirthday) as age
From student
Where substr(Sname32)'海'
截图:
〔27〕查询姓刘〞学生姓名
解:Select Sname
From student
Where substr(Sname12)'刘'
截图:
〔28〕查询课程号C〞开头两字母05〞课程号课程名
解:Select CnoCname
From course
Where Cno like 'C'and substr(Cno32)'05'
截图:
(29) 某学生选修某门课程没参加考试选修课记录没考试成绩试查
找缺少考试成绩学生相应课程号
解:Select SnoCno
From score
Where Score0
截图:
〔30〕查找全部成绩记录学生学号课程号
解:Select SnoCno
From score
Where Score0
截图:
〔31〕查找电商系0101〞班年龄27岁学生学号姓名
解:Select SnoSname
From student
Where Sclass'电商系0101'and year(date())year(Sbirthday)<27
截图:
〔32〕查找选修C001〞号课程学生学号成绩查询结果分数降序排序
解:Select SnoScore
From score
Where Cno'C001'
Order by Score desc
截图:
(33) 查询全体学生情况查询结果班级升序排列班级中学生年龄降序
排列
解:Select *year(date())year(Sbirthday)as age
From student
Order by Sclass ascage desc
截图:
〔34〕查询学生总数
解:Select count(Sno) as 学生总数
From student
截图:
〔35〕查询选修课程学生数
解:Select count(Sno) as 选修课程学生数
From score
截图:
〔36〕课程中查询高分学生学号成绩
解:Select Snomax(Score) as 高分
From score
截图:
〔37〕查询学C001〞课程学生高分数
解:Select max(Score) as 高分数
From score
Where Cno'C001'
截图
〔38〕计算课程号相应选课数
解:Select Cnocount(*) as 选课数
From score
Group by Cno
截图:
〔39〕查询电商系0102〞班选修两门课程学生学号姓名
解:Select studentSnostudentSnamecount(*) as 选课门数
From scorestudent
Where Sclass'电商系0102'and scoreSnostudentSno
Group by Cno
having count(*)>2
截图:
〔40〕然连接studentscore表
解:Select *
From student full join score
On studentSnoscoreSno
截图:
〔41〕身连接查询门课程间接先行课〔先行课先行课〕
解:Select xCnoyCpno
From course as xcourse as y
Where xCpnoyCno
Group by xCno
截图:
〔42〕复合条件连接查询选修c001〞号课程成绩90分学
解:Select studentSnostudentSnamescoreScore
From studentscore
Where studentSnoscoreSno
and scoreCno'C001'
and scoreScore>90
截图:
〔43〕复合条件连接查询学生选修课程名成绩
解:Select courseCnamescoreScore
From coursescore
Where courseCnoscoreCno
截图:
〔44〕查询选修全部课程学生
解:Select studentSnostudentSname
From student
Where Sno in
(select scoreSno
From score
Group by scoreSno
Having count(*)7)
截图:
〔45〕查询少选修全部学分数4学分课程学生学号姓名
解:select studentSnostudentSname
from studentscorecourse
where studentSnoscoreSno
and scoreCnocourseCno
group by scoreSno
having sum(courseCredit)>4
截图:
〔46〕查询选修C001号课程学生学号姓名
解:Select studentSnostudentSname
From student
Where Sno in
(select scoreSno
From score
Where scoreCno'C001')
截图:
〔47〕查询选修课程C001c007学生学号姓名
解:Select studentSnostudentSname
From student
Where Sno in
(select scoreSno
From score
Where scoreCno'C001'or scoreCno'C007')
截图
〔48〕查询会计系0102〞班学生年龄27岁〔现年龄〕学生
解:Select *
From student
Where Sclass'会计系0102'and year(date())year(Sbirthday)<27
截图:
〔49〕查询选修课程C001选修课程c007学生学号姓名
解:Select studentSnostudentSname
From student
Where Sno in
(select scoreSno
From score
Where scoreCno in('C001''C007')
Group by scoreSno
Having count(Cno)2)
截图:
〔50〕查询选修课程名数库原理〞学生学号姓名性年龄
解Select studentSnostudentSname year(date())year(Sbirthday) as age
from studentscorecourse
where courseCname'数库原理'
and studentSnoscoreSno
and scoreCnocourseCno
截图:
〔51〕查询班中信系0101〞班学生年龄学生名单
解:Select Sname
From student
Where Sbirthday>all
(select Sbirthday
From student
Where Sclass'信系0101')
截图
〔52〕查询齐振国〞班学学生学号姓名性年龄
解:Select SnoSnameyear(date())year(Sbirthday)as age
From student
Where Sclass in
(select Sclass
From student
Where Sname'齐振国')
截图:
〔53〕建立信系0101〞班学生视图定义视图名info_student1〞
截图:
(55) 建立信系0101〞班选修C001〞课程学生视图定义视图名
info_C001_student1〞
截图:
(56) 建立信系0101〞班选修C001〞课程成绩90分学生视图定义
视图名info_c001_student2〞
截图:
〔57〕定义反映学生年龄视图定义视图名vbirthday_student〞
截图:
〔58〕学生表中女生记录定义视图视图名vfemale_student〞
截图:
〔59〕学生学号均成绩定义视图视图名vaverage_student〞
截图:
〔60〕删视图info_student1〞删重建
解:Delete view info_student1
截图:
〔61〕信系0101〞班学生视图中找出年龄27岁〔现年龄〕学生
解:use info_student2
select *
from info_student2
where year(date())year(Sbirthday)<27
截图:
〔62〕利视图查询信系0101〞班选修C001〞课程学生
解:Select Sname
From view info_C001_student2
截图:
(63) 通信系0101〞班info_student2视图中学号011111103〞学生姓名改潘
长江〞
解Modify view info_student2
截图:
(63) 信系0101〞班info_student1视图中插入新学生记录中:学号:
011111136姓名:张艺谋性:男出生日期:1987119
截图:
(65) 通视图info_student1删信系0101班学号011111135〞姓名黄健中〞
学生记录
解:Delete from info_student1
Where Sno'011111135'
截图:
文档香网(httpswwwxiangdangnet)户传
《香当网》用户分享的内容,不代表《香当网》观点或立场,请自行判断内容的真实性和可靠性!
该内容是文档的文本内容,更好的格式请下载文档