数据库应用技术 ---案例new

《数据库应用技术 ---案例new》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、1、selectUSEstudentGOSELECTstud_id,name,birthday,gender,markFROMstud_infoWHEREnameLIKEN'郑_'USEstudentGOSELECTteacher_id,name,tech_title,salaryFROMteacher_infoWHEREtech_titleIN(N'助教',N'讲师',N'副教授')USEstudentGOSELECTAVG(grade)FROMstud_gradeWHEREcourse_id='0401010102'USEst

2、udentGOSELECTstud_id学号,name姓名,year(getdate())-year(birthday)年龄,birthday出生日期FROMstud_infoWHEREgender=N'男'ORDERBYbirthdayASCSEstudentGOSELECTsubstring(stud_id,5,2)专业编号,avg(mark)平均入学成绩FROMstud_infoWHEREsubstring(stud_id,3,2)='01'GROUPBYsubstring(stud_id,5,2)USEstudentGOS

3、ELECTtech_title,avg(age)FROMteacher_infoGROUPBYtech_titleHAVINGtech_title=N'讲师'USEstudentGOSELECTtech_title,salaryFROMteacher_infoWHEREtech_title=N'讲师'ORDERBYtech_titleCOMPUTEsum(salary)/*查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩*/USEstudentGOSELECTstud_info.stud_id,stud_grade.

4、name,stud_info.zipcode,stud_grade.gradeFROMstud_info,stud_gradeWHEREstud_info.stud_id=stud_grade.stud_id/*在FROM子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号*/USEstudentGOSELECTteacher_info.teacher_id,teacher_info.name,lesson_info.course_nameFROMlesson_infoINNERJOINteacher_infoON(l

5、esson_info.course_id=teacher_info.course_id)/*在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参加考试的学生基本信息和成绩分数。*/USEstudentGOSELECT*FROMstud_infoINNERJOINstud_gradeONstud_info.stud_id=stud_grade.stud_idORDERBYstud_info.stud_id/*stud_info和stud_grade采用自然连接以限制结果集的冗余列数据*/USEs

6、tudentGOSELECTstud_grade.*,stud_info.telcode,stud_info.markFROMstud_gradeINNERJOINstud_infoONstud_grade.stud_id=stud_info.stud_idORDERBYstud_grade.stud_idUSEstudentGOINSERTINTOstud_info--为了说明方便，先在学生信息表中插入一条新记录VALUES('0401010704',N'王一明','03/03/1986',N'男',N'甘肃省兰州市','062

7、1-2221837','590000',573)SELECTstud_info.stud_id,stud_info.name,stud_grade.course_idFROMstud_infoLEFTOUTERJOINstud_gradeONstud_info.stud_id=stud_grade.stud_idORDERBYstud_info.stud_id,stud_info.name,stud_grade.course_id/*学生信息表stud_info右外连接学生成绩表stud_grade*/USEstudentGOSE

8、LECTstud_info.stud_id,stud_info.name,stud_grade.course_idFROMstud_gradeRIGHTOUTERJOINstud_infoONstud_info.stud_id=stud_grade